您的位置: 旅游网 > 时尚

Java读写Excel

发布时间:2019-09-13 19:26:17

本文主要向你演示如何使用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

003importjava.io.File;

004importjava.io.IOException;

005importjava.util.Locale;

006

007importjxl.CellView;

008importjxl.Workbook;

009importjxl.WorkbookSettings;

010importjxl.format.UnderlineStyle;

011importjxl.write.Formula;

012importjxl.write.Label;

013importjxl.write.Number;

014importjxl.write.WritableCellFormat;

015importjxl.write.WritableFont;

016importjxl.write.WritableSheet;

017importjxl.write.WritableWorkbook;

018importjxl.write.WriteException;

019importjxl.write.biff.RowsExceededException;

020

021publicclassWriteExcel {

022

023privateWritableCellFormat timesBoldUnderline;

024privateWritableCellFormat times;

025privateString inputFile;

026

027publicvoidsetOutputFile(String inputFile) {

028this.inputFile = inputFile;

029}

030

031publicvoidwrite()throwsIOException, WriteException {

032File file =newFile(inputFile);

033WorkbookSettings wbSettings =newWorkbookSettings();

034

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

036

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

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

039WritableSheet excelSheet = workbook.getSheet(0);

040createLabel(excelSheet);

041createContent(excelSheet);

042

043workbook.write();

044workbook.close();

045}

046

047privatevoidcreateLabel(WritableSheet sheet)

048throwsWriteException {

049// Lets create a times font

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

051// Define the cell format

052times =newWritableCellFormat(times10pt);

053// Lets automatically wrap the cells

054times.setWrap(true);

055

056// Create create a bold font with unterlines

057WritableFont times10ptBoldUnderline =newWritableFont(

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

059UnderlineStyle.SINGLE);

060timesBoldUnderline =newWritableCellFormat(times10ptBoldUnderline);

061// Lets automatically wrap the cells

062timesBoldUnderline.setWrap(true);

063

064CellView cv =newCellView();

065cv.setFormat(times);

066cv.setFormat(timesBoldUnderline);

067cv.setAutosize(true);

068

069// Write a few headers

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

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

072

073}

074

075privatevoidcreateContent(WritableSheet sheet)throwsWriteException,

076RowsExceededException {

077// Write a few number

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

079// First column

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

081// Second column

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

083}

084// Lets calculate the sum of it

085StringBuffer buf =newStringBuffer();

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

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

088sheet.addCell(f);

089buf =newStringBuffer();

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

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

092sheet.addCell(f);

093

094// Now a bit of text

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

096// First column

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

098// Second column

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

100}

101}

102

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

104throwsRowsExceededException, WriteException {

105Label label;

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

107sheet.addCell(label);

108}

109

110privatevoidaddNumber(WritableSheet sheet,intcolumn,introw,

111Integer integer)throwsWriteException, RowsExceededException {

112Number number;

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

114sheet.addCell(number);

115}

116

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

118throwsWriteException, RowsExceededException {

119Label label;

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

121sheet.addCell(label);

122}

123

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

125WriteExcel test =newWriteExcel();

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

127test.write();

128System.out

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

130}

131}

读取Excel

01packagereader;

02

03importjava.io.File;

04importjava.io.IOException;

05

06importjxl.Cell;

07importjxl.CellType;

08importjxl.Sheet;

09importjxl.Workbook;

10importjxl.read.biff.BiffException;

11

12publicclassReadExcel {

13

14privateString inputFile;

15

16publicvoidsetInputFile(String inputFile) {

17this.inputFile = inputFile;

18}

19

20publicvoidread()throwsIOException {

21File inputWorkbook =newFile(inputFile);

22Workbook w;

23try{

24w = Workbook.getWorkbook(inputWorkbook);

25// Get the first sheet

26Sheet sheet = w.getSheet(0);

27// Loop over first 10 column and lines

28

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

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

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

32CellType type = cell.getType();

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

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

35+ cell.getContents());

36}

37

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

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

40+ cell.getContents());

41}

42

43}

44}

45}catch(BiffException e) {

46e.printStackTrace();

47}

48}

49

50publicstaticvoidmain(String[] args)throwsIOException {

51ReadExcel test =newReadExcel();

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

53test.read();

54}

55

56}

儿童发热39度怎么办
小儿厌食的病因
薏芽健脾凝胶怎么样
丁桂薏芽健脾凝胶成分
猜你会喜欢的
猜你会喜欢的