POI以SAX方式解析Excel2007大文件(包含空单元格的处理) Java生成CSV文件实例详解

http://blog.****.net/l081307114/article/details/46009015

http://www.cnblogs.com/dreammyle/p/5458280.html

. Office2007与Office Open XML

在Office 2007之前，Office一直都是以二进制位的方式存储，但这种格式不易被其它软件拿来使用，在各界的压力下，MicroSoft于2005年发布了基于XML的ooxml开放文档标准。ooxml的xml schema强调减少load time，增快parsing speed，将child elements分开存储，而不是multiple attributes一起存，这有点类似于HTML的结构。ooxml 使用XML和ZIP技术结合进行文件存储，因为XML是一个基于文本的格式，而且ZIP容器支持内容的压缩，所以其一大优势就是可以大大减小文件的尺寸。其它特点这里不再叙述。

2. SAX方式解析XML

SAX全称Simple API for XML，它是一个接口，也是一个软件包。它是一种XML解析的替代方法，不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式，它逐行扫描文档，一边扫描，一边解析。所以那些只需要单遍读取内容的应用程序就可以从SAX解析中受益，这对大型文档的解析是个巨大优势。另外，SAX “推" 模型可用于广播环境，能够同时注册多个ContentHandler，并行接收事件，而不是在一个管道中一个接一个地进行处理。一些支持 SAX 的语法分析器包括 Xerces，Apache parser（以前的 IBM 语法分析器）、MSXML（Microsoft 语法分析器）和 XDK（Oracle 语法分析器）。这些语法分析器是最灵活的，因为它们还支持 DOM。

3. POI以SAX解析excel2007文件

所需jar包：poi-3.10-FINAL-20140208.jar，poi-ooxml-3.10-FINAL-20140208.jar， poi-ooxml-schemas-3.10-FINAL-20140208.jar

辅助工具：Open XML SDK 2.5

原始文件：book1.xlsx

SDK展示：注意其中第四行只有三个cell元素，第五行只有两个cell元素，而这种空单元格的处理正是我们所要注意的

程序源码：

