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=64bcf03dc46e4f82aa72dd72289f49c9ab31a704;hb=6a40c8111e3dfd7e2ca31a0d1aefd4d9579d2928;hp=dbd0793d9244e69aecfb8a9678b1a4fcfb7cede1;hpb=12cfed09974f82584c66c460500b5b3a61789e7e;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 dbd0793d9..64bcf03dc 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,145 +1,267 @@ package org.argeo.server.jxl.dao; import java.io.InputStream; -import java.nio.charset.Charset; 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.TreeMap; +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.io.IOUtils; 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.beans.factory.InitializingBean; -import org.springframework.beans.factory.generic.GenericBeanFactoryAccessor; -import org.springframework.context.ApplicationContext; import org.springframework.context.ApplicationContextAware; import org.springframework.core.io.Resource; import org.springframework.util.StringUtils; -public class JxlDaoSupport implements LightDaoSupport, ApplicationContextAware, - InitializingBean { +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 List> additionalClasses = new ArrayList>(); + private String encoding = "cp1252"; + private Locale locale = null; - private Map, Map> model = new HashMap, Map>(); - - private Map externalRefs = new HashMap(); - - private List scannedPackages = new ArrayList(); - - private List workbooks = new ArrayList(); - - private Integer charset = 0; - - public void afterPropertiesSet() throws Exception { - init(); - } - - public void init() { - // used to resolve inner references - Map> tempRefs = new HashMap>(); - - List references = new ArrayList(); - - for (Resource res : workbooks) { - InputStream in = null; - try { - in = res.getInputStream(); - load(in, references, tempRefs); - } catch (Exception e) { - throw new ArgeoServerException("Cannot load stream", e); - } finally { - IOUtils.closeQuietly(in); - } - } - - // Inject references - for (Reference ref : references) { - injectReference(ref, tempRefs); - } - if (log.isDebugEnabled()) - log.debug(references.size() + " references linked"); - } - - public List> getSupportedClasses() { - List> res = new ArrayList>(); - res.addAll(additionalClasses); - res.addAll(model.keySet()); - return res; - } - - public void load(InputStream in, List references, - Map> tempRefs) { + protected void load(InputStream in, List references) { try { WorkbookSettings workbookSettings = new WorkbookSettings(); - workbookSettings.setCharacterSet(charset); + workbookSettings.setEncoding(encoding); + if (locale != null) + workbookSettings.setLocale(locale); Workbook workbook = Workbook.getWorkbook(in, workbookSettings); for (Sheet sheet : workbook.getSheets()) { - loadSheet(sheet, references, tempRefs); + loadSheet(sheet, references); } } catch (Exception e) { throw new ArgeoServerException("Cannot load workbook", e); } } - protected void loadSheet(Sheet sheet, List references, - Map> tempRefs) throws JXLException { + protected void loadSheet(Sheet sheet, List references) + throws JXLException { + String sheetName = sheet.getName(); if (log.isTraceEnabled()) - log.debug("Instantiate sheet " + sheet.getName()); + log.debug("Instantiate sheet " + sheetName); + + String tableName; + int hashIndex = sheetName.lastIndexOf('#'); + if (hashIndex >= 0) { + tableName = sheetName.substring(0, hashIndex); + } else { + tableName = sheetName; + } - Cell[] firstRow = sheet.getRow(0); + 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); + } + + } else { - Class clss = findClassToInstantiate(sheet); - model.put(clss, new TreeMap()); + Cell[] firstRow = sheet.getRow(0); + String keyProperty = firstRow[0].getContents(); - tempRefs.put(sheet.getName(), new ArrayList()); + 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(); + 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 = new BeanWrapperImpl(clss); + 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, tempRefs)) + references)) continue cells; loadCell(cell, bw, pName, keyProperty, row, references); } }// cells - model.get(clss).put(bw.getPropertyValue(keyProperty), - bw.getWrappedInstance()); - tempRefs.get(sheet.getName()).add(bw.getWrappedInstance()); + saveOrUpdate(bw.getPropertyValue(keyProperty), bw + .getWrappedInstance(), clss); + // tempRefs.get(sheet.getName()).add(bw.getWrappedInstance()); + registerInTabularView(sheet.getName(), bw.getWrappedInstance()); } + } - if (log.isDebugEnabled()) - log.debug(model.get(clss).size() + " objects of type " + clss - + " instantiated"); + 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(" start building map " + propertyName); + row++; + Map map = new HashMap(); + 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.trace(" set map " + propertyName + + " of size " + map.size()); + break mapRows;// map is over + } + } + } else { + loadCell(cell, bw, propertyName, keyProperty, row, + references); + } + + } + saveOrUpdate(bw.getPropertyValue(keyProperty), bw + .getWrappedInstance(), clss); + // tempRefs.get(sheet.getName()).add(bw.getWrappedInstance()); + registerInTabularView(sheet.getName(), bw.getWrappedInstance()); + }// columns + } + + protected void loadAsObject(BeanWrapper bw, Sheet sheet, + List 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 map = new HashMap(); + if (currentRow.length == 1 + || currentRow[1].getContents().trim().equals("")) { + // simple map + } else { + // map of maps + List subKeys = new ArrayList(); + 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 subMap = new HashMap(); + + 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 + } + } + + } + } else if (List.class.isAssignableFrom(rowType)) { + throw new UnsupportedOperationException(); + } else { + bw.setPropertyValue(propertyName, getCellValue(currentRow[1])); + } + } } protected void loadCell(Cell cell, BeanWrapper bw, String propertyName, @@ -159,226 +281,81 @@ public class JxlDaoSupport implements LightDaoSupport, ApplicationContextAware, if (targetRowStr.charAt(0) == '$') targetRowStr = targetRowStr.substring(1); Integer targetRow = Integer.parseInt(targetRowStr); - references.add(new Reference(bw.getWrappedInstance(), propertyName, - targetSheet, targetRow)); + 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(); + Object cellValue = getCellValue(cell); + if (propertyName.equals(keyProperty) - && !StringUtils.hasText(contents)) { + && !StringUtils.hasText(cellValue.toString())) { // auto allocate key column if empty - contents = Integer.toString(row); + cellValue = Integer.toString(row); } if (propertyName.charAt(0) == '#') {// externalRef references.add(new Reference(bw.getWrappedInstance(), - propertyName.substring(1), contents)); + propertyName.substring(1), cellValue.toString())); } else { - bw.setPropertyValue(propertyName, contents); + bw.setPropertyValue(propertyName, cellValue); } if (log.isTraceEnabled()) - log.debug(" " + propertyName + "=" + contents); + log.debug(" " + propertyName + "=" + cellValue); } } - /** 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, Map> tempRefs) { - return false; - } - - protected void injectReference(Reference reference, - Map> tempRefs) { - BeanWrapper bw = new BeanWrapperImpl(reference.object); - Object targetObject; - if (reference.getExternalRef() != null) { - String ref = reference.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 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 { - targetObject = tempRefs.get(reference.getTargetSheet()).get( - reference.targetRow - 2); + contents = cell.getContents(); } - bw.setPropertyValue(reference.property, targetObject); - + return contents; } - protected Class findClassToInstantiate(Sheet sheet) { - // TODO: ability to map sheet names and class names - String className = sheet.getName(); - Class clss = null; - try { - clss = classLoader.loadClass(className); - return clss; - } catch (ClassNotFoundException e) { - // silent - } - - scannedPkgs: for (String pkg : scannedPackages) { - try { - clss = classLoader.loadClass(pkg.trim() + "." + className); - break scannedPkgs; - } catch (ClassNotFoundException e) { - // silent - if (log.isTraceEnabled()) - log.trace(e.getMessage()); - } - } - - if (clss == null) - throw new ArgeoServerException("Cannot find a class for sheet " - + sheet.getName()); - - return clss; - } - - @SuppressWarnings("unchecked") - public T getByKey(Class clss, Object key) { - return (T) model.get(findClass(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; } /** - * Slow. - * - * @return the first found + * @deprecated use + * {@link #overrideCell(Cell, BeanWrapper, String, String, Integer, List)} + * instead. This method is not called anymore. */ - public T getByField(Class clss, String field, Object value) { - List all = list(clss, null); - T res = null; - for (T obj : all) { - if (new BeanWrapperImpl(obj).getPropertyValue(field).equals(value)) { - res = obj; - break; - } - } - return res; - } - - @SuppressWarnings("unchecked") - public List list(Class clss, Object filter) { - List res = new ArrayList(); - - Class classToUse = findClass(clss); - if (classToUse != null) - res.addAll((Collection) model.get(classToUse).values()); - - if (applicationContext != null) - res.addAll(new GenericBeanFactoryAccessor(applicationContext) - .getBeansOfType(clss).values()); - - return res; - } - - @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 - } - return null; - } - - public void setApplicationContext(ApplicationContext applicationContext) - throws BeansException { - this.applicationContext = applicationContext; - } - - public void setExternalRefs(Map externalRefs) { - this.externalRefs = externalRefs; - } - - public Map getExternalRefs() { - return externalRefs; + protected Boolean overrideCell(Cell cell, BeanWrapper bw, + String propertyName, String keyProperty, Integer row, + List references, Map> tempRefs) { + throw new UnsupportedOperationException(); } - public void setScannedPackages(List scannedPackages) { - this.scannedPackages = scannedPackages; + public void setEncoding(String encoding) { + this.encoding = encoding; } - public List getScannedPackages() { - return scannedPackages; + public void setLocale(Locale locale) { + this.locale = locale; } + /** @deprecated use {@link #setResources(List)} instead. */ public void setWorkbooks(List workbooks) { - this.workbooks = workbooks; - } - - public List getWorkbooks() { - return workbooks; - } - - public void setClassLoader(ClassLoader classLoader) { - this.classLoader = classLoader; - } - - public List> getAdditionalClasses() { - return additionalClasses; - } - - public void setAdditionalClasses(List> additionalClasses) { - this.additionalClasses = additionalClasses; - } - - public void setCharset(Integer charset) { - this.charset = charset; - } - - 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; - } - - public Integer getTargetRow() { - return targetRow; - } - - public String getExternalRef() { - return externalRef; - } - + 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###"); } }