注意： 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());
}
}
}