1 package org
.argeo
.server
.jxl
.dao
;
3 import java
.io
.InputStream
;
4 import java
.util
.ArrayList
;
5 import java
.util
.HashMap
;
7 import java
.util
.Locale
;
9 import java
.util
.StringTokenizer
;
13 import jxl
.FormulaCell
;
14 import jxl
.JXLException
;
16 import jxl
.NumberCell
;
19 import jxl
.WorkbookSettings
;
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
;
31 public class JxlDaoSupport
extends AbstractTabularDaoSupport
implements
32 LightDaoSupport
, ApplicationContextAware
, InitializingBean
{
33 private final static Log log
= LogFactory
.getLog(JxlDaoSupport
.class);
35 private String encoding
= "cp1252";
36 private Locale locale
= null;
38 protected void load(InputStream in
, List
<Reference
> references
) {
40 WorkbookSettings workbookSettings
= new WorkbookSettings();
41 workbookSettings
.setEncoding(encoding
);
43 workbookSettings
.setLocale(locale
);
44 Workbook workbook
= Workbook
.getWorkbook(in
, workbookSettings
);
45 for (Sheet sheet
: workbook
.getSheets()) {
46 loadSheet(sheet
, references
);
48 } catch (Exception e
) {
49 throw new ArgeoException("Cannot load workbook", e
);
53 protected void loadSheet(Sheet sheet
, List
<Reference
> references
)
55 String sheetName
= sheet
.getName();
56 if (log
.isTraceEnabled())
57 log
.debug("Instantiate sheet " + sheetName
);
60 int hashIndex
= sheetName
.lastIndexOf('#');
62 tableName
= sheetName
.substring(0, hashIndex
);
64 tableName
= sheetName
;
67 Class
<?
> clss
= findClassToInstantiate(tableName
);
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 "
83 String fieldValue
= espSt
.nextToken();
84 bw
.setPropertyValue(fieldName
, fieldValue
);
85 loadAsObject(bw
, sheet
, references
);
86 saveOrUpdate(bw
.getPropertyValue(keyProperty
), bw
87 .getWrappedInstance(), clss
);
92 Cell
[] firstRow
= sheet
.getRow(0);
93 String keyProperty
= firstRow
[0].getContents();
95 if (keyProperty
.charAt(keyProperty
.length() - 1) == '>') {
96 loadAsColumns(clss
, keyProperty
.substring(0, keyProperty
97 .length() - 1), sheet
, firstRow
, references
);
99 loadAsRows(clss
, keyProperty
, sheet
, firstRow
, references
);
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
);
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();
115 if (col
< currentRow
.length
) {
116 Cell cell
= currentRow
[col
];
117 if (overrideCell(cell
, bw
, pName
, keyProperty
, row
,
120 loadCell(cell
, bw
, pName
, keyProperty
, row
, references
);
124 saveOrUpdate(bw
.getPropertyValue(keyProperty
), bw
125 .getWrappedInstance(), clss
);
126 // tempRefs.get(sheet.getName()).add(bw.getWrappedInstance());
127 registerInTabularView(sheet
.getName(), bw
.getWrappedInstance());
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);
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
];
146 propertyName
= keyProperty
;
148 propertyName
= firstColumn
[row
].getContents();
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
);
158 Map
<Object
, Object
> map
= new HashMap
<Object
, Object
>();
159 String firstColContents
= firstColumn
[row
].getContents();
160 mapRows
: for (; row
< column
.length
; 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
);
170 // check next row too see if one should break
171 if (row
< firstColumn
.length
- 1)
172 firstColContents
= firstColumn
[row
+ 1]
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
185 loadCell(cell
, bw
, propertyName
, keyProperty
, row
,
190 saveOrUpdate(bw
.getPropertyValue(keyProperty
), bw
191 .getWrappedInstance(), clss
);
192 // tempRefs.get(sheet.getName()).add(bw.getWrappedInstance());
193 registerInTabularView(sheet
.getName(), bw
.getWrappedInstance());
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("")) {
213 List
<Object
> subKeys
= new ArrayList
<Object
>();
214 for (int col
= 1; col
< currentRow
.length
; col
++) {
215 subKeys
.add(getCellValue(currentRow
[col
]));
217 if (log
.isTraceEnabled())
218 log
.trace(" subKeys=" + subKeys
);
220 String firstColContents
= firstColumn
[row
].getContents();
221 mapRows
: for (; row
< firstColumn
.length
; row
++) {
222 currentRow
= sheet
.getRow(row
);
224 Object key
= firstColContents
;
225 Map
<Object
, Object
> subMap
= new HashMap
<Object
, Object
>();
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
);
239 map
.put(key
, subMap
);
241 // check next row too see if one should break
242 if (row
< firstColumn
.length
- 1)
243 firstColContents
= firstColumn
[row
+ 1]
245 if (bw
.isWritableProperty(firstColContents
)
246 || firstColContents
.trim().equals("")
247 || row
== firstColumn
.length
- 1) {
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
258 } else if (List
.class.isAssignableFrom(rowType
)) {
259 throw new UnsupportedOperationException();
261 bw
.setPropertyValue(propertyName
, getCellValue(currentRow
[1]));
266 protected void loadCell(Cell cell
, BeanWrapper bw
, String propertyName
,
267 String keyProperty
, Integer row
, List
<Reference
> references
)
268 throws JXLException
{
270 if (cell
instanceof FormulaCell
) {
271 String formula
= ((FormulaCell
) cell
).getFormula();
272 int index
= formula
.indexOf('!');
274 throw new ArgeoException("Cannot interpret formula "
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
,
287 if (log
.isTraceEnabled())
288 log
.debug(" formula: " + formula
+ " | content: "
289 + cell
.getContents() + " | targetSheet=" + targetSheet
290 + ", targetRow=" + targetRow
);
292 Object cellValue
= getCellValue(cell
);
294 if (propertyName
.equals(keyProperty
)
295 && !StringUtils
.hasText(cellValue
.toString())) {
296 // auto allocate key column if empty
297 cellValue
= Integer
.toString(row
);
300 if (propertyName
.charAt(0) == '#') {// externalRef
301 references
.add(new Reference(bw
.getWrappedInstance(),
302 propertyName
.substring(1), cellValue
.toString()));
304 bw
.setPropertyValue(propertyName
, cellValue
);
307 if (log
.isTraceEnabled())
308 log
.debug(" " + propertyName
+ "=" + cellValue
);
313 protected Object
getCellValue(Cell cell
) {
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();
322 contents
= cell
.getContents();
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
) {
334 public void setEncoding(String encoding
) {
335 this.encoding
= encoding
;
338 public void setLocale(Locale locale
) {
339 this.locale
= locale
;