Poi方式解析excel_JAVA_编程开发_程序员俱乐部

最近项目需要实现几个excel导入导出的功能、最开始用的jxl、不过jxl不能解析07以上的文档、感觉不是很方便、感觉apache官方的API对微软的文档支持还不错、便试着用了一下、比jxl稍微复杂了一点点、不过好在功能比较齐全、适用性比较强、

这里只用Poi解析和创建了excel文档、像word、ppt之类的后面研究一下、

依赖包：

poi-3.7-20101029.jar

poi-ooxml-3.7-20101029.jar?

poi-ooxml-schemas-3.7-20101029.jar?

poi-scratchpad-3.7-20101029.jar?

xmlbeans-2.3.0.jar?

geronimo-stax-api_1.0_spec-1.0.jar?

dom4j-1.6.1.jar

后面几个包不能缺、不然解析07的会有异常

1. 解析excel

   class="java" name="code">/**
   	 * 解析excel文件 03/07
   	 * 
   	 * @param file
   	 * @return
   	 * @throws Exception
   	 */
   	public static List<Map<String, Object>> import2Excel(File file)
   			throws Exception {
   		// 数据头map
   		Map<String, String> headerMap = new HashMap<String, String>();
   		List<Map<String, Object>> returnList = new ArrayList<Map<String, Object>>();
   		// 工作簿
   		Workbook wb = null;
   		FileInputStream is = null;
   
   		is = new FileInputStream(file);
   		wb = WorkbookFactory.create(is);
   		// 读取行和列
   		Sheet sheet = wb.getSheetAt(0);
   		int row = sheet.getPhysicalNumberOfRows();
   		// 读取头
   		Row header = sheet.getRow(0);
   		// 列数
   		int column = header.getPhysicalNumberOfCells();
   		for (int i = 0; i < column; i++) {// 读取头
   			Cell tempCell = header.getCell(i);
   			String content = tempCell.getStringCellValue();
   			headerMap.put(String.valueOf(i), content);
   		}
   		// 读取记录
   		for (int i = 1; i < row; i++) {
   			Map<String, Object> tempMap = new HashMap<String, Object>();
   			Row tempRow = sheet.getRow(i);
   			for (int j = 0, length = tempRow.getPhysicalNumberOfCells(); j < length; j++) {
   				Cell tempCell = tempRow.getCell(j);
   				Object content = new Object();
   				switch (tempCell.getCellType()) {
   				case Cell.CELL_TYPE_FORMULA:
   					content = tempCell.getCellFormula();
   					break;
   				case Cell.CELL_TYPE_NUMERIC:
   					if (HSSFDateUtil.isCellDateFormatted(tempCell)) {
   						content = tempCell.getDateCellValue();
   					} else {
   						content = tempCell.getNumericCellValue();
   					}
   					break;
   				case Cell.CELL_TYPE_STRING:
   					content = tempCell.getStringCellValue();
   					break;
   				case Cell.CELL_TYPE_BOOLEAN:
   					content = tempCell.getBooleanCellValue();
   					break;
   				default:
   					content = "";
   				}
   				tempMap.put(headerMap.get(String.valueOf(j)), content);
   			}
   			returnList.add(tempMap);
   		}
   
   		return returnList;
   	}

   ?
2. 创建excel

   /**
   	 * 创建07工作簿
   	 * 
   	 * @param title
   	 * @param dataSet
   	 * @param sheetname
   	 * @return
   	 * @throws Exception
   	 */
   	public static Workbook export2Excel07(Map<String, String> title,
   			List<Map<String, Object>> dataSet, String sheetname)
   			throws Exception {
   		// 创建工作簿
   		Workbook wb = new XSSFWorkbook();
   		Sheet sheet = wb.createSheet(sheetname);
   		// 输入头
   		Row headerRow = sheet.createRow(0);
   		int columnCount = 0;
   		Map<String, String> covertMap = new HashMap<String, String>();
   		for (Map.Entry<String, String> entry : title.entrySet()) {
   			String value = entry.getValue();
   			covertMap.put(String.valueOf(columnCount), entry.getKey());
   			headerRow.createCell(columnCount).setCellValue(value);
   			columnCount++;
   		}
   		// 输入记录
   		for (int i = 0; i < dataSet.size(); i++) {
   			Row tempRow = sheet.createRow(i + 1);
   			for (int j = 0; j < columnCount; j++) {
   				tempRow.createCell(j).setCellValue(
   						""
   								+ dataSet.get(i).get(
   										covertMap.get(String.valueOf(j))));
   			}
   		}
   		return wb;
   	}

   ?创建03的差不多、接口都一样，实现类不同一点、、、

   /**
   	 * 创建03工作簿
   	 * 
   	 * @param title
   	 * @param dataSet
   	 * @param sheetname
   	 * @return
   	 * @throws Exception
   	 */
   	public static HSSFWorkbook export2Excel03(Map<String, String> title,
   			List<Map<String, Object>> dataSet, String sheetname)
   			throws Exception {
   		// 创建工作簿
   		HSSFWorkbook wb = new HSSFWorkbook();
   		HSSFSheet sheet = wb.createSheet(sheetname);
   		// 输入头
   		Row headerRow = sheet.createRow(0);
   		int columnCount = 0;
   		Map<String, String> covertMap = new HashMap<String, String>();
   		for (Map.Entry<String, String> entry : title.entrySet()) {
   			String value = entry.getValue();
   			covertMap.put(String.valueOf(columnCount), entry.getKey());
   			headerRow.createCell(columnCount).setCellValue(value);
   			columnCount++;
   		}
   		// 输入记录
   		for (int i = 0; i < dataSet.size(); i++) {
   			Row tempRow = sheet.createRow(i + 1);
   			for (int j = 0; j < columnCount; j++) {
   				tempRow.createCell(j).setCellValue(
   						""
   								+ dataSet.get(i).get(
   										covertMap.get(String.valueOf(j))));
   			}
   		}
   		return wb;
   	}

   ?设置格式什么的就没有弄了、大概看看文档设置起来也容易、、