Java code

TestSXSSFExcel.java (text/x-java), 42.02 KB, created by Michael on 2015-05-01 15:26:31 UTC

(hide)

Description: Java code

Filename:

MIME Type:

Creator: Michael

Created: 2015-05-01 15:26:31 UTC

Size: 42.02 KB

patch

obsolete

>package ca.zdata.programs.util; > >import java.io.File; >import java.io.FileOutputStream; >import java.io.IOException; >import java.io.InputStream; >import java.math.RoundingMode; >import java.text.DecimalFormat; >import java.text.NumberFormat; >import java.text.SimpleDateFormat; >import java.util.ArrayList; >import java.util.Date; >import java.util.HashMap; >import java.util.List; >import java.util.Map; > >import javax.xml.parsers.ParserConfigurationException; >import javax.xml.parsers.SAXParser; >import javax.xml.parsers.SAXParserFactory; > >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.Cell; >import org.apache.poi.ss.usermodel.CellStyle; >import org.apache.poi.ss.usermodel.DataFormat; >import org.apache.poi.ss.usermodel.DataFormatter; >import org.apache.poi.ss.usermodel.FillPatternType; >import org.apache.poi.ss.usermodel.Row; >import org.apache.poi.ss.usermodel.Sheet; >import org.apache.poi.ss.usermodel.Workbook; >import org.apache.poi.ss.usermodel.WorkbookFactory; >import org.apache.poi.ss.util.CellRangeAddress; >import org.apache.poi.ss.util.SSCellRange; >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.streaming.SXSSFCell; >import org.apache.poi.xssf.streaming.SXSSFRow; >import org.apache.poi.xssf.streaming.SXSSFSheet; >import org.apache.poi.xssf.streaming.SXSSFWorkbook; >import org.apache.poi.xssf.usermodel.XSSFCell; >import org.apache.poi.xssf.usermodel.XSSFCellStyle; >import org.apache.poi.xssf.usermodel.XSSFColor; >import org.apache.poi.xssf.usermodel.XSSFFont; >import org.apache.poi.xssf.usermodel.XSSFRichTextString; >import org.apache.poi.xssf.usermodel.XSSFRow; >import org.apache.poi.xssf.usermodel.XSSFSheet; >import org.apache.poi.xssf.usermodel.XSSFWorkbook; >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 ca.zdata.banking.model.Currency; >import ca.zdata.programs.report.CurrencyItem; >import ca.zdata.programs.report.Item; > >@SuppressWarnings({"unchecked", "rawtypes"}) >public class TestSXSSFExcel extends DefaultHandler >{ > String templateFilePath; > String tempDirPath; > String outputFilePath; > > private int rowAccessWindowSize; > > private SXSSFWorkbook spreadSheetDoc; > private SXSSFWorkbook currentWorkbook; > private Sheet sheet; > private SXSSFRow row; > private SXSSFCell cell; > > private List<String> subReports; > private String currentSubreport; > > List<String> mergedCells; > > private int fromRowNum; > private int toRowNum = -1; > private int aRow; > private int sourceRowNum; > private int thisColumn; > private int lastColumnNumber; > private int firstColumn; > private int aColumn; > private int subreportStartRow; > private int subreportEndRow = -1; > private int firstRow; > > private static final String TEMPLATE_START_PREFIX = "<<<"; > private static final String TEMPLATE_END_PREFIX = ">>>"; > private static final int FIRST_ROW = 0; > > private StringBuilder value; > private String element; > private boolean vIsOpen; > private List items; > private List<CellStyle> cellStyles; > private XSSFCellStyle rowStyle; > private XSSFCellStyle cellStyle; > private StylesTable stylesTable; > >// private List<String> formatStrings; > private String formatString; > private short formatIndex; > private final DataFormatter formatter; > private String dateFormatPattern; > private Map<String, Short> currencyFormatMap = new HashMap<String, Short>(); > > private xssfDataType nextDataType; > private ReadOnlySharedStringsTable sharedStringsTable; > private Map<String, XSSFColor> colors; > private Map<String, XSSFCellStyle> stylesMap; > > private int numRecords; > > enum xssfDataType > { > BOOL, > ERROR, > FORMULA, > INLINESTR, > SSTINDEX, > NUMBER, > } > > public static void main(String[] args) > { > System.out.println("Started..."); > int num = 100; > if(args != null && args.length > 0){ > try{ > num = Integer.parseInt(args[0]); > } > catch(NumberFormatException ignored){} > } > TestSXSSFExcel myTest = new TestSXSSFExcel(num); > myTest.showMemoryUsage(); > try{ > myTest.init(); > myTest.process(); > myTest.finish(); > } > catch(Exception e){ > e.printStackTrace(); > } > > System.out.println("Finished!"); > myTest.showMemoryUsage(); > } > > public TestSXSSFExcel(int num) > { > numRecords = num; > templateFilePath = "/usr/local/tomcat6/webapps/PROGRAMS/WEB-INF/files/templates/TestReportTemplate.xlsx"; > tempDirPath = "/usr/local/Universa/TempReportsDir/XSSF/"; > outputFilePath = "/usr/local/tomcat6/webapps/SPOOLER/TestSXSSF[" + numRecords + "].xlsx"; > > rowAccessWindowSize = 3; > formatter = new DataFormatter(); > dateFormatPattern = "yyyy/MM/dd"; > } > > public void init() throws Exception > { > File templateFile = new File(templateFilePath); > if (!templateFile.exists()) { > throw new RuntimeException("The template '" + templateFilePath + "' does not exist!"); > } > > File tmpDir = new File(tempDirPath); > if(!tmpDir.exists()){ > if(!tmpDir.mkdirs()){ > throw new RuntimeException("Failed to create temporary storage " + tempDirPath); > } > } > > tmpDir.setWritable(true, false); > XSSFWorkbook template = (XSSFWorkbook)WorkbookFactory.create(templateFile); > > spreadSheetDoc = new SXSSFWorkbook(rowAccessWindowSize); > spreadSheetDoc.setCompressTempFiles(true); // temp files will be gzipped > > splitTemplateToSubReports(template); > > // release the template > template.close(); > } > > public void process() throws Exception > { > System.out.println("Processing..."); > showMemoryUsage(); > nextSubreport(); // header > addDataItem(new Item("[HEADER]", String.class, "This is a header!"), false); > > nextSubreport(); // report body > showMemoryUsage(); > for(int i = 0; i < numRecords; i++) > { > Long amount = new Long((100+i)*100+75); > if(i % 2 == 1){ > amount *= -1; > } > > Currency cur = new Currency(); > cur.setCode(124); > cur.setAbbreviation("CAD"); > cur.setName("Canadian dollar"); > cur.setFractionDigits(2); > cur.setSymbol("$"); > cur.setSortOrder(124); > cur.setExchangeGlid(0); > > DecimalFormat df = new DecimalFormat(cur.getSymbol() + "#,##0.00"); > > addRow(new Item(Date.class, new Date()), new Item(String.class, "AAA"), new Item(Boolean.class, true), > new CurrencyItem(amount, df.format((double)amount / Math.pow(10, cur.getFractionDigits())), cur, false, false), new Item(Integer.class, i), new Item(Double.class, i+0.25)); > } > > nextSubreport(); // footer > showMemoryUsage(); > addDataItem(new Item("[FOOTER]", String.class, "This is a footer!"), false); > > // make sure last subreport is saved > nextSubreport(); > } > > public void finish() > { > merge(); > System.out.println("Saving..."); > showMemoryUsage(); > save(spreadSheetDoc, outputFilePath); > cleanup(tempDirPath); > } > > private void merge() > { > System.out.println("Merging..."); > currentWorkbook = spreadSheetDoc; > sheet = spreadSheetDoc.createSheet(); > > for(int i = 0; i < subReports.size(); i++) > { > String sub = subReports.get(i); > copySheetUsingSax(sub, i < subReports.size()-1); > System.gc(); > } > } > > private void save(SXSSFWorkbook wb, String filePath) > { > System.out.println("Saving " + filePath); > showMemoryUsage(); > try{ > // make sure all rows from active sheet are flushed > sheet = wb.getSheetAt(wb.getActiveSheetIndex()); > > ((SXSSFSheet)sheet).flushRows(0); > > File outFile = new File(filePath); > if(outFile.exists()){ > outFile.delete(); > } > > FileOutputStream fos = new FileOutputStream(filePath); > wb.write(fos); > fos.close(); > wb.close(); > > // dispose of temporary files backing this workbook on disk > wb.dispose(); > } catch(Exception e){ > System.err.println("Error: Cannot save the document. See stacktrace below."); > e.printStackTrace(); > } > } > > private void nextSubreport() > { > if(currentSubreport != null && currentWorkbook != null){ > // save current sub-report > save(currentWorkbook, tempDirPath + currentSubreport + ".xlsx"); > } > > if(currentSubreport == null){ > currentSubreport = subReports.get(0); > } > else > { > int index = subReports.indexOf(currentSubreport); > if(index < subReports.size()-1){ > currentSubreport = subReports.get(++index); > } > else{ > currentSubreport = null; > } > } > > counter = 0; > mergedCells = null; > currentWorkbook = null; > sheet = null; > aRow = FIRST_ROW; > sourceRowNum = FIRST_ROW; > rowStyle = null; > cellStyles = null; > colors = null; > stylesMap = null; > System.gc(); > > if (!isEmpty(currentSubreport)) > { > for(String sub : subReports) > { > if(currentSubreport.equals(sub)) > { > currentWorkbook = new SXSSFWorkbook(rowAccessWindowSize); > currentWorkbook.setCompressTempFiles(true); // temp files will be gzipped > sheet = currentWorkbook.createSheet(); > copySheetUsingSax(currentSubreport, false); > aRow = sheet.getLastRowNum(); > aColumn = firstColumn; > rowStyle = null; > cellStyles = null; > return; > } > } > } > } > > private void splitTemplateToSubReports(XSSFWorkbook template) > { > System.out.println("Splitting template..."); > showMemoryUsage(); > subReports = new ArrayList<String>(); > > XSSFSheet sourceSheet = template.getSheetAt(0); > while(fromRowNum <= sourceSheet.getLastRowNum()) > { > String sub = findNextSection(sourceSheet, fromRowNum); > if(!isEmpty(sub) && createSectionTemplate(sourceSheet, sub)) > { > fromRowNum = toRowNum + 1; > subReports.add(sub); > } > else{ > fromRowNum++; > } > } > } > > private String findNextSection(XSSFSheet sourceSheet, int fromRow) > { > String sectionName = null; > for(int curRow = fromRow; curRow <= sourceSheet.getLastRowNum(); curRow++) > { > boolean deductOneRow = false; > XSSFRow _row = sourceSheet.getRow(curRow); > if(_row == null){ > continue; > } > > int firstCellNum = _row.getFirstCellNum(); > if(firstCellNum >=0) > { > XSSFCell firstCell = _row.getCell(firstCellNum), toCell = null; > try > { > String cellText = firstCell.getStringCellValue(); > if(cellText == null || cellText.trim().isEmpty()){ > continue; > } > > fromRow = curRow; > fromRowNum = firstCell.getRowIndex(); > if(cellText.equalsIgnoreCase("[HEADER]")){ > sectionName = "[HEADER]"; > } > else if(cellText.startsWith(TEMPLATE_START_PREFIX)) > { > sectionName = cellText.substring(3, cellText.indexOf("]")+1); > fromRowNum = ++fromRow; > toCell = findCell(sourceSheet, fromRow, TEMPLATE_END_PREFIX + sectionName, false, false); > deductOneRow = true; > } > else if(cellText.contains("[FOOTER]")){ > sectionName = "[FOOTER]"; > } > > if(!isEmpty(sectionName)) > { > toRowNum = toCell == null ? fromRowNum : toCell.getRowIndex(); > > if(deductOneRow && toRowNum > fromRowNum){ > toRowNum--; > } > > return sectionName; > } > } catch(Exception ignored){} > } > } > > return null; > } > > private boolean createSectionTemplate(XSSFSheet sourceSheet, String sectionName) > { > try > { > // using DOM Model > XSSFWorkbook wb = new XSSFWorkbook(); > sheet = wb.createSheet(); > copySheetUsingDOM(sourceSheet); > > String fileName = tempDirPath + sectionName + ".xlsx"; > FileOutputStream fos = new FileOutputStream(fileName); > wb.write(fos); > fos.close(); > wb.close(); > > File file = new File(fileName); > if(file.exists()){ > file.setWritable(true, false); > } > > return true; > }catch(Exception e){ > e.printStackTrace(); > return false; > } > } > > private void copySheetUsingDOM(XSSFSheet sourceSheet) > { > int destRowNum = 0; > colors = new HashMap<String, XSSFColor>(); > for(int curRow = fromRowNum; curRow <= toRowNum; curRow++){ > XSSFRow destRow = (XSSFRow)sheet.createRow(destRowNum++); > copyRow(sourceSheet.getRow(curRow), destRow); > } > > // merged regions > int numMergedRegions = sourceSheet.getNumMergedRegions(); > for(int j = 0; j < numMergedRegions; j++) > { > CellRangeAddress range = sourceSheet.getMergedRegion(j); > if(range.getFirstRow() >= fromRowNum && range.getLastRow() <= toRowNum) > { > int firstRangeRowNum = range.getFirstRow() - fromRowNum; > int lastRangeRowNum = range.getLastRow() - fromRowNum; > if(lastRangeRowNum >= firstRangeRowNum){ > sheet.addMergedRegion(new CellRangeAddress(firstRangeRowNum, lastRangeRowNum, range.getFirstColumn(), range.getLastColumn())); > } > } > } > } > > private void copyRow(XSSFRow sourceRow, XSSFRow destRow) > { > if(sourceRow != null) > { > destRow.setHeight(sourceRow.getHeight()); > XSSFCellStyle sourceStyle = sourceRow.getRowStyle(); > if(sourceStyle != null) > { > XSSFCellStyle destStyle = destRow.getSheet().getWorkbook().createCellStyle(); >// destStyle.cloneStyleFrom(sourceStyle); > copyCellStyle(sourceStyle, destStyle, destRow.getSheet().getWorkbook()); > destRow.setRowStyle(destStyle); > } > > for(Cell sourceCell : sourceRow) > { > if(sourceCell != null){ > XSSFCell destCell = destRow.createCell(sourceCell.getColumnIndex()); > copyCell((XSSFCell)sourceCell, destCell); > } > } > } > } > > private void copyCell(XSSFCell sourceCell, XSSFCell destCell) > { > int type = sourceCell.getCellType(); > destCell.setCellType(type); > switch (type) > { > case Cell.CELL_TYPE_FORMULA: > destCell.setCellFormula(sourceCell.getCellFormula()); > break; > case Cell.CELL_TYPE_BOOLEAN: > destCell.setCellValue(sourceCell.getBooleanCellValue()); > break; > case Cell.CELL_TYPE_NUMERIC: > destCell.setCellValue(sourceCell.getNumericCellValue()); > break; > case Cell.CELL_TYPE_ERROR: > destCell.setCellErrorValue(sourceCell.getErrorCellValue()); > break; > case Cell.CELL_TYPE_STRING: > destCell.setCellValue(sourceCell.getRichStringCellValue()); > break; > case Cell.CELL_TYPE_BLANK: > default: > break; > } > > destCell.setCellComment(sourceCell.getCellComment()); > > XSSFCellStyle sourceStyle = sourceCell.getCellStyle(); > XSSFCellStyle destStyle = destCell.getSheet().getWorkbook().createCellStyle(); >// destStyle.cloneStyleFrom(sourceStyle); > copyCellStyle(sourceStyle, destStyle, destCell.getSheet().getWorkbook()); > destCell.setCellStyle(destStyle); > > destCell.getSheet().setColumnWidth(sourceCell.getColumnIndex(), sourceCell.getSheet().getColumnWidth(sourceCell.getColumnIndex())); > } > > private XSSFCell findCell(XSSFSheet sourceSheet, int fromRow, String content, boolean part, boolean caseSensitive) > { > for(int rowNum = fromRow; rowNum <= sourceSheet.getLastRowNum(); rowNum++) > { > XSSFRow _row = sourceSheet.getRow(rowNum); > if(_row == null){ > continue; > } > > for(Cell _cell : _row) > { > try > { > String cellContent = _cell.getStringCellValue(); > if(part) > { > if(!caseSensitive && cellContent.toLowerCase().contains(content.toLowerCase()) || caseSensitive && cellContent.contains(content)){ > return (XSSFCell)_cell; > } > } > else > { > if(!caseSensitive && cellContent.equalsIgnoreCase(content) || caseSensitive && cellContent.equals(content)){ > return (XSSFCell)_cell; > } > } > }catch(Exception ignored){} > } > } > > return null; > } > > private void cleanup(String tempDir) > { > try{ > currentWorkbook.close(); > spreadSheetDoc.close(); > }catch(Exception ignored){} > > File tmpDir = new File(tempDir); > if(tmpDir.exists()) > { > File[] list = tmpDir.listFiles(); > for(File f : list) > { > if(!f.isDirectory()) > { > try{ > if(!f.delete()){ > System.out.println("Failed to delete " + f.getName()); > } > } catch(Exception e){ > e.printStackTrace(); > } > } > else > { > // recursive call > cleanup(f.getAbsolutePath()); > try > { > if(!f.delete()){ > System.out.println("Failed to delete " + f.getName()); > } > } catch(Exception e){ > e.printStackTrace(); > } > } > } > > if(!tmpDir.delete()){ > System.out.println("Failed to delete " + tempDir); > } > } > } > > private boolean isEmpty(String s) { > return s == null || s.trim().length() == 0; > } > > /** > * Reads the source sheet using "SAX" approach, and copies it into the current sheet, using "Buffered Streaming" model > * @param sub > * @param insertEmptyRow > */ > private void copySheetUsingSax(String sub, boolean insertEmptyRow) > { > try{ > String subReportPath = tempDirPath + sub + ".xlsx"; > File xlsxFile = new File(subReportPath); > if (!xlsxFile.exists()) { > System.err.println("Not found or not a file: " + xlsxFile.getPath()); > return; > } > > value = new StringBuilder(); > mergedCells = null; > > // The package open is instantaneous, as it should be. > OPCPackage xlsxPackage = OPCPackage.open(xlsxFile, PackageAccess.READ); > XSSFReader xssfReader = new XSSFReader(xlsxPackage); > XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData(); > if (iter.hasNext()) > { > InputStream stream = iter.next(); > getMergedCellsFromTemplate(stream); > stream.close(); > } > > xlsxPackage.close(); > // stream is closed, reopen it again > xlsxPackage = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ); > xssfReader = new XSSFReader(xlsxPackage); > iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData(); > if (iter.hasNext()) > { > InputStream stream = iter.next(); > sharedStringsTable = new ReadOnlySharedStringsTable(xlsxPackage); > stylesTable = xssfReader.getStylesTable(); > colors = new HashMap<String, XSSFColor>(); > stylesMap = new HashMap<String, XSSFCellStyle>(); > processSheet(stream); > stream.close(); > if(insertEmptyRow){ > // create one empty row when merging for better separation > appendRow(0); > aRow++; > } > } > > xlsxPackage.close(); > } catch(Exception e){ > e.printStackTrace(); > } > } > > /** > * Retrieves merged cell regions from the sheet > * > * @param sheetInputStream > * > * @throws SAXException > * @throws ParserConfigurationException > * @throws IOException > */ > private void getMergedCellsFromTemplate(InputStream sheetInputStream) throws SAXException, ParserConfigurationException, IOException > { > InputSource sheetSource = new InputSource(sheetInputStream); > SAXParserFactory saxFactory = SAXParserFactory.newInstance(); > SAXParser saxParser = saxFactory.newSAXParser(); > XMLReader sheetParser = saxParser.getXMLReader(); > sheetParser.setContentHandler(this); > element = "mergeCell"; > sheetParser.parse(sheetSource); > } > > /** > * Parses the content of one sheet and merges it into the streaming model sheet, > * using the specified styles and shared-strings tables. > * > * @param sstyles > * @param strings > * @param sheetInputStream > * @param rowNum - starting row number in the destination sheet > * > * @return int - last row number of the destination sheet > */ > private void processSheet(InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException > { > InputSource sheetSource = new InputSource(sheetInputStream); > SAXParserFactory saxFactory = SAXParserFactory.newInstance(); > SAXParser saxParser = saxFactory.newSAXParser(); > XMLReader sheetParser = saxParser.getXMLReader(); > sheetParser.setContentHandler(this); > element = null; > sheetParser.parse(sheetSource); > } > > public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException > { > if(!isEmpty(element)) > { > if(element.equals(name)) > { > if(mergedCells == null){ > mergedCells = new ArrayList<String>(); > } > > mergedCells.add(attributes.getValue("ref")); > } > } > else > { > if ("inlineStr".equals(name) || "v".equals(name) || "t".equals(name)) > { > vIsOpen = true; > // Clear contents cache > value.setLength(0); > } > else if("row".equals(name)) > { >// formatStrings = new ArrayList<String>(); > items = new ArrayList<Item>(); > cellStyles = new ArrayList<CellStyle>(); > rowStyle = null; > String rowStyleStr = attributes.getValue("s"); > if(!isEmpty(rowStyleStr)) > { > int styleIndex = Integer.parseInt(rowStyleStr); > XSSFCellStyle sourceStyle = stylesTable.getStyleAt(styleIndex); > rowStyle = (XSSFCellStyle)currentWorkbook.createCellStyle(); >// rowStyle.cloneStyleFrom(sourceStyle); > copyCellStyle(sourceStyle, rowStyle, currentWorkbook); > } > } > else if ("c".equals(name)) // c => cell > { > Item item = new Item(); > formatIndex = -1; > formatString = null; > > // Get the cell reference > getCellRef(attributes, false); > String cellType = attributes.getValue("t"); > String cellStyleStr = attributes.getValue("s"); > if("n".equals(cellType)){ > nextDataType = xssfDataType.NUMBER; > item.setValueType(Number.class); > } > else if ("b".equals(cellType)){ > nextDataType = xssfDataType.BOOL; > item.setValueType(Boolean.class); > } > else > { > if("inlineStr".equals(cellType)){ > nextDataType = xssfDataType.INLINESTR; > } > else if("e".equals(cellType)){ > nextDataType = xssfDataType.ERROR; > } > else if("s".equals(cellType)){ > nextDataType = xssfDataType.SSTINDEX; > } > else if("str".equals(cellType)){ > nextDataType = xssfDataType.FORMULA; > } > > item.setValueType(String.class); > } > > items.add(item); > cellStyle = null; > if (!isEmpty(cellStyleStr)) > { > // It's a number, but almost certainly one > // with a special style or format > int styleIndex = Integer.parseInt(cellStyleStr); > XSSFCellStyle sourceStyle = stylesTable.getStyleAt(styleIndex); > > formatString = sourceStyle.getDataFormatString(); > if(formatString == null){ > formatIndex = sourceStyle.getDataFormat(); > formatString = BuiltinFormats.getBuiltinFormat(formatIndex); > } > >// cellStyle = stylesMap.get(cellStyleStr); >// if(cellStyle == null) >// { > cellStyle = currentWorkbook. getXSSFWorkbook().getStylesSource().createCellStyle(); >// cellStyle.cloneStyleFrom(sourceStyle); > copyCellStyle(sourceStyle, cellStyle, currentWorkbook); >// stylesMap.put(cellStyleStr, cellStyle); >// } > } > > cellStyles.add(cellStyle); >// formatStrings.add(formatString); > } > } > } > > private int counter; > /* > * (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 > { > if(isEmpty(element) && items != null) > { > // v => contents of a cell > if ("v".equals(name) || "t".equals(name)) > { > Item item = (Item)items.get(items.size()-1); > // 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); > item.setValue(first != 0); > break; > case ERROR: > item.setValue(value.toString()); > break; > case INLINESTR: > XSSFRichTextString rtsi = new XSSFRichTextString(value.toString()); > item.setValue(rtsi.toString()); > break; > case SSTINDEX: > String sstIndex = value.toString(); > try > { > int idx = Integer.parseInt(sstIndex); > XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx)); > item.setValue(rtss.toString()); > } > catch (NumberFormatException ex) { > ex.printStackTrace();//("Failed to parse SST index '" + sstIndex + "': " + ex.toString()); > } > > break; > case FORMULA: > case NUMBER: > String n = value.toString(); > if (formatString != null) > { > String formattedValue = formatter.formatRawCellContents(Double.parseDouble(n), formatIndex, formatString); > if(formatString.equals("GENERAL")) > { > if(formattedValue.contains(".")){ > item.setValue(Double.parseDouble(formattedValue)); > } > else{ > item.setValue(Long.parseLong(formattedValue)); > } > } > else if(formatString.equals(dateFormatPattern)) > { > try{ > SimpleDateFormat sdf = new SimpleDateFormat(dateFormatPattern); > item.setValue(sdf.parse(formattedValue)); > item.setValueType(Date.class); > } > catch(Exception e){ > System.out.println(formattedValue + " is not Date/Time object."); > item.setValue(Double.parseDouble(n)); > } > } > else > { // currency > item.setValue(Double.parseDouble(n)); > item.setFormattedValue(formatString); > } > } > else{ > item.setValue(Double.parseDouble(n)); > } > > break; > default: > break; > } > > if (lastColumnNumber == -1) { > lastColumnNumber = 0; > } > > // Update column > if (thisColumn > -1) > lastColumnNumber = thisColumn; > > } > else if("f".equals(name)) // formula > { > Item item = (Item)items.get(items.size()-1); > item.setValueType(String.class); > item.setValue(value); > } > else if ("row".equals(name)) > { > addDataRow((Item[])items.toArray(new Item[items.size()]), true, items.size()); > counter++; > if(counter % 10000 == 0){ > showMemoryUsage(); > } > > // Columns are 0 based > if (lastColumnNumber == -1) { > lastColumnNumber = 0; > } > > // We're onto a new row > lastColumnNumber = -1; > } > } > } > > private void getCellRef(Attributes attributes, boolean newRow) > { > String r = attributes.getValue("r"); > int firstDigit = findFirstDigitIndex(r); > thisColumn = nameToColumn(r.substring(0, firstDigit)); > if(firstDigit > -1) > { > try{ > sourceRowNum = Integer.parseInt(r.substring(firstDigit)) - 1; > }catch(NumberFormatException nfe){ > if(newRow){ > sourceRowNum++; > } > } > } > } > > /** > * Finds if the current row belongs to any existing merged region in the currently opened window > * of the streaming model > * > * @param rowNum > * @return region > */ > private CellRangeAddress findMergedRegion(int rowNum) > { > CellRangeAddress region = null; > try > { > for(String cellStr : mergedCells) > { > String[] cells = cellStr.split(":"); > int index = findFirstDigitIndex(cells[0]); > int rowNumFirst = Integer.parseInt(cells[0].substring(index)) - 1; > int firstCol = nameToColumn(cells[0].substring(0, index)); > > index = findFirstDigitIndex(cells[1]); > int rowNumLast = Integer.parseInt(cells[1].substring(index)) - 1; > int lastCol = nameToColumn(cells[1].substring(0, index)); > // check if this part of the merged region is within the current window size of the streaming model > if(sourceRowNum >= rowNumFirst && sourceRowNum <= rowNumLast && sourceRowNum - rowNumFirst < rowAccessWindowSize){ > return new CellRangeAddress(rowNumFirst, sourceRowNum, firstCol, lastCol); > } > } > } catch(Exception e){ > return null; > } > > return region; > } > > private int findFirstDigitIndex(String src) > { > for (int c = 0; c < src.length(); ++c) { > if (Character.isDigit(src.charAt(c))) { > return c; > } > } > > return -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; > } > > public void addRow(Item ...its){ > addDataRow(its, true, its.length); > } > > public void addDataRow(Item[] itemsArray, boolean newLine, int numCells) > { > if(cellStyles == null){ > cellStyles = new ArrayList<CellStyle>(); > } > > if (newLine) { > aColumn = firstColumn; > appendRow(numCells); > } > > if (itemsArray != null ) > { > for (Item item : itemsArray) > { > cell = (SXSSFCell)row.getCell(aColumn); > if(cell == null){ > cell = (SXSSFCell)row.createCell(aColumn); > CellStyle style = currentWorkbook.createCellStyle(); > cell.setCellStyle(style); > cellStyles.add(style); > } > > insertByType(item); > aColumn++; > } > } > > // check for merged region > CellRangeAddress mergedRegion = findMergedRegion(sourceRowNum); > if(mergedRegion != null) > { > int rowDiff = aRow - sourceRowNum; > if(rowDiff != 0){ > mergedRegion.setFirstRow(mergedRegion.getFirstRow() + rowDiff); > mergedRegion.setLastRow(mergedRegion.getLastRow() + rowDiff); > } > sheet.addMergedRegion(mergedRegion); > } > > if (newLine) { > ++aRow; > ++subreportEndRow; > } > } > > private void appendRow(int numCells) > { > if (aRow >= firstRow) > { > row = (SXSSFRow)sheet.createRow(aRow); > for(int i = 0; i < numCells; i++) > { > cell = (SXSSFCell)row.createCell(i); > CellStyle style = null; > if(cellStyles.isEmpty() || i == cellStyles.size()){ > style = currentWorkbook.createCellStyle(); > cellStyles.add(style); > } > else{ > style = cellStyles.get(i); > } > > cell.setCellStyle(style); > } > > if(rowStyle != null){ > row.setRowStyle(rowStyle); > } > } > } > > private void insertByType(Item item) > { > Class<?> type = item.getValueType(); > Object retobj = item.getValue(); > > if (retobj == null){ > return; > } > > CellStyle style = cell.getCellStyle(); //cellStyles == null || aColumn >= cellStyles.size() ? cell.getCellStyle() : cellStyles.get(aColumn); > try > { > cell.setCellType(Cell.CELL_TYPE_NUMERIC); > if (item instanceof CurrencyItem) > { > CurrencyItem currencyItem = (CurrencyItem) item; > setCurrencyFormat(currencyItem, style); > Long val = (Long) retobj; > > if (currencyItem.isZeroHidden() && val == 0){ > cell.setCellValue(""); > } else { > cell.setCellValue(new Double(val.doubleValue() / Math.pow(10, currencyItem.getCurrency().getFractionDigits()))); > } > } > else if (type == Double.class || type == double.class ) > { > cell.setCellValue((Double)retobj); > } > else if (type == Short.class || type == short.class){ > cell.setCellValue((Short)retobj); > } > else if (type == Integer.class || type == int.class){ > cell.setCellValue((Integer)retobj); > } > else if(type == Long.class || type == long.class){ > cell.setCellValue((Long)retobj); > } > else if (type == java.util.Date.class) > { > if(!dateFormatPattern.equals(style.getDataFormatString())){ > style.setDataFormat(currentWorkbook.createDataFormat().getFormat(dateFormatPattern)); > } > > cell.setCellValue((Date)retobj); > } > else if(type == Number.class) > { > Object formattedValue = item.getFormattedValue(); > if(formattedValue == null) > { > if(retobj.toString().contains(".")){ > cell.setCellValue((Double)retobj); > } > else{ > cell.setCellValue((Long)retobj); > } > } > else > { // currency > if(!formattedValue.toString().equals(style.getDataFormatString())){ > style.setDataFormat(currentWorkbook.createDataFormat().getFormat(formattedValue.toString())); > } > > cell.setCellValue((Double)retobj); > } > } > else if (type == Boolean.class || type.getName().equals("boolean")){ > cell.setCellValue(((Boolean) retobj).toString()); > } > else > { > cell.setCellType(Cell.CELL_TYPE_STRING); > cell.setCellValue(retobj.toString()); > if(type == String.class) > { > String[] lines = retobj.toString().split("\n"); > if(lines.length > 1) > { > short fontHeight = 0; > fontHeight = ((XSSFCellStyle)cell.getCellStyle()).getFont().getFontHeightInPoints(); > cell.getRow().setHeightInPoints(lines.length * (fontHeight+1)); > style.setWrapText(true); > } > } > } > } catch(Exception e){ > System.err.println("Error inserting the item '" + retobj + "'\n" + e.getMessage()); > e.printStackTrace(); > } > } > > private void setCurrencyFormat(CurrencyItem currencyItem, CellStyle style) > { > try > { > short key = getCurrencyFormatKey(currencyItem); > if(style.getDataFormat() != key){ > style.setDataFormat(key); > cell.setCellStyle(style); > } > } catch (Exception e) { > e.printStackTrace(); > } > } > > private Short getCurrencyFormatKey(CurrencyItem currencyItem) throws Exception > { > Currency currency = currencyItem.getCurrency(); > String currencySymbol = currencyItem.isHideCurrencySymbol() ? "" : currency.getSymbol(); > String currencyFormatMapKey = currency.getCode() + "-" + currencySymbol + "-" + currency.getFractionDigits();//generateCurrencyFormatKey(currencyItem); > Short key = currencyFormatMap.get(currencyFormatMapKey); > if (key == null) > { > String fracDigPattern = currency.getFractionDigits() == 0 ? "0" : "0." + String.format("%0" + currency.getFractionDigits() + "d", 0); > String currencyPattern = "[$" + currencySymbol + "-409]#,##" + fracDigPattern; > String ft = currencyItem.isHideCurrencySymbol() > ? "#,##" + fracDigPattern + ";[RED]-#,##" + fracDigPattern > : currencyPattern + ";[RED]-" + currencyPattern; > DataFormat df = currentWorkbook.createDataFormat(); > key = df.getFormat(ft); > currencyFormatMap.put(currencyFormatMapKey, key); > } > > return key; > } > > private void addDataItem(Item item, boolean withinSubreport) throws Exception > { > CellRangeAddress aRangeAddress = withinSubreport && subreportStartRow >= 0 > ? getRange(item.getKey(), true, subreportStartRow, subreportEndRow) > : getRange(item.getKey(), true, null, null); > > // The report skips the item insertion if the placeholder is not found: > if (aRangeAddress == null) { > return; > } > > cell = (SXSSFCell)sheet.getRow(aRangeAddress.getFirstRow()).getCell(aRangeAddress.getFirstColumn()); > aRow = sheet.getRow(aRangeAddress.getFirstRow()).getRowNum(); > aColumn = aRangeAddress.getFirstColumn(); > insertByType(item); > } > > private CellRangeAddress getRange(String searchStr, boolean cleanOutText, Integer fromRow, Integer toRow) throws Exception > { > CellRangeAddress aRangeAddress = null; > List lst = null; > // Assumption is we are at the last row of the "access window" > int _firstRow = fromRow == null ? aRow - rowAccessWindowSize + 1 : fromRow.intValue(); > if(_firstRow < 0){ > _firstRow = 0; > } > > int lastRow = toRow == null ? aRow : toRow.intValue(); > for(int r = _firstRow; r <= lastRow; r++) > { > Row _row = sheet.getRow(r); > if(_row == null){ > continue; > } > > for(Cell _cell : _row) > { > try > { > String cellText = _cell.getStringCellValue(); > if(cellText.contains(searchStr)) > { > aRangeAddress = new CellRangeAddress(_row.getRowNum(), _row.getRowNum(), _cell.getColumnIndex(), _cell.getColumnIndex()); > lst = new ArrayList<SXSSFCell>(); > lst.add(_cell); > break; > } > } catch(Exception ignored){} > } > > if(aRangeAddress != null){ > break; > } > } > > if (aRangeAddress != null && cleanOutText) > { > aRow = aRangeAddress.getFirstRow(); > SSCellRange range = SSCellRange.create(aRow, aRangeAddress.getFirstColumn(), 1, 1, lst, Cell.class); > cell = (SXSSFCell)range.getCell(0, 0); > cell.setCellValue(""); > } > > return aRangeAddress; > } > > private void copyCellStyle(XSSFCellStyle source, XSSFCellStyle dest, Workbook wb) > { > if(source == null){ > return; > } > > if(dest == null){ > dest = (XSSFCellStyle)currentWorkbook.createCellStyle(); > } > > // alignment > dest.setAlignment(source.getAlignment()); > dest.setVerticalAlignment(source.getVerticalAlignment()); > > // borders > dest.setBorderBottom(source.getBorderBottomEnum()); > dest.setBorderLeft(source.getBorderLeftEnum()); > dest.setBorderRight(source.getBorderRightEnum()); > dest.setBorderTop(source.getBorderTopEnum()); > > XSSFColor sColor = source.getBottomBorderXSSFColor(); > if(sColor != null){ > dest.setBottomBorderColor(getColor(sColor)); > } > > sColor = source.getLeftBorderXSSFColor(); > if(sColor != null){ > dest.setLeftBorderColor(getColor(sColor)); > } > > sColor = source.getRightBorderXSSFColor(); > if(sColor != null){ > dest.setRightBorderColor(getColor(sColor)); > } > > sColor = source.getTopBorderXSSFColor(); > if(sColor != null){ > dest.setTopBorderColor(getColor(sColor)); > } > > //TODO: font > dest.setIndention(source.getIndention()); > > XSSFFont s_font = source.getFont(); > XSSFFont d_font = (XSSFFont)wb.createFont(); > d_font.setBold(s_font.getBold()); > d_font.setBoldweight(s_font.getBoldweight()); > d_font.setCharSet(s_font.getCharSet()); > d_font.setColor(getColor(s_font.getXSSFColor())); > d_font.setFamily(s_font.getFamily()); > d_font.setFontHeight(s_font.getFontHeight()); > d_font.setFontName(s_font.getFontName()); > d_font.setItalic(s_font.getItalic()); > d_font.setStrikeout(s_font.getStrikeout()); > d_font.setUnderline(s_font.getUnderline()); > dest.setFont(d_font); > > // pattern > FillPatternType fpt = source.getFillPatternEnum(); > if(fpt != null){ > dest.setFillPattern(fpt); > } > > // fill colors > sColor = source.getFillForegroundXSSFColor(); > if(sColor != null){ > dest.setFillForegroundColor(getColor(sColor)); > } > > sColor = source.getFillBackgroundXSSFColor(); > if(sColor != null){ > dest.setFillBackgroundColor(getColor(sColor)); > } > > // data format > dest.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat(source.getDataFormatString())); > > // rotation > dest.setRotation(source.getRotation()); > > // others > dest.setShrinkToFit(source.getShrinkToFit()); > dest.setLocked(source.getLocked()); > dest.setHidden(source.getHidden()); > dest.setWrapText(source.getWrapText()); > } > > /** > * Local caching > * @param sColor > * @return > */ > private XSSFColor getColor(XSSFColor sColor) > { > if(sColor == null){ > return null; > } > > String aRgb = sColor.getARGBHex(); > XSSFColor color = colors.get(aRgb); > if(color == null){ > color = new XSSFColor(sColor.getRgb()); > colors.put(aRgb, color); > } > > return color; > } > > public void showMemoryUsage() > { > Runtime runtime = Runtime.getRuntime(); > DecimalFormat format = new DecimalFormat("#,##0"); > format.setRoundingMode(RoundingMode.HALF_UP); > > StringBuilder sb = new StringBuilder("["); > long maxMemory = runtime.maxMemory(); > long allocatedMemory = runtime.totalMemory(); > long freeMemory = runtime.freeMemory(); > > SimpleDateFormat stf = new SimpleDateFormat("HH:mm:ss"); > > sb.append(stf.format(new Date())); > sb.append("] Memory usage: "); > sb.append(format.format(allocatedMemory * 100.0 / maxMemory)); > sb.append("% Total: "); > sb.append(format.format(maxMemory / 1024 / 1024)); > sb.append("Mb, Allocated: "); > sb.append(format.format(allocatedMemory / 1024 / 1024)); > sb.append("Mb, Free: "); > sb.append(format.format(freeMemory / 1024 / 1024)); > sb.append("Mb"); > > System.out.println(sb.toString()); > } >} View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]