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 static de.enerko.reports2.utils.Types.numberToInteger; import java.lang.reflect.Method; import java.sql.SQLException; import java.sql.Struct; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.ss.usermodel.Cell; /** * Represents a worksheet cell and corresponds with the * PL/SQL object type t_hre_cell_definition * @author Michael J. Simons, 2013-06-17 */ public class CellDefinition { public static CellDefinition fromStruct(final Struct struct) throws SQLException { CellDefinition rv = null; if (struct != null) { final Object[] attributes = struct.getAttributes(); rv = new CellDefinition((String) attributes[0], numberToInteger((Number) attributes[1]), numberToInteger((Number) attributes[2]), (String) attributes[3], (String) attributes[4], (String) attributes[5], CommentDefinition.fromStruct((Struct) attributes[6])); } return rv; } /** * Points to another cell by sheetname, columan and row */ public static class CellPointer { public final String sheetname; public final int column; public final int row; public CellPointer(String sheetname, int column, int row) { this.sheetname = sheetname; this.column = column; this.row = row; } @Override public String toString() { return "CellPointer [sheetname=" + sheetname + ", column=" + column + ", row=" + row + "]"; } } private static class CellValue { public final String type; public final String representation; public CellValue(String type, String representation) { this.type = type; this.representation = representation; } } /** Pattern to split the {@link #type} into the actual type and a reference cell */ public final static Pattern FORMAT_PATTERN = Pattern .compile("(\\w+)(\\s*;\\s*\"([^\"]+)\"\\s*(\\w{1,3}\\d{1,}))?"); /** The name of the sheet inside the Excel document */ public final String sheetname; /** Column Index (0-based) */ public final int column; /** Row Index (0-based) */ public final int row; /** Cellreference ("A1" notation), only used for output) */ public final String name; /** Contains either type or type and a reference cell as 'datentyp; "SHEETNAME" CELLREFERENCE' */ private final String type; /** A string representation of the value */ public final String value; /** An optional cell comment */ public final CommentDefinition comment; /** Actual used datatype */ private String actualType; /** A reference cell */ private CellPointer referenceCell; public CellDefinition(String sheetname, int column, int row, String name, String type, String value) { this(sheetname, column, row, name, type, value, null); } public CellDefinition(String sheetname, int column, int row, String name, String type, String value, CommentDefinition comment) { this.sheetname = sheetname; this.column = column; this.row = row; this.name = name; this.type = type; this.value = value; this.comment = comment; } public CellDefinition(final String sheetname, final Cell cell) { final int ct = cell.getCellType(); Method m = null; try { m = this.getClass().getDeclaredMethod("parse_" + Report.IMPORTABLE_CELL_TYPES.get(new Integer(ct)), new Class[] { Cell.class }); } catch (Exception e) { e.printStackTrace(); } finally { if (m == null) throw new RuntimeException("Invalid type " + ct); } try { final CellValue cellValue = (CellValue) m.invoke(this, new Object[] { cell }); this.sheetname = sheetname; this.column = cell.getColumnIndex(); this.row = cell.getRowIndex(); this.name = CellReferenceHelper.getCellReference(cell.getColumnIndex(), cell.getRowIndex()); this.type = cellValue.type; this.value = cellValue.representation; if (cell.getCellComment() == null || cell.getCellComment().getString() == null) this.comment = null; else this.comment = new CommentDefinition(cell.getCellComment()); } catch (Exception e) { throw new RuntimeException(e); } } protected CellValue parse_string(Cell in) { return new CellValue("string", in.getStringCellValue()); } protected CellValue parse_number(Cell in) { CellValue rv = null; try { if (HSSFDateUtil.isCellDateFormatted(in)) { rv = new CellValue("datetime", Report.DATEFORMAT_OUT.format(in.getDateCellValue())); } else { rv = new CellValue("number", Double.toString(in.getNumericCellValue())); } } catch (IllegalStateException e) { // Siehe Dokumentation getNumericCellValue rv = new CellValue("string", in.getStringCellValue()); } return rv; } public String getType() { if (this.actualType == null) this.computeActualTypeAndReferenceCell(); return this.actualType; } public CellPointer getReferenceCell() { if (this.actualType == null) this.computeActualTypeAndReferenceCell(); return referenceCell; } public void setReferenceCell(CellPointer referenceCell) { this.referenceCell = referenceCell; } private void computeActualTypeAndReferenceCell() { final Matcher m = FORMAT_PATTERN.matcher(this.type); if (!m.matches()) throw new RuntimeException("Invalid type definition: " + type); this.actualType = m.group(1); this.referenceCell = m.group(2) == null ? null : new CellPointer(m.group(3), CellReferenceHelper.getColumn(m.group(4)), CellReferenceHelper.getRow(m.group(4))); } public Object[] toSQLStructObject() { return new Object[] { this.sheetname, this.column, this.row, this.name, this.type, this.value, this.comment == null ? null : this.comment.toSQLStructObject() }; } public boolean hasComment() { return this.comment != null && this.comment.text != null && this.comment.text.trim().length() != 0; } }