利用java操作excel文件
Java Excel是一开放源码项目，通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。使用该API非Windows操作系统也可以通过纯Java应用来处理Excel数据表。因为是使用Java编写的，所以我们在Web应用中可以通过JSP、Servlet来调用API实现对Excel数据表的访问。
/**
* 从excel表格中读出数据
* @param sourcefile
* @return
*/
public List<WaterPrice> readExcel(String sourcefile) {
Workbook rwb = null;
List<WaterPrice> waterPrices = new ArrayList<WaterPrice>();
try {
// 直接从本地文件创建Workbook(工作簿)
InputStream is = new FileInputStream(sourcefile);
rwb = Workbook.getWorkbook(is);
// 获取第一张Sheet表(工作表，以0为下标开始)
Sheet rs = rwb.getSheet(0);
for (int i = 3; i < 28; i++) {
String managerDepartment = rs.getCell(0, i).getContents();
if (managerDepartment != null
&& managerDepartment.trim().length() > 0) {
BigDecimal wPrice = new BigDecimal(rs.getCell(1, i)
.getContents());
BigDecimal sewagePrice = new BigDecimal(rs.getCell(2, i)
.getContents());
String remark = rs.getCell(3, i).getContents();
WaterPrice waterPrice = new WaterPrice();
waterPrice.setManagerDepartment(managerDepartment);
waterPrice.setWaterPrice(wPrice);
waterPrice.setSewagePrice(sewagePrice);
waterPrice.setRemark(remark);
waterPrices.add(waterPrice);
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return waterPrices;
}
/**
* 将数据写入excel表格中
* @param targetfile
*/
public void writeExcel(String targetfile) {
WritableWorkbook wwb = null;
try {
// 创建可写入的Excel工作薄
OutputStream os = new FileOutputStream(targetfile);
wwb = Workbook.createWorkbook(os);
// 创建Excel工作表
WritableSheet ws = wwb.createSheet("水价管理", 0);
for (int i = 8; i < 28; i++) {
Label managerDepartment = new Label(0, i, "管理部门" + i);
ws.addCell(managerDepartment);
Number waterPrice = new Number(1, i, 555);
ws.addCell(waterPrice);
Number sewagePrice = new Number(2, i, 6);
ws.addCell(sewagePrice);
Label remark = new Label(3, i, "备注" + i);
ws.addCell(remark);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} finally {
try {
// 记得一定要写这两句哦,写入Exel工作表,关闭Excel工作薄对象
wwb.write();
wwb.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
java excel api 下载地址：
http://sourceforge.net/project/showfiles.php?group_id=79926
java excel api 参考学习地址：
http://www-128.ibm.com/developerworks/cn/java/l-javaExcel/