workflow_streamliner/src/main/java/cn/kdan/ExcelUtil.java

package cn.kdan;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import java.io.*;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtil {
    public static void main(String[] args) {
        ExcelUtil excelUtil = new ExcelUtil();
        excelUtil.getSheet();
        excelUtil.insert();
        excelUtil.insertWindows();
        excelUtil.statistics();
        excelUtil.statisticsWindows();
    }

    /**
     *根据设备资产表生成各个设备的sheet表单
     */
    private void getSheet() {
        String inputFilePath = "src/main/resources/长沙 - 资产管理(2024.9).xlsx";
        String outputFilePath = "src/main/resources/output.xlsx";;

        try (FileInputStream fis = new FileInputStream(new File(inputFilePath));
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // 读取第一个sheet
            Workbook newWorkbook = new XSSFWorkbook(); // 创建新的工作簿

            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);

                Cell cell1 = row.getCell(15); // 假设第1列为索引0
                Cell cell2 = row.getCell(2); // 假设第2列为索引1
                if(StringUtils.isEmpty(cell2.getStringCellValue())&&StringUtils.isEmpty(cell1.getStringCellValue())){
                    continue;
                }
                String sheetName = cell1.getStringCellValue();
                if(!StringUtils.isEmpty(cell2.getStringCellValue())){
                    sheetName = sheetName+ "(" + cell2.getStringCellValue() +")";
                }
                Sheet newSheet = newWorkbook.createSheet(sheetName); // 创建新的Sheet
                // 创建一个CellStyle来设置样式
                CellStyle blueStyle = newWorkbook.createCellStyle();

                // 设置背景色为蓝色
                blueStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
                blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                // 创建一个白色字体的Font
                Font whiteFont = newWorkbook.createFont();
                whiteFont.setColor(IndexedColors.WHITE.getIndex());
                newSheet.setColumnWidth(0, 60 * 256);
                newSheet.setColumnWidth(1, 60 * 256);
                newSheet.setColumnWidth(2, 40 * 256);
                // 将字体应用到CellStyle
                blueStyle.setFont(whiteFont);
                Row oneRow = newSheet.createRow(0); // 第一行
                // 应用样式到第一行的前三列
                for (int col = 0; col < 3; col++) {
                    Cell cell = oneRow.createCell(col); // 对应的列
                    if(col== 0){
                        cell.setCellValue(cell1.getStringCellValue());
                    }
                    cell.setCellStyle(blueStyle);
                }

                Row twoRow = newSheet.createRow(1); // 第二行
                // 设置第二行前三列的值
                // 注意：我们再次遍历列，但这次是在第二行上创建和设置单元格
                for (int col = 0; col < 3; col++) {
                    Cell cell = twoRow.createCell(col); // 对应的列
                    // 假设我们为每列设置不同的值
                    switch (col) {
                        case 0:
                            cell.setCellValue("软件名称");
                            break;
                        case 1:
                            cell.setCellValue("软件来源");
                            break;
                        case 2:
                            cell.setCellValue("软件版本号");
                            break;
                    }
                    cell.setCellStyle(blueStyle);
                }
            }

            // 写入到新的 Excel 文件
            try (FileOutputStream fos = new FileOutputStream(outputFilePath)) {
                newWorkbook.write(fos);
            }

            newWorkbook.close();
            System.out.println("数据已成功写入到新sheet中。");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 将txt的数据插入到对应人员sheet页
     */
    public void insert() {
        String directoryPath = "src/main/resources/mac";
        String excelFilePath = "src/main/resources/output.xlsx";

        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(new File(excelFilePath)))) {
            File dir = new File(directoryPath);
            File[] directoryListing = dir.listFiles((dir1, name) -> name.endsWith(".txt"));

            if (directoryListing != null) {
                for (File file : directoryListing) {
                    String appName = extractAppNameFromFile(file);
                    for (Sheet sheet : workbook) {
                        if (sheet.getSheetName().contains(appName)) {
                            int rowNum = 0;
                            try (BufferedReader br = new BufferedReader(new FileReader(file))) {
                                String line;
                                while ((line = br.readLine()) != null) {
                                    if (++rowNum > 3) { // Start from the fourth line
                                        String[] columns = line.split("(?<=\\.app)\\s+", 2); // Assuming space as delimiter, adjust as needed
                                        Row row = sheet.createRow(rowNum - 3 + 1) ;
                                        for (int i = 0; i < columns.length && i < 2; i++) {
                                            Cell cell = row.createCell(i);
                                            cell.setCellValue(columns[i]);
                                        }
                                    }
                                }
                            }
                            break;
                        }
                    }
                }
                // Write the workbook in file system
                try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {
                    workbook.write(outputStream);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 统计mac总表
     */
    public void statistics() {
        String directoryPath = "src/main/resources/mac";
        String excelFilePath = "src/main/resources/output.xlsx";

        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(new File(excelFilePath)))) {
            File dir = new File(directoryPath);
            File[] directoryListing = dir.listFiles((dir1, name) -> name.endsWith(".txt"));

            if (directoryListing != null) {
                Map<String, Integer> countMap = new HashMap<>();
                Map<String, Set<String>> secondColumnMap = new HashMap<>(); // 使用Set存储不重复的第二列数据

                for (File file : directoryListing) {
                    try (BufferedReader br = new BufferedReader(new FileReader(file))) {
                        String line;
                        while ((line = br.readLine()) != null) {
                            String[] columns = line.split("(?<=\\.app)\\s+", 2); // Assuming space as delimiter, adjust as needed
                            if (columns.length > 1) {
                                String key = columns[0];
                                String secondValue = columns[1];
                                if (key.equals("")){
                                    System.out.println(file.getAbsoluteFile());
                                }
                                // 更新计数
                                countMap.put(key, countMap.getOrDefault(key, 0) + 1);

                                // 存储不重复的第二列数据
                                secondColumnMap.putIfAbsent(key, new HashSet<>());
                                secondColumnMap.get(key).add(secondValue);
                            }
                        }
                    }
                }

                // 创建sheet并写入数据
                Sheet sheet = workbook.createSheet("安装软件汇总_mac");
                workbook.setSheetOrder(sheet.getSheetName(), 0);
                int rowNum = 1;
                // 创建一个CellStyle来设置样式
                CellStyle blueStyle = workbook.createCellStyle();

                // 设置背景色为蓝色
                blueStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
                blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                // 创建一个白色字体的Font
                Font whiteFont = workbook.createFont();
                whiteFont.setColor(IndexedColors.WHITE.getIndex());
                sheet.setColumnWidth(0, 60 * 256);
                sheet.setColumnWidth(1, 60 * 256);
                sheet.setColumnWidth(2, 40 * 256);
                // 将字体应用到CellStyle
                blueStyle.setFont(whiteFont);
                Row oneRow = sheet.createRow(0); // 第一行
                // 应用样式到第一行的前三列
                for (int col = 0; col < 3; col++) {
                    Cell cell = oneRow.createCell(col); // 对应的列
                    if(col== 0){
                        cell.setCellValue("mac_汇总");
                    }
                    cell.setCellStyle(blueStyle);
                }
                // 写入表头
                Row headerRow = sheet.createRow(rowNum++);
                for (int col = 0; col < 3; col++) {
                    Cell cell = headerRow.createCell(col); // 对应的列
                    // 假设我们为每列设置不同的值
                    switch (col) {
                        case 0:
                            cell.setCellValue("App 名称");
                            break;
                        case 1:
                            cell.setCellValue("使用设备数");
                            break;
                        case 2:
                            cell.setCellValue("软件来源");
                            break;
                    }
                    cell.setCellStyle(blueStyle);
                }

                // 写入数据
                for (Map.Entry<String, Integer> entry : countMap.entrySet()) {
                    Row row = sheet.createRow(rowNum++);
                    row.createCell(0).setCellValue(entry.getKey());
                    row.createCell(1).setCellValue(entry.getValue());

                    // 将不重复的第二列数据合并为一个字符串
                    String secondValues = String.join("\n", secondColumnMap.get(entry.getKey()));
                    Cell sourceCell = row.createCell(2); // 写入第二列数据
                    sourceCell.setCellValue(secondValues);
                    CellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setWrapText(true);
                    sourceCell.setCellStyle(cellStyle);
                }

                // 写入工作簿到文件系统
                try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {
                    workbook.write(outputStream);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 将txt的数据插入到对应人员sheet页
     */
    public void insertWindows() {
        String directoryPath = "src/main/resources/windows";
        String excelFilePath = "src/main/resources/output.xlsx";

        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(new File(excelFilePath)))) {
            File dir = new File(directoryPath);
            File[] directoryListing = dir.listFiles((dir1, name) -> name.endsWith(".txt"));

            if (directoryListing != null) {
                for (File file : directoryListing) {
                    String appName = extractAppNameFromFile(file);
                    for (Sheet sheet : workbook) {
                        if (sheet.getSheetName().contains(appName + ")")) {
                            int rowNum = 0;
                            try (BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF-16LE"))) { // 指定编码为 UTF-16LE
                                String line;
                                while ((line = br.readLine()) != null) {
                                    if (++rowNum > 1) { // Start from the fourth line
                                        // 使用正则表达式找到任意字符后面跟着两个空格的位置
                                        Pattern pattern = Pattern.compile("(.).\\s\\s");
                                        Matcher matcher = pattern.matcher(line);
                                        if (matcher.find()) {
                                            int index = matcher.start() + 2; // 匹配的起始位置加上三个字符长度

                                            Row row = sheet.createRow(rowNum) ;
                                            // 在找到的位置进行分割
                                            String part1 = line.substring(0, index).trim();
                                            Cell cell = row.createCell(0);
                                            cell.setCellValue(part1);
                                            String part2 = line.substring(index).trim(); // 去除前导空格
                                            Cell cell1 = row.createCell(2);
                                            cell1.setCellValue(part2);
                                            // 输出结果
                                            System.out.println("Part 1: " + part1);
                                            System.out.println("Part 2: " + part2);
                                        } else {
                                            System.out.println("No match found.");
                                        }
                                        /*String[] columns = line.split(".\\s\\s", 2); // Assuming space as delimiter, adjust as needed
                                        Row row = sheet.createRow(rowNum - 3 + 1) ;
                                        for (int i = 0; i < columns.length && i < 2; i++) {
                                            if (i == 1) {
                                                Cell cell = row.createCell(i + 1);
                                                cell.setCellValue(columns[i].trim());
                                            } else {
                                                Cell cell = row.createCell(i);
                                                cell.setCellValue(columns[i]);

                                            }
                                        }*/
                                    }
                                }
                            }
                            break;
                        }
                    }
                }
                // Write the workbook in file system
                try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {
                    workbook.write(outputStream);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 统计Windows总表
     */
    public void statisticsWindows() {
        String directoryPath = "src/main/resources/windows";
        String excelFilePath = "src/main/resources/output.xlsx";

        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(new File(excelFilePath)))) {
            File dir = new File(directoryPath);
            File[] directoryListing = dir.listFiles((dir1, name) -> name.endsWith(".txt"));

            if (directoryListing != null) {
                Map<String, Integer> countMap = new HashMap<>();
                Map<String, Set<String>> secondColumnMap = new HashMap<>(); // 使用Set存储不重复的第二列数据

                for (File file : directoryListing) {
                    try (BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF-16LE"))) {
                        String line;
                        while ((line = br.readLine()) != null) {
                            // 使用正则表达式找到任意字符后面跟着两个空格的位置
                            Pattern pattern = Pattern.compile("(.).\\s\\s");
                            Matcher matcher = pattern.matcher(line);
                            if (matcher.find()) {
                                int index = matcher.start() + 3; // 匹配的起始位置加上三个字符长度

                                // 在找到的位置进行分割
                                String key = line.substring(0, index);
                                String secondValue = line.substring(index).trim(); // 去除前导空格

                                if (key.equals("")) {
                                    System.out.println(file.getAbsoluteFile());
                                }
                                // 更新计数
                                countMap.put(key, countMap.getOrDefault(key, 0) + 1);

                                // 存储不重复的第二列数据
                                secondColumnMap.putIfAbsent(key, new HashSet<>());
                                secondColumnMap.get(key).add(secondValue);
                            } else {
                                System.out.println("No match found.");
                            }
                            /*String[] columns = line.split(".\\s\\s", 2); // 按空格分隔
                            if (columns.length > 1) {
                                String key = columns[0];
                                String secondValue = columns[1].trim();
                                if (key.equals("")) {
                                    System.out.println(file.getAbsoluteFile());
                                }
                                // 更新计数
                                countMap.put(key, countMap.getOrDefault(key, 0) + 1);

                                // 存储不重复的第二列数据
                                secondColumnMap.putIfAbsent(key, new HashSet<>());
                                secondColumnMap.get(key).add(secondValue);
                            }*/
                        }
                    }
                }

                // 创建sheet并写入数据
                Sheet sheet = workbook.createSheet("安装软件汇总_windows");
                workbook.setSheetOrder(sheet.getSheetName(), 0);
                int rowNum = 1;
                // 创建一个CellStyle来设置样式
                CellStyle blueStyle = workbook.createCellStyle();

                // 设置背景色为蓝色
                blueStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
                blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

                // 创建一个白色字体的Font
                Font whiteFont = workbook.createFont();
                whiteFont.setColor(IndexedColors.WHITE.getIndex());
                sheet.setColumnWidth(0, 60 * 256);
                sheet.setColumnWidth(1, 60 * 256);
                sheet.setColumnWidth(2, 40 * 256);
                // 将字体应用到CellStyle
                blueStyle.setFont(whiteFont);
                Row oneRow = sheet.createRow(0); // 第一行
                // 应用样式到第一行的前三列
                for (int col = 0; col < 3; col++) {
                    Cell cell = oneRow.createCell(col); // 对应的列
                    if (col == 0) {
                        cell.setCellValue("windows_汇总");
                    }
                    cell.setCellStyle(blueStyle);
                }
                // 写入表头
                Row headerRow = sheet.createRow(rowNum++);
                for (int col = 0; col < 3; col++) {
                    Cell cell = headerRow.createCell(col); // 对应的列
                    // 假设我们为每列设置不同的值
                    switch (col) {
                        case 0:
                            cell.setCellValue("App 名称");
                            break;
                        case 1:
                            cell.setCellValue("使用设备数");
                            break;
                        case 2:
                            cell.setCellValue("版本号");
                            break;
                    }
                    cell.setCellStyle(blueStyle);
                }

                // 写入数据
                for (Map.Entry<String, Integer> entry : countMap.entrySet()) {
                    Row row = sheet.createRow(rowNum++);
                    row.createCell(0).setCellValue(entry.getKey());
                    row.createCell(1).setCellValue(entry.getValue());

                    // 将不重复的第二列数据合并为一个字符串
                    String secondValues = String.join("\n", secondColumnMap.get(entry.getKey()));
                    Cell sourceCell = row.createCell(2); // 写入第二列数据
                    sourceCell.setCellValue(secondValues);
                    CellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setWrapText(true);
                    sourceCell.setCellStyle(cellStyle);
                }

                // 写入工作簿到文件系统
                try (FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {
                    workbook.write(outputStream);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取txt里的名字用于匹配sheet进行填充
     * @param file file
     * @return String
     */
    public static String extractAppNameFromFile(File file) {
        // 定义正则表达式来匹配括号内的内容
        Pattern pattern = Pattern.compile("\\(([^)]+)\\)");

        // 转换文件名为字符串，以便使用正则表达式匹配
        String fileName = file.getName();

        // 创建matcher对象
        Matcher matcher = pattern.matcher(fileName);

        // 查找匹配项
        if (matcher.find()) {
            // 返回匹配到的括号内的内容
            return matcher.group(1);
        }
        System.out.println("txt文件名格式错误"+fileName);
        // 如果没有找到匹配项，返回null或空字符串（根据你的需求）
        return null; // 或者 ""
    }
}