在实际应用中，我们难免会遇到解析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) -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 listFile[] 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>>();// 获取第一个sheetsheet = 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);}}// 遍历解析出来的listStringBuffer 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格式成功*************");}// 读取excelpublic 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-ddcellValue = 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,表示:文件追加内容，不重新生成,默认为falsebw = new BufferedWriter(new OutputStreamWriter(out, "GBK"));bw.write(str += "\r\n");// 换行 bw.flush();} catch (Exception e) {e.printStackTrace();} finally {bw.close();}}}
from：/usuallyuser/article/details/81217122
