java使用Apache POI操作excel文件
- 官方介绍
HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
从官方文档中了解到：POI提供的HSSF包用于操作 Excel '97(-2007)的.xls文件，而XSSF包则用于操作Excel2007之后的.xslx文件。
- 需要的jar包
POI官网上下载包并解压获取java操作excel文件必须的jar包：
其中dom4j-1.6.1.jar和xbean.jar（下载地址：http://mirror.bjtu.edu.cn/apache/xmlbeans/binaries/ 网站：http://xmlbeans.apache.org
）并不包含在POI提供的jar包中，需要单独下载，否则程序会抛出异常：java.lang.ClassNotFoundException:org.apache.xmlbeans.XmlOptions。
）并不包含在POI提供的jar包中，需要单独下载，否则程序会抛出异常：java.lang.ClassNotFoundException:org.apache.xmlbeans.XmlOptions。
- 具体代码
在Eclipse中创建一个java project，将上面列出来的jar包都加入到工程的classpath中，否则引用不到jar包会报错。
直接上代码（代码基本框架来自Apache POI官方网站，自行调整部分）：
创建excel文件并写入内容：
public static void createWorkbook() throws IOException { Workbook wb = new HSSFWorkbook(); String safeName1 = WorkbookUtil.createSafeSheetName("[O'sheet1]"); Sheet sheet1 = wb.createSheet(safeName1); CreationHelper createHelper = wb.getCreationHelper(); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet1.createRow((short) 0); // Create a cell and put a value in it. Cell cell = row.createCell(0); cell.setCellValue(1234); // Or do it on one line. row.createCell(2).setCellValue( createHelper.createRichTextString("This is a string")); row.createCell(3).setCellValue(true); // we style the second cell as a date (and time). It is important to // create a new cell style from the workbook otherwise you can end up // modifying the built in style and effecting not only this cell but // other cells. CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat( "m/d/yy h:mm")); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); // you can also set date as java.util.Calendar CellStyle cellStyle1 = wb.createCellStyle(); cellStyle1.setDataFormat(createHelper.createDataFormat().getFormat( "yyyyMMdd HH:mm:ss")); cellStyle1.setBorderBottom(CellStyle.BORDER_THIN); cellStyle1.setBottomBorderColor(IndexedColors.BLACK.getIndex()); cellStyle1.setBorderLeft(CellStyle.BORDER_THIN); cellStyle1.setLeftBorderColor(IndexedColors.GREEN.getIndex()); cellStyle1.setBorderRight(CellStyle.BORDER_THIN); cellStyle1.setRightBorderColor(IndexedColors.BLUE.getIndex()); cellStyle1.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED); cellStyle1.setTopBorderColor(IndexedColors.BLACK.getIndex()); cell = row.createCell(4); cell.setCellValue(Calendar.getInstance()); cell.setCellStyle(cellStyle1); FileOutputStream fileOut = new FileOutputStream("e:/test/workbook.xls"); wb.write(fileOut); fileOut.close(); }
读取excel文件的内容：
public static void readExcel() throws InvalidFormatException, IOException { // Use a file Workbook wb1 = WorkbookFactory.create(new File("e:/test/userinfo.xls")); Sheet sheet = wb1.getSheetAt(0); // Decide which rows to process // int rowStart = Math.min(10, sheet.getFirstRowNum()); // int rowEnd = Math.max(40, sheet.getLastRowNum()); int rowStart = sheet.getLastRowNum(); int rowEnd = sheet.getLastRowNum() + 1; logger.info(sheet.getFirstRowNum()); logger.info(sheet.getLastRowNum()); for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) { Row r = sheet.getRow(rowNum); int lastColumn = Math.max(r.getLastCellNum(), 10); logger.info(lastColumn); // To get the contents of a cell, you first need to know what kind // of cell it is (asking a string cell for its numeric contents will // get you a NumberFormatException for example). So, you will want // to switch on the cell's type, and then call the appropriate // getter for that cell. for (int cn = 0; cn < lastColumn; cn++) { // Cell cell = r.getCell(cn, Row.RETURN_BLANK_AS_NULL); Cell cell = r.getCell(cn); switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: logger.info(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { logger.info(cell.getDateCellValue()); } else { logger.info(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: logger.info(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: logger.info(cell.getCellFormula()); break; default: logger.info("empty"); } } } }
下面给出一个具体的例子，实例中的excel文件内容如下：
我们的程序要做的事情是：根据第一行标题的顺序来读取每一行文件的内容，实际标题和内容的顺序是不确定的，但是我们要求按照给定的顺序输出文件内容。
代码如下：
public static void readUserInfo() throws InvalidFormatException, IOException { String[] titles = { "收费编号", "收费性质", "姓名", "家庭住址", "工作单位", "电话", "手机", "小区楼号", "单元号", "楼层", "房间号", "建筑面积（㎡）", "面积依据", "A面积", "A超", "A轻体", "B面积", "B超", "B轻体", "用户编号", "所属楼前表表号" }; //用来存储标题和顺序的map，key为标题，value为顺序号 Map<String, Integer> titleMap = new HashMap<String, Integer>(); //将既定顺序写入map for (int i=0 ; i<titles.length; i++) { titleMap.put(titles[i], i); } Workbook wb = WorkbookFactory.create(new File("e:/test/userinfo.xls")); for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) { Sheet xSheet = wb.getSheetAt(numSheet); if (xSheet == null) { continue; } // 获取第一行的标题内容 Row tRow = xSheet.getRow(0); //存储标题顺序的数组 Integer[] titleSort = new Integer[tRow.getLastCellNum()]; //循环标题 for (int titleNum = 0; titleNum < tRow.getLastCellNum(); titleNum++) { Cell tCell = tRow.getCell(titleNum); String title = ""; if (tCell == null || "".equals(tCell)) { } else if (tCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {// 布尔类型处理 // logger.info(xCell.getBooleanCellValue()); } else if (tCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {// 数值类型处理 title = doubleToString(tCell.getNumericCellValue()); } else {// 其他类型处理 title = tCell.getStringCellValue(); } //通过获取的标题，从map中读取顺讯号，写入保存标题顺序号的数组 Integer ts = titleMap.get(title); if (ts != null) { titleSort[titleNum] = ts; } } // 循环行Row for (int rowNum = 1; rowNum < xSheet.getLastRowNum() + 1; rowNum++) { Row xRow = xSheet.getRow(rowNum); if (xRow == null) { continue; } // 循环列Cell String[] v = new String[titleSort.length]; for (int cellNum = 0; cellNum < titleSort.length; cellNum++) { Cell xCell = xRow.getCell(cellNum); String value = ""; if (xCell == null || "".equals(xCell)) { } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {// 布尔类型处理 logger.info(xCell.getBooleanCellValue()); } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {// 数值类型处理 value = doubleToString(xCell.getNumericCellValue()); } else {// 其他类型处理 value = xCell.getStringCellValue(); } //按照标题顺序的编号来存储每一行记录 v[titleSort[cellNum]] = value; // logger.info("v[" + titleSort[cellNum] + "] = " + v[titleSort[cellNum]]); } //循环结果数组，获取的与既定顺序相同 for (int i = 0; i < v.length; i++) { logger.info(v[i]); } } } }
上段程序中用到的工具类doubleToString（将excel中的double类型转为String类型，处理了科学计数法形式的数）：
private static String doubleToString(double d) { String str = Double.valueOf(d).toString(); // System.out.println(str); String result = ""; if (str.indexOf("E") > 2) { int index = str.indexOf("E"); int power = Integer.parseInt(str.substring(index + 1)); BigDecimal value = new BigDecimal(str.substring(0, index)); value = value.movePointRight(power); result = value.toString(); } else { if (str.indexOf(".0") > 0) result = str.substring(0, str.indexOf(".0")); else result = str; } return result; }
目前对于POI的应用只限于此，并没有再深入，以后写了新的相关内容会继续补充，请大大们批评指正！
