Java tutorial
/* * Copyright 2013 ENERKO Informatik GmbH * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ package de.enerko.reports2.engine; import java.io.BufferedInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.text.DateFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Collections; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Locale; import java.util.Map; import java.util.Set; import java.util.logging.Level; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.formula.IStabilityClassifier; import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.ClientAnchor; import org.apache.poi.ss.usermodel.Comment; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.Drawing; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; /** * Implements a report on the basis of Apache HSSF.<br> * If any numerical, date or datetime based cell leads to a {@link ParseException} or * {@link NumberFormatException} the whole report is canceled.<br> * At the end of the report all functions are evaluated. The report will be created * nevertheless if evaluation fails (Excel will then evaluate the formulas). * @author Michael J. Simons, 2013-06-18 */ public class Report { /** * If a cell has {@link CellDefinition#sheetname} set to this value, * the sheet with the name {@link CellDefinition#value} will be hidden */ public final static String HIDE_SHEET_CELL = "__HIDE_SHEET__"; /** * If a cell has {@link CellDefinition#sheetname} set to this value, * the sheet with the name {@link CellDefinition#value} will be deleted */ public final static String DELETE_SHEET_CELL = "__DELETE_SHEET__"; /** * If a cell has {@link CellDefinition#sheetname} set to this value * the sheet will be cloned. __CLONE_SHEET__"source"_as_"target" */ public final static Pattern CLONE_SHEET_CELL = Pattern .compile("__CLONE_SHEET__\"(\\w+[\\w- ]+\\w+)\"_as_\"(\\w+[\\w- ]+\\w+)\""); public final static Map<String, SimpleDateFormat> DATE_FORMATS_SQL; public final static Map<String, String> DATE_FORMATS_EXCEL; public final static Map<Integer, String> IMPORTABLE_CELL_TYPES; public final static DateFormat DATEFORMAT_OUT = new SimpleDateFormat("dd.MM.yyyy HH:mm", Locale.GERMAN); static { final HashMap<String, SimpleDateFormat> dateFormatsSqlTmp = new HashMap<String, SimpleDateFormat>(); dateFormatsSqlTmp.put("date", ConcreteArgument.dateFormat); dateFormatsSqlTmp.put("datetime", ConcreteArgument.dateTimeFormat); DATE_FORMATS_SQL = Collections.unmodifiableMap(dateFormatsSqlTmp); final HashMap<String, String> dateFormatsExcel = new HashMap<String, String>(); dateFormatsExcel.put("date", "dd/mm/yyyy"); dateFormatsExcel.put("datetime", "dd/mm/yyyy HH:mm"); DATE_FORMATS_EXCEL = Collections.unmodifiableMap(dateFormatsExcel); final HashMap<Integer, String> importableCellTypes = new HashMap<Integer, String>(); importableCellTypes.put(new Integer(Cell.CELL_TYPE_STRING), "string"); importableCellTypes.put(new Integer(Cell.CELL_TYPE_NUMERIC), "number"); importableCellTypes.put(new Integer(Cell.CELL_TYPE_FORMULA), "number"); IMPORTABLE_CELL_TYPES = Collections.unmodifiableMap(importableCellTypes); } private final Workbook workbook; /** * There is a maximum number of 4000 cell styles to HSSF. As each number gets formatted, this isn't much * so formatted cell styles are cached */ private final Map<String, CellStyle> formatCache = new HashMap<String, CellStyle>(); Report(final ReportSource reportSource, UDFFinder customFunctions) { this(reportSource, customFunctions, null); } Report(final ReportSource reportSource, UDFFinder customFunctions, final InputStream template) { if (template == null) this.workbook = new HSSFWorkbook(); else try { this.workbook = new HSSFWorkbook(new BufferedInputStream(template)); } catch (IOException e) { throw new RuntimeException("Could not load template for report!"); } if (customFunctions != null) this.workbook.addToolPack(customFunctions); final Set<String> sheetsToHide = new HashSet<String>(); final Set<String> sheetsToDelete = new HashSet<String>(); String previousSheetName = null; Sheet sheet = null; // Iterator over all celldefinitions // this doesn't compile inside Oracle Database VM. You need to import the compiled classes // or use reportSource.iterator() directly for (CellDefinition cellDefinition : reportSource) { Matcher m = null; if (HIDE_SHEET_CELL.equals(cellDefinition.sheetname)) { sheetsToHide.add(cellDefinition.value); } else if (DELETE_SHEET_CELL.equals(cellDefinition.sheetname)) { sheetsToDelete.add(cellDefinition.value); } else if ((m = CLONE_SHEET_CELL.matcher(cellDefinition.sheetname)).matches()) { final String sourceName = m.group(1); final String targetName = m.group(2); final Sheet target = workbook.cloneSheet(workbook.getSheetIndex(sourceName)); workbook.setSheetName(workbook.getSheetIndex(target), targetName); } else { // Create and cache the current sheet. if (previousSheetName == null || !previousSheetName.equals(cellDefinition.sheetname)) { previousSheetName = cellDefinition.sheetname; sheet = getSheet(workbook, cellDefinition.sheetname); } // create, fill and add cell this.addCell(workbook, sheet, cellDefinition); } } // Evaluate all formulas try { final FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); formulaEvaluator.clearAllCachedResultValues(); formulaEvaluator.evaluateAll(); } catch (Exception e) { } // Hide and delete sheets for (String sheetName : sheetsToHide) workbook.setSheetHidden(workbook.getSheetIndex(sheetName), true); for (String sheetName : sheetsToDelete) workbook.removeSheetAt(workbook.getSheetIndex(sheetName)); } Report(final InputStream workbook, UDFFinder customFunctions) { try { this.workbook = new HSSFWorkbook(new BufferedInputStream(workbook)); } catch (IOException e) { throw new RuntimeException("Could not load template for report!"); } if (customFunctions != null) this.workbook.addToolPack(customFunctions); } /** * Writes the report into the given {@link OutputStream}, flushes and closes the stream. * @param out * @throws IOException */ public void write(final OutputStream out) throws IOException { this.workbook.write(out); out.flush(); out.close(); } public List<CellDefinition> evaluateWorkbook() { final List<CellDefinition> rv = new ArrayList<CellDefinition>(); boolean reevaluate = false; if (workbook instanceof HSSFWorkbook) { try { workbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); } catch (Exception e) { reevaluate = true; } } final FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook, IStabilityClassifier.TOTALLY_IMMUTABLE); formulaEvaluator.clearAllCachedResultValues(); for (int i = 0; i < workbook.getNumberOfSheets(); ++i) { final Sheet sheet = workbook.getSheetAt(i); for (Row row : sheet) { for (Cell cell : row) { if (reevaluate && cell.getCellType() == Cell.CELL_TYPE_FORMULA) { try { formulaEvaluator.evaluateFormulaCell(cell); } catch (Exception e) { ReportEngine.logger.log(Level.WARNING, String.format("Could not evaluate formula '%s' in cell %s on sheet '%s': %s", cell.getCellFormula(), CellReferenceHelper .getCellReference(cell.getColumnIndex(), row.getRowNum()), sheet.getSheetName(), e.getMessage())); } } final CellDefinition cellDefinition = IMPORTABLE_CELL_TYPES.containsKey( new Integer(cell.getCellType())) ? new CellDefinition(sheet.getSheetName(), cell) : null; if (cellDefinition != null) rv.add(cellDefinition); } } } return rv; } /** * Create a new {@link Sheet} if the sheet with the given name doesn't exist, * otherwise returns the existing sheet. * @param workbook * @param name * @return Existing or newly created sheet */ private Sheet getSheet(final Workbook workbook, final String name) { final String validName = name.replaceAll("[\\\\/\\?\\*\\[\\]]", "_"); Sheet sheet = workbook.getSheet(validName); if (sheet == null) sheet = workbook.createSheet(validName); return sheet; } /** * This method adds a new cell to the sheet of a workbook. It could * (together with {@link #fill(Workbook, Cell, String, String, boolean)}) be moved to * the {@link CellDefinition} itself, but that would mean that the {@link CellDefinition} is * tied to a specific Excel API. Having those methods here allows the Report to become * an interface if a second engine (i.e. JXL) should be added in the future. * @param workbook * @param sheet * @param cellDefinition */ private void addCell(final Workbook workbook, final Sheet sheet, final CellDefinition cellDefinition) { final int columnNum = cellDefinition.column, rowNum = cellDefinition.row; Row row = sheet.getRow(rowNum); if (row == null) row = sheet.createRow(rowNum); Cell cell = row.getCell(columnNum); // If the cell already exists and is no blank cell // it will be used including all formating if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { cell = fill(workbook, cell, cellDefinition, false); } // Otherwise a new cell will be created, the datatype set and // optionally a format will be created else { cell = fill(workbook, row.createCell(columnNum), cellDefinition, true); final Sheet referenceSheet; if (cellDefinition.getReferenceCell() != null && (referenceSheet = workbook.getSheet(cellDefinition.getReferenceCell().sheetname)) != null) { final Row referenceRow = referenceSheet.getRow(cellDefinition.getReferenceCell().row); final Cell referenceCell = referenceRow == null ? null : referenceRow.getCell(cellDefinition.getReferenceCell().column); if (referenceCell != null && referenceCell.getCellStyle() != null) cell.setCellStyle(referenceCell.getCellStyle()); } } // Add an optional comment if (cellDefinition.hasComment()) { final CreationHelper factory = workbook.getCreationHelper(); final Drawing drawing = sheet.createDrawingPatriarch(); final ClientAnchor commentAnchor = factory.createClientAnchor(); final int col1 = cellDefinition.comment.column == null ? cell.getColumnIndex() + 1 : cellDefinition.comment.column; final int row1 = cellDefinition.comment.row == null ? cell.getRowIndex() : cellDefinition.comment.row; commentAnchor.setCol1(col1); commentAnchor.setRow1(row1); commentAnchor.setCol2(col1 + Math.max(1, cellDefinition.comment.width)); commentAnchor.setRow2(row1 + Math.max(1, cellDefinition.comment.height)); final Comment comment = drawing.createCellComment(commentAnchor); comment.setString(factory.createRichTextString(cellDefinition.comment.text)); comment.setAuthor(cellDefinition.comment.author); comment.setVisible(cellDefinition.comment.visible); cell.setCellComment(comment); } } private CellStyle getFormat(final Workbook workbook, final Cell cell, String type, String value) { String format = (String) DATE_FORMATS_EXCEL.get(type.toLowerCase()); // Type is number if (format == null) { final String[] hlp = value.split("@@"); format = hlp.length > 1 ? hlp[1] : "0.00####"; } CellStyle cellStyle = formatCache.get(format); if (cellStyle == null) { cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(workbook.createDataFormat().getFormat(format)); formatCache.put(format, cellStyle); } return cellStyle; } private SimpleDateFormat getDateFormatSql(String type) { return ((SimpleDateFormat) DATE_FORMATS_SQL.get(type.toLowerCase())); } private Cell fill(final Workbook workbook, Cell tmp, final CellDefinition cellDefinition, boolean setType) { final String type = cellDefinition.getType(); if (type.equalsIgnoreCase("string")) { if (setType) tmp.setCellType(Cell.CELL_TYPE_STRING); tmp.setCellValue(cellDefinition.value); } else if (type.equalsIgnoreCase("number")) { if (setType) { tmp.setCellType(Cell.CELL_TYPE_NUMERIC); tmp.setCellStyle(getFormat(workbook, tmp, type, cellDefinition.value)); } try { tmp.setCellValue(Double.parseDouble(cellDefinition.value.split("@@")[0])); } catch (NumberFormatException e) { throw new RuntimeException(String.format("Could not parse value \"%s\" for numeric cell %dx%d!", cellDefinition.value, tmp.getColumnIndex(), tmp.getRowIndex())); } } else if (type.equalsIgnoreCase("date") || type.equalsIgnoreCase("datetime")) { if (setType) { tmp.setCellType(Cell.CELL_TYPE_NUMERIC); tmp.setCellStyle(getFormat(workbook, tmp, type, cellDefinition.value)); } try { tmp.setCellValue(getDateFormatSql(type).parse(cellDefinition.value)); } catch (ParseException e) { throw new RuntimeException( String.format("Could not parse value \"%s\" for date/datetime cell %dx%d!", cellDefinition.value, tmp.getColumnIndex(), tmp.getRowIndex())); } } else if (type.equalsIgnoreCase("formula")) { if (setType) tmp.setCellType(Cell.CELL_TYPE_FORMULA); tmp.setCellFormula(cellDefinition.value); } else throw new RuntimeException("Invalid type " + type); return tmp; } }