|
1
16
17 package info.jtrac.domain;
18
19 import info.jtrac.util.ItemUtils;
20 import java.io.InputStream  ;
21 import java.io.Serializable ;
22 import java.util.ArrayList ;
23 import java.util.List ;
24 import org.apache.commons.logging.Log;
25 import org.apache.commons.logging.LogFactory;
26 import org.apache.poi.hssf.usermodel.HSSFCell;
27 import org.apache.poi.hssf.usermodel.HSSFRow;
28 import org.apache.poi.hssf.usermodel.HSSFSheet;
29 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
30 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
31
32
37 public class ExcelFile implements Serializable {
38
39 protected final Log logger = LogFactory.getLog(getClass());
40
41 public class Column {
42
43 private String label;
44 private Field field;
45
46 public Column(String label) {
47 this.label = label;
48 }
49
50 public Field getField() {
51 return field;
52 }
53
54 public String getLabel() {
55 return label;
56 }
57
58 }
59
60 public class Cell {
61
62 private Object value;
63
64 public Cell(Object value) {
65 this.value = value;
66 }
67
68 @Override
69 public String toString() {
70 if (value == null) {
71 return "";
72 }
73 if (value instanceof String ) {
74 return ItemUtils.fixWhiteSpace((String ) value);
75 }
76 return value.toString();
77 }
78
79 }
80
81 private List <Column> columns;
82 private List <List <Cell>> rows;
83
84 public List <List <Cell>> getRows() {
85 return rows;
86 }
87
88 public List <Column> getColumns() {
89 return columns;
90 }
91
92
95 private int[] selCols;
96 private int[] selRows;
97 private int action;
98
99 public int getAction() {
100 return action;
101 }
102
103 public void setAction(int action) {
104 this.action = action;
105 }
106
107 public int[] getSelCols() {
108 return selCols;
109 }
110
111 public void setSelCols(int[] selCols) {
112 this.selCols = selCols;
113 }
114
115 public int[] getSelRows() {
116 return selRows;
117 }
118
119 public void setSelRows(int[] selRows) {
120 this.selRows = selRows;
121 }
122
123
126
127 public void deleteSelectedRowsAndColumns() {
128 int cursor = 0;
129 if (selRows != null) {
130 for(int i : selRows) {
131 rows.remove(i - cursor);
132 cursor++;
133 }
134 }
135 cursor = 0;
136 if (selCols != null) {
137 for(int i : selCols) {
138 columns.remove(i - cursor);
139 for(List <Cell> cells : rows) {
140 cells.remove(i - cursor);
141 }
142 cursor++;
143 }
144 }
145 }
146
147 public void convertSelectedColumnsToDate() {
148 if (selCols == null) {
149 return;
150 }
151 HSSFWorkbook wb = new HSSFWorkbook();
153 HSSFSheet sheet = wb.createSheet();
154 HSSFRow row = sheet.createRow(0);
155 HSSFCell cell = row.createCell((short) 0);
156 for(int i : selCols) {
157 for(List <Cell> cells : rows) {
158 Cell c = cells.get(i);
159 if (c != null && c.value instanceof Double ) {
160 cell.setCellValue((Double ) c.value);
161 c.value = cell.getDateCellValue();
162 }
163 }
164 }
165 }
166
167 public void concatenateSelectedColumns() {
168 if (selCols == null) {
169 return;
170 }
171 List <Cell> list = new ArrayList <Cell>(rows.size());
172 for(List <Cell> cells : rows) {
173 list.add(new Cell(null));
174 }
175 int first = selCols[0];
176 for(int i : selCols) {
177 int rowIndex = 0;
178 for(List <Cell> cells : rows) {
179 Cell c = cells.get(i);
180 if (c != null) {
181 String s = (String ) list.get(rowIndex).value;
182 if (s == null) {
183 s = (String ) c.value;
184 } else {
185 s += "\n\n" + c.value;
186 }
187 list.set(rowIndex, new Cell(s));
188 }
189 rowIndex++;
190 }
191 }
192 int rowIndex = 0;
194 for(List <Cell> cells : rows) {
195 cells.set(first, list.get(rowIndex));
196 rowIndex++;
197 }
198 }
199
200 public void extractSummaryFromSelectedColumn() {
201 if (selCols == null) {
202 return;
203 }
204 int first = selCols[0];
205 for(List <Cell> cells : rows) {
206 Cell c = cells.get(first);
207 if (c != null && c.value != null) {
208 String s = c.value.toString();
209 if (s.length() > 80) {
210 s = s.substring(0, 80);
211 }
212 cells.add(0, new Cell(s));
213 } else {
214 cells.add(0, null);
215 }
216 }
217 columns.add(0, new Column("Summary"));
218 }
219
220
222 public ExcelFile() {
223 }
225
226 public ExcelFile(InputStream is) {
227 POIFSFileSystem fs = null;
228 HSSFWorkbook wb = null;
229 try {
230 fs = new POIFSFileSystem(is);
231 wb = new HSSFWorkbook(fs);
232 } catch (Exception e) {
233 throw new RuntimeException (e);
234 }
235 HSSFSheet sheet = wb.getSheetAt(0);
236 HSSFRow r = null;
237 HSSFCell c = null;
238 int row = 0;
239 int col = 0;
240 columns = new ArrayList <Column>();
241 r = sheet.getRow(row);
243 while(true) {
244 c = r.getCell((short) col);
245 if (c == null) {
246 break;
247 }
248 String value = c.getStringCellValue();
249 if (value == null || value.trim().length() == 0) {
250 break;
251 }
252 Column column = new Column(value.trim());
253 columns.add(column);
254 col++;
255 }
256 rows = new ArrayList <List <Cell>>();
258 while(true) {
259 row++;
260 r = sheet.getRow(row);
261 if (r == null) {
262 break;
263 }
264 List rowData = new ArrayList (columns.size());
265 boolean isEmptyRow = true;
266 for(col = 0; col < columns.size(); col++) {
267 c = r.getCell((short) col);
268 Object value = null;
269 switch(c.getCellType()) {
270 case(HSSFCell.CELL_TYPE_STRING) : value = c.getStringCellValue(); break;
271 case(HSSFCell.CELL_TYPE_NUMERIC) :
272 value = c.getNumericCellValue();
274 break;
275 case(HSSFCell.CELL_TYPE_BLANK) : break;
276 }
277 if (value != null && value.toString().length() > 0) {
278 isEmptyRow = false;
279 rowData.add(new Cell(value));
280 } else {
281 rowData.add(null);
282 }
283 }
284 if(isEmptyRow) {
285 break;
286 }
287 rows.add(rowData);
288 }
289 }
290
291 }
292 |
Popular Tags |
Java API By Example, From Geeks To Geeks.