Changeset 1087


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

Added xlsx import functionality

Location:
trunk
Files:
2 added
3 edited

Legend:

Unmodified
Added
Removed
  • trunk/grails-app/controllers/dbnp/importer/ImporterController.groovy

    r1083 r1087  
    2323import dbnp.studycapturing.Template
    2424import dbnp.studycapturing.Study
    25 import dbnp.studycapturing.Subject
    26 import dbnp.studycapturing.Event
    27 
    28 import dbnp.studycapturing.Sample
     25
    2926import dbnp.studycapturing.TemplateFieldType
    30 import dbnp.studycapturing.TemplateField
    3127import grails.converters.JSON
    32 import org.apache.poi.hssf.usermodel.HSSFWorkbook
     28import org.apache.poi.ss.usermodel.Workbook
    3329import grails.plugins.springsecurity.Secured
    3430
     
    125121     * @return workbook object reference
    126122     */
    127     private HSSFWorkbook handleUpload(formfilename) {
     123    private Workbook handleUpload(formfilename) {
    128124
    129125        def downloadedfile = request.getFile(formfilename);
  • 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()]
  • trunk/grails-app/taglib/dbnp/importer/ImporterTagLib.groovy

    r1058 r1087  
    1616package dbnp.importer
    1717import dbnp.studycapturing.Template
    18 import dbnp.studycapturing.TemplateField
    1918import dbnp.studycapturing.TemplateFieldType
    20 import org.apache.poi.hssf.usermodel.HSSFCell
     19import org.apache.poi.ss.usermodel.Cell
    2120import org.apache.poi.ss.usermodel.DataFormatter
    2221
     
    208207
    209208    /**
    210     * @param cell HSSFCell variable
     209    * @param cell Cell variable
    211210    * @return good representation of variable (instead of toString())
    212211    */
     
    216215
    217216        switch (cell.getCellType()) {
    218             case HSSFCell.CELL_TYPE_STRING      :   out << cell.getStringCellValue()
     217            case Cell.CELL_TYPE_STRING  :   out << cell.getStringCellValue()
    219218                                                    break
    220             case HSSFCell.CELL_TYPE_NUMERIC     :   out << df.formatCellValue(cell)
     219            case Cell.CELL_TYPE_NUMERIC :   out << df.formatCellValue(cell)
    221220                                                    break
    222221        }
Note: See TracChangeset for help on using the changeset viewer.