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

Last change on this file since 50 was 50, checked in by robert@…, 9 years ago
  • Added links to remove samples from a run
  • Added synchronization of authorization for all studies, if a user logs in
  • Improved excel import to handle Error-values in a cell
File size: 6.9 KB
Line 
1package nl.tno.massSequencing.files
2
3import java.text.DecimalFormat
4import java.text.Format
5import java.text.NumberFormat
6import org.apache.poi.hssf.usermodel.*
7import org.apache.poi.ss.usermodel.*
8
9/**
10 * Convenience methods for reading and writing excel files
11 * @author      Robert Horlings robert@isdat.nl
12 * @see         apache poi api
13 *
14 */
15class ExcelService {
16    static transactional = true
17       
18        /**
19         * Create a new excel workbook, containing 1 sheet
20         * @return
21         */
22        public Workbook create() {
23                Workbook book = new HSSFWorkbook()
24                book.createSheet()
25               
26                return book
27        }
28       
29        public Workbook open( String filename ) {
30                return open( new File( filename ) );
31        }
32       
33        public Workbook open( File file ) {
34                return open( new FileInputStream( file ) );     
35        }
36       
37        public Workbook open( InputStream file ) {
38                return WorkbookFactory.create(file);
39        }
40       
41        /**
42         * Reads specific rows from a given sheet of a file
43         *
44         */
45        public ArrayList readData( Workbook book, int sheetIndex = 0, int startRow = -1, int endRow = -1, int maxRows = 0, int startColumn = 0, int endColumn = 0 ) {
46                if( book == null )
47                        throw new Exception( "No workbook given." );
48                       
49                if( sheetIndex >= book.getNumberOfSheets() )
50                        throw new Exception( "Sheet with index " + sheetIndex + " doesn't exist. Workbook has " + book.getNumberOfSheets() + " sheets." )
51
52
53                Sheet sheet = book.getSheetAt(sheetIndex)
54               
55                // Determine the start and end row if none is given
56                if( startRow == null || startRow == -1 )
57                        startRow = sheet.getFirstRowNum();
58
59                if( endRow == null || endRow == -1 ) {
60                        endRow = sheet.getLastRowNum();
61                }
62
63                // Check whether a max # rows is given
64                if( maxRows > 0 ) {
65                        endRow = Math.min( endRow, startRow + maxRows - 1 );
66                }
67               
68                if( startRow < sheet.getFirstRowNum() || startRow > sheet.getLastRowNum() )
69                        throw new Exception( "Can't return start row " + startRow + " since the row doesn't exist." )
70                       
71                if( endRow < sheet.getFirstRowNum() || endRow > sheet.getLastRowNum() )
72                        throw new Exception( "Can't return end row " + endRow + " since the row doesn't exist.")
73
74                // Now loop through all rows, retrieving data from the excel file
75                def df = new DataFormatter()
76                DecimalFormat numberformat =  new DecimalFormat( "0" );
77               
78                ArrayList data = []
79               
80                for( def rowNum = startRow; rowNum <= endRow; rowNum++ ) {
81                        Row excelRow = sheet.getRow( rowNum );
82                       
83                        if( !rowIsEmpty( excelRow ) ) {
84                                ArrayList row = []
85                               
86                                for( def colNum = 0; colNum < excelRow.getLastCellNum(); colNum++ ) {
87                                        Cell c = excelRow.getCell( colNum );
88                                        if( c ) {
89                                                switch( c.getCellType() ) {
90                                                        case Cell.CELL_TYPE_NUMERIC:
91                                                                row << numberformat.format( c.getNumericCellValue() );
92                                                                break;
93                                                        case Cell.CELL_TYPE_BLANK:
94                                                        case Cell.CELL_TYPE_BOOLEAN:
95                                                        case Cell.CELL_TYPE_STRING:
96                                                        case Cell.CELL_TYPE_FORMULA:
97                                                                row << df.formatCellValue( c );
98                                                                break
99                                                        case Cell.CELL_TYPE_ERROR:
100                                                                row << "Err";
101                                                                break;
102                                                }
103                                        } else {
104                                                row << ""
105                                        }
106                                       
107                                }
108                               
109                                data << row;
110                        }
111                }
112               
113                return data
114        }
115       
116        /**
117         * Read all data from a row
118         * @param book
119         * @return
120         */
121        public ArrayList readRow( Workbook book, int sheetIndex = 0, int rowNum = 0 ) {
122                return readData( book, sheetIndex, rowNum, rowNum )[0];
123        }
124       
125        /**
126         * Write all given data to an excel sheet.
127         * @param data
128         */
129        public Workbook writeData( Workbook book, ArrayList data, int sheetIndex = 0, int startRow = 0, int startColumn = 0, CellStyle cellStyle = null ) {
130                if( book == null )
131                        throw new Exception( "No workbook given." );
132                       
133                if( sheetIndex >= book.getNumberOfSheets() )
134                        throw new Exception( "Sheet with index " + sheetIndex + " doesn't exist. Workbook has " + book.getNumberOfSheets() + " sheets." )
135       
136                Sheet sheet = book.getSheetAt(sheetIndex)
137               
138                // Loop through all rows
139                short rowNum = (short) startRow
140                data.each { ArrayList row -> 
141                        // Check whether the row already exists
142                        Row excelRow = sheet.getRow(rowNum)
143                       
144                        // If not, create a new one
145                        if( excelRow == null )
146                                excelRow = sheet.createRow(rowNum)
147                               
148                        short colNum = (short) startColumn
149                        for ( short i = 0; i < row.size(); i++ ) {
150                                Cell c = excelRow.createCell(colNum + i)
151                                c.setCellValue( row[i] )
152                               
153                                if( cellStyle != null )
154                                        c.setCellStyle(cellStyle)
155                        }
156                       
157                        rowNum++;
158                }
159               
160                return book
161        }
162       
163        public Workbook writeRow( Workbook book, ArrayList row, int sheetIndex = 0, int rowNum = 0, CellStyle cellStyle = null ) {
164                return writeData( book, [row], sheetIndex, rowNum, 0, cellStyle )
165        }
166       
167        /**
168         * Writes a header to the excel file (being bold and with a border-bottom)
169         * @param book
170         * @param header
171         * @return
172         */
173        public Workbook writeHeader(Workbook book, ArrayList headers, int sheetIndex = 0, int rowNum = 0) {
174                // Create a bold font and assign it to the row
175                CellStyle cs = book.createCellStyle();
176                cs.setBorderBottom(CellStyle.BORDER_THIN);
177                cs.setBottomBorderColor(IndexedColors.BLACK.getIndex());
178
179                Font boldFont = book.createFont()
180                boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
181                cs.setFont(boldFont)
182               
183                return writeRow( book, headers, sheetIndex, rowNum, cs);
184        }
185       
186        public Workbook writeDataWithHeader( Workbook book, ArrayList data, int sheetIndex = 0, int rowNum = 0, CellStyle cellStyle = null ) {
187                writeHeader( book, data.head(), sheetIndex, rowNum );
188                return writeData( book, data.tail(), sheetIndex, rowNum + 1, cellStyle );
189        }
190       
191        /**
192         * Resizes specified columns to match it contents
193         * @param book
194         * @return
195         */
196        public Workbook autoSizeColumns( Workbook book, int sheetIndex = 0, def columns = 0 ) {
197                if( book == null )
198                        throw new Exception( "No workbook given." );
199                       
200                if( sheetIndex >= book.getNumberOfSheets() )
201                        throw new Exception( "Sheet with index " + sheetIndex + " doesn't exist. Workbook has " + book.getNumberOfSheets() + " sheets." )
202       
203                Sheet sheet = book.getSheetAt(sheetIndex)
204
205                if( !( columns instanceof Collection ) ) 
206                        columns = [columns]
207               
208                columns.each { sheet.autoSizeColumn( (short) it ); }
209               
210                return book
211
212        }
213       
214       
215        /**
216         * Checks whether an excel row is empty
217         * @param row   Row from the excel sheet
218         * @return              True if all cells in this row are empty or the given row is null. False otherwise
219         */
220        def rowIsEmpty( Row excelRow ) {
221                if( !excelRow )
222                        return true;
223               
224                def df = new DataFormatter();
225                for( int i = excelRow.getFirstCellNum(); i < excelRow.getLastCellNum(); i++ ) {
226                        Cell cell = excelRow.getCell( i );
227                       
228                        try {
229                                def value = df.formatCellValue(cell)
230                                if( value )
231                                        return false
232                        } catch (NumberFormatException nfe) {
233                                // If the number can't be formatted, the row isn't empty
234                                return false;
235                        }
236                }
237               
238                return true;
239        }
240
241       
242        /**
243         * Return the given workbook for download
244         */
245        public void downloadFile( Workbook book, String filename, def response ) {
246                if( book == null )
247                        throw new Exception( "No workbook given." );
248
249                response.setHeader("Content-disposition", "attachment;filename=\"${filename}\"")
250                response.setContentType("application/octet-stream")
251                book.write(response.outputStream)
252                response.outputStream.close()
253
254        }
255}
Note: See TracBrowser for help on using the repository browser.