Menu
▾
▴
[Objectlabkit-svn] SF.net SVN: objectlabkit:[384] trunk/utils-excel
|
From: <ma...@us...> - 2012-08-24 10:41:10
|
Revision: 384
http://objectlabkit.svn.sourceforge.net/objectlabkit/?rev=384&view=rev
Author: marchy
Date: 2012-08-24 10:41:03 +0000 (Fri, 24 Aug 2012)
Log Message:
-----------
Excel gets some tests + upgrade to poi 3.8.
Upgrading poi library to latest version 3.8. This required some refactors, but it means that it works with xlsx's now too.
Adding tests for read* methods... so I think we're ready to release this module.
Modified Paths:
--------------
trunk/utils-excel/pom.xml
trunk/utils-excel/src/main/java/net/objectlab/kit/util/excel/Excel.java
Added Paths:
-----------
trunk/utils-excel/src/test/
trunk/utils-excel/src/test/java/
trunk/utils-excel/src/test/java/net.objectlab.kit.util.excel/
trunk/utils-excel/src/test/java/net.objectlab.kit.util.excel/ExcelTest.java
trunk/utils-excel/src/test/resources/
trunk/utils-excel/src/test/resources/net/
trunk/utils-excel/src/test/resources/net/objectlab/
trunk/utils-excel/src/test/resources/net/objectlab/kit/
trunk/utils-excel/src/test/resources/net/objectlab/kit/util/
trunk/utils-excel/src/test/resources/net/objectlab/kit/util/excel/
trunk/utils-excel/src/test/resources/net/objectlab/kit/util/excel/Test Workbook.xls
trunk/utils-excel/src/test/resources/net/objectlab/kit/util/excel/Test Workbook.xlsx
Property Changed:
----------------
trunk/utils-excel/
Property changes on: trunk/utils-excel
___________________________________________________________________
Modified: svn:ignore
- target
+ target
.idea
*.iml
Modified: trunk/utils-excel/pom.xml
===================================================================
--- trunk/utils-excel/pom.xml 2012-04-25 15:28:20 UTC (rev 383)
+++ trunk/utils-excel/pom.xml 2012-08-24 10:41:03 UTC (rev 384)
@@ -13,9 +13,15 @@
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>3.2-FINAL</version>
+ <artifactId>poi-ooxml</artifactId>
+ <version>3.8</version>
</dependency>
+ <dependency>
+ <groupId>junit</groupId>
+ <artifactId>junit</artifactId>
+ <version>4.10</version>
+ <scope>test</scope>
+ </dependency>
</dependencies>
<build>
Modified: trunk/utils-excel/src/main/java/net/objectlab/kit/util/excel/Excel.java
===================================================================
--- trunk/utils-excel/src/main/java/net/objectlab/kit/util/excel/Excel.java 2012-04-25 15:28:20 UTC (rev 383)
+++ trunk/utils-excel/src/main/java/net/objectlab/kit/util/excel/Excel.java 2012-08-24 10:41:03 UTC (rev 384)
@@ -1,114 +1,106 @@
package net.objectlab.kit.util.excel;
-import java.io.FileInputStream;
-import java.io.FileNotFoundException;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.AreaReference;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.ss.util.CellReference;
+
import java.io.InputStream;
import java.math.BigDecimal;
-import java.util.ArrayList;
-import java.util.Calendar;
-import java.util.Date;
-import java.util.LinkedList;
-import java.util.List;
+import java.util.*;
-import org.apache.poi.hssf.usermodel.HSSFCell;
-import org.apache.poi.hssf.usermodel.HSSFName;
-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.hssf.util.RangeAddress;
-import org.apache.poi.poifs.filesystem.POIFSFileSystem;
-
public class Excel {
- private HSSFWorkbook workbook;
+ private Workbook workbook;
- private InputStream inputStream;
-
- public Excel() {
- }
-
public Excel(InputStream in) {
- setInputStream(in);
- init();
+ init(in);
}
- public void init() throws RuntimeException {
+ private void init(InputStream inputStream) throws RuntimeException {
- POIFSFileSystem fs;
try {
- fs = new POIFSFileSystem(inputStream);
- workbook = new HSSFWorkbook(fs);
+ workbook = WorkbookFactory.create(inputStream);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
- public <E> E readValue(String cellAddress, Class<E> type) {
+ public <E> E readValueAt(String cellAddress, Class<E> type) {
+ return readCell(cellAt(cellAddress), type);
+ }
- Object[][] result = readBlock(cellAddress, true, type);
- return (E) result[0][0];
+ public <E> List<E> readColumn(String rangeOrStartAddress, Class<E> type) {
+
+ Object[][] arr = readBlock(rangeOrStartAddress, type);
+
+ List<E> result = new LinkedList<E>();
+ for (int i = 0; i < arr.length; i++) {
+ result.add((E)arr[i][0]);
+ }
+
+ return result;
}
+ public String namedRangeToRangeAddress(String namedRange) {
+ int namedCellIndex = getWorkbook().getNameIndex(namedRange);
+ Name namedCell = getWorkbook().getNameAt(namedCellIndex);
+
+ return namedCell.getRefersToFormula();
+ }
+
+ public Cell cellAt(String cellAddr) {
+ CellReference cr = new CellReference(cellAddr);
+
+ return workbook
+ .getSheet(cr.getSheetName())
+ .getRow(cr.getRow())
+ .getCell((int) cr.getCol());
+ }
+
/**
- *
- * @param range
- * either the range of the entire block to be read, or just the
- * top row of the cells, in which case the method will stop when
- * the first empty cell is reached in the first column
- * @param colTypes
- * classes of the result types, per column
- * @return 2 dimensional array, containing the data read, cast as per
- * colTypes
+ * @param range either the range of the entire block to be read, or just the
+ * top row of the cells, in which case the method will stop when
+ * the first empty cell is reached in the first column
+ * @param colTypes An array of data types expected at each column.
+ * If this array is shorter than the number of column, then the last
+ * data type is used until the end. So if only one value is given,
+ * then that is used for the entire block.
*/
public Object[][] readBlock(String range, Class... colTypes) {
- return readBlock(range, false, colTypes);
- }
-
- private Object[][] readBlock(String range, boolean oneLiner, Class... colTypes) {
+ CellRangeAddress cra = CellRangeAddress.valueOf(range);
+ AreaReference ar = new AreaReference(range);
+ Sheet sheet = workbook.getSheet(ar.getFirstCell().getSheetName());
- RangeAddress ra = new RangeAddress(range);
- HSSFSheet sheet = workbook.getSheet(ra.getSheetName());
+ int firstColumn = cra.getFirstColumn();
+ int firstRow = cra.getFirstRow();
+ int lastRow = cra.getLastRow();
+ int height = lastRow - firstRow + 1;
+ int width = cra.getLastColumn() - firstColumn + 1;
- oneLiner = (ra.getHeight() == 1 && oneLiner);
-
List<Object> result;
- if (ra.getHeight() == 1) {
+ if (height == 1) {
result = new LinkedList<Object>();
} else {
- result = new ArrayList<Object>(ra.getHeight());
+ result = new ArrayList<Object>(height);
}
- int x = ra.getXPosition(ra.getFromCell());
- int y = ra.getYPosition(ra.getFromCell());
-
- Class colType = colTypes[0];
- for (int i = 0; (isMoreToRead(sheet, oneLiner, x, y, i)); i++) {
- HSSFRow row = sheet.getRow(y + i - 1);
- Object[] resultRow = new Object[ra.getWidth()];
+ for (int rowNum = 0; moreDataToRead(sheet, firstColumn, firstRow, lastRow, rowNum); rowNum++) {
+ Row row = sheet.getRow(firstRow + rowNum);
+ Object[] resultRow = new Object[width];
result.add(resultRow);
- for (int j = 0; j < ra.getWidth(); j++) {
- HSSFCell cell = row.getCell((short) (x + j - 1));
+ for (int colNum = 0; colNum < width; colNum++) {
- if (colTypes.length > j) {
- colType = colTypes[j];
+ Class colType;
+ if (colNum < colTypes.length - 1) {
+ colType = colTypes[colNum];
+ } else {
+ colType = colTypes[colTypes.length - 1];
}
- if (colType == Date.class) {
- resultRow[j] = cell.getDateCellValue();
- } else if (colType == Calendar.class) {
- Calendar cal = Calendar.getInstance();
- cal.setTime(cell.getDateCellValue());
- resultRow[j] = cal;
- } else if (colType == Integer.class) {
- resultRow[j] = ((Double) cell.getNumericCellValue()).intValue();
- } else if (colType == Double.class) {
- resultRow[j] = (Double) cell.getNumericCellValue();
- } else if (colType == BigDecimal.class) {
- resultRow[j] = new BigDecimal(String.valueOf(cell.getNumericCellValue()));
- } else if (colType == String.class) {
- resultRow[j] = cell.getRichStringCellValue().getString();
- }
+ Cell cell = row.getCell(firstColumn + colNum);
+ resultRow[colNum] = readCell(cell, colType);
}
}
@@ -116,19 +108,42 @@
return result.toArray(new Object[][] {});
}
- private boolean isMoreToRead(HSSFSheet sheet, boolean oneLiner, int x, int y, int i) {
+ private <E> E readCell(Cell cell, Class<E> colType) {
- if (oneLiner) {
- return (i < 1);
+ if (colType == Date.class) {
+ return (E) cell.getDateCellValue();
+ } else if (colType == Calendar.class) {
+ Calendar cal = Calendar.getInstance();
+ cal.setTime(cell.getDateCellValue());
+ return (E) cal;
+ } else if (colType == Integer.class) {
+ return (E) ((Integer) ((Double) cell.getNumericCellValue()).intValue());
+ } else if (colType == Double.class) {
+ return (E) (Double) cell.getNumericCellValue();
+ } else if (colType == BigDecimal.class) {
+ return (E) new BigDecimal(String.valueOf(cell.getNumericCellValue()));
+ } else if (colType == String.class) {
+ return (E) cell.getRichStringCellValue().getString();
+ } else {
+ throw new RuntimeException("Column type not supported: " + colType);
}
-
+
+ }
+
+ private boolean moreDataToRead(Sheet sheet, int firstColumn, int firstRow, int lastRow, int rowNum) {
+
+ int height = lastRow - firstRow + 1;
+ if (height > 1 && firstRow + rowNum > lastRow) {
+ return false;
+ }
+
// check if the cell is empty
- HSSFRow row = sheet.getRow(y + i - 1);
+ Row row = sheet.getRow(firstRow + rowNum);
if (row == null) {
return false;
}
-
- HSSFCell cell = row.getCell((short) (x - 1));
+
+ Cell cell = row.getCell(firstColumn);
if (cell == null) {
return false;
}
@@ -136,49 +151,7 @@
return !(str == null || "".equals(str));
}
- public <E> List<E> readColumn(String rangeOrStartAddress, Class<E> type) {
-
- Object[][] arr = readBlock(rangeOrStartAddress, false, type);
-
- List<E> result = new LinkedList<E>();
- for (int i = 0; i < arr.length; i++) {
- result.add((E)arr[i][0]);
- }
-
- return result;
- }
-
- public String namedRangeToRangeAddress(String namedRange) {
- int namedCellIndex = getWorkbook().getNameIndex(namedRange);
- HSSFName namedCell = getWorkbook().getNameAt(namedCellIndex);
-
- return namedCell.getReference();
- }
-
- public HSSFCell getCell(String cellAddr) {
- RangeAddress ra = new RangeAddress(cellAddr);
-
- return workbook.getSheet(ra.getSheetName()).getRow(ra.getYPosition(ra.getFromCell()) - 1).getCell(
- (short) (ra.getXPosition(ra.getFromCell()) - 1));
- }
-
- public HSSFWorkbook getWorkbook() {
+ public Workbook getWorkbook() {
return workbook;
}
-
- public void setWorkbook(HSSFWorkbook workbook) {
- this.workbook = workbook;
- }
-
- public void setInputStream(InputStream inputStream) {
- this.inputStream = inputStream;
- }
-
- public void setFilename(String filename) {
- try {
- inputStream = new FileInputStream(filename);
- } catch (FileNotFoundException e) {
- throw new RuntimeException(e);
- }
- }
}
Added: trunk/utils-excel/src/test/java/net.objectlab.kit.util.excel/ExcelTest.java
===================================================================
--- trunk/utils-excel/src/test/java/net.objectlab.kit.util.excel/ExcelTest.java (rev 0)
+++ trunk/utils-excel/src/test/java/net.objectlab.kit.util.excel/ExcelTest.java 2012-08-24 10:41:03 UTC (rev 384)
@@ -0,0 +1,100 @@
+package net.objectlab.kit.util.excel;
+
+
+import org.junit.Before;
+import org.junit.Test;
+
+import java.math.BigDecimal;
+import java.util.List;
+
+import static junit.framework.Assert.assertEquals;
+
+/**
+ * @author Marcin Jekot
+ * @since 2012/08/17
+ */
+public class ExcelTest {
+
+ private Excel xls;
+ private Excel xlsx;
+
+ @Before
+ public void setUp() {
+ xls = new Excel(getClass().getResourceAsStream("Test Workbook.xls"));
+ xlsx = new Excel(getClass().getResourceAsStream("Test Workbook.xlsx"));
+ }
+
+ @Test
+ public void shouldReadSingleCell() {
+ // When
+ String result = xlsx.readValueAt("'Test Sheet'!C3", String.class);
+
+ // Then
+ assertEquals("TestA", result);
+ }
+
+ @Test
+ public void shouldReadColumn() {
+ // When
+ List<String> result = xlsx.readColumn("'Test Sheet'!C3", String.class);
+
+ // Then
+ assertEquals(9, result.size());
+ assertEquals("TestA", result.get(0));
+ assertEquals("TestI", result.get(8));
+ }
+
+ @Test
+ public void shouldReadTwoLinesAndStop() {
+ // When
+ Object[][] result = xlsx.readBlock("'Test Sheet'!C3:F4", String.class, Integer.class);
+
+ // Then
+ assertEquals("Number of rows read should be 2", 2, result.length);
+ assertEquals("Number of columns read should be 4", 4, result[0].length);
+ assertEquals("Number of columns read should be 4", 4, result[1].length);
+ }
+
+ @Test
+ public void shouldReadWholeBlockGivenTopLine() {
+ // When
+ Object[][] result = xlsx.readBlock("'Test Sheet'!C3:F3", String.class, Integer.class);
+
+ // Then
+ assertEquals("Number of rows read should be 9", 9, result.length);
+ assertEquals("Number of columns read should be 4", 4, result[0].length);
+ assertEquals("Number of columns read should be 4", 4, result[8].length);
+ }
+
+ @Test
+ public void shouldReadARangeOfCellsInXls() {
+ shouldReadARangeOfCells(xls);
+ }
+
+ @Test
+ public void shouldReadARangeOfCellsInXlx() {
+ shouldReadARangeOfCells(xlsx);
+ }
+
+ private void shouldReadARangeOfCells(Excel xl) {
+ // When
+ Object[][] result = xl.readBlock("'Test Sheet'!C3:F12", String.class, Integer.class, Double.class, BigDecimal.class);
+
+ // Then
+ final int numberOfRows = 9;
+ assertEquals(numberOfRows, result.length);
+
+ for (int i = 1; i <= numberOfRows; i++) {
+
+ Object[] data = result[i - 1];
+ assertEquals("Test" + Character.toString((char) ('A' + i - 1)), data[0]);
+ assertEquals(i, data[1]);
+
+ final int decimal = i % 10;
+ assertEquals(i + decimal / (double) 10, data[2]);
+ assertEquals(new BigDecimal("" + i + "." + decimal + decimal), data[3]);
+
+ assertEquals(4, data.length);
+ }
+ }
+}
Added: trunk/utils-excel/src/test/resources/net/objectlab/kit/util/excel/Test Workbook.xls
===================================================================
(Binary files differ)
Property changes on: trunk/utils-excel/src/test/resources/net/objectlab/kit/util/excel/Test Workbook.xls
___________________________________________________________________
Added: svn:mime-type
+ application/octet-stream
Added: trunk/utils-excel/src/test/resources/net/objectlab/kit/util/excel/Test Workbook.xlsx
===================================================================
(Binary files differ)
Property changes on: trunk/utils-excel/src/test/resources/net/objectlab/kit/util/excel/Test Workbook.xlsx
___________________________________________________________________
Added: svn:mime-type
+ application/octet-stream
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|