excel文件读取返回map（xls，xlsx）
1.xls
package com.cmos.ngoccontrol.util; import java.io.FileInputStream; import java.io.IOException; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.DateUtil; 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 com.cmos.core.logger.Logger; import com.cmos.core.logger.LoggerFactory; public class XlsReaderUtil { //日志 private static final Logger LOGGER = LoggerFactory.getActionLog(XlsReaderUtil.class); //用来标识已经中断解析 private static boolean terminateParseFlag = false; /*//源表头 private List<String> title = new ArrayList<>(); //源表数据 private List<Map<String, Object>> result = new ArrayList<>(); //源文件中缺少字段信息的无效数据信息记录 private List<String> errorList = new ArrayList<String>();*/ /** 获取单元格的值 * * @param cell * @return */ private static String getCellValue(Cell cell) { String cellValue = ""; DataFormatter formatter = new DataFormatter(); if (cell != null) { // 判断单元格数据的类型，不同类型调用不同的方法 switch (cell.getCellType()) { // 数值类型 case Cell.CELL_TYPE_NUMERIC: // 进一步判断 ，单元格格式是日期格式 if (DateUtil.isCellDateFormatted(cell)) { cellValue = formatter.formatCellValue(cell); } else { // 数值(手机号) double value = cell.getNumericCellValue(); DecimalFormat df = new DecimalFormat("#"); cellValue = df.format(value); } break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; // 判断单元格是公式格式，需要做一种特殊处理来得到相应的值 case Cell.CELL_TYPE_FORMULA: { try { cellValue = String.valueOf(cell.getNumericCellValue()); } catch (IllegalStateException e) { cellValue = String.valueOf(cell.getRichStringCellValue()); LOGGER.error("getCellValue() error",e); } } break; case Cell.CELL_TYPE_BLANK: cellValue = ""; break; case Cell.CELL_TYPE_ERROR: cellValue = ""; break; default: cellValue = cell.toString().trim(); break; } } return cellValue.trim(); } /** * * @param filePath 临时文件路径 * @param sourceFields 源文件字段 * @param targetFields 目标文件字段 * @param errorList 错误数据（例如：第几行数据错误，错误原因：字段长度与文件标题长度不一致） * @param dispose 处理后前台需要展示的数据（文件总行数，实际行数（正确数据行数），错误行数） * @return * @throws IOException */ public static Map<String, Object> readExcel(String path, String splitStr, int controlParseNum) { //源表头 List<String> title = new ArrayList<String>(); //源表数据 List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); //源文件中缺少字段信息的无效数据信息记录 List<String> errorList = new ArrayList<String>(); Map<String, Object> mapR = new HashMap<String, Object>(); FileInputStream inStream = null; Workbook workBook = null; Sheet sheet = null; int rowNum = 0; try { inStream = new FileInputStream(path); workBook = WorkbookFactory.create(inStream); sheet = workBook.getSheetAt(0); // 获取总行数 rowNum = sheet.getLastRowNum() + 1; for (int i = 0; i < rowNum; i++) { if(!terminateParseFlag && controlParseNum != -1){ Row row = sheet.getRow(i); if (i == 0) { for (int j = 0; j < row.getLastCellNum(); j++) { // 读取第一行 存入标题 Cell cell = row.getCell(j); // 获取单元格的值 String str = getCellValue(cell); title.add(str); } } else { Map<String, Object> beanRow=new HashMap<String, Object>(); for(int k = 0; k <title.size();k++){ // 读取数据行 Cell cell = row.getCell(k); // 获取单元格的值 String str = getCellValue(cell); beanRow.put(title.get(k), "".equals(str) ? " " : str); } result.add(beanRow); } if(i == controlParseNum){ terminateParseFlag = true; /* throw new IOException();*/ break; } } } } catch (Exception e) { LOGGER.error("readExcelWithTitle() error",e); } finally { if (inStream != null) { try { inStream.close(); } catch (IOException e) { LOGGER.error("FileInputStream close() error",e); } } } mapR.put("title", title); mapR.put("result", result); mapR.put("total", rowNum); mapR.put("errorList", errorList); return mapR; } /*public static void main(String[] args) throws IOException { Map<String, Object> map = XlsReaderUtil.readExcel("D://test.xls", ",", 2); System.out.println(map); }*/ }
2.xlsx
package com.cmos.ngoccontrol.util; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.openxml4j.exceptions.OpenXML4JException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.xml.sax.Attributes; import org.xml.sax.InputSource; import org.xml.sax.SAXException; import org.xml.sax.XMLReader; import org.xml.sax.helpers.DefaultHandler; import org.xml.sax.helpers.XMLReaderFactory; import com.cmos.core.logger.Logger; import com.cmos.core.logger.LoggerFactory; public class XlsxReaderUtil extends DefaultHandler { //日志 private static final Logger LOGGER = LoggerFactory.getActionLog(XlsxReaderUtil.class); // 共享字符串表 private SharedStringsTable sst; // 上一次的内容 private String lastContents; private boolean nextIsString; private boolean cellNull; private int sheetIndex = -1; private List<String> rowlist = new ArrayList<String>(); // 当前行 private int curRow = 0; // 当前列 private int curCol = 0; private int totalRow = 0; //用来控制解析多少行 private int controlParseNum = 0; //用来标识已经中断解析 private boolean terminateParseFlag = false; //源表头 private List<String> title = new ArrayList<String>(); //源表数据 private List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); //源文件中缺少字段信息的无效数据信息记录 private List<String> errorList = new ArrayList<String>(); private String path; public XlsxReaderUtil() { super(); } public XlsxReaderUtil(String path, int controlParseNum) { super(); this.path = path; this.controlParseNum = controlParseNum; } /** * 只遍历一个电子表格，其中sheetId为要遍历的sheet索引，从1开始，1-3 * * @param filename * @param sheetId * @throws Exception */ /*public void processOneSheet(String filename, int sheetId) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader r = new XSSFReader(pkg); SharedStringsTable sst = r.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); // 根据 rId# 或 rSheet# 查找sheet InputStream sheet2 = r.getSheet("rId" + sheetId); sheetIndex++; InputSource sheetSource = new InputSource(sheet2); parser.parse(sheetSource); sheet2.close(); } */ /** * 遍历工作簿中所有的电子表格 * * @param filename * @throws Exception */ public Map<String, Object> process(){ System.out.println(System.currentTimeMillis()); XMLReader parser = null; Iterator<InputStream> sheets = null; try { OPCPackage pkg = OPCPackage.open(path); XSSFReader r = new XSSFReader(pkg); SharedStringsTable sst = r.getSharedStringsTable(); parser = fetchSheetParser(sst); sheets = r.getSheetsData(); } catch (InvalidFormatException e) { LOGGER.error("process() InvalidFormat error", e); } catch (IOException e) { LOGGER.error("process() IO error", e); } catch (OpenXML4JException e) { LOGGER.error("process() OpenXML4J error", e); } catch (SAXException e) { LOGGER.error("process() SAXException error", e); } if(sheets == null){ return null; } while (sheets.hasNext()) { curRow = 0; sheetIndex++; InputStream sheet = null; sheet = sheets.next(); InputSource sheetSource = new InputSource(sheet); try { parser.parse(sheetSource); } catch (IOException e) { LOGGER.error("process() IO error", e); } catch (SAXException e) { LOGGER.error("process() SAX error", e); } finally { if(sheet != null){ try { sheet.close(); } catch (IOException e) { LOGGER.error("process() InputStream close error", e); } } } } Map<String, Object> mapR = new HashMap<String, Object>(); mapR.put("result", result); mapR.put("title", title); mapR.put("total", totalRow - sheetIndex - 1); mapR.put("errorList", errorList); return mapR; } public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException { XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser"); this.sst = sst; parser.setContentHandler(this); return parser; } public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException { // c => 单元格 if ("c".equals(name)) { // 如果下一个元素是 SST 的索引，则将nextIsString标记为true String cellType = attributes.getValue("t"); if ("s".equals(cellType)) { nextIsString = true; cellNull = false; } else { nextIsString = false; cellNull = true; } } // 置空 lastContents = ""; } public void endElement(String uri, String localName, String name) throws SAXException { // 根据SST的索引值的到单元格的真正要存储的字符串 // 这时characters()方法可能会被调用多次 if (nextIsString) { try { int idx = Integer.parseInt(lastContents); lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString(); } catch (Exception e) { //LOGGER.error("endElement() error", e); } } if ("v".equals(name) || "t".equals(name)) { String value = lastContents.trim(); value = "".equals(value) ? " " : value; rowlist.add(curCol, value); curCol++; cellNull = false; }else if("c".equals(name) && cellNull == true){ rowlist.add(curCol, ""); curCol++; cellNull = false; } else { // 如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法 if ("row".equals(name)) { optRows(sheetIndex, curRow, rowlist); rowlist.clear(); totalRow++; curRow++; curCol = 0; } } } public void optRows(int sheetIndex, int curRow, List<String> rowList){ Map<String, Object> map = new HashMap<>(); if(!terminateParseFlag && controlParseNum != -1){ if(sheetIndex == 0 && curRow ==0){ for (String string : rowlist) { title.add(string); } } else if(curRow != 0){ if(rowlist.size() == title.size()){ for (int i =0; i < rowlist.size(); i++) { map.put(title.get(i), rowlist.get(i)); } result.add(map); } else{ LOGGER.debug("第"+(sheetIndex+1)+"页，第"+(curRow+1)+"行数据与第一行数据长度不匹配！"); } } /*if(totalRow - sheetIndex == controlParseNum){ terminateParseFlag = true; try { throw new SAXException(); } catch (SAXException e) { LOGGER.info("读取前"+controlParseNum+"数据", e); } }*/ } } public void characters(char[] ch, int start, int length) throws SAXException { // 得到单元格内容的值 lastContents += new String(ch, start, length); } /*public static void main(String[] args) { try { // ExcelReaderUtil.readExcel(rowReader, // "E://2016-07-04-011940a.xls"); System.out.println("**********************************************"); //ExcelReaderUtil.readExcel(rowReader, "E://test.xlsx"); XlsxReaderUtil reader = new XlsxReaderUtil("20170731084636595455.xlsx",20); Map<String, Object> process = reader.process(); System.out.println(process); } catch (Exception e) { e.printStackTrace(); } }*/ }
