Apache POI组件使用eventusermodel模式读取Excel文档内容
package com.test;import java.io.FileWriter;import java.io.IOException;import java.io.InputStream;import java.util.LinkedList;import java.util.List;import org.apache.commons.lang.StringUtils;import org.apache.poi.openxml4j.opc.OPCPackage;import org.apache.poi.openxml4j.opc.PackageAccess;import org.apache.poi.ss.usermodel.BuiltinFormats;import org.apache.poi.ss.usermodel.DataFormatter;import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;import org.apache.poi.xssf.eventusermodel.XSSFReader;import org.apache.poi.xssf.model.StylesTable;import org.apache.poi.xssf.usermodel.XSSFCellStyle;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;public class Test {public static void main(String[] args) {long a=System.currentTimeMillis();ExecThread thread = new ExecThread();thread.start();try {thread.join();} catch (InterruptedException e) {e.printStackTrace();}System.out.println("Main ... "+Thread.currentThread().getName());System.out.println("执行耗时 :"+(System.currentTimeMillis()-a)/1000f+" 秒 ");}public static class ExecThread extends Thread{public ExecThread(){}/* (non-Javadoc) * @see java.lang.Thread#run() */@Overridepublic void run() {try {FileWriter fileWriter = new FileWriter("D:/test-out.txt");String excelPath = "D:/test.xlsx";new ExcelHandler(fileWriter,excelPath,"1","").start();System.out.println("ExecThread ... "+Thread.currentThread().getName());} catch (IOException e) {e.printStackTrace();}}}public static class ExcelHandler extends DefaultHandler{private final String excelPath;private final String sheetIndex;private final String nullString;/** * 从<c />标签得到的单元格格式，获取当前单元格数值需要用到 */ private enum XSSFDataType { BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, }private XSSFReader xssfReader;private ReadOnlySharedStringsTable sst;private StylesTable stylesTable;private boolean vIsOpen = false;private StringBuffer value = new StringBuffer();protected XSSFDataType nextDataType;// 当前遍历的Excel单元格列索引protected int thisColumnIndex = -1; // The last column printed to the output stream protected int lastColumnIndex = -1; protected int maxColumnCount = -1; protected int formatIndex; protected String formatString; private final DataFormatter formatter = new DataFormatter();private List<String> rowDatas = null; private String targetFile;private FileWriter writer;public ExcelHandler(String targetFile, String excelPath, String sheetIndex, String nullString){//this.writer = writer;this.targetFile = targetFile;this.excelPath = excelPath;this.sheetIndex = sheetIndex;this.nullString = nullString;}public ExcelHandler(FileWriter writer, String excelPath, String sheetIndex, String nullString){this.writer = writer;this.excelPath = excelPath;this.sheetIndex = sheetIndex;this.nullString = nullString;}/** * 初始化 */public void start(){try {OPCPackage op = OPCPackage.open(this.excelPath,PackageAccess.READ);this.xssfReader = new XSSFReader(op);this.sst = new ReadOnlySharedStringsTable(op);this.stylesTable = xssfReader.getStylesTable();// 开始解析parseXmlContent(new InputSource(getOneSheetStream(sheetIndex)));} catch (Exception e) {e.printStackTrace();}}/* (non-Javadoc) * @see org.xml.sax.helpers.DefaultHandler#startDocument() */@Overridepublic void startDocument() throws SAXException {try {if(null == writer && StringUtils.isNotBlank(targetFile)) {writer = new FileWriter(this.targetFile);}} catch (IOException e) {e.printStackTrace();}System.out.println("start Excel document ... "+Thread.currentThread().getName());}/* (non-Javadoc) * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes) */@Overridepublic void startElement(String uri, String localName, String qName,Attributes attributes) throws SAXException {if ("inlineStr".equals(qName) || "v".equals(qName)) {vIsOpen = true;// Clear contents cachevalue.setLength(0);}// c => cellelse if ("c".equals(qName)) {// Get the cell referenceString r = attributes.getValue("r");int firstDigit = -1;for (int c = 0; c < r.length(); ++c) {if (Character.isDigit(r.charAt(c))) {firstDigit = c;break;}}// 当前列索引thisColumnIndex = nameToColumn(r.substring(0, firstDigit));// Set up defaults.this.nextDataType = XSSFDataType.NUMBER;this.formatIndex = -1;this.formatString = null;String cellType = attributes.getValue("t");String cellStyleStr = attributes.getValue("s");if ("b".equals(cellType))nextDataType = XSSFDataType.BOOL;else if ("e".equals(cellType))nextDataType = XSSFDataType.ERROR;else if ("inlineStr".equals(cellType))nextDataType = XSSFDataType.INLINESTR;else if ("s".equals(cellType))nextDataType = XSSFDataType.SSTINDEX;else if ("str".equals(cellType))nextDataType = XSSFDataType.FORMULA;else if (cellStyleStr != null) {// It's a number, but almost certainly one// with a special style or format int styleIndex = Integer.parseInt(cellStyleStr);XSSFCellStyle style = this.stylesTable.getStyleAt(styleIndex);this.formatIndex = style.getDataFormat();this.formatString = style.getDataFormatString();if (this.formatString == null)this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);}}// row => 行开始else if ("row".equals(qName)) {//System.out.println("------------ row ---------------");//line = resultWriter.createLine();rowDatas = new LinkedList<String>();}}/* (non-Javadoc) * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String) */@Overridepublic void endElement(String uri, String localName, String qName)throws SAXException {Object thisObj = null;if("v".equals(qName)) {// 当前单元格数据thisObj = extractCellValue(value,nextDataType);//System.out.println("current cell value is : "+thisObj);// Output after we've seen the string contents// Emit commas for any fields that were missing on this rowif (lastColumnIndex == -1) {lastColumnIndex = 0;}try {for (int i = lastColumnIndex; i < thisColumnIndex; ++i) {//rowDatas.append(this.fieldSplit);//line.addField(nullString);rowDatas.add(nullString);writer.write("|");}// Might be the empty string.//line.addField(thisObj==null?nullString:String.valueOf(thisObj));rowDatas.add(thisObj==null?nullString:String.valueOf(thisObj));writer.write(thisObj==null?nullString:String.valueOf(thisObj));}catch (Exception e) {e.printStackTrace();}// Update columnif (thisColumnIndex > -1)lastColumnIndex = thisColumnIndex;}else if("row".equals(qName)) {try {// Print out any missing commas if neededif (maxColumnCount > 0) {// Columns are 0 basedif (lastColumnIndex == -1) {lastColumnIndex = 0;}for (int i = lastColumnIndex; i < (this.maxColumnCount)-1; i++) {//rowDatas.append(this.fieldSplit);//line.addField(nullString);rowDatas.add(nullString);writer.write("|");}}// We're onto a new row//resultWriter.sendToWriter(line);ExcelReader.datas.add(rowDatas);writer.write("\r\n");}catch (Exception e){e.printStackTrace();}lastColumnIndex = -1;}}/* (non-Javadoc) * @see net.bingosoft.ExcelTemplate.imports.handler.DefaultHandler#characters(char[], int, int) */@Overridepublic void characters(char[] ch, int start, int length)throws SAXException {if(vIsOpen){value.append(ch,start,length);}}/* (non-Javadoc) * @see org.xml.sax.helpers.DefaultHandler#endDocument() */@Overridepublic void endDocument() throws SAXException {//resultWriter.flush();ExcelReader.isReturn = true;System.out.println("End Excel Document .. "+Thread.currentThread().getName());try {this.writer.flush();this.writer.close();} catch (IOException e) {e.printStackTrace();}}/** * <p>获取指定sheet的数据流</p> * @param sheetId * @return */private InputStream getOneSheetStream(String sheetId){InputStream in = null;try {in = xssfReader.getSheet("rId"+sheetId);} catch (Exception e) {e.printStackTrace();} return in;}/** * <p>以xml的格式解析Excel数据</p> * @param sheetSource * @param tables * @param table * @throws Exception */private void parseXmlContent(InputSource sheetSource) throws Exception {XMLReader xmlReader = null;try {System.out.println("create XML reader.");xmlReader = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");} catch (Exception e) {e.printStackTrace();}xmlReader.setContentHandler(this);xmlReader.parse(sheetSource);}/** * 从列名转换为列索引 * @param name * @return */private int nameToColumn(String name) { int column = -1; for (int i = 0; i < name.length(); ++i) { int c = name.charAt(i); column = (column + 1) * 26 + c - 'A'; } return column; }/** * 抽取单元格数据 * @param value * @param nextDataType * @return */private Object extractCellValue(StringBuffer value,XSSFDataType nextDataType){Object obj = "";switch(nextDataType) {case BOOL : char first = value.charAt(0);obj = first=='0'?false:true;break;case ERROR : obj = value.toString().trim();break;case FORMULA : obj = value.toString().trim();break;case INLINESTR : XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());obj = rtsi.toString().trim();break;case SSTINDEX : String sstIndex = value.toString().trim();try {int idx = Integer.parseInt(sstIndex);XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));obj = rtss.toString().trim();}catch (NumberFormatException ex) {ex.printStackTrace();}break;case NUMBER :String n = value.toString().trim();if (this.formatString != null) {obj = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);}else{obj = n;}break;default:obj = "";break;}return obj;}}}?
1 楼 史汉发 昨天 程序不能直接运行啊 2 楼 kjkhi 昨天 史汉发 写道程序不能直接运行啊