X-Git-Url: http://git.argeo.org/?a=blobdiff_plain;f=server%2Fruntime%2Forg.argeo.server.jxl%2Fsrc%2Fmain%2Fjava%2Forg%2Fargeo%2Fserver%2Fjxl%2Fdao%2FJxlDaoSupport.java;h=48dd8b233d0e6ff84a36a6d3ab4b8db318de6d26;hb=d8c2b3440de26af2f60e90e4e1b1f4fdbc7b0735;hp=390fae72be070277692ad22dd19e06cc89476aaa;hpb=db84ccc332884c4b89b755923538cecf2f00df68;p=lgpl%2Fargeo-commons.git 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 index 390fae72b..48dd8b233 100644 --- 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 @@ -1,147 +1,274 @@ 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.CellType; import jxl.FormulaCell; +import jxl.JXLException; 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.factory.InitializingBean; +import org.springframework.context.ApplicationContextAware; +import org.springframework.util.StringUtils; -public class JxlDaoSupport { +public class JxlDaoSupport extends AbstractTabularDaoSupport implements + LightDaoSupport, ApplicationContextAware, InitializingBean { private final static Log log = LogFactory.getLog(JxlDaoSupport.class); - private ClassLoader classLoader = getClass().getClassLoader(); + private Integer charset = 0; - private Map> model = new HashMap>(); + protected void load(InputStream in, List references) { + try { + WorkbookSettings workbookSettings = new WorkbookSettings(); + workbookSettings.setCharacterSet(charset); + Workbook workbook = Workbook.getWorkbook(in, workbookSettings); + for (Sheet sheet : workbook.getSheets()) { + loadSheet(sheet, references); + } + } catch (Exception e) { + throw new ArgeoServerException("Cannot load workbook", e); + } + } - public void load(InputStream in) { + protected void loadSheet(Sheet sheet, List references) + throws JXLException { + if (log.isTraceEnabled()) + log.debug("Instantiate sheet " + sheet.getName()); - try { - // used to resolve inner references - Map> tempRefs = new HashMap>(); - List links = new ArrayList(); + Cell[] firstRow = sheet.getRow(0); - Workbook workbook = Workbook.getWorkbook(in); + Class clss = findClassToInstantiate(sheet); + // model.put(clss, new TreeMap()); - 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++) { + // tempRefs.put(sheet.getName(), new ArrayList()); + + String keyProperty = firstRow[0].getContents(); + + if (keyProperty.charAt(keyProperty.length() - 1) == '>') { + loadAsColumns(clss, keyProperty.substring(0, + keyProperty.length() - 1), sheet, firstRow, references); + } else { + loadAsRows(clss, keyProperty, sheet, firstRow, references); + } + } + + protected void loadAsRows(Class clss, String keyProperty, Sheet sheet, + Cell[] firstRow, List 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 + + 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 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(" 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)); + log.trace(" start building map " + propertyName); + row++; + Map map = new HashMap(); + String firstColContents = firstColumn[row].getContents(); + mapRows: for (; row < column.length; row++) { + cell = column[row]; - if (log.isTraceEnabled()) - log.debug(" formula: " + formula - + " | content: " + cell.getContents() - + " | targetSheet=" + targetSheet - + ", targetRow=" + targetRow); + Object key = firstColContents; + CellType type = cell.getType(); + if (log.isTraceEnabled()) + log.trace(" row=" + row + ", firstColContents=" + + firstColContents + ", key=" + key + + ", type=" + type); + if (type.equals(CellType.NUMBER)) { + map + .put(key, Double.parseDouble(cell + .getContents())); } else { - bw.setPropertyValue(pName, cell.getContents()); + map.put(key, cell.getContents()); + } + // 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 + "=" - + cell.getContents()); + 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); } + saveOrUpdate(bw.getPropertyValue(keyProperty), bw + .getWrappedInstance(), clss); + // tempRefs.get(sheet.getName()).add(bw.getWrappedInstance()); + registerInTabularView(sheet.getName(), bw.getWrappedInstance()); + }// columns + } - 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); + protected void loadCell(Cell cell, BeanWrapper bw, String propertyName, + String keyProperty, Integer row, List 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 { + String contents = cell.getContents(); + + // if (cell.getType() == CellType.LABEL) { + // LabelCell lc = (LabelCell) cell; + // contents = lc.getString(); + // } else if (cell.getType() == CellType.NUMBER) { + // NumberCell nc = (NumberCell) cell; + // contents = new Double(nc.getValue()).toString(); + // } else { + // contents = cell.getContents(); + // } + + if (propertyName.equals(keyProperty) + && !StringUtils.hasText(contents)) { + // auto allocate key column if empty + contents = Integer.toString(row); } - } catch (Exception e) { - throw new ArgeoServerException("Cannot load workbook", e); + if (propertyName.charAt(0) == '#') {// externalRef + references.add(new Reference(bw.getWrappedInstance(), + propertyName.substring(1), contents)); + } else { + bw.setPropertyValue(propertyName, contents); + } + + if (log.isTraceEnabled()) + log.debug(" " + propertyName + "=" + contents); } + } - @SuppressWarnings("unchecked") - public T getByKey(Class clss, Object key) { - return (T) model.get(clss).get(key); + /** Returns true if property was set (thus bypassing standard process). */ + protected Boolean overrideCell(Cell cell, BeanWrapper bw, + String propertyName, String keyProperty, Integer row, + List references) { + return false; } - 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; - } + /** + * @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 references, Map> tempRefs) { + throw new UnsupportedOperationException(); + } - public Object getObject() { - return object; + protected Class findClassToInstantiate(Sheet sheet) { + // TODO: ability to map sheet names and class names + String className = sheet.getName(); + Class clss = null; + try { + clss = getClassLoader().loadClass(className); + return clss; + } catch (ClassNotFoundException e) { + // silent } - public String getProperty() { - return property; + scannedPkgs: for (String pkg : getScannedPackages()) { + try { + clss = getClassLoader().loadClass(pkg.trim() + "." + className); + break scannedPkgs; + } catch (ClassNotFoundException e) { + // silent + if (log.isTraceEnabled()) + log.trace(e.getMessage()); + } } - public String getTargetSheet() { - return targetSheet; - } + if (clss == null) + throw new ArgeoServerException("Cannot find a class for sheet " + + sheet.getName()); - public Integer getTargetRow() { - return targetRow; - } + return clss; + } + public void setCharset(Integer charset) { + this.charset = charset; } }