读取 Excel 或 TXT

/**
 * 这里的读取的 Excel 有格式限制：列标题且没重复标题也没有一个标题对应多列，具体代码可根据实际需求来
 * 返回的格式是 json 格式（标题无序）：
 * [
 *      {"列标题2":"11","列标题1":"10","列标题3":"12"},
 *      {"列标题2":"21","列标题1":"20","列标题3":"22"},
 *      {"列标题2":"31","列标题1":"30","列标题3":"32"}
 * ]
 * Created by lurenwu on 2020-11-11.
 */
@Component
public class ReadExcelUtil {

    private ArrayList<String> excelAllowType = new ArrayList<String>(){{
        add("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        add("application/vnd.ms-excel");
        add("application/vnd.ms-excel.sheet.macroenabled.12");
    }};


    public String readFileContent(MultipartFile file, Integer titleRowNum, Integer contentRowNum) throws Exception {
        String contentType = file.getContentType();
        if("text/plain".equals(contentType)){
            return readTxt(file);
        }else if(excelAllowType.contains(contentType)){
            if (titleRowNum == null || contentRowNum == null) {
                throw new Exception("参数错误！");
            }
            return readExcel(file, titleRowNum, contentRowNum);
        }else {
            throw new Exception("非法的文件格式！");
        }
    }


    /**
     * 读取 Excel
     * @param excelFile Excel 文件
     * @param titleRowNum 标题所在行（从 0 开始）
     * @param contentRowNum 内容起始行（从 0 开始）
     * @return 返回 json 格式字符串
     * @throws Exception 异常
     */
    public String readExcel(MultipartFile excelFile, int titleRowNum, int contentRowNum) throws Exception {
        String contentType = excelFile.getContentType();
        if(!excelAllowType.contains(contentType)){
            throw new Exception("非法的文件格式！");
        }
        // Use an InputStream, needs more memory
        DataFormatter formatter = new DataFormatter();// cell.getStringCellValue()只能获取文本格式单元格的值

        ArrayList<Object> list = new ArrayList<>();
        InputStream inputStream = null;
        try {
            inputStream = excelFile.getInputStream();
            Workbook sheets = WorkbookFactory.create(inputStream);
            for (Sheet sheet : sheets) {
                ArrayList<Object> sheetList = new ArrayList<>();
                // 获取标题
                Row rowCells = sheet.getRow(titleRowNum);
                ArrayList<String> titles = new ArrayList<>();
                if(rowCells == null){
                    continue;
                }
                for (int i = 0; i < rowCells.getLastCellNum(); i++) {
                    titles.add(getCellValue(sheet, formatter, rowCells.getCell(i)));
                }
                // 获取内容
                for(int x = contentRowNum; x <= sheet.getLastRowNum(); x++){
                    Row row = sheet.getRow(x);
                    // 获取每个单元格的值
                    if(row != null){
                        // FIXME 这里是用的 map 装的参数，因此 key （即列标题）不能有重复值
                        HashMap<String, Object> map = new HashMap<>();
                        for(int i = 0; i < row.getLastCellNum(); i++){
                            map.put(titles.get(i), getCellValue(sheet, formatter, row.getCell(i)));
                        }
                        sheetList.add(map);
                    }
                }
                list.add(sheetList);
            }
            // 返回 json
            ObjectMapper objectMapper = new ObjectMapper();
            if(list != null && list.size() == 1){
                return objectMapper.writeValueAsString(list.get(0));
            }
            return objectMapper.writeValueAsString(list);
        } finally {
            if(inputStream != null){
                inputStream.close();
            }
        }
    }

    public String readTxt(MultipartFile txtFile) throws IOException {
        StringBuilder result = new StringBuilder();
        InputStream inputStream = null;
        InputStreamReader inputStreamReader = null;
        BufferedReader bufferedReader = null;
        try {
            inputStream = txtFile.getInputStream();
            inputStreamReader = new InputStreamReader(inputStream);
            bufferedReader = new BufferedReader(inputStreamReader);
            String str;
            while ((str = bufferedReader.readLine()) != null) {
                result.append(str);
            }
            return result.toString();
        }finally {
            if(bufferedReader != null){
                bufferedReader.close();
            }
            if(inputStreamReader != null){
                inputStreamReader.close();
            }
            if(inputStream != null){
                inputStream.close();
            }
        }
    }

    /**
     * 获取单元格的值
     * @param sheet sheet
     * @param formatter DataFormatter
     * @param cell cell
     * @return 单元格的值
     */
    public String getCellValue(Sheet sheet, DataFormatter formatter, Cell cell){
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();// 获取合并单元格列表
        for (CellRangeAddress mergedRegion : mergedRegions) {
            if(mergedRegion.isInRange(cell)){
                return formatter.formatCellValue(sheet.getRow(mergedRegion.getFirstRow())
                        .getCell(mergedRegion.getFirstColumn()));
            }
        }
        return formatter.formatCellValue(cell);
    }

}

application.properties

lurenwu.export.ground-info-excel-allow-type=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel,application/vnd.ms-excel.sheet.macroenabled.12