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="https://www.cnblogs.com/wuyongyin/p/+ 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 = https://www.cnblogs.com/wuyongyin/p/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.demo.general.excel.event; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.*; import java.net.URL; 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資料檔案 */ public class Excel2007Writer { private static final String TEMPLATE_FILE = "excel/2007_event_write_template.xlsx"; /**要生成的excel檔案名稱*/ private String fileName; private OutputStream os; /**臨時的xml檔案*/ private File xmlFile; private Writer xmlWriter; private int row = 0; /**excel模板檔案*/ private File templateFile; /**excel模板檔案是否為臨時檔案*/ private boolean isTemplateFileTmp = false; /**作業表的xml檔案名稱 例如:/xl/worksheets/sheet1.xml*/ private String sheetXmlName = ""; public Excel2007Writer(String fileName) throws Exception { this.fileName = fileName; init(); } public Excel2007Writer(OutputStream os) throws Exception { this.os = os; init(); } private void init() throws Exception { xmlFile = File.createTempFile("sheet", ".xml"); xmlWriter = new OutputStreamWriter(new FileOutputStream(xmlFile, true),"UTF-8"); Excel2007WriterUtil.beginSheet(xmlWriter); XSSFSheet sheet; URL url = Excel2007Writer.class.getClassLoader().getResource(TEMPLATE_FILE); if (url != null) { templateFile = new File(url.getFile()); XSSFWorkbook wb = new XSSFWorkbook(templateFile); sheet = wb.getSheetAt(0); //如果模板檔案不存在，則新建臨時的模板檔案 } else { XSSFWorkbook wb = new XSSFWorkbook(); sheet = wb.createSheet(); isTemplateFileTmp = true; templateFile = File.createTempFile("template", ".xlsx"); 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(); if (os == null) { os = new FileOutputStream(fileName); } 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(); if (isTemplateFileTmp) { //洗掉臨時的模板檔案 templateFile.delete(); } //洗掉臨時的xml檔案 xmlFile.delete(); } public static void main(String[] args) throws Exception { Excel2007Writer excel2007Writer = new Excel2007Writer(new FileOutputStream("d:/a.xlsx")); 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.net.URL; 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 final String TEMPLATE_FILE = "excel/2007_event_write_template.xlsx"; /**最大寫入資料行數，防止死回圈*/ private static final int MAX_LINE = 10000000; private static Logger logger = LoggerFactory.getLogger(Excel2007Reader.class); /** * 生成excel檔案，所有的資料都寫到第一個sheet中 * @param fileName 檔案全路徑名稱 * @param data 資料提供者，不停的呼叫data.get方法來獲取一行資料，直到獲取的值為null * 一定要在某個條件下回傳null，否則會造成死回圈 * @throws Exception */ public static void generateExcel(String fileName, Supplier<List<Object>> data) throws Exception { OutputStream os = new FileOutputStream(fileName); generateExcel(os, data); os.close(); } /** * 生成excel檔案，所有的資料都寫到第一個sheet中 * @param os 輸出流 * @param data 資料提供者，不停的呼叫data.get方法來獲取一行資料，直到獲取的值為null * 一定要在某個條件下回傳null，否則會造成死回圈 * @throws Exception */ public static void generateExcel(OutputStream os, Supplier<List<Object>> data) throws Exception { File templateFile; //模板是否為臨時檔案 boolean isTemplateFileTmp = false; XSSFSheet sheet; URL url = Excel2007WriterStatic.class.getClassLoader().getResource(TEMPLATE_FILE); if (url != null) { templateFile = new File(url.getFile()); XSSFWorkbook wb = new XSSFWorkbook(templateFile); sheet = wb.getSheetAt(0); //如果模板檔案不存在，則新建臨時的模板檔案 } else { XSSFWorkbook wb = new XSSFWorkbook(); sheet = wb.createSheet(); isTemplateFileTmp = true; templateFile = File.createTempFile("template", ".xlsx"); 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 =https://www.cnblogs.com/wuyongyin/p/ data.get(); if (rowData =https://www.cnblogs.com/wuyongyin/p/= 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(); if (isTemplateFileTmp) { //洗掉臨時的模板檔案 templateFile.delete(); } //洗掉臨時的xml檔案 xmlFile.delete(); } public static void main(String[] args) throws Exception { generateExcel("d:/a2.xlsx", 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 = https://www.cnblogs.com/wuyongyin/p/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 =https://www.cnblogs.com/wuyongyin/p/ 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 =https://www.cnblogs.com/wuyongyin/p/= 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"); } }
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/280602.html
標籤：Java