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

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