Java 操作 Excel(3)--POI 事件模式读写Excel
Apache POI 是基于 Office Open XML 标准（OOXML）和 Microsoft 的 OLE 2复合文档格式（OLE2）处理各种文件格式的开源框架。本文主要介绍使用 POI 的事件模式来读写 Excel，POI 的事件模式消耗内存较小但编程复杂，适合大数据量。本文中所使用到的软件版本：jdk1.8.0_181、POI 5.0.0。
1、引入依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.0.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.0.0</version> </dependency> <dependency> <groupId>xerces</groupId> <artifactId>xercesImpl</artifactId> <version>2.12.1</version> </dependency>
2、行数据处理接口
编写行数处理的通用接口，用于读取一行数据后的回调
package com.abc.demo.general.excel.event; import java.util.List; /** * 行数据处理器，每读取一行数据后会回调该接口的handle方法 */ public interface IRowDataHandler { /** * 每行数据处理，在该方法里实现自己的业务逻辑 * @param sheetIndex sheet下标(从0开始) * @param sheetName sheet名称 * @param row 当前行号(从0开始) * @param rowData 当前行数据 */ void handle(int sheetIndex, String sheetName, int row, List<String> rowData); }
简单实现：
package com.abc.demo.general.excel.event; import java.util.List; /** * 简单打印每行数据 */ public class SimpleRowDataHandler implements IRowDataHandler { @Override public void handle(int sheetIndex, String sheetName, int row, List<String> rowData) { System.out.println("sheetIndex=" + sheetIndex + ",sheetName=" + sheetName + ",row=" + row + ",rowData=" + rowData); } }
2、Excel 2003 事件模式读
参考 POI 源码中给出的例子，然后自己改写；源码位置如下：
自己编写例子如下：
package com.abc.demo.general.excel.event; import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener; import org.apache.poi.hssf.eventusermodel.*; import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord; import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord; import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.*; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.FileInputStream; import java.io.IOException; import java.util.ArrayList; import java.util.List; /** * Excel 2003 事件方式读取数据 */ public class Excel2003Reader implements HSSFListener { private static Logger logger = LoggerFactory.getLogger(Excel2003Reader.class); /**最小的列数，不足补空字符串*/ private int minColumns = -1; /**Should we output the formula, or the value it has?*/ private boolean outputFormulaValues = true; /**For parsing Formulas*/ private SheetRecordCollectingListener workbookBuildingListener; //excel2003工作薄 private HSSFWorkbook stubWorkbook; // Records we pick up as we process private SSTRecord sstRecord; private FormatTrackingHSSFListener formatListener; //表索引 private int sheetIndex = -1; private BoundSheetRecord[] orderedBSRs; private String sheetName; private ArrayList boundSheetRecords = new ArrayList(); // For handling formulas with string results private boolean outputNextStringRecord; //行数据 private List<String> rowData = new ArrayList<>(); private IRowDataHandler rowDataHandler; public Excel2003Reader() { } public Excel2003Reader(IRowDataHandler rowDataHandler) { this.rowDataHandler = rowDataHandler; } public Excel2003Reader(IRowDataHandler rowDataHandler, int minColumns) { this.rowDataHandler = rowDataHandler; this.minColumns = minColumns; } public void setRowDataHandler(IRowDataHandler rowDataHandler) { this.rowDataHandler = rowDataHandler; } /** * 解析所有sheet数据 * @param fileName * @throws IOException */ public void process(String fileName) throws IOException { this.init(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fileName)); MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this); formatListener = new FormatTrackingHSSFListener(listener); HSSFEventFactory factory = new HSSFEventFactory(); HSSFRequest request = new HSSFRequest(); if (outputFormulaValues) { request.addListenerForAllRecords(formatListener); } else { workbookBuildingListener = new SheetRecordCollectingListener(formatListener); request.addListenerForAllRecords(workbookBuildingListener); } factory.processWorkbookEvents(request, fs); fs.close(); } private void init() { sheetIndex = -1; sheetName = ""; boundSheetRecords.clear(); workbookBuildingListener = null; stubWorkbook = null; orderedBSRs = null; } @Override public void processRecord(Record record) { String value; switch (record.getSid()) { case BoundSheetRecord.sid: BoundSheetRecord boundSheetRecord = (BoundSheetRecord) record; logger.info("工作表名称: {}", boundSheetRecord.getSheetname()); boundSheetRecords.add(record); break; //工作表或工作簿的开头 case BOFRecord.sid: BOFRecord bofRecord = (BOFRecord) record; if (bofRecord.getType() == BOFRecord.TYPE_WORKSHEET) { //Create sub workbook if required if (workbookBuildingListener != null && stubWorkbook == null) { stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook(); } sheetIndex++; if (orderedBSRs == null) { orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords); } sheetName = orderedBSRs[sheetIndex].getSheetname(); } break; case SSTRecord.sid: sstRecord = (SSTRecord) record; break; case BlankRecord.sid: rowData.add(""); break; //布尔类型 case BoolErrRecord.sid: BoolErrRecord boolErrRecord = (BoolErrRecord) record; value = boolErrRecord.getBooleanValue() + ""; rowData.add(value); break; //公式 case FormulaRecord.sid: FormulaRecord formulaRecord = (FormulaRecord) record; if (outputFormulaValues) { if (Double.isNaN(formulaRecord.getValue())) { //Formula result is a string,This is stored in the next record outputNextStringRecord = true; } else { value = formatListener.formatNumberDateCell(formulaRecord); rowData.add(value); } } else { value = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, formulaRecord.getParsedExpression()) + '"'; rowData.add(value); } break; //公式的字符串 case StringRecord.sid: if (outputNextStringRecord) { //String for formula StringRecord stringRecord = (StringRecord) record; outputNextStringRecord = false; rowData.add(stringRecord.getString()); } break; case LabelRecord.sid: LabelRecord labelRecord = (LabelRecord) record; value = labelRecord.getValue().trim(); rowData.add(value); break; //字符串 case LabelSSTRecord.sid: LabelSSTRecord labelSSTRecord = (LabelSSTRecord) record; value = ""; if (sstRecord != null) { value = sstRecord.getString(labelSSTRecord.getSSTIndex()).toString().trim(); } rowData.add(value); break; //数字 case NumberRecord.sid: NumberRecord numberRecord = (NumberRecord) record; value = formatListener.formatNumberDateCell(numberRecord).trim(); rowData.add(value); break; default: //logger.warn("无效的类型:{}", record.getSid()); break; } // 空值的操作 if (record instanceof MissingCellDummyRecord) { rowData.add(""); } //行结束 if (record instanceof LastCellOfRowDummyRecord) { if (rowData.size() < minColumns) { int size = rowData.size(); for (int i = 0; i < minColumns - size; i++) { rowData.add(""); } } rowDataHandler.handle(sheetIndex, sheetName, ((LastCellOfRowDummyRecord)record).getRow(), rowData); rowData.clear(); } } public static void main(String[] args) throws IOException { Excel2003Reader excel2003Reader = new Excel2003Reader(new SimpleRowDataHandler(), 8); excel2003Reader.process("d:/a.xls"); } }
3、Excel 2007 事件模式读写
Excel 2007 使用 XML 来存储数据，可以把一个 Excel 文件的后缀改为 zip，再用解压软件打开，可以到里面的 XML 文件；我们读写 Excel 只要使用 SAX 方法来处理 Sheet 对应的 XML 文件。
3.1、Excel 2007 事件模式写
先生成一个临时的 XML 文件来保存 Sheet 数据，然后通过 Zip 方式打开一个 Excel 模板文件，把模板 Excel 里除了 Sheet 数据对应的 XML 文件都拷贝到结果 Excel 文件里，最后写入保存了 Sheet 数据的 XML 文件到结果文件里。
3.1.1、辅助类
该类用于写 XML 数据。
package com.abc.demo.general.excel.event; import org.apache.poi.ss.util.CellReference; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.Writer; class Excel2007WriterUtil { private static final String LINE_SEPARATOR = System.getProperty("line.separator"); public static void beginSheet(Writer writer) throws IOException { writer.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">"); writer.write("<sheetData>" + LINE_SEPARATOR); } public static void endSheet(Writer writer) throws IOException { writer.write("</sheetData>"); writer.write("</worksheet>"); } public static void beginRow(Writer writer, int rowNum) throws IOException { writer.write("<row r=\"" + rowNum + "\">" + LINE_SEPARATOR); } public static void endRow(Writer writer) throws IOException { writer.write("</row>" + LINE_SEPARATOR); } /** * 生成单元格节点 * @param writer * @param rowIndex 行索引(从0开始) * @param columnIndex 列索引(从0开始) * @param value * @param styleIndex * @throws IOException */ public static void createCell(Writer writer, int rowIndex, int columnIndex, Object value, int styleIndex) throws IOException { String cellReferenceString = new CellReference(rowIndex, columnIndex).formatAsString(); String t = ""; String valueNode = ""; if (value instanceof Double) { t = "n"; valueNode = "<v>" + value + "</v>"; } else { t = "inlineStr"; valueNode = "<is><t>" + value + "</t></is>"; } writer.write("<c r=\"" + cellReferenceString + "\" t=\"" + t + "\""); if (styleIndex != -1) { writer.write(" s=\"" + styleIndex + "\""); } writer.write(">"); writer.write(valueNode); writer.write("</c>"); } public static void createCell(Writer writer, int rowIndex, int columnIndex, Object value) throws IOException { createCell(writer, rowIndex, columnIndex, value, -1); } public static void copyStream(InputStream is, OutputStream os) throws IOException { byte[] temp = new byte[1024]; int count; while ((count = is.read(temp)) >= 0) { os.write(temp, 0, count); } } }
3.1.2、实现一
package com.abc.common.excel.event; import com.abc.common.util.DateUtil; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.*; import java.util.Arrays; import java.util.Enumeration; import java.util.List; import java.util.zip.ZipEntry; import java.util.zip.ZipFile; import java.util.zip.ZipOutputStream; /** * Excel 2007 事件方式写数据 * 先生成sheet的xml文件，然后根据excel的模板文件覆盖其中的sheet数据文件 * @author wuyy */ public class Excel2007Writer { private OutputStream os; /**生成临时文件的目录*/ private String tempPath; /**临时的xml文件*/ private File xmlFile; private Writer xmlWriter; private int row = 0; /**excel模板文件*/ private File templateFile; /**工作表的xml文件名称 例如:/xl/worksheets/sheet1.xml*/ private String sheetXmlName = ""; public Excel2007Writer(OutputStream os, String tempPath) throws Exception { this.os = os; this.tempPath = tempPath; init(); } private void init() throws Exception { if (!tempPath.endsWith(System.getProperty("file.separator"))) { tempPath += System.getProperty("file.separator"); } xmlFile = new File(tempPath + "sheet_" + DateUtil.getCurrentDateString("yyyyMMddHHmmssSSS") + ".xml"); xmlWriter = new OutputStreamWriter(new FileOutputStream(xmlFile, true),"UTF-8"); Excel2007WriterUtil.beginSheet(xmlWriter); XSSFSheet sheet; String absolutePath = tempPath; if (!absolutePath.endsWith(System.getProperty("file.separator"))) { absolutePath += System.getProperty("file.separator"); } absolutePath += "excel_export_template" + ".xlsx"; templateFile = new File(absolutePath); if (templateFile.exists()) { XSSFWorkbook wb = new XSSFWorkbook(templateFile); sheet = wb.getSheetAt(0); } else { XSSFWorkbook wb = new XSSFWorkbook(); sheet = wb.createSheet(); FileOutputStream fos = new FileOutputStream(templateFile); wb.write(fos); fos.close(); wb.close(); } sheetXmlName = sheet.getPackagePart().getPartName().getName(); } /** * 增加一行数据 * @param values * @throws IOException */ public void addLine(List<Object> values) throws IOException { Excel2007WriterUtil.beginRow(xmlWriter, row + 1); for (int i = 0; i < values.size(); i++) { Object value = values.get(i); Excel2007WriterUtil.createCell(xmlWriter, row, i, value); } Excel2007WriterUtil.endRow(xmlWriter); row++; } /** * 生成excel文件 * @throws Exception */ public void generateExcel() throws Exception { Excel2007WriterUtil.endSheet(xmlWriter); xmlWriter.close(); ZipOutputStream zos = new ZipOutputStream(os); ZipFile templateZipFile = new ZipFile(templateFile); Enumeration<ZipEntry> zipEntrys = (Enumeration<ZipEntry>) templateZipFile.entries(); //先把非sheet数据文件写进去 while (zipEntrys.hasMoreElements()) { ZipEntry zipEntry = zipEntrys.nextElement(); if (!zipEntry.getName().equals(sheetXmlName.substring(1))) { zos.putNextEntry(new ZipEntry(zipEntry.getName())); InputStream is = templateZipFile.getInputStream(zipEntry); Excel2007WriterUtil.copyStream(is, zos); is.close(); } } //写sheet数据文件 zos.putNextEntry(new ZipEntry(sheetXmlName.substring(1))); InputStream is = new FileInputStream(xmlFile); Excel2007WriterUtil.copyStream(is, zos); is.close(); templateZipFile.close(); zos.close(); os.close(); //删除临时的xml文件 xmlFile.delete(); } public static void main(String[] args) throws Exception { Excel2007Writer excel2007Writer = new Excel2007Writer(new FileOutputStream("d:/a.xlsx"), "d:/temp"); for (int i = 0; i < 100; i++) { excel2007Writer.addLine(Arrays.asList("第" + i + "行", "a", "b", "c", "d")); } excel2007Writer.generateExcel(); } }
该方式通过新建 Excel2007Writer，然后不停的增加行，最后生成 Excel 文件。
3.1.3、实现二
package com.abc.demo.general.excel.event; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.*; import java.util.Arrays; import java.util.Enumeration; import java.util.List; import java.util.function.Supplier; import java.util.zip.ZipEntry; import java.util.zip.ZipFile; import java.util.zip.ZipOutputStream; /** * Excel 2007 事件方式写数据 * 先生成sheet的xml文件，然后根据excel的模板文件覆盖其中的sheet数据文件 */ public class Excel2007WriterStatic { private static Logger logger = LoggerFactory.getLogger(Excel2007Reader.class); /**最大写入数据行数，防止死循环*/ private static final int MAX_LINE = 10000000; /** * 生成excel文件，所有的数据都写到第一个sheet中 * @param os 输出流 * @param tempPath 生成临时文件的目录 * @param data 数据提供者，不停的调用data.get方法来获取一行数据，直到获取的值为null * 一定要在某个条件下返回null，否则会造成死循环 * @throws Exception */ public static void generateExcel(OutputStream os, String tempPath, Supplier<List<Object>> data) throws Exception { XSSFSheet sheet; String absolutePath = tempPath; if (!absolutePath.endsWith(System.getProperty("file.separator"))) { absolutePath += System.getProperty("file.separator"); } absolutePath += "excel_export_template" + ".xlsx"; File templateFile = new File(absolutePath); if (templateFile.exists()) { XSSFWorkbook wb = new XSSFWorkbook(templateFile); sheet = wb.getSheetAt(0); } else { XSSFWorkbook wb = new XSSFWorkbook(); sheet = wb.createSheet(); FileOutputStream fos = new FileOutputStream(templateFile); wb.write(fos); fos.close(); wb.close(); } //工作表的xml文件名 例如:/xl/worksheets/sheet1.xml String sheetXmlName = sheet.getPackagePart().getPartName().getName(); File xmlFile = File.createTempFile("sheet", ".xml"); Writer writer = new OutputStreamWriter(new FileOutputStream(xmlFile, true),"UTF-8"); //写入数据到临时xml文件 Excel2007WriterUtil.beginSheet(writer); int row = 0; while (true) { List<Object> rowData = data.get(); if (rowData == null) { break; } if (row >= MAX_LINE) { logger.warn("请确认Supplier的get方法是否在某个条件下返回null"); break; } Excel2007WriterUtil.beginRow(writer, row + 1); for (int i = 0; i < rowData.size(); i++) { Object o = rowData.get(i); Excel2007WriterUtil.createCell(writer, row, i, o); } Excel2007WriterUtil.endRow(writer); row++; } Excel2007WriterUtil.endSheet(writer); writer.close(); ZipOutputStream zos = new ZipOutputStream(os); ZipFile templateZipFile = new ZipFile(templateFile); Enumeration<ZipEntry> zipEntrys = (Enumeration<ZipEntry>) templateZipFile.entries(); //先把非sheet数据文件写进去 while (zipEntrys.hasMoreElements()) { ZipEntry zipEntry = zipEntrys.nextElement(); if (!zipEntry.getName().equals(sheetXmlName.substring(1))) { zos.putNextEntry(new ZipEntry(zipEntry.getName())); InputStream is = templateZipFile.getInputStream(zipEntry); Excel2007WriterUtil.copyStream(is, zos); is.close(); } } //写sheet数据文件 zos.putNextEntry(new ZipEntry(sheetXmlName.substring(1))); InputStream is = new FileInputStream(xmlFile); Excel2007WriterUtil.copyStream(is, zos); is.close(); templateZipFile.close(); zos.close(); //删除临时的xml文件 xmlFile.delete(); } public static void main(String[] args) throws Exception { generateExcel(new FileOutputStream("d:/a2.xlsx"), "d:/temp", new Supplier<List<Object>>() { private int num = 0; @Override public List<Object> get() { if (num >= 100) { return null; } num++; return Arrays.asList("第" + num + "行", "a", "b", "c"); } }); } }
该方式通过静态方法来调用，但需要实现 Supplier 接口来提供数据；通过不断调用 Supplier 的 get 方法来获取数据直到获取的值为 null，所以 Supplier 一定要在某个条件下返回 null，否则会造成死循环。
3.3、Excel 2007 事件模式读
参考 POI 源码中给出的例子，然后自己改写；源码位置如下：
自己编写例子如下：
package com.abc.demo.general.excel.event; import com.abc.demo.general.util.DateUtil; import org.apache.commons.lang3.StringUtils; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.openxml4j.opc.PackageAccess; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.slf4j.Logger; import org.slf4j.LoggerFactory; 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 java.io.InputStream; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.List; /** * Excel 2007 事件方式读取数据 */ public class Excel2007Reader extends DefaultHandler { private static Logger logger = LoggerFactory.getLogger(Excel2007Reader.class); private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss"; private int sheetIndex; private String sheetName; /**最小的列数，不足补空字符串*/ private int minColumns = -1; /**单元格类型*/ private String cellType; /**单元格样式*/ private String cellStyle; /**当前单元格坐标*/ private String currentXy; /**当前单元格行坐标*/ private String currentY; /**前一单元格坐标*/ private String preXy; /**节点值*/ private String text; /**c节点下是否包含子节点*/ private boolean cHasChild; /**行数据*/ private List<String> rowData = new ArrayList<>(); private SharedStringsTable sharedStringsTable; private StylesTable stylesTable; private IRowDataHandler rowDataHandler; public Excel2007Reader() { } public Excel2007Reader(IRowDataHandler rowDataHandler) { this.rowDataHandler = rowDataHandler; } public Excel2007Reader(IRowDataHandler rowDataHandler, int minColumns) { this.rowDataHandler = rowDataHandler; this.minColumns = minColumns; } public void setRowDataHandler(IRowDataHandler rowDataHandler) { this.rowDataHandler = rowDataHandler; } @Override public void startDocument() throws SAXException { super.startDocument(); preXy = ""; } @Override public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException { //单元格 if ("c".equals(qName)) { cHasChild = false; this.cellType = attributes.getValue("t"); this.cellStyle = attributes.getValue("s"); currentXy = attributes.getValue("r"); String currentX = currentXy.replaceAll("\\d", "").trim(); currentY = currentXy.replaceAll("[A-Za-z]", "").trim(); if (StringUtils.isBlank(preXy)) { for (int i = 0; i < colXToNum(currentX); i++) { rowData.add(""); } } else { String preX = preXy.replaceAll("\\d", "").trim(); String preY = preXy.replaceAll("[A-Za-z]", "").trim(); int differ = colXToNum(currentX) - colXToNum(preX); //当前列和前一列之前存在空列 if (differ > 1) { for (int i = 1; i < differ; i++) { rowData.add(""); } } //换行且新行不从A列开始，补充前几列的空值 if (currentY.compareTo(preY) > 0 && !"A".equalsIgnoreCase(currentX)) { for (int i = 0; i < colXToNum(currentX); i++) { rowData.add(""); } } } preXy = currentXy; } else if ("v".equals(qName) || "t".equals(qName)) { cHasChild = true; } text = ""; } @Override public void endElement(String uri, String localName, String qName) throws SAXException { if ("v".equals(qName) || "t".equals(qName)) { rowData.add(getValue()); } else if ("c".equals(qName)) { //c节点补包含子节点 if (!cHasChild) { rowData.add(""); } } else if (qName.equals("row")) { if (rowData.size() < minColumns) { int size = rowData.size(); for (int i = 0; i < minColumns - size; i++) { rowData.add(""); } } rowDataHandler.handle(sheetIndex, sheetName, Integer.parseInt(currentY) - 1, rowData); rowData.clear(); } } private String getValue() { if (StringUtils.isBlank(text)) { return text; } String result = ""; //布尔类型 if ("b".equals(cellType)) { result = text.charAt(0) == '0' ? "false" : "true"; //错误 } else if ("e".equals(cellType)) { result = "error:" + text; //SSTINDEX } else if ("s".equals(cellType)) { int idx = Integer.parseInt(text); result = sharedStringsTable.getItemAt(idx).toString(); //INLINESTR } else if ("inlineStr".equals(cellType)) { result = new XSSFRichTextString(text).toString(); //FORMULA } else if ("str".equals(cellType)) { result = text; //NUMBER } else if ("n".equals(cellType) || StringUtils.isBlank(cellType)) { short dataFormat = -1; String dataFormatString = ""; if (StringUtils.isNotBlank(cellStyle)) { int styleIndex = Integer.parseInt(cellStyle); XSSFCellStyle style = stylesTable.getStyleAt(styleIndex); dataFormat = style.getDataFormat(); dataFormatString = style.getDataFormatString(); } double value = Double.parseDouble(text); if (org.apache.poi.ss.usermodel.DateUtil.isADateFormat(dataFormat, dataFormatString)) { Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value); result = DateUtil.getDateString(date, DEFAULT_DATE_FORMAT); } else { long valueLong = (long)value; if (valueLong - value == 0) { result = String.valueOf(valueLong); } else { result = String.valueOf(value); } } } else { throw new RuntimeException("不支持的单元格类型。currentXy=" + currentXy + ",cellType=" + cellType); } return result; } @Override public void characters(char[] ch, int start, int length) throws SAXException { super.characters(ch, start, length); text += new String(ch, start, length); } /** * Excel列字母转列索引(从0开始) * @param colX 列字母 * @return */ private int colXToNum(String colX) { if (StringUtils.isBlank(colX)) { throw new RuntimeException("列字母不能为空 : [" + colX + "]"); } colX = colX.toUpperCase(); int length = colX.length(); int result = 0; for (int i = 0; i < length; i++) { char ch = colX.charAt(length - i - 1); int num = ch - 'A' + 1; num *= Math.pow(26, i); result += num; } return result - 1; } /** * 解析指定sheet数据 * @param fileName * @param sheetIndexes * @throws Exception */ public void process(String fileName, List<Integer> sheetIndexes) throws Exception { if (rowDataHandler == null) { throw new Exception("请设置行数据处理器"); } OPCPackage opcPackage = OPCPackage.open(fileName, PackageAccess.READ); XSSFReader xssfReader = new XSSFReader(opcPackage); sharedStringsTable = xssfReader.getSharedStringsTable(); stylesTable = xssfReader.getStylesTable(); XMLReader xmlReader = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser"); xmlReader.setContentHandler(this); XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData(); sheetIndex = 0; while (sheets.hasNext()) { if (sheetIndexes != null && !sheetIndexes.contains(sheetIndex)) { continue; } InputStream sheet = sheets.next(); sheetName = sheets.getSheetName(); InputSource sheetSource = new InputSource(sheet); xmlReader.parse(sheetSource); sheet.close(); sheetIndex++; } opcPackage.close(); } /** * 解析所有sheet数据 * @param fileName * @throws Exception */ public void process(String fileName) throws Exception { this.process(fileName, null); } /** * 解析第一个sheet的数据 * @param fileName * @throws Exception */ public void processFirstSheet(String fileName) throws Exception { this.process(fileName, Arrays.asList(0)); } public static void main(String[] args) throws Exception { Excel2007Reader excel2007Handler = new Excel2007Reader(new SimpleRowDataHandler()); excel2007Handler.process("d:/a.xlsx"); } }
