在实际应用中，我们难免会遇到解析excel文件入库事情，有时候为了方便，需要将excel文件转成txt格式文件。下面代码里面提供对xls、xlsx两种格式的excel文件解析，并写入到一个新的txt文件中，数据以分隔符逗号","隔开。
excel文件如图所示:
转换后的txt文件:
需要依赖4个jar包:
package com.xuan.excel;
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.OutputStreamWriter;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry; import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; /**
* <p>版权所有：版权所有(C) 2014-2099</p>
* ──────────────────────────────────
* <p>作 者：青莲剑仙</p>
* ──────────────────────────────────
* <p>将excel转成TXT文本<p>
**/
public class ExcelToTxt { private static File [] getFiles(String path){
File file = new File(path);
// get the folder list
File[] array = file.listFiles();
return array;
} public static void main(String[] args) throws IOException {
File[] files=getFiles("D:\\keyword");
for(int i=0;i<files.length;i++){
if (files[i].isFile()) {
System.out.println("f=="+files[i].getPath()+" "+files[i].getName());
publishTxt(files[i].getPath());
}
if (files[i].isDirectory()) {
System.out.println("d=="+files[i].getPath()+" "+files[i].getName());
File[] files2=getFiles(files[i].getPath());
for(int j=0;j<files2.length;j++){
publishTxt(files2[j].getPath());
} }
}
} public static void publishTxt(String excelPath){
String columns[] = { "交易时间", "记账日期", "银行流水号", "商户流水号", "订单号", "订单状态", "付款方账号/客户号", "付款方户名",
"订单金额", "交易金额", "手续费", "结算金额", "柜台代码", "发卡行/通道", "支付卡种", "交易类型", "期数", "授权号", "项目号",
"基本户", "备注一", "备注二" };
Workbook wb = null;
Sheet sheet = null;
Row row = null;
List<Map<String, String>> list = null;
String cellData = null;
String fileType=excelPath.substring(excelPath.indexOf('.') + 1); wb = readExcel(excelPath);
if (wb != null) {
// 用来存放表中数据
list = new ArrayList<Map<String, String>>();
// 获取第一个sheet
sheet = wb.getSheetAt(0);
// 获取最大行数
int rownum = sheet.getPhysicalNumberOfRows();
// 获取第二行
row = sheet.getRow(1);
// 获取最大列数
int colnum = row.getPhysicalNumberOfCells();
for (int i = 0; i < rownum; i++) {
Map<String, String> map = new LinkedHashMap<String, String>();
row = sheet.getRow(i);
if (row != null) {
for (int j = 0; j < colnum; j++) {
cellData = (String) getCellFormatValue(row.getCell(j));
map.put(columns[j], cellData);
}
} else {
break;
}
list.add(map);
}
}
// 遍历解析出来的list
StringBuffer sb = new StringBuffer();
for (int i = 0; i < list.size(); i++) {
for (Entry<String, String> entry : list.get(i).entrySet()) {
String value = entry.getValue();
sb.append(value+",");
}
sb.append("\r\n");
}
try {
WriteToFile(sb.toString(), excelPath.replace(".xlsx", ".txt"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} System.out.println("*************EXCEL转成TXT格式成功*************"); } // 读取excel
public static Workbook readExcel(String filePath) {
Workbook wb = null;
if (filePath == null) {
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if (".xls".equals(extString)) {
return wb = new HSSFWorkbook(is);
} else if (".xlsx".equals(extString)) {
return wb = new XSSFWorkbook(is);
} else {
return wb = null;
} } catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return wb;
} public static Object getCellFormatValue(Cell cell) {
Object cellValue = null;
if (cell != null) {
// 判断cell类型
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: {
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
try{
// 判断cell是否为日期格式
if (DateUtil.isCellDateFormatted(cell)) {
// 转换为日期格式YYYY-mm-dd
cellValue = cell.getRichStringCellValue().getString();//cell.getDateCellValue();
} else {
// 数字
cellValue =cell.getRichStringCellValue().getString(); //String.valueOf(cell.getNumericCellValue());
}
}catch(Exception ex){}
break;
}
case Cell.CELL_TYPE_STRING: {
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
} else {
cellValue = "";
}
return cellValue;
} /**
* 生成文件
* @param str
* @param filePath
* @throws IOException
*/ public static void WriteToFile(String str, String filePath) throws IOException {
BufferedWriter bw = null;
try {
FileOutputStream out = new FileOutputStream(filePath, true);// true,表示:文件追加内容，不重新生成,默认为false
bw = new BufferedWriter(new OutputStreamWriter(out, "GBK"));
bw.write(str += "\r\n");// 换行
bw.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
bw.close();
}
}
}
from：https://blog.csdn.net/usuallyuser/article/details/81217122
