place_name_search/src/main/java/com/example/poiaddr/util/ExcelReaderUtils.java

package com.example.poiaddr.util;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;

/**
 * xlsx文档解析并输出内容为List<Map<String,String>>
 * 大文件分割
 */
public class ExcelReaderUtils {

    public static void writeToExcel(List<Map<String, Object>> dataList, String filePath) throws IOException {
        // 创建一个新的工作簿（对应一个Excel文件）
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet("Sheet1");

        // 写入表头（从Map的键中获取列名）
        if (!dataList.isEmpty()) {
            Map<String, Object> firstMap = dataList.get(0);
            int colIndex = 0;
            Row headerRow = sheet.createRow(0);
            for (String key : firstMap.keySet()) {
                Cell cell = headerRow.createCell(colIndex++);
                cell.setCellValue(key);
            }
        }

        // 逐行写入数据
        int rowIndex = 1;
        for (Map<String, Object> dataMap : dataList) {
            Row dataRow = sheet.createRow(rowIndex++);
            int colIndex = 0;
            for (Object value : dataMap.values()) {
                Cell cell = dataRow.createCell(colIndex++);
                if (value instanceof String) {
                    cell.setCellValue((String) value);
                } else if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof Double) {
                    cell.setCellValue((Double) value);
                } else if (value instanceof Boolean) {
                    cell.setCellValue((Boolean) value);
                } else {
                    cell.setCellValue(value != null ? value.toString() : "");
                }
            }
        }

