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 ; 9 import java.io.FileOutputStream ; 10 import java.io.IOException ; 11 import java.io.FileNotFoundException ; 12 import java.util.*; 13 14 import com.calipso.reportgenerator.common.*; 15 import com.calipso.reportgenerator.reportcalculator.SharedFloat; 16 17 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 47 public void serializeTo(String path) throws IOException { 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 [] 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 (path)); 86 } 87 88 private Object [] getNonGroupingDimensionsFrom(Object [] elements) { 89 int length = elements.length;; 90 if(columnHeaderTableModel.getVisibleMetricsCount() > 1){ 91 length--; 92 } 93 Object [] nonGroupingDimensions = new Object [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 [] values = columnHeaderTableModel.getValueFrom(i); 105 if(isValid(values)) { 106 boolean isDistinct = false; 107 for(int j = 0; j < cant; 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 (i)); 114 } 115 } 116 } 117 return cutCols; 118 } 119 120 private int getChangeIndex(Object [] values, int i) { 121 int changeIndex = -1; 122 for(; i < columnHeaderTableModel.getColumnCount() ; i++) { 123 Object [] 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 caption=""; 154 for(; columnHeaderTableModel.getColumnCount()>ii ; ii++) { 155 Object [] 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 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 caption = (reportResult.getReportSpec().getDimensionFromName(queryDimension.getName())).getCaption(); 183 if (caption.indexOf(';')>=0){ 184 String 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 if (!rowName.equalsIgnoreCase("")){ 193 } 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 caption = (reportResult.getReportSpec().getDimensionFromName(queryDimension.getName())).getCaption(); 210 if (caption.indexOf(';')>=0){ 211 String 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 [] 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 file) throws IOException { 239 FileOutputStream fileOut = file; 240 wb.write(fileOut); 241 fileOut.close(); 242 } 243 244 private int fillCellFrom(Object [] valueFrom, HSSFRow row, int index) { 245 int parseValues = 0; 246 for(int i = 0 ; i < valueFrom.length ; i++) { 247 String 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 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 (Float.NaN)))||(((sharedFloat).getValue().equals(new Float (Float.POSITIVE_INFINITY))))||(((sharedFloat).getValue().equals(new Float (Float.NEGATIVE_INFINITY))))){ 270 cell.setCellValue("0"); 271 }else{ 272 cell.setCellValue(new Double (model.getValueAt(i, j).toString()).doubleValue()); 273 274 } 275 } 276 } 277 | Popular Tags |