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