JAVA操作Excel (POI)

package com.quantum.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;

public class ExcelUtils {

    public static Workbook workbook;
    public static ArrayList<String> activeManage;


    public static void openExcel(String filepath) {
        ExcelUtils.workbook = getWorkbook(filepath);
    }

    public static Workbook getWorkbook(String filepath) {
        FileInputStream inputStream = null;
        Workbook Workbook = null;
        try {
            inputStream = new FileInputStream(filepath);
            if (filepath.endsWith(".xls")) {
                Workbook = new HSSFWorkbook(inputStream);
            } else {
                Workbook = new XSSFWorkbook(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                assert inputStream != null;
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return Workbook;
    }

    public void modify(String username, String status, String email, String managerEmail, String managerStatus, String manager) {
        openExcel("src/main/resources/data/testData.xls");
        Sheet sheet = workbook.getSheet("checkStatus");

        int rows1 = sheet.getPhysicalNumberOfRows();

        for (int i = 1; i < rows1; i++) {
            // 第一列
            String value = sheet.getRow(i).getCell(0).getStringCellValue();
            System.out.println(value);
            if (username.equalsIgnoreCase(value)) {
                // status
                sheet.getRow(i).createCell(2).setCellValue(status);
                System.out.println("status写入成功");
                // email
                sheet.getRow(i).createCell(1).setCellValue(email);
                System.out.println("email写入成功");

                // manager
                sheet.getRow(i).createCell(3).setCellValue(manager);
                System.out.println("manager写入成功");

                // manager email
                sheet.getRow(i).createCell(4).setCellValue(managerEmail);
                System.out.println("manager email写入成功");

                // manager status
                sheet.getRow(i).createCell(5).setCellValue(managerStatus);
                System.out.println("manager status写入成功");

                break;
            }


        }
        generateExcel(workbook, "src/main/resources/data/testData.xls");


    }

    public static void writeData(String username, String status, int column) {

        Sheet sheet = workbook.getSheetAt(0);

        int rows1 = sheet.getPhysicalNumberOfRows();

        for (int i = 1; i < rows1; i++) {
            // 第一列 username
            String value = cellValue(sheet.getRow(i).getCell(0));
            System.out.println(value);
            if (username.equalsIgnoreCase(value)) {
                // status
                sheet.getRow(i).createCell(column).setCellValue(status);
                System.out.println("status写入成功");
                break;
            }
        }
    }

    public static void generateExcel(Workbook workbook, String pathName) {
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(pathName);
            workbook.write(fileOutputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                assert fileOutputStream != null;
                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }


    }

    public static void getActiveManager() {
        Workbook workbook = getWorkbook("src/main/resources/data/activeManager.xlsx");

        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        ArrayList<String> managerList = new ArrayList<>();

        for (int i = 1; i < lastRowNum; i++) {
            String stringCellValue = sheet.getRow(i).getCell(0).getStringCellValue().trim();
            managerList.add(stringCellValue);
        }

        System.out.println(managerList);
        activeManage = managerList;

    }

    private static String cellValue(Cell cell) {
        String s = "";
        if (cell != null) {
            int cellType = cell.getCellType();
            switch (cellType) {
                case 0:
                    long numericCellValue = (long) cell.getNumericCellValue();
                    s = String.valueOf(numericCellValue);
                    break;
                case 1:
                    s = cell.getStringCellValue();
                    break;
            }
        }

        return s;
    }

    public static void setCellBackgroundAsRed(Cell cell) {

        CellStyle cellStyle = cell.getCellStyle();
        short color = cellStyle.getFillForegroundColor();
        short fillPattern = cellStyle.getFillPattern();

        if (color == IndexedColors.RED.getIndex() && fillPattern == CellStyle.SOLID_FOREGROUND) {
            System.out.println("无需再设置");
        } else {
            CellStyle redCell = workbook.createCellStyle();
            redCell.setFillForegroundColor(IndexedColors.RED.getIndex());
            redCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
            cell.setCellStyle(redCell);
        }

    }

    public static void main(String[] args) {

        String path = "src/main/resources/data/activeUser1.xlsx";
        openExcel(path);
        Sheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        int lastRowNum = sheet.getLastRowNum();

        System.out.println("rows = " + rows);
        System.out.println("lastRowNum = " + lastRowNum);

        ArrayList<String> li2 = new ArrayList<>();


//        for (int i = 1; i < rows; i++) {
//            Row row = sheet.getRow(i);
//            String s2 = cellValue(row.getCell(6));
//            String s3 = cellValue(row.getCell(7));
//            String s4 = cellValue(row.getCell(8));
//
//            if (!"".equals(s2)) {
//                li2.add(s2);
//            }
//            if (!"".equals(s3)) {
//                li2.add(s3);
//            }
//            if (!"".equals(s4)) {
//                li2.add(s4);
//            }
//        }
//
//        for (int i = 1; i < rows; i++) {
//            Row row = sheet.getRow(i);
//            Cell cell = row.getCell(0);
//            String s = cellValue(cell);
//            if (li2.contains(s)) {
//                setCellBackgroundAsRed(cell);
//            }
//            Cell cell1 = row.getCell(1);
//            String s1 = cellValue(cell1);
//            if (li2.contains(s1)) {
//                setCellBackgroundAsRed(cell1);
//            }
//
//        }
//
//        generateExcel(workbook, path);


    }


}