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=76ac7dd55d4f23466536ad57eae8799346ea44e0;hb=d5367a2b0896c064f7d184a16d5c0efb0373d930;hp=48dd8b233d0e6ff84a36a6d3ab4b8db318de6d26;hpb=d8c2b3440de26af2f60e90e4e1b1f4fdbc7b0735;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 48dd8b233..76ac7dd55 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,14 +1,19 @@ 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.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; @@ -21,18 +26,22 @@ import org.argeo.server.dao.LightDaoSupport; import org.springframework.beans.BeanWrapper; 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 extends AbstractTabularDaoSupport implements LightDaoSupport, ApplicationContextAware, InitializingBean { private final static Log log = LogFactory.getLog(JxlDaoSupport.class); - private Integer charset = 0; + private String encoding = "cp1252"; + private Locale locale = null; 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); @@ -44,23 +53,52 @@ public class JxlDaoSupport extends AbstractTabularDaoSupport implements 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); - Cell[] firstRow = sheet.getRow(0); - - Class clss = findClassToInstantiate(sheet); - // model.put(clss, new TreeMap()); + String tableName; + int hashIndex = sheetName.lastIndexOf('#'); + if (hashIndex >= 0) { + tableName = sheetName.substring(0, hashIndex); + } else { + tableName = sheetName; + } - // tempRefs.put(sheet.getName(), new ArrayList()); + Class clss = findClassToInstantiate(tableName); - String keyProperty = firstRow[0].getContents(); + 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); + } - if (keyProperty.charAt(keyProperty.length() - 1) == '>') { - loadAsColumns(clss, keyProperty.substring(0, - keyProperty.length() - 1), sheet, firstRow, references); } else { - loadAsRows(clss, keyProperty, sheet, firstRow, references); + + Cell[] firstRow = sheet.getRow(0); + 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); + } } } @@ -122,20 +160,13 @@ public class JxlDaoSupport extends AbstractTabularDaoSupport implements String firstColContents = firstColumn[row].getContents(); mapRows: for (; row < column.length; row++) { cell = column[row]; - 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 { - map.put(key, cell.getContents()); - } + + ", 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) @@ -164,6 +195,75 @@ public class JxlDaoSupport extends AbstractTabularDaoSupport implements }// 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, String keyProperty, Integer row, List references) throws JXLException { @@ -190,37 +290,41 @@ public class JxlDaoSupport extends AbstractTabularDaoSupport implements + 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(); - // } + 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); } } + 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; + } + /** Returns true if property was set (thus bypassing standard process). */ protected Boolean overrideCell(Cell cell, BeanWrapper bw, String propertyName, String keyProperty, Integer row, @@ -228,47 +332,12 @@ public class JxlDaoSupport extends AbstractTabularDaoSupport implements return false; } - /** - * @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 void setEncoding(String encoding) { + this.encoding = encoding; } - 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 - } - - 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()); - } - } - - if (clss == null) - throw new ArgeoServerException("Cannot find a class for sheet " - + sheet.getName()); - - return clss; + public void setLocale(Locale locale) { + this.locale = locale; } - public void setCharset(Integer charset) { - this.charset = charset; - } }