Java 1.8
based on HSSF and XSSF Limitations
my poi version is 3.17 POI Examples
lauches my code
public class Controller {
EX stressTest;
public void fineFile() {
String stresstest = "C:\\Stresstest.xlsx";
HashMap<String, String[]> stressTestMap = new HashMap<>();
stressTestMap.put("aaaa", new String[]{"myField", "The field"});
stressTestMap.put("bbbb", new String[]{"other", "Other value"});
try {
InputStream stressTestIS = new FileInputStream(stresstest);
stressTest = new EX(stresstest, stressTestIS, stressTestMap);
} catch (IOException exp) {
}
}
public void printErr() {
if (stressTest.thereAreErrors()) {
try {
FileWriter myWriter = new FileWriter(
"C:\\logErrorsStressTest" +
(new SimpleDateFormat("ddMMyyyyHHmmss")).format(new Date()) +
".txt"
);
myWriter.write(stressTest.getBodyFileErrors());
myWriter.close();
} catch (IOException e) {
e.printStackTrace();
}
} else {
}
}
public void createBD() {
List<OneObjectWhatever> entitiesList =
(
!stressTest.thereAreErrors()
? ((List<OneObjectWhatever>) stressTest.toListCustomerObject(OneObjectWhatever.class))
: new ArrayList<>()
);
entitiesList.forEach(entity -> {
Field[] fields = entity.getClass().getDeclaredFields();
String valueString = "";
for (Field attr : fields) {
try {
attr.setAccessible(true);
valueString += " StressTest:" + attr.getName() + ": -" + attr.get(fields) + "- ";
attr.setAccessible(true);
} catch (Exception reflectionError) {
System.out.println(reflectionError);
}
}
});
}
}
MY CODE
public class EX {
private HashMap<Integer, HashMap<Integer, String> > rows;
private List<String> errors;
private int maxColOfHeader, minColOfHeader;
private HashMap<Integer, String> header;
private HashMap<String,String[]> relationHeaderClassPropertyDescription;
private void initVariables(String name, InputStream file) {
this.rows = new HashMap();
this.header = new HashMap<>();
this.errors = new ArrayList<String>(){{add("["+name+"] empty cells in position -> ");}};
try{
InputStream is = FileMagic.prepareToCheckMagic(file);
FileMagic fm = FileMagic.valueOf(is);
is.close();
switch (fm) {
case OLE2:
XLS2CSVmra xls2csv = new XLS2CSVmra(name, 50, rows);
xls2csv.process();
System.out.println("OLE2");
break;
case OOXML:
File flatFile = new File(name);
OPCPackage p = OPCPackage.open(flatFile, PackageAccess.READ);
XLSX2CSV xlsx2csv = new XLSX2CSV(p, System.out, 50, this.rows);
xlsx2csv.process();
p.close();
System.out.println("OOXML");
break;
default:
System.out.println("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
break;
}
} catch (IOException | EncryptedDocumentException | SAXException | OpenXML4JException exp){
System.out.println(exp);
exp.printStackTrace();
}
int rowHeader = rows.keySet().stream().findFirst().get();
this.header.putAll(rows.get(rowHeader));
this.rows.remove(rowHeader);
this.minColOfHeader = this.header.keySet().stream().findFirst().get();
this.maxColOfHeader = this.header.entrySet().stream()
.mapToInt(e -> e.getKey()).max()
.orElseThrow(NoSuchElementException::new);
}
public EX(String name, InputStream file, HashMap<String,String[]> relationHeaderClassPropertyDescription_) {
this.relationHeaderClassPropertyDescription = relationHeaderClassPropertyDescription_;
initVariables(name, file);
validate();
}
private void validate(){
rows.forEach((inx,row) -> {
for(int i = minColOfHeader; i <= maxColOfHeader; i++) {
//System.out.println("r:"+inx+" c:"+i+" cr:"+(!row.containsKey(i))+" vr:"+((!row.containsKey(i)) || row.get(i).trim().isEmpty())+" ch:"+header.containsKey(i)+" vh:"+(header.containsKey(i) && (!header.get(i).trim().isEmpty()))+" val:"+(row.containsKey(i)&&!row.get(i).trim().isEmpty()?row.get(i):"empty"));
if((!row.containsKey(i)) || row.get(i).trim().isEmpty()) {
if(header.containsKey(i) && (!header.get(i).trim().isEmpty())) {
String description = getRelationHeaders(i,1);
errors.add(" ["+header.get(i)+"]{"+description+"} = fila: "+(inx+1)+" - columna: "+ CellReference.convertNumToColString(i));
// System.out.println(" fila: "+inx+" - columna: " + i + " - valor: "+ (row.get(i).isEmpty()?"empty":row.get(i)));
}
}
}
});
header.forEach((i,v)->{System.out.println("stressTestMap.put(\""+v+"\", new String[]{\"{"+i+"}\",\"Mi descripcion XD\"});");});
}
public String getBodyFileErrors()
{
return String.join(System.lineSeparator(), errors);
}
public boolean thereAreErrors() {
return errors.stream().count() > 1;
}
public<T extends Class> List<? extends Object> toListCustomerObject(T type) {
List<Object> list = new ArrayList<>();
rows.forEach((inx, row) -> {
try {
Object obj = type.newInstance();
for(int i = minColOfHeader; i <= maxColOfHeader; i++) {
if (row.containsKey(i) && !row.get(i).trim().isEmpty()) {
if (header.containsKey(i) && !header.get(i).trim().isEmpty()) {
if(relationHeaderClassPropertyDescription.containsKey(header.get(i))) {
String nameProperty = getRelationHeaders(i,0);
Field field = type.getDeclaredField(nameProperty);
try{
field.setAccessible(true);
field.set(obj, (isConvertibleTo(field.getType(),row.get(i)) ? toObject(field.getType(),row.get(i)) : defaultValue(field.getType())) );
field.setAccessible(false);
}catch (Exception fex) {
//System.out.println("113"+fex);
continue;
}
}
}
}
}
list.add(obj);
} catch (Exception ex) {
//System.out.println("123:"+ex);
}
});
return list;
}
private Object toObject( Class clazz, String value ) {
if( Boolean.class == clazz || Boolean.TYPE == clazz) return Boolean.parseBoolean( value );
if( Byte.class == clazz || Byte.TYPE == clazz) return Byte.parseByte( value );
if( Short.class == clazz || Short.TYPE == clazz) return Short.parseShort( value );
if( Integer.class == clazz || Integer.TYPE == clazz) return Integer.parseInt( value );
if( Long.class == clazz || Long.TYPE == clazz) return Long.parseLong( value );
if( Float.class == clazz || Float.TYPE == clazz) return Float.parseFloat( value );
if( Double.class == clazz || Double.TYPE == clazz) return Double.parseDouble( value );
return value;
}
private boolean isConvertibleTo( Class clazz, String value ) {
String ptn = "";
if( Boolean.class == clazz || Boolean.TYPE == clazz) ptn = ".*";
if( Byte.class == clazz || Byte.TYPE == clazz) ptn = "^\\d+$";
if( Short.class == clazz || Short.TYPE == clazz) ptn = "^\\d+$";
if( Integer.class == clazz || Integer.TYPE == clazz) ptn = "^\\d+$";
if( Long.class == clazz || Long.TYPE == clazz) ptn = "^\\d+$";
if( Float.class == clazz || Float.TYPE == clazz) ptn = "^\\d+(\\.\\d+)?$";
if( Double.class == clazz || Double.TYPE == clazz) ptn = "^\\d+(\\.\\d+)?$";
Pattern pattern = Pattern.compile(ptn, Pattern.CASE_INSENSITIVE);
Matcher matcher = pattern.matcher(value);
return matcher.find();
}
private Object defaultValue( Class clazz) {
if( Boolean.class == clazz || Boolean.TYPE == clazz) return Boolean.parseBoolean( "false" );
if( Byte.class == clazz || Byte.TYPE == clazz) return Byte.parseByte( "0" );
if( Short.class == clazz || Short.TYPE == clazz) return Short.parseShort( "0" );
if( Integer.class == clazz || Integer.TYPE == clazz) return Integer.parseInt( "0" );
if( Long.class == clazz || Long.TYPE == clazz) return Long.parseLong( "0" );
if( Float.class == clazz || Float.TYPE == clazz) return Float.parseFloat( "0.0" );
if( Double.class == clazz || Double.TYPE == clazz) return Double.parseDouble( "0.0" );
return "";
}
private String getRelationHeaders(Integer columnIndexHeader, Integer TypeOrDescription /*0 - Type, 1 - Description*/) {
try {
return relationHeaderClassPropertyDescription.get(header.get(columnIndexHeader))[TypeOrDescription];
} catch (Exception e) {
}
return header.get(columnIndexHeader);
}
}
these are the modifications I made to the examples:
XLSX2CSV
public class XLSX2CSV {
/**
* Uses the XSSF Event SAX helpers to do most of the work
* of parsing the Sheet XML, and outputs the contents
* as a (basic) CSV.
*/
private class SheetToCSV implements SheetContentsHandler {
private boolean firstCellOfRow = false;
private int currentRow = -1;
private int currentCol = -1;
HashMap<Integer, String> valuesCell;
private void outputMissingRows(int number) {
for (int i=0; i<number; i++) {
for (int j=0; j<minColumns; j++) {
output.append(',');
}
output.append('\n');
}
}
@Override
public void startRow(int rowNum) {
// If there were gaps, output the missing rows
outputMissingRows(rowNum-currentRow-1);
// Prepare for this row
firstCellOfRow = true;
currentRow = rowNum;
currentCol = -1;
valuesCell = new HashMap<>();
}
@Override
public void endRow(int rowNum) {
// Ensure the minimum number of columns
for (int i = currentCol; i < minColumns; i++) {
output.append(',');
}
output.append('\n');
if (!valuesCell.isEmpty())
_rows.put(rowNum, valuesCell);
}
@Override
public void cell(String cellReference, String formattedValue,
XSSFComment comment) {
if (firstCellOfRow) {
firstCellOfRow = false;
} else {
output.append(',');
}
// gracefully handle missing CellRef here in a similar way as XSSFCell does
if (cellReference == null) {
cellReference = new CellAddress(currentRow, currentCol).formatAsString();
}
// Did we miss any cells?
int thisCol = (new CellReference(cellReference)).getCol();
int missedCols = thisCol - currentCol - 1;
for (int i = 0; i < missedCols; i++) {
output.append(',');
}
currentCol = thisCol;
if (!formattedValue.isEmpty())
valuesCell.put(thisCol, formattedValue);
// Number or string?
output.append(formattedValue);
/*try {
//noinspection ResultOfMethodCallIgnored
Double.parseDouble(formattedValue);
output.append(formattedValue);
} catch (NumberFormatException e) {
output.append('"');
output.append(formattedValue);
output.append('"');
}*/
}
@Override
public void headerFooter(String text, boolean isHeader, String tagName) {
// Skip, no headers or footers in CSV
}
}
///////////////////////////////////////
private final OPCPackage xlsxPackage;
/**
* Number of columns to read starting with leftmost
*/
private final int minColumns;
/**
* Destination for data
*/
private final PrintStream output;
public HashMap<Integer, HashMap<Integer, String>> _rows;
/**
* Creates a new XLSX -> CSV converter
*
* @param pkg The XLSX package to process
* @param output The PrintStream to output the CSV to
* @param minColumns The minimum number of columns to output, or -1 for no minimum
*/
public XLSX2CSV(OPCPackage pkg, PrintStream output, int minColumns, HashMap<Integer, HashMap<Integer, String> > __rows) {
this.xlsxPackage = pkg;
this.output = output;
this.minColumns = minColumns;
this._rows = __rows;
}
/**
* Parses and shows the content of one sheet
* using the specified styles and shared-strings tables.
*
* @param styles The table of styles that may be referenced by cells in the sheet
* @param strings The table of strings that may be referenced by cells in the sheet
* @param sheetInputStream The stream to read the sheet-data from.
* @exception java.io.IOException An IO exception from the parser,
* possibly from a byte stream or character stream
* supplied by the application.
* @throws SAXException if parsing the XML data fails.
*/
public void processSheet(
StylesTable styles,
ReadOnlySharedStringsTable strings,
SheetContentsHandler sheetHandler,
InputStream sheetInputStream) throws IOException, SAXException {
DataFormatter formatter = new DataFormatter();
InputSource sheetSource = new InputSource(sheetInputStream);
try {
XMLReader sheetParser = SAXHelper.newXMLReader();
ContentHandler handler = new XSSFSheetXMLHandler(
styles, null, strings, sheetHandler, formatter, false);
sheetParser.setContentHandler(handler);
sheetParser.parse(sheetSource);
} catch(ParserConfigurationException e) {
throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
}
}
/**
* Initiates the processing of the XLS workbook file to CSV.
*
* @throws IOException If reading the data from the package fails.
* @throws SAXException if parsing the XML data fails.
*/
public void process() throws IOException, OpenXML4JException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
int index = 0;
while (iter.hasNext()) {
InputStream stream = iter.next();
String sheetName = iter.getSheetName();
this.output.println();
this.output.println(sheetName + " [index=" + index + "]:");
processSheet(styles, strings, new SheetToCSV(), stream);
stream.close();
++index;
break;
}
}
}
XLS2CSVmra
public class XLS2CSVmra implements HSSFListener {
private int minColumns;
private POIFSFileSystem fs;
private PrintStream output;
public HashMap<Integer, HashMap<Integer, String>> _rows;
private HashMap<Integer, String> valuesCell;
private int lastRowNumber;
private int lastColumnNumber;
/** Should we output the formula, or the value it has? */
private boolean outputFormulaValues = false;
/** For parsing Formulas */
private SheetRecordCollectingListener workbookBuildingListener;
private HSSFWorkbook stubWorkbook;
// Records we pick up as we process
private SSTRecord sstRecord;
private FormatTrackingHSSFListener formatListener;
/** So we known which sheet we're on */
private int sheetIndex = -1;
private BoundSheetRecord[] orderedBSRs;
private List<BoundSheetRecord> boundSheetRecords = new ArrayList<BoundSheetRecord>();
// For handling formulas with string results
private int nextRow;
private int nextColumn;
private boolean outputNextStringRecord;
/**
* Creates a new XLS -> CSV converter
* @param fs The POIFSFileSystem to process
* @param output The PrintStream to output the CSV to
* @param minColumns The minimum number of columns to output, or -1 for no minimum
*/
public XLS2CSVmra(POIFSFileSystem fs, PrintStream output, int minColumns, HashMap<Integer, HashMap<Integer, String>> __rows) {
this.fs = fs;
this.output = output;
this.minColumns = minColumns;
this._rows = __rows;
this.valuesCell = new HashMap<>();
}
/**
* Creates a new XLS -> CSV converter
* @param filename The file to process
* @param minColumns The minimum number of columns to output, or -1 for no minimum
* @throws IOException
* @throws FileNotFoundException
*/
public XLS2CSVmra(String filename, int minColumns, HashMap<Integer, HashMap<Integer, String>> __rows) throws IOException, FileNotFoundException {
this(
new POIFSFileSystem(new FileInputStream(filename)),
System.out, minColumns,
__rows
);
}
/**
* Initiates the processing of the XLS file to CSV
*/
public void process() throws IOException {
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);
}
/**
* Main HSSFListener method, processes events, and outputs the
* CSV as the file is processed.
*/
@Override
public void processRecord(Record record) {
if(sheetIndex>0)
return;
int thisRow = -1;
int thisColumn = -1;
String thisStr = null;
switch (record.getSid())
{
case BoundSheetRecord.sid:
if(sheetIndex==-1)
boundSheetRecords.add((BoundSheetRecord)record);
break;
case BOFRecord.sid:
BOFRecord br = (BOFRecord)record;
if(br.getType() == BOFRecord.TYPE_WORKSHEET && sheetIndex==-1) {
// Create sub workbook if required
if(workbookBuildingListener != null && stubWorkbook == null) {
stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
}
// Output the worksheet name
// Works by ordering the BSRs by the location of
// their BOFRecords, and then knowing that we
// process BOFRecords in byte offset order
sheetIndex++;
if(orderedBSRs == null) {
orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
}
output.println();
output.println(
orderedBSRs[sheetIndex].getSheetname() +
" [" + (sheetIndex+1) + "]:"
);
}
break;
case SSTRecord.sid:
sstRecord = (SSTRecord) record;
break;
case BlankRecord.sid:
BlankRecord brec = (BlankRecord) record;
thisRow = brec.getRow();
thisColumn = brec.getColumn();
thisStr = "";
break;
case BoolErrRecord.sid:
BoolErrRecord berec = (BoolErrRecord) record;
thisRow = berec.getRow();
thisColumn = berec.getColumn();
thisStr = "";
break;
case FormulaRecord.sid:
FormulaRecord frec = (FormulaRecord) record;
thisRow = frec.getRow();
thisColumn = frec.getColumn();
if(outputFormulaValues) {
if(Double.isNaN( frec.getValue() )) {
// Formula result is a string
// This is stored in the next record
outputNextStringRecord = true;
nextRow = frec.getRow();
nextColumn = frec.getColumn();
} else {
thisStr = formatListener.formatNumberDateCell(frec);
}
} else {
thisStr = '"' +
HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
}
break;
case StringRecord.sid:
if(outputNextStringRecord) {
// String for formula
StringRecord srec = (StringRecord)record;
thisStr = srec.getString();
thisRow = nextRow;
thisColumn = nextColumn;
outputNextStringRecord = false;
}
break;
case LabelRecord.sid:
LabelRecord lrec = (LabelRecord) record;
thisRow = lrec.getRow();
thisColumn = lrec.getColumn();
thisStr = '"' + lrec.getValue() + '"';
break;
case LabelSSTRecord.sid:
LabelSSTRecord lsrec = (LabelSSTRecord) record;
thisRow = lsrec.getRow();
thisColumn = lsrec.getColumn();
if(sstRecord == null) {
thisStr = '"' + "(No SST Record, can't identify string)" + '"';
} else {
thisStr = '"' + sstRecord.getString(lsrec.getSSTIndex()).toString() + '"';
}
break;
case NoteRecord.sid:
NoteRecord nrec = (NoteRecord) record;
thisRow = nrec.getRow();
thisColumn = nrec.getColumn();
// TODO: Find object to match nrec.getShapeId()
thisStr = '"' + "(TODO)" + '"';
break;
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord) record;
thisRow = numrec.getRow();
thisColumn = numrec.getColumn();
// Format
thisStr = formatListener.formatNumberDateCell(numrec);
break;
case RKRecord.sid:
RKRecord rkrec = (RKRecord) record;
thisRow = rkrec.getRow();
thisColumn = rkrec.getColumn();
thisStr = '"' + "(TODO)" + '"';
break;
default:
break;
}
// Handle new row
if(thisRow != -1 && thisRow != lastRowNumber) {
lastColumnNumber = -1;
}
// Handle missing column
if(record instanceof MissingCellDummyRecord) {
MissingCellDummyRecord mc = (MissingCellDummyRecord)record;
thisRow = mc.getRow();
thisColumn = mc.getColumn();
thisStr = "";
}
// If we got something to print out, do so
if(thisStr != null) {
if (thisColumn > 0) {
output.print(',');
}
if (!thisStr.isEmpty())
valuesCell.put(thisColumn, thisStr);
output.print(thisStr);
}
// Update column and row count
if(thisRow > -1)
lastRowNumber = thisRow;
if(thisColumn > -1)
lastColumnNumber = thisColumn;
// Handle end of row
if(record instanceof LastCellOfRowDummyRecord) {
// Print out any missing commas if needed
if(minColumns > 0) {
// Columns are 0 based
if(lastColumnNumber == -1) { lastColumnNumber = 0; }
for(int i=lastColumnNumber; i<(minColumns); i++) {
output.print(',');
}
}
// We're onto a new row
lastColumnNumber = -1;
// End the row
output.println();
if(!valuesCell.isEmpty()) {
HashMap<Integer, String> newRow = new HashMap<>();
valuesCell.forEach((inx,vStr) -> {
newRow.put(inx, vStr);
});
_rows.put(lastRowNumber, newRow);
valuesCell = new HashMap<>();
}
}
}
}
