1 package com.poi.dealXlsx;
  2 
  3 import org.apache.poi.openxml4j.exceptions.OpenXML4JException;  
  4 
  5 /* ==================================================================== 
  6         Licensed to the Apache Software Foundation (ASF) under one or more 
  7         contributor license agreements.  See the NOTICE file distributed with 
  8         this work for additional information regarding copyright ownership. 
  9         The ASF licenses this file to You under the Apache License, Version 2.0 
 10         (the "License"); you may not use this file except in compliance with 
 11         the License.  You may obtain a copy of the License at 
 12  
 13         http://www.apache.org/licenses/LICENSE-2.0 
 14  
 15         Unless required by applicable law or agreed to in writing, software 
 16         distributed under the License is distributed on an "AS IS" BASIS, 
 17         WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 
 18         See the License for the specific language governing permissions and 
 19         limitations under the License. 
 20         ==================================================================== */  
 21   
 22   
 23 import java.io.File;  
 24 import java.io.IOException;  
 25 import java.io.InputStream;  
 26 import java.io.PrintStream;  
 27   
 28 import javax.xml.parsers.ParserConfigurationException;  
 29   
 30 import org.apache.poi.openxml4j.opc.OPCPackage;  
 31 import org.apache.poi.openxml4j.opc.PackageAccess;  
 32 import org.apache.poi.ss.usermodel.DataFormatter;  
 33 import org.apache.poi.ss.util.CellAddress;  
 34 import org.apache.poi.ss.util.CellReference;  
 35 import org.apache.poi.util.SAXHelper;  
 36 import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;  
 37 import org.apache.poi.xssf.eventusermodel.XSSFReader;  
 38 import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;  
 39 import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;  
 40 import org.apache.poi.xssf.extractor.XSSFEventBasedExcelExtractor;  
 41 import org.apache.poi.xssf.model.StylesTable;  
 42 import org.apache.poi.xssf.usermodel.XSSFComment;  
 43 import org.xml.sax.ContentHandler;  
 44 import org.xml.sax.InputSource;  
 45 import org.xml.sax.SAXException;  
 46 import org.xml.sax.XMLReader;  
 47   
 48 /** 
 49      *一个基本的XLSX - > CSV处理器
 50      * POI样本程序XLS2CSVmra从包中
 51      * org.apache.poi.hssf.eventusermodel.examples。
 52      *与HSSF版本一样，这会试图找到错误
 53      *行和单元格，并为它们输出空条目。
 54      * <p />
 55      *使用SAX解析器读取数据表以保持
 56      *内存占用比较小，所以这应该是
 57      *能够阅读庞大的工作簿。样式表和
 58      *共享字符串表必须保存在内存中。该
 59      *标准POI样式表类使用，但是一个自定义
 60      *（只读）类用于共享字符串表
 61      *因为标准的POI SharedStringsTable增长很大
 62      *快速与唯一字符串的数量。
 63      * <p />
 64      *更高级的SAX事件解析实现
 65      *的XLSX文件，请参阅{@link XSSFEventBasedExcelExtractor}
 66      *和{@link XSSFSheetXMLHandler}。请注意，在很多情况下，
 67      *可以简单地使用那些与习惯
 68      * {@link SheetContentsHandler}并且不需要SAX代码
 69      * 你自己！ 
 70  */  
 71 public class XLSX2CSV {  
 72     /**
 73           *使用XSSF Event SAX助手进行大部分工作
 74           *解析Sheet XML，并输出内容
 75           *作为（基本）CSV。
 76          */ 
 77     private class SheetToCSV implements SheetContentsHandler {  
 78         private boolean firstCellOfRow = false;  
 79         private int currentRow = -1;  
 80         private int currentCol = -1;  
 81   
 82         /**
 83          * 输出缺失的行
 84          * @param number
 85          */
 86         private void outputMissingRows(int number) {  
 87             for (int i = 0; i < number; i++) {  
 88                 for (int j = 0; j < minColumns; j++) {  
 89                     output.append(‘,‘);  
 90                 }  
 91                 output.append(‘\n‘);  
 92             }  
 93         }  
 94   
 95         @Override  
 96         public void startRow(int rowNum) {  
 97             // If there were gaps, output the missing rows  
 98             outputMissingRows(rowNum - currentRow - 1);  
 99             // Prepare for this row  
100             firstCellOfRow = true;  
101             currentRow = rowNum;  
102             currentCol = -1;  
103         }  
104   
105         @Override  
106         public void endRow(int rowNum) {  
107             // Ensure the minimum number of columns  
108             for (int i = currentCol; i < minColumns; i++) {  
109                 output.append(‘,‘);  
110             }  
111             output.append(‘\n‘);  
112         }  
113   
114         @Override  
115         public void cell(String cellReference, String formattedValue,XSSFComment comment) {  
116             if (firstCellOfRow) {  
117                 firstCellOfRow = false;  
118             } else {  
119                 output.append(‘,‘);  
120             }  
121   
122             // gracefully handle missing CellRef here in a similar way as XSSFCell does  
123             if (cellReference == null) {  
124                 cellReference = new CellAddress(currentRow, currentCol).formatAsString();  
125             }  
126   
127             // Did we miss any cells?  
128             int thisCol = (new CellReference(cellReference)).getCol();  
129             int missedCols = thisCol - currentCol - 1;  
130             for (int i = 0; i < missedCols; i++) {  
131                 output.append(‘,‘);  
132             }  
133             currentCol = thisCol;  
134   
135             // Number or string?  
136             try {  
137                 Double.parseDouble(formattedValue);  
138                 output.append(formattedValue);  
139             } catch (NumberFormatException e) {  
140                 output.append(‘"‘);  
141                 output.append(formattedValue);  
142                 output.append(‘"‘);  
143             }  
144         }  
145   
146         @Override  
147         public void headerFooter(String text, boolean isHeader, String tagName) {  
148             // Skip, no headers or footers in CSV  
149         }  
150     }  
151   
152 
153   
154     /**
155      * 表示可以存储多个数据对象的容器。
156      */
157     private final OPCPackage xlsxPackage;  
158   
159     /** 
160      * 以最左边开始读取的列数
161      */  
162     private final int minColumns;  
163   
164     /** 
165      * 写出数据流
166      */  
167     private final PrintStream output;  
168   
169     /** 
170      * 创建一个新的XLSX -> CSV转换器
171      * 
172      * @param pkg        The XLSX package to process 
173      * @param output     The PrintStream to output the CSV to 
174      * @param minColumns 要输出的最小列数，或-1表示最小值
175      */  
176     public XLSX2CSV(OPCPackage pkg, PrintStream output, int minColumns) {  
177         this.xlsxPackage = pkg;  
178         this.output = output;  
179         this.minColumns = minColumns;  
180     }  
181   
182     /** 
183      * Parses and shows the content of one sheet 
184      * using the specified styles and shared-strings tables. 
185      *解析并显示一个工作簿的内容
186             *使用指定的样式和共享字符串表。
187      * @param styles     工作簿中所有工作表共享的样式表。
188      * @param strings     这是处理共享字符串表的轻量级方式。 大多数文本单元格将引用这里的内容。请注意，如果字符串由不同格式的位组成，则每个SI条目都可以有多个T元素
189      * @param sheetInputStream         
190      */  
191     public void processSheet(StylesTable styles,
192             ReadOnlySharedStringsTable strings,
193             SheetContentsHandler sheetHandler, InputStream sheetInputStream)
194             throws IOException, ParserConfigurationException, SAXException {
195         DataFormatter formatter = new DataFormatter();
196         InputSource sheetSource = new InputSource(sheetInputStream);
197         try {
198             XMLReader sheetParser = SAXHelper.newXMLReader();
199             ContentHandler handler = new XSSFSheetXMLHandler(styles, null,
200                     strings, sheetHandler, formatter, false);
201             sheetParser.setContentHandler(handler);
202             sheetParser.parse(sheetSource);
203         } catch (ParserConfigurationException e) {
204             throw new RuntimeException("SAX parser appears to be broken - "
205                     + e.getMessage());
206         }
207     }
208   
209     /** 
210      * Initiates the processing of the XLS workbook file to CSV. 
211      * 启动将XLS工作簿文件处理为CSV。
212      * 
213      * @throws IOException 
214      * @throws OpenXML4JException 
215      * @throws ParserConfigurationException 
216      * @throws SAXException 
217      */  
218     public void process()  
219             throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {  
220         ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);  
221         XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);  
222         StylesTable styles = xssfReader.getStylesTable();  
223         XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();  
224         int index = 0;  
225         while (iter.hasNext()) {  
226             InputStream stream = iter.next();  
227             String sheetName = iter.getSheetName();  
228             this.output.println();  
229             this.output.println(sheetName + " [index=" + index + "]:");  
230             processSheet(styles, strings, new SheetToCSV(), stream);  
231             stream.close();  
232             ++index;  
233         }  
234     }  
235   
236     public static void main(String[] args) throws Exception {  
237       /*  if (args.length < 1) { 
238             System.err.println("Use:"); 
239             System.err.println("  XLSX2CSV <xlsx file> [min columns]"); 
240             return; 
241         }*/  
242   
243         File xlsxFile = new File("D:/基因数据测试/S1.xlsx");  
244         if (!xlsxFile.exists()) {  
245             System.err.println("没找到文件: " + xlsxFile.getPath());  
246             return;  
247         }  
248   
249         int minColumns = -1;  
250         if (args.length >= 2)  
251             minColumns = Integer.parseInt(args[1]);  
252   
253         // The package open is instantaneous, as it should be.  
254         OPCPackage p = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ);  
255         XLSX2CSV xlsx2csv = new XLSX2CSV(p, System.out, minColumns);  
256         xlsx2csv.process();  
257         p.close();  
258     }  
259 }