针对某些业务系统导出的数据都是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 += "\n";
}
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("\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 = cell.getRichStringCellValue().getString().replaceAll("\n", " ") + ",";;
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文档的数据文件不能带有其他类型的文件。
