肿瘤康复网 > 使用POI实现读取Excel数据并生成柱状图折线图饼状图的工具类

使用POI实现读取Excel数据并生成柱状图折线图饼状图的工具类

时间：2023-05-18 16:13:14

相关推荐

最近有一个需求是要通过程序自动根据excel中的数据来动态生成相应的图表。其中就有柱状图，折线图，饼状图。但是没想到用wps生成非常的简单，一点就可以生成，使用POI生成的过程非常的难受。不得不将这次经验记录在博客中，以备后用。也奉献给大家~

一、数据生成

首先我们先创建一个版本以上的excel文件（以

xlsx结尾）。

将以下数据放入sheet1。

手机型号测评分数销售数量OPPO Reno9 Pro+ 5G12103657523423Xiaomi 12 Pro天现版1187600343242ROG游戏手机6 天现至尊版1187449535362一加 Ace Pro118323483234小米12S Pro11768502346625Redmi K50 至尊版11725174324324Xiaomi 12S11643093424234联想救者 Y901164235234434小米MIX FOLD 2116410932423432iQo0 10 Pro11512589403424

如图所示

二、在Java工程中引入POM

<dependencies><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.xmlbeans</groupId><artifactId>xmlbeans</artifactId><version>3.1.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>ooxml-schemas</artifactId><version>1.4</version></dependency></dependencies>

三、将工具类引入

