"Go" Java convert Excel file to txt format file

In the actual application, we will inevitably encounter parsing Excel file warehousing things, and sometimes for convenience, you need to convert Excel files to txt format files. The following code contains the XLS, xlsx two format of Excel file parsing, and write to a new TXT file, the data separated by a comma "," delimited.

Excel file:

Converted TXT file:

You need to rely on 4 jar packages:

 
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> Copyright: Copyright (C) 2014-2099 </ p>
* ────────────────────────────────────
* <p> Author: Qinglian Sword Fairy </ p>
* ────────────────────────────────────
* <p> Convert excel to TXT text <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 [] = {"transaction time", "accounting date", "bank serial number", "merchant serial number", "order number", "order status", "payer account / customer number", "payment Square account name ",
                "Order Amount", "Transaction Amount", "Handling Fee", "Settlement Amount", "Counter Code", "Issuer / Channel", "Payment Card Type", "Transaction Type", "Terms", "Authorization Number "," item number ",
                "Basic Household", "Remark 1", "Remark 2"};
        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) {
            // Used to store data in the table
            list = new ArrayList <Map <String, String >> ();
            // Get the first sheet
            sheet = wb.getSheetAt (0);
            // Get the maximum number of rows
            int rownum = sheet.getPhysicalNumberOfRows ();
            // get the second line
            row = sheet.getRow (1);
            // Get the maximum number of columns
            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);
            }
        }
        // Traverse the parsed 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 converted to TXT format successfully *************");
        
    }
 
    // read 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) {
            // determine the cell type
            switch (cell.getCellType ()) {
            case Cell.CELL_TYPE_NUMERIC: {
                cellValue = String.valueOf (cell.getNumericCellValue ());
                break;
            }
            case Cell.CELL_TYPE_FORMULA: {
                try {
                // determine whether the cell is in date format
                if (DateUtil.isCellDateFormatted (cell)) {
                    // Convert to date format YYYY-mm-dd
                    cellValue = cell.getRichStringCellValue (). getString (); / cell.getDateCellValue ();
                } else {
                    // number
                    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;
    }
 
    / **
     * Generate file
     * @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, means: append content to the file, not regenerate, default is false
            bw = new BufferedWriter (new OutputStreamWriter (out, "GBK"));
            bw.write (str + = "\ r \ n"); // line feed
            bw.flush ();
        } catch (Exception e) {
            e.printStackTrace ();
        } finally {
            bw.close ();
        }
    }
}

from:81217122

"Go" Java convert Excel file to txt format file