Ignore:
Timestamp:
Nov 5, 2010, 11:55:58 AM (10 years ago)
Author:
s.h.sikkema@…
Message:

Added xlsx import functionality

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/grails-app/services/dbnp/importer/ImporterService.groovy

    r1083 r1087  
    1616
    1717package 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
     18import org.apache.poi.ss.usermodel.*
    2119
    2220import dbnp.studycapturing.TemplateFieldType
     
    2826import dbnp.studycapturing.Sample
    2927
    30 import dbnp.data.Term
    31 
    3228class ImporterService {
    3329    def AuthenticationService
     
    3935    * @return high level representation of the workbook
    4036    */
    41     HSSFWorkbook getWorkbook(InputStream is) {
    42         POIFSFileSystem fs = new POIFSFileSystem(is)
    43         HSSFWorkbook    wb = new HSSFWorkbook(fs);
    44         return wb;
     37    Workbook getWorkbook(InputStream is) {
     38        WorkbookFactory.create(is)
    4539    }
    4640
     
    5044     * @return header representation as a MappingColumn hashmap
    5145     */
    52     def getHeader(HSSFWorkbook wb, int sheetindex, int headerrow, int datamatrix_start, theEntity=null){
     46    def getHeader(Workbook wb, int sheetindex, int headerrow, int datamatrix_start, theEntity=null){
    5347
    5448        def sheet = wb.getSheetAt(sheetindex)   
     
    5953        def property = new String()
    6054
    61         //for (HSSFCell c: sheet.getRow(datamatrix_start)) {
     55        //for (Cell c: sheet.getRow(datamatrix_start)) {
    6256
    6357        (0..sheetrow.getLastCellNum() -1 ).each { columnindex ->
    6458
    6559            //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()
     60            def datamatrix_celltype = sheet.getRow(datamatrix_start).getCell(columnindex,Row.CREATE_NULL_AS_BLANK).getCellType()
    6761            def datamatrix_celldata = df.formatCellValue(sheet.getRow(datamatrix_start).getCell(columnindex))
    6862            def datamatrix_cell     = sheet.getRow(datamatrix_start).getCell(columnindex)           
     
    7468               
    7569            switch (datamatrix_celltype) {
    76                     case HSSFCell.CELL_TYPE_STRING:
     70                    case Cell.CELL_TYPE_STRING:
    7771                            //parse cell value as double
    7872                            def doubleBoolean = true
     
    9488
    9589                            break
    96                     case HSSFCell.CELL_TYPE_NUMERIC:
     90                    case Cell.CELL_TYPE_NUMERIC:
    9791                            def fieldtype = TemplateFieldType.INTEGER
    9892                            def doubleBoolean = true
     
    116110                                }
    117111
    118                             if (HSSFDateUtil.isCellDateFormatted(datamatrix_cell)) fieldtype = TemplateFieldType.DATE
     112                            if (DateUtil.isCellDateFormatted(datamatrix_cell)) fieldtype = TemplateFieldType.DATE
    119113
    120114                            header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
     
    124118                                                                            property:property);
    125119                            break
    126                     case HSSFCell.CELL_TYPE_BLANK:
     120                    case Cell.CELL_TYPE_BLANK:
    127121                            header[columnindex] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell),
    128122                                                                            templatefieldtype:TemplateFieldType.STRING,
     
    150144     * @param sheetindex sheet index used
    151145     * @param rows amount of rows returned
    152      * @return two dimensional array (matrix) of HSSFCell objects
     146     * @return two dimensional array (matrix) of Cell objects
    153147     */
    154148
    155     HSSFCell[][] getDatamatrix(HSSFWorkbook wb, header, int sheetindex, int datamatrix_start, int count) {
     149    Cell[][] getDatamatrix(Workbook wb, header, int sheetindex, int datamatrix_start, int count) {
    156150        def sheet = wb.getSheetAt(sheetindex)
    157151        def rows  = []
     
    164158
    165159            // walk through every cell
    166             /*for (HSSFCell c: sheet.getRow(rowindex)) {
     160            /*for (Cell c: sheet.getRow(rowindex)) {
    167161                row.add(c)
    168162                println c.getColumnIndex() + "=" +c
     
    170164           
    171165            (0..header.size()-1).each { columnindex ->
    172                 def c = sheet.getRow(rowindex).getCell(columnindex, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK)               
     166                def c = sheet.getRow(rowindex).getCell(columnindex, Row.CREATE_NULL_AS_BLANK)
    173167                //row.add(df.formatCellValue(c))
    174168                row.add(c)
     
    222216    * @see dbnp.importer.MappingColumn
    223217    */
    224     def importData(template_id, HSSFWorkbook wb, int sheetindex, int rowindex, mcmap) {
     218    def importData(template_id, Workbook wb, int sheetindex, int rowindex, mcmap) {
    225219        def sheet = wb.getSheetAt(sheetindex)
    226220        def table = []
     
    237231     *
    238232     * @param datamatrix two dimensional array containing entities and possibly also failed cells
    239      * @return array of failed cells in [mappingcolumn, hssfcell] format
     233     * @return array of failed cells in [mappingcolumn, cell] format
    240234     * */
    241235    def getFailedCells(datamatrix) {
     
    259253     *
    260254     * @param datamatrix two dimensional array containing entities and possibly also failed cells
    261      * @param failedcells map of failed cells in [mappingcolumn, hssfcell] format
     255     * @param failedcells map of failed cells in [mappingcolumn, cell] format
    262256     * @param correctedcells map of corrected cells
    263257     **/
     
    273267
    274268            record.each { entity ->
    275               // LinkedHashMap means a "mappingcolumn:hssfcell" object is inside the record (= failed to map to entity)
     269              // LinkedHashMap means a "mappingcolumn:cell" object is inside the record (= failed to map to entity)
    276270              if (!entity.getClass().getName().equals('java.util.LinkedHashMap'))
    277271              newrecord.add(entity)
     
    435429         * @param mcmap map containing MappingColumn objects
    436430         */
    437         def createRecord(template_id, HSSFRow excelrow, mcmap) {
     431        def createRecord(template_id, Row excelrow, mcmap) {
    438432                def df = new DataFormatter()
    439433                def template = Template.get(template_id)
     
    449443
    450444                // Go through the Excel row cell by cell
    451                 for (HSSFCell cell: excelrow) {
     445                for (Cell cell: excelrow) {
    452446                        // get the MappingColumn information of the current cell
    453447                        def mc = mcmap[cell.getColumnIndex()]
Note: See TracChangeset for help on using the changeset viewer.