source: trunk/grails-app/services/dbnp/importer/ImporterService.groovy @ 1087

Last change on this file since 1087 was 1087, checked in by s.h.sikkema@…, 10 years ago

Added xlsx import functionality

  • Property svn:keywords set to Date Author Rev
File size: 18.9 KB
Line 
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: 1087 $
13 * $Author: s.h.sikkema@gmail.com $
14 * $Date: 2010-11-05 10:55:58 +0000 (vr, 05 nov 2010) $
15 */
16
17package dbnp.importer
18import org.apache.poi.ss.usermodel.*
19
20import dbnp.studycapturing.TemplateFieldType
21import dbnp.studycapturing.Template
22import dbnp.studycapturing.SamplingEvent
23import dbnp.studycapturing.Study
24import dbnp.studycapturing.Subject
25import dbnp.studycapturing.Event
26import dbnp.studycapturing.Sample
27
28class ImporterService {
29    def AuthenticationService
30
31    boolean transactional = true
32
33    /**
34    * @param is input stream representing the (workbook) resource
35    * @return high level representation of the workbook
36    */
37    Workbook getWorkbook(InputStream is) {
38        WorkbookFactory.create(is)
39    }
40
41    /**
42     * @param wb high level representation of the workbook
43     * @param sheetindex sheet to use within the workbook
44     * @return header representation as a MappingColumn hashmap
45     */
46    def getHeader(Workbook wb, int sheetindex, int headerrow, int datamatrix_start, theEntity=null){
47
48        def sheet = wb.getSheetAt(sheetindex)   
49        def sheetrow = sheet.getRow(datamatrix_start)
50        //def header = []
51        def header = [:]
52        def df = new DataFormatter()
53        def property = new String()
54
55        //for (Cell c: sheet.getRow(datamatrix_start)) {
56
57        (0..sheetrow.getLastCellNum() -1 ).each { columnindex ->
58
59            //def index =   c.getColumnIndex()
60            def datamatrix_celltype = sheet.getRow(datamatrix_start).getCell(columnindex,Row.CREATE_NULL_AS_BLANK).getCellType()
61            def datamatrix_celldata = df.formatCellValue(sheet.getRow(datamatrix_start).getCell(columnindex))
62            def datamatrix_cell     = sheet.getRow(datamatrix_start).getCell(columnindex)           
63            def headercell = sheet.getRow(headerrow-1+sheet.getFirstRowNum()).getCell(columnindex)
64            def tft = TemplateFieldType.STRING //default templatefield type
65
66            // Check for every celltype, currently redundant code, but possibly this will be
67            // a piece of custom code for every cell type like specific formatting         
68               
69            switch (datamatrix_celltype) {
70                    case Cell.CELL_TYPE_STRING:
71                            //parse cell value as double
72                            def doubleBoolean = true
73                            def fieldtype = TemplateFieldType.STRING
74
75                            // is this string perhaps a double?
76                            try {
77                                formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE)
78                            } catch (NumberFormatException nfe) { doubleBoolean = false }
79                            finally {
80                                if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE
81                            }
82
83                            header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
84                                                                            templatefieldtype:fieldtype,
85                                                                            index:columnindex,
86                                                                            entity:theEntity,
87                                                                            property:property);
88
89                            break
90                    case Cell.CELL_TYPE_NUMERIC:
91                            def fieldtype = TemplateFieldType.INTEGER
92                            def doubleBoolean = true
93                            def integerBoolean = true
94
95                            // is this cell really an integer?
96                            try {
97                                Integer.valueOf(datamatrix_celldata)
98                            } catch (NumberFormatException nfe) { integerBoolean = false }
99                            finally {
100                                if (integerBoolean) fieldtype = TemplateFieldType.INTEGER
101                            }
102
103                            // it's not an integer, perhaps a double?
104                            if (!integerBoolean)
105                                try {
106                                    formatValue(datamatrix_celldata, TemplateFieldType.DOUBLE)
107                                } catch (NumberFormatException nfe) { doubleBoolean = false }
108                                finally {
109                                    if (doubleBoolean) fieldtype = TemplateFieldType.DOUBLE
110                                }
111
112                            if (DateUtil.isCellDateFormatted(datamatrix_cell)) fieldtype = TemplateFieldType.DATE
113
114                            header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
115                                                                            templatefieldtype:fieldtype,
116                                                                            index:columnindex,
117                                                                            entity:theEntity,
118                                                                            property:property);
119                            break
120                    case Cell.CELL_TYPE_BLANK:
121                            header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
122                                                                            templatefieldtype:TemplateFieldType.STRING,
123                                                                            index:columnindex,
124                                                                            entity:theEntity,
125                                                                            property:property);
126                            break
127                    default:
128                            header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
129                                                                            templatefieldtype:TemplateFieldType.STRING,
130                                                                            index:columnindex,
131                                                                            entity:theEntity,
132                                                                            property:property);
133                            break
134            } // end of switch
135        } // end of cell loop
136        return header
137    }
138
139    /**
140     * This method is meant to return a matrix of the rows and columns
141     * used in the preview
142     *
143     * @param wb workbook object
144     * @param sheetindex sheet index used
145     * @param rows amount of rows returned
146     * @return two dimensional array (matrix) of Cell objects
147     */
148
149    Cell[][] getDatamatrix(Workbook wb, header, int sheetindex, int datamatrix_start, int count) {
150        def sheet = wb.getSheetAt(sheetindex)
151        def rows  = []
152        def df = new DataFormatter()   
153
154        // walk through all rows
155        (count <= sheet.getLastRowNum()) ?
156        ((datamatrix_start+sheet.getFirstRowNum())..count).each { rowindex ->
157            def row = []
158
159            // walk through every cell
160            /*for (Cell c: sheet.getRow(rowindex)) {
161                row.add(c)
162                println c.getColumnIndex() + "=" +c
163            }*/
164           
165            (0..header.size()-1).each { columnindex ->
166                def c = sheet.getRow(rowindex).getCell(columnindex, Row.CREATE_NULL_AS_BLANK)
167                //row.add(df.formatCellValue(c))
168                row.add(c)
169                //if (c.getCellType() == c.CELL_TYPE_STRING) println "STR"+c.getStringCellValue()
170                //if (c.getCellType() == c.CELL_TYPE_NUMERIC) println "INT" +c.getNumericCellValue()
171            }
172                //row.add(df.formatCellValue(c))
173            rows.add(row)
174        } : 0
175
176        return rows
177    }
178
179    /**
180    * This method will move a file to a new location.
181    *
182    * @param file File object to move
183    * @param folderpath folder to move the file to
184    * @param filename (new) filename to give
185    * @return if file has been moved succesful, the new path and filename will be returned, otherwise an empty string will be returned
186    */
187    def moveFile(File file, String folderpath, String filename) {
188        try {
189                def rnd = ""; //System.currentTimeMillis()
190                file.transferTo(new File(folderpath, rnd+filename))
191                return folderpath + filename
192            } catch(Exception exception) {
193                log.error "File move error, ${exception}"
194                return ""
195                }
196    }
197
198    /**
199    * @return random numeric value
200    */
201    def random = {
202            return System.currentTimeMillis() + Runtime.runtime.freeMemory()
203        }
204
205    /**
206    * Method to read data from a workbook and to import data into a two dimensional
207    * array
208    *
209    * @param template_id template identifier to use fields from
210    * @param wb POI horrible spreadsheet formatted workbook object
211    * @param mcmap linked hashmap (preserved order) of MappingColumns
212    * @param sheetindex sheet to use when using multiple sheets
213    * @param rowindex first row to start with reading the actual data (NOT the header)
214    * @return two dimensional array containing records (with entities)
215    *
216    * @see dbnp.importer.MappingColumn
217    */
218    def importData(template_id, Workbook wb, int sheetindex, int rowindex, mcmap) {
219        def sheet = wb.getSheetAt(sheetindex)
220        def table = []
221       
222        // walk through all rows and fill the table with records
223        (rowindex..sheet.getLastRowNum()).each { i ->
224            table.add(createRecord(template_id, sheet.getRow(i), mcmap))
225        }
226        return table   
227    }
228
229    /** Method to extract failed cells from the datamatrix. Failed cells are cell values
230     * which could not be stored in an entity (e.g. Humu Supiuns in an ontology field).
231     *
232     * @param datamatrix two dimensional array containing entities and possibly also failed cells
233     * @return array of failed cells in [mappingcolumn, cell] format
234     * */
235    def getFailedCells(datamatrix) {
236       def failedcells = []
237       
238       datamatrix.each { record ->
239            record.each { column ->
240                column.each {                   
241                    if (it.getClass().getName().equals('java.util.LinkedHashMap$Entry')) {                       
242                        failedcells.add(it)
243                    }
244                }
245            }
246        }
247
248        return failedcells
249    }
250
251    /** Method to put failed cells back into the datamatrix. Failed cells are cell values
252     * which could not be stored in an entity (e.g. Humu Supiuns in an ontology field).
253     *
254     * @param datamatrix two dimensional array containing entities and possibly also failed cells
255     * @param failedcells map of failed cells in [mappingcolumn, cell] format
256     * @param correctedcells map of corrected cells
257     **/
258    def saveCorrectedCells(datamatrix, failedcells, correctedcells) {
259        /*failedcells.each {
260            println it
261        }*/
262        def newdatamatrix = []
263
264        // Remove failed cells 'entity' / clean up datamatrix
265        datamatrix.each { record ->
266            def newrecord = []
267
268            record.each { entity ->
269              // LinkedHashMap means a "mappingcolumn:cell" object is inside the record (= failed to map to entity)
270              if (!entity.getClass().getName().equals('java.util.LinkedHashMap'))
271              newrecord.add(entity)
272            }
273
274            newdatamatrix.add(newrecord)
275        }
276
277        newdatamatrix.each { record ->
278            record.each { entity ->
279                entity.giveFields().each { field ->
280                    println "das"+ field
281                }
282            }
283        }
284
285        println "-----"
286        correctedcells.each {
287            println it.dump()
288        }
289    }
290   
291    /**
292     * Method to store a matrix containing the entities in a record like structure. Every row in the table
293     * contains one or more entity objects (which contain fields with values). So actually a row represents
294     * a record with fields from one or more different entities.
295     *
296     * @param study entity Study
297     * @param datamatrix two dimensional array containing entities with values read from Excel file
298     */   
299    def saveDatamatrix(Study study, datamatrix) {
300        def validatedSuccesfully = 0
301        def entitystored = null
302        study.refresh()       
303       
304        // go through the data matrix, read every record and validate the entity and try to persist it
305        datamatrix.each { record ->
306            record.each { entity ->
307                        switch (entity.getClass()) {
308                        case Study       :  print "Persisting Study `" + entity + "`: "
309                                                entity.owner = AuthenticationService.getLoggedInUser()
310                                                if (persistEntity(entity)) validatedSuccesfully++
311                                                break
312                        case Subject     :  print "Persisting Subject `" + entity + "`: "
313                                                entity.parent = study
314                                               
315                                                // is the current entity not already in the database?
316                                                entitystored = isEntityStored(entity)
317                                               
318                                                // this entity is new, so add it to the study
319                                                if (entitystored==null) study.addToSubjects(entity)
320                                                else // existing entity, so update it
321                                                    updateEntity(entitystored, entity)
322
323                                                if (persistEntity(study)) validatedSuccesfully++
324                                                break
325                        case Event       :  print "Persisting Event `" + entity + "`: "
326                                                entity.parent = study
327                                                study.addToEvents(entity)
328                                                if (persistEntity(entity)) validatedSuccesfully++
329                                                break
330                        case Sample      :  print "Persisting Sample `" + entity +"`: "
331                                                entity.parent = study
332                                                study.addToSamples(entity)
333                                                if (persistEntity(entity)) validatedSuccesfully++
334                                                break
335                        case SamplingEvent: print "Persisting SamplingEvent `" + entity + "`: "
336                                                entity.parent = study
337                                                study.addToSamplingEvents(entity)
338                                                if (persistEntity(entity)) validatedSuccesfully++
339                                                break
340                        default          :  println "Skipping persisting of `" + entity.getclass() +"`"
341                                                break
342                        } // end switch
343            } // end record
344        } // end datamatrix
345        return validatedSuccesfully
346    }
347
348    /**
349     * Check whether an entity already exists within a study. A combination of
350     * the study where the entity will be stored and a unique field in the entity is
351     * used to check whether the instantiated entity (read from Excel) is new.
352     * If the entity is found in the database it will be returned as so and
353     * it should update the entity-values (read from Ecel) into the record in the database.
354     *
355     * @param entity entity object like a Study, Subject, Sample et cetera
356     * @return entity if found, otherwise null
357     */
358    def isEntityStored(entity) {
359            switch (entity.getClass()) {
360                        case Study          :  return Study.findByCode(entity.code)
361                                               break
362                        case Subject        :  return Subject.findByParentAndName(entity.parent, entity.name)
363                                               break
364                        case Event          :  break
365                        case Sample         :  break
366                        case SamplingEvent  :  break
367                        default             :  // unknown entity
368                                               return null
369            }
370    }
371
372    /**
373     * Find the entity and update the fields. The entity is an instance
374     * read from Excel. This method looks in the database for the entity
375     * having the same identifier. If it has found the same entity
376     * already in the database, it will update the record.
377     *
378     * @param entitystored existing record in the database to update
379     * @param entity entity read from Excel
380     */
381    def updateEntity(entitystored, entity) {
382        switch (entity.getClass()) {
383                        case Study          :  break
384                        case Subject        :  entitystored.properties = entity.properties
385                                               entitystored.save()
386                                               break
387                        case Event          :  break
388                        case Sample         :  break
389                        case SamplingEvent  :  break
390                        default             :  // unknown entity
391                                               return null
392        }
393    }
394
395    /**
396     * Method to persist entities into the database
397     * Checks whether entity already exists (based on identifier column 'name')
398     *
399     * @param entity entity object like Study, Subject, Protocol et cetera
400     *
401     */
402    boolean persistEntity(entity) {
403            println "persisting ${entity}"           
404            // if not validated
405                if (entity.validate()) {
406                        if (entity.save()) { //.merge?
407                                return true
408                        }
409                        else { // if save was unsuccesful
410                                entity.errors.allErrors.each {
411                                        println it
412                                }
413                                return false
414                        }
415                }
416            else { // if not validated
417                    entity.errors.each {
418                            println it
419                    }
420                        return false
421            }
422         }
423
424        /**
425         * This method creates a record (array) containing entities with values
426         *
427         * @param template_id template identifier
428         * @param excelrow POI based Excel row containing the cells
429         * @param mcmap map containing MappingColumn objects
430         */
431        def createRecord(template_id, Row excelrow, mcmap) {
432                def df = new DataFormatter()
433                def template = Template.get(template_id)
434                def record = []
435                def failed = [:]
436
437                // Initialize all possible entities with the chosen template
438                def study = new Study(template: template)
439                def subject = new Subject(template: template)
440                def samplingEvent = new SamplingEvent(template: template)
441                def event = new Event(template: template)
442                def sample = new Sample(template: template)
443
444                // Go through the Excel row cell by cell
445                for (Cell cell: excelrow) {
446                        // get the MappingColumn information of the current cell
447                        def mc = mcmap[cell.getColumnIndex()]
448                        def value                       
449
450                        // Check if column must be imported
451                        if (mc!=null) if (!mc.dontimport) {
452                                try {
453                                        value = formatValue(df.formatCellValue(cell), mc.templatefieldtype)
454                                } catch (NumberFormatException nfe) {
455                                        value = ""
456                                }
457
458                                try {
459
460                                // which entity does the current cell (field) belong to?
461                                    switch (mc.entity) {
462                                        case Study: // does the entity already exist in the record? If not make it so.
463                                                (record.any {it.getClass() == mc.entity}) ? 0 : record.add(study)
464                                                study.setFieldValue(mc.property, value)
465                                                break
466                                        case Subject: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(subject)
467                                                subject.setFieldValue(mc.property, value)
468                                                break
469                                        case SamplingEvent: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(samplingEvent)
470                                                samplingEvent.setFieldValue(mc.property, value)
471                                                break
472                                        case Event: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(event)
473                                                event.setFieldValue(mc.property, value)
474                                                break
475                                        case Sample: (record.any {it.getClass() == mc.entity}) ? 0 : record.add(sample)
476                                                sample.setFieldValue(mc.property, value)
477                                                break
478                                        case Object:   // don't import
479                                                break
480                                    } // end switch
481                                } catch (IllegalArgumentException iae) {
482                                    // store the mapping column and the cell in an array
483                                    failed.put(mc, cell)
484                                    //println "failed ("+mc.templatefieldtype+"`" + value + "`"
485                                }
486                        } // end
487                } // end for
488
489        // add the failed columns to the record (might also be just an empty map if nothing failed)
490        // a failed column means that using the entity.setFieldValue() threw an exception
491        //record.add(failed)
492        return record
493    }
494
495    /**
496    * Method to parse a value conform a specific type
497    * @param value string containing the value
498    * @return object corresponding to the TemplateFieldType
499    */
500    def formatValue(String value, TemplateFieldType type) throws NumberFormatException {
501            switch (type) {
502                case TemplateFieldType.STRING       :   return value.trim()
503                case TemplateFieldType.TEXT         :   return value.trim()
504                case TemplateFieldType.INTEGER      :   return (int) Double.valueOf(value)
505                case TemplateFieldType.FLOAT        :   return Float.valueOf(value.replace(",","."));
506                case TemplateFieldType.DOUBLE       :   return Double.valueOf(value.replace(",","."));
507                case TemplateFieldType.STRINGLIST   :   return value.trim()
508                case TemplateFieldType.ONTOLOGYTERM :   return value.trim()
509                case TemplateFieldType.DATE         :   return value
510                default                             :   return value
511            }
512    }
513
514}
Note: See TracBrowser for help on using the repository browser.