XlsExcelToHSSFReader
package com.xin.demo.excel;
import org.apache.poi.poifs.filesystem.*;
import org.apache.poi.hssf.usermodel.*;
import java.io.*;
import org.apache.poi.hssf.eventusermodel.*;
import org.apache.poi.hssf.model.*;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.eventusermodel.dummyrecord.*;
import java.util.*;
public class XlsExcelToHSSFReader implements HSSFListener
{
private int minColums;
private POIFSFileSystem fs;
private int totalRows;
private int lastRowNumber;
private int lastColumnNumber;
private boolean outputFormulaValues;
private EventWorkbookBuilder.SheetRecordCollectingListener workbookBuildingListener;
private HSSFWorkbook hssworkBook;
private sstworkBook sstworkBook;
private FormatTrackingHSSFListener formatListener;
private final HSSFDataFormatter formatter;
private String filePath;
private int sheetIndex;
private BoundSheetRecord[] orderedBSRs;
private ArrayList boundSheetRecords;
private int nextRow;
private int nextColumn;
private boolean outputNextStringRecord;
private int curRow;
private int rowSize;
private List<String> cellList;
private List<String[]> resultList;
private boolean flag;
private String sheetName;
private int ignorerows;
private boolean onlyHeader;
public XlsExcelToHSSFReader() {
this.minColums = -1;
this.totalRows = 0;
this.outputFormulaValues = true;
this.formatter = new HSSFDataFormatter();
this.filePath = "";
this.sheetIndex = 0;
this.boundSheetRecords = new ArrayList();
this.curRow = 0;
this.rowSize = 0;
this.cellList = new ArrayList<String>();
this.resultList = new ArrayList<String[]>();
this.flag = false;
}
public int process(final String fileName) throws Exception {
this.filePath = fileName;
this.fs = new POIFSFileSystem((InputStream)new FileInputStream(fileName));
final MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener((HSSFListener)this);
this.formatListener = new FormatTrackingHSSFListener((HSSFListener)listener);
final HSSFEventFactory factory = new HSSFEventFactory();
final HSSFRequest request = new HSSFRequest();
if (this.outputFormulaValues) {
request.addListenerForAllRecords((HSSFListener)this.formatListener);
}
else {
request.addListenerForAllRecords((HSSFListener)(this.workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener((HSSFListener)this.formatListener)));
}
factory.processWorkbookEvents(request, this.fs);
return this.totalRows;
}
public String[][] process(final String filePath, final InputStream inputStream, final int ignorerows) throws Exception {
this.filePath = filePath;
this.ignorerows = ignorerows;
this.fs = new POIFSFileSystem(inputStream);
final MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener((HSSFListener)this);
this.formatListener = new FormatTrackingHSSFListener((HSSFListener)listener);
final HSSFEventFactory factory = new HSSFEventFactory();
final HSSFRequest request = new HSSFRequest();
if (this.outputFormulaValues) {
request.addListenerForAllRecords((HSSFListener)this.formatListener);
}
else {
request.addListenerForAllRecords((HSSFListener)(this.workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener((HSSFListener)this.formatListener)));
}
factory.processWorkbookEvents(request, this.fs);
return this.resultList.toArray(new String[0][]);
}
public String[][] process(final InputStream inputStream, final int ignorerows, final boolean onlyHeader) throws Exception {
this.onlyHeader = onlyHeader;
this.ignorerows = ignorerows;
this.fs = new POIFSFileSystem(inputStream);
final MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener((HSSFListener)this);
this.formatListener = new FormatTrackingHSSFListener((HSSFListener)listener);
final HSSFEventFactory factory = new HSSFEventFactory();
final HSSFRequest request = new HSSFRequest();
if (this.outputFormulaValues) {
request.addListenerForAllRecords((HSSFListener)this.formatListener);
}
else {
request.addListenerForAllRecords((HSSFListener)(this.workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener((HSSFListener)this.formatListener)));
}
factory.processWorkbookEvents(request, this.fs);
return this.resultList.toArray(new String[0][]);
}
public void processRecord(final Record record) {
if (this.onlyHeader && this.curRow > 0) {
return;
}
int thisRow = -1;
int thisColumn = -1;
String thisStr = null;
String value = null;
switch (record.getSid()) {
case 133: {
this.boundSheetRecords.add(record);
break;
}
case 2057: {
final BOFRecord br = (BOFRecord)record;
if (br.getType() == 16) {
if (this.workbookBuildingListener != null && this.hssworkBook == null) {
this.hssworkBook = this.workbookBuildingListener.getStubHSSFWorkbook();
}
if (this.orderedBSRs == null) {
this.orderedBSRs = BoundSheetRecord.orderByBofPosition((List)this.boundSheetRecords);
}
this.sheetName = this.orderedBSRs[this.sheetIndex].getSheetname();
++this.sheetIndex;
break;
}
break;
}
case 252: {
this.sstworkBook = (sstworkBook)record;
break;
}
case 513: {
final BlankRecord brec = (BlankRecord)record;
thisRow = brec.getRow();
thisColumn = brec.getColumn();
thisStr = "";
this.cellList.add(thisColumn, thisStr);
break;
}
case 517: {
final BoolErrRecord berec = (BoolErrRecord)record;
thisRow = berec.getRow();
thisColumn = berec.getColumn();
thisStr = berec.getBooleanValue() + "";
this.cellList.add(thisColumn, thisStr);
this.checkRowIsNull(thisStr);
break;
}
case 6: {
final FormulaRecord frec = (FormulaRecord)record;
thisRow = frec.getRow();
thisColumn = frec.getColumn();
if (this.outputFormulaValues) {
if (Double.isNaN(frec.getValue())) {
this.outputNextStringRecord = true;
this.nextRow = frec.getRow();
this.nextColumn = frec.getColumn();
}
else {
thisStr = '\"' + HSSFFormulaParser.toFormulaString(this.hssworkBook, frec.getParsedExpression()) + '\"';
}
}
else {
thisStr = '\"' + HSSFFormulaParser.toFormulaString(this.hssworkBook, frec.getParsedExpression()) + '\"';
}
this.cellList.add(thisColumn, thisStr);
this.checkRowIsNull(thisStr);
break;
}
case 519: {
if (this.outputNextStringRecord) {
final StringRecord srec = (StringRecord)record;
thisStr = srec.getString();
thisRow = this.nextRow;
thisColumn = this.nextColumn;
this.outputNextStringRecord = false;
break;
}
break;
}
case 516: {
final LabelRecord lrec = (LabelRecord)record;
thisRow = (this.curRow = lrec.getRow());
thisColumn = lrec.getColumn();
value = lrec.getValue().trim();
value = (value.equals("") ? "" : value);
this.cellList.add(thisColumn, value);
this.checkRowIsNull(value);
break;
}
case 253: {
final LabelsstworkBook lsrec = (LabelsstworkBook)record;
thisRow = (this.curRow = lsrec.getRow());
thisColumn = lsrec.getColumn();
if (this.sstworkBook == null) {
this.cellList.add(thisColumn, "");
break;
}
value = this.sstworkBook.getString(lsrec.getSSTIndex()).toString().trim();
value = (value.equals("") ? "" : value);
this.cellList.add(thisColumn, value);
this.checkRowIsNull(value);
break;
}
case 515: {
final NumberRecord numrec = (NumberRecord)record;
thisRow = (this.curRow = numrec.getRow());
thisColumn = numrec.getColumn();
final Double valueDouble = numrec.getValue();
String formatString = this.formatListener.getFormatString((CellValueRecordInterface)numrec);
if (formatString.contains("m/d/yy")) {
formatString = "yyyy-MM-dd hh:mm:ss";
}
final int formatIndex = this.formatListener.getFormatIndex((CellValueRecordInterface)numrec);
value = this.formatter.formatRawCellContents((double)valueDouble, formatIndex, formatString).trim();
value = (value.equals("") ? "" : value);
this.cellList.add(thisColumn, value);
this.checkRowIsNull(value);
break;
}
}
if (thisRow != -1 && thisRow != this.lastRowNumber) {
this.lastColumnNumber = -1;
}
if (record instanceof MissingCellDummyRecord) {
final MissingCellDummyRecord mc = (MissingCellDummyRecord)record;
thisRow = (this.curRow = mc.getRow());
thisColumn = mc.getColumn();
this.cellList.add(thisColumn, "");
}
if (thisRow > -1) {
this.lastRowNumber = thisRow;
}
if (thisColumn > -1) {
this.lastColumnNumber = thisColumn;
}
if (record instanceof LastCellOfRowDummyRecord) {
if (this.minColums > 0 && this.lastColumnNumber == -1) {
this.lastColumnNumber = 0;
}
this.lastColumnNumber = -1;
if (this.curRow == 0) {
this.rowSize = this.cellList.size();
}
if (this.flag && this.curRow >= this.ignorerows) {
if (this.cellList.size() < this.rowSize) {
for (int chazhi = this.rowSize - this.cellList.size(), i = 0; i < chazhi; ++i) {
this.cellList.add("");
}
}
final String[] row = this.cellList.toArray(new String[0]);
this.resultList.add(row);
++this.totalRows;
}
this.cellList.clear();
this.flag = false;
}
}
public void checkRowIsNull(final String value) {
if (value != null && !"".equals(value)) {
this.flag = true;
}
}
public static void sendRows(final String filePath, final String sheetName, final int sheetIndex, final int curRow, final List<String> cellList) {
final StringBuffer oneLineSb = new StringBuffer();
oneLineSb.append(filePath);
oneLineSb.append("--");
oneLineSb.append("sheet" + sheetIndex);
oneLineSb.append("::" + sheetName);
oneLineSb.append("--");
oneLineSb.append("row" + curRow);
oneLineSb.append("::");
for (final String cell : cellList) {
oneLineSb.append(cell.trim());
oneLineSb.append("|");
}
String oneLine = oneLineSb.toString();
if (oneLine.endsWith("|")) {
oneLine = oneLine.substring(0, oneLine.lastIndexOf("|"));
}
System.out.println(oneLine);
}
}
XlsxExcelToSaxReader
package com.xin.demo.excel;
import org.apache.poi.xssf.model.*;
import java.util.*;
import java.io.*;
import org.apache.poi.openxml4j.opc.*;
import org.apache.poi.xssf.eventusermodel.*;
import org.xml.sax.helpers.*;
import org.xml.sax.*;
import org.apache.poi.xssf.usermodel.*;
public class XlsxExcelToSaxReader extends DefaultHandler
{
private SharedStringsTable sst;
private List<String[]> resultList;
private String lastContents;
private String dimension;
private int longest;
private String lastCellid;
private String lastRowid;
private List<String> currentRow;
private boolean issstIndex;
private String filePath;
private int ignorerows;
private boolean onlyHeader;
private int curRow;
private int curCol;
private int sheetIndex;
private String sheetName;
private StylesTable stylesTable;
public XlsxExcelToSaxReader() {
this.resultList = new ArrayList<String[]>();
this.issstIndex = false;
this.ignorerows = 0;
this.onlyHeader = false;
this.curRow = 1;
this.curCol = 0;
this.sheetIndex = 0;
this.sheetName = "";
}
public String[][] process(final InputStream inputStream, final int ignorerows, final boolean onlyHeader) throws Exception {
this.onlyHeader = onlyHeader;
this.ignorerows = ignorerows;
final OPCPackage pkg = OPCPackage.open(inputStream);
final XSSFReader xssfReader = new XSSFReader(pkg);
this.stylesTable = xssfReader.getStylesTable();
final SharedStringsTable sst = xssfReader.getSharedStringsTable();
final XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
final XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)xssfReader.getSheetsData();
while (sheets.hasNext()) {
this.curRow = 0;
++this.sheetIndex;
final InputStream sheet = sheets.next();
this.sheetName = sheets.getSheetName();
final InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
inputStream.close();
return this.resultList.toArray(new String[0][]);
}
public String[][] process(final String filename, final InputStream inputStream, final int ignorerows) throws Exception {
this.filePath = filename;
this.ignorerows = ignorerows;
final OPCPackage pkg = OPCPackage.open(filename);
final XSSFReader xssfReader = new XSSFReader(pkg);
this.stylesTable = xssfReader.getStylesTable();
final SharedStringsTable sst = xssfReader.getSharedStringsTable();
final XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
final XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)xssfReader.getSheetsData();
while (sheets.hasNext()) {
this.curRow = 0;
++this.sheetIndex;
final InputStream sheet = sheets.next();
this.sheetName = sheets.getSheetName();
final InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
inputStream.close();
return this.resultList.toArray(new String[0][]);
}
@Override
public void startElement(final String uri, final String localName, final String qName, final Attributes attributes) throws SAXException {
if (this.onlyHeader && this.curRow > 0) {
return;
}
this.lastContents = "";
if (qName.equals("dimension")) {
this.dimension = attributes.getValue("ref");
this.longest = covertRowIdtoInt(this.dimension.substring(this.dimension.indexOf(":") + 1));
}
if (qName.equals("row")) {
final String rowNum = attributes.getValue("r");
if (this.lastRowid != null) {
int gap = Integer.parseInt(rowNum) - Integer.parseInt(this.lastRowid);
if (gap > 1) {
--gap;
while (gap > 0) {
this.resultList.add(new String[this.longest]);
--gap;
}
}
}
this.lastRowid = attributes.getValue("r");
this.currentRow = new ArrayList<String>();
}
if (qName.equals("c")) {
final String rowId = attributes.getValue("r");
if (this.lastCellid != null) {
for (int gap = covertRowIdtoInt(rowId) - covertRowIdtoInt(this.lastCellid), i = 0; i < gap - 1; ++i) {
this.currentRow.add("");
}
}
else if (!"A1".equals(rowId)) {
for (int j = 0; j < covertRowIdtoInt(rowId) - 1; ++j) {
this.currentRow.add("");
}
}
this.lastCellid = rowId;
if (attributes.getValue("t") != null && attributes.getValue("t").equals("s")) {
this.issstIndex = true;
}
else {
this.issstIndex = false;
}
}
}
@Override
public void endElement(final String uri, final String localName, final String qName) throws SAXException {
if (this.onlyHeader && this.curRow > 0) {
return;
}
if (qName.equals("row")) {
if (covertRowIdtoInt(this.lastCellid) < this.longest) {
for (int i = 0; i < this.longest - covertRowIdtoInt(this.lastCellid); ++i) {
this.currentRow.add("");
}
}
if (this.curRow >= this.ignorerows) {
this.resultList.add(this.currentRow.toArray(new String[0]));
}
++this.curRow;
this.curCol = 0;
this.lastCellid = null;
}
if (qName.equals("v")) {
if (this.issstIndex) {
final String sstIndex = this.lastContents.toString();
try {
final int idx = Integer.parseInt(sstIndex);
final XSSFRichTextString rtss = new XSSFRichTextString(this.sst.getEntryAt(idx));
this.lastContents = rtss.toString();
this.currentRow.add(this.lastContents);
}
catch (NumberFormatException ex) {
ex.printStackTrace();
}
}
else {
this.currentRow.add(this.lastContents);
}
}
}
@Override
public void characters(final char[] ch, final int start, final int length) throws SAXException {
if (this.onlyHeader && this.curRow > 0) {
return;
}
this.lastContents += new String(ch, start, length);
}
public static int covertRowIdtoInt(final String rowId) {
int firstDigit = -1;
for (int c = 0; c < rowId.length(); ++c) {
if (Character.isDigit(rowId.charAt(c))) {
firstDigit = c;
break;
}
}
final String newRowId = rowId.substring(0, firstDigit);
int num = 0;
int result = 0;
for (int length = newRowId.length(), i = 0; i < length; ++i) {
final char ch = newRowId.charAt(length - i - 1);
num = ch - 'A' + '\u0001';
num *= (int)Math.pow(26.0, i);
result += num;
}
return result;
}
}
428
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
