001: package com.calipso.reportgenerator.userinterface;
002:
003: import org.apache.poi.hssf.usermodel.HSSFSheet;
004: import org.apache.poi.hssf.usermodel.HSSFWorkbook;
005: import org.apache.poi.hssf.usermodel.HSSFRow;
006: import org.apache.poi.hssf.usermodel.HSSFCell;
007: import javax.swing.*;
008: import javax.swing.table.TableModel;
009: import java.io.FileOutputStream;
010: import java.io.IOException;
011: import java.io.FileNotFoundException;
012: import java.util.*;
013:
014: import com.calipso.reportgenerator.common.*;
015: import com.calipso.reportgenerator.reportcalculator.SharedFloat;
016:
017: /**
018: *
019: */
020:
021: public class ExcelFormatSerializer {
022:
023: private JTable dataTable;
024: private HeaderTableModel columnHeaderTableModel;
025: private HeaderTableModel rowHeaderTableModel;
026: private ReportResult reportResult;
027: private int metricsCount;
028: private ArrayList rows;
029: private ArrayList columns;
030:
031: public ExcelFormatSerializer(PivotTable pivotTable) {
032: initialize(pivotTable);
033: }
034:
035: private void initialize(PivotTable pivotTable) {
036: this .rowHeaderTableModel = (HeaderTableModel) pivotTable
037: .getRowHeaderjTable().getModel();
038: this .columnHeaderTableModel = (HeaderTableModel) pivotTable
039: .getColumnHeaderjTable().getModel();
040: this .dataTable = pivotTable.getDatajTable();
041: this .metricsCount = pivotTable.getPivotTableFrame()
042: .getReportResult().getReportQuery().getVisibleMetrics()
043: .size();
044: this .reportResult = pivotTable.getReportResult();
045: }
046:
047: /**
048: * Crea un archivo excel con los datos en el path especificado
049: * @param path
050: */
051: public void serializeTo(String path) throws IOException {
052: HSSFWorkbook wb = new HSSFWorkbook();
053: HSSFSheet sheet = wb.createSheet(reportResult.getReportSpec()
054: .getTitle().trim());
055: int sum = 0;
056: int index = 0;
057: Vector cutCols = getNonGroupingDimensionIndexesFromModel();
058: fillFirstRow(sheet);
059: int additionalRows = getAdditionalRows();
060: int colCount = rowHeaderTableModel.getColumnCount();
061: if (colCount == 0)
062: colCount++;
063: for (int i = 0; i < rowHeaderTableModel.getRowCount(); i++) {
064: if (isValid(rowHeaderTableModel.getValueFrom(i))) {
065: Object[] groupingDimensions = rowHeaderTableModel
066: .getValueFrom(i);
067: if (cutCols.size() > 0) {
068: if (isValid(groupingDimensions)) {
069: HSSFRow row = sheet
070: .createRow((short) (index + 1));
071: fillCellFrom(groupingDimensions, row, 0);
072: index++;
073: for (int j = 0; j < columnHeaderTableModel
074: .getColumnCount(); j++) {
075: fillCellFrom(i, j, row, colCount
076: + additionalRows + j);
077: }
078: }
079: } else {
080: index = 0;
081: if (isValid(groupingDimensions)) {
082: HSSFRow row = sheet
083: .createRow((short) (sum + 1));
084: fillCellFrom(groupingDimensions, row, index);
085: index += groupingDimensions.length
086: + additionalRows;
087: if (index == 0)
088: index++;
089: for (int z = 0; z < metricsCount; z++) {
090: fillCellFrom(i, z, row, index);
091: index++;
092: }
093: sum++;
094: }
095: }
096: }
097: }
098: serializeFrom(wb, new FileOutputStream(path));
099: }
100:
101: private Object[] getNonGroupingDimensionsFrom(Object[] elements) {
102: int length = elements.length;
103: ;
104: if (columnHeaderTableModel.getVisibleMetricsCount() > 1) {
105: length--;
106: }
107: Object[] nonGroupingDimensions = new Object[length];
108: for (int i = 0; i < length; i++) {
109: nonGroupingDimensions[i] = elements[i];
110: }
111: return nonGroupingDimensions;
112: }
113:
114: private Vector getNonGroupingDimensionIndexesFromModel() {
115: Vector cutCols = new Vector();
116: int cant = columnHeaderTableModel.getDimensionCount();
117: for (int i = 0; i < columnHeaderTableModel.getColumnCount(); i++) {
118: Object[] values = columnHeaderTableModel.getValueFrom(i);
119: if (isValid(values)) {
120: boolean isDistinct = false;
121: for (int j = 0; j < cant/* - 1*/; j++) {
122: if (i == 0
123: || (columnHeaderTableModel.getValueFrom(i)[j] != columnHeaderTableModel
124: .getValueFrom(i - 1)[j])) {
125: isDistinct = true;
126: }
127: }
128: if (isDistinct) {
129: cutCols.add(new Integer(/*changeIndex*/i));
130: }
131: }
132: }
133: return cutCols;
134: }
135:
136: private int getChangeIndex(Object[] values, int i) {
137: int changeIndex = -1;
138: for (; i < columnHeaderTableModel.getColumnCount(); i++) {
139: Object[] current = columnHeaderTableModel.getValueFrom(i);
140: if (isValid(current)) {
141: for (int j = 0; j < current.length; j++) {
142: if (j != current.length - 1) {
143: if (current[j] != values[j]) {
144: changeIndex = i;
145: break;
146: }
147: }
148: }
149: }
150: if (changeIndex != -1) {
151: break;
152: }
153: }
154: return changeIndex;
155: }
156:
157: private void fillFirstRow(HSSFSheet sheet) {
158: HSSFRow row = sheet.createRow((short) 0);
159: int i = 0;
160: int ii = 0;
161:
162: for (; getRows().size() > i; i++) {
163: row.createCell((short) i).setCellValue(
164: getRows().get(i).toString());
165: }
166: if (i == 0) {
167: i++;
168: }
169: String caption = "";
170: for (; columnHeaderTableModel.getColumnCount() > ii; ii++) {
171: Object[] values = columnHeaderTableModel.getValueFrom(ii);
172: for (int iii = 0; values.length > iii; iii++) {
173: if (values[iii] == null) {
174: caption = caption + "Total";
175: } else {
176: caption = caption + values[iii].toString();
177: }
178: if (iii < (values.length - 1)) {
179: caption = caption + "\\";
180: }
181:
182: }
183: if ((caption.equalsIgnoreCase(""))
184: && (reportResult.getReportQuery()
185: .getVisibleMetrics().size() == 1)) {
186: String metricName = ((QueryMetric) reportResult
187: .getReportQuery().getVisibleMetrics().get(0))
188: .getName();
189: caption = ((ReportMetricSpec) reportResult
190: .getReportSpec().getMetricFromName(metricName))
191: .getCaption();
192: }
193: row.createCell((short) (i + ii)).setCellValue(caption);
194: caption = "";
195: }
196: }
197:
198: private int getAdditionalRows() {
199: int index = 0;
200: Iterator rowDimensionsIterator = reportResult.getReportQuery()
201: .getRowDimensions().iterator();
202: int i = 0;
203: for (; rowDimensionsIterator.hasNext(); i++) {
204: QueryDimension queryDimension = (QueryDimension) rowDimensionsIterator
205: .next();
206: String caption = (reportResult.getReportSpec()
207: .getDimensionFromName(queryDimension.getName()))
208: .getCaption();
209: if (caption.indexOf(';') >= 0) {
210: String rowName = caption;
211: int indexOf = rowName.indexOf(';');
212: while (indexOf >= 0) {
213: rowName = rowName.substring(indexOf + 1, rowName
214: .length());
215: indexOf = rowName.indexOf(';');
216: index = index + 1;
217: }
218: //index = index -1;
219: if (!rowName.equalsIgnoreCase("")) {
220: // index = index +1;
221: }
222: }
223: }
224: return index;
225: }
226:
227: private ArrayList getRows() {
228: if (rows != null) {
229: return rows;
230: }
231: rows = new ArrayList();
232: Iterator rowDimensionsIterator = reportResult.getReportQuery()
233: .getRowDimensions().iterator();
234: int i = 0;
235: for (; rowDimensionsIterator.hasNext(); i++) {
236: QueryDimension queryDimension = (QueryDimension) rowDimensionsIterator
237: .next();
238: String caption = (reportResult.getReportSpec()
239: .getDimensionFromName(queryDimension.getName()))
240: .getCaption();
241: if (caption.indexOf(';') >= 0) {
242: String rowName = caption;
243: int indexOf = rowName.indexOf(';');
244: while (indexOf >= 0) {
245: rows.add(rowName.substring(0, indexOf));
246: rowName = rowName.substring(indexOf + 1, rowName
247: .length());
248: indexOf = rowName.indexOf(';');
249: }
250: if (!rowName.equalsIgnoreCase("")) {
251: rows.add(rowName);
252: }
253: } else {
254: rows.add(caption);
255: }
256: }
257: return rows;
258: }
259:
260: private boolean isValid(Object[] valueFrom) {
261: for (int i = 0; i < valueFrom.length; i++) {
262: if (valueFrom[i] == null) {
263: return false;
264: }
265: }
266: return true;
267: }
268:
269: private void serializeFrom(HSSFWorkbook wb, FileOutputStream file)
270: throws IOException {
271: FileOutputStream fileOut = file;
272: wb.write(fileOut);
273: fileOut.close();
274: }
275:
276: private int fillCellFrom(Object[] valueFrom, HSSFRow row, int index) {
277: int parseValues = 0;
278: for (int i = 0; i < valueFrom.length; i++) {
279: String value = (valueFrom[i]).toString();
280: if (value.indexOf(';') >= 0) {
281: int indexOf = value.indexOf(';');
282: while (indexOf >= 0) {
283: row.createCell((short) index++).setCellValue(
284: value.substring(0, indexOf));
285: value = value
286: .substring(indexOf + 1, value.length());
287: parseValues = parseValues + 1;
288: indexOf = value.indexOf(';');
289: }
290: row.createCell((short) index++).setCellValue(value);
291: } else {
292: row.createCell((short) index++).setCellValue(
293: (valueFrom[i]).toString());
294: }
295: }
296: return parseValues;
297: }
298:
299: private void fillCellFrom(int i, int j, HSSFRow row, int index) {
300: TableModel model = dataTable.getModel();
301: HSSFCell cell = row.createCell((short) index++);
302: cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
303: SharedFloat sharedFloat = (SharedFloat) (model.getValueAt(i, j));
304: if ((sharedFloat == null)
305: || ((sharedFloat).getValue()
306: .equals(new Float(Float.NaN)))
307: || (((sharedFloat).getValue().equals(new Float(
308: Float.POSITIVE_INFINITY))))
309: || (((sharedFloat).getValue().equals(new Float(
310: Float.NEGATIVE_INFINITY))))) {
311: cell.setCellValue("0");
312: } else {
313: cell.setCellValue(new Double(model.getValueAt(i, j)
314: .toString()).doubleValue());
315:
316: }
317: }
318: }
|