Menu
▾
▴
[Objectlabkit-svn] SF.net SVN: objectlabkit:[281] trunk
|
From: <ma...@us...> - 2009-08-27 11:55:35
|
Revision: 281
http://objectlabkit.svn.sourceforge.net/objectlabkit/?rev=281&view=rev
Author: marchy
Date: 2009-08-27 11:55:22 +0000 (Thu, 27 Aug 2009)
Log Message:
-----------
Adding Excel utility with permission from Rand Merchant Bank (it was originally written there).
Added Paths:
-----------
trunk/utils-excel/
trunk/utils-excel/pom.xml
trunk/utils-excel/src/
trunk/utils-excel/src/main/
trunk/utils-excel/src/main/java/
trunk/utils-excel/src/main/java/net/
trunk/utils-excel/src/main/java/net/objectlab/
trunk/utils-excel/src/main/java/net/objectlab/kit/
trunk/utils-excel/src/main/java/net/objectlab/kit/util/
trunk/utils-excel/src/main/java/net/objectlab/kit/util/excel/
trunk/utils-excel/src/main/java/net/objectlab/kit/util/excel/Excel.java
Added: trunk/utils-excel/pom.xml
===================================================================
--- trunk/utils-excel/pom.xml (rev 0)
+++ trunk/utils-excel/pom.xml 2009-08-27 11:55:22 UTC (rev 281)
@@ -0,0 +1,34 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+ xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
+ <modelVersion>4.0.0</modelVersion>
+
+ <groupId>net.objectlab.kit.util</groupId>
+ <artifactId>excel</artifactId>
+ <version>1.0-SNAPSHOT</version>
+ <packaging>jar</packaging>
+
+ <name>ObjectLab Kit - Excel Utility</name>
+
+ <dependencies>
+ <dependency>
+ <groupId>org.apache.poi</groupId>
+ <artifactId>poi</artifactId>
+ <version>3.2-FINAL</version>
+ </dependency>
+ </dependencies>
+
+ <build>
+ <plugins>
+ <plugin>
+ <groupId>org.apache.maven.plugins</groupId>
+ <artifactId>maven-compiler-plugin</artifactId>
+ <configuration>
+ <source>1.5</source>
+ <target>1.5</target>
+ </configuration>
+ </plugin>
+ </plugins>
+ </build>
+
+</project>
Property changes on: trunk/utils-excel/pom.xml
___________________________________________________________________
Added: svn:mime-type
+ text/plain
Added: 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 (rev 0)
+++ trunk/utils-excel/src/main/java/net/objectlab/kit/util/excel/Excel.java 2009-08-27 11:55:22 UTC (rev 281)
@@ -0,0 +1,184 @@
+package net.objectlab.kit.util.excel;
+
+import java.io.FileInputStream;
+import java.io.FileNotFoundException;
+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 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 InputStream inputStream;
+
+ public Excel() {
+ }
+
+ public Excel(InputStream in) {
+ setInputStream(in);
+ init();
+ }
+
+ public void init() throws RuntimeException {
+
+ POIFSFileSystem fs;
+ try {
+ fs = new POIFSFileSystem(inputStream);
+ workbook = new HSSFWorkbook(fs);
+ } catch (Exception e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ public <E> E readValue(String cellAddress, Class<E> type) {
+
+ Object[][] result = readBlock(cellAddress, true, type);
+ return (E) result[0][0];
+ }
+
+ /**
+ *
+ * @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
+ */
+ public Object[][] readBlock(String range, Class... colTypes) {
+ return readBlock(range, false, colTypes);
+ }
+
+
+ private Object[][] readBlock(String range, boolean oneLiner, Class... colTypes) {
+
+ RangeAddress ra = new RangeAddress(range);
+ HSSFSheet sheet = workbook.getSheet(ra.getSheetName());
+
+ oneLiner = (ra.getHeight() == 1 && oneLiner);
+
+ List<Object> result;
+ if (ra.getHeight() == 1) {
+ result = new LinkedList<Object>();
+ } else {
+ result = new ArrayList<Object>(ra.getHeight());
+ }
+
+ 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()];
+ result.add(resultRow);
+ for (int j = 0; j < ra.getWidth(); j++) {
+ HSSFCell cell = row.getCell((short) (x + j - 1));
+
+ if (colTypes.length > j) {
+ colType = colTypes[j];
+ }
+
+ 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();
+ }
+ }
+
+ }
+
+ return result.toArray(new Object[][] {});
+ }
+
+ private boolean isMoreToRead(HSSFSheet sheet, boolean oneLiner, int x, int y, int i) {
+
+ if (oneLiner) {
+ return (i < 1);
+ }
+
+ // check if the cell is empty
+ HSSFRow row = sheet.getRow(y + i - 1);
+ if (row == null) {
+ return false;
+ }
+
+ HSSFCell cell = row.getCell((short) (x - 1));
+ if (cell == null) {
+ return false;
+ }
+ String str = cell.toString();
+ 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() {
+ 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);
+ }
+ }
+}
Property changes on: trunk/utils-excel/src/main/java/net/objectlab/kit/util/excel/Excel.java
___________________________________________________________________
Added: svn:mime-type
+ text/plain
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
