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.Collection;
   6 import java.util.HashMap;
   7 import java.util.List;
   8 import java.util.Map;
   9 import java.util.TreeMap;
  10
  11 import jxl.Cell;
  12 import jxl.FormulaCell;
  13 import jxl.JXLException;
  14 import jxl.Sheet;
  15 import jxl.Workbook;
  16
  17 import org.apache.commons.io.IOUtils;
  18 import org.apache.commons.logging.Log;
  19 import org.apache.commons.logging.LogFactory;
  20 import org.argeo.server.ArgeoServerException;
  21 import org.argeo.server.dao.LightDaoSupport;
  22 import org.springframework.beans.BeanWrapper;
  23 import org.springframework.beans.BeanWrapperImpl;
  24 import org.springframework.beans.BeansException;
  25 import org.springframework.beans.factory.InitializingBean;
  26 import org.springframework.beans.factory.generic.GenericBeanFactoryAccessor;
  27 import org.springframework.context.ApplicationContext;
  28 import org.springframework.context.ApplicationContextAware;
  29 import org.springframework.core.io.Resource;
  30 import org.springframework.util.StringUtils;
  31
  32 public class JxlDaoSupport implements LightDaoSupport, ApplicationContextAware,
  33                 InitializingBean {
  34         private final static Log log = LogFactory.getLog(JxlDaoSupport.class);
  35
  36         private ClassLoader classLoader = getClass().getClassLoader();
  37         private ApplicationContext applicationContext;
  38
  39         private Map<Class<?>, Map<Object, Object>> model = new HashMap<Class<?>, Map<Object, Object>>();
  40
  41         private Map<String, Object> externalRefs = new HashMap<String, Object>();
  42
  43         private List<String> scannedPackages = new ArrayList<String>();
  44
  45         private List<Resource> workbooks = new ArrayList<Resource>();
  46
  47         public void afterPropertiesSet() throws Exception {
  48                 init();
  49         }
  50
  51         public void init() {
  52                 // used to resolve inner references
  53                 Map<String, List<Object>> tempRefs = new HashMap<String, List<Object>>();
  54
  55                 List<Reference> references = new ArrayList<Reference>();
  56
  57                 for (Resource res : workbooks) {
  58                         InputStream in = null;
  59                         try {
  60                                 in = res.getInputStream();
  61                                 load(in, references, tempRefs);
  62                         } catch (Exception e) {
  63                                 throw new ArgeoServerException("Cannot load stream", e);
  64                         } finally {
  65                                 IOUtils.closeQuietly(in);
  66                         }
  67                 }
  68
  69                 // Inject references
  70                 for (Reference ref : references) {
  71                         injectReference(ref, tempRefs);
  72                 }
  73                 if (log.isDebugEnabled())
  74                         log.debug(references.size() + " references linked");
  75         }
  76
  77         public void load(InputStream in, List<Reference> references,
  78                         Map<String, List<Object>> tempRefs) {
  79                 try {
  80                         Workbook workbook = Workbook.getWorkbook(in);
  81                         for (Sheet sheet : workbook.getSheets()) {
  82                                 loadSheet(sheet, references, tempRefs);
  83                         }
  84                 } catch (Exception e) {
  85                         throw new ArgeoServerException("Cannot load workbook", e);
  86                 }
  87         }
  88
  89         protected void loadSheet(Sheet sheet, List<Reference> references,
  90                         Map<String, List<Object>> tempRefs) throws JXLException {
  91                 if (log.isTraceEnabled())
  92                         log.debug("Instantiate sheet " + sheet.getName());
  93
  94                 Cell[] firstRow = sheet.getRow(0);
  95
  96                 Class<?> clss = findClassToInstantiate(sheet);
  97                 model.put(clss, new TreeMap<Object, Object>());
  98
  99                 tempRefs.put(sheet.getName(), new ArrayList<Object>());
 100
 101                 String keyProperty = firstRow[0].getContents();
 102                 for (int row = 1; row < sheet.getRows(); row++) {
 103                         if (log.isTraceEnabled())
 104                                 log.trace(" row " + row);
 105
 106                         Cell[] currentRow = sheet.getRow(row);
 107                         BeanWrapper bw = new BeanWrapperImpl(clss);
 108                         cells: for (int col = 0; col < firstRow.length; col++) {
 109                                 String pName = firstRow[col].getContents();
 110
 111                                 if (col < currentRow.length) {
 112                                         Cell cell = currentRow[col];
 113                                         if (overrideCell(cell, bw, pName, keyProperty, row,
 114                                                         references, tempRefs))
 115                                                 continue cells;
 116                                         loadCell(cell, bw, pName, keyProperty, row, references);
 117                                 }
 118                         }// cells
 119
 120                         model.get(clss).put(bw.getPropertyValue(keyProperty),
 121                                         bw.getWrappedInstance());
 122                         tempRefs.get(sheet.getName()).add(bw.getWrappedInstance());
 123                 }
 124
 125                 if (log.isDebugEnabled())
 126                         log.debug(model.get(clss).size() + " objects of type " + clss
 127                                         + " instantiated");
 128
 129         }
 130
 131         protected void loadCell(Cell cell, BeanWrapper bw, String propertyName,
 132                         String keyProperty, Integer row, List<Reference> references)
 133                         throws JXLException {
 134
 135                 if (cell instanceof FormulaCell) {
 136                         String formula = ((FormulaCell) cell).getFormula();
 137                         int index = formula.indexOf('!');
 138                         if (index < 0)
 139                                 throw new ArgeoServerException("Cannot interpret formula "
 140                                                 + formula);
 141                         ;
 142                         String targetSheet = formula.substring(0, index);
 143                         // assume no double letters!!
 144                         String targetRowStr = formula.substring(index + 2);
 145                         if (targetRowStr.charAt(0) == '$')
 146                                 targetRowStr = targetRowStr.substring(1);
 147                         Integer targetRow = Integer.parseInt(targetRowStr);
 148                         references.add(new Reference(bw.getWrappedInstance(), propertyName,
 149                                         targetSheet, targetRow));
 150
 151                         if (log.isTraceEnabled())
 152                                 log.debug("  formula: " + formula + " | content: "
 153                                                 + cell.getContents() + " | targetSheet=" + targetSheet
 154                                                 + ", targetRow=" + targetRow);
 155                 } else {
 156                         String contents = cell.getContents();
 157                         if (propertyName.equals(keyProperty)
 158                                         && !StringUtils.hasText(contents)) {
 159                                 // auto allocate key column if empty
 160                                 contents = Integer.toString(row);
 161                         }
 162
 163                         if (propertyName.charAt(0) == '#') {// externalRef
 164                                 references.add(new Reference(bw.getWrappedInstance(),
 165                                                 propertyName.substring(1), contents));
 166                         } else {
 167                                 bw.setPropertyValue(propertyName, contents);
 168                         }
 169
 170                         if (log.isTraceEnabled())
 171                                 log.debug("  " + propertyName + "=" + contents);
 172                 }
 173
 174         }
 175
 176         /** Returns true if property was set (thus bypassing standard process). */
 177         protected Boolean overrideCell(Cell cell, BeanWrapper bw,
 178                         String propertyName, String keyProperty, Integer row,
 179                         List<Reference> references, Map<String, List<Object>> tempRefs) {
 180                 return false;
 181         }
 182
 183         protected void injectReference(Reference reference,
 184                         Map<String, List<Object>> tempRefs) {
 185                 BeanWrapper bw = new BeanWrapperImpl(reference.object);
 186                 Object targetObject;
 187                 if (reference.getExternalRef() != null) {
 188                         String ref = reference.getExternalRef();
 189                         if (externalRefs.containsKey(ref))
 190                                 targetObject = externalRefs.get(ref);
 191                         else if (applicationContext != null)
 192                                 targetObject = applicationContext.getBean(ref);
 193                         else {
 194                                 targetObject = null;
 195                                 log.warn("Ref " + ref + " not found");
 196                         }
 197                 } else {
 198                         targetObject = tempRefs.get(reference.getTargetSheet()).get(
 199                                         reference.targetRow - 2);
 200                 }
 201                 bw.setPropertyValue(reference.property, targetObject);
 202
 203         }
 204
 205         protected Class<?> findClassToInstantiate(Sheet sheet) {
 206                 // TODO: ability to map sheet names and class names
 207                 String className = sheet.getName();
 208                 Class<?> clss = null;
 209                 try {
 210                         clss = classLoader.loadClass(className);
 211                         return clss;
 212                 } catch (ClassNotFoundException e) {
 213                         // silent
 214                 }
 215
 216                 scannedPkgs: for (String pkg : scannedPackages) {
 217                         try {
 218                                 clss = classLoader.loadClass(pkg.trim() + "." + className);
 219                                 break scannedPkgs;
 220                         } catch (ClassNotFoundException e) {
 221                                 // silent
 222                                 if (log.isTraceEnabled())
 223                                         log.trace(e.getMessage());
 224                         }
 225                 }
 226
 227                 if (clss == null)
 228                         throw new ArgeoServerException("Cannot find a class for sheet "
 229                                         + sheet.getName());
 230
 231                 return clss;
 232         }
 233
 234         @SuppressWarnings("unchecked")
 235         public <T> T getByKey(Class<T> clss, Object key) {
 236                 return (T) model.get(findClass(clss)).get(key);
 237         }
 238
 239         /**
 240          * Slow.
 241          *
 242          * @return the first found
 243          */
 244         public <T> T getByField(Class<T> clss, String field, Object value) {
 245                 List<T> all = list(clss, null);
 246                 T res = null;
 247                 for (T obj : all) {
 248                         if (new BeanWrapperImpl(obj).getPropertyValue(field).equals(value)) {
 249                                 res = obj;
 250                                 break;
 251                         }
 252                 }
 253                 return res;
 254         }
 255
 256         @SuppressWarnings("unchecked")
 257         public <T> List<T> list(Class<T> clss, Object filter) {
 258                 List<T> res = new ArrayList<T>();
 259
 260                 Class classToUse = findClass(clss);
 261                 if (classToUse != null)
 262                         res.addAll((Collection<T>) model.get(classToUse).values());
 263
 264                 if (applicationContext != null)
 265                         res.addAll(new GenericBeanFactoryAccessor(applicationContext)
 266                                         .getBeansOfType(clss).values());
 267
 268                 return res;
 269         }
 270
 271         @SuppressWarnings("unchecked")
 272         protected Class findClass(Class parent) {
 273                 if (model.containsKey(parent))
 274                         return parent;
 275
 276                 for (Class clss : model.keySet()) {
 277                         if (parent.isAssignableFrom(clss))
 278                                 return clss;// return the first found
 279                 }
 280                 return null;
 281         }
 282
 283         public void setApplicationContext(ApplicationContext applicationContext)
 284                         throws BeansException {
 285                 this.applicationContext = applicationContext;
 286         }
 287
 288         public void setExternalRefs(Map<String, Object> externalRefs) {
 289                 this.externalRefs = externalRefs;
 290         }
 291
 292         public Map<String, Object> getExternalRefs() {
 293                 return externalRefs;
 294         }
 295
 296         public void setScannedPackages(List<String> scannedPackages) {
 297                 this.scannedPackages = scannedPackages;
 298         }
 299
 300         public List<String> getScannedPackages() {
 301                 return scannedPackages;
 302         }
 303
 304         public void setWorkbooks(List<Resource> workbooks) {
 305                 this.workbooks = workbooks;
 306         }
 307
 308         public List<Resource> getWorkbooks() {
 309                 return workbooks;
 310         }
 311
 312         public void setClassLoader(ClassLoader classLoader) {
 313                 this.classLoader = classLoader;
 314         }
 315
 316         public static class Reference {
 317                 private Object object;
 318                 private String property;
 319                 private String targetSheet;
 320                 private Integer targetRow;
 321                 private String externalRef;
 322
 323                 public Reference(Object object, String property, String targetSheet,
 324                                 Integer targetRow) {
 325                         this.object = object;
 326                         this.property = property;
 327                         this.targetSheet = targetSheet;
 328                         this.targetRow = targetRow;
 329                 }
 330
 331                 public Reference(Object object, String property, String externalRef) {
 332                         this.object = object;
 333                         this.property = property;
 334                         this.externalRef = externalRef;
 335                 }
 336
 337                 public Object getObject() {
 338                         return object;
 339                 }
 340
 341                 public String getProperty() {
 342                         return property;
 343                 }
 344
 345                 public String getTargetSheet() {
 346                         return targetSheet;
 347                 }
 348
 349                 public Integer getTargetRow() {
 350                         return targetRow;
 351                 }
 352
 353                 public String getExternalRef() {
 354                         return externalRef;
 355                 }
 356
 357         }
 358 }