使用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文件的可写的副本，所有的修改都是在这个副本上做的。下面是一个例子。
//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);
}
最后一个建议就是尽量使用新版本的jxl.