Java tutorial
/******************************************************************************* * Copyright (c) 2014 Open Door Logistics (www.opendoorlogistics.com) * All rights reserved. This program and the accompanying materials * are made available under the terms of the GNU Lesser Public License v3 * which accompanies this distribution, and is available at http://www.gnu.org/licenses/lgpl.txt ******************************************************************************/ package com.opendoorlogistics.core.tables.io; import java.awt.Dimension; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.List; import java.util.Random; import org.apache.commons.io.FilenameUtils; import org.apache.poi.POIXMLProperties; import org.apache.poi.hpsf.SummaryInformation; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; 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.util.TempFile; import org.apache.poi.util.TempFileCreationStrategy; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.opendoorlogistics.api.ExecutionReport; import com.opendoorlogistics.api.components.ProcessingApi; import com.opendoorlogistics.api.tables.ODLColumnType; import com.opendoorlogistics.api.tables.ODLDatastore; import com.opendoorlogistics.api.tables.ODLDatastoreAlterable; import com.opendoorlogistics.api.tables.ODLTableAlterable; import com.opendoorlogistics.api.tables.ODLTableDefinition; import com.opendoorlogistics.api.tables.ODLTableDefinitionAlterable; import com.opendoorlogistics.api.tables.ODLTableReadOnly; import com.opendoorlogistics.core.AppConstants; import com.opendoorlogistics.core.tables.ColumnValueProcessor; import com.opendoorlogistics.core.tables.ODLFactory; import com.opendoorlogistics.core.tables.io.SchemaIO.SchemaColumnDefinition; import com.opendoorlogistics.core.tables.memory.ODLDatastoreImpl; import com.opendoorlogistics.core.tables.utils.TableUtils; import com.opendoorlogistics.core.utils.UpdateTimer; import com.opendoorlogistics.core.utils.Version; import com.opendoorlogistics.core.utils.strings.StandardisedStringTreeMap; import com.opendoorlogistics.core.utils.strings.Strings; final public class PoiIO { private static boolean initialised = false; private static File poiTempFileDirectory; // http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx public static int MAX_CHAR_COUNT_IN_EXCEL_CELL = 32767; static String SCHEMA_SHEET_NAME = "#ODLSchema - DO NOT EDIT"; static final SimpleDateFormat ODL_TIME_FORMATTER = new SimpleDateFormat("HH:mm:ss.SSS"); static { initPOI(); } public static void initPOI() { if (!initialised) { // create a directory based on a random number so different instances of the application // should use different tmp directories (unless they're started at exactly the same nanosecond). Random random = new Random(); int val = random.nextInt(); poiTempFileDirectory = new File(System.getProperty("java.io.tmpdir"), "odlpoi" + val); TempFile.setTempFileCreationStrategy(new TempFileCreationStrategy() { @Override public File createTempFile(String prefix, String suffix) throws IOException { // check dir exists, make if doesn't if (!poiTempFileDirectory.exists()) { poiTempFileDirectory.mkdir(); poiTempFileDirectory.deleteOnExit(); } File newFile = File.createTempFile(prefix, suffix, poiTempFileDirectory); return newFile; } }); initialised = true; } } public static boolean exportDatastore(ODLDatastore<? extends ODLTableReadOnly> ds, File file, boolean xlsx, ProcessingApi processing, ExecutionReport report) { //tmpFileBugFix(); Workbook wb = null; SXSSFWorkbook sxssfwb = null; HSSFWorkbook hssfwb = null; if (xlsx == false) { hssfwb = new HSSFWorkbook(); hssfwb.createInformationProperties(); hssfwb.getSummaryInformation().setAuthor(AppConstants.ORG_NAME); wb = hssfwb; } else { // sxssfwb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk sxssfwb = new SXSSFWorkbook(null, 100, false, true); wb = sxssfwb; // XSSFWorkbook xssfWorkbook = new XSSFWorkbook(); /// POIXMLProperties xmlProps = sxssfwb. //POIXMLProperties.CoreProperties coreProps = xmlProps.getCoreProperties(); // coreProps.setCreator(AppConstants.ORG_NAME); // wb = xssfWorkbook; } try { // save schema addSchema(ds, wb); for (ODLTableDefinition table : TableUtils.getAlphabeticallySortedTables(ds)) { ODLTableReadOnly tro = (ODLTableReadOnly) table; Sheet sheet = wb.createSheet(tro.getName()); if (sheet == null) { return false; } exportTable(sheet, tro, 0, processing, report); if (processing != null && processing.isCancelled()) { return false; } } if (processing != null) { processing.postStatusMessage("Saving whole workbook to disk."); } saveWorkbook(file, wb); } catch (Exception e) { throw new RuntimeException(e); } finally { if (sxssfwb != null) { sxssfwb.dispose(); } if (hssfwb != null) { try { hssfwb.close(); } catch (Exception e2) { // TODO: handle exception } } } return true; } /** * See http://thinktibits.blogspot.co.uk/2012/12/Java-POI-XLS-XLSX-Change-Cell-Font-Color-Example.html * Currently only for xlsx * @param wb * @param sheet */ private static void styleHeader(Workbook wb, Sheet sheet) { if (XSSFWorkbook.class.isInstance(wb) && XSSFSheet.class.isInstance(sheet)) { XSSFWorkbook my_workbook = (XSSFWorkbook) wb; XSSFCellStyle my_style = my_workbook.createCellStyle(); XSSFFont my_font = my_workbook.createFont(); my_font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); my_style.setFont(my_font); Row row = sheet.getRow(0); if (row != null && row.getFirstCellNum() >= 0) { for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) { Cell cell = row.getCell(i); if (cell != null) { cell.setCellStyle(my_style); } } } } } // public static void clearSheet(Sheet sheet) { // while (sheet.getPhysicalNumberOfRows() > 0) { // Row row = sheet.getRow(sheet.getLastRowNum()); // sheet.removeRow(row); // } // } private static void addSchema(ODLDatastore<? extends ODLTableDefinition> ds, Workbook wb) { ODLTableReadOnly table = SchemaIO.createSchemaTable(ds); Sheet sheet = wb.createSheet(SCHEMA_SHEET_NAME); // write out key-value table Row row = sheet.createRow(0); row.createCell(0).setCellValue(SchemaIO.KEY_COLUMN); row.createCell(1).setCellValue(SchemaIO.VALUE_COLUMN); row = sheet.createRow(1); row.createCell(0).setCellValue(SchemaIO.APP_VERSION_KEY); row.createCell(1).setCellValue(AppConstants.getAppVersion().toString()); // write schema table exportTable(sheet, table, sheet.getLastRowNum() + 2, null, null); // hide the sheet from users wb.setSheetHidden(wb.getNumberOfSheets() - 1, Workbook.SHEET_STATE_VERY_HIDDEN); } // private static void exportTable(Sheet sheet, ODLTableReadOnly table, ExecutionReport report) { // exportTable(sheet, table, 0, report); // } private static void exportTable(Sheet sheet, ODLTableReadOnly table, int firstOutputRow, ProcessingApi processingApi, ExecutionReport report) { UpdateTimer timer = new UpdateTimer(250); int nbOversized = 0; // create header row int nc = table.getColumnCount(); Row header = sheet.createRow(firstOutputRow); for (int col = 0; col < nc; col++) { Cell cell = header.createCell(col); cell.setCellValue(table.getColumnName(col)); } // set header style styleHeader(sheet.getWorkbook(), sheet); // write data for (int srcRow = 0; srcRow < table.getRowCount(); srcRow++) { Row row = sheet.createRow(firstOutputRow + 1 + srcRow); for (int col = 0; col < nc; col++) { Cell cell = row.createCell(col); if (saveElementToCell(table, srcRow, col, cell) == SaveElementResult.OVERSIZED) { nbOversized++; } } if (processingApi != null && processingApi.isCancelled()) { return; } if (processingApi != null && timer.isUpdate()) { processingApi.postStatusMessage( "Saving - processed row " + (srcRow + 1) + " of sheet " + table.getName()); } } if (nbOversized > 0 && report != null) { report.log(getOversizedWarningMessage(nbOversized, table.getName())); } } public enum SaveElementResult { OK, OVERSIZED } private static SaveElementResult saveElementToCell(ODLTableReadOnly table, int row, int col, Cell cell) { boolean oversized = false; switch (table.getColumnType(col)) { case LONG: case DOUBLE: Number dVal = (Number) table.getValueAt(row, col); if (dVal != null) { cell.setCellValue(dVal.doubleValue()); cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else { cell.setCellValue((String) null); cell.setCellType(Cell.CELL_TYPE_BLANK); } break; default: String sval = TableUtils.getValueAsString(table, row, col); if (sval != null) { if (sval.length() >= MAX_CHAR_COUNT_IN_EXCEL_CELL) { oversized = true; } cell.setCellValue(sval.toString()); } else { cell.setCellValue((String) null); } cell.setCellType(Cell.CELL_TYPE_STRING); break; } return oversized ? SaveElementResult.OVERSIZED : SaveElementResult.OK; } private static String getOversizedWarningMessage(int nbOversized, String tableName) { String s = "Found " + nbOversized + " cell(s) in table \"" + tableName + "\" longer than maximum Excel cell length (" + MAX_CHAR_COUNT_IN_EXCEL_CELL + ")." + System.lineSeparator() + "This spreadsheet may not open correctly in Excel; Libreoffice or OpenOffice should be OK."; return s; } private static void saveWorkbook(File file, Workbook wb) { try { FileOutputStream fos = new FileOutputStream(file, false); wb.write(fos); fos.flush(); fos.close(); } catch (Throwable e) { throw new RuntimeException(e); } } // public static Workbook deepCopyWorkbook(Workbook wb) { // return fromBytes(toBytes(wb)); // } // public static byte[] toBytes(Workbook wb) { // try { // ByteArrayOutputStream bos = new ByteArrayOutputStream(); // wb.write(bos); // byte[] bytes = bos.toByteArray(); // return bytes; // } catch (Throwable e) { // throw new RuntimeException(e); // } // } // public static Workbook fromBytes(byte[] bytes) { // try { // ByteArrayInputStream bis = new ByteArrayInputStream(bytes); // return WorkbookFactory.create(bis); // } catch (Throwable e) { // throw new RuntimeException(e); // } // } // public static boolean isXLSX(Workbook wb) { // return XSSFWorkbook.class.isInstance(wb); // } private static Dimension getBoundingBox(Sheet sheet) { Dimension ret = new Dimension(0, sheet.getLastRowNum() + 1); for (int i = 0; i < ret.height; i++) { Row row = sheet.getRow(i); if (row != null) { ret.width = Math.max(ret.width, row.getLastCellNum()); } } return ret; } public static ODLDatastoreAlterable<ODLTableAlterable> importExcel(File file, ProcessingApi processingApi, ExecutionReport report) { ODLDatastoreAlterable<ODLTableAlterable> ds = ODLFactory.createAlterable(); // FileInputStream fis=null; // try { // fis = new FileInputStream(file); // importExcel(fis, ds, report); // } catch (Exception e) { // if(report!=null){ // report.setFailed(e); // } // } // finally{ // if(fis!=null){ // try { // fis.close(); // } catch (Exception e2) { // if(report!=null){ // report.setFailed(e2); // } // } // } // } // load xlsx using our xml parser which can handle much larger files String ext = FilenameUtils.getExtension(file.getAbsolutePath()); if (Strings.equalsStd(ext, "xlsx")) { return XmlParserLoader.importExcel(file, processingApi, report); } try (FileInputStream fis = new FileInputStream(file)) { return importExcel(fis, report); } catch (Exception e) { if (report != null) { report.setFailed(e); } } return ds; } public static ODLDatastoreAlterable<ODLTableAlterable> importExcel(InputStream stream, ExecutionReport report) { //tmpFileBugFix(); ODLDatastoreAlterable<ODLTableAlterable> ds = ODLFactory.createAlterable(); Workbook wb = null; try { wb = WorkbookFactory.create(stream); String author = getAuthor(wb); if (author != null && Strings.equalsStd(author, AppConstants.ORG_NAME)) { ds.setFlags(ds.getFlags() | ODLDatastore.FLAG_FILE_CREATED_BY_ODL); } } catch (Throwable e) { throw new RuntimeException(e); } // look for the schema; remove it from the workbook to simplify the later workbook updating code // (the schema gets held by the datastore structure anyway) SchemaSheetInformation info = null; for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); if (Strings.equalsStd(sheet.getSheetName(), SCHEMA_SHEET_NAME)) { info = importSchemaTables(sheet, report); wb.removeSheetAt(i); break; } } for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); ODLTableAlterable table = ds.createTable(sheet.getSheetName(), -1); importSheet(table, sheet, info != null ? info.schema : null, false); } return ds; } private static void importSheet(ODLTableAlterable table, Sheet sheet, SchemaIO schema, boolean isSchema) { Dimension size = getBoundingBox(sheet); importSheetSubset(table, sheet, schema, isSchema, 0, size.height - 1, size.width); } static class SchemaSheetInformation { SchemaIO schema; StandardisedStringTreeMap<String> keyValues; Version appVersion; } /** * Schema table can contain multiple tables... * @param sheet */ private static SchemaSheetInformation importSchemaTables(Sheet sheet, ExecutionReport report) { List<ODLTableReadOnly> tables = new ArrayList<>(); // schema tables are separated by empty rows int lastRow = sheet.getLastRowNum(); int firstRow = sheet.getFirstRowNum(); int firstNonEmptyRow = -1; int nbCols = 0; for (int x = firstRow; x <= lastRow; x++) { // check for completely empty row Row row = sheet.getRow(x); boolean isEmptyRow = true; for (int y = 0; row != null && y <= row.getLastCellNum(); y++) { if (isEmptyCell(row, y) == false) { isEmptyRow = false; } } if (isEmptyRow || x == lastRow) { // dump table if row was empty or on last row, but we previously had a non empty row if (firstNonEmptyRow != -1) { ODLDatastoreAlterable<ODLTableAlterable> tmpDs = ODLDatastoreImpl.alterableFactory.create(); ODLTableAlterable table = tmpDs.createTable(sheet.getSheetName(), -1); importSheetSubset(table, sheet, null, true, firstNonEmptyRow, isEmptyRow ? x - 1 : x, nbCols); tables.add(table); } firstNonEmptyRow = -1; } else if (firstNonEmptyRow == -1) { // initialise table if we've just found the first non empty row firstNonEmptyRow = x; nbCols = 0; for (int y = 0; y <= row.getLastCellNum(); y++) { if (isEmptyCell(row, y)) { break; } else { nbCols = y + 1; } } } } return readSchemaFromODLTables(tables, report); } /** * @param tables * @param report * @return */ static SchemaSheetInformation readSchemaFromODLTables(List<ODLTableReadOnly> tables, ExecutionReport report) { SchemaSheetInformation ret = new SchemaSheetInformation(); if (tables.size() == 1) { // schema table ret.schema = SchemaIO.load(tables.get(0), report); } else if (tables.size() > 1) { // first table is key value map ret.keyValues = new StandardisedStringTreeMap<>(false); ODLTableReadOnly kvTable = tables.get(0); for (int i = 0; i < kvTable.getRowCount(); i++) { if (kvTable.getValueAt(i, 0) != null) { String key = kvTable.getValueAt(i, 0).toString(); String val = null; if (kvTable.getValueAt(i, 1) != null) { val = kvTable.getValueAt(i, 1).toString(); } ret.keyValues.put(key, val); } } // read application version String appVersion = ret.keyValues.get(SchemaIO.APP_VERSION_KEY); if (appVersion != null) { ret.appVersion = new Version(appVersion); // if app version is lower than current, do any required processing here before reading schema if (ret.appVersion.compareTo(AppConstants.getAppVersion()) < 0) { } } // schema table ret.schema = SchemaIO.load(tables.get(1), report); } return ret; } private static boolean isEmptyCell(Row row, int col) { String value = getFormulaSafeTextValue(row.getCell(col)); boolean isEmpty = Strings.isEmpty(value); return isEmpty; } /** * Import the sheet and return key-values if its a schema * @param ds * @param sheet * @param schema * @param isSchemaSheet * @return */ private static void importSheetSubset(ODLTableAlterable table, Sheet sheet, SchemaIO schema, boolean isSchemaSheet, int firstRow, int lastRow, int nbCols) { // get column names Row header = sheet.getRow(firstRow); for (int col = 0; col < nbCols; col++) { // try getting schema definition for the column String name = null; SchemaColumnDefinition dfn = null; if (header != null) { name = getFormulaSafeTextValue(header.getCell(col)); if (name != null && schema != null) { dfn = schema.findDefinition(sheet.getSheetName(), name); } } name = getValidNewColumnName(name, table); // use the schema column definition if we have one if (dfn != null) { addColumnFromDfn(dfn, name, col, table); } else { // analyse the other rows for a 'best guess' type ODLColumnType chosenType = ODLColumnType.STRING; if (isSchemaSheet == false) { ColumnTypeEstimator typeEstimator = new ColumnTypeEstimator(); for (int rowIndx = firstRow + 1; rowIndx <= lastRow; rowIndx++) { Row row = sheet.getRow(rowIndx); String value = getFormulaSafeTextValue(row.getCell(col)); typeEstimator.processValue(value); } chosenType = typeEstimator.getEstimatedType(); } table.addColumn(col, name, chosenType, 0); } } // load all other rows for (int rowIndx = firstRow + 1; rowIndx <= lastRow; rowIndx++) { Row row = sheet.getRow(rowIndx); int outRowIndx = table.createEmptyRow(rowIndx); for (int col = 0; col < nbCols; col++) { String value = getFormulaSafeTextValue(row.getCell(col)); table.setValueAt(value, outRowIndx, col); } } } /** * @param dfn * @param name * @param colId * @param table */ static void addColumnFromDfn(SchemaColumnDefinition dfn, String name, int colId, ODLTableDefinitionAlterable table) { // get flags long flags = 0; try { flags = Long.parseLong(dfn.getFlags()); } catch (Throwable e) { } // get type ODLColumnType type = SchemaIO.getOdlColumnType(dfn); // create column table.addColumn(colId, name, type, flags); int colIndex = table.getColumnCount() - 1; // set default value if (Strings.isEmpty(dfn.getDefaultValue()) == false) { Object val = ColumnValueProcessor.convertToMe(type, dfn.getDefaultValue()); if (val != null) { table.setColumnDefaultValue(colIndex, val); } } // set description table.setColumnDescription(colIndex, dfn.getDescription()); // set tags if (dfn.getTags() != null) { String[] split = dfn.getTags().split(","); table.setColumnTags(colIndex, Strings.toTreeSet(split)); } } static String getValidNewColumnName(String name, ODLTableDefinition table) { if (Strings.isEmpty(name)) { name = "Auto-name"; } if (TableUtils.findColumnIndx(table, name, true) != -1) { name = TableUtils.getUniqueNumberedColumnName(name, table); } return name; } private static String getAuthor(Workbook wb) { if (HSSFWorkbook.class.isInstance(wb)) { HSSFWorkbook hssf = (HSSFWorkbook) wb; SummaryInformation info = hssf.getSummaryInformation(); if (info != null) { return info.getAuthor(); } } else if (XSSFWorkbook.class.isInstance(wb)) { XSSFWorkbook xssf = (XSSFWorkbook) wb; POIXMLProperties xmlProps = xssf.getProperties(); if (xmlProps != null) { POIXMLProperties.CoreProperties coreProps = xmlProps.getCoreProperties(); if (coreProps != null) { return coreProps.getCreator(); } } } return null; } private static String getFormulaSafeTextValue(Cell cell) { if (cell == null) { return null; } if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { return getTextValue(cell, cell.getCachedFormulaResultType()); } else { return getTextValue(cell, cell.getCellType()); } } private static String getTextValue(Cell cell, int treatAsCellType) { if (cell == null) { return null; } switch (treatAsCellType) { case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { Calendar cal = Calendar.getInstance(); cal.setTime(date); @SuppressWarnings("deprecation") int year = date.getYear(); if (year == -1) { // equivalent to 1899 which is the first data .. assume its a time String s = ODL_TIME_FORMATTER.format(date); return s; } // System.out.println(year); } return cell.getDateCellValue().toString(); } else { String ret = Double.toString(cell.getNumericCellValue()); if (ret.endsWith(".0")) { ret = ret.substring(0, ret.length() - 2); } return ret; } case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() ? "T" : "F"; case Cell.CELL_TYPE_FORMULA: return cell.getCellFormula(); case Cell.CELL_TYPE_BLANK: return null; } return ""; } // private static String getExcelFormatCode(ODLColumnType type){ // switch(type){ // case STRING: // return "General"; // // case LONG: // return "0"; // // case DOUBLE: // return "#,##0.000"; // // case COLOUR: // return ";;;\"COLOUR(\"@\")\""; // // case IMAGE: // return ";;;\"IMAGE(\"@\")\""; // // default: // return "General"; // } // // } // private static ODLColumnType getColumnTypeFromExcelFormatCode(String code){ // code = code.trim().toLowerCase(); // if(code.contains("image")){ // return ODLColumnType.IMAGE; // } // if(code.contains("colour") || code.contains("color")){ // return ODLColumnType.COLOUR; // } // // if(code.contains("@")){ // return ODLColumnType.STRING; // } // // if(code.contains(".")){ // return ODLColumnType.DOUBLE; // } // // if(code.equals("general")){ // return ODLColumnType.STRING; // } // // if(code.contains("#") || code.contains("0")){ // return ODLColumnType.LONG; // } // // return ODLColumnType.STRING; // } }