|
1 package com.calipso.reportgenerator.reportmanager;
2
3 import com.calipso.reportgenerator.reportcalculator.IDataSource;
4 import com.calipso.reportgenerator.common.InfoException;
5 import java.io.*;
6 import java.util.ArrayList  ;
7 import java.util.List ;
8 import java.util.Vector ;
9 import java.util.Enumeration ;
10
11 import org.apache.poi.hssf.usermodel.HSSFCell;
12 import org.apache.poi.hssf.usermodel.HSSFRow;
13 import org.apache.poi.hssf.usermodel.HSSFSheet;
14 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
15 import com.calipso.reportgenerator.common.*;
16 import com.calipso.reportgenerator.reportcalculator.*;
17 import com.calipso.reportgenerator.reportdefinitions.types.ReportDataType;
18 import com.calipso.common.DateEx;
19
20
23
24 public class ExcelReportDataSource extends ReportDataSource {
25
26 private static final int STRING = 1;
27 private static final int NUMERIC = 0;
28 private IDataSource dataSource;
29 private org.apache.poi.poifs.filesystem.POIFSFileSystem fileSystem;
30 private String sheetName;
31 private String dataInitialCell;
32 private String dataEndingCell;
33 private ExcelSheetPosition initialPosition;
34 private ExcelSheetPosition endingPosition;
35 private int dimensionsCount;
36
37
42 public ExcelReportDataSource(ReportSpec reportSpec, ReportDataSourceSpec reportDataSourceSpec, ReportGeneratorConfiguration configuration) throws InfoException {
43 super(reportSpec, reportDataSourceSpec);
44 setGeneratorConfiguration(configuration);
45 File file = new File(reportDataSourceSpec.getExpression());
47 sheetName = reportDataSourceSpec.getSheetName();
48 dataEndingCell = reportDataSourceSpec.getDataEndingCell();
49 dataInitialCell = reportDataSourceSpec.getDataInitialCell();
50 try {
51 InputStream inputStream = new FileInputStream(file);
52 fileSystem = new org.apache.poi.poifs.filesystem.POIFSFileSystem(inputStream);
53 } catch (Exception e) {
54 throw new InfoException(LanguageTraslator.traslate("473"),e);
55 }
56 }
57
58 public IDataSource getDataSource(Matrix matrix) throws InfoException {
59
61 fillFromFile(matrix);
62
63 return dataSource;
64 }
65
66 private void fillFromFile(Matrix matrix) throws InfoException{
67 try {
68 HSSFWorkbook wb = new HSSFWorkbook(fileSystem);
69 HSSFSheet sheet;
70 if(sheetName!=null && !sheetName.equalsIgnoreCase("")){
71 sheet = wb.getSheet(sheetName);
72 }else{
73 sheet = wb.getSheetAt(0);
74 }
75 createInitialPositions(sheet);
76 for(int i = initialPosition.getRow() ; i <= endingPosition.getRow() ; i++) {
78
80 Object [] row = getItemVector(sheet.getRow(i));
81 try {
82 if ((getFilter()== null)||((getFilter()!= null) && (getFilter().matches(row)))) {
83 matrix.add(row);
85 }
86 } catch (InfoException e) {
87 throw new InfoException(LanguageTraslator.traslate("386"), e);
88 }
89 }
90 } catch (Exception e) {
91 throw new InfoException(LanguageTraslator.traslate("316"),e);
92 } catch(OutOfMemoryError e) {
93 throw new InfoException(LanguageTraslator.traslate("326"), e);
94 }
95 }
96
97 private void createInitialPositions(HSSFSheet sheet) throws InfoException {
98 if(dataInitialCell==null || dataInitialCell.equalsIgnoreCase("")){
99 int initialRow = sheet.getFirstRowNum();
100 short initialColumn = sheet.getRow(initialRow).getFirstCellNum();
101 initialPosition = new ExcelSheetPosition(initialRow, initialColumn);
102 }else{
103 initialPosition = new ExcelSheetPosition(getDataInitialCell());
104 initialPosition.setRow(initialPosition.getRow()-1);
105 }
106 if(dataEndingCell==null || dataEndingCell.equalsIgnoreCase("")){
107 int finalRow = sheet.getLastRowNum();
108 short finalColumn = (short)(sheet.getRow(finalRow).getLastCellNum()-1);
109 endingPosition = new ExcelSheetPosition(finalRow, finalColumn);
110 }else{
111 endingPosition = new ExcelSheetPosition(getDataEndingCell());
112 endingPosition.setRow(endingPosition.getRow()-1);
113 }
114 }
115
116 private String getDataEndingCell() {
117 return dataEndingCell;
118 }
119
120 private String getDataInitialCell() {
121 return dataInitialCell;
122 }
123
124
196
197 private Object [] getItemVector(HSSFRow row) throws InfoException {
198 int dimCount = getReportSpec().getDimensionsByIndex().size();
199 int colNum = dimCount + getReportSpec().getMetricsByIndex().size();
200 Object [] collection = new Object [colNum];
201 int j = initialPosition.getColumn();
203 for (int i = 0; i < (colNum); i++) {
204 if(i < dimCount){
205 ReportDimensionSpec dimension = getReportSpec().getDimensionFromIndex(i);
207 if(dimension.getCalculated()){
208 collection[i] = dimension.getValue(collection, getReportDataSourceSpec());
209 }else{
210 HSSFCell cell = row.getCell((short)j);
211 collection[i] = getValueForDimension(getValueFromCell(cell), dimension, collection, i);
212 j++;
213 }
214 }else{
215 ReportMetricSpec metric = getReportSpec().getMetricFromIndex(i - dimCount);
217 if(metric.getCalculated()){
218 collection[i] = metric.getValue(collection);
219 }else{
220 HSSFCell cell = row.getCell((short)j);
221 collection[i] = getValueForMetric(getValueFromCell(cell), metric, collection, i);
222 j++;
223 }
224 }
225 }
226 return collection;
227 }
228
229 public int getFilterVarMode() {
230 return ReportFilterBuilder.VARMODE_DATAINDEX;
231 }
232
233 private Object getValueFromCell(HSSFCell cell) {
234 if (cell != null) {
235 switch(cell.getCellType()) {
236 case STRING:
237 return (cell.getStringCellValue());
238 case NUMERIC:
239 Double aDouble = new Double (cell.getNumericCellValue());
240 if ( aDouble.doubleValue() == aDouble.intValue()) {
241 return (new Integer (aDouble.intValue()));
242 } else {
243 return (aDouble);
244 }
245 default:
246 return ("");
247 }
248 }else{
249 return ("");
250 }
251 }
252
253 }
254 |
Popular Tags |
Java API By Example, From Geeks To Geeks.