pom文件 , 导入fastjson和poi
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
</dependencies>
main方法
import java.util.Scanner;
public class Demo {
public static void main(String[] args) throws Exception{
//input写目录,扫描目录下所有xls和xlsx文件进行转换
//input写具体文件,直接转换成json文件
//output写生成json文件的保存路径
ExcelToJson.excelToJson(input , output);
}
}
ExcelToJson 类
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import xyz.wbls.javautils.FileUtils;
import java.io.*;
public class ExcelToJson {
/**
* excelToJson方法
*
* @param input 要转换的excel文件或者excel所在文件夹路径
* @param output 转换完成后保存文件路径
* @throws IOException 异常
*/
public static void excelToJson(String input, String output) throws IOException {
File in = new File(input);
File out = new File(output);
if (!out.exists()) out.mkdirs();
if (in.isDirectory()) {
//获取文件夹下所有xls和xlsx后缀的文件
//FileUtils.getFiles(file , ...ext) 是另一篇文章
// https://blog.csdn.net/qq_25278517/article/details/107610175
File[] files = FileUtils.getFiles(in, "xls", "xlsx");
for (File file : files) {
saveFile(toJson(file.getCanonicalPath()), out, file.getName());
}
} else saveFile(toJson(input), out, in.getName());
if (bufferedWriter != null) bufferedWriter.close();
}
private static BufferedWriter bufferedWriter;
/**
* @param jsonString 转换完成后的json字符串
* @param out 转换完成后json文件保存路径
* @param fileName 文件名
* @throws IOException 异常
*/
private static void saveFile(String jsonString, File out, String fileName) throws IOException {
if (jsonString == null) return;
File file = new File(out.getCanonicalPath() + "\\" + fileName + ".json");
file.createNewFile();
System.out.println(file.getCanonicalPath());
bufferedWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), "utf-8"));
bufferedWriter.write(jsonString);
bufferedWriter.flush();
}
/**
* @param filePath 文件路径
* @return 返回转换以后的json字符串
* @throws IOException 异常
*/
private static String toJson(String filePath) throws IOException {
Workbook wb;
if (filePath == null) return null;
FileInputStream fis = new FileInputStream(filePath);
String ext = filePath.substring(filePath.lastIndexOf("."));
if (ext.equals(".xls")) wb = new HSSFWorkbook(fis);
else if (ext.equals(".xlsx")) wb = new XSSFWorkbook(fis);
else return null;
int sheetNum = wb.getNumberOfSheets();
JSONObject jsonObject = new JSONObject();
for (int s = 0; s < sheetNum; s++) {
Sheet sheet = wb.getSheetAt(s);
int rowCount = sheet.getPhysicalNumberOfRows();
if (rowCount <= 1) continue;
int firstRow = 0;
for (int i = 0; i < rowCount; i++) {
if (!isFirstRow(sheet, i)) continue;
firstRow = i;
break;
}
Row row1 = sheet.getRow(firstRow);
int colCount = row1.getPhysicalNumberOfCells();
JSONArray jsonArray = new JSONArray();
for (int i = firstRow + 1; i < rowCount; i++) {
Row row = sheet.getRow(i);
if (row != null) {
JSONObject rowObj = new JSONObject(true);
for (int j = 0; j < colCount; j++) {
Cell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
rowObj.put(row1.getCell(j).getStringCellValue(), cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
if (DateUtil.isCellDateFormatted(cell)) {
rowObj.put(row1.getCell(j).getStringCellValue(), cell.getDateCellValue());
} else {
rowObj.put(row1.getCell(j).getStringCellValue(), cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
rowObj.put(row1.getCell(j).getStringCellValue(), cell.getStringCellValue());
break;
default:
rowObj.put(row1.getCell(j).getStringCellValue(), "");
}
} else rowObj.put(row.getCell(j).getStringCellValue(), "");
}
jsonArray.add(rowObj);
}
}
jsonObject.put(sheet.getSheetName(), jsonArray);
}
return JSONArray.toJSONString(jsonObject, true);
}
/**
* 进行简单的判断是否可以做首行 , 过滤表格顶部有合并单元格
* 对每一行做判断 ,得到以一个每列都不为空的行 ,作为首行
* @param sheet sheet 工作表
* @param number number 当前工作表的第n行
* @return 可以做为首行返回true , 不可以返回false
*/
public static boolean isFirstRow(Sheet sheet,int number){
Row row = sheet.getRow(number);
int physicalNumberOfCells = row.getPhysicalNumberOfCells();
boolean isFirstRow = false;
for (int j = 0; j < physicalNumberOfCells; j++) {
if (row.getCell(j) == null) break;
else if (row.getCell(j).getStringCellValue().equals("")) break;
else if (j == physicalNumberOfCells - 1) isFirstRow = true;
}
return isFirstRow;
}
}