I am reading an Excel file using POI's XSSF and SAX (Event API). The Excel sheet has thousands of rows so this is the only way that I have found to have good performance. Now I would like to read Excel file from one particularly row, for example row 6 because these Excel files are updated every day and I store the last row that I have already stored so I can read only the new rows.
How can I start from one row?
private void getExcelField(AcquisitionForm acquisitionForm) throws ExcelReadException, IOException{
InputStream stream=null;
OPCPackage p=null;
try{
p = OPCPackage.open(acquisitionForm.getDatatablePath(), PackageAccess.READ);
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(p);
XSSFReader xssfReader = new XSSFReader(p);
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
int index = 0;
//Test with one sheet
iter.hasNext();
//(iter.hasNext()) {
stream = iter.next();
String sheetName = iter.getSheetName();
processSheet(styles, strings, stream, acquisitionForm);
stream.close();
//++index;
//}
p.close();
}catch(Exception e){
throw new ExcelReadException("An error occured during excel file reading ", e);
}finally{
if (stream!=null)
stream.close();
if (p!=null)
p.close();
//++index;
// }
}
}
/**
* Parses and shows the content of one sheet
* using the specified styles and shared-strings tables.
* @param styles
* @param strings
* @param sheetInputStream
* @throws ExcelReadException
*/
private void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream, AcquisitionForm acquisitionForm) throws Exception {
InputSource sheetSource = new InputSource(sheetInputStream);
SAXParserFactory saxFactory = SAXParserFactory.newInstance();
SAXParser saxParser = saxFactory.newSAXParser();
XMLReader sheetParser = saxParser.getXMLReader();
//ContentHandler handler = new MyXSSFSheetHandler(styles, strings);
MyXSSFSheetHandler handler = new MyXSSFSheetHandler(styles, strings,databaseAcquisitionServices, acquisitionForm);
sheetParser.setContentHandler(handler);
sheetParser.parse(sheetSource);
}
MyXSSFSheetHandler
public MyXSSFSheetHandler(StylesTable styles, ReadOnlySharedStringsTable strings,DatabaseAcquisitionServices databaseAcquisitionServices, AcquisitionForm acquisitionForm, int sheetIndex) {
this.stylesTable = styles;
this.sharedStringsTable = strings;
this.formatter = new DataFormatter();
this.value = new StringBuffer();
this.nextDataType = XssfDataType.NUMBER;
this.databaseAcquisitionServices=databaseAcquisitionServices;
this.acquisitionForm=acquisitionForm;
this.sheetIndex = sheetIndex;
}
/**
*
*/
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if ("inlineStr".equals(name) || "v".equals(name)) {
vIsOpen = true;
// Clear contents cache
value.setLength(0);
}
// c => cell
else if ("c".equals(name)) {
// Get the cell reference
cellCoordinate = attributes.getValue("r");
int firstDigit = -1;
for (int c = 0; c < cellCoordinate.length(); ++c) {
if (Character.isDigit(cellCoordinate.charAt(c))) {
firstDigit = c;
break;
}
}
thisColumn = nameToColumn(cellCoordinate.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 = stylesTable.getStyleAt(styleIndex);
this.formatIndex = style.getDataFormat();
this.formatString = style.getDataFormatString();
if (this.formatString == null) {
this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
}
}
}
}
/*
* (non-Javadoc)
* @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
*/
public void endElement(String uri, String localName, String name)
throws SAXException {
String cellValue = null;
//String thisStr = null;
// v => contents of a cell
if ("v".equals(name)) {
// Process the value contents as required.
// Do now, as characters() may be called more than once
switch (nextDataType) {
case BOOL:
char first = value.charAt(0);
//thisStr = first == '0' ? "FALSE" : "TRUE";
//cellValue= new Boolean(first =='0' ? false: true);
cellValue=first == '0' ? "false" : "true";
break;
case ERROR:
//thisStr = "\"ERROR:" + value.toString() + '"';
cellValue=new String(value.toString());
break;
case FORMULA:
// A formula could result in a string value,
// so always add double-quote characters.
//thisStr = '"' + value.toString() + '"';
cellValue=new String(value.toString());
break;
case INLINESTR:
XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
//thisStr = '"' + rtsi.toString() + '"';
cellValue=new String(rtsi.toString());
break;
case SSTINDEX:
String sstIndex = value.toString();
try {
int idx = Integer.parseInt(sstIndex);
XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
// thisStr = '"' + rtss.toString() + '"';
cellValue=new String(rtss.toString());
}
catch (NumberFormatException ex) {
System.out.println("Failed to parse SST index '" + sstIndex + "': " + ex.toString());
}
break;
case NUMBER:
String n = value.toString();
if (this.formatString != null && n.length() > 0){
cellValue = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);
//cellValue=new Double(Double.parseDouble(n));
}
else{
//thisStr = n;
cellValue=new String(n);
}
break;
default:
cellValue="";
//thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
break;
}
// Output after we've seen the string contents
// Emit commas for any fields that were missing on this row
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
// for (int i = lastColumnNumber; i < thisColumn; ++i){
// System.out.print(',');
// }
// Might be the empty string.
rowValues.put(cellCoordinate,cellValue);
//System.out.print(cellValue);
// Update column
if (thisColumn > -1)
lastColumnNumber = thisColumn;
} else if ("row".equals(name)) {
// We're onto a new row
//I have to pass new HashMap because otherwise all the map into archiveAcquisition have the same values
databaseAcquisitionServices.archiveAcquisition(new TreeMap<>(rowValues), rowCounter, acquisitionForm, sheetIndex);
//Clear the structure used to store row data
rowValues.clear();
rowCounter++;
//System.out.println();
lastColumnNumber = -1;
}
}
/**
* Captures characters only if a suitable element is open.
* Originally was just "v"; extended for inlineStr also.
*/
public void characters(char[] ch, int start, int length)
throws SAXException {
if (vIsOpen)
value.append(ch, start, length);
}
/**
* Converts an Excel column name like "C" to a zero-based index.
*
* @param name
* @return Index corresponding to the specified name
*/
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;
}
You will likely need to count the number of rows that you see in a class derived from SheetContentsHandler, I don't think you need to have a separate XSSFSheetHAndler, I would rather use the default one and only have a dervied SheetContentsHandler which has callback-methods for all the things that you are interested in, e.g. startRow, endRow, cell.
You can take a look at the XLSX2CSV sample for an example of how streaming reading of XLSX files can be done, especially how the class SheetToCSV is used to get calls for each row/cell, you could e.g. use something like if (currentRow < startRow) { return } in the method cell() to skip rows.
The cell-method is called with a parameter String cellReference which can be used to retrieve the row/cell coordinates via new CellReference(cellReference).getCol() and new CellReference(cellReference).getRow().