com.opendoorlogistics.core.tables.io.PoiIO.java Source code

Java tutorial

Introduction

Here is the source code for com.opendoorlogistics.core.tables.io.PoiIO.java

Source

/*******************************************************************************
 * 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;
    // }

}