1 package at.meikel.dmrl.server.xlsreader;
2
3 import java.io.FileInputStream;
4 import java.io.FileNotFoundException;
5 import java.io.IOException;
6 import java.io.InputStream;
7 import java.io.PrintStream;
8 import java.util.Hashtable;
9 import java.util.Iterator;
10 import java.util.SortedSet;
11 import java.util.TreeSet;
12 import java.util.Vector;
13
14 import org.apache.log4j.Logger;
15 import org.apache.poi.hssf.usermodel.HSSFRow;
16 import org.apache.poi.hssf.usermodel.HSSFSheet;
17 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
18 import org.apache.poi.ss.usermodel.Cell;
19
20 public class Table {
21
22 private Vector<Row> rows = new Vector<Row>();
23 private int minColumnIndex = 0;
24 private int maxColumnIndex = 0;
25 private Hashtable<Integer, Integer> virtual2physicalMap = null;
26 private Hashtable<Integer, String> columnNames = null;
27
28 private static final Logger LOGGER = Logger.getLogger(Table.class);
29
30 private Table() {
31
32 }
33
34 public static Table read(String filename, String sheetName) {
35 try {
36 return read(new FileInputStream(filename), sheetName);
37 } catch (FileNotFoundException e) {
38 LOGGER.warn("Unable to read file.", e);
39 return null;
40 }
41 }
42
43 public static Table read(InputStream is, String sheetName) {
44 Table result = new Table();
45 try {
46 HSSFWorkbook wb = new HSSFWorkbook(is);
47
48 HSSFSheet sheet = wb.getSheet(sheetName);
49 for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet
50 .getLastRowNum(); rowIndex++) {
51 HSSFRow row = sheet.getRow(rowIndex);
52
53 if (row == null) {
54 continue;
55 }
56
57 Row xrrow = result.createRow();
58
59 Iterator<Cell> cellIterator = row.cellIterator();
60 while (cellIterator.hasNext()) {
61 Cell cell = cellIterator.next();
62 Object value = null;
63 switch (cell.getCellType()) {
64 case Cell.CELL_TYPE_BLANK:
65 value = "CELL_TYPE_BLANK";
66 break;
67
68 case Cell.CELL_TYPE_BOOLEAN:
69 value = cell.getBooleanCellValue();
70 break;
71
72 case Cell.CELL_TYPE_ERROR:
73 value = cell.getErrorCellValue();
74 break;
75
76 case Cell.CELL_TYPE_FORMULA:
77 value = cell.getCellFormula();
78 break;
79
80 case Cell.CELL_TYPE_NUMERIC:
81 value = cell.getNumericCellValue();
82 break;
83
84 case Cell.CELL_TYPE_STRING:
85 value = cell.getStringCellValue();
86 break;
87
88 default:
89 value = "NA";
90 break;
91 }
92 if (value != null) {
93 xrrow.setColumnValue(cell.getColumnIndex(), value);
94 }
95 }
96 }
97 } catch (IOException e) {
98 LOGGER.warn("Unable to read file.", e);
99 return null;
100 }
101
102 result.computeColumnVirtualIndicesAndHeaders();
103 return result;
104 }
105
106 public boolean hasColumns() {
107 return columnNames != null;
108 }
109
110 public String getColumnName(int columnIndex) {
111 return columnNames.get(new Integer(columnIndex));
112 }
113
114 public int getMinRowIndex() {
115 return 1;
116 }
117
118 public int getMaxRowIndex() {
119 return rows.size();
120 }
121
122 public Row getRow(int rowIndex) {
123 return rows.get(new Integer(rowIndex - 1));
124 }
125
126 public void print(PrintStream ps) {
127 for (int columnIndex = minColumnIndex; columnIndex <= maxColumnIndex; columnIndex++) {
128 if (columnIndex > minColumnIndex) {
129 ps.print(" | ");
130 }
131 ps.print(getColumnName(columnIndex));
132 }
133
134 ps.println();
135 ps.println();
136
137 for (Row row : rows) {
138 for (int columnIndex = minColumnIndex; columnIndex <= maxColumnIndex; columnIndex++) {
139 if (columnIndex > minColumnIndex) {
140 ps.print(" | ");
141 }
142 ps.print(row.getColumnValue(virtual2physicalMap
143 .get(new Integer(columnIndex).intValue())));
144 }
145
146 ps.println();
147 ps.println();
148 }
149 }
150
151 private Row createRow() {
152 Row result = new Row();
153 rows.add(result);
154 return result;
155 }
156
157 private void computeColumnVirtualIndicesAndHeaders() {
158 String[] skipValues = new String[]{"Platz", "Pass", "Name", "Kat.",
159 "Kader", "Verein"
160
161
162 };
163 SortedSet<Integer> physicalIndices = new TreeSet<Integer>();
164 Iterator<Row> rowIterator = rows.iterator();
165 boolean skip = true;
166 while (rowIterator.hasNext()) {
167 Row row = rowIterator.next();
168 if (skip) {
169 skip = false;
170 for (int i = 0; i < skipValues.length; i++) {
171 if (!skipValues[i].equals(row.getNthColumnValue(i))) {
172 skip = true;
173 break;
174 }
175 }
176
177 if (skip) {
178 rowIterator.remove();
179 continue;
180 }
181
182 physicalIndices.addAll(row.getColomnIndices());
183 }
184 }
185
186 if (physicalIndices.isEmpty()) {
187 return;
188 }
189
190 virtual2physicalMap = new Hashtable<Integer, Integer>();
191 int virtualIndex = 0;
192 for (Integer physicalIndex : physicalIndices) {
193 virtualIndex++;
194 virtual2physicalMap.put(new Integer(virtualIndex), physicalIndex);
195 }
196 minColumnIndex = 1;
197 maxColumnIndex = virtualIndex;
198
199 if (rows.isEmpty()) {
200 return;
201 }
202
203 columnNames = new Hashtable<Integer, String>();
204 Row headerRow = rows.remove(0);
205 for (int columnIndex = minColumnIndex; columnIndex <= maxColumnIndex; columnIndex++) {
206 headerRow.getColumnValue(columnIndex);
207 Object value = headerRow.getColumnValue(virtual2physicalMap
208 .get(new Integer(columnIndex).intValue()));
209 columnNames.put(new Integer(columnIndex), value == null ? "NA"
210 : value.toString());
211 }
212 }
213 }