Java读写Excel-中间件-软件频道-至顶网

Java读写Excel

扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条

本文主要向你演示如何使用JavaExcel API来读写Excel文件。关于JavaExcel API，这是一个开源的lib库。其相关的feature如下：

来源：陈皓的博客【原创】 2009年12月15日

关键字： Excel java

本文主要向你演示如何使用JavaExcel API来读写Excel文件。关于JavaExcel API，这是一个开源的lib库。其相关的feature如下：

支持Excel 95, 97, 2000,XP, 2003的制表页。

可以读写相关的Excel公式（仅支持Excel 97 及以后版本）

可以生成 Excel 2000 格式的xls文件。

支持字体，数字和日期格式。

支持单元格的阴影，边框和颜色。

可以修改已存在的制表页。

国际化多语言集。(公式目前支持，英文，法文，西班牙文和德文）

支持图表拷贝。

支持图片的插入和复制。

日志生成可以使用Jakarta Commons Logging, log4j, JDK 1.4 Logger, 等。

更多……

你可以在这里下载：http://jexcelapi.sourceforge.net/，然后，把jxl.jar加到你的Java的classpath中。

下面是两段例程，一段是如何创建Excel，一段是如何读取Excel。

创建Excel

 
001  
packagewriter;

002
  
003
importjava.io.File;

004
importjava.io.IOException;

005
importjava.util.Locale;

006
  
007
importjxl.CellView;

008
importjxl.Workbook;

009
importjxl.WorkbookSettings;

010
importjxl.format.UnderlineStyle;

011
importjxl.write.Formula;

012
importjxl.write.Label;

013
importjxl.write.Number;

014
importjxl.write.WritableCellFormat;

015
importjxl.write.WritableFont;

016
importjxl.write.WritableSheet;

017
importjxl.write.WritableWorkbook;

018
importjxl.write.WriteException;

019
importjxl.write.biff.RowsExceededException;

020
  
021
publicclassWriteExcel {

022
  
023
    privateWritableCellFormat timesBoldUnderline;

024
    privateWritableCellFormat times;

025
    privateString inputFile;

026
  
027
publicvoidsetOutputFile(String inputFile) {

028
    this.inputFile = inputFile;

029
    }

030
  
031
    publicvoidwrite()throwsIOException, WriteException {

032
        File file =newFile(inputFile);

033
        WorkbookSettings wbSettings =newWorkbookSettings();

034
  
035
        wbSettings.setLocale(newLocale("en","EN"));

036
  
037
        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);

038
        workbook.createSheet("Report",0);

039
        WritableSheet excelSheet = workbook.getSheet(0);

040
        createLabel(excelSheet);

041
        createContent(excelSheet);

042
  
043
        workbook.write();

044
        workbook.close();

045
    }

046
  
047
    privatevoidcreateLabel(WritableSheet sheet)

048
            throwsWriteException {

049
        // Lets create a times font

050
        WritableFont times10pt =newWritableFont(WritableFont.TIMES,10);

051
        // Define the cell format

052
        times =newWritableCellFormat(times10pt);

053
        // Lets automatically wrap the cells

054
        times.setWrap(true);

055
  
056
        // Create create a bold font with unterlines

057
        WritableFont times10ptBoldUnderline =newWritableFont(

058
                WritableFont.TIMES,10, WritableFont.BOLD,false,

059
                UnderlineStyle.SINGLE);

060
        timesBoldUnderline =newWritableCellFormat(times10ptBoldUnderline);

061
        // Lets automatically wrap the cells

062
        timesBoldUnderline.setWrap(true);

063
  
064
        CellView cv =newCellView();

065
        cv.setFormat(times);

066
        cv.setFormat(timesBoldUnderline);

067
        cv.setAutosize(true);

068
  
069
        // Write a few headers

070
        addCaption(sheet,0,0,"Header 1");

071
        addCaption(sheet,1,0,"This is another header");

072
  
073
    }

074
  
075
    privatevoidcreateContent(WritableSheet sheet)throwsWriteException,

076
            RowsExceededException {

077
        // Write a few number

078
        for(inti =1; i <10; i++) {

079
            // First column

080
            addNumber(sheet,0, i, i +10);

081
            // Second column

082
            addNumber(sheet,1, i, i * i);

083
        }

084
        // Lets calculate the sum of it

085
        StringBuffer buf =newStringBuffer();

086
        buf.append("SUM(A2:A10)");

087
        Formula f =newFormula(0,10, buf.toString());

088
        sheet.addCell(f);

089
        buf =newStringBuffer();

090
        buf.append("SUM(B2:B10)");

091
        f =newFormula(1,10, buf.toString());

092
        sheet.addCell(f);

093
  
094
        // Now a bit of text

095
        for(inti =12; i <20; i++) {

096
            // First column

097
            addLabel(sheet,0, i,"Boring text "+ i);

098
            // Second column

099
            addLabel(sheet,1, i,"Another text");

100
        }

101
    }

102
  
103
    privatevoidaddCaption(WritableSheet sheet,intcolumn,introw, String s)

104
            throwsRowsExceededException, WriteException {

105
        Label label;

106
        label =newLabel(column, row, s, timesBoldUnderline);

107
        sheet.addCell(label);

108
    }

109
  
110
    privatevoidaddNumber(WritableSheet sheet,intcolumn,introw,

111
            Integer integer)throwsWriteException, RowsExceededException {

112
        Number number;

113
        number =newNumber(column, row, integer, times);

114
        sheet.addCell(number);

115
    }

116
  
117
    privatevoidaddLabel(WritableSheet sheet,intcolumn,introw, String s)

118
            throwsWriteException, RowsExceededException {

119
        Label label;

120
        label =newLabel(column, row, s, times);

121
        sheet.addCell(label);

122
    }

123
  
124
    publicstaticvoidmain(String[] args)throwsWriteException, IOException {

125
        WriteExcel test =newWriteExcel();

126
        test.setOutputFile("c:/temp/lars.xls");

127
        test.write();

128
        System.out

129
                .println("Please check the result file under c:/temp/lars.xls ");

130
    }

131
}

