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