JAVA读写EXCEL

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

• 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133

  package writer;   import java.io.File; import java.io.IOException; import java.util.Locale;   import jxl.CellView; import jxl.Workbook; import jxl.WorkbookSettings; import jxl.format.UnderlineStyle; import jxl.write.Formula; import jxl.write.Label; import jxl.write.Number; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import jxl.write.biff.RowsExceededException;     public class WriteExcel {        private WritableCellFormat timesBoldUnderline;      private WritableCellFormat times;      private String inputFile;       public void setOutputFile(String inputFile) {      this .inputFile = inputFile;      }        public void write() throws IOException, WriteException {          File file = new File(inputFile);          WorkbookSettings wbSettings = new WorkbookSettings();            wbSettings.setLocale( new Locale( "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();      }        private void createLabel(WritableSheet sheet)              throws WriteException {          // Lets create a times font          WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10 );          // Define the cell format          times = new WritableCellFormat(times10pt);          // Lets automatically wrap the cells          times.setWrap( true );            // Create create a bold font with unterlines          WritableFont times10ptBoldUnderline = new WritableFont(                  WritableFont.TIMES, 10 , WritableFont.BOLD, false ,                  UnderlineStyle.SINGLE);          timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);          // Lets automatically wrap the cells          timesBoldUnderline.setWrap( true );            CellView cv = new CellView();          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" );                  }        private void createContent(WritableSheet sheet) throws WriteException,              RowsExceededException {          // Write a few number          for ( int i = 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 = new StringBuffer();          buf.append( "SUM(A2:A10)" );          Formula f = new Formula( 0 , 10 , buf.toString());          sheet.addCell(f);          buf = new StringBuffer();          buf.append( "SUM(B2:B10)" );          f = new Formula( 1 , 10 , buf.toString());          sheet.addCell(f);            // Now a bit of text          for ( int i = 12 ; i < 20 ; i++) {              // First column              addLabel(sheet, 0 , i, "Boring text " + i);              // Second column              addLabel(sheet, 1 , i, "Another text" );          }      }        private void addCaption(WritableSheet sheet, int column, int row, String s)              throws RowsExceededException, WriteException {          Label label;          label = new Label(column, row, s, timesBoldUnderline);          sheet.addCell(label);      }        private void addNumber(WritableSheet sheet, int column, int row,              Integer integer) throws WriteException, RowsExceededException {          Number number;          number = new Number(column, row, integer, times);          sheet.addCell(number);      }        private void addLabel(WritableSheet sheet, int column, int row, String s)              throws WriteException, RowsExceededException {          Label label;          label = new Label(column, row, s, times);          sheet.addCell(label);      }        public static void main(String[] args) throws WriteException, IOException {          WriteExcel test = new WriteExcel();          test.setOutputFile( "c:/temp/lars.xls" );          test.write();          System.out                  .println( "Please check the result file under c:/temp/lars.xls " );      } }

• 读取Excel
   

• 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

  package reader;   import java.io.File; import java.io.IOException;   import jxl.Cell; import jxl.CellType; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException;   public class ReadExcel {        private String inputFile;        public void setInputFile(String inputFile) {          this .inputFile = inputFile;      }        public void read() throws IOException  {          File inputWorkbook = new File(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 ( int j = 0 ; j < sheet.getColumns(); j++) {                  for ( int i = 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();          }      }        public static void main(String[] args) throws IOException {          ReadExcel test = new ReadExcel();          test.setInputFile( "c:/temp/lars.xls" );          test.read();      }   }