java 读取excel 转换csv_用Java将Excel的xls和xlsx文件转换成csv文件的方法, XLS2CSV, XLSX2CSV...

利用poi将excel文件后缀为.xls .xlsx的文件转换成txt/csv文本文件

首先，引入所需的jar包：

2

3 net.sf.opencsv

4 opencsv

5 2.1

6

7

8 org.apache.poi

9 ooxml-schemas

10 1.1

11 pom

12

13

14 org.apache.poi

15 poi

16 3.7

17

18

19 org.apache.poi

20 ooxml-schemas

21 1.1

22

23

24 org.apache.poi

25 poi-ooxml

26 3.7

27

28

29 dom4j

30 dom4j

31 1.6.1

32

33

完整代码

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.WorkbookSettings;

import java.io.*;

import java.util.Locale;

/**

* @ClassName: ExcelToCsv

* @Description:转换excel成csv格式

* @Author: mashiwei

*/

public class ExcelToCsv {

/**

*将excel(xls/xlsx)转换成csv文件

* @param excelFile

* @param csvFile

* @return String

*/

public static String getCsv(String excelFile, String csvFile) {

//.xlsx文件后缀转成csv

if (excelFile.endsWith(".xlsx")){

XLSX2CSV.trans(excelFile,csvFile);

return csvFile;

}

//.xls文件后缀转成csv

else {

try {

OutputStream os = new FileOutputStream(new File(csvFile));

OutputStreamWriter osw = new OutputStreamWriter(os, "UTF8");

BufferedWriter bw = new BufferedWriter(osw);

// 载入Excel文件

WorkbookSettings ws = new WorkbookSettings();

ws.setLocale(new Locale("en", "EN"));

Workbook wk = Workbook.getWorkbook(new File(excelFile), ws);

// 从工作簿(workbook)取得每页(sheets)

for (int sheet = 0; sheet < wk.getNumberOfSheets(); sheet++) {

Sheet s = wk.getSheet(sheet);

Cell[] row = null;

// 从每页(sheet)取得每个区块(Cell)

for (int i = 0; i < s.getRows(); i++) {

row = s.getRow(i);

if (row.length > 0) {

bw.write(row[0].getContents());

for (int j = 1; j < row.length; j++) {

//写入分隔符

bw.write(‘,‘);

bw.write(row[j].getContents());

}

}

bw.newLine();

}

}

bw.flush();

bw.close();

} catch (Exception e) {

System.err.println(e.toString());

e.printStackTrace();

}

return csvFile;

}

}

}

package com.ys.penspark.steps.excelinput;

import org.apache.poi.openxml4j.exceptions.OpenXML4JException;

import org.apache.poi.openxml4j.opc.OPCPackage;

import org.apache.poi.openxml4j.opc.PackageAccess;

import org.apache.poi.ss.usermodel.BuiltinFormats;

import org.apache.poi.ss.usermodel.DataFormatter;

import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;

import org.apache.poi.xssf.eventusermodel.XSSFReader;

import org.apache.poi.xssf.model.StylesTable;

import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import org.xml.sax.*;

import org.xml.sax.helpers.DefaultHandler;

import javax.xml.parsers.ParserConfigurationException;

import javax.xml.parsers.SAXParser;

import javax.xml.parsers.SAXParserFactory;

import java.io.IOException;

import java.io.InputStream;

import java.io.PrintStream;

