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: 1002 $ |
---|
13 | * $Author: t.w.abma@umcutrecht.nl $ |
---|
14 | * $Date: 2010-10-27 13:50:56 +0000 (wo, 27 okt 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.SamplingEvent |
---|
25 | import dbnp.studycapturing.Study |
---|
26 | import dbnp.studycapturing.Subject |
---|
27 | import dbnp.studycapturing.Event |
---|
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, int headerrow, int datamatrix_start, theEntity=null){ |
---|
52 | |
---|
53 | def sheet = wb.getSheetAt(sheetindex) |
---|
54 | def sheetrow = sheet.getRow(datamatrix_start) |
---|
55 | //def header = [] |
---|
56 | def header = [:] |
---|
57 | def df = new DataFormatter() |
---|
58 | def property = new String() |
---|
59 | |
---|
60 | //for (HSSFCell c: sheet.getRow(datamatrix_start)) { |
---|
61 | |
---|
62 | (0..sheetrow.getLastCellNum() -1 ).each { columnindex -> |
---|
63 | |
---|
64 | //def index = c.getColumnIndex() |
---|
65 | def datamatrix_celltype = sheet.getRow(datamatrix_start).getCell(columnindex, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK).getCellType() |
---|
66 | def datamatrix_celldata = df.formatCellValue(sheet.getRow(datamatrix_start).getCell(columnindex)) |
---|
67 | def datamatrix_cell = sheet.getRow(datamatrix_start).getCell(columnindex) |
---|
68 | println "frn is "+sheet.getFirstRowNum() |
---|
69 | def headercell = sheet.getRow(headerrow-1+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 datamatrix_start, int count) { |
---|
156 | def sheet = wb.getSheetAt(sheetindex) |
---|
157 | def rows = [] |
---|
158 | def df = new DataFormatter() |
---|
159 | |
---|
160 | // walk through all rows |
---|
161 | (count <= sheet.getLastRowNum()) ? |
---|
162 | ((datamatrix_start+sheet.getFirstRowNum())..count).each { rowindex -> |
---|
163 | def row = [] |
---|
164 | |
---|
165 | // walk through every cell |
---|
166 | /*for (HSSFCell c: sheet.getRow(rowindex)) { |
---|
167 | row.add(c) |
---|
168 | println c.getColumnIndex() + "=" +c |
---|
169 | }*/ |
---|
170 | |
---|
171 | (0..header.size()-1).each { columnindex -> |
---|
172 | def c = sheet.getRow(rowindex).getCell(columnindex, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK) |
---|
173 | //row.add(df.formatCellValue(c)) |
---|
174 | row.add(c) |
---|
175 | //if (c.getCellType() == c.CELL_TYPE_STRING) println "STR"+c.getStringCellValue() |
---|
176 | //if (c.getCellType() == c.CELL_TYPE_NUMERIC) println "INT" +c.getNumericCellValue() |
---|
177 | } |
---|
178 | //row.add(df.formatCellValue(c)) |
---|
179 | rows.add(row) |
---|
180 | } : 0 |
---|
181 | |
---|
182 | return rows |
---|
183 | } |
---|
184 | |
---|
185 | /** |
---|
186 | * This method will move a file to a new location. |
---|
187 | * |
---|
188 | * @param file File object to move |
---|
189 | * @param folderpath folder to move the file to |
---|
190 | * @param filename (new) filename to give |
---|
191 | * @return if file has been moved succesful, the new path and filename will be returned, otherwise an empty string will be returned |
---|
192 | */ |
---|
193 | def moveFile(File file, String folderpath, String filename) { |
---|
194 | try { |
---|
195 | def rnd = ""; //System.currentTimeMillis() |
---|
196 | file.transferTo(new File(folderpath, rnd+filename)) |
---|
197 | return folderpath + filename |
---|
198 | } catch(Exception exception) { |
---|
199 | log.error "File move error, ${exception}" |
---|
200 | return "" |
---|
201 | } |
---|
202 | } |
---|
203 | |
---|
204 | /** |
---|
205 | * @return random numeric value |
---|
206 | */ |
---|
207 | def random = { |
---|
208 | return System.currentTimeMillis() + Runtime.runtime.freeMemory() |
---|
209 | } |
---|
210 | |
---|
211 | /** |
---|
212 | * Method to read data from a workbook and to import data into a two dimensional |
---|
213 | * array |
---|
214 | * |
---|
215 | * @param template_id template identifier to use fields from |
---|
216 | * @param wb POI horrible spreadsheet formatted workbook object |
---|
217 | * @param mcmap linked hashmap (preserved order) of MappingColumns |
---|
218 | * @param sheetindex sheet to use when using multiple sheets |
---|
219 | * @param rowindex first row to start with reading the actual data (NOT the header) |
---|
220 | * @return two dimensional array containing records (with entities) |
---|
221 | * |
---|
222 | * @see dbnp.importer.MappingColumn |
---|
223 | */ |
---|
224 | def importdata(template_id, HSSFWorkbook wb, int sheetindex, int rowindex, mcmap) { |
---|
225 | def sheet = wb.getSheetAt(sheetindex) |
---|
226 | def table = [] |
---|
227 | |
---|
228 | // walk through all rows and fill the table with records |
---|
229 | (rowindex..sheet.getLastRowNum()).each { i -> |
---|
230 | table.add(createRecord(template_id, sheet.getRow(i), mcmap)) |
---|
231 | } |
---|
232 | return table |
---|
233 | } |
---|
234 | |
---|
235 | /** |
---|
236 | * Method to store a matrix containing the entities in a record like structure. Every row in the table |
---|
237 | * contains one or more entity objects (which contain fields with values). So actually a row represents |
---|
238 | * a record with fields from one or more different entities. |
---|
239 | * |
---|
240 | * @param study entity Study |
---|
241 | * @param datamatrix two dimensional array containing entities with values read from Excel file |
---|
242 | */ |
---|
243 | def saveDatamatrix(Study study, datamatrix) { |
---|
244 | def validatedSuccesfully = 0 |
---|
245 | def entitystored = null |
---|
246 | study.refresh() |
---|
247 | |
---|
248 | // go through the data matrix, read every record and validate the entity and try to persist it |
---|
249 | datamatrix.each { record -> |
---|
250 | record.each { entity -> |
---|
251 | switch (entity.getClass()) { |
---|
252 | case Study : print "Persisting Study `" + entity + "`: " |
---|
253 | if (persistEntity(entity)) validatedSuccesfully++ |
---|
254 | break |
---|
255 | case Subject : print "Persisting Subject `" + entity + "`: " |
---|
256 | entity.parent = study |
---|
257 | |
---|
258 | // is the current entity not already in the database? |
---|
259 | entitystored = isEntityStored(entity) |
---|
260 | |
---|
261 | // this entity is new, so add it to the study |
---|
262 | if (entitystored==null) study.addToSubjects(entity) |
---|
263 | else // existing entity, so update it |
---|
264 | updateEntity(entitystored, entity) |
---|
265 | |
---|
266 | if (persistEntity(study)) validatedSuccesfully++ |
---|
267 | break |
---|
268 | case Event : print "Persisting Event `" + entity + "`: " |
---|
269 | entity.parent = study |
---|
270 | study.addToEvents(entity) |
---|
271 | if (persistEntity(entity)) validatedSuccesfully++ |
---|
272 | break |
---|
273 | case Sample : print "Persisting Sample `" + entity +"`: " |
---|
274 | entity.parent = study |
---|
275 | study.addToSamples(entity) |
---|
276 | if (persistEntity(entity)) validatedSuccesfully++ |
---|
277 | break |
---|
278 | case SamplingEvent: print "Persisting SamplingEvent `" + entity + "`: " |
---|
279 | entity.parent = study |
---|
280 | study.addToSamplingEvents(entity) |
---|
281 | if (persistEntity(entity)) validatedSuccesfully++ |
---|
282 | break |
---|
283 | default : println "Skipping persisting of `" + entity.getclass() +"`" |
---|
284 | break |
---|
285 | } // end switch |
---|
286 | } // end record |
---|
287 | } // end datamatrix |
---|
288 | return validatedSuccesfully |
---|
289 | } |
---|
290 | |
---|
291 | /** |
---|
292 | * Check whether an entity already exists within a study. A combination of |
---|
293 | * the study where the entity will be stored and a unique field in the entity is |
---|
294 | * used to check whether the instantiated entity (read from Excel) is new. |
---|
295 | * If the entity is found in the database it will be returned as so and |
---|
296 | * it should update the entity-values (read from Ecel) into the record in the database. |
---|
297 | * |
---|
298 | * @param entity entity object like a Study, Subject, Sample et cetera |
---|
299 | * @return entity if found, otherwise null |
---|
300 | */ |
---|
301 | def isEntityStored(entity) { |
---|
302 | switch (entity.getClass()) { |
---|
303 | case Study : return Study.findByCode(entity.code) |
---|
304 | break |
---|
305 | case Subject : return Subject.findByParentAndName(entity.parent, entity.name) |
---|
306 | break |
---|
307 | case Event : break |
---|
308 | case Sample : break |
---|
309 | case SamplingEvent : break |
---|
310 | default : // unknown entity |
---|
311 | return null |
---|
312 | } |
---|
313 | } |
---|
314 | |
---|
315 | /** |
---|
316 | * Find the entity and update the fields. The entity is an instance |
---|
317 | * read from Excel. This method looks in the database for the entity |
---|
318 | * having the same identifier. If it has found the same entity |
---|
319 | * already in the database, it will update the record. |
---|
320 | * |
---|
321 | * @param entitystored existing record in the database to update |
---|
322 | * @param entity entity read from Excel |
---|
323 | */ |
---|
324 | def updateEntity(entitystored, entity) { |
---|
325 | switch (entity.getClass()) { |
---|
326 | case Study : break |
---|
327 | case Subject : entitystored.properties = entity.properties |
---|
328 | entitystored.save() |
---|
329 | break |
---|
330 | case Event : break |
---|
331 | case Sample : break |
---|
332 | case SamplingEvent : break |
---|
333 | default : // unknown entity |
---|
334 | return null |
---|
335 | } |
---|
336 | } |
---|
337 | |
---|
338 | /** |
---|
339 | * Method to persist entities into the database |
---|
340 | * Checks whether entity already exists (based on identifier column 'name') |
---|
341 | * |
---|
342 | * @param entity entity object like Study, Subject, Protocol et cetera |
---|
343 | * |
---|
344 | */ |
---|
345 | boolean persistEntity(entity) { |
---|
346 | println "persisting ${entity}" |
---|
347 | // if not validated |
---|
348 | if (entity.validate()) { |
---|
349 | if (entity.save()) { //.merge? |
---|
350 | return true |
---|
351 | } |
---|
352 | else { // if save was unsuccesful |
---|
353 | entity.errors.allErrors.each { |
---|
354 | println it |
---|
355 | } |
---|
356 | return false |
---|
357 | } |
---|
358 | } |
---|
359 | else { // if not validated |
---|
360 | entity.errors.each { |
---|
361 | println it |
---|
362 | } |
---|
363 | return false |
---|
364 | } |
---|
365 | } |
---|
366 | |
---|
367 | /** |
---|
368 | * This method creates a record (array) containing entities with values |
---|
369 | * |
---|
370 | * @param template_id template identifier |
---|
371 | * @param excelrow POI based Excel row containing the cells |
---|
372 | * @param mcmap map containing MappingColumn objects |
---|
373 | */ |
---|
374 | def createRecord(template_id, HSSFRow excelrow, mcmap) { |
---|
375 | def df = new DataFormatter() |
---|
376 | def template = Template.get(template_id) |
---|
377 | def record = [] |
---|
378 | |
---|
379 | // Initialize all possible entities with the chosen template |
---|
380 | def study = new Study(template: template) |
---|
381 | def subject = new Subject(template: template) |
---|
382 | def samplingEvent = new SamplingEvent(template: template) |
---|
383 | def event = new Event(template: template) |
---|
384 | def sample = new Sample(template: template) |
---|
385 | |
---|
386 | // Go through the Excel row cell by cell |
---|
387 | for (HSSFCell cell: excelrow) { |
---|
388 | // get the MappingColumn information of the current cell |
---|
389 | def mc = mcmap[cell.getColumnIndex()] |
---|
390 | def value |
---|
391 | |
---|
392 | // Check if column must be imported |
---|
393 | if (!mc.dontimport) { |
---|
394 | try { |
---|
395 | value = formatValue(df.formatCellValue(cell), mc.templatefieldtype) |
---|
396 | } catch (NumberFormatException nfe) { |
---|
397 | value = "" |
---|
398 | } |
---|
399 | |
---|
400 | try { |
---|
401 | |
---|
402 | // which entity does the current cell (field) belong to? |
---|
403 | switch (mc.entity) { |
---|
404 | case Study: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(study) |
---|
405 | study.setFieldValue(mc.property, value) |
---|
406 | break |
---|
407 | case Subject: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(subject) |
---|
408 | subject.setFieldValue(mc.property, value) |
---|
409 | break |
---|
410 | case SamplingEvent: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(samplingEvent) |
---|
411 | samplingEvent.setFieldValue(mc.property, value) |
---|
412 | break |
---|
413 | case Event: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(event) |
---|
414 | event.setFieldValue(mc.property, value) |
---|
415 | break |
---|
416 | case Sample: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(sample) |
---|
417 | sample.setFieldValue(mc.property, value) |
---|
418 | break |
---|
419 | case Object: // don't import |
---|
420 | break |
---|
421 | } // end switch |
---|
422 | } catch (IllegalArgumentException iae) { |
---|
423 | // leave the field empty and let the user choose the ontology manually in a later step |
---|
424 | |
---|
425 | } |
---|
426 | } // end |
---|
427 | } // end for |
---|
428 | |
---|
429 | return record |
---|
430 | } |
---|
431 | |
---|
432 | /** |
---|
433 | * Method to parse a value conform a specific type |
---|
434 | * @param value string containing the value |
---|
435 | * @return object corresponding to the TemplateFieldType |
---|
436 | */ |
---|
437 | def formatValue(String value, TemplateFieldType type) throws NumberFormatException { |
---|
438 | switch (type) { |
---|
439 | case TemplateFieldType.STRING : return value.trim() |
---|
440 | case TemplateFieldType.TEXT : return value.trim() |
---|
441 | case TemplateFieldType.INTEGER : return (int) Double.valueOf(value) |
---|
442 | case TemplateFieldType.FLOAT : return Float.valueOf(value.replace(",",".")); |
---|
443 | case TemplateFieldType.DOUBLE : return Double.valueOf(value.replace(",",".")); |
---|
444 | case TemplateFieldType.STRINGLIST : return value.trim() |
---|
445 | case TemplateFieldType.ONTOLOGYTERM : return value.trim() |
---|
446 | case TemplateFieldType.DATE : return value |
---|
447 | default : return value |
---|
448 | } |
---|
449 | } |
---|
450 | |
---|
451 | } |
---|