1 | /** |
---|
2 | * Importer service |
---|
3 | * |
---|
4 | * The importer service handles the import of tabular, comma delimited and Excel format |
---|
5 | * based files. |
---|
6 | * |
---|
7 | * @package importer |
---|
8 | * @author t.w.abma@umcutrecht.nl |
---|
9 | * @since 20100126 |
---|
10 | * |
---|
11 | * Revision information: |
---|
12 | * $Rev: 706 $ |
---|
13 | * $Author: keesvb $ |
---|
14 | * $Date: 2010-07-26 10:26:50 +0000 (ma, 26 jul 2010) $ |
---|
15 | */ |
---|
16 | |
---|
17 | package dbnp.importer |
---|
18 | import org.apache.poi.hssf.usermodel.* |
---|
19 | import org.apache.poi.poifs.filesystem.POIFSFileSystem |
---|
20 | import org.apache.poi.ss.usermodel.DataFormatter |
---|
21 | |
---|
22 | import dbnp.studycapturing.TemplateFieldType |
---|
23 | import dbnp.studycapturing.Template |
---|
24 | import dbnp.studycapturing.Study |
---|
25 | import dbnp.studycapturing.Subject |
---|
26 | import dbnp.studycapturing.Event |
---|
27 | |
---|
28 | import dbnp.studycapturing.Sample |
---|
29 | |
---|
30 | import dbnp.data.Term |
---|
31 | |
---|
32 | class ImporterService { |
---|
33 | |
---|
34 | boolean transactional = true |
---|
35 | |
---|
36 | /** |
---|
37 | * @param is input stream representing the (workbook) resource |
---|
38 | * @return high level representation of the workbook |
---|
39 | */ |
---|
40 | HSSFWorkbook getWorkbook(InputStream is) { |
---|
41 | POIFSFileSystem fs = new POIFSFileSystem(is) |
---|
42 | HSSFWorkbook wb = new HSSFWorkbook(fs); |
---|
43 | return wb; |
---|
44 | } |
---|
45 | |
---|
46 | /** |
---|
47 | * @param wb high level representation of the workbook |
---|
48 | * @param sheetindex sheet to use within the workbook |
---|
49 | * @return header representation as a MappingColumn hashmap |
---|
50 | */ |
---|
51 | def getHeader(HSSFWorkbook wb, int sheetindex, theEntity=null){ |
---|
52 | |
---|
53 | def sheet = wb.getSheetAt(sheetindex) |
---|
54 | def datamatrix_start = sheet.getFirstRowNum() + 2 |
---|
55 | def sheetrow = sheet.getRow(datamatrix_start) |
---|
56 | //def header = [] |
---|
57 | def header = [:] |
---|
58 | def df = new DataFormatter() |
---|
59 | def property = new String() |
---|
60 | |
---|
61 | //for (HSSFCell c: sheet.getRow(datamatrix_start)) { |
---|
62 | |
---|
63 | (0..sheetrow.getLastCellNum() -1 ).each { columnindex -> |
---|
64 | |
---|
65 | //def index = c.getColumnIndex() |
---|
66 | def datamatrix_celltype = sheet.getRow(datamatrix_start).getCell(columnindex, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK).getCellType() |
---|
67 | def datamatrix_celldata = df.formatCellValue(sheet.getRow(datamatrix_start).getCell(columnindex)) |
---|
68 | def datamatrix_cell = sheet.getRow(datamatrix_start).getCell(columnindex) |
---|
69 | def headercell = sheet.getRow(sheet.getFirstRowNum()).getCell(columnindex) |
---|
70 | def tft = TemplateFieldType.STRING //default templatefield type |
---|
71 | |
---|
72 | // Check for every celltype, currently redundant code, but possibly this will be |
---|
73 | // a piece of custom code for every cell type like specific formatting |
---|
74 | |
---|
75 | switch (datamatrix_celltype) { |
---|
76 | case HSSFCell.CELL_TYPE_STRING: |
---|
77 | //parse cell value as double |
---|
78 | def doubleBoolean = true |
---|
79 | def fieldtype = TemplateFieldType.STRING |
---|
80 | |
---|
81 | // is this string perhaps a double? |
---|
82 | try { |
---|
83 | formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE) |
---|
84 | } catch (NumberFormatException nfe) { doubleBoolean = false } |
---|
85 | finally { |
---|
86 | if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE |
---|
87 | } |
---|
88 | |
---|
89 | header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell), |
---|
90 | templatefieldtype:fieldtype, |
---|
91 | index:columnindex, |
---|
92 | entity:theEntity, |
---|
93 | property:property); |
---|
94 | |
---|
95 | break |
---|
96 | case HSSFCell.CELL_TYPE_NUMERIC: |
---|
97 | def fieldtype = TemplateFieldType.INTEGER |
---|
98 | def doubleBoolean = true |
---|
99 | def integerBoolean = true |
---|
100 | |
---|
101 | // is this cell really an integer? |
---|
102 | try { |
---|
103 | Integer.valueOf(datamatrix_celldata) |
---|
104 | } catch (NumberFormatException nfe) { integerBoolean = false } |
---|
105 | finally { |
---|
106 | if (integerBoolean) fieldtype = TemplateFieldType.INTEGER |
---|
107 | } |
---|
108 | |
---|
109 | // it's not an integer, perhaps a double? |
---|
110 | if (!integerBoolean) |
---|
111 | try { |
---|
112 | formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE) |
---|
113 | } catch (NumberFormatException nfe) { doubleBoolean = false } |
---|
114 | finally { |
---|
115 | if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE |
---|
116 | } |
---|
117 | |
---|
118 | if (HSSFDateUtil.isCellDateFormatted(datamatrix_cell)) fieldtype = TemplateFieldType.DATE |
---|
119 | |
---|
120 | header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell), |
---|
121 | templatefieldtype:fieldtype, |
---|
122 | index:columnindex, |
---|
123 | entity:theEntity, |
---|
124 | property:property); |
---|
125 | break |
---|
126 | case HSSFCell.CELL_TYPE_BLANK: |
---|
127 | header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell), |
---|
128 | templatefieldtype:TemplateFieldType.STRING, |
---|
129 | index:columnindex, |
---|
130 | entity:theEntity, |
---|
131 | property:property); |
---|
132 | break |
---|
133 | default: |
---|
134 | header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell), |
---|
135 | templatefieldtype:TemplateFieldType.STRING, |
---|
136 | index:columnindex, |
---|
137 | entity:theEntity, |
---|
138 | property:property); |
---|
139 | break |
---|
140 | } // end of switch |
---|
141 | } // end of cell loop |
---|
142 | return header |
---|
143 | } |
---|
144 | |
---|
145 | /** |
---|
146 | * This method is meant to return a matrix of the rows and columns |
---|
147 | * used in the preview |
---|
148 | * |
---|
149 | * @param wb workbook object |
---|
150 | * @param sheetindex sheet index used |
---|
151 | * @param rows amount of rows returned |
---|
152 | * @return two dimensional array (matrix) of HSSFCell objects |
---|
153 | */ |
---|
154 | |
---|
155 | HSSFCell[][] getDatamatrix(HSSFWorkbook wb, header, int sheetindex, int count) { |
---|
156 | def sheet = wb.getSheetAt(sheetindex) |
---|
157 | def rows = [] |
---|
158 | def df = new DataFormatter() |
---|
159 | def datamatrix_start = 1 |
---|
160 | |
---|
161 | // walk through all rows |
---|
162 | (count <= sheet.getLastRowNum()) ? |
---|
163 | ((datamatrix_start+sheet.getFirstRowNum())..count).each { rowindex -> |
---|
164 | def row = [] |
---|
165 | |
---|
166 | // walk through every cell |
---|
167 | /*for (HSSFCell c: sheet.getRow(rowindex)) { |
---|
168 | row.add(c) |
---|
169 | println c.getColumnIndex() + "=" +c |
---|
170 | }*/ |
---|
171 | |
---|
172 | (0..header.size()-1).each { columnindex -> |
---|
173 | def c = sheet.getRow(rowindex).getCell(columnindex, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK) |
---|
174 | //row.add(df.formatCellValue(c)) |
---|
175 | row.add(c) |
---|
176 | //if (c.getCellType() == c.CELL_TYPE_STRING) println "STR"+c.getStringCellValue() |
---|
177 | //if (c.getCellType() == c.CELL_TYPE_NUMERIC) println "INT" +c.getNumericCellValue() |
---|
178 | } |
---|
179 | //row.add(df.formatCellValue(c)) |
---|
180 | rows.add(row) |
---|
181 | } : 0 |
---|
182 | |
---|
183 | return rows |
---|
184 | } |
---|
185 | |
---|
186 | /** |
---|
187 | * This method will move a file to a new location. |
---|
188 | * |
---|
189 | * @param file File object to move |
---|
190 | * @param folderpath folder to move the file to |
---|
191 | * @param filename (new) filename to give |
---|
192 | * @return if file has been moved succesful, the new path and filename will be returned, otherwise an empty string will be returned |
---|
193 | */ |
---|
194 | def moveFile(File file, String folderpath, String filename) { |
---|
195 | try { |
---|
196 | def rnd = ""; //System.currentTimeMillis() |
---|
197 | file.transferTo(new File(folderpath, rnd+filename)) |
---|
198 | return folderpath + filename |
---|
199 | } catch(Exception exception) { |
---|
200 | log.error "File move error, ${exception}" |
---|
201 | return "" |
---|
202 | } |
---|
203 | } |
---|
204 | |
---|
205 | /** |
---|
206 | * @return random numeric value |
---|
207 | */ |
---|
208 | def random = { |
---|
209 | return System.currentTimeMillis() + Runtime.runtime.freeMemory() |
---|
210 | } |
---|
211 | |
---|
212 | /** |
---|
213 | * Method to read data from a workbook and to import data into a two dimensional |
---|
214 | * array |
---|
215 | * |
---|
216 | * @param template_id template identifier to use fields from |
---|
217 | * @param wb POI horrible spreadsheet formatted workbook object |
---|
218 | * @param mcmap linked hashmap (preserved order) of MappingColumns |
---|
219 | * @param sheetindex sheet to use when using multiple sheets |
---|
220 | * @param rowindex first row to start with reading the actual data (NOT the header) |
---|
221 | * @return two dimensional array containing records (with entities) |
---|
222 | * |
---|
223 | * @see dbnp.importer.MappingColumn |
---|
224 | */ |
---|
225 | def importdata(template_id, HSSFWorkbook wb, int sheetindex, int rowindex, mcmap) { |
---|
226 | def sheet = wb.getSheetAt(sheetindex) |
---|
227 | def table = [] |
---|
228 | |
---|
229 | // walk through all rows |
---|
230 | (rowindex..sheet.getLastRowNum()).each { i -> |
---|
231 | table.add(createRecord(template_id, sheet.getRow(i), mcmap)) |
---|
232 | } |
---|
233 | |
---|
234 | /*table.each { |
---|
235 | it.each { entity -> |
---|
236 | entity.giveFields().each { field -> |
---|
237 | print field.name + ":" + entity.getFieldValue(field.name) + "/" |
---|
238 | } |
---|
239 | println |
---|
240 | } |
---|
241 | }*/ |
---|
242 | |
---|
243 | return table |
---|
244 | } |
---|
245 | |
---|
246 | /** |
---|
247 | * Method to store a matrix containing the entities in a record like structure. Every row in the table |
---|
248 | * contains one or more entity objects (which contain fields with values). So actually a row represents |
---|
249 | * a record with fields from one or more different entities. |
---|
250 | * |
---|
251 | * @param study entity Study |
---|
252 | * @param datamatrix two dimensional array containing entities with values read from Excel file * |
---|
253 | */ |
---|
254 | def saveDatamatrix(Study study, datamatrix) { |
---|
255 | def validatedSuccesfully = 0 |
---|
256 | study.refresh() |
---|
257 | |
---|
258 | datamatrix.each { record -> |
---|
259 | record.each { entity -> |
---|
260 | if(entity.validate()) { |
---|
261 | switch (entity.getClass()) { |
---|
262 | case Study : print "Persisting Study `" + entity + "`: " |
---|
263 | persistEntity(entity) |
---|
264 | break |
---|
265 | case Subject : print "Persisting Subject `" + entity + "`: " |
---|
266 | persistEntity(entity) |
---|
267 | study.addToSubjects(entity) |
---|
268 | break |
---|
269 | case Event : print "Persisting Event `" + entity + "`: " |
---|
270 | persistEntity(entity) |
---|
271 | study.addToEvents(entity) |
---|
272 | break |
---|
273 | case Sample : print "Persisting Sample `" + entity +"`: " |
---|
274 | persistEntity(entity) |
---|
275 | study.addToSamples(entity) |
---|
276 | break |
---|
277 | default : println "Skipping persisting of `" + entity.getclass() +"`" |
---|
278 | break |
---|
279 | } // end switch |
---|
280 | validatedSuccesfully++ |
---|
281 | } // end if |
---|
282 | } // end record |
---|
283 | } // end datamatrix |
---|
284 | return validatedSuccesfully |
---|
285 | } |
---|
286 | |
---|
287 | /** |
---|
288 | * Method to persist entities into the database |
---|
289 | * Checks whether entity already exists (based on identifier column 'name') |
---|
290 | * |
---|
291 | * @param entity entity object like Study, Subject, Protocol et cetera |
---|
292 | * |
---|
293 | */ |
---|
294 | def persistEntity(entity) { |
---|
295 | if (!entity.save()) //.merge? |
---|
296 | entity.errors.allErrors.each { |
---|
297 | println it |
---|
298 | } |
---|
299 | } |
---|
300 | |
---|
301 | /** |
---|
302 | * This method creates a record (array) containing entities with values |
---|
303 | * |
---|
304 | * @param template_id template identifier |
---|
305 | * @param excelrow POI based Excel row containing the cells |
---|
306 | * @param mcmap map containing MappingColumn objects |
---|
307 | */ |
---|
308 | def createRecord(template_id, HSSFRow excelrow, mcmap) { |
---|
309 | def df = new DataFormatter() |
---|
310 | def template = Template.get(template_id) |
---|
311 | def record = [] |
---|
312 | |
---|
313 | def study = new Study(template:template) |
---|
314 | def subject = new Subject(template:template) |
---|
315 | def event = new Event(template:template) |
---|
316 | def sample = new Sample(template:template) |
---|
317 | |
---|
318 | for (HSSFCell cell: excelrow) { |
---|
319 | def mc = mcmap[cell.getColumnIndex()] |
---|
320 | def value |
---|
321 | |
---|
322 | // Check if column must be imported |
---|
323 | if (!mc.dontimport) { |
---|
324 | try { |
---|
325 | value = formatValue(df.formatCellValue(cell), mc.templatefieldtype) |
---|
326 | } catch (NumberFormatException nfe) { |
---|
327 | value = "" |
---|
328 | } |
---|
329 | |
---|
330 | switch(mc.entity) { |
---|
331 | case Study : (record.any {it.getClass()==mc.entity}) ? 0 : record.add(study) |
---|
332 | study.setFieldValue(mc.property, value) |
---|
333 | break |
---|
334 | case Subject : (record.any {it.getClass()==mc.entity}) ? 0 : record.add(subject) |
---|
335 | subject.setFieldValue(mc.property, value) |
---|
336 | break |
---|
337 | case Event : (record.any {it.getClass()==mc.entity}) ? 0 : record.add(event) |
---|
338 | event.setFieldValue(mc.property, value) |
---|
339 | break |
---|
340 | case Sample : (record.any {it.getClass()==mc.entity}) ? 0 : record.add(sample) |
---|
341 | sample.setFieldValue(mc.property, value) |
---|
342 | break |
---|
343 | case Object : // don't import |
---|
344 | break |
---|
345 | } // end switch |
---|
346 | } // end |
---|
347 | } // end for |
---|
348 | |
---|
349 | return record |
---|
350 | } |
---|
351 | |
---|
352 | /** |
---|
353 | * Method to parse a value conform a specific type |
---|
354 | * @param value string containing the value |
---|
355 | * @return object corresponding to the TemplateFieldType |
---|
356 | */ |
---|
357 | def formatValue(String value, TemplateFieldType type) throws NumberFormatException { |
---|
358 | switch (type) { |
---|
359 | case TemplateFieldType.STRING : return value.trim() |
---|
360 | case TemplateFieldType.TEXT : return value.trim() |
---|
361 | case TemplateFieldType.INTEGER : return (int) Double.valueOf(value) |
---|
362 | case TemplateFieldType.FLOAT : return Float.valueOf(value.replace(",",".")); |
---|
363 | case TemplateFieldType.DOUBLE : return Double.valueOf(value.replace(",",".")); |
---|
364 | case TemplateFieldType.STRINGLIST : return value.trim() |
---|
365 | case TemplateFieldType.ONTOLOGYTERM : return value.trim() |
---|
366 | case TemplateFieldType.DATE : return value |
---|
367 | default : return value |
---|
368 | } |
---|
369 | } |
---|
370 | |
---|
371 | } |
---|