public class XLSX2CSV {

/**

* The type of the data value is indicated by an attribute on the cell. The

* value is usually in a "v" element within the cell.

*/

enum xssfDataType {

BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,

}

class MyXSSFSheetHandler extends DefaultHandler {

/**

* Table with styles

*/

private StylesTable stylesTable;

/**

* Table with unique strings

*/

private ReadOnlySharedStringsTable sharedStringsTable;

/**

* Destination for data

*/

private final PrintStream output;

/**

* Number of columns to read starting with leftmost

*/

private final int minColumnCount;

// Set when V start element is seen

private boolean vIsOpen;

// Set when cell start element is seen;

// used when cell close element is seen.

private xssfDataType nextDataType;

// Used to format numeric cell values.

private short formatIndex;

private String formatString;

private final DataFormatter formatter;

private int thisColumn = -1;

// The last column printed to the output stream

private int lastColumnNumber = -1;

// Gathers characters as they are seen.

private StringBuffer value;

public MyXSSFSheetHandler(StylesTable styles,

ReadOnlySharedStringsTable strings, int cols, PrintStream target) {

this.stylesTable = styles;

this.sharedStringsTable = strings;

this.minColumnCount = cols;

this.output = target;

this.value = new StringBuffer();

this.nextDataType = xssfDataType.NUMBER;

this.formatter = new DataFormatter();

}

public void startElement(String uri, String localName, String name,

Attributes attributes) throws SAXException {

if ("inlineStr".equals(name) || "v".equals(name)) {

vIsOpen = true;

// Clear contents cache

value.setLength(0);

}

// c => cell

else if ("c".equals(name)) {

// Get the cell reference

String r = attributes.getValue("r");

int firstDigit = -1;

for (int c = 0; c < r.length(); ++c) {

if (Character.isDigit(r.charAt(c))) {

firstDigit = c;

break;

}

}

thisColumn = nameToColumn(r.substring(0, firstDigit));

// Set up defaults.

this.nextDataType = xssfDataType.NUMBER;

this.formatIndex = -1;

this.formatString = null;

String cellType = attributes.getValue("t");

String cellStyleStr = attributes.getValue("s");

if ("b".equals(cellType))

nextDataType = xssfDataType.BOOL;

else if ("e".equals(cellType))

nextDataType = xssfDataType.ERROR;

else if ("inlineStr".equals(cellType))

nextDataType = xssfDataType.INLINESTR;

else if ("s".equals(cellType))

nextDataType = xssfDataType.SSTINDEX;

else if ("str".equals(cellType))

nextDataType = xssfDataType.FORMULA;

else if (cellStyleStr != null) {

// It‘s a number, but almost certainly one

// with a special style or format

int styleIndex = Integer.parseInt(cellStyleStr);

XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);

this.formatIndex = style.getDataFormat();

this.formatString = style.getDataFormatString();

if (this.formatString == null)

this.formatString = BuiltinFormats

.getBuiltinFormat(this.formatIndex);

}

}

}

public void endElement(String uri, String localName, String name)

throws SAXException {

String thisStr = null;

// v => contents of a cell

if ("v".equals(name)) {

// Process the value contents as required.

// Do now, as characters() may be called more than once

switch (nextDataType) {

case BOOL:

char first = value.charAt(0);

thisStr = first == ‘0‘ ? "FALSE" : "TRUE";

break;

case ERROR:

// thisStr = "\"ERROR:" + value.toString() + ‘"‘;

thisStr = "\"ERROR:" + value.toString() + ‘"‘;

break;

case FORMULA:

// A formula could result in a string value,

// so always add double-quote characters.

// thisStr = ‘"‘ + value.toString() + ‘"‘;

thisStr = value.toString();

break;

case INLINESTR:

// TODO: have seen an example of this, so it‘s untested.

XSSFRichTextString rtsi = new XSSFRichTextString(value

.toString());

// thisStr = ‘"‘ + rtsi.toString() + ‘"‘;

thisStr = rtsi.toString();

break;

case SSTINDEX:

String sstIndex = value.toString();

try {

int idx = Integer.parseInt(sstIndex);

XSSFRichTextString rtss = new XSSFRichTextString(

sharedStringsTable.getEntryAt(idx));

// thisStr = ‘"‘ + rtss.toString() + ‘"‘;

thisStr = rtss.toString() ;

} catch (NumberFormatException ex) {

output.println("Failed to parse SST index ‘" + sstIndex

+ "‘: " + ex.toString());

}

break;

case NUMBER:

String n = value.toString();

if (this.formatString != null)

thisStr = formatter.formatRawCellContents(Double

.parseDouble(n), this.formatIndex,

this.formatString);

else

thisStr = n;

break;

default:

// thisStr = "(TODO: Unexpected type: " + nextDataType + ")";

thisStr = "(TODO: Unexpected type: " + nextDataType + ")";

break;

}

// Output after we‘ve seen the string contents

// Emit commas for any fields that were missing on this row

if (lastColumnNumber == -1) {

lastColumnNumber = 0;

}

for (int i = lastColumnNumber; i < thisColumn; ++i)

output.print(‘,‘);

// Might be the empty string.

output.print(thisStr);

// Update column

if (thisColumn > -1)

lastColumnNumber = thisColumn;

} else if ("row".equals(name)) {

// Print out any missing commas if needed

if (minColumns > 0) {

// Columns are 0 based

if (lastColumnNumber == -1) {

lastColumnNumber = 0;

}

for (int i = lastColumnNumber; i < (this.minColumnCount); i++) {

output.print(‘,‘);

}

}

// We‘re onto a new row

output.println();

lastColumnNumber = -1;

}

}

public void characters(char[] ch, int start, int length)

throws SAXException {

if (vIsOpen)

value.append(ch, start, length);

}

private int nameToColumn(String name) {

int column = -1;

for (int i = 0; i < name.length(); ++i) {

int c = name.charAt(i);

column = (column + 1) * 26 + c - ‘A‘;

}

return column;

}

}

