Java tutorial
/** * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you 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. */ package org.apache.metamodel.excel; import java.io.File; import java.io.InputStream; import java.io.OutputStream; import java.text.NumberFormat; import java.util.Date; import java.util.Iterator; import javax.xml.parsers.SAXParser; import javax.xml.parsers.SAXParserFactory; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.formula.FormulaParseException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Color; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.FontUnderline; import org.apache.poi.ss.usermodel.FormulaError; 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; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.metamodel.MetaModelException; import org.apache.metamodel.MetaModelHelper; import org.apache.metamodel.data.DataSet; import org.apache.metamodel.data.DataSetHeader; import org.apache.metamodel.data.DefaultRow; import org.apache.metamodel.data.EmptyDataSet; import org.apache.metamodel.data.Style; import org.apache.metamodel.data.Style.SizeUnit; import org.apache.metamodel.data.StyleBuilder; import org.apache.metamodel.query.SelectItem; import org.apache.metamodel.schema.Table; import org.apache.metamodel.util.Action; import org.apache.metamodel.util.DateUtils; import org.apache.metamodel.util.FileHelper; import org.apache.metamodel.util.FileResource; import org.apache.metamodel.util.FormatHelper; import org.apache.metamodel.util.Func; import org.apache.metamodel.util.InMemoryResource; import org.apache.metamodel.util.Resource; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.xml.sax.XMLReader; /** * Convenience/reusable methods for Excel workbook handling. */ final class ExcelUtils { private static final Logger logger = LoggerFactory.getLogger(ExcelUtils.class); private static final NumberFormat _numberFormat = FormatHelper.getUiNumberFormat(); private ExcelUtils() { // prevent instantiation } public static XMLReader createXmlReader() { try { SAXParserFactory saxFactory = SAXParserFactory.newInstance(); SAXParser saxParser = saxFactory.newSAXParser(); XMLReader sheetParser = saxParser.getXMLReader(); return sheetParser; } catch (Exception e) { throw new MetaModelException(e); } } public static Workbook readWorkbook(Resource resource) { if (!resource.isExists()) { // resource does not exist- create a blank workbook if (isXlsxFile(resource)) { return new SXSSFWorkbook(1000); } else { return new HSSFWorkbook(); } } if (resource instanceof FileResource) { final File file = ((FileResource) resource).getFile(); try { return WorkbookFactory.create(file); } catch (Exception e) { logger.error("Could not open workbook", e); throw new IllegalStateException("Could not open workbook", e); } } return resource.read(new Func<InputStream, Workbook>() { @Override public Workbook eval(InputStream inputStream) { try { return WorkbookFactory.create(inputStream); } catch (Exception e) { logger.error("Could not open workbook", e); throw new IllegalStateException("Could not open workbook", e); } } }); } public static boolean isXlsxFile(Resource resource) { if (resource == null) { return false; } return resource.getName().toLowerCase().endsWith(".xlsx"); } /** * Initializes a workbook instance based on a {@link ExcelDataContext}. * * @return a workbook instance based on the ExcelDataContext. */ public static Workbook readWorkbook(ExcelDataContext dataContext) { Resource resource = dataContext.getResource(); return readWorkbook(resource); } /** * Writes the {@link Workbook} to a {@link Resource}. The {@link Workbook} * will be closed as a result of this operation! * * @param dataContext * @param wb */ public static void writeAndCloseWorkbook(ExcelDataContext dataContext, final Workbook wb) { // first write to a temp file to avoid that workbook source is the same // as the target (will cause read+write cyclic overflow) final Resource realResource = dataContext.getResource(); final Resource tempResource = new InMemoryResource(realResource.getQualifiedPath()); tempResource.write(new Action<OutputStream>() { @Override public void run(OutputStream outputStream) throws Exception { wb.write(outputStream); } }); if (wb instanceof HSSFWorkbook && realResource instanceof FileResource && realResource.isExists()) { // TODO POI has a problem with closing a file-reference/channel // after wb.write() is invoked. See POI issue to be fixed: // https://bz.apache.org/bugzilla/show_bug.cgi?id=58480 System.gc(); System.runFinalization(); try { Thread.sleep(800); } catch (InterruptedException e) { } } FileHelper.safeClose(wb); FileHelper.copy(tempResource, realResource); } public static String getCellValue(Workbook wb, Cell cell) { if (cell == null) { return null; } final String cellCoordinate = "(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")"; final String result; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: result = null; break; case Cell.CELL_TYPE_BOOLEAN: result = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: String errorResult; try { byte errorCode = cell.getErrorCellValue(); FormulaError formulaError = FormulaError.forInt(errorCode); errorResult = formulaError.getString(); } catch (RuntimeException e) { logger.debug("Getting error code for {} failed!: {}", cellCoordinate, e.getMessage()); if (cell instanceof XSSFCell) { // hack to get error string, which is available String value = ((XSSFCell) cell).getErrorCellString(); errorResult = value; } else { logger.error("Couldn't handle unexpected error scenario in cell: " + cellCoordinate, e); throw e; } } result = errorResult; break; case Cell.CELL_TYPE_FORMULA: // result = cell.getCellFormula(); result = getFormulaCellValue(wb, cell); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date == null) { result = null; } else { result = DateUtils.createDateFormat().format(date); } } else { // TODO: Consider not formatting it, but simple using // Double.toString(...) result = _numberFormat.format(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: result = cell.getRichStringCellValue().getString(); break; default: throw new IllegalStateException("Unknown cell type: " + cell.getCellType()); } logger.debug("cell {} resolved to value: {}", cellCoordinate, result); return result; } private static String getFormulaCellValue(Workbook wb, Cell cell) { // first try with a cached/precalculated value try { double numericCellValue = cell.getNumericCellValue(); // TODO: Consider not formatting it, but simple using // Double.toString(...) return _numberFormat.format(numericCellValue); } catch (Exception e) { if (logger.isInfoEnabled()) { logger.info("Failed to fetch cached/precalculated formula value of cell: " + cell, e); } } // evaluate cell first, if possible try { if (logger.isInfoEnabled()) { logger.info("cell({},{}) is a formula. Attempting to evaluate: {}", new Object[] { cell.getRowIndex(), cell.getColumnIndex(), cell.getCellFormula() }); } final FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); // calculates the formula and puts it's value back into the cell final Cell evaluatedCell = evaluator.evaluateInCell(cell); return getCellValue(wb, evaluatedCell); } catch (RuntimeException e) { logger.warn("Exception occurred while evaluating formula at position ({},{}): {}", new Object[] { cell.getRowIndex(), cell.getColumnIndex(), e.getMessage() }); // Some exceptions we simply log - result will be then be the // actual formula if (e instanceof FormulaParseException) { logger.error("Parse exception occurred while evaluating cell formula: " + cell, e); } else if (e instanceof IllegalArgumentException) { logger.error("Illegal formula argument occurred while evaluating cell formula: " + cell, e); } else { logger.error("Unexpected exception occurred while evaluating cell formula: " + cell, e); } } // last resort: return the string formula return cell.getCellFormula(); } public static Style getCellStyle(Workbook workbook, Cell cell) { if (cell == null) { return Style.NO_STYLE; } final CellStyle cellStyle = cell.getCellStyle(); final short fontIndex = cellStyle.getFontIndex(); final Font font = workbook.getFontAt(fontIndex); final StyleBuilder styleBuilder = new StyleBuilder(); // Font bold, italic, underline if (font.getBoldweight() >= Font.BOLDWEIGHT_BOLD) { styleBuilder.bold(); } if (font.getItalic()) { styleBuilder.italic(); } if (font.getUnderline() != FontUnderline.NONE.getByteValue()) { styleBuilder.underline(); } // Font size final Font stdFont = workbook.getFontAt((short) 0); final short fontSize = font.getFontHeightInPoints(); if (stdFont.getFontHeightInPoints() != fontSize) { styleBuilder.fontSize(fontSize, SizeUnit.PT); } // Font color final short colorIndex = font.getColor(); if (font instanceof HSSFFont) { if (colorIndex != HSSFFont.COLOR_NORMAL) { final HSSFWorkbook wb = (HSSFWorkbook) workbook; HSSFColor color = wb.getCustomPalette().getColor(colorIndex); if (color != null) { short[] triplet = color.getTriplet(); styleBuilder.foreground(triplet); } } } else if (font instanceof XSSFFont) { XSSFFont xssfFont = (XSSFFont) font; XSSFColor color = xssfFont.getXSSFColor(); if (color != null) { String argbHex = color.getARGBHex(); if (argbHex != null) { styleBuilder.foreground(argbHex.substring(2)); } } } else { throw new IllegalStateException( "Unexpected font type: " + (font == null ? "null" : font.getClass()) + ")"); } // Background color if (cellStyle.getFillPattern() == 1) { Color color = cellStyle.getFillForegroundColorColor(); if (color instanceof HSSFColor) { short[] triplet = ((HSSFColor) color).getTriplet(); if (triplet != null) { styleBuilder.background(triplet); } } else if (color instanceof XSSFColor) { String argb = ((XSSFColor) color).getARGBHex(); if (argb != null) { styleBuilder.background(argb.substring(2)); } } else { throw new IllegalStateException( "Unexpected color type: " + (color == null ? "null" : color.getClass()) + ")"); } } // alignment switch (cellStyle.getAlignment()) { case CellStyle.ALIGN_LEFT: styleBuilder.leftAligned(); break; case CellStyle.ALIGN_RIGHT: styleBuilder.rightAligned(); break; case CellStyle.ALIGN_CENTER: styleBuilder.centerAligned(); break; case CellStyle.ALIGN_JUSTIFY: styleBuilder.justifyAligned(); break; } return styleBuilder.create(); } public static Iterator<Row> getRowIterator(Sheet sheet, ExcelConfiguration configuration, boolean jumpToDataRows) { final Iterator<Row> iterator; if (configuration.isSkipEmptyLines()) { iterator = sheet.rowIterator(); } else { iterator = new ZeroBasedRowIterator(sheet); } if (jumpToDataRows) { final int columnNameLineNumber = configuration.getColumnNameLineNumber(); if (columnNameLineNumber != ExcelConfiguration.NO_COLUMN_NAME_LINE) { // iterate past the column headers if (iterator.hasNext()) { iterator.next(); } for (int i = 1; i < columnNameLineNumber; i++) { if (iterator.hasNext()) { iterator.next(); } else { // no more rows! break; } } } } return iterator; } /** * Creates a MetaModel row based on an Excel row * * @param workbook * @param row * @param selectItems * select items of the columns in the table * @return */ public static DefaultRow createRow(Workbook workbook, Row row, DataSetHeader header) { final int size = header.size(); final String[] values = new String[size]; final Style[] styles = new Style[size]; if (row != null) { for (int i = 0; i < size; i++) { final int columnNumber = header.getSelectItem(i).getColumn().getColumnNumber(); final Cell cell = row.getCell(columnNumber); final String value = ExcelUtils.getCellValue(workbook, cell); final Style style = ExcelUtils.getCellStyle(workbook, cell); values[i] = value; styles[i] = style; } } return new DefaultRow(header, values, styles); } public static DataSet getDataSet(Workbook workbook, Sheet sheet, Table table, ExcelConfiguration configuration) { final SelectItem[] selectItems = MetaModelHelper.createSelectItems(table.getColumns()); final Iterator<Row> rowIterator = getRowIterator(sheet, configuration, true); if (!rowIterator.hasNext()) { // no more rows! FileHelper.safeClose(workbook); return new EmptyDataSet(selectItems); } final DataSet dataSet = new XlsDataSet(selectItems, workbook, rowIterator); return dataSet; } }