Java POI 读取编辑 Excel

 需要添加POI依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>            

 

1.在用POI处理excel文件之前，必须先判断要处理的文件是不是EXCEL文件

为什么必须要判断文件类型：POI只能处理xlsx和xls 类型的excel文件，如果用POI处理的文件不是excel文件xlsx和xls 类型的excel文件，XSSFWorkbook workbook = new XSSFWorkbook(is); 会抛出NotOfficeXmlFileException异常

org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException: No valid entries or contents found, this is not a valid OOXML (Office Open XML) file

而NotOfficeXmlFileException异常属于RuntimeException， RuntimeException是需要我们编程来避免的，不应该捕捉或者抛出。所以NotOfficeXmlFileException和NullPointerException一样，是我们要在编程中避免的。

怎么判断文件类型： 3.17及以上的POI版本中， 封装了FileMagic类来判断 上传的文件是不是POI可以处理的excel文件。

FileMagic通过判断文件的魔数来判断文件类型

魔数(Magic Number)每个Class文件的头4个字节称为魔数，它的唯一作用是用于确定这个文件是否为一个能被虚拟机接收的Class文件。

参考  POI如何揭开excel文件的神秘面纱（POI判断excel文件格式源码解析）

import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.poifs.filesystem.FileMagic;

public class ExcelUtils {

    public static Boolean isExcelFile(InputStream is) throws IOException {
        Boolean isExcel = false;
        FileMagic fileMagic = FileMagic.valueOf((is));
        if (fileMagic.equals(FileMagic.OOXML)) { // XLSX
            isExcel = true;
        } else if (fileMagic.equals(FileMagic.OLE2)) {// XLS
            isExcel = true;
        }
        return isExcel;
    }
}

2.读取excel

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelClass {

    Logger logger = LoggerFactory.getLogger(ExcelClass.class);

    @Test
    public void readExcel() {

        try {
            String filePath = "C:\\test\\test.xlsx";
            InputStream is = new BufferedInputStream(new FileInputStream(filePath));

            if (ExcelUtils.isExcelFile(is)) {

                XSSFWorkbook workbook = new XSSFWorkbook(is);
                Sheet sheet = workbook.getSheetAt(0);

                Map<String, String> map = new HashMap<>();

                logger.info("sheet row account is " + sheet.getLastRowNum());

                for (int i = 0; i < sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    Cell cell00 = row.getCell(0);
                    Cell cell01 = row.getCell(1);

                    logger.info("cell00 is " + cell00.getStringCellValue().trim());
                    logger.info("cell01 is " + cell01.getStringCellValue().trim());
                    map.put(row.getCell(3).getStringCellValue().trim(), row.getCell(0).getStringCellValue().trim());
                }

                is.close();
            } else {
                logger.error("this is not a excel file: " + filePath);
            }
        } catch (IOException e) {
            logger.error("Error Message: ", e);
        }

    }

}

 

2.使用CellStyle设置单元格的格式：添加边框，居中方式，时间格式和数据格式

3.实现 插入一行 : 先把insertstartRow到最后一行向下移动一行，在移动后的insertstartRow处（移动后留的空白），新建一个新行，这样就实现了数据行的插入

参考 POI shiftRows方法

//把startRow到endRow的 记录 下移 n行
    void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight);
//在rownum处新建一行
    Row createRow(int rownum);

4. 合并单元格 ：public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

参考 POI 合并单元格

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Date;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PoiExcelTest {
    private static String excelFilePath = "C:/TEST/test.xlsx";

    public static void main(String[] args) {
        try {
            updateExcelCell(excelFilePath);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void updateExcelCell(String filePath) throws IOException {

        InputStream is = new FileInputStream(filePath);
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);

        System.out.println(sheet.getLastRowNum());

        // 1. update workbook sheet row cell
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            for (Cell cell : row) {
                if ("field_reference".equals(cell.getStringCellValue().trim())) {
                    cell.setCellValue("fff");
                } else if ("field_credit_date".equals(cell.getStringCellValue().trim())) {
                    cell.setCellValue("ff2020/2/30 12:45");
                }
            }
        }
        // 2.1 set CellStyle : 边框
        CellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setBorderBottom(BorderStyle.THIN);
        cellStyle1.setBorderLeft(BorderStyle.THIN);
        cellStyle1.setBorderRight(BorderStyle.THIN);
        cellStyle1.setBorderBottom(BorderStyle.THIN);

        Cell cell1 = sheet.getRow(10).getCell(0);
        cell1.setCellValue("cell1");
        cell1.setCellStyle(cellStyle1);

        // 2.2 set CellStyle : 对齐方式
        CellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);

        Cell cell2 = sheet.getRow(11).getCell(0);
        cell2.setCellValue("cell2");
        cell2.setCellStyle(cellStyle2);

        // 2.3 set CellStyle : 数字格式
        DataFormat numberDataFormat = workbook.createDataFormat();
        CellStyle cellStyle3 = workbook.createCellStyle();
        cellStyle3.setDataFormat(numberDataFormat.getFormat("#,##0.00"));

        Cell cell3 = sheet.getRow(12).getCell(0);
        cell3.setCellValue(300000002);
        cell3.setCellStyle(cellStyle3);

        // 2.4 set CellStyle : 日期时间格式
        DataFormat dateDataFormat = workbook.createDataFormat();
        CellStyle cellStyle4 = workbook.createCellStyle();
        cellStyle4.setDataFormat(dateDataFormat.getFormat("yyyy-MM-dd"));

        Cell cell4 = sheet.getRow(13).getCell(0);
        cell4.setCellValue(new Date());
        cell4.setCellStyle(cellStyle4);

        // 3. 用shiftRows() 和 createRow() 实现插入一行
        // 循环3此 就是插入 3行
        for (int i = 0; i < 3; i++) {
            sheet.shiftRows(5, sheet.getLastRowNum() + i, 1, true, false);
            Row row = sheet.createRow(5);

            Cell cell01 = row.createCell(0);
            cell01.setCellValue("aaa");

            Cell cell02 = row.createCell(1);
            cell02.setCellValue("bbb");

            Cell cell03 = row.createCell(2);

            Cell cell04 = row.createCell(3);

            // 4.合并单元格 合并第5行 的第2,3,4列
            CellRangeAddress region = new CellRangeAddress(5, 5, 1, 3);
            sheet.addMergedRegion(region);
        }

        OutputStream out = new FileOutputStream(filePath);
        workbook.write(out);

        out.flush();
        out.close();
        is.close();
    }

}