Excel转CSV格式

注意： CSV文件导出来的 可以理解为 就是一个 普通的文件， 但至于使用什么样的软件打开就是另一马事了，比如Excel打开后 出来 “自动过滤了数字前面的0”，这玩意程序控制不到，那是Excel的事情。CSV不是Excel文件切记，只不过用表格软件打开CSV而已

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ExclToCsv {

    public static void main(String[] args) throws Exception {
        excelToCsvBatch("C:\\tmp\\flowPool\\flowPool\\20211206150814");
    }

    /**
     * 将指定目录下的 Excel 文件转 csv格式文件
     * @param srcFilePath 目录地址
     */
    public static void excelToCsvBatch(String srcFilePath) {
        ArrayList<File> listFiles = getListFiles(srcFilePath);
        for(File file : listFiles){
            System.out.println(file.getParent()+file.getName().substring(0,file.getName().lastIndexOf(".")));
            ExclToCsv.excelToCsv(file.getPath(), file.getParent()+"\\"+file.getName().substring(0,file.getName().lastIndexOf("."))+".csv");
        }
    }

    /**
     * 获取指定目录下的所有的文件（不包括文件夹），采用了递归
     * @param obj 字符串路径 || File对象指向目录
     * @return 目录中所有文件的File对象
     */
    private static ArrayList<File> getListFiles(Object obj) {
        File directory = null;
        if (obj instanceof File) {
            directory = (File) obj;
        } else {
            directory = new File(obj.toString());
        }
        ArrayList<File> files = new ArrayList<File>();
        if (directory.isFile()) {
            files.add(directory);
            return files;
        } else if (directory.isDirectory()) {
            File[] fileArr = directory.listFiles();
            for (int i = 0; i < fileArr.length; i++) {
                File fileOne = fileArr[i];
                files.addAll(getListFiles(fileOne));
            }
        }
        return files;
    }

    /**
     * 将excel表格转成csv格式
     *
     * @param oldFilePath
     * @param newFilePath
     */
    public static void excelToCsv(String oldFilePath, String newFilePath) {
        String buffer = "";
        Workbook wb = null;
        Sheet sheet = null;
        Row row = null;
        Row rowHead = null;
        List<Map<String, String>> list = null;
        String cellData = null;
        String filePath = oldFilePath;

        wb = readExcel(filePath);
        if (wb != null) {
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                sheet = wb.getSheetAt(i);
                // 标题总列数
                rowHead = sheet.getRow(i);
                if (rowHead == null) {
                    continue;
                }
                //总列数colNum
                int colNum = rowHead.getPhysicalNumberOfCells();
                String[] keyArray = new String[colNum];
                Map<String, Object> map = new LinkedHashMap<>();

                //用来存放表中数据
                list = new ArrayList<Map<String, String>>();
                //获取第一个sheet
                sheet = wb.getSheetAt(i);
                //获取最大行数
                int rownum = sheet.getPhysicalNumberOfRows();
                //获取第一行
                row = sheet.getRow(0);
                //获取最大列数
                int colnum = row.getPhysicalNumberOfCells();
                for (int n = 0; n < rownum; n++) {
                    row = sheet.getRow(n);
                    for (int m = 0; m < colnum; m++) {
                        cellData = getCellFormatValue(row.getCell(m)).toString();
                        buffer += cellData;
                    }
                    buffer = buffer.substring(0, buffer.lastIndexOf(","));
                    buffer += "\n";
                }

                String savePath = newFilePath;
                File saveCSV = new File(savePath);
                try {
                    if (!saveCSV.exists()) {
                        saveCSV.createNewFile();
                    }
                    BufferedWriter writer = new BufferedWriter(new FileWriter(saveCSV));
                    writer.write(new String(new byte[] { (byte) 0xEF, (byte) 0xBB,(byte) 0xBF }));
                    writer.write(buffer);
                    writer.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }

        }

    }

    //读取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类型
            int cellType = cell.getCellType();
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC: {
                    String cellva = getValue(cell);
                    cellValue = cellva.replaceAll("\n", " ") + ",";
                    break;
                }
                case Cell.CELL_TYPE_FORMULA: {
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        //转换为日期格式YYYY-mm-dd
                        cellValue = String.valueOf(cell.getDateCellValue()).replaceAll("\n", " ") + ",";
                    } else {
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue()).replaceAll("\n", " ") + ",";
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING: {
                    cellValue = String.valueOf(cell.getRichStringCellValue()).replaceAll("\n", " ") + ",";
                    break;
                }
                default:
                    cellValue = "" + ",";
            }
        } else {
            cellValue = "" + ",";
        }
        return cellValue;
    }

    /**
     * 此方法为去掉转csv时数字等默认加上的小数点
     * 如果不需要刻意不调用此方法
     */
    public static String getValue(Cell hssfCell) {
        if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            Object inputValue = null;// 单元格值
            Long longVal = Math.round(hssfCell.getNumericCellValue());
            Double doubleVal = hssfCell.getNumericCellValue();
            if (Double.parseDouble(longVal + ".0") == doubleVal) {   //判断是否含有小数位.0
                inputValue = longVal;
            } else {
                inputValue = doubleVal;
            }
            DecimalFormat df = new DecimalFormat("#");    //在此处更改小数点及位数，按自己需求选择；
            return String.valueOf(df.format(inputValue));      //返回String类型
        } else {
            // 返回字符串类型的值
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

}