// /

private OPCPackage xlsxPackage;

private int minColumns;

private PrintStream output;

private final String OUTPUT_CHARSET = "GBK";

/**

* Creates a new XLSX -> CSV converter

*

* @param pkg

* The XLSX package to process

* @param output

* The PrintStream to output the CSV to

* @param minColumns

* The minimum number of columns to output, or -1 for no minimum

*/

public XLSX2CSV(OPCPackage pkg, PrintStream output, int minColumns) {

this.xlsxPackage = pkg;

this.output = output;

this.minColumns = minColumns;

}

//TODO catch exceptions

public XLSX2CSV(String inputFilePath, String outputFilePath) throws Exception {

xlsxPackage = OPCPackage.open(inputFilePath, PackageAccess.READ);

output = new PrintStream(outputFilePath, OUTPUT_CHARSET);

minColumns = -1;

}

/**

* Parses and shows the content of one sheet using the specified styles and

* shared-strings tables.

*

* @param styles

* @param strings

* @param sheetInputStream

*/

public void processSheet(StylesTable styles,

ReadOnlySharedStringsTable strings, InputStream sheetInputStream)

throws IOException, ParserConfigurationException, SAXException {

InputSource sheetSource = new InputSource(sheetInputStream);

SAXParserFactory saxFactory = SAXParserFactory.newInstance();

SAXParser saxParser = saxFactory.newSAXParser();

XMLReader sheetParser = saxParser.getXMLReader();

ContentHandler handler = new MyXSSFSheetHandler(styles, strings,

this.minColumns, this.output);

sheetParser.setContentHandler(handler);

sheetParser.parse(sheetSource);

}

public void process() throws IOException, OpenXML4JException,

ParserConfigurationException, SAXException {

ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(

this.xlsxPackage);

XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);

StylesTable styles = xssfReader.getStylesTable();

XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader

.getSheetsData();

int index = 0;

while (iter.hasNext()) {

InputStream stream = iter.next();

String sheetName = iter.getSheetName();

// this.output.println();

// this.output.println(sheetName + " [index=" + index + "]:");

processSheet(styles, strings, stream);

stream.close();

++index;

}

}

public static void trans(String fileInput,String fileOutput){

XLSX2CSV xlsx2csv = null;

try {

xlsx2csv = new XLSX2CSV(fileInput, fileOutput);

} catch (Exception e) {

e.printStackTrace();

}

try {

xlsx2csv.process();

} catch (IOException e) {

e.printStackTrace();

} catch (OpenXML4JException e) {

e.printStackTrace();

} catch (ParserConfigurationException e) {

e.printStackTrace();

} catch (SAXException e) {

e.printStackTrace();

}

}

}

调用

ExcelToCsv.getCsv("input", "output");

原文：http://www.cnblogs.com/xiaoma0529/p/7098518.html