001 package com.sptci.rwt;
002
003 import java.util.List;
004
005 import org.apache.poi.hssf.usermodel.HSSFCell;
006 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
007 import org.apache.poi.hssf.usermodel.HSSFFont;
008 import org.apache.poi.hssf.usermodel.HSSFRichTextString;
009 import org.apache.poi.hssf.usermodel.HSSFRow;
010 import org.apache.poi.hssf.usermodel.HSSFSheet;
011 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
012
013 import org.apache.poi.hssf.util.Region;
014
015 /**
016 * An Excel workbook generator for data contained in a {@link Rows}
017 * object. This uses the
018 * <a href='http://poi.apache.org/hssf/index.html'>Apache POI HSSF</a>
019 * library for creating Excel workbooks.
020 *
021 * <p>© Copyright 2007 <a href='http://sptci.com/' target='_new'>Sans Pareil Technologies, Inc.</a></p>
022 * @author Rakesh Vidyadharan 2007-10-09
023 * @version $Id: ExcelGenerator.java 4123 2008-05-25 21:49:01Z rakesh $
024 */
025 class ExcelGenerator
026 {
027 /**
028 * Create a new workbook that holds the data in {@link Rows}. Also
029 * display <code>query</code> to indicate the statement that was used to
030 * generate the results.
031 *
032 * @see #createQuery
033 * @see #createHeader
034 * @see #createRow
035 * @param query The SQL query to execute.
036 * @param rows The list of rows data set to export.
037 * @return The work book that contains the data in <code>rows</code>.
038 */
039 HSSFWorkbook generate( final String query, final List<Rows> rows )
040 {
041 final HSSFWorkbook workbook = new HSSFWorkbook();
042
043 int count = 0;
044 for ( Rows result : rows )
045 {
046 final HSSFSheet sheet =
047 workbook.createSheet( "Query Results: " + ++count );
048 boolean first = true;
049 short index = 0;
050 for ( Row row : result.getRows() )
051 {
052 if ( first )
053 {
054 createQuery( query, workbook, sheet, index++,
055 (short) row.getColumns().size() );
056 createHeader( row, workbook, sheet, index++ );
057 first = false;
058 }
059
060 createRow( row, workbook, sheet, index++ );
061 }
062 }
063
064 return workbook;
065 }
066
067 /**
068 * Create the cells used to display the SQL statement that was executed
069 * to generate the results.
070 *
071 * @param query The SQL statement used to generate the results.
072 * @param workbook The excel workbook.
073 * @param sheet The excel work sheet.
074 * @param index The row number for the header.
075 * @param columns The number of columns this row spans.
076 */
077 private void createQuery( final String query, final HSSFWorkbook workbook,
078 final HSSFSheet sheet, final short index, final short columns )
079 {
080 HSSFFont font = workbook.createFont();
081 font.setFontName( "Helvetica" );
082 font.setItalic( true );
083
084 HSSFCellStyle style = workbook.createCellStyle();
085 style.setFont( font );
086
087 HSSFRow sheetRow = sheet.createRow( index );
088 HSSFCell cell = sheetRow.createCell( (short) 0 );
089 cell.setCellStyle( style );
090 cell.setCellValue( new HSSFRichTextString( query ) );
091 sheet.addMergedRegion( new Region( index, (short) 0, index, columns ) );
092 }
093
094 /**
095 * Create the header row for the excel sheet using the {@link
096 * com.sptci.rwt.Column#name} field.
097 *
098 * @param row The row instance from which column names are extracted.
099 * @param workbook The excel workbook.
100 * @param sheet The sheet in which the header row is to be inserted.
101 * @param index The row number for the header.
102 */
103 private void createHeader( final Row row, final HSSFWorkbook workbook,
104 final HSSFSheet sheet, final short index )
105 {
106 HSSFFont font = workbook.createFont();
107 font.setBoldweight( HSSFFont.BOLDWEIGHT_BOLD );
108 font.setFontName( "Helvetica" );
109
110 HSSFCellStyle style = workbook.createCellStyle();
111 style.setFont( font );
112
113 HSSFRow sheetRow = sheet.createRow( index );
114 short columnIndex = 0;
115 for ( Column column : row.getColumns() )
116 {
117 HSSFCell cell = sheetRow.createCell( columnIndex++ );
118 cell.setCellStyle( style );
119 cell.setCellValue( new HSSFRichTextString( column.getName() ) );
120 }
121 }
122
123 /**
124 * Create a header row for the excel sheet using the {@link
125 * com.sptci.rwt.Column#name} field.
126 *
127 * @param row The row instance from which column names are extracted.
128 * @param workbook The excel workbook.
129 * @param sheet The sheet in which the header row is to be inserted.
130 * @param index The row index to set.
131 */
132 private void createRow( final Row row, final HSSFWorkbook workbook,
133 final HSSFSheet sheet, final short index )
134 {
135 HSSFFont font = workbook.createFont();
136 font.setFontName( "Helvetica" );
137
138 HSSFCellStyle style = workbook.createCellStyle();
139 style.setFont( font );
140
141 HSSFRow sheetRow = sheet.createRow( index );
142 short columnIndex = 0;
143 for ( Column column : row.getColumns() )
144 {
145 HSSFCell cell = sheetRow.createCell( columnIndex++ );
146 cell.setCellStyle( style );
147 if ( column.getContent() != null )
148 {
149 cell.setCellValue(
150 new HSSFRichTextString( column.getContent().toString() ) );
151 }
152 else
153 {
154 cell.setCellValue( new HSSFRichTextString( "" ) );
155 }
156 }
157 }
158 }