POI解析1997-2003、2007版本excel

本文主要是介绍POI解析1997-2003、2007版本excel，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

本文就poi解析1997-2003/2007版本进行总结，走统一接口。

maven引入：

sheet存储模型

import java.util.HashMap;
import java.util.Map;/*** excel sheet 模型抽象* Created by xieyang@e6yun.com on 2016年11月2日.**/
public class SheetCollection {/***sheet 名称 */private String sheetName;/*** key :行号* value: [key:名称;value:单元格的值]*/private Map<String, Map<String, String>> sheetMapCollection=new HashMap<String, Map<String,String>>();/*** sheet 集合中新增一行* Created by xieyang@e6yun.com on 2016年11月2日.* @param rowId 行号* @param rowValues 行中单元格集合*/public void insertRow(String rowId,Map<String, String> rowValues){sheetMapCollection.put(rowId, rowValues);}public String getSheetName() {return sheetName;}public void setSheetName(String sheetName) {this.sheetName = sheetName;}public Map<String, Map<String, String>> getSheetMapCollection() {return sheetMapCollection;}public void setSheetMapCollection(Map<String, Map<String, String>> sheetMapCollection) {this.sheetMapCollection = sheetMapCollection;}@Overridepublic String toString() {return "SheetCollection [sheetName=" + sheetName+ ", sheetMapCollection=" + sheetMapCollection + "]";}
}

excel存储模型，多个sheet集合

import java.util.ArrayList;
import java.util.List;/*** excel sheet集合* Created by xieyang@e6yun.com on 2016年11月2日.**/
public class ExcelCollection {/*** excel 描述*/private String excelDesc;/*** 多个sheet集合*/private  List<SheetCollection> sheetCollections=new ArrayList<SheetCollection>();/*** 添加一个sheet数据集合* Created by xieyang@e6yun.com on 2016年11月2日.* @param sheetCollection*/public void insertSheet(SheetCollection sheetCollection){sheetCollections.add(sheetCollection);}public String getExcelDesc() {return excelDesc;}public void setExcelDesc(String excelDesc) {this.excelDesc = excelDesc;}public List<SheetCollection> getSheetCollections() {return sheetCollections;}public void setSheetCollections(List<SheetCollection> sheetCollections) {this.sheetCollections = sheetCollections;}@Overridepublic String toString() {return "ExcelCollection [excelDesc=" + excelDesc+ ", sheetCollections=" + sheetCollections + "]";}
}

excel 解析助手

import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.e6yun.routeOptimize.common.Constants;
import com.e6yun.routeOptimize.utils.excel.model.ExcelCollection;
import com.e6yun.routeOptimize.utils.excel.model.SheetCollection;/*** excel助手* excel 03,07版本解析与写入* Created by xieyang@e6yun.com on 2016年11月2日.**/
public class ExcelUtils {private static Logger logger=Logger.getLogger(ExcelUtils.class);/*** 解析excel* Created by xieyang@e6yun.com on 2016年11月2日.* @param fileName* @return*/public static ExcelCollection parseExcel(String fileName){ExcelCollection excelCollection=null;try {//excel Poi顶层抽象Workbook workbook=null;if(fileName.endsWith(Constants.EXCEL_03_SURFIX)){workbook=new HSSFWorkbook(new FileInputStream(fileName));}else if(fileName.endsWith(Constants.EXCEL_07_SURFIX)){workbook=new XSSFWorkbook(fileName);}else{return excelCollection;}//解析excelexcelCollection=getExcelFromWorkBook(workbook);//关闭excel文件释放资源workbook.close();} catch (IOException e) {logger.error(String.format("Parse Excel [%s] fail!,The Reason Is [%s]", fileName,e));}return excelCollection;}/*** 从WorkBook中获取数据* Created by xieyang@e6yun.com on 2016年11月2日.* @param workbook* @return*/private static ExcelCollection getExcelFromWorkBook(Workbook workbook){ExcelCollection excelCollection=new ExcelCollection();//获取sheet数量int sheetNums=workbook.getNumberOfSheets();//遍历sheetfor (int i=0;i<sheetNums;i++) {Sheet sheet=workbook.getSheetAt(i);SheetCollection sheetCollection=new SheetCollection();//获取sheet名称String sheetName=sheet.getSheetName();sheetCollection.setSheetName(sheetName);//sheet中总行数int rowNums=sheet.getLastRowNum();Map<String, String> tempTitleMap=new HashMap<String, String>();for (int rowIndex = 0; rowIndex < rowNums; rowIndex++) {Map<String, String> rowMap=new HashMap<String, String>();Row row=sheet.getRow(rowIndex);//获取每行的列数short columnNums=row.getLastCellNum();for (int columnIndex = 0; columnIndex < columnNums; columnIndex++) {Cell cell=row.getCell(columnIndex);if(cell!=null){cell.setCellType(HSSFCell.CELL_TYPE_STRING);if(rowIndex==0){//加入到临时表头中tempTitleMap.put(columnIndex+"", cell.getStringCellValue());}rowMap.put(tempTitleMap.get(columnIndex+""), cell.getStringCellValue());}}sheetCollection.insertRow(rowIndex+"", rowMap);}excelCollection.insertSheet(sheetCollection);}return excelCollection;}}