import java.io.InputStream;
import java.util.ArrayList;
-import java.util.Collection;
import java.util.HashMap;
import java.util.List;
+import java.util.Locale;
import java.util.Map;
+import java.util.StringTokenizer;
import jxl.Cell;
+import jxl.CellType;
import jxl.FormulaCell;
+import jxl.JXLException;
+import jxl.LabelCell;
+import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
+import jxl.WorkbookSettings;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.argeo.server.ArgeoServerException;
+import org.argeo.server.dao.AbstractTabularDaoSupport;
+import org.argeo.server.dao.LightDaoSupport;
import org.springframework.beans.BeanWrapper;
-import org.springframework.beans.BeanWrapperImpl;
-import org.springframework.beans.BeansException;
-import org.springframework.context.ApplicationContext;
+import org.springframework.beans.factory.InitializingBean;
import org.springframework.context.ApplicationContextAware;
+import org.springframework.core.io.Resource;
import org.springframework.util.StringUtils;
-public class JxlDaoSupport implements ApplicationContextAware {
+public class JxlDaoSupport extends AbstractTabularDaoSupport implements
+ LightDaoSupport, ApplicationContextAware, InitializingBean {
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<String, Object> externalRefs = new HashMap<String, Object>();
-
- public void load(InputStream in) {
+ private String encoding = "cp1252";
+ private Locale locale = null;
+ protected void load(InputStream in, List<Reference> references) {
try {
- // used to resolve inner references
- Map<String, List<Object>> tempRefs = new HashMap<String, List<Object>>();
- List<Reference> links = new ArrayList<Reference>();
+ WorkbookSettings workbookSettings = new WorkbookSettings();
+ workbookSettings.setEncoding(encoding);
+ if (locale != null)
+ workbookSettings.setLocale(locale);
+ Workbook workbook = Workbook.getWorkbook(in, workbookSettings);
+ for (Sheet sheet : workbook.getSheets()) {
+ loadSheet(sheet, references);
+ }
+ } catch (Exception e) {
+ throw new ArgeoServerException("Cannot load workbook", e);
+ }
+ }
- Workbook workbook = Workbook.getWorkbook(in);
+ protected void loadSheet(Sheet sheet, List<Reference> references)
+ throws JXLException {
+ String sheetName = sheet.getName();
+ if (log.isTraceEnabled())
+ log.debug("Instantiate sheet " + sheetName);
+
+ String tableName;
+ int hashIndex = sheetName.lastIndexOf('#');
+ if (hashIndex >= 0) {
+ tableName = sheetName.substring(0, hashIndex);
+ } else {
+ tableName = sheetName;
+ }
- for (Sheet sheet : workbook.getSheets()) {
- if (log.isTraceEnabled())
- log.debug("Instantiate sheet " + sheet.getName());
+ Class<?> clss = findClassToInstantiate(tableName);
+
+ if (hashIndex >= 0) {
+ // see
+ // http://stackoverflow.com/questions/451452/valid-characters-for-excel-sheet-names
+ BeanWrapper bw = newBeanWrapper(clss);
+ StringTokenizer espSt = new StringTokenizer(sheetName
+ .substring(hashIndex + 1), "&=");
+ String keyProperty = null;
+ while (espSt.hasMoreTokens()) {
+ String fieldName = espSt.nextToken();
+ if (keyProperty == null)
+ keyProperty = fieldName;
+ if (!espSt.hasMoreTokens())
+ throw new ArgeoServerException("Badly formatted sheetname "
+ + sheetName);
+ String fieldValue = espSt.nextToken();
+ bw.setPropertyValue(fieldName, fieldValue);
+ loadAsObject(bw, sheet, references);
+ saveOrUpdate(bw.getPropertyValue(keyProperty), bw
+ .getWrappedInstance(), clss);
+ }
- Cell[] firstRow = sheet.getRow(0);
+ } else {
- // TODO: ability to map sheet names and class names
- String className = sheet.getName();
- Class<?> clss = classLoader.loadClass(className);
- model.put(clss, new HashMap<Object, Object>());
+ Cell[] firstRow = sheet.getRow(0);
+ String keyProperty = firstRow[0].getContents();
- tempRefs.put(sheet.getName(), new ArrayList<Object>());
+ if (keyProperty.charAt(keyProperty.length() - 1) == '>') {
+ loadAsColumns(clss, keyProperty.substring(0, keyProperty
+ .length() - 1), sheet, firstRow, references);
+ } else {
+ loadAsRows(clss, keyProperty, sheet, firstRow, references);
+ }
+ }
+ }
- String keyProperty = firstRow[0].getContents();
- for (int row = 1; row < sheet.getRows(); row++) {
- if (log.isTraceEnabled())
- log.trace(" row " + row);
-
- Cell[] currentRow = sheet.getRow(row);
- BeanWrapper bw = new BeanWrapperImpl(clss);
- for (int col = 0; col < firstRow.length; col++) {
- String pName = firstRow[col].getContents();
-
- 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!!
- 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));
+ protected void loadAsRows(Class<?> clss, String keyProperty, Sheet sheet,
+ Cell[] firstRow, List<Reference> references) throws JXLException {
+ for (int row = 1; row < sheet.getRows(); row++) {
+ if (log.isTraceEnabled())
+ log.trace(" row " + row);
+
+ Cell[] currentRow = sheet.getRow(row);
+ BeanWrapper bw = newBeanWrapper(clss);
+ cells: for (int col = 0; col < firstRow.length; col++) {
+ String pName = firstRow[col].getContents();
+
+ if (col < currentRow.length) {
+ Cell cell = currentRow[col];
+ if (overrideCell(cell, bw, pName, keyProperty, row,
+ references))
+ continue cells;
+ loadCell(cell, bw, pName, keyProperty, row, references);
+ }
+ }// cells
- if (log.isTraceEnabled())
- log.debug(" formula: " + formula
- + " | content: " + cell.getContents()
- + " | targetSheet=" + targetSheet
- + ", targetRow=" + targetRow);
- } else {
- 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);
- }
+ saveOrUpdate(bw.getPropertyValue(keyProperty), bw
+ .getWrappedInstance(), clss);
+ // tempRefs.get(sheet.getName()).add(bw.getWrappedInstance());
+ registerInTabularView(sheet.getName(), bw.getWrappedInstance());
+ }
+ }
+ protected void loadAsColumns(Class<?> clss, String keyProperty,
+ Sheet sheet, Cell[] firstRow, List<Reference> references)
+ throws JXLException {
+ Cell[] firstColumn = sheet.getColumn(0);
+
+ for (int col = 1; col < firstRow.length; col++) {
+ if (log.isTraceEnabled())
+ log.trace(" column " + col);
+ BeanWrapper bw = newBeanWrapper(clss);
+ Cell[] column = sheet.getColumn(col);
+ for (int row = 0; row < column.length; row++) {
+ Cell cell = column[row];
+
+ String propertyName;
+ if (row == 0)
+ propertyName = keyProperty;
+ else
+ propertyName = firstColumn[row].getContents();
+
+ Class<?> rowType = bw.getPropertyType(propertyName);
+ if (log.isTraceEnabled())
+ log.trace(" " + propertyName + " rowType="
+ + rowType.getName());
+ if (Map.class.isAssignableFrom(rowType)) {
+ if (log.isTraceEnabled())
+ log.trace(" start building map " + propertyName);
+ row++;
+ Map<Object, Object> map = new HashMap<Object, Object>();
+ String firstColContents = firstColumn[row].getContents();
+ mapRows: for (; row < column.length; row++) {
+ cell = column[row];
+ Object key = firstColContents;
+ if (log.isTraceEnabled())
+ log.trace(" row=" + row + ", firstColContents="
+ + firstColContents + ", key=" + key
+ + ", type=" + cell.getType());
+ Object cellValue = getCellValue(cell);
+ map.put(key, cellValue);
+
+ // check next row too see if one should break
+ if (row < firstColumn.length - 1)
+ firstColContents = firstColumn[row + 1]
+ .getContents();
+ if (bw.isWritableProperty(firstColContents)
+ || firstColContents.trim().equals("")
+ || row == firstColumn.length - 1) {
+ bw.setPropertyValue(propertyName, map);
if (log.isTraceEnabled())
- log.debug(" " + pName + "=" + contents);
+ log.trace(" set map " + propertyName
+ + " of size " + map.size());
+ break mapRows;// map is over
}
- }// properties set
-
- model.get(clss).put(bw.getPropertyValue(keyProperty),
- bw.getWrappedInstance());
- tempRefs.get(sheet.getName()).add(bw.getWrappedInstance());
+ }
+ } else {
+ loadCell(cell, bw, propertyName, keyProperty, row,
+ references);
}
- if (log.isDebugEnabled())
- log.debug(model.get(clss).size() + " objects of type "
- + clss + " instantiated");
}
+ saveOrUpdate(bw.getPropertyValue(keyProperty), bw
+ .getWrappedInstance(), clss);
+ // tempRefs.get(sheet.getName()).add(bw.getWrappedInstance());
+ registerInTabularView(sheet.getName(), bw.getWrappedInstance());
+ }// columns
+ }
- for (Reference link : links) {
- BeanWrapper bw = new BeanWrapperImpl(link.object);
- 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");
- }
+ protected void loadAsObject(BeanWrapper bw, Sheet sheet,
+ List<Reference> references) {
+ Cell[] firstColumn = sheet.getColumn(0);
+ for (int row = 0; row < firstColumn.length; row++) {
+ if (log.isTraceEnabled())
+ log.trace(" row " + row);
+ Cell[] currentRow = sheet.getRow(row);
+ String propertyName = firstColumn[row].getContents();
+ Class<?> rowType = bw.getPropertyType(propertyName);
+ if (Map.class.isAssignableFrom(rowType)) {
+ Map<Object, Object> map = new HashMap<Object, Object>();
+ if (currentRow.length == 1
+ || currentRow[1].getContents().trim().equals("")) {
+ // simple map
} else {
- targetObject = tempRefs.get(link.getTargetSheet()).get(
- link.targetRow - 2);
+ // map of maps
+ List<Object> subKeys = new ArrayList<Object>();
+ for (int col = 1; col < currentRow.length; col++) {
+ subKeys.add(getCellValue(currentRow[col]));
+ }
+ if (log.isTraceEnabled())
+ log.trace(" subKeys=" + subKeys);
+ row++;
+ String firstColContents = firstColumn[row].getContents();
+ mapRows: for (; row < firstColumn.length; row++) {
+ currentRow = sheet.getRow(row);
+
+ Object key = firstColContents;
+ Map<Object, Object> subMap = new HashMap<Object, Object>();
+
+ for (int col = 1; col < currentRow.length
+ && col < subKeys.size() + 1; col++) {
+ Object subKey = subKeys.get(col - 1);
+ Cell cell = currentRow[col];
+ if (log.isTraceEnabled())
+ log.trace(" row=" + row
+ + ", firstColContents="
+ + firstColContents + ", subKey="
+ + subKey + ", type=" + cell.getType());
+ Object cellValue = getCellValue(cell);
+ subMap.put(subKey, cellValue);
+ }
+ map.put(key, subMap);
+
+ // check next row too see if one should break
+ if (row < firstColumn.length - 1)
+ firstColContents = firstColumn[row + 1]
+ .getContents();
+ if (bw.isWritableProperty(firstColContents)
+ || firstColContents.trim().equals("")
+ || row == firstColumn.length - 1) {
+ log.trace(map);
+ bw.setPropertyValue(propertyName, map);
+ if (log.isTraceEnabled())
+ log.trace(" set map " + propertyName
+ + " of size " + map.size());
+ break mapRows;// map is over
+ }
+ }
+
}
- bw.setPropertyValue(link.property, targetObject);
+ } else if (List.class.isAssignableFrom(rowType)) {
+ throw new UnsupportedOperationException();
+ } else {
+ bw.setPropertyValue(propertyName, getCellValue(currentRow[1]));
}
- 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(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());
- }
+ protected void loadCell(Cell cell, BeanWrapper bw, String propertyName,
+ String keyProperty, Integer row, List<Reference> references)
+ throws JXLException {
+
+ if (cell instanceof FormulaCell) {
+ String formula = ((FormulaCell) cell).getFormula();
+ int index = formula.indexOf('!');
+ if (index < 0)
+ throw new ArgeoServerException("Cannot interpret formula "
+ + formula);
+ ;
+ String targetSheet = formula.substring(0, index);
+ // assume no double letters!!
+ String targetRowStr = formula.substring(index + 2);
+ if (targetRowStr.charAt(0) == '$')
+ targetRowStr = targetRowStr.substring(1);
+ Integer targetRow = Integer.parseInt(targetRowStr);
+ references.add(new TabularInternalReference(
+ bw.getWrappedInstance(), propertyName, targetSheet,
+ targetRow));
+
+ if (log.isTraceEnabled())
+ log.debug(" formula: " + formula + " | content: "
+ + cell.getContents() + " | targetSheet=" + targetSheet
+ + ", targetRow=" + targetRow);
+ } else {
+ Object cellValue = getCellValue(cell);
+
+ if (propertyName.equals(keyProperty)
+ && !StringUtils.hasText(cellValue.toString())) {
+ // auto allocate key column if empty
+ cellValue = Integer.toString(row);
+ }
- @SuppressWarnings("unchecked")
- protected Class findClass(Class parent) {
- if (model.containsKey(parent))
- return parent;
+ if (propertyName.charAt(0) == '#') {// externalRef
+ references.add(new Reference(bw.getWrappedInstance(),
+ propertyName.substring(1), cellValue.toString()));
+ } else {
+ bw.setPropertyValue(propertyName, cellValue);
+ }
- for (Class clss : model.keySet()) {
- if (parent.isAssignableFrom(clss))
- return clss;// return the first found
+ if (log.isTraceEnabled())
+ log.debug(" " + propertyName + "=" + cellValue);
}
- 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;
+ protected Object getCellValue(Cell cell) {
+ Object contents;
+ if (cell.getType() == CellType.LABEL) {
+ LabelCell lc = (LabelCell) cell;
+ contents = lc.getString();
+ } else if (cell.getType() == CellType.NUMBER) {
+ NumberCell nc = (NumberCell) cell;
+ contents = nc.getValue();
+ } else {
+ contents = cell.getContents();
+ }
+ return contents;
}
- public Map<String, Object> getExternalRefs() {
- return externalRefs;
+ /** Returns true if property was set (thus bypassing standard process). */
+ protected Boolean overrideCell(Cell cell, BeanWrapper bw,
+ String propertyName, String keyProperty, Integer row,
+ List<Reference> references) {
+ return false;
}
- public static class Reference {
- private Object object;
- private String property;
- private String targetSheet;
- private Integer targetRow;
- private String externalRef;
-
- public Reference(Object object, String property, String targetSheet,
- Integer targetRow) {
- 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;
- }
-
- public String getProperty() {
- return property;
- }
-
- public String getTargetSheet() {
- return targetSheet;
- }
+ /**
+ * @deprecated use
+ * {@link #overrideCell(Cell, BeanWrapper, String, String, Integer, List)}
+ * instead. This method is not called anymore.
+ */
+ protected Boolean overrideCell(Cell cell, BeanWrapper bw,
+ String propertyName, String keyProperty, Integer row,
+ List<Reference> references, Map<String, List<Object>> tempRefs) {
+ throw new UnsupportedOperationException();
+ }
- public Integer getTargetRow() {
- return targetRow;
- }
+ public void setEncoding(String encoding) {
+ this.encoding = encoding;
+ }
- public String getExternalRef() {
- return externalRef;
- }
+ public void setLocale(Locale locale) {
+ this.locale = locale;
+ }
+ /** @deprecated use {@link #setResources(List)} instead. */
+ public void setWorkbooks(List<Resource> workbooks) {
+ setResources(workbooks);
+ log.warn("###\n" + "### Use of the 'workbooks' property is deprecated!"
+ + " It will be removed in one of the next releases."
+ + " Use the 'resources' property instead." + "\n###");
}
}
projects / lgpl / argeo-commons.git / blobdiff