针对某些业务系统导出的数据都是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文档的数据文件不能带有其他类型的文件。