source: trunk/grails-app/services/nl/tno/massSequencing/files/ExcelService.groovy

Last change on this file was 58, checked in by robert@…, 8 years ago

Implemented importing of classifications

File size: 10.4 KB
Line 
1package nl.tno.massSequencing.files
2
3import groovy.lang.Closure;
4
5import java.io.File;
6import java.text.DecimalFormat
7import java.text.Format
8import java.text.NumberFormat
9import java.util.Map;
10
11import org.apache.poi.xssf.usermodel.XSSFWorkbook;
12import org.apache.poi.hssf.usermodel.HSSFWorkbook;
13import org.apache.poi.ss.usermodel.*
14
15/**
16 * Convenience methods for reading and writing excel files
17 * @author      Robert Horlings robert@isdat.nl
18 * @see         apache poi api
19 *
20 */
21class ExcelService implements nl.tno.massSequencing.imports.Importer {
22    static transactional = false
23       
24        /**
25         * Create a new excel workbook, containing 1 sheet
26         * @return
27         */
28        public Workbook create() {
29                Workbook book = new HSSFWorkbook()
30                book.createSheet()
31               
32                return book
33        }
34       
35        public Workbook open( String filename ) {
36                return open( new File( filename ) );
37        }
38       
39        public Workbook open( File file ) {
40                return open( new FileInputStream( file ) );     
41        }
42       
43        public Workbook open( InputStream file ) {
44                return WorkbookFactory.create(file);
45        }
46       
47        /**
48         * Reads specific rows from a given sheet of a file
49         *
50         */
51        public ArrayList readData( Workbook book, int sheetIndex = 0, int startRow = -1, int endRow = -1, int maxRows = 0, int startColumn = 0, int endColumn = 0 ) {
52                if( book == null )
53                        throw new Exception( "No workbook given." );
54                       
55                if( sheetIndex >= book.getNumberOfSheets() )
56                        throw new Exception( "Sheet with index " + sheetIndex + " doesn't exist. Workbook has " + book.getNumberOfSheets() + " sheets." )
57
58
59                Sheet sheet = book.getSheetAt(sheetIndex)
60               
61                // Determine the start and end row if none is given
62                if( startRow == null || startRow == -1 )
63                        startRow = sheet.getFirstRowNum();
64
65                if( endRow == null || endRow == -1 ) {
66                        endRow = sheet.getLastRowNum();
67                }
68
69                // Check whether a max # rows is given
70                if( maxRows > 0 ) {
71                        endRow = Math.min( endRow, startRow + maxRows - 1 );
72                }
73               
74                if( startRow < sheet.getFirstRowNum() || startRow > sheet.getLastRowNum() )
75                        throw new Exception( "Can't return start row " + startRow + " since the row doesn't exist." )
76                       
77                if( endRow < sheet.getFirstRowNum() || endRow > sheet.getLastRowNum() )
78                        throw new Exception( "Can't return end row " + endRow + " since the row doesn't exist.")
79
80                // Now loop through all rows, retrieving data from the excel file
81                def df = new DataFormatter()
82                DecimalFormat numberformat =  new DecimalFormat( "0" );
83               
84                ArrayList data = []
85               
86                for( def rowNum = startRow; rowNum <= endRow; rowNum++ ) {
87                        Row excelRow = sheet.getRow( rowNum );
88                       
89                        if( !rowIsEmpty( excelRow ) ) {
90                                ArrayList row = []
91                               
92                                for( def colNum = 0; colNum < excelRow.getLastCellNum(); colNum++ ) {
93                                        Cell c = excelRow.getCell( colNum );
94                                        if( c ) {
95                                                switch( c.getCellType() ) {
96                                                        case Cell.CELL_TYPE_NUMERIC:
97                                                                row << numberformat.format( c.getNumericCellValue() );
98                                                                break;
99                                                        case Cell.CELL_TYPE_BLANK:
100                                                        case Cell.CELL_TYPE_BOOLEAN:
101                                                        case Cell.CELL_TYPE_STRING:
102                                                        case Cell.CELL_TYPE_FORMULA:
103                                                                row << df.formatCellValue( c );
104                                                                break
105                                                        case Cell.CELL_TYPE_ERROR:
106                                                                row << "Err";
107                                                                break;
108                                                }
109                                        } else {
110                                                row << ""
111                                        }
112                                       
113                                }
114                               
115                                data << row;
116                        }
117                }
118               
119                return data
120        }
121       
122        /**
123         * Read all data from a row
124         * @param book
125         * @return
126         */
127        public ArrayList readRow( Workbook book, int sheetIndex = 0, int rowNum = 0 ) {
128                return readData( book, sheetIndex, rowNum, rowNum )[0];
129        }
130       
131        /**
132         * Write all given data to an excel sheet.
133         * @param data
134         */
135        public Workbook writeData( Workbook book, ArrayList data, int sheetIndex = 0, int startRow = 0, int startColumn = 0, CellStyle cellStyle = null ) {
136                if( book == null )
137                        throw new Exception( "No workbook given." );
138                       
139                if( sheetIndex >= book.getNumberOfSheets() )
140                        throw new Exception( "Sheet with index " + sheetIndex + " doesn't exist. Workbook has " + book.getNumberOfSheets() + " sheets." )
141       
142                Sheet sheet = book.getSheetAt(sheetIndex)
143               
144                // Loop through all rows
145                short rowNum = (short) startRow
146                data.each { ArrayList row -> 
147                        // Check whether the row already exists
148                        Row excelRow = sheet.getRow(rowNum)
149                       
150                        // If not, create a new one
151                        if( excelRow == null )
152                                excelRow = sheet.createRow(rowNum)
153                               
154                        short colNum = (short) startColumn
155                        for ( short i = 0; i < row.size(); i++ ) {
156                                Cell c = excelRow.createCell(colNum + i)
157                                c.setCellValue( row[i] )
158                               
159                                if( cellStyle != null )
160                                        c.setCellStyle(cellStyle)
161                        }
162                       
163                        rowNum++;
164                }
165               
166                return book
167        }
168       
169        public Workbook writeRow( Workbook book, ArrayList row, int sheetIndex = 0, int rowNum = 0, CellStyle cellStyle = null ) {
170                return writeData( book, [row], sheetIndex, rowNum, 0, cellStyle )
171        }
172       
173        /**
174         * Writes a header to the excel file (being bold and with a border-bottom)
175         * @param book
176         * @param header
177         * @return
178         */
179        public Workbook writeHeader(Workbook book, ArrayList headers, int sheetIndex = 0, int rowNum = 0) {
180                // Create a bold font and assign it to the row
181                CellStyle cs = book.createCellStyle();
182                cs.setBorderBottom(CellStyle.BORDER_THIN);
183                cs.setBottomBorderColor(IndexedColors.BLACK.getIndex());
184
185                Font boldFont = book.createFont()
186                boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
187                cs.setFont(boldFont)
188               
189                return writeRow( book, headers, sheetIndex, rowNum, cs);
190        }
191       
192        public Workbook writeDataWithHeader( Workbook book, ArrayList data, int sheetIndex = 0, int rowNum = 0, CellStyle cellStyle = null ) {
193                writeHeader( book, data.head(), sheetIndex, rowNum );
194                return writeData( book, data.tail(), sheetIndex, rowNum + 1, cellStyle );
195        }
196       
197        /**
198         * Resizes specified columns to match it contents
199         * @param book
200         * @return
201         */
202        public Workbook autoSizeColumns( Workbook book, int sheetIndex = 0, def columns = 0 ) {
203                if( book == null )
204                        throw new Exception( "No workbook given." );
205                       
206                if( sheetIndex >= book.getNumberOfSheets() )
207                        throw new Exception( "Sheet with index " + sheetIndex + " doesn't exist. Workbook has " + book.getNumberOfSheets() + " sheets." )
208       
209                Sheet sheet = book.getSheetAt(sheetIndex)
210
211                if( !( columns instanceof Collection ) ) 
212                        columns = [columns]
213               
214                columns.each { sheet.autoSizeColumn( (short) it ); }
215               
216                return book
217
218        }
219       
220       
221        /**
222         * Checks whether an excel row is empty
223         * @param row   Row from the excel sheet
224         * @return              True if all cells in this row are empty or the given row is null. False otherwise
225         */
226        def rowIsEmpty( Row excelRow ) {
227                if( !excelRow )
228                        return true;
229               
230                def df = new DataFormatter();
231                for( int i = excelRow.getFirstCellNum(); i < excelRow.getLastCellNum(); i++ ) {
232                        Cell cell = excelRow.getCell( i );
233                       
234                        try {
235                                def value = df.formatCellValue(cell)
236                                if( value )
237                                        return false
238                        } catch (NumberFormatException nfe) {
239                                // If the number can't be formatted, the row isn't empty
240                                return false;
241                        }
242                }
243               
244                return true;
245        }
246
247       
248        /**
249         * Return the given workbook for download
250         */
251        public void downloadFile( Workbook book, String filename, def response ) {
252                if( book == null )
253                        throw new Exception( "No workbook given." );
254
255                response.setHeader("Content-disposition", "attachment;filename=\"${filename}\"")
256                response.setContentType("application/octet-stream")
257                book.write(response.outputStream)
258                response.outputStream.close()
259
260        }
261       
262       
263        /**
264         * Determines whether a file can be processed.
265         * @param filetype      Filetype of the file
266         * @see determineFileType()
267         * @return
268         */
269        public boolean canParseFileType( String filetype ) {
270                switch( filetype ) {
271                        case "excel":
272                                return true;
273                        default:
274                                return false;
275                }
276        }
277
278        /**
279         * Parses the given excel file
280         * @param file                  File to parse
281         * @param filetype              Type of the given file (only excel is accepted)
282         * @param onProgress    Closure to execute when progress indicators should be updated.
283         *                                              Has 2 parameters: progress that indicates the number of bytes that have been processed. The second parameter determines
284         *                                              the number of bytes that has to be processed extra after this action (e.g. when a zip file is extracted, the extracted
285         *                                              files should be added to the total number of bytes to be processed)
286         * @return                              Structure with information about the parsed files. The 'success' files are
287         *                                              moved to the given directory
288         *
289         * Examples:
290         *                      [filename: 'abc.fasta', type: FASTA, numSequences: 190]
291         *                      [filename: 'cde.qual', type: QUAL, numSequences: 140, avgQuality: 29]
292         *
293         *                      [filename: 'test.zip', type: ZIP, extraFiles: [newfile1.xls, newfile2.xls, newfile3.xls]]
294         *
295         *                      [filename: 'testing.xls', type: 'unknown', message: 'Type not recognized']
296         *
297         */
298        public Map parseFile( File file, String filetype, Closure onProgress ) {
299                switch( filetype ) {
300                        case "excel":
301                                return parseExcel( file, onProgress );
302                        default:
303                                return [ success: false, type: filetype, message: 'Filetype could not be parsed.' ]
304                }
305        }
306       
307        /**
308        * Parses a given excel file with a match between filenames and samples
309        * @param file                   File to parse
310        * @param onProgress             Closure to execute when progress indicators should be updated.
311        *                                               Has 2 parameters: numFilesProcessed and numBytesProcessed that indicate the number
312        *                                               of files and bytes that have been processed in this file (so the first parameter should
313        *                                               only be 1 when the file is finished)
314        * @return                               List structure. The matches array contains an array of matches between filenames and sample(name)s.
315        *                                               The extension for all files are removed in the 'basename' parameter, in order to improve matching.
316        *                                               Examples:
317        *
318        *   [ success: true, filename: 'abc.xls', type: 'excel', matches: [ [ filename: 's1.qual', basename: 's1', sample: 'sample a' ], [ filename: 's9.fna', basename: 's9', sample: 'sample b' ] ]
319        *   [ success: false, filename: 'def.xls', type: 'excel', message: 'File is not a valid XLS file' ]
320        */
321   protected def parseExcel( File file, Closure onProgress ) {
322           long startTime = System.nanoTime();
323           log.trace "Start parsing XLS " + file.getName()
324
325           def matches = []
326
327           // Read excel file
328           def wb;
329           try {
330                   wb = open( file );
331           } catch( Exception e ) {
332                   // If an exception occurs, the file can't be opened. Return the error message
333                   return [ success: false, type: "excel", filename: file.getName(), message: "Excel file could not be opened or parsed."]
334           }
335
336           // Read all data into an array, and the header in a separate array
337           def header = readRow( wb, 0, 0 );
338           def data = readData( wb, 0, 1 );
339
340           // Update progress and return
341           onProgress( file.size(), 0 );
342           return [ success: true, type: "excel", filename: file.getName(), header: header, data: data ];
343   }
344
345       
346}
Note: See TracBrowser for help on using the repository browser.