java解析excel文件，返回json

我这里用的是springboot项目，配合Maven使用的。首先需要引入依赖：

        <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.76</version></dependency>

接下来写一个ExcelUtils工具类，用于读取和解析单元格内的数据

    
public class ExcelUtils {// sheet页下标private static final int SHEET_INDEX = 0;// 开始读取的行private static final int STARTER_EAD_LINE = 4;// 去除最后读取的行private static final int TAIL_LINE = 2;上面的三个参数值根据自己要解析的excel定。private static final String[] PARAM_ARR = new String[] {"", ""};数组内容自己定....public static void main(String[] args) {//读取excel数据ExcelUtils excelUtil = new ExcelUtils();List<Map<String, String>> result = excelUtil.readExcelToObj("文件路径");JSONArray jsonArray = new JSONArray();for (Map<String, String> map : result) {JSONObject jsonObject = new JSONObject();for (Map.Entry<String, String> entry : map.entrySet()) {jsonObject.put(entry.getKey(), entry.getValue());}jsonArray.add(jsonObject);}System.out.println(jsonArray.toJSONString());}private List<Map<String, String>> readExcelToObj(String path) {Workbook wb = null;List<Map<String, String>> result = null;try {wb = WorkbookFactory.create(new File(path));result = readExcel(wb, SHEET_INDEX, STARTER_EAD_LINE, TAIL_LINE);} catch (InvalidFormatException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}return result;}public String getCellValue(Cell cell) {if (cell == null) {return "";}if (cell.getCellType() == CellType.STRING) {return cell.getStringCellValue();} else if (cell.getCellType() == CellType.BOOLEAN) {return String.valueOf(cell.getBooleanCellValue());} else if (cell.getCellType() == CellType.FORMULA) {return String.valueOf((int) cell.getNumericCellValue());} else if (cell.getCellType() == CellType.NUMERIC) {return String.valueOf((int) cell.getNumericCellValue());}return "";}private boolean isMergedRegion(Sheet sheet, int row, int column) {int sheetMergeCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; i++) {CellRangeAddress range = sheet.getMergedRegion(i);int firstColumn = range.getFirstColumn();int lastColumn = range.getLastColumn();int firstRow = range.getFirstRow();int lastRow = range.getLastRow();if (row >= firstRow && row <= lastRow) {if (column >= firstColumn && column <= lastColumn) {return true;}}}return false;}public String getMergedRegionValue(Sheet sheet, int row, int column) {int sheetMergeCount = sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; i++) {CellRangeAddress ca = sheet.getMergedRegion(i);int firstColumn = ca.getFirstColumn();int lastColumn = ca.getLastColumn();int firstRow = ca.getFirstRow();int lastRow = ca.getLastRow();if (row >= firstRow && row <= lastRow) {if (column >= firstColumn && column <= lastColumn) {Row fRow = sheet.getRow(firstRow);Cell fCell = fRow.getCell(firstColumn);return getCellValue(fCell);}}}return null;}private List<Map<String, String>> readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) {Sheet sheet = wb.getSheetAt(sheetIndex);Row row = null;List<Map<String, String>> result = new ArrayList<Map<String, String>>();for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {row = sheet.getRow(i);Map<String, String> map = new HashMap<String, String>();// 开始循环填充数据for (Cell c : row) {String returnStr = "";boolean isMerge = isMergedRegion(sheet, i, c.getColumnIndex());//判断是否具有合并单元格if (isMerge) {String rs = getMergedRegionValue(sheet, row.getRowNum(), c.getColumnIndex());returnStr = rs;} else {//设置单元格类型c.setCellType(CellType.STRING);returnStr = c.getRichStringCellValue().getString();}int columnIndex = c.getColumnIndex();if (columnIndex < PARAM_ARR.length) {map.put(PARAM_ARR[columnIndex], returnStr);}}result.add(map);}return result;}}

执行main方法，填写正确的路径即可获取数据。

正常情况下后端接受的类型并非File类型，而是MultipartFile类型，因此需要转化

String originalFilename = file.getOriginalFilenam;
String fileType = "";   // 文件类型File tempFile = null;
try {tempFile = File.createTempFile(originalFilename, fileType);file.transferTo(tempFile);
} catch (IOException e) {log.error("文件转换失败！", e);
}List<Map<String, String>> maps = excelUtils.readExcelToObjByUpload(tempFile);

格式转换完后执行即可获得List<Map<String, String>>数据。

遍历数据，获得json返回

JSONArray jsonArray = new JSONArray();for (Map<String, String> map : maps) {boolean putFlag = true;JSONObject jsonObject = new JSONObject();for (Map.Entry<String, String> entry : map.entrySet()) {// 此行有一处数据为空则不加入，自己根据业务逻辑加判断if (StringUtils.isNull(entry.getValue())) {putFlag = false;break;}jsonObject.put(entry.getKey(), entry.getValue());}if (putFlag) {jsonArray.add(jsonObject);}
}log.info("excel解析完数据为：{}", jsonArray.toJSONString());