package com.icbc;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xddf.usermodel.PresetColor;import org.apache.poi.xddf.usermodel.XDDFColor;import org.apache.poi.xddf.usermodel.XDDFShapeProperties;import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;import org.apache.poi.xddf.usermodel.chart.*;import org.apache.poi.xssf.usermodel.*;import org.openxmlformats.schemas.drawingml.x.chart.CTDLbls;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.*;/*** ClassName: ExcelChartUtils <br/>* Description: 导出excel中绘制图表<br/>** @author stalin<br />* @since JDK 1.8*/public class ExcelChartUtils {public static final String READ_FILE_PATH = "C:\\Users\\Administrator\\Desktop\\数据.xlsx";public static void main(String[] args) throws IOException {ExcelChartUtils excelChartUtils=new ExcelChartUtils();FileInputStream fis = new FileInputStream(READ_FILE_PATH);XSSFWorkbook wb = new XSSFWorkbook(fis);String sheetName = "手机评分折线统计图";String sheetName3 = "手机销售量占比饼状图";String sheetName2 = "手机销售量柱状图";excelChartUtils.exportLineDiagram(wb,sheetName2);excelChartUtils.exportPieDirgram(wb,sheetName3);excelChartUtils.exportBarDiagram(wb,sheetName);}public void exportPieDirgram(XSSFWorkbook wb,String sheetName){XSSFSheet sheet = wb.createSheet(sheetName);// 创建一个画布XSSFDrawing drawing = sheet.createDrawingPatriarch();// 前四个默认0，[0,4]：从0列4行开始;[7,20]:到7列20行结束// 默认宽度(14-8)*12XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 30, 40);// 创建一个chart对象XSSFChart chart = drawing.createChart(anchor);// 标题chart.setTitleText("手机销售占比饼状图");// 标题是否覆盖图表chart.setTitleOverlay(false);// 图例位置XDDFChartLegend legend = chart.getOrAddLegend();legend.setPosition(LegendPosition.TOP_RIGHT);//解析数据Map<String, List<String>> dataMap = ExcelChartUtils.getData(wb,0,0,1);;List<String> keylist = dataMap.get("key");List<String> valueList = dataMap.get("value");List<Integer> vList = new ArrayList<>();for(int i = 0;i<valueList.size();i++){if(valueList.get(i).isEmpty()){vList.add(0);}else{vList.add(Integer.valueOf(valueList.get(i).split("\\.")[0]));}}//XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));//从指定表格中的位置上获取对应数据XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(keylist.toArray(new String[keylist.size()]));// 数据1，单元格范围位置[1, 0]到[1, 6]// XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(vList.toArray(new Integer[vList.size()]));// XDDFChartData data = chart.createData(ChartTypes.PIE3D, null, null);XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);// 设置为可变颜色data.setVaryColors(true);// 图表加载数据data.addSeries(countries, area);// 绘制chart.plot(data);CTDLbls dLbls = chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDLbls();dLbls.addNewShowVal().setVal(false);dLbls.addNewShowLegendKey().setVal(false);dLbls.addNewShowCatName().setVal(true);// 类别名称dLbls.addNewShowSerName().setVal(false);dLbls.addNewShowPercent().setVal(true);// 百分比dLbls.addNewShowLeaderLines().setVal(true);// 引导线dLbls.setSeparator("\n");// 分隔符为分行符dLbls.addNewDLblPos().setVal(org.openxmlformats.schemas.drawingml.x.chart.STDLblPos.Enum.forString("inEnd"));// 数据标签内// 打印图表的xmlSystem.out.println(chart.getCTChart());}public void exportLineDiagram(XSSFWorkbook wb,String sheetName) throws IOException {FileOutputStream fileOut = null;XSSFSheet sheet = wb.createSheet(sheetName);// 创建一个画布XSSFDrawing drawing = sheet.createDrawingPatriarch();// 前四个默认0，[0,5]：从0列5行开始;[7,26]:到7列26行结束// 默认宽度(14-8)*12XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 30, 40);// 创建一个chart对象XSSFChart chart = drawing.createChart(anchor);// 标题chart.setTitleText("手机评分折线统计图");// 标题覆盖chart.setTitleOverlay(false);// 图例位置XDDFChartLegend legend = chart.getOrAddLegend();legend.setPosition(LegendPosition.TOP);// 分类轴标(X轴),标题位置XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.LEFT);// bottomAxis.setTitle("国家");// 值(Y轴)轴,标题位置XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.BOTTOM);// leftAxis.setTitle("面积大小");Map<String, List<String>> dataMap = ExcelChartUtils.getData(wb,0,0,1);List<String> keylist = dataMap.get("key");List<String> valueList = dataMap.get("value");List<Integer> vList = new ArrayList<>();for(int i = 0;i<valueList.size();i++){if(valueList.get(i).isEmpty()){vList.add(0);}else{vList.add(Integer.valueOf(valueList.get(i).split("\\.")[0]));}}// keylist.remove(0);// valueList.remove(0);// CellRangeAddress(起始行号，终止行号，起始列号，终止列号）// 分类轴标(X轴)数据，单元格范围位置[0, 0]到[0, 6]//XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));//从指定表格中的位置上获取对应数据XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(keylist.toArray(new String[keylist.size()]));// 数据1，单元格范围位置[1, 0]到[1, 6]// XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(vList.toArray(new Integer[vList.size()]));// bar：条形图，XDDFLineChartData bar = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);// 设置为可变颜色bar.setVaryColors(false);// 如果需要设置成自己想要的颜色，这里可变颜色要设置成false// 图表加载数据，条形图XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) bar.addSeries(countries, area);// 条形图例标题series1.setTitle("手机评分折线统计图", null);XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE));// 条形图，填充颜色series1.setFillProperties(fill);// 绘制chart.plot(bar);// CTBarSer ser = chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(0);// CTLegend legend2 = chart.getCTChartSpace().getChart().getLegend();//更详细的图例设置// 打印图表的xmlSystem.out.println(chart.getCTChart());}public String exportBarDiagram(XSSFWorkbook wb,String sheetName) throws IOException {FileOutputStream fileOut = null;try {XSSFSheet sheet = wb.createSheet(sheetName);// 创建一个画布XSSFDrawing drawing = sheet.createDrawingPatriarch();// 前四个默认0，[0,5]：从0列5行开始;[7,26]:到7列26行结束// 默认宽度(14-8)*12XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 30, 40);// 创建一个chart对象XSSFChart chart = drawing.createChart(anchor);// 标题chart.setTitleText("手机销售量柱状图");// 标题覆盖chart.setTitleOverlay(false);// 图例位置XDDFChartLegend legend = chart.getOrAddLegend();legend.setPosition(LegendPosition.TOP);// 分类轴标(X轴),标题位置XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);// bottomAxis.setTitle("国家");// 值(Y轴)轴,标题位置XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);// leftAxis.setTitle("面积大小");Map<String, List<String>> dataMap = ExcelChartUtils.getData(wb,0,0,1);List<String> keylist = dataMap.get("key");List<String> valueList = dataMap.get("value");List<Integer> vList = new ArrayList<>();for(int i = 0;i<valueList.size();i++){if(valueList.get(i).isEmpty()){vList.add(0);}else{vList.add(Integer.valueOf(valueList.get(i).split("\\.")[0]));}}// keylist.remove(0);// valueList.remove(0);// CellRangeAddress(起始行号，终止行号，起始列号，终止列号）// 分类轴标(X轴)数据，单元格范围位置[0, 0]到[0, 6]//XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, 6));//从指定表格中的位置上获取对应数据XDDFCategoryDataSource countries = XDDFDataSourcesFactory.fromArray(keylist.toArray(new String[keylist.size()]));// 数据1，单元格范围位置[1, 0]到[1, 6]// XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, 6));XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(vList.toArray(new Integer[vList.size()]));// bar：条形图，XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);// 设置为可变颜色bar.setVaryColors(false);// 如果需要设置成自己想要的颜色，这里可变颜色要设置成false// 条形图方向，纵向/横向：纵向bar.setBarDirection(BarDirection.COL);// 图表加载数据，条形图1XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(countries, area);// 条形图例标题series1.setTitle("手机销售量柱状图", null);XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE));// 条形图，填充颜色series1.setFillProperties(fill);// 绘制chart.plot(bar);// CTBarSer ser = chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(0);// CTLegend legend2 = chart.getCTChartSpace().getChart().getLegend();//更详细的图例设置// 打印图表的xmlSystem.out.println(chart.getCTChart());fileOut = new FileOutputStream(READ_FILE_PATH);wb.write(fileOut);} catch (Exception e) {e.printStackTrace();} finally {wb.close();if (fileOut != null) {fileOut.close();}}return "success";}/**** @param workbook 待读取excel* @param sheetIndex 读取某一列sheet页* @param keyNum 第一个数据列，字符串类型* @param valueNum 第二个数据列，数字类型* @return*/public static Map<String,List<String>> getData( XSSFWorkbook workbook, int sheetIndex,int keyNum,int valueNum){Map<String, List<String>> resMap = new HashMap<>();FileInputStream fis = null;//XSSFWorkbook workbook = null;List<String> keylist = new ArrayList();List<String> valueList = new ArrayList<>();try {// fis = new FileInputStream(filePath);// workbook = new XSSFWorkbook(fis);Sheet sheet = workbook.getSheetAt(sheetIndex);Row row;Cell cell;for (int i = 1; i < sheet.getLastRowNum(); i++) {row = sheet.getRow(i);cell = row.getCell(keyNum); // 0 is the column indexSystem.out.println(cell.getStringCellValue());keylist.add(cell.getStringCellValue());cell = row.getCell(valueNum); // 0 is the column indexdouble numericCellValue;if(cell.getCellType().equals(CellType.NUMERIC)){numericCellValue = cell.getNumericCellValue();}else {numericCellValue = Double.valueOf(cell.getStringCellValue());}valueList.add( String.valueOf(numericCellValue) );}System.out.println("name:"+keylist);System.out.println("value:"+valueList);} catch (Exception e) {e.printStackTrace();}// resMap.put("key",keylist.toArray(new String[keylist.size()]));// resMap.put("value",valueList.toArray(new String[valueList.size()]));resMap.put("key",keylist);resMap.put("value",valueList);return resMap;}}

注：

1、读取数据需要注意的就是第一列是文字描述，第二列是对应的数值。否则可能导致解析异常。详见代码中getData方法。

2、如果感觉图标太大可通过调整drawing.createAnchor方法中的坐标进行修改。

XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 30, 40);