java读取Excel文件中批注和背景色

1、本文主要介绍如何在java中通过poi获取excel文件中的批注和颜色信息

需要引入poi的依赖包

        <poi.version>3.17</poi.version>  

           <!-- excel -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>${poi.version}</version>
            </dependency>

            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>${poi.version}</version>
            </dependency>

            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>${poi.version}</version>
            </dependency>

 

主要代码如下：

package com.xgxx.common.excel.poi;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;

/**
 * Poi - Excel 读取
 * 2020-12-23 16:51
 * @author win
 */
@Slf4j
@Component
public class PoiExcelReader {

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    /**
     * 默认表头 行数 (后续可调整增加入参动态调整行数)
     */
    private static final Integer DEFAULT_HEAD = 2;

    public static void main(String[] args) {
        String filePath ="C:\\Users\\win\\Desktop\\test\\aa.xlsx";
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filePath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        PoiExcelReader reader = new PoiExcelReader();
        reader.readExcel(inputStream);
    }

    /**
     * 获取工作簿
     * @author guoSt
     * @date 2020/12/23 16:53
     * @param inputStream:
     * @param fileType:
     * @return :
     */
    private Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }


    /**
     * 读取 Excel文件 内容
     * @author guoSt
     * @date 2020/12/23 17:03
     * @param inputStream:
     */
    private  void readExcel(InputStream inputStream) {
        Workbook workbook = null;
        try {

            workbook = getWorkbook(inputStream, XLSX);

            analyzeExcel(workbook);

        } catch (Exception e) {
            log.error(e.toString());
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                log.error("关闭数据流出错！错误信息：" + e.getMessage());
            }
        }
    }
    /**
     * 解析文件内容
     * @author guoSt
     * @date 2020/12/23 17:06
     * @param wb:
     */
    private  void analyzeExcel(Workbook wb){
        //读取第一个 sheet
        Sheet sheet= wb.getSheetAt(0);
        //获取行数
        int rowNum=sheet.getLastRowNum();
        if(rowNum < DEFAULT_HEAD){
            log.info("Excel没有内容需要读取！");
            return;
        }
        //i = 2 跳过表头 (模板表头占了两行)
        for(int i = 2; i <= rowNum ; i++) {
            //获取当前行
            Row row = sheet.getRow(i);
            //获取列数
            int colNum = row.getLastCellNum();

            for (int j = 0; j < colNum; j++) {
                //单元格
                Cell cell = row.getCell(j);
                CellStyle cellStyle = cell.getCellStyle();
                // xlsx 07版
                // 为了解决：NUMERIC cell
                cell.setCellType(CellType.STRING);

                //1、取内容  需要对列数进行特殊判断
                System.out.println(String.format("第：%s行，第%s列，单元格内容：%s", i, j, cell.getStringCellValue()));

                //2、批注信息
                if (cell.getCellComment() != null) {
                    System.out.println("批注：" + cell.getCellComment().getString().toString());

                    //3、取颜色信息
                    XSSFColor xssfColor = (XSSFColor) cellStyle.getFillForegroundColorColor();
                    if (null != xssfColor) {
                        //得到rgb的byte数组
                        byte[] bytes = xssfColor.getRGB();
                        //得到argb十六进制字符串
                        String argbHex = xssfColor.getARGBHex();

                        System.out.print("argbHex：" + argbHex);
                        System.out.print("颜色：" + Arrays.toString(bytes));

                        for (int k = 0; k < bytes.length; k++) {
                            System.out.print(bytes[k] + "-");
                        }
                    }
                }
            }

        }
    }

}