最近有个场景,在ftp服务器上,读取csv文件并入库,但是客户提供的一部分文件却是xls文件,就得搞个将excel转为csv文件的方法,话不多说直接开干。
方法
public static void convertExcelToCSV(String excelFilePath, String csvFilePath) throws IOException {
FileInputStream inputStream = new FileInputStream(excelFilePath);
Workbook workbook = getWorkbook(inputStream, excelFilePath);
inputStream.close();
FileWriter writer = new FileWriter(csvFilePath);
for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
StringBuilder stringBuilder = new StringBuilder();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
stringBuilder.append(getCellValue(cell));
if (cellIterator.hasNext()) {
stringBuilder.append(",");
}
}
writer.write(stringBuilder.toString() + "\n");
}
if (sheetIndex < workbook.getNumberOfSheets() - 1) {
writer.write("\n"); // 在不同工作表之间添加一个空行
}
}
writer.flush();
writer.close();
workbook.close();
}
private static Workbook getWorkbook(FileInputStream inputStream, String excelFilePath) throws IOException {
Workbook workbook = null;
if (excelFilePath.endsWith("xlsx")) {
workbook = new XSSFWorkbook(inputStream);
} else if (excelFilePath.endsWith("xls")) {
workbook = new HSSFWorkbook(inputStream);
} else {
throw new IllegalArgumentException("The specified file is not an Excel file");
}
return workbook;
}
private static String getCellValue(Cell cell) {
//DataFormatter formatter = new DataFormatter();
String formatPattern = "yyyy-MM-dd HH:mm:ss"; // 自定义日期时间格式
//FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
switch (cell.getCellType()) {
case STRING:
return cell.getRichStringCellValue().getString();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
DateFormat dateFormat = new SimpleDateFormat(formatPattern);
return dateFormat.format(date);
} else {
return Double.toString(cell.getNumericCellValue());
}
case BOOLEAN:
return Boolean.toString(cell.getBooleanCellValue());
case FORMULA:
return cell.getCellFormula();
default:
return "";
}
}
测试
public static void main(String[] args) {
String excelFilePath = "D:\\我是excel文件.xlsx";
String csvFilePath = "D:\\我是csv文件.csv";
try {
convertExcelToCSV(excelFilePath, csvFilePath);
System.out.println("Excel file converted to CSV successfully!");
} catch (IOException e) {
e.printStackTrace();
}
}
以上为整合保留时间格式
需求的全部代码,亲测非常好用!