1. package test;
2. import java.io.BufferedWriter;
3. import java.io.FileOutputStream;
4. import java.io.IOException;
5. import java.io.InputStream;
6. import java.io.OutputStreamWriter;
7. import java.sql.SQLException;
8. import java.util.ArrayList;
9. import java.util.Iterator;
10. import java.util.List;
11. import org.apache.poi.openxml4j.opc.OPCPackage;
12. import org.apache.poi.ss.usermodel.BuiltinFormats;
13. import org.apache.poi.ss.usermodel.DataFormatter;
14. import org.apache.poi.xssf.eventusermodel.XSSFReader;
15. import org.apache.poi.xssf.model.SharedStringsTable;
16. import org.apache.poi.xssf.model.StylesTable;
17. import org.apache.poi.xssf.usermodel.XSSFCellStyle;
18. import org.apache.poi.xssf.usermodel.XSSFRichTextString;
19. import org.xml.sax.Attributes;
20. import org.xml.sax.ContentHandler;
21. import org.xml.sax.InputSource;
22. import org.xml.sax.SAXException;
23. import org.xml.sax.XMLReader;
24. import org.xml.sax.helpers.DefaultHandler;
25. import org.xml.sax.helpers.XMLReaderFactory;
26. public class ExampleEventUserModel {
27. private static StylesTable stylesTable;
28. /**
29. * 处理一个sheet
30. * @param filename
31. * @throws Exception
32. */
33. public void processOneSheet(String filename) throws Exception {
34. OPCPackage pkg = OPCPackage.open(filename);
35. XSSFReader r = new XSSFReader( pkg );
36. stylesTable = r.getStylesTable();
37. SharedStringsTable sst = r.getSharedStringsTable();
38. XMLReader parser = fetchSheetParser(sst);
39. // Seems to either be rId# or rSheet#
40. InputStream sheet2 = r.getSheet("rId1");
41. InputSource sheetSource = new InputSource(sheet2);
42. parser.parse(sheetSource);
43. sheet2.close();
44. }
45. /**
46. * 处理所有sheet
47. * @param filename
48. * @throws Exception
49. */
50. public void processAllSheets(String filename) throws Exception {
51. OPCPackage pkg = OPCPackage.open(filename);
52. XSSFReader r = new XSSFReader( pkg );
53. SharedStringsTable sst = r.getSharedStringsTable();
54. XMLReader parser = fetchSheetParser(sst);
55. Iterator<InputStream> sheets = r.getSheetsData();
56. while(sheets.hasNext()) {
57. System.out.println("Processing new sheet:\n");
58. InputStream sheet = sheets.next();
59. InputSource sheetSource = new InputSource(sheet);
60. parser.parse(sheetSource);
61. sheet.close();
62. System.out.println("");
63. }
64. }
65. /**
66. * 获取解析器
67. * @param sst
68. * @return
69. * @throws SAXException
70. */
71. public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
72. XMLReader parser =
73. XMLReaderFactory.createXMLReader(
74. "org.apache.xerces.parsers.SAXParser"
75. );
76. ContentHandler handler = new SheetHandler(sst);
77. parser.setContentHandler(handler);
78. return parser;
79. }
80. /**
81. * 自定义解析处理器
82. * See org.xml.sax.helpers.DefaultHandler javadocs
83. */
84. private static class SheetHandler extends DefaultHandler {
85. private SharedStringsTable sst;
86. private String lastContents;
87. private boolean nextIsString;
88. private List<String> rowlist = new ArrayList<String>();
89. private int curRow = 0;
90. private int curCol = 0;
91. //定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
92. private String preRef = null, ref = null;
93. //定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
94. private String maxRef = null;
95. private CellDataType nextDataType = CellDataType.SSTINDEX;
96. private final DataFormatter formatter = new DataFormatter();
97. private short formatIndex;
98. private String formatString;
99. //用一个enum表示单元格可能的数据类型
100. enum CellDataType{
101. BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
102. }
103. private SheetHandler(SharedStringsTable sst) {
104. this.sst = sst;
105. }
106. /**
107. * 解析一个element的开始时触发事件
108. */
109. public void startElement(String uri, String localName, String name,
110. Attributes attributes) throws SAXException {
111. // c => cell
112. if(name.equals("c")) {
113. //前一个单元格的位置
114. if(preRef == null){
115. preRef = attributes.getValue("r");
116. }else{
117. preRef = ref;
118. }
119. //当前单元格的位置
120. ref = attributes.getValue("r");
121. this.setNextDataType(attributes);
122. // Figure out if the value is an index in the SST
123. String cellType = attributes.getValue("t");
124. if(cellType != null && cellType.equals("s")) {
125. nextIsString = true;
126. } else {
127. nextIsString = false;
128. }
129. }
130. // Clear contents cache
131. lastContents = "";
132. }
133. /**
134. * 根据element属性设置数据类型
135. * @param attributes
136. */
137. public void setNextDataType(Attributes attributes){
138. nextDataType = CellDataType.NUMBER;
139. formatIndex = -1;
140. formatString = null;
141. String cellType = attributes.getValue("t");
142. String cellStyleStr = attributes.getValue("s");
143. if ("b".equals(cellType)){
144. nextDataType = CellDataType.BOOL;
145. }else if ("e".equals(cellType)){
146. nextDataType = CellDataType.ERROR;
147. }else if ("inlineStr".equals(cellType)){
148. nextDataType = CellDataType.INLINESTR;
149. }else if ("s".equals(cellType)){
150. nextDataType = CellDataType.SSTINDEX;
151. }else if ("str".equals(cellType)){
152. nextDataType = CellDataType.FORMULA;
153. }
154. if (cellStyleStr != null){
155. int styleIndex = Integer.parseInt(cellStyleStr);
156. XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
157. formatIndex = style.getDataFormat();
158. formatString = style.getDataFormatString();
159. if ("m/d/yy" == formatString){
160. nextDataType = CellDataType.DATE;
161. //full format is "yyyy-MM-dd hh:mm:ss.SSS";
162. formatString = "yyyy-MM-dd";
163. }
164. if (formatString == null){
165. nextDataType = CellDataType.NULL;
166. formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
167. }
168. }
169. }
170. /**
171. * 解析一个element元素结束时触发事件
172. */
173. public void endElement(String uri, String localName, String name)
174. throws SAXException {
175. // Process the last contents as required.
176. // Do now, as characters() may be called more than once
177. if(nextIsString) {
178. int idx = Integer.parseInt(lastContents);
179. lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
180. nextIsString = false;
181. }
182. // v => contents of a cell
183. // Output after we've seen the string contents
184. if (name.equals("v")) {
185. String value = this.getDataValue(lastContents.trim(), "");
186. //补全单元格之间的空单元格
187. if(!ref.equals(preRef)){
188. int len = countNullCell(ref, preRef);
189. for(int i=0;i<len;i++){
190. rowlist.add(curCol, "");
191. curCol++;
192. }
193. }
194. rowlist.add(curCol, value);
195. curCol++;
196. }else {
197. //如果标签名称为 row，这说明已到行尾，调用 optRows() 方法
198. if (name.equals("row")) {
199. String value = "";
200. //默认第一行为表头，以该行单元格数目为最大数目
201. if(curRow == 0){
202. maxRef = ref;
203. }
204. //补全一行尾部可能缺失的单元格
205. if(maxRef != null){
206. int len = countNullCell(maxRef, ref);
207. for(int i=0;i<=len;i++){
208. rowlist.add(curCol, "");
209. curCol++;
210. }
211. }
212. //拼接一行的数据
213. for(int i=0;i<rowlist.size();i++){
214. if(rowlist.get(i).contains(",")){
215. value += "\""+rowlist.get(i)+"\",";
216. }else{
217. value += rowlist.get(i)+",";
218. }
219. }
220. //加换行符
221. value += "\n";
222. try {
223. writer.write(value);
224. } catch (IOException e) {
225. e.printStackTrace();
226. }
227. curRow++;
228. //一行的末尾重置一些数据
229. rowlist.clear();
230. curCol = 0;
231. preRef = null;
232. ref = null;
233. }
234. }
235. }
236. /**
237. * 根据数据类型获取数据
238. * @param value
239. * @param thisStr
240. * @return
241. */
242. public String getDataValue(String value, String thisStr)
243. {
244. switch (nextDataType)
245. {
246. //这几个的顺序不能随便交换，交换了很可能会导致数据错误
247. case BOOL:
248. char first = value.charAt(0);
249. thisStr = first == '0' ? "FALSE" : "TRUE";
250. break;
251. case ERROR:
252. thisStr = "\"ERROR:" + value.toString() + '"';
253. break;
254. case FORMULA:
255. thisStr = '"' + value.toString() + '"';
256. break;
257. case INLINESTR:
258. XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
259. thisStr = rtsi.toString();
260. rtsi = null;
261. break;
262. case SSTINDEX:
263. String sstIndex = value.toString();
264. thisStr = value.toString();
265. break;
266. case NUMBER:
267. if (formatString != null){
268. thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
269. }else{
270. thisStr = value;
271. }
272. thisStr = thisStr.replace("_", "").trim();
273. break;
274. case DATE:
275. try{
276. thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
277. }catch(NumberFormatException ex){
278. thisStr = value.toString();
279. }
280. thisStr = thisStr.replace(" ", "");
281. break;
282. default:
283. thisStr = "";
284. break;
285. }
286. return thisStr;
287. }
288. /**
289. * 获取element的文本数据
290. */
291. public void characters(char[] ch, int start, int length)
292. throws SAXException {
293. lastContents += new String(ch, start, length);
294. }
295. /**
296. * 计算两个单元格之间的单元格数目(同一行)
297. * @param ref
298. * @param preRef
299. * @return
300. */
301. public int countNullCell(String ref, String preRef){
302. //excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
303. String xfd = ref.replaceAll("\\d+", "");
304. String xfd_1 = preRef.replaceAll("\\d+", "");
305. xfd = fillChar(xfd, 3, '@', true);
306. xfd_1 = fillChar(xfd_1, 3, '@', true);
307. char[] letter = xfd.toCharArray();
308. char[] letter_1 = xfd_1.toCharArray();
309. int res = (letter[0]-letter_1[0])*26*26 + (letter[1]-letter_1[1])*26 + (letter[2]-letter_1[2]);
310. return res-1;
311. }
312. /**
313. * 字符串的填充
314. * @param str
315. * @param len
316. * @param let
317. * @param isPre
318. * @return
319. */
320. String fillChar(String str, int len, char let, boolean isPre){
321. int len_1 = str.length();
322. if(len_1 <len){
323. if(isPre){
324. for(int i=0;i<(len-len_1);i++){
325. str = let+str;
326. }
327. }else{
328. for(int i=0;i<(len-len_1);i++){
329. str = str+let;
330. }
331. }
332. }
333. return str;
334. }
335. }
336. static BufferedWriter writer = null;
337. public static void main(String[] args) throws Exception {
338. ExampleEventUserModel example = new ExampleEventUserModel();
339. String str = "Book1";
340. String filename = "D:\\"+str+".xlsx ";
341. System.out.println("-- 程序开始 --");
342. long time_1 = System.currentTimeMillis();
343. try{
344. writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("D:\\"+str+".csv")));
345. example.processOneSheet(filename);
346. }finally{
347. writer.close();
348. }
349. long time_2 = System.currentTimeMillis();
350. System.out.println("-- 程序结束 --");
351. System.out.println("-- 耗时 --"+(time_2 - time_1)+"ms");
352. }
353. }

