JAVA操作Excel (POI)

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;

public class ExcelUtils {

    public static Workbook workbook;

    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(".xlsx")) {
                Workbook = new XSSFWorkbook(inputStream);
            } else {
                Workbook = new HSSFWorkbook(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                assert inputStream != null;
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return Workbook;
    }

    public static void newWorkbook() {
        workbook =  new HSSFWorkbook();
    }

    /**
     *  row and column start from 0
     * @param row
     * @param column
     * @param value
     */
    public static void writeData(int row, int column,String value) {
        Sheet sheet;
        try {
             sheet = workbook.getSheetAt(0);
        }catch (IllegalArgumentException e){
            sheet=workbook.createSheet();
        }

        Row row1 = sheet.createRow(row);
        Cell cell = row1.createCell(column);
        cell.setCellValue(value);
    }

    /**
     *  循环找column1 列，当值为targetValue时，设置column2对应行的值为newData
     * @param column1
     * @param targetValue
     * @param newData
     * @param column2
     */
    public static void writeData(int column1,String targetValue,  int column2,String newData) {

        Sheet sheet;
        try {
            sheet = workbook.getSheetAt(0);
        }catch (IllegalArgumentException e){
            sheet=workbook.createSheet();
        }

        int rows1 = sheet.getPhysicalNumberOfRows();

        for (int i = 0; i < rows1; i++) {

            try {
                String value = cellValue(sheet.getRow(i).getCell(column1));
                System.out.println(value);
                if (targetValue.equalsIgnoreCase(value)) {
                    sheet.getRow(i).createCell(column2).setCellValue(newData);
                    break;
                }
            } catch (NullPointerException e) {
                System.out.println("no this cell");
            }
        }
    }

    public static void generateExcel(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();
            }
            workbook=null;
        }
    }

    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) {
        // task1: 新建excel并设置 excel的C3的值为test1
        String path = "src/main/resources/data/test.xlsx";
        newWorkbook();
        writeData(2,2,"test1");
        generateExcel(path);

        // task2: 编辑已存在的excel,设置A2的值为demo
        openExcel(path);
        writeData(1,0,"demo");
        generateExcel(path);

        // task3: 编辑已存在的excel，设置A列值为demo的这行第3列的值为demo3
        openExcel(path);
        writeData(0,"demo",2,"demo3");
        generateExcel(path);
    }


}

task1:

task2:

task3:

    @Test
    public void demo1(){
        String path = "src/main/resources/data/test.xlsx";
        newWorkbook();

        // 设置字体  红色 加粗 15号字体
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short)15);

        // cell style: 上下左右居中
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyle.setFont(font);

        // 合并第一行的第一列到第八列
        CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, 8);
        Sheet sheet = workbook.createSheet();
        Cell cell = sheet.createRow(0).createCell(0);
        cell.setCellValue("标题");
        cell.setCellStyle(cellStyle);
        sheet.addMergedRegion(rangeAddress);

        generateExcel(path);
    }