java中对ecel表的读取写入

1.依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

2.引入包和代码

package org.example;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.HashMap;
import java.util.Map;

public class NewTest {
    public static void main(String[] args) {

        //读入文件
        String inputFilePath = "C:\\Users\\zhaowen_chen\\Desktop\\Analysis.xlsx";
        Workbook workbook = null;
        try {
            workbook = new XSSFWorkbook(new FileInputStream(inputFilePath));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        Sheet sheet = workbook.getSheetAt(0);

        // 用于存储统计结果
        Map<String, Integer> applyCountMap = new HashMap<>();
        Map<String, Integer> shortageCountMap = new HashMap<>();

        //双重Map，用于统计
        Map<String, Map<String, Integer>> map = new HashMap<>();


        // 遍历Excel表中的每一行（跳过标题行）
        for (Row row : sheet) {
            // 接收数据并存储统计结果
            Map<String, Integer> productApplyCountMap = new HashMap<>();
            if (row.getRowNum() == 0) continue; // 跳过标题行

            //项目编号（取数据的方式row.getCell(1).getStringCellValue(),1为单元格的位置
            String applicant = row.getCell(1).getStringCellValue();

            if (!applicant.isEmpty()) {
                //全部参与人
                String applyStr = row.getCell(32).getStringCellValue();
                //已打卡人员
                String shortageStr1 = row.getCell(33).getStringCellValue();
                //未打卡人员
                String shortageStr2 = row.getCell(34).getStringCellValue();

                //用函数把单元格内的数据以，分割成一个字符串数组
                String[] applyProducts = applyStr.split(",");
                String[] shortageProducts = shortageStr1.split(",");
                String[] shortageProducts2 = shortageStr2.split(",");


              // 统计参与人名词出现次数
                for (String product : applyProducts) {
                    if (!product.isEmpty()) {
                        productApplyCountMap.put(product, productApplyCountMap.getOrDefault(product, 0) + 1);
                    }
                }
                // 参与人名词出现次数减去已打卡人名出现次数和未打卡人员名字出现次数
                for (String product : shortageProducts) {
                    if (!product.isEmpty()) {
                        productApplyCountMap.put(product, productApplyCountMap.getOrDefault(product, 100) - 10);
                    }
                }
                for (String product : shortageProducts2) {
                    if (!product.isEmpty()) {
                        productApplyCountMap.put(product, productApplyCountMap.getOrDefault(product, 1000) - 20);
                    }
                }

                if (applyProducts.length<shortageProducts.length){
                    System.out.println(applicant);
                }
                map.put(applicant, productApplyCountMap);

            }


        }


        // 创建新的Excel文件
        String outputFilePath = "C:\\Users\\zhaowen_chen\\Desktop\\analysis_result.xlsx";
        Workbook outputWorkbook = new XSSFWorkbook();
        Sheet outputSheet = outputWorkbook.createSheet("Analysis Result");

        // 写入标题
        Row titleRow = outputSheet.createRow(0);


        titleRow.createCell(0).setCellValue("申请人");
        titleRow.createCell(1).setCellValue("申请次数");
        titleRow.createCell(2).setCellValue("缺货次数");
//        // 写入统计结果
        int rowNum = 1;
        // 写入统计结果
        rowNum = 1;
        for (Map.Entry<String, Map<String, Integer>> outerEntry : map.entrySet()) {
            String outerKey = outerEntry.getKey();
            Map<String, Integer> innerMap = outerEntry.getValue();
            for (Map.Entry<String, Integer> innerEntry : innerMap.entrySet()) {
                String innerKey = innerEntry.getKey();
                Integer value = innerEntry.getValue();
                Row row = outputSheet.createRow(rowNum++);
                row.createCell(1).setCellValue(outerKey);
                row.createCell(0).setCellValue(innerKey);
                //行数加1,统计已打卡人员
                if (value==-9){
                    row.createCell(2).setCellValue(innerKey);
                }
                if (value==-19){
                    row.createCell(3).setCellValue(innerKey);
                }
            }
        }


        // 保存Excel文件
        try (FileOutputStream outputStream = new FileOutputStream(outputFilePath)) {
            outputWorkbook.write(outputStream);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        // 关闭资源
        try {
            workbook.close();
            outputWorkbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }



    }
}