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.util.ArrayList; import java.util.Collections; import java.util.Date; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.FontUnderline; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; import org.apache.poi.xssf.model.StylesTable; 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.metamodel.data.Style; import org.apache.metamodel.data.Style.SizeUnit; import org.apache.metamodel.data.StyleBuilder; import org.apache.metamodel.util.DateUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.xml.sax.Attributes; import org.xml.sax.SAXException; import org.xml.sax.helpers.DefaultHandler; /** * XML handler for transforming a sheet into rows. Uses an * {@link XlsxRowCallback} to publish identified rows. */ final class XlsxSheetToRowsHandler extends DefaultHandler { private static final Logger logger = LoggerFactory.getLogger(XlsxSheetToRowsHandler.class); private static enum XssfDataType { BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, } // global variables private final XlsxRowCallback _callback; private final ExcelConfiguration _configuration; private final StylesTable _stylesTable; private final SharedStringsTable _sharedStringTable; // variables used to hold information about the current rows private int _rowNumber; private final List<String> _rowValues; private final List<Style> _styles; // variables used to hold information about the current visited cells private final StringBuilder _value; private final StyleBuilder _style; private boolean _inCell; private boolean _inFormula; private int _columnNumber; private XssfDataType _dataType; private int _formatIndex; private String _formatString; public XlsxSheetToRowsHandler(XlsxRowCallback callback, XSSFReader xssfReader, ExcelConfiguration configuration) throws Exception { _callback = callback; _configuration = configuration; _sharedStringTable = xssfReader.getSharedStringsTable(); _stylesTable = xssfReader.getStylesTable(); _value = new StringBuilder(); _style = new StyleBuilder(); _rowValues = new ArrayList<String>(); _styles = new ArrayList<Style>(); _rowNumber = -1; _inCell = false; _inFormula = false; } @Override public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException { if ("row".equals(qName)) { // element is a row // excel row numbers are 1-based int rowNumber = Integer.parseInt(attributes.getValue("r")); rowNumber = rowNumber - 1; if (_configuration.isSkipEmptyLines()) { _rowNumber++; } else { while (_rowNumber + 1 < rowNumber) { // empty lines are not skipped, so dispatch empty lines _rowNumber++; List<String> emptyValues = Collections.emptyList(); List<Style> emptyStyles = Collections.emptyList(); _callback.row(_rowNumber, emptyValues, emptyStyles); } _rowNumber = rowNumber; } } else if ("c".equals(qName)) { // element is a cell _inCell = true; final String r = attributes.getValue("r"); int firstDigit = -1; for (int c = 0; c < r.length(); ++c) { if (Character.isDigit(r.charAt(c))) { firstDigit = c; break; } } _columnNumber = nameToColumn(r.substring(0, firstDigit)); // Set up defaults. _dataType = XssfDataType.NUMBER; _formatIndex = -1; _formatString = null; final String cellType = attributes.getValue("t"); if ("b".equals(cellType)) { _dataType = XssfDataType.BOOL; } else if ("e".equals(cellType)) { _dataType = XssfDataType.ERROR; } else if ("inlineStr".equals(cellType)) { _dataType = XssfDataType.INLINESTR; } else if ("s".equals(cellType)) { _dataType = XssfDataType.SSTINDEX; } else if ("str".equals(cellType)) { _dataType = XssfDataType.FORMULA; } String cellStyleStr = attributes.getValue("s"); 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); configureStyle(style); if (_dataType == XssfDataType.NUMBER) { this._formatIndex = style.getDataFormat(); this._formatString = style.getDataFormatString(); if (this._formatString == null) { this._formatString = BuiltinFormats.getBuiltinFormat(this._formatIndex); } } } } else if (_inCell && "f".equals(qName)) { // skip the actual formula line _inFormula = true; } } private void configureStyle(XSSFCellStyle style) { XSSFFont font = style.getFont(); if (font.getBold()) { _style.bold(); } if (font.getItalic()) { _style.italic(); } if (font.getUnderline() != FontUnderline.NONE.getByteValue()) { _style.underline(); } if (style.getFillPatternEnum() == FillPatternType.SOLID_FOREGROUND) { XSSFColor fillForegroundXSSFColor = style.getFillForegroundXSSFColor(); String argb = fillForegroundXSSFColor.getARGBHex(); if (argb != null) { _style.background(argb.substring(2)); } } final XSSFFont stdFont = _stylesTable.getStyleAt(0).getFont(); final short fontHeight = style.getFont().getFontHeightInPoints(); if (stdFont.getFontHeightInPoints() != fontHeight) { _style.fontSize(fontHeight, SizeUnit.PT); } XSSFColor fontColor = style.getFont().getXSSFColor(); if (fontColor != null) { String argbHex = fontColor.getARGBHex(); if (argbHex != null) { _style.foreground(argbHex.substring(2)); } } switch (style.getAlignmentEnum()) { case LEFT: _style.leftAligned(); break; case RIGHT: _style.rightAligned(); break; case CENTER: _style.centerAligned(); break; case JUSTIFY: _style.justifyAligned(); break; default: // do nothing break; } } @Override public void endElement(String uri, String localName, String qName) throws SAXException { if ("row".equals(qName)) { // element was a row boolean next = _callback.row(_rowNumber, _rowValues, _styles); if (!next) { throw new XlsxStopParsingException(); } _rowValues.clear(); _styles.clear(); } else if ("c".equals(qName)) { // element was a cell _inCell = false; while (_rowValues.size() < _columnNumber) { _rowValues.add(null); _styles.add(Style.NO_STYLE); } _rowValues.add(createValue()); _styles.add(_style.create()); _value.setLength(0); _style.reset(); } else if (_inFormula && "f".equals(qName)) { // skip the actual formula line _inFormula = false; } } private String createValue() { if (_value.length() == 0) { return null; } switch (_dataType) { case BOOL: char first = _value.charAt(0); return first == '0' ? "false" : "true"; case ERROR: logger.warn("Error-cell occurred: {}", _value); return _value.toString(); case FORMULA: return _value.toString(); case INLINESTR: XSSFRichTextString rtsi = new XSSFRichTextString(_value.toString()); return rtsi.toString(); case SSTINDEX: String sstIndex = _value.toString(); int idx = Integer.parseInt(sstIndex); XSSFRichTextString rtss = new XSSFRichTextString(_sharedStringTable.getEntryAt(idx)); return rtss.toString(); case NUMBER: final String numberString = _value.toString(); if (_formatString != null) { DataFormatter formatter = getDataFormatter(); if (HSSFDateUtil.isADateFormat(_formatIndex, _formatString)) { Date date = DateUtil.getJavaDate(Double.parseDouble(numberString)); return DateUtils.createDateFormat().format(date); } return formatter.formatRawCellContents(Double.parseDouble(numberString), _formatIndex, _formatString); } else { if (numberString.endsWith(".0")) { // xlsx only stores doubles, so integers get ".0" appended // to them return numberString.substring(0, numberString.length() - 2); } return numberString; } default: logger.error("Unsupported data type: {}", _dataType); return ""; } } private DataFormatter getDataFormatter() { return new DataFormatter(); } @Override public void characters(char[] ch, int start, int length) throws SAXException { if (_inCell && !_inFormula) { _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; } }