1 package com.cib.common.excelUnils;
2
3 import java.io.IOException;
4 import java.io.InputStream;
5 import java.util.ArrayList;
6 import java.util.Iterator;
7 import java.util.List;
8 import java.util.regex.Matcher;
9 import java.util.regex.Pattern;
10
11 import org.apache.commons.lang.StringUtils;
12 import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
13 import org.apache.poi.openxml4j.opc.OPCPackage;
14 import org.apache.poi.ss.usermodel.BuiltinFormats;
15 import org.apache.poi.ss.usermodel.DataFormatter;
16 import org.apache.poi.xssf.eventusermodel.XSSFReader;
17 import org.apache.poi.xssf.model.SharedStringsTable;
18 import org.apache.poi.xssf.model.StylesTable;
19 import org.apache.poi.xssf.usermodel.XSSFCellStyle;
20 import org.apache.poi.xssf.usermodel.XSSFRichTextString;
21 import org.xml.sax.Attributes;
22 import org.xml.sax.InputSource;
23 import org.xml.sax.SAXException;
24 import org.xml.sax.XMLReader;
25 import org.xml.sax.helpers.DefaultHandler;
26 import org.xml.sax.helpers.XMLReaderFactory;
27
28 /**
29 * 名称: ExcelXlsxReader.java<br>
30 * 描述: <br>
31 * 类型: JAVA<br>
32 * 最近修改时间:2017年11月09日 上午10:00:52<br>
33 *
34 * @since 2017年11月09日
35 * @author “小涛”
36 */
37 public abstract class ExcelXlsxReader extends DefaultHandler {
38
39 /**
40 * 共享字符串表
41 */
42 private SharedStringsTable sst;
43
44
45 /**
46 * 上一次的内容
47 */
48 private String lastContents;
49
50
51 /**
52 * 字符串标识
53 */
54 private boolean nextIsString;
55
56
57 /**
58 * 工作表索引
59 */
60 private int sheetIndex = -1;
61
62
63 /**
64 * 行集合
65 */
66 private List<String> rowlist = new ArrayList<String>();
67
68
69 /**
70 * 当前行
71 */
72 private int curRow = 0;
73
74
75 /**
76 * 当前列
77 */
78 private int curCol = 0;
79
80
81 /**
82 * T元素标识
83 */
84 private boolean isTElement;
85
86
87 /**
88 * 异常信息，如果为空则表示没有异常
89 */
90 private String exceptionMessage;
91
92
93 /**
94 * 单元格数据类型，默认为字符串类型
95 */
96 private CellDataType nextDataType = CellDataType.SSTINDEX;
97
98
99 private final DataFormatter formatter = new DataFormatter();
100
101
102 private short formatIndex;
103
104
105 private String formatString;
106
107
108 // 定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
109 private String preRef = null, ref = null;
110
111
112 // 定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
113 private String maxRef = null;
114
115
116 /**
117 * 单元格
118 */
119 private StylesTable stylesTable;
120
121
122 /**
123 * 遍历工作簿中所有的电子表格
124 *
125 * @param filename
126 * @throws IOException
127 * @throws OpenXML4JException
128 * @throws SAXException
129 * @throws Exception
130 */
131 public void process(String filename) throws IOException, OpenXML4JException, SAXException {
132 OPCPackage pkg = OPCPackage.open(filename);
133 XSSFReader xssfReader = new XSSFReader(pkg);
134 stylesTable = xssfReader.getStylesTable();
135 SharedStringsTable sst = xssfReader.getSharedStringsTable();
136 XMLReader parser = this.fetchSheetParser(sst);
137 Iterator<InputStream> sheets = xssfReader.getSheetsData();
138 while (sheets.hasNext()) {
139 curRow = 0;
140 sheetIndex++;
141 InputStream sheet = sheets.next();
142 InputSource sheetSource = new InputSource(sheet);
143 parser.parse(sheetSource);
144 sheet.close();
145 }
146 }
147
148
149 public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
150 XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
151 this.sst = sst;
152 parser.setContentHandler(this);
153 return parser;
154 }
155
156
157 public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
158 // c => 单元格
159 if ("c".equals(name)) {
160 // 前一个单元格的位置
161 if (preRef == null) {
162 preRef = attributes.getValue("r");
163 } else {
164 preRef = ref;
165 }
166 // 当前单元格的位置
167 ref = attributes.getValue("r");
168 // 设定单元格类型
169 this.setNextDataType(attributes);
170 // Figure out if the value is an index in the SST
171 String cellType = attributes.getValue("t");
172 if (cellType != null && cellType.equals("s")) {
173 nextIsString = true;
174 } else {
175 nextIsString = false;
176 }
177 }
178
179
180 // 当元素为t时
181 if ("t".equals(name)) {
182 isTElement = true;
183 } else {
184 isTElement = false;
185 }
186
187
188 // 置空
189 lastContents = "";
190 }
191
192
193 /**
194 * 单元格中的数据可能的数据类型
195 */
196 enum CellDataType {
197 BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
198 }
199
200
201 /**
202 * 处理数据类型
203 *
204 * @param attributes
205 */
206 public void setNextDataType(Attributes attributes) {
207 nextDataType = CellDataType.NUMBER;
208 formatIndex = -1;
209 formatString = null;
210 String cellType = attributes.getValue("t");
211 String cellStyleStr = attributes.getValue("s");
212 String columData = attributes.getValue("r");
213
214 if ("b".equals(cellType)) {
215 nextDataType = CellDataType.BOOL;
216 } else if ("e".equals(cellType)) {
217 nextDataType = CellDataType.ERROR;
218 } else if ("inlineStr".equals(cellType)) {
219 nextDataType = CellDataType.INLINESTR;
220 } else if ("s".equals(cellType)) {
221 nextDataType = CellDataType.SSTINDEX;
222 } else if ("str".equals(cellType)) {
223 nextDataType = CellDataType.FORMULA;
224 }
225
226
227 if (cellStyleStr != null) {
228 int styleIndex = Integer.parseInt(cellStyleStr);
229 XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
230 formatIndex = style.getDataFormat();
231 formatString = style.getDataFormatString();
232
233
234 if ("m/d/yy" == formatString) {
235 nextDataType = CellDataType.DATE;
236 formatString = "yyyy-MM-dd hh:mm:ss";
237 }
238
239
240 if (formatString == null) {
241 nextDataType = CellDataType.NULL;
242 formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
243 }
244 }
245 }
246
247
248 /**
249 * 对解析出来的数据进行类型处理
250 *
251 * @param value
252 * 单元格的值（这时候是一串数字）
253 * @param thisStr
254 * 一个空字符串
255 * @return
256 */
257 public String getDataValue(String value, String thisStr) {
258 switch (nextDataType) {
259 // 这几个的顺序不能随便交换，交换了很可能会导致数据错误
260 case BOOL:
261 char first = value.charAt(0);
262 thisStr = first == '0' ? "FALSE" : "TRUE";
263 break;
264 case ERROR:
265 thisStr = "\"ERROR:" + value.toString() + '"';
266 break;
267 case FORMULA:
268 thisStr = '"' + value.toString() + '"';
269 break;
270 case INLINESTR:
271 XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
272
273 thisStr = rtsi.toString();
274 rtsi = null;
275 break;
276 case SSTINDEX:
277 String sstIndex = value.toString();
278 try {
279 int idx = Integer.parseInt(sstIndex);
280 XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));
281 thisStr = rtss.toString();
282 rtss = null;
283 } catch (NumberFormatException ex) {
284 thisStr = value.toString();
285 }
286
287 break;
288 case NUMBER:
289 if (formatString != null) {
290 thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
291 } else {
292 thisStr = value;
293 }
294 thisStr = thisStr.replace("_", "").trim();
295
296 break;
297 case DATE:
298 //获取的日期是一串数字、需要使用此HasDigit方法来过滤第一行的表头、日期类型
299 if (HasDigit(value)) {
300 thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
301 // 对日期字符串作特殊处理
302 thisStr = thisStr.replace(" ", " ");
303 break;
304 }
305 default:
306 thisStr = "null";
307 break;
308 }
309 return thisStr;
310 }
311
312
313 @Override
314 public void endElement(String uri, String localName, String name) throws SAXException {
315 // 根据SST的索引值的到单元格的真正要存储的字符串
316 // 这时characters()方法可能会被调用多次
317 if (nextIsString && StringUtils.isNotEmpty(lastContents) && StringUtils.isNumeric(lastContents)) {
318 int idx = Integer.parseInt(lastContents);
319 lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
320 }
321
322
323 // t元素也包含字符串
324 if (isTElement) {
325 // 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
326 String value = lastContents.trim();
327 rowlist.add(curCol, value);
328 curCol++;
329 isTElement = false;
330 } else if ("v".equals(name)) {
331 // v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
332 String value = this.getDataValue(lastContents.trim(), "");
333 // 补全单元格之间的空单元格
334 if (!ref.equals(preRef)) {
335 int len = countNullCell(ref, preRef);
336 for (int i = 0; i < len; i++) {
337 rowlist.add(curCol, "null");
338 curCol++;
339 }
340 }
341 rowlist.add(curCol, value);
342 curCol++;
343 } else {
344 // 如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
345 if (name.equals("row")) {
346 // 默认第一行为表头，以该行单元格数目为最大数目
347 if (curRow == 0) {
348 maxRef = ref;
349 }
350 // 补全一行尾部可能缺失的单元格
351 if (maxRef != null) {
352 int len = countNullCell(maxRef, ref);
353 for (int i = 0; i <= len; i++) {
354 rowlist.add(curCol, "");
355 curCol++;
356 }
357 }
358 getRows(sheetIndex, curRow, rowlist);
359 rowlist.clear();
360 curRow++;
361 curCol = 0;
362 preRef = null;
363 ref = null;
364 }
365 }
366 }
367
368
369 /**
370 * 计算两个单元格之间的单元格数目(同一行)
371 * 此方法用来判断单元格是空的、如不加、则不会读取空的单元格
372 * @param ref
373 * @param preRef
374 * @return
375 */
376 public int countNullCell(String ref, String preRef) {
377 // excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
378 String xfd = ref.replaceAll("\\d+", "");
379 String xfd_1 = preRef.replaceAll("\\d+", "");
380
381 xfd = fillChar(xfd, 3, '@', true);
382 xfd_1 = fillChar(xfd_1, 3, '@', true);
383
384 char[] letter = xfd.toCharArray();
385 char[] letter_1 = xfd_1.toCharArray();
386 int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
387 return res - 1;
388 }
389
390
391 /**
392 * 字符串的填充
393 *
394 * @param str
395 * @param len
396 * @param let
397 * @param isPre
398 * @return
399 */
400 String fillChar(String str, int len, char let, boolean isPre) {
401 int len_1 = str.length();
402 if (len_1 < len) {
403 if (isPre) {
404 for (int i = 0; i < (len - len_1); i++) {
405 str = let + str;
406 }
407 } else {
408 for (int i = 0; i < (len - len_1); i++) {
409 str = str + let;
410 }
411 }
412 }
413 return str;
414 }
415
416
417 @Override
418 public void characters(char[] ch, int start, int length) throws SAXException {
419 // 得到单元格内容的值
420 lastContents += new String(ch, start, length);
421 }
422
423
424 /**
425 * @return the exceptionMessage
426 */
427 public String getExceptionMessage() {
428 return exceptionMessage;
429 }
430
431 /**
432 * 获取行数据回调
433 *
434 * @param sheetIndex
435 * @param curRow
436 * @param rowList
437 */
438 public abstract void getRows(int sheetIndex, int curRow,
439 List<String> rowList);
440
441 //判断一个字符串是否含有数字
442 public boolean HasDigit(String content) {
443 boolean flag = false;
444 Pattern p = Pattern.compile("[1-9]\\d*\\.?\\d*");
445 Matcher m = p.matcher(content);
446 if (m.matches()) {
447 flag = true;
448 }
449 return flag;
450 }
451
452 }
