java将excel文件转换成txt格式文件

       在实际应用中，我们难免会遇到解析excel文件入库事情，有时候为了方便，需要将excel文件转成txt格式文件。下面代码里面提供对xls、xlsx两种格式的excel文件解析，并写入到一个新的txt文件中，数据以分隔符逗号","隔开。

 excel文件如图所示: 

转换后的txt文件:

需要依赖4个jar包:

import java.io.BufferedWriter;
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>
**/
@SuppressWarnings("resource")
public class ExcelToTxt {

	public static void main(String[] args) throws IOException {
		Workbook wb = null;
		Sheet sheet = null;
		Row row = null;
		List<Map<String, String>> list = null;
		String cellData = null;
		String excelPath = "/Users/my/Documents/SHOP.105581000033387.20180714.txt.xlsx";
		String textPath = "/Users/my/Documents/SHOP.105581000033387.20180714.txt";
		String columns[] = { "交易时间", "记账日期", "银行流水号", "商户流水号", "订单号", "订单状态", "付款方账号/客户号", "付款方户名",
				"订单金额", "交易金额", "手续费", "结算金额", "柜台代码", "发卡行/通道", "支付卡种", "交易类型", "期数", "授权号", "项目号",
				"基本户", "备注一", "备注二" };
		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");
		}
		WriteToFile(sb.toString(), textPath);
		
		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: {
				// 判断cell是否为日期格式
				if (DateUtil.isCellDateFormatted(cell)) {
					// 转换为日期格式YYYY-mm-dd
					cellValue = cell.getDateCellValue();
				} else {
					// 数字
					cellValue = String.valueOf(cell.getNumericCellValue());
				}
				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();
		}
	}
}