source: trunk/grails-app/services/nl/tno/metagenomics/files/ExcelService.groovy @ 24

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

Improved export of fasta files and added properties to assaysamples
File size: 6.7 KB
Line 
1package nl.tno.metagenomics.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                                                if( c.getCellType() == Cell.CELL_TYPE_NUMERIC ) {
90                                                        row << numberformat.format( c.getNumericCellValue() );
91                                                } else {
92                                                        row << df.formatCellValue( c );
93                                                }
94                                        } else {
95                                                row << ""
96                                        }
97                                       
98                                }
99                               
100                                data << row;
101                        }
102                }
103               
104                return data
105        }
106       
107        /**
108         * Read all data from a row
109         * @param book
110         * @return
111         */
112        public ArrayList readRow( Workbook book, int sheetIndex = 0, int rowNum = 0 ) {
113                return readData( book, sheetIndex, rowNum, rowNum )[0];
114        }
115       
116        /**
117         * Write all given data to an excel sheet.
118         * @param data
119         */
120        public Workbook writeData( Workbook book, ArrayList data, int sheetIndex = 0, int startRow = 0, int startColumn = 0, CellStyle cellStyle = null ) {
121                if( book == null )
122                        throw new Exception( "No workbook given." );
123                       
124                if( sheetIndex >= book.getNumberOfSheets() )
125                        throw new Exception( "Sheet with index " + sheetIndex + " doesn't exist. Workbook has " + book.getNumberOfSheets() + " sheets." )
126       
127                Sheet sheet = book.getSheetAt(sheetIndex)
128               
129                // Loop through all rows
130                short rowNum = (short) startRow
131                data.each { ArrayList row -> 
132                        // Check whether the row already exists
133                        Row excelRow = sheet.getRow(rowNum)
134                       
135                        // If not, create a new one
136                        if( excelRow == null )
137                                excelRow = sheet.createRow(rowNum)
138                               
139                        short colNum = (short) startColumn
140                        for ( short i = 0; i < row.size(); i++ ) {
141                                Cell c = excelRow.createCell(colNum + i)
142                                c.setCellValue( row[i] )
143                               
144                                if( cellStyle != null )
145                                        c.setCellStyle(cellStyle)
146                        }
147                       
148                        rowNum++;
149                }
150               
151                return book
152        }
153       
154        public Workbook writeRow( Workbook book, ArrayList row, int sheetIndex = 0, int rowNum = 0, CellStyle cellStyle = null ) {
155                return writeData( book, [row], sheetIndex, rowNum, 0, cellStyle )
156        }
157       
158        /**
159         * Writes a header to the excel file (being bold and with a border-bottom)
160         * @param book
161         * @param header
162         * @return
163         */
164        public Workbook writeHeader(Workbook book, ArrayList headers, int sheetIndex = 0, int rowNum = 0) {
165                // Create a bold font and assign it to the row
166                CellStyle cs = book.createCellStyle();
167                cs.setBorderBottom(CellStyle.BORDER_THIN);
168                cs.setBottomBorderColor(IndexedColors.BLACK.getIndex());
169
170                Font boldFont = book.createFont()
171                boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
172                cs.setFont(boldFont)
173               
174                return writeRow( book, headers, sheetIndex, rowNum, cs);
175        }
176       
177        public Workbook writeDataWithHeader( Workbook book, ArrayList data, int sheetIndex = 0, int rowNum = 0, CellStyle cellStyle = null ) {
178                writeHeader( book, data.head(), sheetIndex, rowNum );
179                return writeData( book, data.tail(), sheetIndex, rowNum + 1, cellStyle );
180        }
181       
182        /**
183         * Resizes specified columns to match it contents
184         * @param book
185         * @return
186         */
187        public Workbook autoSizeColumns( Workbook book, int sheetIndex = 0, def columns = 0 ) {
188                if( book == null )
189                        throw new Exception( "No workbook given." );
190                       
191                if( sheetIndex >= book.getNumberOfSheets() )
192                        throw new Exception( "Sheet with index " + sheetIndex + " doesn't exist. Workbook has " + book.getNumberOfSheets() + " sheets." )
193       
194                Sheet sheet = book.getSheetAt(sheetIndex)
195
196                if( !( columns instanceof Collection ) ) 
197                        columns = [columns]
198               
199                columns.each { sheet.autoSizeColumn( (short) it ); }
200               
201                return book
202
203        }
204       
205       
206        /**
207         * Checks whether an excel row is empty
208         * @param row   Row from the excel sheet
209         * @return              True if all cells in this row are empty or the given row is null. False otherwise
210         */
211        def rowIsEmpty( Row excelRow ) {
212                if( !excelRow )
213                        return true;
214               
215                def df = new DataFormatter();
216                for( int i = excelRow.getFirstCellNum(); i < excelRow.getLastCellNum(); i++ ) {
217                        Cell cell = excelRow.getCell( i );
218                       
219                        try {
220                                def value = df.formatCellValue(cell)
221                                if( value )
222                                        return false
223                        } catch (NumberFormatException nfe) {
224                                // If the number can't be formatted, the row isn't empty
225                                return false;
226                        }
227                }
228               
229                return true;
230        }
231
232       
233        /**
234         * Return the given workbook for download
235         */
236        public void downloadFile( Workbook book, String filename, def response ) {
237                if( book == null )
238                        throw new Exception( "No workbook given." );
239
240                response.setHeader("Content-disposition", "attachment;filename=\"${filename}\"")
241                response.setContentType("application/octet-stream")
242                book.write(response.outputStream)
243                response.outputStream.close()
244
245        }
246}
Note: See TracBrowser for help on using the repository browser.