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

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