原址:http://blog.csdn.net/sjsky_007/article/details/5434978
Java Excel是一开放源码项目,通过它开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件以及插入图片等等。
详细介绍及API查看官方:http://www.andykhan.com/jexcelapi/index.html
ps:读取结束时注意调用close()方法;释放内存
写入结束时先调用write()方法,否则得到的是空Excel,因为先前的操作都是存储在缓存中。具体见代码
一、读取Excel的例子:
注意对数字、日期等不同CellType的读取
- /**
- * jxl 读取
- * @author Michael sun
- */
- public class JxlRead {
- /**
- * 读取 excel 文件
- * @param filePath
- * @throws Exception
- */
- private void readExcel(String filePath) throws Exception {
- InputStream is = null;
- Workbook workbook = null;
- try {
- is = new FileInputStream(filePath);
- workbook = Workbook.getWorkbook(is);
- // sheet row column 下标都是从0开始的
- Sheet sheet = workbook.getSheet(0);
- int column = sheet.getColumns();
- int row = sheet.getRows();
- System.out.println("共有" + row + "行," + column + "列数据");
- // A1是字符
- Cell cellA1 = sheet.getCell(0, 0);
- System.out.println("A1 type:" + cellA1.getType());
- if (cellA1.getType().equals(CellType.LABEL)) {
- System.out.println("A1 content:" + cellA1.getContents());
- }
- // B1是数字
- Cell cellB1 = sheet.getCell(1, 0);
- System.out.println("B1 type:" + cellB1.getType());
- if (cellB1.getType().equals(CellType.NUMBER)) {
- NumberCell numberCell = (NumberCell) cellB1;
- double douval = numberCell.getValue();
- System.out.println("B1 value:" + douval);
- }
- // C1是日期
- Cell cellC1 = sheet.getCell(2, 0);
- System.out.println("C1 type:" + cellC1.getType());
- if (cellC1.getType().equals(CellType.DATE)) {
- DateCell dateCell = (DateCell) cellC1;
- Date date = dateCell.getDate();
- System.out.println("C1 date:" + date);
- }
- // 操作完成时,关闭对象,释放占用的内存空间
- workbook.close();
- is.close();
- } catch (Exception e) {
- e.printStackTrace(System.out);
- } finally {
- if (is != null) {
- is.close();
- }
- }
- }
- /**
- * @param args
- * @throws Exception
- */
- public static void main(String[] args) throws Exception {
- String filePath = "D://test//testjxlread.xls";
- JxlRead jxlRead = new JxlRead();
- jxlRead.readExcel(filePath);
- }
- }
运行结果:
共有1行,4列数据
A1 type:Label
A1 content:字符
B1 type:Number
B1 value:123.0
C1 type:Date
C1 date:Wed Feb 24 08:00:00 CST 2010
二、写入Excel的例子:
- /**
- * 写入excel
- * @author Michael sun
- */
- public class JxlWrite {
- /**
- * 写入 excel 文件
- * @param filePath
- * @throws Exception
- */
- private void writeExcel(String filePath) throws Exception {
- OutputStream os = null;
- try {
- // 构建Workbook对象
- os = new FileOutputStream(filePath);
- WritableWorkbook wwb = Workbook.createWorkbook(os);
- // 构建Excel sheet
- WritableSheet sheet = wwb.createSheet("test write sheet", 0);
- // 设置标题格式
- WritableFont wfTitle = new jxl.write.WritableFont(
- WritableFont.ARIAL, 18, WritableFont.BOLD, true);
- WritableCellFormat wcfTitle = new WritableCellFormat(wfTitle);
- // 设置水平对齐方式
- wcfTitle.setAlignment(Alignment.CENTRE);
- // 设置垂直对齐方式
- wcfTitle.setVerticalAlignment(VerticalAlignment.CENTRE);
- // 设置是否自动换行
- wcfTitle.setWrap(true);
- // 合并A1->C2
- sheet.mergeCells(0, 0, 2, 1);
- Label titleCell = new Label(0, 0, "Title Cell ", wcfTitle);
- sheet.addCell(titleCell);
- WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
- WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
- Colour.BLUE);
- WritableCellFormat wcf = new WritableCellFormat(wf);
- // A3
- Label labelCell = new Label(0, 2, "Label Cell ");
- sheet.addCell(labelCell);
- // B3
- Label labelCellFmt = new Label(1, 2,
- "Label Cell with WritableCellFormat ", wcf);
- sheet.addCell(labelCellFmt);
- // A4 添加jxl.write.Number对象
- jxl.write.Number labelN = new jxl.write.Number(0, 3, 3.1415926);
- sheet.addCell(labelN);
- // B4 添加Number对象 jxl.write.NumberFormat
- NumberFormat nf = new NumberFormat("#.##");
- WritableCellFormat wcfN = new WritableCellFormat(nf);
- jxl.write.Number labelNF = new jxl.write.Number(1, 3, 3.1415926,
- wcfN);
- sheet.addCell(labelNF);
- // A5 添加jxl.write.Boolean对象
- jxl.write.Boolean labelB = new jxl.write.Boolean(0, 4, true);
- sheet.addCell(labelB);
- // A6 添加 jxl.write.DateTime对象
- jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 5,
- new Date());
- sheet.addCell(labelDT);
- // B6 添加DateTime对象 jxl.write.DateFormat
- jxl.write.DateFormat df = new jxl.write.DateFormat(
- "yyyy-MM-dd HH:mm:ss");
- WritableCellFormat wcfDF = new WritableCellFormat(df);
- jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 5,
- new Date(), wcfDF);
- sheet.addCell(labelDTF);
- //先调用write();再调用close();
- wwb.write();
- wwb.close();
- os.close();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- if (null != os) {
- os.close();
- }
- }
- }
- /**
- * @param args
- * @throws Exception
- */
- public static void main(String[] args) throws Exception {
- String filePath = "D://test//testjxlwrite.xls";
- JxlWrite jxlwrite = new JxlWrite();
- jxlwrite.writeExcel(filePath);
- }
- }
运行结果:
ps:添加DateTime对象时 如果没有加格式时,出现1900/1/0 不知啥原因?
三、插入图片
- /**
- * jxl 插入图片(图像格式只支持png)
- * @author Michael sun
- */
- public class JxlWriteImg {
- /**
- *
- * @param filePath
- */
- private void writeImg(String filePath) throws Exception {
- OutputStream os = null;
- try {
- String imgPath = "d://test//xx.png";
- os = new FileOutputStream(filePath);
- WritableWorkbook wwb = Workbook.createWorkbook(os);
- WritableSheet ws = wwb.createSheet("write img", 0);
- File imgFile = new File(imgPath);
- // WritableImage(col, row, width, height, imgFile);
- WritableImage image = new WritableImage(2, 1, 8, 20, imgFile);
- ws.addImage(image);
- wwb.write();
- wwb.close();
- } catch (Exception e) {
- System.out.println(e);
- } finally {
- if (null != os) {
- os.close();
- }
- }
- }
- /**
- * @param args
- * @throws Exception
- */
- public static void main(String[] args) throws Exception {
- String filePath = "D://test//testjxlwriteimg.xls";
- JxlWriteImg jxlWriteImg = new JxlWriteImg();
- jxlWriteImg.writeImg(filePath);
- }
- }
四、更新Excel
- /**
- * jxl 更新excel
- * @author Michael sun
- */
- public class JxlUpdate {
- /**
- *
- * @param filePath
- */
- private void doUpdate(String filePath) {
- try {
- // 获得原Excel文件
- Workbook wb = Workbook.getWorkbook(new File(filePath));
- // 打开一个文件的副本,并且指定数据写回到原文件
- WritableWorkbook wwb = Workbook.createWorkbook(new File(filePath),
- wb);
- // 对第一个工作簿的A1 更新
- WritableSheet wsheet0 = wwb.getSheet(0);
- WritableCell wc00 = wsheet0.getWritableCell(0, 0);
- if (wc00.getType() == CellType.LABEL) {
- Label label00 = (Label) wc00;
- label00.setString("updata data");
- }
- // 添加一个工作表
- WritableSheet sheet = wwb.createSheet("新增工作簿", 1);
- // 写入一些测试数据
- sheet.addCell(new Label(0, 0, "test data"));
- // 关闭工作薄对象
- wwb.write();
- wwb.close();
- wb.close();
- } catch (Exception e) {
- System.out.println(e);
- }
- }
- /**
- * @param args
- */
- public static void main(String[] args) {
- String filePath = "D://test//testjxlupdate.xls";
- JxlUpdate jxlUpdate = new JxlUpdate();
- jxlUpdate.doUpdate(filePath);
- }
- }