From: Mathieu Baudier Date: Thu, 1 Oct 2009 11:34:48 +0000 (+0000) Subject: Introduce JXL DAO support X-Git-Tag: argeo-commons-2.1.30~1761 X-Git-Url: http://git.argeo.org/?p=lgpl%2Fargeo-commons.git;a=commitdiff_plain;h=db84ccc332884c4b89b755923538cecf2f00df68 Introduce JXL DAO support git-svn-id: https://svn.argeo.org/commons/trunk@2998 4cfe0d0a-d680-48aa-b62c-e0a02a3f76cc --- diff --git a/server/runtime/org.argeo.server.jxl/.classpath b/server/runtime/org.argeo.server.jxl/.classpath new file mode 100644 index 000000000..6b67f5112 --- /dev/null +++ b/server/runtime/org.argeo.server.jxl/.classpath @@ -0,0 +1,9 @@ + + + + + + + + + diff --git a/server/runtime/org.argeo.server.jxl/.project b/server/runtime/org.argeo.server.jxl/.project new file mode 100644 index 000000000..77aace3d8 --- /dev/null +++ b/server/runtime/org.argeo.server.jxl/.project @@ -0,0 +1,23 @@ + + + org.argeo.server.jxl + + + + + + org.eclipse.jdt.core.javabuilder + + + + + org.maven.ide.eclipse.maven2Builder + + + + + + org.maven.ide.eclipse.maven2Nature + org.eclipse.jdt.core.javanature + + diff --git a/server/runtime/org.argeo.server.jxl/.settings/org.eclipse.jdt.core.prefs b/server/runtime/org.argeo.server.jxl/.settings/org.eclipse.jdt.core.prefs new file mode 100644 index 000000000..737c94ce5 --- /dev/null +++ b/server/runtime/org.argeo.server.jxl/.settings/org.eclipse.jdt.core.prefs @@ -0,0 +1,5 @@ +#Thu Oct 01 12:06:12 CEST 2009 +eclipse.preferences.version=1 +org.eclipse.jdt.core.compiler.codegen.targetPlatform=1.5 +org.eclipse.jdt.core.compiler.compliance=1.5 +org.eclipse.jdt.core.compiler.source=1.5 diff --git a/server/runtime/org.argeo.server.jxl/.settings/org.maven.ide.eclipse.prefs b/server/runtime/org.argeo.server.jxl/.settings/org.maven.ide.eclipse.prefs new file mode 100644 index 000000000..ef3da6253 --- /dev/null +++ b/server/runtime/org.argeo.server.jxl/.settings/org.maven.ide.eclipse.prefs @@ -0,0 +1,9 @@ +#Thu Oct 01 12:05:59 CEST 2009 +activeProfiles= +eclipse.preferences.version=1 +fullBuildGoals=process-test-resources +includeModules=false +resolveWorkspaceProjects=true +resourceFilterGoals=process-resources resources\:testResources +skipCompilerPlugin=true +version=1 diff --git a/server/runtime/org.argeo.server.jxl/pom.xml b/server/runtime/org.argeo.server.jxl/pom.xml index 12fde803f..06213f558 100644 --- a/server/runtime/org.argeo.server.jxl/pom.xml +++ b/server/runtime/org.argeo.server.jxl/pom.xml @@ -51,5 +51,20 @@ com.springsource.jxl + + + org.junit + com.springsource.junit + test + + + org.argeo.commons.basic + org.argeo.basic.dep.log4j + 0.1.1-SNAPSHOT + pom + test + + + diff --git a/server/runtime/org.argeo.server.jxl/src/main/java/org/argeo/server/jxl/dao/JxlDaoSheet.java b/server/runtime/org.argeo.server.jxl/src/main/java/org/argeo/server/jxl/dao/JxlDaoSheet.java new file mode 100644 index 000000000..307d3b9ba --- /dev/null +++ b/server/runtime/org.argeo.server.jxl/src/main/java/org/argeo/server/jxl/dao/JxlDaoSheet.java @@ -0,0 +1,5 @@ +package org.argeo.server.jxl.dao; + +public class JxlDaoSheet { + private Class targetClass; +} diff --git a/server/runtime/org.argeo.server.jxl/src/main/java/org/argeo/server/jxl/dao/JxlDaoSupport.java b/server/runtime/org.argeo.server.jxl/src/main/java/org/argeo/server/jxl/dao/JxlDaoSupport.java new file mode 100644 index 000000000..390fae72b --- /dev/null +++ b/server/runtime/org.argeo.server.jxl/src/main/java/org/argeo/server/jxl/dao/JxlDaoSupport.java @@ -0,0 +1,147 @@ +package org.argeo.server.jxl.dao; + +import java.io.InputStream; +import java.util.ArrayList; +import java.util.HashMap; +import java.util.List; +import java.util.Map; + +import jxl.Cell; +import jxl.FormulaCell; +import jxl.Sheet; +import jxl.Workbook; + +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.argeo.server.ArgeoServerException; +import org.springframework.beans.BeanWrapper; +import org.springframework.beans.BeanWrapperImpl; + +public class JxlDaoSupport { + private final static Log log = LogFactory.getLog(JxlDaoSupport.class); + + private ClassLoader classLoader = getClass().getClassLoader(); + + private Map> model = new HashMap>(); + + public void load(InputStream in) { + + try { + // used to resolve inner references + Map> tempRefs = new HashMap>(); + List links = new ArrayList(); + + Workbook workbook = Workbook.getWorkbook(in); + + for (Sheet sheet : workbook.getSheets()) { + if (log.isDebugEnabled()) + log + .debug("Instantiate objects of sheet " + + sheet.getName()); + + Cell[] firstRow = sheet.getRow(0); + + // TODO: ability to map sheet names and class names + String className = sheet.getName(); + Class clss = classLoader.loadClass(className); + model.put(clss, new HashMap()); + + tempRefs.put(sheet.getName(), new ArrayList()); + + String keyProperty = firstRow[0].getContents(); + for (int i = 1; i < sheet.getRows(); i++) { + if (log.isTraceEnabled()) + log.trace(" row " + i); + + Cell[] currentRow = sheet.getRow(i); + BeanWrapper bw = new BeanWrapperImpl(clss); + for (int j = 0; j < firstRow.length; j++) { + String pName = firstRow[j].getContents(); + + Cell cell = currentRow[j]; + if (cell instanceof FormulaCell) { + String formula = ((FormulaCell) cell).getFormula(); + int index = formula.indexOf('!'); + String targetSheet = formula.substring(0, index); + // assume no double letters + Integer targetRow = Integer.parseInt(formula + .substring(index + 2)); + links.add(new Link(bw.getWrappedInstance(), pName, + targetSheet, targetRow)); + + if (log.isTraceEnabled()) + log.debug(" formula: " + formula + + " | content: " + cell.getContents() + + " | targetSheet=" + targetSheet + + ", targetRow=" + targetRow); + } else { + bw.setPropertyValue(pName, cell.getContents()); + + if (log.isTraceEnabled()) + log.debug(" " + pName + "=" + + cell.getContents()); + } + }// properties set + + model.get(clss).put(bw.getPropertyValue(keyProperty), + bw.getWrappedInstance()); + tempRefs.get(sheet.getName()).add(bw.getWrappedInstance()); + } + + if (log.isDebugEnabled()) + log.debug(model.get(clss).size() + " objects of type " + + clss); + } + + if (log.isDebugEnabled()) + log.debug("Link " + links.size() + " references"); + for (Link link : links) { + BeanWrapper bw = new BeanWrapperImpl(link.object); + Object targetObject = tempRefs.get(link.getTargetSheet()).get( + link.targetRow - 2); + bw.setPropertyValue(link.property, targetObject); + } + + } catch (Exception e) { + throw new ArgeoServerException("Cannot load workbook", e); + } + } + + @SuppressWarnings("unchecked") + public T getByKey(Class clss, Object key) { + return (T) model.get(clss).get(key); + } + + public static class Link { + private Object object; + private String property; + private String targetSheet; + private Integer targetRow; + + public Link(Object object, String property, String targetSheet, + Integer targetRow) { + super(); + this.object = object; + this.property = property; + this.targetSheet = targetSheet; + this.targetRow = targetRow; + } + + public Object getObject() { + return object; + } + + public String getProperty() { + return property; + } + + public String getTargetSheet() { + return targetSheet; + } + + public Integer getTargetRow() { + return targetRow; + } + + } +} diff --git a/server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/JxlDaoSupportTest.java b/server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/JxlDaoSupportTest.java new file mode 100644 index 000000000..b79a8a03d --- /dev/null +++ b/server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/JxlDaoSupportTest.java @@ -0,0 +1,26 @@ +package org.argeo.server.jxl.dao; + +import java.io.InputStream; + +import junit.framework.TestCase; + +public class JxlDaoSupportTest extends TestCase { + public void testBasic() { + JxlDaoSupport jsd = new JxlDaoSupport(); + InputStream in = getClass().getResourceAsStream("/dao/simple.xls"); + jsd.load(in); + + SimpleObject soAaa = jsd.getByKey(SimpleObject.class, "aaa"); + assertNotNull(soAaa); + assertEquals("aaa", soAaa.getString()); + assertEquals(1, soAaa.getInteger().intValue()); + assertNotNull(soAaa.getOtherObject()); + assertEquals("USD", soAaa.getOtherObject().getKey()); + assertEquals("US Dollar", soAaa.getOtherObject().getValue()); + + SimpleObject soBbb = jsd.getByKey(SimpleObject.class, "bbb"); + assertNotNull(soBbb.getOtherObject()); + assertEquals("bbb", ((SimpleObject) soBbb.getOtherObject().getValue()) + .getString()); + } +} diff --git a/server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/OtherObject.java b/server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/OtherObject.java new file mode 100644 index 000000000..2d7abd6e4 --- /dev/null +++ b/server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/OtherObject.java @@ -0,0 +1,22 @@ +package org.argeo.server.jxl.dao; + +public class OtherObject { + private String key; + private Object value; + + public String getKey() { + return key; + } + + public void setKey(String key) { + this.key = key; + } + + public Object getValue() { + return value; + } + + public void setValue(Object value) { + this.value = value; + } +} diff --git a/server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/SimpleObject.java b/server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/SimpleObject.java new file mode 100644 index 000000000..ae5aea50d --- /dev/null +++ b/server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/SimpleObject.java @@ -0,0 +1,31 @@ +package org.argeo.server.jxl.dao; + +public class SimpleObject { + private String string; + private Integer integer; + private OtherObject otherObject; + + public String getString() { + return string; + } + + public void setString(String sting) { + this.string = sting; + } + + public Integer getInteger() { + return integer; + } + + public void setInteger(Integer integer) { + this.integer = integer; + } + + public OtherObject getOtherObject() { + return otherObject; + } + + public void setOtherObject(OtherObject otherObject) { + this.otherObject = otherObject; + } +} diff --git a/server/runtime/org.argeo.server.jxl/src/test/resources/dao/simple.xls b/server/runtime/org.argeo.server.jxl/src/test/resources/dao/simple.xls new file mode 100644 index 000000000..7cb8841c7 Binary files /dev/null and b/server/runtime/org.argeo.server.jxl/src/test/resources/dao/simple.xls differ diff --git a/server/runtime/org.argeo.server.jxl/src/test/resources/log4j.properties b/server/runtime/org.argeo.server.jxl/src/test/resources/log4j.properties new file mode 100644 index 000000000..6a9667c2e --- /dev/null +++ b/server/runtime/org.argeo.server.jxl/src/test/resources/log4j.properties @@ -0,0 +1,13 @@ +log4j.rootLogger=WARN, console + +## Levels +log4j.logger.org.argeo=DEBUG +log4j.logger.org.argeo.server.jxl=TRACE + +## Appenders +# console is set to be a ConsoleAppender. +log4j.appender.console=org.apache.log4j.ConsoleAppender + +# console uses PatternLayout. +log4j.appender.console.layout=org.apache.log4j.PatternLayout +log4j.appender.console.layout.ConversionPattern= %-5p %d{ISO8601} %m - %c%n diff --git a/server/runtime/pom.xml b/server/runtime/pom.xml index f496689b9..7032ecbf6 100644 --- a/server/runtime/pom.xml +++ b/server/runtime/pom.xml @@ -16,5 +16,6 @@ org.argeo.server.core org.argeo.server.json org.argeo.server.osgi + org.argeo.server.jxl \ No newline at end of file