From db84ccc332884c4b89b755923538cecf2f00df68 Mon Sep 17 00:00:00 2001 From: Mathieu Baudier Date: Thu, 1 Oct 2009 11:34:48 +0000 Subject: [PATCH] Introduce JXL DAO support git-svn-id: https://svn.argeo.org/commons/trunk@2998 4cfe0d0a-d680-48aa-b62c-e0a02a3f76cc --- .../runtime/org.argeo.server.jxl/.classpath | 9 ++ server/runtime/org.argeo.server.jxl/.project | 23 +++ .../.settings/org.eclipse.jdt.core.prefs | 5 + .../.settings/org.maven.ide.eclipse.prefs | 9 ++ server/runtime/org.argeo.server.jxl/pom.xml | 15 ++ .../org/argeo/server/jxl/dao/JxlDaoSheet.java | 5 + .../argeo/server/jxl/dao/JxlDaoSupport.java | 147 ++++++++++++++++++ .../server/jxl/dao/JxlDaoSupportTest.java | 26 ++++ .../org/argeo/server/jxl/dao/OtherObject.java | 22 +++ .../argeo/server/jxl/dao/SimpleObject.java | 31 ++++ .../src/test/resources/dao/simple.xls | Bin 0 -> 6656 bytes .../src/test/resources/log4j.properties | 13 ++ server/runtime/pom.xml | 1 + 13 files changed, 306 insertions(+) create mode 100644 server/runtime/org.argeo.server.jxl/.classpath create mode 100644 server/runtime/org.argeo.server.jxl/.project create mode 100644 server/runtime/org.argeo.server.jxl/.settings/org.eclipse.jdt.core.prefs create mode 100644 server/runtime/org.argeo.server.jxl/.settings/org.maven.ide.eclipse.prefs create mode 100644 server/runtime/org.argeo.server.jxl/src/main/java/org/argeo/server/jxl/dao/JxlDaoSheet.java create mode 100644 server/runtime/org.argeo.server.jxl/src/main/java/org/argeo/server/jxl/dao/JxlDaoSupport.java create mode 100644 server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/JxlDaoSupportTest.java create mode 100644 server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/OtherObject.java create mode 100644 server/runtime/org.argeo.server.jxl/src/test/java/org/argeo/server/jxl/dao/SimpleObject.java create mode 100644 server/runtime/org.argeo.server.jxl/src/test/resources/dao/simple.xls create mode 100644 server/runtime/org.argeo.server.jxl/src/test/resources/log4j.properties 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 0000000000000000000000000000000000000000..7cb8841c78c6ee4f55dccdfc69b2119214c9ccfd GIT binary patch literal 6656 zcmeHLU2IfE6h8NMw`@yc+wv!%c&$FdK*hI0B>ws0HQ#bAfrl zd|(0a5U>zf1S|#~1|9*H0FMF|@EA}BJPteoECuR;Wk3V499RKx%uRmE{FBJ91X_LB zYCp|eVmeof1u7y~#qzLxIqNK4zWV-4V8qGL8Rf+c)1h+DzvKuxp>8|&dY<6=wVetyM7gFP<^RmpLl z%rA6o?dV>=`J(Q)>_$!XKc>KBu<*U9StU*%y~-cXOl88YPT z-00aM<4N=KA2G2T(~@Ocoi5X~CS9&+Z91gsob(J$=cdEi9bma3 zhoo70dA);=O}axZwmak`TgDuBkK?xN+?!|_u#+u4@sZsLr?YRT)1NvdO%mlD5RSQM zgU-~j<9hWE!$~yacLLiL3PTI9SxFy5$;f_wlRh3CnU`3Q@{EPpfCe5utm)PE9u>L# zYMGhg0rM5EqY>F};6fTXZNw1AWXV+?K{%2kIGQ3jaw31?RJwsp$fJtDRdy`p#z%*+ zYvQ9RXUK7Jk|pzlhHBfk5%=}=A>QTegNgRoiE#&|-kx@->+P}HlZk}wLekOO4cm8& zyGeAIa>i2K&R`agqAK|ra%$vZ={eQve>l}D%x(a)=Tg+@Osg@U8y{Xhx23OZ-ODOZ z>y;ZFAS~q~%Qe}LFK0q4v_+%F2ej^79GMo*$_@2(+u|e6n6=e;#p+Iu*rQEblkSL} zsH<;XRlj~7PEnkxcdpiD4>?wS*Fo+6O^xd@l+_w8uUi(#Hz0qo`NDzb3z>Dx1Nmc8 zhquCjF18^Dq{(P9a_w`eA-(9kLS9otiQ!iA7J*>$M&Z%Giz6l%mU)wo42*~hWh@lR zNR*&#!hfTBeS0MXhG$!^Wa=__B}$`cFY>xssiTkT8CZ^+3eOyWgt+5U58p)~FB+Ym zVV1KQD8f$pF{&3RcEdG}>M+=iS*#m0M>m!9=suOMNEaHKnT_ZU^vH1cafIv{qs**C z7HTpLDzC_RR$_&GFr`x4u?@HTb7rl4rqB3Tz7mFo{$MtU49o*L3}l+QFvz_yms{0y zUpeNQ*t!}5%q^7B_=Peu5rol2C+qWU=33_2%mro1<1ZNGmq^>)vaD*moiw_QZS3|GO zlw*w0Q#$3AUW1$X=StciZ~_kEY`Q-)|=Y4*K$h_ZFiU*7_TXfOrteWAB6$%nELV2nV#xoYc1>3O zjxxRjx!&9d^Z|FQ9Qb&`cPEByfZh?ja62)Eoq-yqWvQR@p>Bp!S~a55CcmNqy$Nwj zX*OvcUN$_org.argeo.server.core org.argeo.server.json org.argeo.server.osgi + org.argeo.server.jxl \ No newline at end of file -- 2.30.2