KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > calipso > reportgenerator > userinterface > ExcelFormatSerializer


1 package com.calipso.reportgenerator.userinterface;
2
3 import org.apache.poi.hssf.usermodel.HSSFSheet;
4 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
5 import org.apache.poi.hssf.usermodel.HSSFRow;
6 import org.apache.poi.hssf.usermodel.HSSFCell;
7 import javax.swing.*;
8 import javax.swing.table.TableModel JavaDoc;
9 import java.io.FileOutputStream JavaDoc;
10 import java.io.IOException JavaDoc;
11 import java.io.FileNotFoundException JavaDoc;
12 import java.util.*;
13
14 import com.calipso.reportgenerator.common.*;
15 import com.calipso.reportgenerator.reportcalculator.SharedFloat;
16
17 /**
18  *
19  */

20
21 public class ExcelFormatSerializer {
22
23   private JTable dataTable;
24   private HeaderTableModel columnHeaderTableModel;
25   private HeaderTableModel rowHeaderTableModel;
26   private ReportResult reportResult;
27   private int metricsCount;
28   private ArrayList rows;
29   private ArrayList columns;
30
31   public ExcelFormatSerializer(PivotTable pivotTable) {
32     initialize(pivotTable);
33   }
34
35   private void initialize(PivotTable pivotTable) {
36     this.rowHeaderTableModel = (HeaderTableModel) pivotTable.getRowHeaderjTable().getModel();
37     this.columnHeaderTableModel = (HeaderTableModel) pivotTable.getColumnHeaderjTable().getModel();
38     this.dataTable = pivotTable.getDatajTable();
39     this.metricsCount = pivotTable.getPivotTableFrame().getReportResult().getReportQuery().getVisibleMetrics().size();
40     this.reportResult = pivotTable.getReportResult();
41   }
42
43   /**
44    * Crea un archivo excel con los datos en el path especificado
45    * @param path
46    */

47   public void serializeTo(String JavaDoc path) throws IOException JavaDoc {
48     HSSFWorkbook wb = new HSSFWorkbook();
49     HSSFSheet sheet = wb.createSheet(reportResult.getReportSpec().getTitle().trim());
50     int sum = 0;
51     int index = 0;
52     Vector cutCols = getNonGroupingDimensionIndexesFromModel();
53     fillFirstRow(sheet);
54     int additionalRows = getAdditionalRows();
55     int colCount = rowHeaderTableModel.getColumnCount();
56     if (colCount==0) colCount ++;
57     for(int i = 0 ; i < rowHeaderTableModel.getRowCount() ; i++) {
58       if(isValid(rowHeaderTableModel.getValueFrom(i))) {
59         Object JavaDoc [] groupingDimensions = rowHeaderTableModel.getValueFrom(i);
60         if(cutCols.size() > 0) {
61           if (isValid(groupingDimensions)){
62             HSSFRow row = sheet.createRow((short)(index+1));
63             fillCellFrom(groupingDimensions, row, 0);
64             index++;
65             for(int j = 0; j < columnHeaderTableModel.getColumnCount() ; j++) {
66               fillCellFrom(i, j, row, colCount+additionalRows+j);
67             }
68           }
69         } else {
70           index = 0;
71           if (isValid(groupingDimensions)){
72             HSSFRow row = sheet.createRow((short)(sum+1));
73             fillCellFrom(groupingDimensions, row, index);
74             index += groupingDimensions.length+additionalRows;
75             if (index==0) index ++;
76             for(int z = 0 ; z < metricsCount ; z++) {
77               fillCellFrom(i, z, row, index);
78               index++;
79             }
80             sum++;
81           }
82         }
83       }
84     }
85     serializeFrom(wb, new FileOutputStream JavaDoc(path));
86   }
87
88   private Object JavaDoc[] getNonGroupingDimensionsFrom(Object JavaDoc [] elements) {
89     int length = elements.length;;
90     if(columnHeaderTableModel.getVisibleMetricsCount() > 1){
91       length--;
92     }
93     Object JavaDoc [] nonGroupingDimensions = new Object JavaDoc[length];
94     for(int i = 0 ; i < length ; i++) {
95       nonGroupingDimensions [i] = elements [i];
96     }
97     return nonGroupingDimensions;
98   }
99
100   private Vector getNonGroupingDimensionIndexesFromModel() {
101     Vector cutCols = new Vector();
102     int cant = columnHeaderTableModel.getDimensionCount();
103       for(int i = 0 ; i < columnHeaderTableModel.getColumnCount() ; i++) {
104         Object JavaDoc [] values = columnHeaderTableModel.getValueFrom(i);
105         if(isValid(values)) {
106           boolean isDistinct = false;
107           for(int j = 0; j < cant/* - 1*/; j++){
108             if(i == 0 || (columnHeaderTableModel.getValueFrom(i)[j] != columnHeaderTableModel.getValueFrom(i-1)[j])){
109               isDistinct = true;
110             }
111           }
112           if(isDistinct){
113             cutCols.add(new Integer JavaDoc(/*changeIndex*/i));
114           }
115         }
116       }
117     return cutCols;
118   }
119
120   private int getChangeIndex(Object JavaDoc [] values, int i) {
121     int changeIndex = -1;
122     for(; i < columnHeaderTableModel.getColumnCount() ; i++) {
123       Object JavaDoc [] current = columnHeaderTableModel.getValueFrom(i);
124       if(isValid(current)) {
125          for(int j = 0 ; j < current.length ; j++) {
126          if(j != current.length - 1) {
127           if(current [j] != values [j]) {
128             changeIndex = i;
129               break;
130             }
131           }
132         }
133       }
134       if(changeIndex != -1) {
135         break;
136       }
137     }
138     return changeIndex;
139   }
140
141   private void fillFirstRow(HSSFSheet sheet) {
142     HSSFRow row = sheet.createRow((short)0);
143     int i = 0;
144     int ii = 0;
145
146
147     for(; getRows().size()>i ; i++) {
148       row.createCell((short)i).setCellValue(getRows().get(i).toString());
149     }
150     if (i==0){
151       i++;
152     }
153     String JavaDoc caption="";
154     for(; columnHeaderTableModel.getColumnCount()>ii ; ii++) {
155       Object JavaDoc[] values = columnHeaderTableModel.getValueFrom(ii);
156       for (int iii=0;values.length>iii;iii++){
157         if (values[iii]== null){
158           caption = caption + "Total";
159         } else{
160           caption = caption + values[iii].toString();
161         }
162         if (iii<(values.length-1)){
163           caption = caption + "\\";
164         }
165
166       }
167       if ((caption.equalsIgnoreCase(""))&&(reportResult.getReportQuery().getVisibleMetrics().size()==1)){
168         String JavaDoc metricName = ((QueryMetric)reportResult.getReportQuery().getVisibleMetrics().get(0)).getName();
169         caption = ((ReportMetricSpec)reportResult.getReportSpec().getMetricFromName(metricName)).getCaption();
170       }
171       row.createCell((short)(i+ii)).setCellValue(caption);
172       caption = "";
173     }
174   }
175
176   private int getAdditionalRows(){
177     int index = 0;
178     Iterator rowDimensionsIterator = reportResult.getReportQuery().getRowDimensions().iterator();
179     int i=0;
180     for(; rowDimensionsIterator.hasNext() ; i++) {
181       QueryDimension queryDimension = (QueryDimension) rowDimensionsIterator.next();
182       String JavaDoc caption = (reportResult.getReportSpec().getDimensionFromName(queryDimension.getName())).getCaption();
183       if (caption.indexOf(';')>=0){
184         String JavaDoc rowName = caption;
185         int indexOf = rowName.indexOf(';');
186         while (indexOf>=0){
187           rowName = rowName.substring(indexOf+1, rowName.length());
188           indexOf = rowName.indexOf(';');
189           index = index +1;
190         }
191         //index = index -1;
192
if (!rowName.equalsIgnoreCase("")){
193 // index = index +1;
194
}
195       }
196     }
197     return index;
198   }
199
200     private ArrayList getRows() {
201       if (rows != null){
202         return rows;
203       }
204       rows = new ArrayList();
205       Iterator rowDimensionsIterator = reportResult.getReportQuery().getRowDimensions().iterator();
206       int i=0;
207       for(; rowDimensionsIterator.hasNext() ; i++) {
208         QueryDimension queryDimension = (QueryDimension) rowDimensionsIterator.next();
209         String JavaDoc caption = (reportResult.getReportSpec().getDimensionFromName(queryDimension.getName())).getCaption();
210         if (caption.indexOf(';')>=0){
211           String JavaDoc rowName = caption;
212           int indexOf = rowName.indexOf(';');
213           while (indexOf>=0){
214             rows.add(rowName.substring(0,indexOf));
215             rowName = rowName.substring(indexOf+1, rowName.length());
216             indexOf = rowName.indexOf(';');
217           }
218           if (!rowName.equalsIgnoreCase("")){
219             rows.add(rowName);
220           }
221         }else{
222           rows.add(caption);
223         }
224       }
225       return rows;
226     }
227
228     private boolean isValid(Object JavaDoc[] valueFrom) {
229     for(int i = 0 ; i < valueFrom.length ; i++) {
230       if(valueFrom [i] == null) {
231         return false;
232       }
233     }
234     return true;
235   }
236
237
238   private void serializeFrom(HSSFWorkbook wb, FileOutputStream JavaDoc file) throws IOException JavaDoc {
239     FileOutputStream JavaDoc fileOut = file;
240     wb.write(fileOut);
241     fileOut.close();
242   }
243
244   private int fillCellFrom(Object JavaDoc[] valueFrom, HSSFRow row, int index) {
245     int parseValues = 0;
246     for(int i = 0 ; i < valueFrom.length ; i++) {
247       String JavaDoc value = (valueFrom [i]).toString();
248       if (value.indexOf(';')>=0){
249         int indexOf = value.indexOf(';');
250         while (indexOf>=0){
251           row.createCell((short)index++).setCellValue(value.substring(0,indexOf));
252           value = value.substring(indexOf+1, value.length());
253           parseValues = parseValues +1;
254           indexOf = value.indexOf(';');
255         }
256         row.createCell((short)index++).setCellValue(value);
257       }else{
258         row.createCell((short)index++).setCellValue((valueFrom [i]).toString());
259       }
260     }
261     return parseValues;
262   }
263
264   private void fillCellFrom(int i, int j, HSSFRow row, int index) {
265     TableModel JavaDoc model = dataTable.getModel();
266     HSSFCell cell = row.createCell((short)index++);
267     cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
268     SharedFloat sharedFloat = (SharedFloat)(model.getValueAt(i,j));
269     if ((sharedFloat==null)||((sharedFloat).getValue().equals(new Float JavaDoc(Float.NaN)))||(((sharedFloat).getValue().equals(new Float JavaDoc(Float.POSITIVE_INFINITY))))||(((sharedFloat).getValue().equals(new Float JavaDoc(Float.NEGATIVE_INFINITY))))){
270       cell.setCellValue("0");
271     }else{
272       cell.setCellValue(new Double JavaDoc(model.getValueAt(i, j).toString()).doubleValue());
273
274     }
275   }
276 }
277
Popular Tags