jxl对Excel的读、写、更新以及插入图片(含代码实例)

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); } }