读取Excel

01
packagereader;

02
  
03
importjava.io.File;

04
importjava.io.IOException;

05
  
06
importjxl.Cell;

07
importjxl.CellType;

08
importjxl.Sheet;

09
importjxl.Workbook;

10
importjxl.read.biff.BiffException;

11
  
12
publicclassReadExcel {

13
  
14
    privateString inputFile;

15
  
16
    publicvoidsetInputFile(String inputFile) {

17
        this.inputFile = inputFile;

18
    }

19
  
20
    publicvoidread()throwsIOException  {

21
        File inputWorkbook =newFile(inputFile);

22
        Workbook w;

23
        try{

24
            w = Workbook.getWorkbook(inputWorkbook);

25
            // Get the first sheet

26
            Sheet sheet = w.getSheet(0);

27
            // Loop over first 10 column and lines

28
  
29
            for(intj =0; j < sheet.getColumns(); j++) {

30
                for(inti =0; i < sheet.getRows(); i++) {

31
                    Cell cell = sheet.getCell(j, i);

32
                    CellType type = cell.getType();

33
                    if(cell.getType() == CellType.LABEL) {

34
                        System.out.println("I got a label "

35
                                + cell.getContents());

36
                    }

37
  
38
                    if(cell.getType() == CellType.NUMBER) {

39
                        System.out.println("I got a number "

40
                                + cell.getContents());

41
                    }

42
  
43
                }

44
            }

45
        }catch(BiffException e) {

46
            e.printStackTrace();

47
        }

48
    }

49
  
50
    publicstaticvoidmain(String[] args)throwsIOException {

51
        ReadExcel test =newReadExcel();

52
        test.setInputFile("c:/temp/lars.xls");

53
        test.read();

54
    }

55
  
56
}

Java读写Excel

业界热点: