import java.io.InputStream;
import java.util.ArrayList;
+import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.argeo.server.ArgeoServerException;
import org.springframework.beans.BeanWrapper;
import org.springframework.beans.BeanWrapperImpl;
+import org.springframework.beans.BeansException;
+import org.springframework.context.ApplicationContext;
+import org.springframework.context.ApplicationContextAware;
+import org.springframework.util.StringUtils;
-public class JxlDaoSupport {
+public class JxlDaoSupport implements ApplicationContextAware {
private final static Log log = LogFactory.getLog(JxlDaoSupport.class);
private ClassLoader classLoader = getClass().getClassLoader();
+ private ApplicationContext applicationContext;
- private Map<Class, Map<Object, Object>> model = new HashMap<Class, Map<Object, Object>>();
+ private Map<Class<?>, Map<Object, Object>> model = new HashMap<Class<?>, Map<Object, Object>>();
+
+ private Map<String, Object> externalRefs = new HashMap<String, Object>();
public void load(InputStream in) {
try {
// used to resolve inner references
Map<String, List<Object>> tempRefs = new HashMap<String, List<Object>>();
- List<Link> links = new ArrayList<Link>();
+ List<Reference> links = new ArrayList<Reference>();
Workbook workbook = Workbook.getWorkbook(in);
for (Sheet sheet : workbook.getSheets()) {
- if (log.isDebugEnabled())
- log
- .debug("Instantiate objects of sheet "
- + sheet.getName());
-
+ if (log.isTraceEnabled())
+ log.debug("Instantiate sheet " + sheet.getName());
+
Cell[] firstRow = sheet.getRow(0);
// TODO: ability to map sheet names and class names
tempRefs.put(sheet.getName(), new ArrayList<Object>());
String keyProperty = firstRow[0].getContents();
- for (int i = 1; i < sheet.getRows(); i++) {
+ for (int row = 1; row < sheet.getRows(); row++) {
if (log.isTraceEnabled())
- log.trace(" row " + i);
+ log.trace(" row " + row);
- Cell[] currentRow = sheet.getRow(i);
+ Cell[] currentRow = sheet.getRow(row);
BeanWrapper bw = new BeanWrapperImpl(clss);
- for (int j = 0; j < firstRow.length; j++) {
- String pName = firstRow[j].getContents();
+ for (int col = 0; col < firstRow.length; col++) {
+ String pName = firstRow[col].getContents();
- Cell cell = currentRow[j];
+ Cell cell = currentRow[col];
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));
+ // assume no double letters!!
+ String targetRowStr = formula.substring(index + 2);
+ if (targetRowStr.charAt(0) == '$')
+ targetRowStr = targetRowStr.substring(1);
+ Integer targetRow = Integer.parseInt(targetRowStr);
+ links.add(new Reference(bw.getWrappedInstance(),
+ pName, targetSheet, targetRow));
if (log.isTraceEnabled())
log.debug(" formula: " + formula
+ " | targetSheet=" + targetSheet
+ ", targetRow=" + targetRow);
} else {
- bw.setPropertyValue(pName, cell.getContents());
+ String contents = cell.getContents();
+ if (pName.equals(keyProperty)
+ && !StringUtils.hasText(contents)) {
+ // auto allocate key column if empty
+ contents = Integer.toString(row);
+ }
+
+ if (pName.charAt(0) == '#') {// externalRef
+ links.add(new Reference(
+ bw.getWrappedInstance(), pName
+ .substring(1), contents));
+ } else {
+ bw.setPropertyValue(pName, contents);
+ }
if (log.isTraceEnabled())
- log.debug(" " + pName + "="
- + cell.getContents());
+ log.debug(" " + pName + "=" + contents);
}
}// properties set
if (log.isDebugEnabled())
log.debug(model.get(clss).size() + " objects of type "
- + clss);
+ + clss + " instantiated");
}
- if (log.isDebugEnabled())
- log.debug("Link " + links.size() + " references");
- for (Link link : links) {
+ for (Reference link : links) {
BeanWrapper bw = new BeanWrapperImpl(link.object);
- Object targetObject = tempRefs.get(link.getTargetSheet()).get(
- link.targetRow - 2);
+ Object targetObject;
+ if (link.getExternalRef() != null) {
+ String ref = link.getExternalRef();
+ if (externalRefs.containsKey(ref))
+ targetObject = externalRefs.get(ref);
+ else if (applicationContext != null)
+ targetObject = applicationContext.getBean(ref);
+ else {
+ targetObject = null;
+ log.warn("Ref " + ref + " not found");
+ }
+ } else {
+ targetObject = tempRefs.get(link.getTargetSheet()).get(
+ link.targetRow - 2);
+ }
bw.setPropertyValue(link.property, targetObject);
}
+ if (log.isDebugEnabled())
+ log.debug(links.size() + " references linked");
} catch (Exception e) {
throw new ArgeoServerException("Cannot load workbook", e);
@SuppressWarnings("unchecked")
public <T> T getByKey(Class<T> clss, Object key) {
- return (T) model.get(clss).get(key);
+ return (T) model.get(findClass(clss)).get(key);
+ }
+
+ @SuppressWarnings("unchecked")
+ public <T> List<T> list(Class<T> clss, Object filter) {
+ return new ArrayList<T>((Collection<T>) model.get(findClass(clss))
+ .values());
+ }
+
+ @SuppressWarnings("unchecked")
+ protected Class findClass(Class parent) {
+ if (model.containsKey(parent))
+ return parent;
+
+ for (Class clss : model.keySet()) {
+ if (parent.isAssignableFrom(clss))
+ return clss;// return the first found
+ }
+ throw new ArgeoServerException("No implementing class found for "
+ + parent);
+ }
+
+ public void setApplicationContext(ApplicationContext applicationContext)
+ throws BeansException {
+ this.applicationContext = applicationContext;
+ }
+
+ public void setExternalRefs(Map<String, Object> externalRefs) {
+ this.externalRefs = externalRefs;
+ }
+
+ public Map<String, Object> getExternalRefs() {
+ return externalRefs;
}
- public static class Link {
+ public static class Reference {
private Object object;
private String property;
private String targetSheet;
private Integer targetRow;
+ private String externalRef;
- public Link(Object object, String property, String targetSheet,
+ public Reference(Object object, String property, String targetSheet,
Integer targetRow) {
- super();
this.object = object;
this.property = property;
this.targetSheet = targetSheet;
this.targetRow = targetRow;
}
+ public Reference(Object object, String property, String externalRef) {
+ this.object = object;
+ this.property = property;
+ this.externalRef = externalRef;
+ }
+
public Object getObject() {
return object;
}
return targetRow;
}
+ public String getExternalRef() {
+ return externalRef;
+ }
+
}
}
package org.argeo.server.jxl.dao;
import java.io.InputStream;
+import java.util.List;
import junit.framework.TestCase;
public class JxlDaoSupportTest extends TestCase {
- public void testBasic() {
+ public void testBasic() throws Exception {
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());
+ jsd.getExternalRefs().put("test", new OtherObject());
+
+ InputStream in = null;
+ try {
+ 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());
+
+ List<SimpleObject> simpleObjects = jsd.list(SimpleObject.class,
+ null);
+ assertEquals(2, simpleObjects.size());
+ } finally {
+ if (in != null)
+ in.close();
+ }
+
}
}