将excel表格转成csv格式

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

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.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class FileUtils {
    /**
     * 将excel表格转成csv格式
     *
     * @param oldFilePath   d:/a.xlsx
     * @param newFilePath   d:/b.csv
     */
    public static void excelToCsv(String oldFilePath, String newFilePath) {
        //写入csv的字符串,格式为行与行之间用回车换行分割，列与列之间逗号分割
        String buffer = "";
        //excel中表格对象，默认取第一个
        Sheet sheet = null;
        //excel读取对象
        Workbook wb = readExcel(oldFilePath);
        //excel中可能存在第一行字段名称为空的情况,对这种列存入set
        Set<Integer> ignoreCol = new HashSet<Integer>();
        //excel中可能存在第一行字段合并多单元格情况(对应表单多选)，将合并开始列与结束列作为键值对存入map
        Map<Integer, Integer> merageMap = new HashMap<Integer, Integer>();
        if (wb != null) {
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //先对第一行标题进行解析，若是有某列为空，该列进行忽略
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            Row row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            //遍历该excel中所有合并单元格,将第一行的记入map,方便下方取值需要按一列来取
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
                if (mergedRegion.getFirstRow() != 0 || mergedRegion.getFirstRow() != mergedRegion.getLastRow()) {
                    break;
                }
                merageMap.put(mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
            }
            //遍历第一行单元格,将为空的记录到忽略列集合
            for (int j = 0; j < colnum; j++) {
                String cellData = (String) getCellFormatValue(row.getCell(j));
                if (null == cellData || "".equals(cellData)) {
                    ignoreCol.add(j);
                } else if (merageMap.containsKey(j)) {
                    buffer += cellData;
                    j = merageMap.get(j);
                } else {
                    buffer += cellData;
                }
            }
            //将第一行字段标识行写入buffer
            buffer = buffer.substring(0, buffer.lastIndexOf(",")).toString();
            buffer += "\n";
            buffer += ",";
            buffer += "\n";
            buffer += ",";
            buffer += "\n";
            //默认第二行与第三行为表格说明行,从第四行开始遍历
            for (int i = 3; i < rownum; i++) {
                row = sheet.getRow(i);
                for (int j = 0; j < colnum; j++) {
                    //如果该列第一行无标识则忽略
                    if (ignoreCol.contains(j)) {
                        continue;
                        //若果是合并列，则多列统一算作一个值(表示多选，中间用；分割)
                    } else if (merageMap.containsKey(j)) {
                        String merageValue = "";
                        int endIndex = merageMap.get(j);
                        while (j <= endIndex) {
                            merageValue += (String) getCellFormatValue(row.getCell(j));
                            j++;
                        }
                        j--;
                        merageValue = convert(merageValue);
                        buffer += merageValue;
                    } else {
                        String cellData = (String) getCellFormatValue(row.getCell(j));
                        buffer += cellData;
                    }
                }
                buffer = buffer.substring(0, buffer.lastIndexOf(",")).toString();
                buffer += "\n";
            }
            File saveCSV = new File(newFilePath);
            try {
                if (!saveCSV.exists()) {
                    saveCSV.createNewFile();
                }
                BufferedWriter writer = new BufferedWriter(new FileWriter(saveCSV));
                System.out.println(buffer);
                writer.write(buffer);
                writer.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 将多选的多列值(a,b,,,c,)转为规定格式(a;b;c)
     *
     * @param merageValue
     * @return
     */
    private static String convert(String merageValue) {
        String result = "";
        String[] split = merageValue.split(",");
        for (String str : split) {
            if ("".equals(str)) {
                continue;
            }
            result = result + str + ";";
        }
        if ("".equals(result)) {
            return ",";
        } else {
            return result.substring(0, result.lastIndexOf(";")).toString() + ",";
        }
    }

    /**
     * 读取excel
     *
     * @param filePath
     * @return
     */
    private 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;
    }

    /**
     * 获取单元格内容
     *
     * @param cell
     * @return
     */
    private static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellType()) {
                case NUMERIC: {
                    //注意：这里获取的数值会自动保留小数,如原值为10读取后为10.0,故需用poi工具类转为原数
                    cellValue = NumberToTextConverter.toText(cell.getNumericCellValue()).replaceAll("\n", " ") + ",";
                    break;
                }
                case 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 STRING: {
                    cellValue = cell.getRichStringCellValue().getString().replaceAll("\n", " ") + ",";
                    ;
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }
}