excel图表操作

最近在做excel相关的业务，除了对比多张表中单元格数据之外还有图表相关的操作。大多数文章是在表格中填充数据，创建图表。对于图表操作的文章很少，工作完成之余写下这片博客，希望对正在做此类工作的同学有所帮助。技术类型 业务基于 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获取图表的相关数据、属性相对便利。