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: 255 $ |
---|
13 | * $Author: tabma $ |
---|
14 | * $Date: 2010-03-10 18:27:57 +0000 (wo, 10 mrt 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 | import org.apache.poi.hssf.usermodel.HSSFDateUtil |
---|
22 | import dbnp.importer.Column |
---|
23 | import dbnp.studycapturing.TemplateFieldType |
---|
24 | |
---|
25 | class ImporterService { |
---|
26 | |
---|
27 | boolean transactional = true |
---|
28 | |
---|
29 | /** |
---|
30 | * @param is input stream representing the (workbook) resource |
---|
31 | * @return high level representation of the workbook |
---|
32 | */ |
---|
33 | HSSFWorkbook getWorkbook(InputStream is) { |
---|
34 | POIFSFileSystem fs = new POIFSFileSystem(is) |
---|
35 | HSSFWorkbook wb = new HSSFWorkbook(fs); |
---|
36 | return wb; |
---|
37 | } |
---|
38 | |
---|
39 | /** |
---|
40 | * @param wb high level representation of the workbook |
---|
41 | * @return header representation as a string array |
---|
42 | */ |
---|
43 | def getHeader(HSSFWorkbook wb, int sheetindex){ |
---|
44 | |
---|
45 | def sheet = wb.getSheetAt(sheetindex) |
---|
46 | def datamatrix_start = sheet.getFirstRowNum() + 1 |
---|
47 | //def header = [] |
---|
48 | def header = [:] |
---|
49 | def df = new DataFormatter() |
---|
50 | |
---|
51 | |
---|
52 | for (HSSFCell c: sheet.getRow(datamatrix_start)) { |
---|
53 | def datamatrix_celltype = sheet.getRow(datamatrix_start).getCell(c.getColumnIndex()).getCellType() |
---|
54 | def headercell = sheet.getRow(sheet.getFirstRowNum()).getCell(c.getColumnIndex()) |
---|
55 | |
---|
56 | // Check for every celltype, currently redundant code, but possibly this will be |
---|
57 | // a piece of custom code for every cell type like specific formatting |
---|
58 | |
---|
59 | switch (datamatrix_celltype) { |
---|
60 | case HSSFCell.CELL_TYPE_STRING: |
---|
61 | header[c.getColumnIndex()] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell), templatefieldtype:TemplateFieldType.STRING); |
---|
62 | break |
---|
63 | case HSSFCell.CELL_TYPE_NUMERIC: |
---|
64 | if (HSSFDateUtil.isCellDateFormatted(c)) { |
---|
65 | println("DATE") |
---|
66 | header[c.getColumnIndex()] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell), templatefieldtype:TemplateFieldType.DATE) |
---|
67 | } |
---|
68 | else |
---|
69 | header[c.getColumnIndex()] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell), templatefieldtype:TemplateFieldType.INTEGER); |
---|
70 | break |
---|
71 | case HSSFCell.CELL_TYPE_BLANK: |
---|
72 | header[c.getColumnIndex()] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell), templatefieldtype:TemplateFieldType.STRING); |
---|
73 | break |
---|
74 | default: |
---|
75 | header[c.getColumnIndex()] = new dbnp.importer.MappingColumn(name:df.formatCellValue(headercell), templatefieldtype:TemplateFieldType.STRING); |
---|
76 | break |
---|
77 | } |
---|
78 | } |
---|
79 | return header |
---|
80 | } |
---|
81 | |
---|
82 | /** |
---|
83 | * This method is meant to return a matrix of the rows and columns |
---|
84 | * used in the preview |
---|
85 | * |
---|
86 | * @param wb workbook object |
---|
87 | * @param sheetindex sheet index used |
---|
88 | * @param rows amount of rows returned |
---|
89 | * @return two dimensional array (matrix) of HSSFCell objects |
---|
90 | */ |
---|
91 | |
---|
92 | HSSFCell[][] getDatamatrix(HSSFWorkbook wb, int sheetindex, int count) { |
---|
93 | def sheet = wb.getSheetAt(sheetindex) |
---|
94 | def rows = [] |
---|
95 | def df = new DataFormatter() |
---|
96 | |
---|
97 | (count <= sheet.getLastRowNum()) ? |
---|
98 | ((1+sheet.getFirstRowNum())..count).each { rowindex -> |
---|
99 | |
---|
100 | def row = [] |
---|
101 | for (HSSFCell c: sheet.getRow(rowindex)) |
---|
102 | row.add(c) |
---|
103 | //row.add(df.formatCellValue(c)) |
---|
104 | rows.add(row) |
---|
105 | } : 0 |
---|
106 | |
---|
107 | return rows |
---|
108 | } |
---|
109 | |
---|
110 | /** |
---|
111 | * This method will move a file to a new location. |
---|
112 | * |
---|
113 | * @param file File object to move |
---|
114 | * @param folderpath folder to move the file to |
---|
115 | * @param filename (new) filename to give |
---|
116 | * @return if file has been moved succesful, the new path and filename will be returned, otherwise an empty string will be returned |
---|
117 | */ |
---|
118 | def moveFile(File file, String folderpath, String filename) { |
---|
119 | try { |
---|
120 | def rnd = ""; //System.currentTimeMillis() |
---|
121 | file.transferTo(new File(folderpath, rnd+filename)) |
---|
122 | return folderpath + filename |
---|
123 | } catch(Exception exception) { |
---|
124 | log.error "File move error, ${exception}" |
---|
125 | return "" |
---|
126 | } |
---|
127 | } |
---|
128 | |
---|
129 | /** |
---|
130 | * @return random numeric value |
---|
131 | */ |
---|
132 | def random = { |
---|
133 | return System.currentTimeMillis() + Runtime.runtime.freeMemory() |
---|
134 | } |
---|
135 | |
---|
136 | /** |
---|
137 | * Method to read data from a workbook and to import data into the database |
---|
138 | * by using mapping information |
---|
139 | * |
---|
140 | * |
---|
141 | * @param wb POI horrible spreadsheet formatted workbook object |
---|
142 | * @param mc array of MappingColumns |
---|
143 | * @param sheetindex sheet to use when using multiple sheets |
---|
144 | * @param rowindex first row to start with reading the actual data (NOT the header) |
---|
145 | * |
---|
146 | * @see dbnp.importer.MappingColumn |
---|
147 | */ |
---|
148 | def importdata(HSSFWorkbook wb, int sheetindex, int rowindex, MappingColumn[] mc) { |
---|
149 | def sheet = wb.getSheetAt(sheetindex) |
---|
150 | def rows = [] |
---|
151 | |
---|
152 | (count <= sheet.getLastRowNum()) ? |
---|
153 | (rowindex..count).each { i -> |
---|
154 | |
---|
155 | def row = [] |
---|
156 | for (HSSFCell c: sheet.getRow(i)) |
---|
157 | //row.add(c) |
---|
158 | //row.add(df.formatCellValue(c)) |
---|
159 | switch(mc[c.getColumnIndex()].celltype) { |
---|
160 | case 0 : break |
---|
161 | default : break |
---|
162 | } |
---|
163 | |
---|
164 | rows.add(row) |
---|
165 | } : 0 |
---|
166 | } |
---|
167 | } |
---|