001: /*
002: * Copyright 2002-2005 the original author or authors.
003: *
004: * Licensed under the Apache License, Version 2.0 (the "License");
005: * you may not use this file except in compliance with the License.
006: * You may obtain a copy of the License at
007: *
008: * http://www.apache.org/licenses/LICENSE-2.0
009: *
010: * Unless required by applicable law or agreed to in writing, software
011: * distributed under the License is distributed on an "AS IS" BASIS,
012: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
013: * See the License for the specific language governing permissions and
014: * limitations under the License.
015: */
016:
017: package info.jtrac.domain;
018:
019: import info.jtrac.util.ItemUtils;
020: import java.io.InputStream;
021: import java.io.Serializable;
022: import java.util.ArrayList;
023: import java.util.List;
024: import org.apache.poi.hssf.usermodel.HSSFCell;
025: import org.apache.poi.hssf.usermodel.HSSFRow;
026: import org.apache.poi.hssf.usermodel.HSSFSheet;
027: import org.apache.poi.hssf.usermodel.HSSFWorkbook;
028: import org.apache.poi.poifs.filesystem.POIFSFileSystem;
029: import org.slf4j.Logger;
030: import org.slf4j.LoggerFactory;
031:
032: /**
033: * Class that encapsulates an Excel Sheet / Workbook
034: * and is used to process, cleanse and import contents of an
035: * uploaded excel file into JTrac
036: */
037: public class ExcelFile implements Serializable {
038:
039: private static final Logger logger = LoggerFactory
040: .getLogger(ExcelFile.class);
041:
042: /**
043: * represents a column heading and data type
044: */
045: public class Column {
046:
047: private String label;
048: private Field field;
049:
050: public Column(String label) {
051: this .label = label;
052: }
053:
054: public Field getField() {
055: return field;
056: }
057:
058: public String getLabel() {
059: return label;
060: }
061:
062: }
063:
064: /**
065: * represents a cell value, acts as object holder
066: */
067: public class Cell {
068:
069: private Object value;
070:
071: public Cell(Object value) {
072: this .value = value;
073: }
074:
075: @Override
076: public String toString() {
077: if (value == null) {
078: return "";
079: }
080: if (value instanceof String) {
081: return ItemUtils.fixWhiteSpace((String) value);
082: }
083: return value.toString();
084: }
085:
086: }
087:
088: private List<Column> columns;
089: private List<List<Cell>> rows;
090:
091: public List<List<Cell>> getRows() {
092: return rows;
093: }
094:
095: public List<Column> getColumns() {
096: return columns;
097: }
098:
099: //==========================================================================
100: // form binding stuff
101:
102: private int[] selCols;
103: private int[] selRows;
104: private int action;
105:
106: public int getAction() {
107: return action;
108: }
109:
110: public void setAction(int action) {
111: this .action = action;
112: }
113:
114: public int[] getSelCols() {
115: return selCols;
116: }
117:
118: public void setSelCols(int[] selCols) {
119: this .selCols = selCols;
120: }
121:
122: public int[] getSelRows() {
123: return selRows;
124: }
125:
126: public void setSelRows(int[] selRows) {
127: this .selRows = selRows;
128: }
129:
130: //==========================================================================
131: // edits
132:
133: /* note that selected rows and columns would be set by spring MVC */
134: public void deleteSelectedRowsAndColumns() {
135: int cursor = 0;
136: if (selRows != null) {
137: for (int i : selRows) {
138: rows.remove(i - cursor);
139: cursor++;
140: }
141: }
142: cursor = 0;
143: if (selCols != null) {
144: for (int i : selCols) {
145: columns.remove(i - cursor);
146: for (List<Cell> cells : rows) {
147: cells.remove(i - cursor);
148: }
149: cursor++;
150: }
151: }
152: }
153:
154: public void convertSelectedColumnsToDate() {
155: if (selCols == null) {
156: return;
157: }
158: // could not find a better way to convert excel number to date
159: HSSFWorkbook wb = new HSSFWorkbook();
160: HSSFSheet sheet = wb.createSheet();
161: HSSFRow row = sheet.createRow(0);
162: HSSFCell cell = row.createCell((short) 0);
163: for (int i : selCols) {
164: for (List<Cell> cells : rows) {
165: Cell c = cells.get(i);
166: if (c != null && c.value instanceof Double) {
167: cell.setCellValue((Double) c.value);
168: c.value = cell.getDateCellValue();
169: }
170: }
171: }
172: }
173:
174: public void concatenateSelectedColumns() {
175: if (selCols == null) {
176: return;
177: }
178: List<Cell> list = new ArrayList<Cell>(rows.size());
179: for (List<Cell> cells : rows) {
180: list.add(new Cell(null));
181: }
182: int first = selCols[0];
183: for (int i : selCols) {
184: int rowIndex = 0;
185: for (List<Cell> cells : rows) {
186: Cell c = cells.get(i);
187: if (c != null) {
188: String s = (String) list.get(rowIndex).value;
189: if (s == null) {
190: s = (String) c.value;
191: } else {
192: s += "\n\n" + c.value;
193: }
194: list.set(rowIndex, new Cell(s));
195: }
196: rowIndex++;
197: }
198: }
199: // update the first column
200: int rowIndex = 0;
201: for (List<Cell> cells : rows) {
202: cells.set(first, list.get(rowIndex));
203: rowIndex++;
204: }
205: }
206:
207: public void extractSummaryFromSelectedColumn() {
208: if (selCols == null) {
209: return;
210: }
211: int first = selCols[0];
212: for (List<Cell> cells : rows) {
213: Cell c = cells.get(first);
214: if (c != null && c.value != null) {
215: String s = c.value.toString();
216: if (s.length() > 80) {
217: s = s.substring(0, 80);
218: }
219: cells.add(0, new Cell(s));
220: } else {
221: cells.add(0, null);
222: }
223: }
224: columns.add(0, new Column("Summary"));
225: }
226:
227: //==========================================================================
228:
229: public ExcelFile() {
230: // zero arg constructor
231: }
232:
233: public ExcelFile(InputStream is) {
234: POIFSFileSystem fs = null;
235: HSSFWorkbook wb = null;
236: try {
237: fs = new POIFSFileSystem(is);
238: wb = new HSSFWorkbook(fs);
239: } catch (Exception e) {
240: throw new RuntimeException(e);
241: }
242: HSSFSheet sheet = wb.getSheetAt(0);
243: HSSFRow r = null;
244: HSSFCell c = null;
245: int row = 0;
246: int col = 0;
247: columns = new ArrayList<Column>();
248: //========================== HEADER ====================================
249: r = sheet.getRow(row);
250: while (true) {
251: c = r.getCell((short) col);
252: if (c == null) {
253: break;
254: }
255: String value = c.getStringCellValue();
256: if (value == null || value.trim().length() == 0) {
257: break;
258: }
259: Column column = new Column(value.trim());
260: columns.add(column);
261: col++;
262: }
263: //============================ DATA ====================================
264: rows = new ArrayList<List<Cell>>();
265: while (true) {
266: row++;
267: r = sheet.getRow(row);
268: if (r == null) {
269: break;
270: }
271: List rowData = new ArrayList(columns.size());
272: boolean isEmptyRow = true;
273: for (col = 0; col < columns.size(); col++) {
274: c = r.getCell((short) col);
275: Object value = null;
276: switch (c.getCellType()) {
277: case (HSSFCell.CELL_TYPE_STRING):
278: value = c.getStringCellValue();
279: break;
280: case (HSSFCell.CELL_TYPE_NUMERIC):
281: // value = c.getDateCellValue();
282: value = c.getNumericCellValue();
283: break;
284: case (HSSFCell.CELL_TYPE_BLANK):
285: break;
286: default: // do nothing
287: }
288: if (value != null && value.toString().length() > 0) {
289: isEmptyRow = false;
290: rowData.add(new Cell(value));
291: } else {
292: rowData.add(null);
293: }
294: }
295: if (isEmptyRow) {
296: break;
297: }
298: rows.add(rowData);
299: }
300: }
301:
302: }
|