【轉】java將excel檔案轉換成txt格式檔案

標籤：讀取excel   []   util   sub   entry   txt   indexof   成功   結算   

       在實際應用中，我們難免會遇到解析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：81217122

【轉】java將excel檔案轉換成txt格式檔案