使用Jxl读写excel文件

用jxl读excel文件相对比较简单，以下这个函数实现了读excel文件的 的功能。根据传入的excel路径名，sheetid和行编号等读取一列的数据。最后返回给一个arraylist对象。

public ArrayList getList(int sheetid, int rowid, int fromrowno,String xlspath) {
    ArrayList aryList = new ArrayList();
    Workbook book = null;
    try {
      book =
          Workbook.getWorkbook(new java.io.File(xlspath));
    }
    catch (BiffException ex) {
      System.out.println("Read Excel file failed!");
      System.out.println("File name: " + xlspath);
      System.exit(0);

    }
    catch (IOException ex) {
      System.out.println("Read Excel file failed!");
      System.out.println("File name: " + xlspath);
      System.exit(0);

    }//get sheet object
    Sheet sheet = book.getSheet(sheetid);
    String result;
    int i = 0;
    int rowcnt = sheet.getRows();
    for (i = 0; i < rowcnt - fromrowno; i++) {
      result = "";
      Cell cell = sheet.getCell(rowid, i + fromrowno);
      result = cell.getContents();
      result = result.replace('\n', ' ');
      result = result.trim();
      if (!result.equals("")) {
        aryList.add(result);
      }
    }
    book.close();
    return aryList;
  }

Jxl在写excel文件时使用的方法比较怪，也可以说jxl不支持修改excel文件。它的处理方式是每次打开旧excel文件，然后创建一个该excel文件的可写的副本，所有的修改都是在这个副本上做的。下面是一个例子。

public void ExcelWriter(String strSrcFile){  

//create a workbook by opening a existing excel file

Workbook rw = null;
    try {
      rw = Workbook.getWorkbook(new File(strSrcFile));
    }
    catch (BiffException ex) {
      System.out.println("Open template Excel file failed!");
      System.out.println("File name: "+strSrcFile);
      CommonTool.log.warning("Open template Excel file failed! file name: " + strSrcFile);
      System.exit(0);
    }
    catch (IOException ex) {
      System.out.println("Open template Excel file failed!");
      System.out.println("File name: "+strSrcFile);
      CommonTool.log.warning("Open template Excel file failed! file name: " + strSrcFile);
      System.exit(0);
    }
//create a writable workbook by copying the old one
    FileOutputStream fstream = null;
    try {
      fstream = new FileOutputStream(strFile);
    }
    catch (FileNotFoundException ex1) {
      System.out.println("Create result Excel file failed!");
      System.out.println("File name: "+strFile);
      CommonTool.log.warning("Create result Excel file failed! file name: " + strFile);
      System.exit(0);
    }
    try {
      WritableWorkbook book =
          Workbook.createWorkbook(fstream, rw, new WorkbookSettings());
    }
    catch (IOException ex2) {
      System.out.println("Create result Excel file failed! Copy step");
      System.out.println("File name: "+strFile);
      CommonTool.log.warning("Create result Excel file failed! file name: " + strFile);
      System.exit(0);
    }
  
    try {
      WritableCellFormat format = new WritableCellFormat();
      format.setBorder(Border.ALL, BorderLineStyle.THIN);

//1: rowid; 2 column id
      jxl.write.Label label = new jxl.write.Label(1, 2, "test", format);
      book.addCell(label);
    }
    catch (WriteException ex) {
      System.out.println("Add cell failed!");
      System.out.println("Cell: "+str);
      System.exit(0);
    }

}