        // 将工作簿写入到文件
        try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
            workbook.write(outputStream);
        } finally {
            workbook.close();
        }
    }

    public static List<Map<String, Object>> readExcel(String filePath) throws IOException {
        List<Map<String, Object>> resultList = new ArrayList<>();
        FileInputStream fis = new FileInputStream(filePath);
        IOUtils.setByteArrayMaxOverride(400000000);
        // 创建工作簿对象，用于代表整个Excel文件
        Workbook workbook = WorkbookFactory.create(fis);
        // 这里我们默认读取第一个工作表，如果需要读取指定名称或者索引的工作表可以进行相应修改
        Sheet sheet = workbook.getSheetAt(0);
        // 获取表头行
        Row headerRow = sheet.getRow(0);
        int headerSize = headerRow.getLastCellNum();

        // 遍历数据行（从第二行开始，第一行是表头）
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row currentRow = sheet.getRow(rowIndex);
            Map<String, Object> rowMap = new HashMap<>();
            for (int cellIndex = 0; cellIndex < headerSize; cellIndex++) {
                Cell headerCell = headerRow.getCell(cellIndex);
                Cell currentCell = currentRow.getCell(cellIndex);
                String headerValue = getCellValue(headerCell).toString();
                Object currentValue = getCellValue(currentCell);
                rowMap.put(headerValue, currentValue);
            }
            resultList.add(rowMap);
        }

        workbook.close();
        fis.close();
        return resultList;
    }


    public static List<Map<String, Object>> readExcel(File file) throws IOException {
        List<Map<String, Object>> resultList = new ArrayList<>();
        FileInputStream fis = new FileInputStream(file);
        IOUtils.setByteArrayMaxOverride(400000000);
        // 创建工作簿对象，用于代表整个Excel文件
        Workbook workbook = WorkbookFactory.create(fis);
        // 这里我们默认读取第一个工作表，如果需要读取指定名称或者索引的工作表可以进行相应修改
        Sheet sheet = workbook.getSheetAt(0);
        // 获取表头行
        Row headerRow = sheet.getRow(0);
        int headerSize = headerRow.getLastCellNum();

        // 遍历数据行（从第二行开始，第一行是表头）
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row currentRow = sheet.getRow(rowIndex);
            Map<String, Object> rowMap = new HashMap<>();
            for (int cellIndex = 0; cellIndex < headerSize; cellIndex++) {
                Cell headerCell = headerRow.getCell(cellIndex);
                Cell currentCell = currentRow.getCell(cellIndex);
                String headerValue = getCellValue(headerCell).toString();
                Object currentValue = getCellValue(currentCell);
                rowMap.put(headerValue, currentValue);
            }
            resultList.add(rowMap);
        }

        workbook.close();
        fis.close();
        return resultList;
    }

    public static void splitExcelByRows(String inputFilePath, int rowsPerFile) throws IOException {
        FileInputStream inputStream = new FileInputStream(new File(inputFilePath));
        IOUtils.setByteArrayMaxOverride(400000000);
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        int totalRows = sheet.getLastRowNum() + 1;
        int fileCount = (totalRows / rowsPerFile) + (totalRows % rowsPerFile == 0? 0 : 1);

        for (int i = 0; i < fileCount; i++) {
            Workbook newWorkbook = new XSSFWorkbook();
            Sheet newSheet = newWorkbook.createSheet("Sheet1");
            int startRow = i * rowsPerFile;
            int endRow = Math.min((i + 1) * rowsPerFile, totalRows);

            for (int rowIndex = startRow; rowIndex < endRow; rowIndex++) {
                Row sourceRow = sheet.getRow(rowIndex);
                Row newRow = newSheet.createRow(rowIndex - startRow);
                if (sourceRow!= null) {
                    for (int cellIndex = 0; cellIndex < sourceRow.getLastCellNum(); cellIndex++) {
                        Cell sourceCell = sourceRow.getCell(cellIndex);
                        Cell newCell = newRow.createCell(cellIndex);
                        if (sourceCell!= null) {
                            switch (sourceCell.getCellType()) {
                                case STRING:
                                    newCell.setCellValue(sourceCell.getStringCellValue());
                                    break;
                                case NUMERIC:
                                    newCell.setCellValue(sourceCell.getNumericCellValue());
                                    break;
                                case BOOLEAN:
                                    newCell.setCellValue(sourceCell.getBooleanCellValue());
                                    break;
                                // 可以根据实际情况添加更多的类型处理，比如日期等
                                default:
                                    newCell.setCellValue("");
                            }
                        }
                    }
                }
            }

            String outputFilePath = getOutputFilePath(inputFilePath, i);
            FileOutputStream outputStream = new FileOutputStream(outputFilePath);
            newWorkbook.write(outputStream);
            outputStream.close();
            newWorkbook.close();
        }

        workbook.close();
        inputStream.close();
    }

    private static String getOutputFilePath(String inputFilePath, int index) {
        String baseName = inputFilePath.substring(0, inputFilePath.lastIndexOf('.'));
        String extension = inputFilePath.substring(inputFilePath.lastIndexOf('.'));
        return baseName + "_part_" + (index + 1) + extension;
    }

    private static Map<String, String>  updateTableHeader() {
        Map<String, String> table1colToName = new HashMap<>();
        table1colToName.put("ID", "序列号");
        table1colToName.put("TASKID", "任务编号");
        table1colToName.put("BANLIRESULT_12345", "办理结果");
        table1colToName.put("APPEAL_EXPLAIN", "诉求认定说明");
        table1colToName.put("DESCRIPTION_12345", "反馈结论");
        table1colToName.put("NOT_REASON", "未联原因");
        table1colToName.put("CASEVALUATION_12345", "是否满意");
        table1colToName.put("VIEWINFO", "现场查看");
        table1colToName.put("DISPATCHNOTE", "派遣备注");
        table1colToName.put("ENDNOTE", "结案备注");
        table1colToName.put("PARTSN", "部件编号");
        table1colToName.put("WORKGRID", "工作网格");
        table1colToName.put("SPEICALSIGN", "案件特殊标识（特征要素）");
        table1colToName.put("CANCLETIME", "作废时间");
        table1colToName.put("CONTACTINFO", "联系方式");
        table1colToName.put("ACCEPTTIME", "接单时间（最后一次主责部门处理完成时间）");
        table1colToName.put("LASTCONTACTTIME", "首次联系截止时间（最后主责）");
        table1colToName.put("INFOSOURCENAME", "来源名");
        table1colToName.put("INFOTYPENAME", "案件属性名");
        table1colToName.put("INFOBCNAME", "大类名");
        table1colToName.put("INFOSCNAME", "小类名");
        table1colToName.put("INFOZCNAME", "子类名");
        table1colToName.put("STREETNAME", "街道名");
        table1colToName.put("COMMUNITYNAME", "居委名");
        table1colToName.put("WORKGRIDCODE", "责任网格");
        table1colToName.put("EXECUTEDEPTNAME", "最后主责部门");
        table1colToName.put("REPORTER", "反映人");
        table1colToName.put("REPORTDEPTNAME", "渠道来源");
        table1colToName.put("SYNCTIME", "更新时间");
        table1colToName.put("WP_SOURCE", "工单来源");
        table1colToName.put("CASESN", "案卷编号");
        table1colToName.put("INFOSOURCEID", "问题来源");
        table1colToName.put("DISCOVERTIME", "发现时间");
        table1colToName.put("PERCREATETIME", "受理时间（最后一次受理时间）");
        table1colToName.put("CREATETIME", "立案时间（最后一次立案时间）");
        table1colToName.put("DISPATCHTIME", "派遣时间（最后一次派遣时间）");
        table1colToName.put("SOLVINGTIME", "处理完成时间（最后一次主责部门处理完成时间）");
        table1colToName.put("TELASKTIME", "回访时间（最后一次回访时间）");
        table1colToName.put("ENDTIME", "结案时间");
        table1colToName.put("STREETCODE", "街道编号");
        table1colToName.put("COMMUNITYCODE", "村、居村编码");
        table1colToName.put("GRIDCODE", "万米网格编码");
        table1colToName.put("COORDX", "X坐标");
        table1colToName.put("COORDY", "Y坐标");
        table1colToName.put("ADDRESS", "发生地址");
        table1colToName.put("INFOTYPEID", "问题类型编码(0,value:部件；1：事件；...)");
        table1colToName.put("INFOBCCODE", "问题大类编号");
        table1colToName.put("INFOSCCODE", "问题小类编号");
        table1colToName.put("INFOZCCODE", "问题子类编码");
        table1colToName.put("INFOATCODE", "问题管理要点编码");
        table1colToName.put("DESCRIPTION", "问题描述");
        table1colToName.put("STATUS", "T_INFO_MAIN表状态");
        table1colToName.put("DEPTCODE", "立案部门");
        table1colToName.put("EXECUTEDEPTCODE", "最后一次主责部门");
        table1colToName.put("INSERTDEPTCODE", "收集部门");
        table1colToName.put("KEEPERSN", "上报监督员编号");
        table1colToName.put("INSERTUSER", "记录添加操作员（收集人）");
        table1colToName.put("URGENTDEGREE", "紧急程度（0：一般；1：紧急）");
        table1colToName.put("APPROACH", "12345工单处理方式/案卷类型（0：转办; /1：督办; /2：回访复核)");
        table1colToName.put("SIMILARCASESN", "相关案件编号");
        table1colToName.put("SERVICETYPE", "业务类型");
        table1colToName.put("ISANONYMITY", "是否匿名");
        table1colToName.put("USEREVALUATE", "用户评价/满意度(0,value:满意; /1,value:基本满意; /2,value:不满意)");
        table1colToName.put("ALLMIDDLETIME", "整体案卷黄灯开始时间（根据紧急程度middle计算的)");
        table1colToName.put("ALLIMPORTANTTIME", "整体案卷橙灯开始时间（根据紧急程度important计算的)");
        table1colToName.put("ALLENDTIME", "整体案卷截止时间（红灯开始时间）");
        table1colToName.put("MIDDLESOLVINGTIME", "处理阶段黄灯开始时间");
        table1colToName.put("IMPORTANTSOLVINGTIME", "处理阶段橙灯开始时间");
        table1colToName.put("LASTSOLVINGTIME", "处理阶段红灯开始时间（处理截止时间）");
        table1colToName.put("CALLBACK_FLAG", "12345回访复核单状态标识(0：否，1：是)");
        table1colToName.put("URGE_COUNT", "12345催单次数（12345催单时带过来的催单次数）");
        table1colToName.put("DU_LIMIT", "12345督办时限(天)");
        table1colToName.put("CASEEND", "是否自行处置");
        table1colToName.put("BANLIRESULT", "12345办理结果(0,value:解决，1,value:未解决，2,value:部分解决，3,value:不办理退单)");
        table1colToName.put("ENDRESULT", "结案评价");
        table1colToName.put("VERIFYRESULT", "最后的核实结果（1,value:属实，0,value:不属实)");
        table1colToName.put("CHECKRESULT", "最后的核查结果（1,value:完成，0,value:未完成)");
        table1colToName.put("PRIORITYAREA", "重要区域");
        table1colToName.put("CONTACTMODE", "反映人联系方式");
        table1colToName.put("BACKCOUNT", "退单次数（案件所有主责部门累加）");
        table1colToName.put("HESHICOUNT", "核实次数");
        table1colToName.put("HECHACOUNT", "核查次数");
        table1colToName.put("HUIFANGCOUNT", "回访次数");
        table1colToName.put("HASLEADTYPECOUNT", "领导督办次数");
        table1colToName.put("HASTENTYPECOUNT", "催办过的次数");
        table1colToName.put("HOTLINESN", "12319编号，延伸为外系统管理单号");
        table1colToName.put("JHPT_UPDATE_TIME", "JHPT_UPDATE_TIME");
        table1colToName.put("JHPT_DELETE", "JHPT_UPDATE_TIME");
        table1colToName.put("DEPTNAME", "部门名");
        table1colToName.put("STATUSNAME", "状态名");
        return table1colToName;
    }
    private static String getCellValueAsString(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getCellFormula();
                } catch (Exception e) {
                    return "";
                }
            default:
                return "";
        }
    }

    /**
     * 替换表头
     */
    private static void ModifyExcelHeaderJExcelApi(){
        try {
            // 读取现有的Excel文件
            FileInputStream file = new FileInputStream("C:\\Users\\Liumouren\\Desktop\\临时文件\\元以科技\\青浦\\青浦城建所\\poiAddr\\doc\\tableHeader.xlsx");
            Workbook workbook = new XSSFWorkbook(file);
            Map<String, String> table1colToName = updateTableHeader();
            // 遍历每个工作表
            for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) {
                Sheet sheet = workbook.getSheetAt(sheetIndex);
                // 获取表头行（假设第一行是表头）
                Row headerRow = sheet.getRow(0);
                if (headerRow!= null) {
                    // 遍历每个表头单元格进行替换
                    // 遍历表头行中的每个单元格
                    for (Cell cell : headerRow) {
                        if (cell!= null) {
                            String oldHeaderValue = getCellValueAsString(cell);
                            if (table1colToName.containsKey(oldHeaderValue)) {
                                cell.setCellValue(table1colToName.get(oldHeaderValue));
                            }
                        }
                    }
                }
            }
            // 保存修改后的Excel文件
            FileOutputStream outputStream = new FileOutputStream("C:\\Users\\Liumouren\\Desktop\\临时文件\\元以科技\\青浦\\青浦城建所\\poiAddr\\doc\\outPutTableHeader.xlsx");
            workbook.write(outputStream);
            outputStream.close();
            file.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void XlsxFileSplitByRowRange(String filePath, int tag) {
        try {
            // 读取原始xlsx文件
            Workbook workbook = new XSSFWorkbook(new File(filePath));
            Sheet sheet = workbook.getSheetAt(0);
            int totalRows = sheet.getLastRowNum();
            int rowsPerFile = 10000; // 每个分割文件包含的行数
            for (int i = 0; i <= totalRows; i += rowsPerFile) {
                // 创建新的工作簿用于保存分割后的行数据
                Workbook newWorkbook = new XSSFWorkbook();
                Sheet newSheet = newWorkbook.createSheet();
                int endRow = Math.min(i + rowsPerFile, totalRows + 1);
                for (int j = i; j < endRow; j++) {
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        Row newRow = newSheet.createRow(j - i);
                        for (Cell cell : row) {
                            if (cell != null) {
                                Cell newCell = newRow.createCell(cell.getColumnIndex());
                                // 复制单元格类型和值
                                newCell.setCellType(cell.getCellType());
                                if (cell.getCellType() == CellType.STRING) {
                                    newCell.setCellValue(cell.getStringCellValue());
                                } else if (cell.getCellType() == CellType.NUMERIC) {
                                    newCell.setCellValue(cell.getNumericCellValue());
                                } else if (cell.getCellType() == CellType.BOOLEAN) {
                                    newCell.setCellValue(cell.getBooleanCellValue());
                                }
                            }
                        }
                    }
                }
                // 将分割后的行数据保存为新的xlsx文件
                FileOutputStream outputStream = new FileOutputStream("C:\\Users\\Liumouren\\Desktop\\临时文件\\元以科技\\青浦\\青浦城建所\\poiAddr\\doc\\output_" + tag + "_" + (i / rowsPerFile) + ".xlsx");
                newWorkbook.write(outputStream);
                outputStream.close();
                newWorkbook.close();
            }
            workbook.close();
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    private static Object getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return cell.getNumericCellValue();
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }

    public static List<File> listFilesInDirectory(String path) {
        List<File> fileList = new ArrayList<>();
        File directory = new File(path);
        // 首先判断给定的路径是否是一个目录
        if (directory.isDirectory()) {
            File[] files = directory.listFiles();
            if (files != null) {
                for (File file : files) {
                    if (file.isFile()) {
                        fileList.add(file);
                    } else if (file.isDirectory()) {
                        // 如果是子目录，递归调用获取子目录下的文件
                        fileList.addAll(listFilesInDirectory(file.getAbsolutePath()));
                    }
                }
            }
        }
        return fileList;
    }


    public static void main(String[] args) {
//        根据Map字段修改表头
//        ModifyExcelHeaderJExcelApi();

        //          根据文件路径得到下面的所有文件集合
        List<File> fileList = listFilesInDirectory("C:\\Users\\Liumouren\\Desktop\\临时文件\\元以科技\\青浦\\青浦城建所\\poiAddr\\doc\\");
//          根据文件集合得到
//        Set<String> addressSet = new HashSet<>();
//            TODO 所有文件加入
        int tagIndex = 0;
        for (File fileItem : fileList) {
            if (fileItem.getPath().contains(".xlsx") && fileItem.getPath().contains("marge_excel_uuid")) {
                System.out.println(fileItem.getPath());
                tagIndex++;
//                分割文件
                XlsxFileSplitByRowRange(fileItem.getPath(), tagIndex);
            }
        }
        System.exit(0);
/*
//          测试请求结果
        ArrayList<String> resultList = new ArrayList<>();
        ArrayList<String> errorResultList = new ArrayList<>();
        for (String addrStr : addressSet) {
            String resultStr = RequestUtils.request(StringUtils.deleteWhitespace(addrStr));
            if (resultStr != null && resultStr.contains("result")) {
                JSONParser jsonParser = new JSONParser();
                try {
//                        System.out.println(addrStr + "------解析成功数据：" + jsonParser.parse(resultStr));
                    resultList.add(resultStr);
                } catch (Exception e) {
//                        System.err.println(addrStr + "------解析失败数据：" + resultStr);
                    errorResultList.add(addrStr);
                }
            }
        }
        System.out.println("总地址个数：" + addressSet.size());
        System.out.println("解析成功的地址个数：" + resultList.size());
        System.out.println("解析失败的地址个数：" + errorResultList.size());*/
    }
}