业主投票数据统计，格式化工具

数据格式为
1幢2402室

2幢902室

3幢1101室

4幢3104室

类似的N行一列表格。

 

格式转换后形成一个任意行任意列的的矩形表格，方便查看。转化效果如下图。

 

 

 

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.HashMap;
import java.util.Map;

/**
 * 帮我处理一下Excel表格，目前表格格式是一列数据，数据格式为1幢2402室。 现在我不需要把它变成一幢一个表格，每个表格是33行，
 * 列是4列的表格。比如1幢2402室， 就是在一号表格里，第24行第二列的格子变成红色。
 */
public class ExcelProcessor {

    public static void main(String[] args) {
        String inputFilePath = "D:\\output\\weijielong.xlsx";
        String outputDirectory = "D:\\output\\lou";

        try {
            processExcelFile(inputFilePath, outputDirectory);
            System.out.println("Excel处理完成！");
        } catch (IOException e) {
            System.err.println("处理Excel文件时出错: " + e.getMessage());
            e.printStackTrace();
        }
    }

    public static void processExcelFile(String inputFilePath, String outputDirectory) throws IOException {
        // 创建输出目录
        File dir = new File(outputDirectory);
        if (!dir.exists()) {
            dir.mkdirs();
        }

        // 读取输入Excel文件
        try (FileInputStream fis = new FileInputStream(inputFilePath); Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet inputSheet = workbook.getSheetAt(0);
            Map<String, Workbook> buildingWorkbooks = new HashMap<>();
            Map<String, Sheet> buildingSheets = new HashMap<>();

            // 创建红色样式
            
//            Font font = workbook.createFont();
//            font.setFontHeightInPoints((short) 10);
            
//            CellStyle redStyle = workbook.createCellStyle();
//            redStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
//            redStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//            redStyle.setFont(font);


            // 遍历输入表格中的每一行
            for (int i = 0; i <= inputSheet.getLastRowNum(); i++) {
                Row row = inputSheet.getRow(i);
                if (row == null)
                    continue;

                Cell cell = row.getCell(0);
                if (cell == null)
                    continue;

                String roomInfo = cell.getStringCellValue();
                if (roomInfo == null || roomInfo.trim().isEmpty())
                    continue;

                // 解析房号信息
                ExcelProcessor excelProcessor=new ExcelProcessor();
                Room room = excelProcessor.parseRoomInfo(roomInfo);
                if (room == null)
                    continue;

                // 获取或创建对应幢的工作簿和工作表
                Workbook buildingWorkbook = buildingWorkbooks.computeIfAbsent(room.building, k -> new XSSFWorkbook());
                Sheet buildingSheet = buildingSheets.computeIfAbsent(room.building, k -> {
                    Sheet sheet = buildingWorkbook.createSheet(k + "幢");
                    // 创建表头
                    Row headerRow = sheet.createRow(0);
                    for (int j = 1; j < 5; j++) {
                        Cell headerCell = headerRow.createCell(j);
                        headerCell.setCellValue("0" + j);
                    }
                    // 创建行头
                    for (int j = 1; j < 34; j++) {
                        Row lineRow = sheet.createRow(j);
                        Cell headerCell = lineRow.createCell(0);
                        headerCell.setCellValue(j + "楼");
                    }
                    return sheet;
                });

                // 计算目标单元格位置
                int targetRowNum = room.floor;
                int targetColNum = room.room;

                // 确保行存在
                Row targetRow = buildingSheet.getRow(targetRowNum);
                if (targetRow == null) {
                    targetRow = buildingSheet.createRow(targetRowNum);
                }

                // 确保单元格存在并设置值
                Cell targetCell = targetRow.getCell(targetColNum);
                if (targetCell == null) {
                    targetCell = targetRow.createCell(targetColNum);
                }
                String val = StringUtils.remove(roomInfo, room.building);
                val = StringUtils.remove(roomInfo, room.building);
                targetCell.setCellValue(StringUtils.remove(val, "室"));
//                targetCell.setCellStyle(redStyle);
            }

            // 保存所有幢的工作簿
            for (Map.Entry<String, Workbook> entry : buildingWorkbooks.entrySet()) {
                String buildingNumber = entry.getKey();
                Workbook buildingWorkbook = entry.getValue();

                String outputFilePath = outputDirectory + "/" + buildingNumber + ".xlsx";
                try (FileOutputStream fos = new FileOutputStream(outputFilePath)) {
                    buildingWorkbook.write(fos);
                }
            }

            // 关闭所有工作簿
            for (Workbook wb : buildingWorkbooks.values()) {
                wb.close();
            }
        }
    }

    private Room parseRoomInfo(String roomInfo) {
        try {
             
            String building ="";
            String huhao="";
            if(StringUtils.contains(roomInfo, "元")) {
                building = StringUtils.substringBefore(roomInfo, "元")+"元";
                huhao=StringUtils.substringBetween(roomInfo,"元","室");
            }else {
                building = StringUtils.substringBefore(roomInfo, "幢")+"幢";    
                huhao=StringUtils.substringBetween(roomInfo,"幢","室");
            }

            System.err.println("building: " + building);

            int floor =0;
            if(huhao.length()==4) {
                floor = Integer.parseInt(huhao.substring(0,2));
            }else {
                floor = Integer.parseInt(huhao.substring(0, 1));
            }

            System.err.println("floor: " + floor);

            int room =0;
            if(huhao.length()==4) {
                room = Integer.parseInt(huhao.substring(2,4));
            }else {
                room = Integer.parseInt(huhao.substring(2, 3));
            }

            System.err.println("room: " + room);
            
            return new Room(building, floor, room);
        } catch (Exception e) {
            System.err.println("解析房号信息失败: " + roomInfo + ", 错误: " + e.getMessage());
            return null;
        }
    }

    class Room {
        String building;
        int floor;
        int room;

        public Room(String building, int floor, int room) {
            this.building = building;
            this.floor = floor;
            this.room = room;
        }
    }
}