最后输出结果：

1. 书目,作者,主题,语言,分类,阅读计划,
2. 生活在别处,米兰.昆德拉,人生感悟,英语,哲理,3周,
3. 人间词话,王国维,诗词评论,中文,文学,5周,
4. 宽容,房龙,,英语,,,
5. 深入理解计算机系统,,,,计算机,,

本文实例主要讲述了Java生成CSV文件的方法，具体实现步骤如下：

1、新建CSVUtils.java文件：

package com.saicfc.pmpf.internal.manage.utils;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;

/**
 * 文件操作
 */
public class CSVUtils {

    /**
     * 生成为CVS文件
     *
     * @param exportData 源数据List
     * @param map csv文件的列表头map
     * @param outPutPath 文件路径
     * @param fileName 文件名称
     * @return
     */
    @SuppressWarnings("rawtypes")
    public static File createCSVFile(List exportData, LinkedHashMap map,
            String outPutPath, String fileName) {
        File csvFile = null;
        BufferedWriter csvFileOutputStream = null;
        try {
            File file = new File(outPutPath);
            if (!file.exists()) {
                file.mkdir();
            }
            // 定义文件名格式并创建
            csvFile = File.createTempFile(fileName, ".csv", new File(outPutPath));
            System.out.println("csvFile：" + csvFile);
            // UTF-8使正确读取分隔符","
            csvFileOutputStream = new BufferedWriter(
                    new OutputStreamWriter(
                            new FileOutputStream(csvFile), "UTF-8"),1024);
            System.out.println("csvFileOutputStream：" + csvFileOutputStream);
            // 写入文件头部
            for (Iterator propertyIterator = map.entrySet().iterator();
                    propertyIterator.hasNext();) {
                java.util.Map.Entry propertyEntry =
                        (java.util.Map.Entry) propertyIterator.next();
                csvFileOutputStream.write(
                        "" + (String) propertyEntry.getValue() != null ?
                                (String) propertyEntry.getValue() : "" + "");
                if (propertyIterator.hasNext()) {
                    csvFileOutputStream.write(",");
                }
            }
            csvFileOutputStream.newLine();
            // 写入文件内容
            for (Iterator iterator = exportData.iterator(); iterator.hasNext();) {
                Object row = (Object) iterator.next();
                for (Iterator propertyIterator = map.entrySet().iterator();
                        propertyIterator.hasNext();) {
                    java.util.Map.Entry propertyEntry =
                            (java.util.Map.Entry) propertyIterator.next();
                    csvFileOutputStream.write((String) BeanUtils.getProperty(
                            row, (String) propertyEntry.getKey()));
                    if (propertyIterator.hasNext()) {
                        csvFileOutputStream.write(",");
                    }
                }
                if (iterator.hasNext()) {
                    csvFileOutputStream.newLine();
                }
            }
            csvFileOutputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                csvFileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return csvFile;
    }

    /**
     * 下载文件
     *
     * @param response
     * @param csvFilePath 文件路径
     * @param fileName 文件名称
     * @throws IOException
     */
    public static void exportFile(HttpServletResponse response,
            String csvFilePath, String fileName)
            throws IOException {
        response.setContentType("application/csv;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" +
                    URLEncoder.encode(fileName, "UTF-8"));

        InputStream in = null;
        try {
            in = new FileInputStream(csvFilePath);
            int len = 0;
            byte[] buffer = new byte[1024];
            response.setCharacterEncoding("UTF-8");
            OutputStream out = response.getOutputStream();
            while ((len = in.read(buffer)) > 0) {
                out.write(new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF });
                out.write(buffer, 0, len);
            }
        } catch (FileNotFoundException e) {
            System.out.println(e);
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }

    /**
     * 删除该目录filePath下的所有文件
     *
     * @param filePath 文件目录路径
     */
    public static void deleteFiles(String filePath) {
        File file = new File(filePath);
        if (file.exists()) {
            File[] files = file.listFiles();
            for (int i = 0; i < files.length; i++) {
                if (files[i].isFile()) {
                    files[i].delete();
                }
            }
        }
    }

    /**
     * 删除单个文件
     *
     * @param filePath 文件目录路径
     * @param fileName 文件名称
     */
    public static void deleteFile(String filePath, String fileName) {
        File file = new File(filePath);
        if (file.exists()) {
            File[] files = file.listFiles();
            for (int i = 0; i < files.length; i++) {
                if (files[i].isFile()) {
                    if (files[i].getName().equals(fileName)) {
                        files[i].delete();
                        return;
                    }
                }
            }
        }
    }

    /**
     * 测试数据
     *
     * @param args
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static void main(String[] args) {
        List exportData = new ArrayList<Map>();
        Map row1 = new LinkedHashMap<String, String>();
        row1.put("1", "11");
        row1.put("2", "12");
        row1.put("3", "13");
        row1.put("4", "14");
        exportData.add(row1);
        row1 = new LinkedHashMap<String, String>();
        row1.put("1", "21");
        row1.put("2", "22");
        row1.put("3", "23");
        row1.put("4", "24");
        exportData.add(row1);
        LinkedHashMap map = new LinkedHashMap();
        map.put("1", "第一列");
        map.put("2", "第二列");
        map.put("3", "第三列");
        map.put("4", "第四列");

        String path = "c:/export/";
        String fileName = "文件导出";
        File file = CSVUtils.createCSVFile(exportData, map, path, fileName);
        String fileName2 = file.getName();
        System.out.println("文件名称：" + fileName2);
    }
}

2、调用createCSVFile方法生成CSV文件

　　public static void main(String[] args) {
        String name = "银行退款数据";
        List exportData = new ArrayList();
        LinkedHashMap datamMap = null;
        for (Iterator iterator = refundList.iterator(); iterator.hasNext();) {
            HashMap map = (HashMap) iterator.next();
            datamMap = new LinkedHashMap();
            datamMap.put("1", map.get("merOrderId"));
            datamMap.put("2", DateUtil.convertDateToString("yyyyMMdd",
                    (Date) map.get("orderTime")));
            BigDecimal amount = (BigDecimal) map.get("amount");
            String amountString = amount.divide(new BigDecimal(10)).toPlainString();
            datamMap.put("3", amountString);
            datamMap.put("4", map.get("remark") != null ? map.get("remark") : "");
            exportData.add(datamMap);
        }
        LinkedHashMap map = new LinkedHashMap();
        map.put("1", "订单号");
        map.put("2", "支付日期");
        map.put("3", "退货现金金额（整数金额 单位：分）");
        map.put("4", "退货原因");
        File file = CSVUtils.createCSVFile(exportData, map, filePath, name);// 生成CSV文件
        fileName = file.getName();
        CSVUtils.exportFile(response, filePath + fileName, fileName);// 下载生成的CSV文件
    }

本文转自：http://www.jb51.net/article/52724.htm