Java读写Excel

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

 packagewriter; 

   

 importjava.io.File; 

 importjava.io.IOException; 

 importjava.util.Locale; 

   

 importjxl.CellView; 

 importjxl.Workbook; 

 importjxl.WorkbookSettings; 

 importjxl.format.UnderlineStyle; 

 importjxl.write.Formula; 

 importjxl.write.Label; 

 importjxl.write.Number; 

 importjxl.write.WritableCellFormat; 

 importjxl.write.WritableFont; 

 importjxl.write.WritableSheet; 

 importjxl.write.WritableWorkbook; 

 importjxl.write.WriteException; 

 importjxl.write.biff.RowsExceededException; 

   

 publicclassWriteExcel { 

   

     privateWritableCellFormat timesBoldUnderline; 

     privateWritableCellFormat times; 

     privateString inputFile; 

   

 publicvoidsetOutputFile(String inputFile) { 

     this.inputFile = inputFile; 

     } 

   

     publicvoidwrite() throwsIOException, WriteException { 

         File file = newFile(inputFile); 

         WorkbookSettings wbSettings = newWorkbookSettings(); 

   

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

   

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

         workbook.createSheet("Report", 0); 

         WritableSheet excelSheet = workbook.getSheet(0); 

         createLabel(excelSheet); 

         createContent(excelSheet); 

   

         workbook.write(); 

         workbook.close(); 

     } 

   

     privatevoidcreateLabel(WritableSheet sheet) 

             throwsWriteException { 

         // Lets create a times font 

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

         // Define the cell format 

         times = newWritableCellFormat(times10pt); 

         // Lets automatically wrap the cells 

         times.setWrap(true); 

   

         // Create create a bold font with unterlines 

         WritableFont times10ptBoldUnderline = newWritableFont( 

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

                 UnderlineStyle.SINGLE); 

         timesBoldUnderline = newWritableCellFormat(times10ptBoldUnderline); 

         // Lets automatically wrap the cells 

         timesBoldUnderline.setWrap(true); 

   

         CellView cv = newCellView(); 

         cv.setFormat(times); 

         cv.setFormat(timesBoldUnderline); 

         cv.setAutosize(true); 

   

         // Write a few headers 

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

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

   

     } 

   

     privatevoidcreateContent(WritableSheet sheet) throwsWriteException, 

             RowsExceededException { 

         // Write a few number 

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

             // First column 

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

             // Second column 

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

         } 

         // Lets calculate the sum of it 

         StringBuffer buf = newStringBuffer(); 

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

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

         sheet.addCell(f); 

         buf = newStringBuffer(); 

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

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

         sheet.addCell(f); 

   

         // Now a bit of text 

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

             // First column 

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

             // Second column 

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

         } 

     } 

   

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

             throwsRowsExceededException, WriteException { 

         Label label; 

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

         sheet.addCell(label); 

     } 

   

     privatevoidaddNumber(WritableSheet sheet, intcolumn, introw, 

             Integer integer) throwsWriteException, RowsExceededException { 

         Number number; 

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

         sheet.addCell(number); 

     } 

   

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

             throwsWriteException, RowsExceededException { 

         Label label; 

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

         sheet.addCell(label); 

     } 

   

     publicstaticvoidmain(String[] args) throwsWriteException, IOException { 

         WriteExcel test = newWriteExcel(); 

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

         test.write(); 

         System.out 

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

     } 

 }


读取Excel

 packagereader; 

   

 importjava.io.File; 

 importjava.io.IOException; 

   

 importjxl.Cell; 

 importjxl.CellType; 

 importjxl.Sheet; 

 importjxl.Workbook; 

 importjxl.read.biff.BiffException; 

   

 publicclassReadExcel { 

   

     privateString inputFile; 

   

     publicvoidsetInputFile(String inputFile) { 

         this.inputFile = inputFile; 

     } 

   

     publicvoidread() throwsIOException  { 

         File inputWorkbook = newFile(inputFile); 

         Workbook w; 

         try{ 

             w = Workbook.getWorkbook(inputWorkbook); 

             // Get the first sheet 

             Sheet sheet = w.getSheet(0); 

             // Loop over first 10 column and lines 

   

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

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

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

                     CellType type = cell.getType(); 

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

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

                                 + cell.getContents()); 

                     } 

   

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

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

                                 + cell.getContents()); 

                     } 

   

                 } 

             } 

         } catch(BiffException e) { 

             e.printStackTrace(); 

         } 

     } 

     publicstaticvoidmain(String[] args) throwsIOException { 

         ReadExcel test = newReadExcel(); 

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

         test.read(); 

     } 

 }

posted on 2009-12-19 23:46 飞熊 阅读(858) 评论(0)  编辑  收藏 所属分类: java


只有注册用户登录后才能发表评论。


网站导航:
 
<2009年12月>
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

导航

统计

常用链接

留言簿(1)

随笔分类

随笔档案

文章分类

文章档案

收藏夹

搜索

最新评论

阅读排行榜

评论排行榜