通过poi获取图表在excel中的位置

excel图表操作

1. 最近在做excel相关的业务，除了对比多张表中单元格数据之外还有图表相关的操作。大多数文章是在表格中填充数据，创建图表。对于图表操作的文章很少，工作完成之余写下这片博客，希望对正在做此类工作的同学有所帮助。
2. 技术类型
   业务基于 springboot-2.1.1、apache-poi-4.1.2

poi推荐使用最新版本 4.x, 3.x版本一些相关的操作在4.x中做了新的扩展 。 链接: [http://poi.apache.org/apidocs/index.html]

下面直接上代码

import com.evan.exceltest.common.BaseResponse;
import com.evan.exceltest.common.ExcelCoumnSeries;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.Objects;

public class ExcelChartSupport {

    private MultipartFile f0,f1;

    public ExcelChartSupport(MultipartFile f0,MultipartFile f1){
        this.f0 = f0;
        this.f1 = f1;
    }

    public BaseResponse check(){
        InputStream isF0 = null;
        InputStream isF1 = null;

        try {
            isF0 = f0.getInputStream();
            isF1 = f1.getInputStream();
            //基于 07 版本之后
            XSSFWorkbook w0 = new XSSFWorkbook(isF0);
            XSSFWorkbook w1 = new XSSFWorkbook(isF1);

            XSSFSheet s0 = w0.getSheetAt(0);
            XSSFSheet s1 = w1.getSheetAt(0);

            XSSFDrawing d0 = s0.getDrawingPatriarch();
            XSSFDrawing d1 = s1.getDrawingPatriarch();
            if (Objects.isNull(d0) || Objects.isNull(d1)){
                return BaseResponse.error();
            }
            XSSFShape sh0 = d0.getShapes().get(0);
            XSSFShape sh1 = d1.getShapes().get(0);
            XSSFClientAnchor a0 = (XSSFClientAnchor) sh0.getAnchor();
            XSSFClientAnchor a1 = (XSSFClientAnchor) sh1.getAnchor();
            
            String cellAddress0 = ExcelCoumnSeries.columnSequence().get(a0.getCol1()) + (a0.getRow1() + 1);
            String cellAddress1 = ExcelCoumnSeries.columnSequence().get(a1.getCol1()) + (a1.getRow1() + 1);
            System.out.println("s0图表位置: " + cellAddress0 + ",s1图表位置： " + cellAddress1);
        }catch (IOException e){

        }finally {
            if (isF0 != null){
                try {
                    isF0.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (isF1 != null){
                try {
                    isF1.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return BaseResponse.success();
    }
}

excel单元格编号工具类

import java.util.ArrayList;
import java.util.List;


public class ExcelCoumnSeries {

    private static Integer RANGE = 200;
    static List<String> excelColumnSequence = new ArrayList<>();

    public static List<String> columnSequence() {
        for(int i=0;i<RANGE;i++){
            excelColumnSequence.add(toLetter(i));
        }
        return excelColumnSequence;
    }
    private static String toLetter(int column){
        int i=column/26;
        int j=column-(i*26);
        return letter(i-1)+letter(j);
    }
    private static String letter(int num){
        if(num>=0&&num<=26){
            return (char)('A'+num)+"";
        }else{
            return "";
        }
    }
}

结语：代码是基于.xlsx版本的操作,对与.xls获取图表的相关数据、属性相对便利。