- 一、写在前面
- 二、问题场景
- 三、场景重现
- 四、ExcelUtil 代码
- 4.1 相关依赖
- 4.2 ExcelUtil 代码
- 五、代码优化
- 5.1 JSONObject 有汉字则无序
- 优化方案一：去掉 key 中的汉字 （不推荐）
- 优化方案二：创建 JSONObject 对象时传入有序集合 （推荐）
- 六、总结反思
- 七、写在后面
（由于考虑到公司业务和代码安全性问题，所有内容已脱敏）
二、问题场景最近，在实习中遇到如下场景，后端读取前端传来的 Excel 表格，解析后通过前端展示给用用户，前后端交互方式为 Json 。
由于是展示给上传 Excel 的用户，所以需要数据顺序不变。
三、场景重现表格如图所示：
交互思路，按行读取 Excel 并存为 Json数据。
代码如下：
package asia.siyuan.util;
import asia.siyuan.util.ExcelUtil;
import org.junit.jupiter.api.Test;
import java.io.File;
public class ExcelUtil {
@Test
public void testExcelToJson() throws Exception {
String PATH = "E:\JetBrains\IdeaProjects\Excel\siyuan-poi\";
String filename = "人员信息表.xlsx";
File file = new File(PATH + filename);
FileInputStream inputStream = new FileInputStream(file);
Workbook workbook = streamToWorkbook(filename, inputStream);
JSONObject jsonObject = resolveExcelToJson(workbook);
System.out.println(jsonObject);
}
}
四、ExcelUtil 代码
4.1 相关依赖
org.apache.poi
poi
3.9
org.apache.poi
poi-ooxml
3.9
joda-time
joda-time
2.10.14
org.junit.jupiter
junit-jupiter
RELEASE
compile
com.alibaba
fastjson
1.2.28
org.apache.commons
commons-lang3
3.12.0
4.2 ExcelUtil 代码
package asia.siyuan.util;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import java.io.FileInputStream;
import java.util.Date;
public class ExcelUtil {
public static Workbook streamToWorkbook(String name, FileInputStream inputStream) throws Exception {
// 1.得到一个工作簿
Workbook workbook = null;
// 2.判断 Excel 是 03版 还是 07版
if (name.toLowerCase().endsWith("xls")) {
workbook = new HSSFWorkbook(inputStream);
} else if (name.toLowerCase().endsWith("xlsx")) {
workbook = new XSSFWorkbook(inputStream);
} else if (StringUtils.isEmpty(name)) {
throw new Exception("未接收到文件");
} else {
throw new Exception("文件格式错误");
}
return workbook;
}
public static JSONObject resolveExcelToJson(Workbook workbook) throws Exception {
JSONObject infos = new JSONObject();
Sheet sheet = workbook.getSheetAt(0);
Row rowTitle = sheet.getRow(0);
int cellCount = rowTitle.getLastCellNum();
String[] titleString = new String[cellCount];
for (int titleNum = 0; titleNum < cellCount; titleNum++) {
Cell cell = rowTitle.getCell(titleNum);
if (isNonEmptyCell(cell)) {
titleString[titleNum] = getCellValue(cell);
}
}
int rowCount = sheet.getLastRowNum();
for (int rowNum = 1; rowNum <= rowCount; rowNum++) {
Row row = sheet.getRow(rowNum);
JSONObject info = new JSONObject();
if (isNonEmptyRow(row)) {
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = row.getCell(cellNum);
if (isNonEmptyCell(cell)) {
info.put(titleString[cellNum], getCellValue(cell));
}
}
infos.put("第" + rowNum + "行", info);
}
}
return infos;
}
public static boolean isNonEmptyCell(Cell cell) {
return cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && !(cell.getCellType() == Cell.CELL_TYPE_STRING && StringUtils.isEmpty(cell.getStringCellValue()));
}
public static boolean isNonEmptyRow(Row row) {
if (row == null) {
return false;
}
int cellCount = row.getLastCellNum();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = row.getCell(cellNum);
if (isNonEmptyCell(cell)) {
return true;
}
}
return true;
}
public static String getCellValue(Cell cell) throws Exception {
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) { // 日期 可能无法识别
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
// 不是日期格式 防止数字过长
cell.setCellType(Cell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = cell.getCellFormula();
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN: // 布尔
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_BLANK: // 空
break;
case Cell.CELL_TYPE_ERROR:
throw new Exception("单元格类型异常");
}
return cellValue;
}
}
五、代码优化
后期可能会根据业务场景适当优化以及拓展。
5.1 JSONObject 有汉字则无序输出格式中含有汉字，导致 Json 无序
即返回 Json 如下：
{"第9行":{"姓名":"吴九","班级":"计科2202","学号":"010220204","性别":"男"},"第2行":{"姓名":"陈二","班级":"计科2201","学号":"010220102","性别":"女"},"第1行":{"姓名":"刘一","班级":"计科2201","学号":"010220101","性别":"男"},"第4行":{"姓名":"李四","班级":"计科2201","学号":"010220104","性别":"女"},"第3行":{"姓名":"张三","班级":"计科2201","学号":"010220103","性别":"男"},"第6行":{"姓名":"赵六","班级":"计科2202","学号":"010220201","性别":"男"},"第5行":{"姓名":"王五","班级":"计科2201","学号":"010220105","性别":"男"},"第8行":{"姓名":"周八","班级":"计科2202","学号":"010220203","性别":"男"},"第7行":{"姓名":"孙七","班级":"计科2202","学号":"010220202","性别":"男"},"第10行":{"姓名":"郑十","班级":"计科2202","学号":"010220205","性别":"女"}}
解析后为
{
"第9行": {
"姓名": "吴九",
"班级": "计科2202",
"学号": "010220204",
"性别": "男"
},
"第2行": {
"姓名": "陈二",
"班级": "计科2201",
"学号": "010220102",
"性别": "女"
},
......,
"第7行": {
"姓名": "孙七",
"班级": "计科2202",
"学号": "010220202",
"性别": "男"
},
"第10行": {
"姓名": "郑十",
"班级": "计科2202",
"学号": "010220205",
"性别": "女"
}
}
优化方案一：去掉 key 中的汉字 （不推荐）
这个应该都不是大家想要的解决方案：
public static JSONObject resolveExcelToJson(Workbook workbook) throws Exception {
JSONObject infos = new JSONObject();
Sheet sheet = workbook.getSheetAt(0);
Row rowTitle = sheet.getRow(0);
int cellCount = rowTitle.getLastCellNum();
String[] titleString = new String[cellCount];
for (int titleNum = 0; titleNum < cellCount; titleNum++) {
Cell cell = rowTitle.getCell(titleNum);
if (isNonEmptyCell(cell)) {
titleString[titleNum] = getCellValue(cell);
}
}
int rowCount = sheet.getLastRowNum();
for (int rowNum = 1; rowNum <= rowCount; rowNum++) {
Row row = sheet.getRow(rowNum);
JSONObject info = new JSONObject();
if (isNonEmptyRow(row)) {
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = row.getCell(cellNum);
if (isNonEmptyCell(cell)) {
info.put(titleString[cellNum], getCellValue(cell));
}
}
infos.put(String.valueOf(rowNum), info);
}
}
return infos;
}
优化方案二：创建 JSONObject 对象时传入有序集合 （推荐）
程序员正解：
public static JSONObject resolveExcelToJson(Workbook workbook) throws Exception {
JSONObject infos = new JSONObject(new LinkedHashMap<>());
return infos;
}
六、总结反思
经过这个需求，我还是学到了很多知识，比如
- 创建 JSONObject 对象时传入有序集合，可以使 JSONObject 有序。
- 提交代码前自己多做测试，避免传上去后出现问题需要多次部署。
- 流相关知识查查漏补缺
- MultipartHttpServletRequest 类中相关方法的应用
欢迎关注，实现期间会经常发一些工作中遇到的问题。
欢迎随时留言讨论，与君共勉，知无不答！
