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                                         if (log.isTraceEnabled())
 219                                                 log.trace("   subKeys=" + subKeys);
 220                                         row++;
 221                                         String firstColContents = firstColumn[row].getContents();
 222                                         mapRows: for (; row < firstColumn.length; row++) {
 223                                                 currentRow = sheet.getRow(row);
 224
 225                                                 Object key = firstColContents;
 226                                                 Map<Object, Object> subMap = new HashMap<Object, Object>();
 227
 228                                                 for (int col = 1; col < currentRow.length
 229                                                                 && col < subKeys.size() + 1; col++) {
 230                                                         Object subKey = subKeys.get(col - 1);
 231                                                         Cell cell = currentRow[col];
 232                                                         if (log.isTraceEnabled())
 233                                                                 log.trace("   row=" + row
 234                                                                                 + ", firstColContents="
 235                                                                                 + firstColContents + ", subKey="
 236                                                                                 + subKey + ", type=" + cell.getType());
 237                                                         Object cellValue = getCellValue(cell);
 238                                                         subMap.put(subKey, cellValue);
 239                                                 }
 240                                                 map.put(key, subMap);
 241
 242                                                 // check next row too see if one should break
 243                                                 if (row < firstColumn.length - 1)
 244                                                         firstColContents = firstColumn[row + 1]
 245                                                                         .getContents();
 246                                                 if (bw.isWritableProperty(firstColContents)
 247                                                                 || firstColContents.trim().equals("")
 248                                                                 || row == firstColumn.length - 1) {
 249                                                         log.trace(map);
 250                                                         bw.setPropertyValue(propertyName, map);
 251                                                         if (log.isTraceEnabled())
 252                                                                 log.trace(" set map " + propertyName
 253                                                                                 + " of size " + map.size());
 254                                                         break mapRows;// map is over
 255                                                 }
 256                                         }
 257
 258                                 }
 259                         } else if (List.class.isAssignableFrom(rowType)) {
 260                                 throw new UnsupportedOperationException();
 261                         } else {
 262                                 bw.setPropertyValue(propertyName, getCellValue(currentRow[1]));
 263                         }
 264                 }
 265         }
 266
 267         protected void loadCell(Cell cell, BeanWrapper bw, String propertyName,
 268                         String keyProperty, Integer row, List<Reference> references)
 269                         throws JXLException {
 270
 271                 if (cell instanceof FormulaCell) {
 272                         String formula = ((FormulaCell) cell).getFormula();
 273                         int index = formula.indexOf('!');
 274                         if (index < 0)
 275                                 throw new ArgeoServerException("Cannot interpret formula "
 276                                                 + formula);
 277                         ;
 278                         String targetSheet = formula.substring(0, index);
 279                         // assume no double letters!!
 280                         String targetRowStr = formula.substring(index + 2);
 281                         if (targetRowStr.charAt(0) == '$')
 282                                 targetRowStr = targetRowStr.substring(1);
 283                         Integer targetRow = Integer.parseInt(targetRowStr);
 284                         references.add(new TabularInternalReference(
 285                                         bw.getWrappedInstance(), propertyName, targetSheet,
 286                                         targetRow));
 287
 288                         if (log.isTraceEnabled())
 289                                 log.debug("  formula: " + formula + " | content: "
 290                                                 + cell.getContents() + " | targetSheet=" + targetSheet
 291                                                 + ", targetRow=" + targetRow);
 292                 } else {
 293                         Object cellValue = getCellValue(cell);
 294
 295                         if (propertyName.equals(keyProperty)
 296                                         && !StringUtils.hasText(cellValue.toString())) {
 297                                 // auto allocate key column if empty
 298                                 cellValue = Integer.toString(row);
 299                         }
 300
 301                         if (propertyName.charAt(0) == '#') {// externalRef
 302                                 references.add(new Reference(bw.getWrappedInstance(),
 303                                                 propertyName.substring(1), cellValue.toString()));
 304                         } else {
 305                                 bw.setPropertyValue(propertyName, cellValue);
 306                         }
 307
 308                         if (log.isTraceEnabled())
 309                                 log.debug("  " + propertyName + "=" + cellValue);
 310                 }
 311
 312         }
 313
 314         protected Object getCellValue(Cell cell) {
 315                 Object contents;
 316                 if (cell.getType() == CellType.LABEL) {
 317                         LabelCell lc = (LabelCell) cell;
 318                         contents = lc.getString();
 319                 } else if (cell.getType() == CellType.NUMBER) {
 320                         NumberCell nc = (NumberCell) cell;
 321                         contents = nc.getValue();
 322                 } else {
 323                         contents = cell.getContents();
 324                 }
 325                 return contents;
 326         }
 327
 328         /** Returns true if property was set (thus bypassing standard process). */
 329         protected Boolean overrideCell(Cell cell, BeanWrapper bw,
 330                         String propertyName, String keyProperty, Integer row,
 331                         List<Reference> references) {
 332                 return false;
 333         }
 334
 335         public void setEncoding(String encoding) {
 336                 this.encoding = encoding;
 337         }
 338
 339         public void setLocale(Locale locale) {
 340                 this.locale = locale;
 341         }
 342
 343 }