From 17ed092eb78edb4960900ad2fe4aaedf4898fc05 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Andreas=20M=C3=BCller?= Date: Fri, 12 Dec 2014 16:47:09 +0000 Subject: [PATCH] add poi-ooxml and upgrade to 3.10-FINAL --- cdmlib-commons/pom.xml | 9 +++++- .../eu/etaxonomy/cdm/common/ExcelUtils.java | 30 +++++++++++-------- .../io/excel/stream/ExcelRecordStream.java | 18 +++++------ .../io/excel/stream/ExcelStreamImport.java | 9 +++--- .../excel/stream/ExcelToStreamConverter.java | 18 ++++++----- .../excel/in/SpecimenSythesysExcelImport.java | 6 ++-- pom.xml | 14 ++++++++- 7 files changed, 65 insertions(+), 39 deletions(-) diff --git a/cdmlib-commons/pom.xml b/cdmlib-commons/pom.xml index 4519ba9f38..e0f2b23193 100644 --- a/cdmlib-commons/pom.xml +++ b/cdmlib-commons/pom.xml @@ -34,7 +34,14 @@ org.apache.poi poi - + + org.apache.poi + poi-ooxml-schemas + + + org.apache.poi + poi-ooxml + commons-lang commons-lang diff --git a/cdmlib-commons/src/main/java/eu/etaxonomy/cdm/common/ExcelUtils.java b/cdmlib-commons/src/main/java/eu/etaxonomy/cdm/common/ExcelUtils.java index 806880c7c0..5a783a3fee 100644 --- a/cdmlib-commons/src/main/java/eu/etaxonomy/cdm/common/ExcelUtils.java +++ b/cdmlib-commons/src/main/java/eu/etaxonomy/cdm/common/ExcelUtils.java @@ -22,10 +22,11 @@ import java.util.Locale; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFDataFormatter; -import org.apache.poi.hssf.usermodel.HSSFRow; -import org.apache.poi.hssf.usermodel.HSSFSheet; -import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.poifs.filesystem.POIFSFileSystem; +import org.apache.poi.ss.usermodel.Cell; +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; /** * @author n.hoffmann @@ -47,9 +48,12 @@ public class ExcelUtils { ArrayList> recordList = new ArrayList>(); try { - POIFSFileSystem fs = new POIFSFileSystem(UriUtils.getInputStream(uri)); - HSSFWorkbook wb = new HSSFWorkbook(fs); - HSSFSheet sheet; +// POIFSFileSystem fs = new POIFSFileSystem(UriUtils.getInputStream(uri)); +// HSSFWorkbook wb = new HSSFWorkbook(fs); + + Workbook wb = WorkbookFactory.create(UriUtils.getInputStream(uri)); + + Sheet sheet; if (worksheetName == null){ sheet = wb.getSheetAt(0); }else{ @@ -61,8 +65,8 @@ public class ExcelUtils { logger.debug(worksheetName + " not provided!"); } }else{ - HSSFRow row; - HSSFCell cell; + Row row; + Cell cell; int rows; // Number of rows rows = sheet.getPhysicalNumberOfRows(); @@ -131,7 +135,7 @@ public class ExcelUtils { } - public static String getCellValue(HSSFCell cell) { + public static String getCellValue(Cell cell) { try { if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING ){ return cell.getStringCellValue(); @@ -168,7 +172,7 @@ public class ExcelUtils { } } - public static String getExcelCellString(HSSFCell cell){ + public static String getExcelCellString(Cell cell){ String result = "%s%s"; result = String.format(result, getExcelColString(cell.getColumnIndex()), cell.getRowIndex()); return result; @@ -190,7 +194,7 @@ public class ExcelUtils { * @param cell * @return */ - private static String getNumericCellValue(HSSFCell cell) { + private static String getNumericCellValue(Cell cell) { Double number = cell.getNumericCellValue(); // HSSFCellStyle style = cell.getCellStyle(); // String dataFormatString = style.getDataFormatString(); @@ -226,7 +230,7 @@ public class ExcelUtils { * @param notEmpty * @return */ - private static boolean checkIsEmptyRow(HSSFRow row) { + private static boolean checkIsEmptyRow(Row row) { if (row == null){ return false; } diff --git a/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/excel/stream/ExcelRecordStream.java b/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/excel/stream/ExcelRecordStream.java index cd9b3aef22..2c4d2d34fd 100644 --- a/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/excel/stream/ExcelRecordStream.java +++ b/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/excel/stream/ExcelRecordStream.java @@ -15,9 +15,9 @@ import java.util.Set; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; -import org.apache.poi.hssf.usermodel.HSSFCell; -import org.apache.poi.hssf.usermodel.HSSFRow; -import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; import eu.etaxonomy.cdm.common.ExcelUtils; import eu.etaxonomy.cdm.io.common.events.IIoObserver; @@ -35,7 +35,7 @@ public class ExcelRecordStream implements IItemStream{ private ExcelStreamImportState state; - private HSSFSheet sheet; + private Sheet sheet; // private ArchiveEntryBase archiveEntry; private TermUri term; private int line = 0; @@ -51,7 +51,7 @@ public class ExcelRecordStream implements IItemStream{ * Constructor. * @param term */ - public ExcelRecordStream(ExcelStreamImportState state, HSSFSheet sheet, TermUri term) { + public ExcelRecordStream(ExcelStreamImportState state, Sheet sheet, TermUri term) { this.state = state; this.sheet = sheet; this.term = term; @@ -92,7 +92,7 @@ public class ExcelRecordStream implements IItemStream{ mapping = getHeaderMapping(sheet.getRow(line++)); } // int rows = sheet.getPhysicalNumberOfRows(); - HSSFRow row = sheet.getRow(line++); + Row row = sheet.getRow(line++); int i = 0; while (row == null && i++ < 10){ row = sheet.getRow(line++); @@ -106,7 +106,7 @@ public class ExcelRecordStream implements IItemStream{ Map map = new HashMap(); for (int c :mapping.keySet()){ - HSSFCell cell = row.getCell(c); + Cell cell = row.getCell(c); String value = ExcelUtils.getCellValue(cell); map.put(mapping.get(c), value); logger.info("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); @@ -123,13 +123,13 @@ public class ExcelRecordStream implements IItemStream{ * @param row * @return */ - private Map getHeaderMapping(HSSFRow row) { + private Map getHeaderMapping(Row row) { Map result = new HashMap(); int cells = row.getPhysicalNumberOfCells(); logger.info("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { - HSSFCell cell = row.getCell(c); + Cell cell = row.getCell(c); String value = ExcelUtils.getCellValue(cell); String termUri = convert2semanticKey(value); if (termUri != null){ diff --git a/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/excel/stream/ExcelStreamImport.java b/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/excel/stream/ExcelStreamImport.java index cf42b5cf05..f11d4eefc9 100644 --- a/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/excel/stream/ExcelStreamImport.java +++ b/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/excel/stream/ExcelStreamImport.java @@ -7,6 +7,7 @@ import java.util.HashMap; import org.apache.http.HttpException; import org.apache.log4j.Logger; +import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.springframework.stereotype.Component; import eu.etaxonomy.cdm.io.dwca.TermUri; @@ -30,10 +31,7 @@ public class ExcelStreamImport extends DwcaDataImportBase> recordList = null; - - /* (non-Javadoc) - * @see eu.etaxonomy.cdm.io.common.CdmIoBase#doInvoke(eu.etaxonomy.cdm.io.common.IoStateBase) - */ + @Override protected void doInvoke(ExcelStreamImportState state) { @@ -61,6 +59,9 @@ public class ExcelStreamImport extends DwcaDataImportBase { * @return * @throws HttpException * @throws IOException + * @throws InvalidFormatException */ - public IReader getWorksheetStream(STATE state) throws IOException, HttpException{ - POIFSFileSystem fs = new POIFSFileSystem(UriUtils.getInputStream(source)); - HSSFWorkbook wb = new HSSFWorkbook(fs); + public IReader getWorksheetStream(STATE state) throws IOException, HttpException, InvalidFormatException{ +// POIFSFileSystem fs = new POIFSFileSystem(UriUtils.getInputStream(source)); +// HSSFWorkbook wb = new HSSFWorkbook(fs); + Workbook wb = WorkbookFactory.create(UriUtils.getInputStream(source)); Map map = new HashMap(); for (int i = 0 ; i < wb.getNumberOfSheets(); i++){ @@ -87,7 +91,7 @@ public class ExcelToStreamConverter { TermUri term= TermUri.DWC_TAXON; Integer i = map.get(term); if (i != null){ - HSSFSheet ws = wb.getSheetAt(i); + Sheet ws = wb.getSheetAt(i); ExcelRecordStream excelRecordStream = new ExcelRecordStream(state, ws, term); streamList.add(excelRecordStream); //for taxa and names }else{ @@ -99,7 +103,7 @@ public class ExcelToStreamConverter { //core relationships i = map.get(term); if (i != null){ - HSSFSheet ws = wb.getSheetAt(i); + Sheet ws = wb.getSheetAt(i); ExcelRecordStream excelRecordStream = new ExcelRecordStream(state, ws, term); streamList.add(excelRecordStream); //for relationships }else{ diff --git a/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/specimen/excel/in/SpecimenSythesysExcelImport.java b/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/specimen/excel/in/SpecimenSythesysExcelImport.java index 7876d7c788..cfe15a789a 100644 --- a/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/specimen/excel/in/SpecimenSythesysExcelImport.java +++ b/cdmlib-io/src/main/java/eu/etaxonomy/cdm/io/specimen/excel/in/SpecimenSythesysExcelImport.java @@ -24,7 +24,7 @@ import java.util.UUID; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; -import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.usermodel.Workbook; import org.springframework.stereotype.Component; import org.springframework.transaction.TransactionStatus; @@ -121,9 +121,7 @@ implements ICdmIO { boolean DEBUG =false; - protected HSSFWorkbook hssfworkbook = null; - - public SpecimenSythesysExcelImport() { + public SpecimenSythesysExcelImport() { super(); } diff --git a/pom.xml b/pom.xml index 2fca9b3248..e01c8d4a47 100755 --- a/pom.xml +++ b/pom.xml @@ -32,6 +32,7 @@ 1.8.0.10 4.2.3 1.3 + 3.10-FINAL scm:svn:http://dev.e-taxonomy.eu/svn/trunk/cdmlib/ @@ -981,8 +982,19 @@ org.apache.poi poi - 3.9 + ${poi.version} + + org.apache.poi + poi-ooxml-schemas + ${poi.version} + + + org.apache.poi + poi-ooxml + ${poi.version} + + org.apache.sanselan sanselan -- 2.34.1