用Java將Excel的xls和xlsx文件轉換成csv文件, XLS2CSV, XLSX2CSV – JAVA編程語言程序開發技術文章

在做的項目中遇到需要處理Excel的多種輸出文件,包括csv, xls, xlsx這三種文件, 所以想先將後兩種合適都轉換到csv格式再一起解析. 於是就去找到瞭下面這兩個類, 兩個好像都是apache官方的例子.來源鏈接:


 


XLS2CSV: http://www.docjar.com/html/api/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java.html
XLSX2CSV: https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java


上面兩個地址中後面一個好像是被墻的,我也是翻過瞭墻才勉強訪問到.為瞭方便大傢我把代碼貼到下面,
不過跟上面的有一點區別: 我自己在兩個類中都多加瞭一個構造器,把功能封裝到隻要傳入來源文件和輸出文件路徑就可以用瞭
使用方法示例請看代碼最後面的main方法.


依賴的包在本頁最下面提供…


XLS2CSV:


  1/**//* ====================================================================
  2Licensed to the Apache Software Foundation (ASF) under one or more
  3contributor license agreements. See the NOTICE file distributed with
  4this work for additional information regarding copyright ownership.
  5The ASF licenses this file to You under the Apache License, Version 2.0
  6(the “License”); you may not use this file except in compliance with
  7the License. You may obtain a copy of the License at
  8
  9http://www.apache.org/licenses/LICENSE-2.0
 10
 11Unless required by applicable law or agreed to in writing, software
 12distributed under the License is distributed on an “AS IS” BASIS,
 13WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 14See the License for the specific language governing permissions and
 15limitations under the License.
 16==================================================================== */
 17package cn.jayslong.convert;
 18
 19import java.io.FileInputStream;
 20import java.io.FileNotFoundException;
 21import java.io.IOException;
 22import java.io.PrintStream;
 23import java.util.ArrayList;
 24
 25import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
 26import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
 27import org.apache.poi.hssf.eventusermodel.HSSFListener;
 28import org.apache.poi.hssf.eventusermodel.HSSFRequest;
 29import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
 30import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
 31import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
 32import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
 33import org.apache.poi.hssf.model.HSSFFormulaParser;
 34import org.apache.poi.hssf.record.BOFRecord;
 35import org.apache.poi.hssf.record.BlankRecord;
 36import org.apache.poi.hssf.record.BoolErrRecord;
 37import org.apache.poi.hssf.record.BoundSheetRecord;
 38import org.apache.poi.hssf.record.FormulaRecord;
 39import org.apache.poi.hssf.record.LabelRecord;
 40import org.apache.poi.hssf.record.LabelSSTRecord;
 41import org.apache.poi.hssf.record.NoteRecord;
 42import org.apache.poi.hssf.record.NumberRecord;
 43import org.apache.poi.hssf.record.RKRecord;
 44import org.apache.poi.hssf.record.Record;
 45import org.apache.poi.hssf.record.SSTRecord;
 46import org.apache.poi.hssf.record.StringRecord;
 47import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 48import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 49
 50/** *//**
 51 * A XLS -> CSV processor, that uses the MissingRecordAware EventModel code to
 52 * ensure it outputs all columns and rows.
 53 *
 54 * @author Nick Burch
 55 */
 56public class XLS2CSV implements HSSFListener {
 57    private int minColumns;
 58    private POIFSFileSystem fs;
 59    private PrintStream output;
 60
 61    private int lastRowNumber;
 62    private int lastColumnNumber;
 63
 64    /** *//** Should we output the formula, or the value it has? */
 65    private boolean outputFormulaValues = true;
 66
 67    /** *//** For parsing Formulas */
 68    private SheetRecordCollectingListener workbookBuildingListener;
 69    private HSSFWorkbook stubWorkbook;
 70
 71    // Records we pick up as we process
 72    private SSTRecord sstRecord;
 73    private FormatTrackingHSSFListener formatListener;
 74
 75    /** *//** So we known which sheet were on */
 76    private int sheetIndex = -1;
 77    private BoundSheetRecord[] orderedBSRs;
 78    private ArrayList boundSheetRecords = new ArrayList();
 79
 80    // For handling formulas with string results
 81    private int nextRow;
 82    private int nextColumn;
 83    private boolean outputNextStringRecord;
 84   
 85    private final String OUTPUT_CHARSET = “GBK”;
 86
 87    /** *//**
 88     * Creates a new XLS -> CSV converter
 89     *
 90     * @param fs
 91     *            The POIFSFileSystem to process
 92     * @param output
 93     *            The PrintStream to output the CSV to
 94     * @param minColumns
 95     *            The minimum number of columns to output, or -1 for no minimum
 96     */
 97    public XLS2CSV(POIFSFileSystem fs, PrintStream output, int minColumns) {
 98        this.fs = fs;
 99        this.output = output;
100        this.minColumns = minColumns;
101    }
102   
103    public XLS2CSV(String inputFilePath, String outputFilePath) throws Exception {
104        fs = new POIFSFileSystem(new FileInputStream(inputFilePath));
105        output = new PrintStream(outputFilePath, OUTPUT_CHARSET);
106        minColumns = -1;
107    }
108
109    /** *//**
110     * Creates a new XLS -> CSV converter
111     *
112     * @param filename
113     *            The file to process
114     * @param minColumns
115     *            The minimum number of columns to output, or -1 for no minimum
116     * @t

發佈留言