<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
1)导出类
package org.example.TestExcel;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.InputStream;
public class ExcelUtils {
public static String getStringData(String path) {
// sheet对应的json数据
JSONObject jsonObject = new JSONObject();
try {
final int START_ROW_NUM = 3;
File dir = new File(ExcelUtils.class.getClassLoader().getResource(path).getPath());
for (File file : dir.listFiles()) {
String fullPath = path + File.separator + file.getName();
InputStream is = ExcelUtils.class.getClassLoader().getResourceAsStream(fullPath);
Workbook workbook = WorkbookFactory.create(is);
// 获取sheet数
int sheetNum = workbook.getNumberOfSheets();
for (int s = 0; s < sheetNum; s++) {
Sheet sheet = workbook.getSheetAt(s);
// 获取最大行数
int rowNum = sheet.getPhysicalNumberOfRows();
if (rowNum <= START_ROW_NUM) {
continue;
}
// 第一行
Row rowFirst = sheet.getRow(0);
// 获取最大列数
int column = rowFirst.getPhysicalNumberOfCells();
// 存储一行数据
JSONArray jsonArray = new JSONArray();
// 遍历每一行
for (int i = START_ROW_NUM; i < rowNum; i++) {
Row row = sheet.getRow(i);
if (row != null) {
// 存储这一行中每一列的数据
JSONObject rowObj = new JSONObject();
// 循环遍历每一列
for (int j = 0; j < column; j++) {
Cell cellData = row.getCell(j);
// 这一列存在
if (cellData != null) {
switch (cellData.getCellTypeEnum()) {
case NUMERIC: {
rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
break;
}
case FORMULA: {
if (DateUtil.isCellDateFormatted(cellData)) {
// 日期
rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getDateCellValue());
} else {
// 纯数字
rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getNumericCellValue());
}
break;
}
case STRING: {
rowObj.put(rowFirst.getCell(j).getStringCellValue(), cellData.getStringCellValue());
break;
}
default: {
System.err.println("不识别的类型" + cellData.getCellTypeEnum());
rowObj.put(rowFirst.getCell(j).getStringCellValue(), "");
}
break;
}
} else {
rowObj.put(rowFirst.getCell(j).getStringCellValue(), "");
}
}
jsonArray.add(rowObj);
}
}
//sheet名字对应的内容
jsonObject.put(sheet.getSheetName(), jsonArray);
}
// 关闭
is.close();
}
} catch (Exception e) {
System.err.println(e.getMessage());
}
return jsonObject.toJSONString();
}
}
2)简单使用
package org.example.TestExcel;
public class Main {
public static void main(String[] args) {
System.out.println(ExcelUtils.getStringData("excel"));
}
}
{"PropertyResource":[{"value":1.0,"key":"Scene.Menu"},{"value":2.0,"key":"Scene.Main"}]}