excel通用解析（xlsx+xls）demo

package zxy.test.poi; import java.io.File; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; 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.ss.usermodel.WorkbookFactory; import org.dom4j.Document; import org.dom4j.Element; import org.dom4j.io.SAXReader; import com.alibaba.fastjson.JSON; /** * @author xinyzhang * * @createTime 2018年9月28日-下午5:42:30 * * @tag */ public class ExcelReader { private static String ELEMENT_PROCESS = "process"; private static String ELEMENT_NAME = "name"; private static String ELEMENT_DATA = "data"; private static String FILE_BASE_PATH = "/fmt/"; private static String IGNONE_START_LINE = "ignone_start_line"; private static String IGNONE_END_LINE = "ignone_end_line"; public static <T> List<T> readExcelToInstance(String filePath, String modelFile, String elementName, String sheetName, Class<T> c) throws Throwable { List<T> ts = new ArrayList<T>(); List<Map<String, Object>> maps = readExcel(filePath, modelFile, elementName, sheetName); ts = (List<T>) JSON.parseArray(JSON.toJSONString(maps), c); return ts; } public static List<Map<String, Object>> readExcel(String filePath, String modelFile, String elementName, String sheetName) throws Throwable { File excelFile = validate(filePath, modelFile, elementName); Element opinionElement = getElementConfig(modelFile, elementName); // 自动适配xls,或者xlsx Workbook workbook = WorkbookFactory.create(excelFile); Sheet sheet = null; if (sheetName != null) { sheet = workbook.getSheet(sheetName); } else { sheet = workbook.getSheetAt(0); } List<Map<String, Object>> maps = getBodyData(opinionElement, sheet); return maps; } private static File validate(String filePath, String element, String exeConfig) throws Throwable { if (filePath == null || filePath.isEmpty() || element == null || element.isEmpty() || exeConfig == null || exeConfig.isEmpty()) { throw new Throwable("入参不能为空"); } if (!(filePath.endsWith(".xls") || filePath.endsWith(".xlsx"))) { throw new Throwable("待解析文件格式，不为xls或者xlsx格式！"); } File file = new File(filePath); if (!file.exists()) { throw new Throwable("未找到待解析文件：" + filePath); } return file; } @SuppressWarnings("unchecked") private static Element getElementConfig(String modelFile, String elementName) throws Throwable { // 获取模板文件，并找到模板文件中适用于本次解析的元素树 InputStream inputStream = ExcelReader.class.getResourceAsStream(FILE_BASE_PATH + modelFile); if (inputStream == null) { throw new Throwable("未找到模板文件：" + modelFile); } SAXReader reader = new SAXReader(); Document document = reader.read(inputStream); Element rootElement = document.getRootElement(); Element processElement = null; for (Element single : (List<Element>) rootElement.elements()) { if (single.getName().equals(ELEMENT_PROCESS) && single.attributeValue(ELEMENT_NAME).equals(elementName)) { processElement = single; break; } } if (processElement == null) { throw new Throwable(" 未找模板文件中的节点名：" + elementName); } return processElement; } private static List<Map<String, Object>> getBodyData(Element processElement, Sheet sheet) throws Throwable { // 获取到元素树后，获取规定的忽略开头行数，和忽略结尾行数，已经对应的解析字段名 int ignoneStartLine = Integer.parseInt(processElement.attributeValue(IGNONE_START_LINE)); int ignoneEndLine = Integer.parseInt(processElement.attributeValue(IGNONE_END_LINE)); List<Map<String, Object>> maps = new ArrayList<Map<String, Object>>(); // 获取解析的字段和顺序 List<ParseSort> parseSorts = covertParseSort(processElement); // 解析数据 int rows = sheet.getPhysicalNumberOfRows(); if (rows < ignoneStartLine + 1) { // 如果不想要这个异常，可以在外面catch住throwble，判断异常语句是否等于这个，如果等于，在转为所需其他的回参：如resp.message=文件无有效数据 throw new Throwable("该待解析文件总行数,小于忽略文件行数,请检查文件是否为空，或文件无有效数据"); } for (int i = ignoneStartLine; i <= rows - ignoneEndLine; i++) { Row row = sheet.getRow(i); if (row == null) continue; // cell的个数应该和解析的配置字段数一样，超过的也不应该进行解析 maps.add(setMap(parseSorts, row, parseSorts.size())); } return maps; } private static Map<String, Object> setMap(List<ParseSort> parseSorts, Row row, int cellCount) { Map<String, Object> map = new HashMap<String, Object>(); for (int i = 0; i < cellCount; i++) { Cell cell = null; if (parseSorts.get(i).index == -1) { cell = row.getCell(i); map.put(parseSorts.get(i).name, getCellValue(cell)); System.out.println( "第" + row.getRowNum() + "行,字段名：" + parseSorts.get(i).name + ",解析结果：" + getCellValue(cell)); } else { cell = row.getCell(parseSorts.get(i).index); map.put(parseSorts.get(i).name, getCellValue(cell)); } } return map; } @SuppressWarnings("unchecked") private static List<ParseSort> covertParseSort(Element element) { List<ParseSort> list = new ArrayList<ExcelReader.ParseSort>(); List<Element> datas = element.elements(ELEMENT_DATA); for (Element data : datas) { for (Element item : (List<Element>) data.elements()) { int index = -1; if (item.attributeValue("index") != null && item.attributeValue("index") != "") { index = Integer.parseInt(item.attributeValue("index").trim()); } list.add(new ParseSort(item.attributeValue(ELEMENT_NAME).trim(), index)); } } return list; } /** * 取得单元格的值 * * @param cell * @return String */ private static String getCellValue(Cell cell) { String cellValue = ""; if (null != cell) { // 以下是判断数据的类型 switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: // 数字 cellValue = cell.getNumericCellValue() + ""; break; case HSSFCell.CELL_TYPE_STRING: // 字符串 cellValue = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean cellValue = cell.getBooleanCellValue() + ""; break; case HSSFCell.CELL_TYPE_FORMULA: // 公式 cellValue = cell.getCellFormula() + ""; break; case HSSFCell.CELL_TYPE_BLANK: // 空值 cellValue = ""; break; case HSSFCell.CELL_TYPE_ERROR: // 故障 cellValue = "非法字符"; break; default: cellValue = "未知类型"; break; } } return cellValue; } private static class ParseSort { String name; int index; /** * @param name * @param index */ public ParseSort(String name, int index) { super(); this.name = name; this.index = index; } } }