server/runtime/org.argeo.server.jxl/src/main/java/org/argeo/server/jxl/dao/JxlDaoSupport.java

   1 package org.argeo.server.jxl.dao;
   2
   3 import java.io.InputStream;
   4 import java.util.ArrayList;
   5 import java.util.HashMap;
   6 import java.util.List;
   7 import java.util.Locale;
   8 import java.util.Map;
   9 import java.util.StringTokenizer;
  10
  11 import jxl.Cell;
  12 import jxl.CellType;
  13 import jxl.FormulaCell;
  14 import jxl.JXLException;
  15 import jxl.LabelCell;
  16 import jxl.NumberCell;
  17 import jxl.Sheet;
  18 import jxl.Workbook;
  19 import jxl.WorkbookSettings;
  20
  21 import org.apache.commons.logging.Log;
  22 import org.apache.commons.logging.LogFactory;
  23 import org.argeo.server.ArgeoServerException;
  24 import org.argeo.server.dao.AbstractTabularDaoSupport;
  25 import org.argeo.server.dao.LightDaoSupport;
  26 import org.springframework.beans.BeanWrapper;
  27 import org.springframework.beans.factory.InitializingBean;
  28 import org.springframework.context.ApplicationContextAware;
  29 import org.springframework.core.io.Resource;
  30 import org.springframework.util.StringUtils;
  31
  32 public class JxlDaoSupport extends AbstractTabularDaoSupport implements
  33                 LightDaoSupport, ApplicationContextAware, InitializingBean {
  34         private final static Log log = LogFactory.getLog(JxlDaoSupport.class);
  35
  36         private String encoding = "cp1252";
  37         private Locale locale = null;
  38
  39         protected void load(InputStream in, List<Reference> references) {
  40                 try {
  41                         WorkbookSettings workbookSettings = new WorkbookSettings();
  42                         workbookSettings.setEncoding(encoding);
  43                         if (locale != null)
  44                                 workbookSettings.setLocale(locale);
  45                         Workbook workbook = Workbook.getWorkbook(in, workbookSettings);
  46                         for (Sheet sheet : workbook.getSheets()) {
  47                                 loadSheet(sheet, references);
  48                         }
  49                 } catch (Exception e) {
  50                         throw new ArgeoServerException("Cannot load workbook", e);
  51                 }
  52         }
  53
  54         protected void loadSheet(Sheet sheet, List<Reference> references)
  55                         throws JXLException {
  56                 String sheetName = sheet.getName();
  57                 if (log.isTraceEnabled())
  58                         log.debug("Instantiate sheet " + sheetName);
  59
  60                 String tableName;
  61                 int hashIndex = sheetName.lastIndexOf('#');
  62                 if (hashIndex >= 0) {
  63                         tableName = sheetName.substring(0, hashIndex);
  64                 } else {
  65                         tableName = sheetName;
  66                 }
  67
  68                 Class<?> clss = findClassToInstantiate(tableName);
  69
  70                 if (hashIndex >= 0) {
  71                         // see
  72                         // http://stackoverflow.com/questions/451452/valid-characters-for-excel-sheet-names
  73                         BeanWrapper bw = newBeanWrapper(clss);
  74                         StringTokenizer espSt = new StringTokenizer(sheetName
  75                                         .substring(hashIndex + 1), "&=");
  76                         String keyProperty = null;
  77                         while (espSt.hasMoreTokens()) {
  78                                 String fieldName = espSt.nextToken();
  79                                 if (keyProperty == null)
  80                                         keyProperty = fieldName;
  81                                 if (!espSt.hasMoreTokens())
  82                                         throw new ArgeoServerException("Badly formatted sheetname "
  83                                                         + sheetName);
  84                                 String fieldValue = espSt.nextToken();
  85                                 bw.setPropertyValue(fieldName, fieldValue);
  86                                 loadAsObject(bw, sheet, references);
  87                                 saveOrUpdate(bw.getPropertyValue(keyProperty), bw
  88                                                 .getWrappedInstance(), clss);
  89                         }
  90
  91                 } else {
  92
  93                         Cell[] firstRow = sheet.getRow(0);
  94                         String keyProperty = firstRow[0].getContents();
  95
  96                         if (keyProperty.charAt(keyProperty.length() - 1) == '>') {
  97                                 loadAsColumns(clss, keyProperty.substring(0, keyProperty
  98                                                 .length() - 1), sheet, firstRow, references);
  99                         } else {
 100                                 loadAsRows(clss, keyProperty, sheet, firstRow, references);
 101                         }
 102                 }
 103         }
 104
 105         protected void loadAsRows(Class<?> clss, String keyProperty, Sheet sheet,
 106                         Cell[] firstRow, List<Reference> references) throws JXLException {
 107                 for (int row = 1; row < sheet.getRows(); row++) {
 108                         if (log.isTraceEnabled())
 109                                 log.trace(" row " + row);
 110
 111                         Cell[] currentRow = sheet.getRow(row);
 112                         BeanWrapper bw = newBeanWrapper(clss);
 113                         cells: for (int col = 0; col < firstRow.length; col++) {
 114                                 String pName = firstRow[col].getContents();
 115
 116                                 if (col < currentRow.length) {
 117                                         Cell cell = currentRow[col];
 118                                         if (overrideCell(cell, bw, pName, keyProperty, row,
 119                                                         references))
 120                                                 continue cells;
 121                                         loadCell(cell, bw, pName, keyProperty, row, references);
 122                                 }
 123                         }// cells
 124
 125                         saveOrUpdate(bw.getPropertyValue(keyProperty), bw
 126                                         .getWrappedInstance(), clss);
 127                         // tempRefs.get(sheet.getName()).add(bw.getWrappedInstance());
 128                         registerInTabularView(sheet.getName(), bw.getWrappedInstance());
 129                 }
 130         }
 131
 132         protected void loadAsColumns(Class<?> clss, String keyProperty,
 133                         Sheet sheet, Cell[] firstRow, List<Reference> references)
 134                         throws JXLException {
 135                 Cell[] firstColumn = sheet.getColumn(0);
 136
 137                 for (int col = 1; col < firstRow.length; col++) {
 138                         if (log.isTraceEnabled())
 139                                 log.trace(" column " + col);
 140                         BeanWrapper bw = newBeanWrapper(clss);
 141                         Cell[] column = sheet.getColumn(col);
 142                         for (int row = 0; row < column.length; row++) {
 143                                 Cell cell = column[row];
 144
 145                                 String propertyName;
 146                                 if (row == 0)
 147                                         propertyName = keyProperty;
 148                                 else
 149                                         propertyName = firstColumn[row].getContents();
 150
 151                                 Class<?> rowType = bw.getPropertyType(propertyName);
 152                                 if (log.isTraceEnabled())
 153                                         log.trace(" " + propertyName + " rowType="
 154                                                         + rowType.getName());
 155                                 if (Map.class.isAssignableFrom(rowType)) {
 156                                         if (log.isTraceEnabled())
 157                                                 log.trace("  start building map " + propertyName);
 158                                         row++;
 159                                         Map<Object, Object> map = new HashMap<Object, Object>();
 160                                         String firstColContents = firstColumn[row].getContents();
 161                                         mapRows: for (; row < column.length; row++) {
 162                                                 cell = column[row];
 163                                                 Object key = firstColContents;
 164                                                 if (log.isTraceEnabled())
 165                                                         log.trace("   row=" + row + ", firstColContents="
 166                                                                         + firstColContents + ", key=" + key
 167                                                                         + ", type=" + cell.getType());
 168                                                 Object cellValue = getCellValue(cell);
 169                                                 map.put(key, cellValue);
 170
 171                                                 // check next row too see if one should break
 172                                                 if (row < firstColumn.length - 1)
 173                                                         firstColContents = firstColumn[row + 1]
 174                                                                         .getContents();
 175                                                 if (bw.isWritableProperty(firstColContents)
 176                                                                 || firstColContents.trim().equals("")
 177                                                                 || row == firstColumn.length - 1) {
 178                                                         bw.setPropertyValue(propertyName, map);
 179                                                         if (log.isTraceEnabled())
 180                                                                 log.trace(" set map " + propertyName
 181                                                                                 + " of size " + map.size());
 182                                                         break mapRows;// map is over
 183                                                 }
 184                                         }
 185                                 } else {
 186                                         loadCell(cell, bw, propertyName, keyProperty, row,
 187                                                         references);
 188                                 }
 189
 190                         }
 191                         saveOrUpdate(bw.getPropertyValue(keyProperty), bw
 192                                         .getWrappedInstance(), clss);
 193                         // tempRefs.get(sheet.getName()).add(bw.getWrappedInstance());
 194                         registerInTabularView(sheet.getName(), bw.getWrappedInstance());
 195                 }// columns
 196         }
 197
 198         protected void loadAsObject(BeanWrapper bw, Sheet sheet,
 199                         List<Reference> references) {
 200                 Cell[] firstColumn = sheet.getColumn(0);
 201                 for (int row = 0; row < firstColumn.length; row++) {
 202                         if (log.isTraceEnabled())
 203                                 log.trace(" row " + row);
 204                         Cell[] currentRow = sheet.getRow(row);
 205                         String propertyName = firstColumn[row].getContents();
 206                         Class<?> rowType = bw.getPropertyType(propertyName);
 207                         if (Map.class.isAssignableFrom(rowType)) {
 208                                 Map<Object, Object> map = new HashMap<Object, Object>();
 209                                 if (currentRow.length == 1
 210                                                 || currentRow[1].getContents().trim().equals("")) {
 211                                         // simple map
 212                                 } else {
 213                                         // map of maps
 214                                         List<Object> subKeys = new ArrayList<Object>();
 215                                         for (int col = 1; col < currentRow.length; col++) {
 216                                                 subKeys.add(getCellValue(currentRow[col]));
 217                                         }
 218                                         row++;
 219                                         String firstColContents = firstColumn[row].getContents();
 220                                         mapRows: for (; row < firstColumn.length; row++) {
 221                                                 currentRow = sheet.getRow(row);
 222
 223                                                 Object key = firstColContents;
 224                                                 Map<Object, Object> subMap = new HashMap<Object, Object>();
 225
 226                                                 for (int col = 0; col < currentRow.length
 227                                                                 && col < subKeys.size(); col++) {
 228                                                         Object subKey = subKeys.get(col);
 229                                                         Cell cell = currentRow[col];
 230                                                         if (log.isTraceEnabled())
 231                                                                 log.trace("   row=" + row
 232                                                                                 + ", firstColContents="
 233                                                                                 + firstColContents + ", subKey="
 234                                                                                 + subKey + ", type=" + cell.getType());
 235                                                         Object cellValue = getCellValue(cell);
 236                                                         subMap.put(subKey, cellValue);
 237                                                 }
 238                                                 map.put(key, subMap);
 239
 240                                                 // check next row too see if one should break
 241                                                 if (row < firstColumn.length - 1)
 242                                                         firstColContents = firstColumn[row + 1]
 243                                                                         .getContents();
 244                                                 if (bw.isWritableProperty(firstColContents)
 245                                                                 || firstColContents.trim().equals("")
 246                                                                 || row == firstColumn.length - 1) {
 247                                                         bw.setPropertyValue(propertyName, map);
 248                                                         if (log.isTraceEnabled())
 249                                                                 log.trace(" set map " + propertyName
 250                                                                                 + " of size " + map.size());
 251                                                         break mapRows;// map is over
 252                                                 }
 253                                         }
 254
 255                                 }
 256                         } else if (List.class.isAssignableFrom(rowType)) {
 257                                 throw new UnsupportedOperationException();
 258                         } else {
 259                                 bw.setPropertyValue(propertyName, getCellValue(currentRow[1]));
 260                         }
 261                 }
 262         }
 263
 264         protected void loadCell(Cell cell, BeanWrapper bw, String propertyName,
 265                         String keyProperty, Integer row, List<Reference> references)
 266                         throws JXLException {
 267
 268                 if (cell instanceof FormulaCell) {
 269                         String formula = ((FormulaCell) cell).getFormula();
 270                         int index = formula.indexOf('!');
 271                         if (index < 0)
 272                                 throw new ArgeoServerException("Cannot interpret formula "
 273                                                 + formula);
 274                         ;
 275                         String targetSheet = formula.substring(0, index);
 276                         // assume no double letters!!
 277                         String targetRowStr = formula.substring(index + 2);
 278                         if (targetRowStr.charAt(0) == '$')
 279                                 targetRowStr = targetRowStr.substring(1);
 280                         Integer targetRow = Integer.parseInt(targetRowStr);
 281                         references.add(new TabularInternalReference(
 282                                         bw.getWrappedInstance(), propertyName, targetSheet,
 283                                         targetRow));
 284
 285                         if (log.isTraceEnabled())
 286                                 log.debug("  formula: " + formula + " | content: "
 287                                                 + cell.getContents() + " | targetSheet=" + targetSheet
 288                                                 + ", targetRow=" + targetRow);
 289                 } else {
 290                         Object cellValue = getCellValue(cell);
 291
 292                         if (propertyName.equals(keyProperty)
 293                                         && !StringUtils.hasText(cellValue.toString())) {
 294                                 // auto allocate key column if empty
 295                                 cellValue = Integer.toString(row);
 296                         }
 297
 298                         if (propertyName.charAt(0) == '#') {// externalRef
 299                                 references.add(new Reference(bw.getWrappedInstance(),
 300                                                 propertyName.substring(1), cellValue.toString()));
 301                         } else {
 302                                 bw.setPropertyValue(propertyName, cellValue);
 303                         }
 304
 305                         if (log.isTraceEnabled())
 306                                 log.debug("  " + propertyName + "=" + cellValue);
 307                 }
 308
 309         }
 310
 311         protected Object getCellValue(Cell cell) {
 312                 Object contents;
 313                 if (cell.getType() == CellType.LABEL) {
 314                         LabelCell lc = (LabelCell) cell;
 315                         contents = lc.getString();
 316                 } else if (cell.getType() == CellType.NUMBER) {
 317                         NumberCell nc = (NumberCell) cell;
 318                         contents = nc.getValue();
 319                 } else {
 320                         contents = cell.getContents();
 321                 }
 322                 return contents;
 323         }
 324
 325         /** Returns true if property was set (thus bypassing standard process). */
 326         protected Boolean overrideCell(Cell cell, BeanWrapper bw,
 327                         String propertyName, String keyProperty, Integer row,
 328                         List<Reference> references) {
 329                 return false;
 330         }
 331
 332         /**
 333          * @deprecated use
 334          *             {@link #overrideCell(Cell, BeanWrapper, String, String, Integer, List)}
 335          *             instead. This method is not called anymore.
 336          */
 337         protected Boolean overrideCell(Cell cell, BeanWrapper bw,
 338                         String propertyName, String keyProperty, Integer row,
 339                         List<Reference> references, Map<String, List<Object>> tempRefs) {
 340                 throw new UnsupportedOperationException();
 341         }
 342
 343         public void setEncoding(String encoding) {
 344                 this.encoding = encoding;
 345         }
 346
 347         public void setLocale(Locale locale) {
 348                 this.locale = locale;
 349         }
 350
 351         /** @deprecated use {@link #setResources(List)} instead. */
 352         public void setWorkbooks(List<Resource> workbooks) {
 353                 setResources(workbooks);
 354                 log.warn("###\n" + "### Use of the 'workbooks' property is deprecated!"
 355                                 + " It will be removed in one of the next releases."
 356                                 + " Use the 'resources' property instead." + "\n###");
 357         }
 358 }