Execl文件批量转csv文件

针对某些业务系统导出的数据都是Execl数据文件，如果需要都转成csv的话，每个文档都点击另存为转换成csv的话，就不方便了，

这里我分享一段代码针对Execl文件批量转换成csv

先在idea创建一个maven项目，具体怎么创建我就不多说了

package com.gong;

import com.sun.javafx.collections.MappingChange;
import org.apache.commons.lang.StringUtils;
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.util.ArrayList;
import java.util.List;
import java.util.Scanner;

public class ExcelToCsv {

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

        wb = readExcel(filePath);
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<MappingChange.Map<String,String>>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 0; i<rownum; i++) {
                row = sheet.getRow(i);
                for (int j = 0; j < colnum; j++) {

                    cellData = (String) getCellFormatValue(row.getCell(j));

                    buffer +=cellData;
                }
                buffer = buffer.substring(0, buffer.lastIndexOf(",")).toString();
                buffer += "
";

            }

            String savePath = newFilePath;
            File saveCSV = new File(savePath);
            try {
                if(!saveCSV.exists())
                    saveCSV.createNewFile();
                BufferedWriter writer = new BufferedWriter(new FileWriter(saveCSV));
                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;
        try {
            is = new FileInputStream(filePath);
            if(XLS_TYPE.equals(extString)){
                return wb = new HSSFWorkbook(is);
            }else if(XLSX_TYPE.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;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                case Cell.CELL_TYPE_NUMERIC:{
                    cellValue = String.valueOf(cell.getNumericCellValue()).replaceAll("
", " ") + ",";
                    break;
                }
                case Cell.CELL_TYPE_FORMULA:{
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
                        //转换为日期格式YYYY-mm-dd
                        cellValue = String.valueOf(cell.getDateCellValue()).replaceAll("
", " ") + ",";;
                    }else{
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue()).replaceAll("
", " ") + ",";;
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:{
                    cellValue = cell.getRichStringCellValue().getString().replaceAll("
", " ") + ",";;
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }

    public static void getFileName(String inputexecl,String outputcsv) {
       // String path = "E:\datas\"; // 路径
        String path = inputexecl;
        File f = new File(path);
        if (!f.exists()) {
            System.out.println(path + " not exists");
            return;
        }

        File fa[] = f.listFiles();//获取该目录下所有文件和目录的绝对路径
        for (int i = 0; i < fa.length; i++) {
            File fs = fa[i];
            if (fs.isDirectory()) {
                System.out.println(fs.getName() + " [目录]");
            } else {
               String filepath= String.valueOf(fs);
                String name=StringUtils.substringBeforeLast(fs.getName(),".");
                excelToCsv(filepath,outputcsv+name+".csv");
                System.out.println(fs.getName());
            }
        }
    }
    public static void main(String[] args) throws Exception {
        System.out.println("请输入Execl数据所在路径");
        Scanner execl=new Scanner(System.in);
        String input=execl.nextLine(); //获取execl输入路径
        System.out.println("请输入csv文件数据的输出路径");
        Scanner csv=new Scanner(System.in);
        String  output = csv.nextLine();
        getFileName(input,output);
       // excelToCsv("E:\datas\test.xlsx","E:\datas\test.csv");

    }

}

pom.xml文件

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.gong</groupId>
  <artifactId>csv</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>csv</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
                 <groupId>net.sf.opencsv</groupId>
                 <artifactId>opencsv</artifactId>
                 <version>2.1</version>
             </dependency>
           <dependency>
                <groupId>org.apache.poi</groupId>
               <artifactId>ooxml-schemas</artifactId>
                <version>1.1</version>
               <type>pom</type>
            </dependency>
           <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.7</version>
           </dependency>
           <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>ooxml-schemas</artifactId>
                <version>1.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.7</version>
            </dependency>
            <dependency>
                <groupId>dom4j</groupId>
                <artifactId>dom4j</artifactId>
                <version>1.6.1</version>
            </dependency>
      <!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
      <dependency>
          <groupId>net.sourceforge.jexcelapi</groupId>
          <artifactId>jxl</artifactId>
          <version>2.6.12</version>
      </dependency>
      <dependency>
          <groupId>commons-lang</groupId>
          <artifactId>commons-lang</artifactId>
          <version>2.6</version>
      </dependency>
      <dependency>
          <groupId>org.jsoup</groupId>
          <artifactId>jsoup</artifactId>
          <version>1.11.3</version>
      </dependency>

  </dependencies>

  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.7.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.0.0</version>
        </plugin>
      </plugins>

    </pluginManagement>
  </build>
</project>

运行分别输入excel文档的目录和csv的输出目录就可以了，在这里提醒一下大家，如果使用我这段代码的话，excel文档的数据文件不能带有其他类型的文件。