Java 批量 Excel转txt

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(); } } }