1
2
3
4
5
6 package org.weda.report.excel;
7
8 import java.io.BufferedInputStream;
9 import java.io.ByteArrayInputStream;
10 import java.io.ByteArrayOutputStream;
11 import java.io.FileInputStream;
12 import java.io.InputStream;
13 import java.sql.Timestamp;
14 import java.util.ArrayList;
15 import java.util.Date;
16 import java.util.List;
17 import javax.servlet.ServletContext;
18 import org.apache.poi.hssf.usermodel.HSSFCell;
19 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
20 import org.apache.poi.hssf.usermodel.HSSFDataFormat;
21 import org.apache.poi.hssf.usermodel.HSSFRow;
22 import org.apache.poi.hssf.usermodel.HSSFSheet;
23 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
24 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
25 import org.weda.converter.ValueTypeConverter;
26 import org.weda.enhance.InjectHivemindObject;
27 import org.weda.model.TableModel;
28 import org.weda.model.TableModelData;
29 import org.weda.model.TableModelRegistry;
30 import org.weda.property.PropertyDescriptor;
31 import org.weda.report.ReportException;
32 import org.weda.report.impl.AbstractReport;
33
34 /**
35 *
36 * @author Mikhail Titov
37 */
38 public class ExcelReport extends AbstractReport {
39 private List<ExcelReportDataInfo> dataInfos =
40 new ArrayList<ExcelReportDataInfo>(2);
41 private String templateFileName;
42
43 @InjectHivemindObject()
44 private static TableModelRegistry tableModelRegistry;
45 @InjectHivemindObject()
46 private static ValueTypeConverter converter;
47 @InjectHivemindObject("service:tapestry.globals.ServletContext")
48 private static ServletContext servletContext;
49 private boolean containsMarkers = false;
50
51 public ExcelReport(){
52 setMimeType("application/vnd.ms-excel");
53 }
54
55 public void addDataInfo(ExcelReportDataInfo dataInfo) {
56 dataInfos.add(dataInfo);
57 if (dataInfo.isUseMarkers())
58 containsMarkers = true;
59 }
60
61 public InputStream generate() throws ReportException {
62 try{
63 ReportEnv env = new ReportEnv();
64 env.workbook = createWorkbook();
65
66 for (ExcelReportDataInfo info: dataInfos){
67 env.currentDataInfo = info;
68 insertData(env);
69 }
70 ByteArrayOutputStream out = new ByteArrayOutputStream();
71 env.workbook.write(out);
72 ByteArrayInputStream result =
73 new ByteArrayInputStream(out.toByteArray());
74 return result;
75 }catch(Exception e){
76 throw new ReportException("Excel report generation error", e);
77 }
78 }
79
80 private HSSFWorkbook createWorkbook() throws Exception {
81 HSSFWorkbook workbook = null;
82 if (templateFileName==null)
83 workbook = new HSSFWorkbook();
84 else {
85 Exception ex=null;
86 InputStream is = null;
87 try{
88 is = servletContext.getResourceAsStream(templateFileName);
89 }catch(Exception e){
90 ex=e;
91 }
92 if (is==null)
93 try{
94 is = new FileInputStream(templateFileName);
95 }catch(Exception e){}
96 if (is==null)
97 throw new ReportException(
98 String.format(
99 "Can't load template file (%s) niether " +
100 "as file nor as web resource"
101 , templateFileName)
102 , ex);
103 try{
104 workbook = new HSSFWorkbook(
105 new POIFSFileSystem(
106 new BufferedInputStream(is)));
107 }finally{
108 is.close();
109 }
110 }
111 return workbook;
112 }
113
114 private void insertData(ReportEnv env)
115 throws Exception
116 {
117 TableModel tableModel =
118 tableModelRegistry.getTableModel(
119 env.currentDataInfo.getTableModelName());
120 setCurrentSheet(env);
121
122 if (env.currentDataInfo.isManageTableModel())
123 tableModel.open();
124 if (env.currentDataInfo.isUseMarkers())
125 updateData(env, tableModel);
126 else
127 addData(env, tableModel);
128 if (env.currentDataInfo.isManageTableModel())
129 tableModel.close();
130 }
131
132 private void updateData(ReportEnv env, TableModel tableModel)
133 throws Exception
134 {
135 TableModelData tableData = tableModel.getData();
136 int dataRow = -1;
137 if (env.currentDataInfo.isUseSelectedRows()){
138 if (tableData.getSelectedRowCount()>0)
139 dataRow = tableData.getSelectedRow();
140 }else{
141 if (tableData.getRowCount()>0)
142 dataRow = 0;
143 }
144 if (dataRow==-1)
145 return;
146 for (int c=0; c<tableModel.getColumnCount(); ++c){
147 CellPosition pos =
148 env.markersManager.getMarkedCellPosition(
149 env.currentSheet, tableModel.getName(), c);
150 if (pos!=null){
151 int r = env.rowsShiftManager.translate(
152 env.currentSheet, pos.getRow());
153 HSSFRow row = env.currentSheet.getRow(r);
154 HSSFCell cell = row.getCell((short)pos.getCol());
155 if (cell==null)
156 cell = row.createCell((short)pos.getCol());
157 HSSFCellStyle style = cell.getCellStyle();
158 List<PropertyDescriptor> columns =
159 tableModel.getColumnDescriptors();
160
161 setCellValue(
162 env.workbook
163 , cell
164 , tableData.getValueAt(dataRow, c)
165 , columns.get(c)
166 , style);
167 }
168 }
169 }
170
171 private void addData(ReportEnv env, TableModel tableModel) throws Exception{
172 TableModelData tableData = tableModel.getData();
173 HSSFCellStyle[] styles = extractStyles(env, tableModel);
174 int startFromRow =
175 env.rowsShiftManager.translate(
176 env.currentSheet, env.currentDataInfo.getStartFromRow());
177 env.rowsShiftManager.shiftRows(
178 env.currentSheet
179 , env.currentDataInfo.getStartFromRow()+1
180 , tableData.getRowCount()-1);
181 List<PropertyDescriptor> columns = tableModel.getColumnDescriptors();
182
183 if (env.currentDataInfo.isCreateTitleRow()){
184 HSSFRow row = env.currentSheet.createRow(startFromRow++);
185 for (int c=0; c<tableModel.getColumnCount(); ++c){
186 short ec = (short)(c+env.currentDataInfo.getStartFromCol());
187 HSSFCell cell = row.createCell(ec);
188 setCellValue(
189 env.workbook, cell, columns.get(c).getDisplayName()
190 , null, null);
191 }
192 }
193
194 for (int r=0; r<tableData.getRowCount(); ++r){
195 int er = r+startFromRow;
196 HSSFRow row = env.currentSheet.createRow(er);
197 for (int c=0; c<tableModel.getColumnCount(); ++c){
198 short ec = (short)(c+env.currentDataInfo.getStartFromCol());
199 HSSFCell cell = row.createCell(ec);
200 HSSFCellStyle style = styles==null? null : styles[c];
201 setCellValue(
202 env.workbook
203 , cell
204 , tableData.getValueAt(r, c)
205 , columns.get(c), style);
206 }
207 }
208 }
209
210 private void setCurrentSheet(ReportEnv env)
211 throws ReportException
212 {
213 HSSFSheet sheet = null;
214 if (env.currentDataInfo.getSheetName()!=null){
215 sheet = env.workbook.getSheet(env.currentDataInfo.getSheetName());
216 if (sheet == null) {
217 int num = 1;
218 int sheetIndex = -1;
219 while ( (sheetIndex=env.workbook.getSheetIndex("sheet_"+num))>=0 )
220 {
221 num++;
222 }
223 String tempSheetName = "sheet_"+num;
224 sheet = env.workbook.createSheet(tempSheetName);
225 sheetIndex = env.workbook.getSheetIndex(tempSheetName);
226 env.workbook.setSheetName(
227 sheetIndex
228 , env.currentDataInfo.getSheetName()
229 , HSSFCell.ENCODING_UTF_16);
230 }
231 }else if (env.currentDataInfo.getSheetPosition()!=null){
232 if (templateFileName==null)
233 throw new ReportException(
234 "Attribute sheetPosition can use only with setted " +
235 "templateFile attribute");
236 sheet = env.workbook.getSheetAt(
237 env.currentDataInfo.getSheetPosition());
238 if (sheet == null)
239 throw new ReportException(
240 String.format(
241 "No sheet found at position (%d) in template " +
242 "excel file (%s)"
243 , env.currentDataInfo.getSheetPosition()
244 , templateFileName));
245 }else
246 throw new ReportException(
247 "The excel sheet not setted. You must setup " +
248 "sheetPosition or sheetName attributes to define sheet.");
249
250 env.currentSheet = sheet;
251 if (containsMarkers)
252 env.markersManager.analyze(sheet);
253 }
254
255 private HSSFCellStyle[] extractStyles(ReportEnv env, TableModel model)
256 throws Exception
257 {
258 HSSFCellStyle[] styles = null;
259 int tRow =
260 env.rowsShiftManager.translate(
261 env.currentSheet, env.currentDataInfo.getStartFromRow());
262 if ( templateFileName != null
263 && env.currentSheet.getLastRowNum()>=tRow)
264 {
265 styles = new HSSFCellStyle[model.getColumnCount()];
266 HSSFRow row = env.currentSheet.getRow(tRow);
267 short fromCol = env.currentDataInfo.getStartFromCol();
268 int i=0;
269 for (int col = fromCol; col<fromCol+model.getColumnCount(); ++col){
270 HSSFCell cell = row.getCell((short)col);
271 if (cell!=null)
272 styles[i] = cell.getCellStyle();
273 ++i;
274 }
275 }
276 return styles;
277 }
278
279 private void shiftRows(HSSFSheet sheet, int fromRow, int rowCount)
280 throws Exception
281 {
282 if (templateFileName != null && fromRow<=sheet.getLastRowNum())
283 sheet.shiftRows(
284 fromRow, sheet.getLastRowNum(), rowCount-1, true, false);
285 }
286
287 private void setCellValue(
288 HSSFWorkbook workbook
289 , HSSFCell cell
290 , Object value, PropertyDescriptor desc, HSSFCellStyle style)
291 throws Exception
292 {
293 cell.setEncoding(HSSFCell.ENCODING_UTF_16);
294 if (value==null)
295 cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
296 else if (value instanceof java.lang.String)
297 cell.setCellValue((String)value);
298 else if (value instanceof Boolean)
299 cell.setCellValue((Boolean)value);
300 else if (value instanceof Number) {
301 if (style==null)
302 style = workbook.createCellStyle();
303 if ( value instanceof Float
304 || value instanceof Double)
305 {
306 style.setDataFormat(
307 HSSFDataFormat.getBuiltinFormat("0.00"));
308 }else
309 style.setDataFormat(
310 HSSFDataFormat.getBuiltinFormat("0"));
311 cell.setCellValue(((Number)value).doubleValue());
312 }else if (value instanceof Date) {
313 if (style==null)
314 style = workbook.createCellStyle();
315 if ( Date.class.equals(value.getClass())
316 || Timestamp.class.equals(value.getClass()))
317 {
318 style.setDataFormat(
319 HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
320 }else if (java.sql.Date.class.equals(value.getClass())){
321 style.setDataFormat(
322 HSSFDataFormat.getBuiltinFormat("m/d/yy"));
323 }else if (java.sql.Time.class.equals(value.getClass()))
324 style.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
325 else
326 style.setDataFormat(
327 HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
328 cell.setCellValue((Date)value);
329 }else {
330 cell.setCellValue(
331 (String)converter.convert(
332 String.class, value, desc.getPattern()));
333 }
334 if (style!=null)
335 cell.setCellStyle(style);
336 }
337
338 public void setDataInfos(List<ExcelReportDataInfo> dataInfos) {
339 this.dataInfos = dataInfos;
340 }
341
342 /**Метод возвращает путь до файла шаблона.
343 */
344 public String getTemplateFileName() {
345 return templateFileName;
346 }
347
348 public void setTemplateFileName(String templateFileName) {
349 this.templateFileName = templateFileName;
350 }
351
352 public void release() throws ReportException {
353 }
354 }