【干货:Excel中生成数据统计图表的三种实现方式】

杭州杨少
1,192 阅读9分钟

背景

 业务中,客户需要导出各种数据统计报表,如果客户要求,我希望导出的报表中能插入这些数据的统计图表该如何操作,如图:
 在之前的一篇文章中,介绍了Excel如何插入图片,即先生成统计图片,然后再将图片插入到excel。
 这里我继续从纯后端的方式来演示如何生成excel图表

【干货:Excel中插入图片的两种方式】

image.png

POI

引入POM
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>4.1.2</version>
</dependency>
调用api生成柱状图
/**
 * POI EXCEL 图表-柱状图
 */
public class ApachePoiBarChart5 {

    public static void main(String[] args) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook("/Users/poi/生成图表/数据表.xlsx");

        //String sheetName = "Sheet1";
        FileOutputStream fileOut = null;
        try {
           // XSSFSheet sheet = wb.createSheet(sheetName);
            XSSFSheet sheet = wb.getSheet("汇总");
            // 创建一个画布
            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            // 前四个默认0，[0,5]：从0列5行开始;[7,26]:到7列26行结束
            // 默认宽度(14-8)*12
            // 从0列 28行开始 画图 到11列 40行
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 28, 11, 40);
            // 创建一个chart对象
            XSSFChart chart = drawing.createChart(anchor);
            // 标题
            chart.setTitleText("一周设备检查量--yh测试");
            // 标题覆盖
            chart.setTitleOverlay(false);

            // 图例位置
            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.TOP);

            // 分类轴标(X轴),标题位置  设置X轴
            XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            //bottomAxis.setTitle("管辖单位");
            // 值(Y轴)轴,标题位置  设置Y轴
            XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
            //leftAxis.setTitle("设备检查量");

            //设置x轴 从1 到 25行 0，0列 注意从0开始计数
            XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, 25, 0, 0));

            //设置y轴 从1到25行 9,9列 注意从0开始计数
            XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 25, 9, 9));

            // bar：条形图，
            XDDFBarChartData bar = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);

            //柱状图中间的那根线 是介于还是 在中间
            leftAxis.setCrossBetween(AxisCrossBetween.MIDPOINT_CATEGORY);
            // 设置为可变颜色
            bar.setVaryColors(false);// 如果需要设置成自己想要的颜色，这里可变颜色要设置成false
            // 设置图形方向 纵向/横向：纵向
            bar.setBarDirection(BarDirection.COL);

            // 图表加载数据，条形图1
            XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) bar.addSeries(countries, area);
            // 条形图例标题
            series1.setTitle("一周设备检查量", null);
            //设置颜色
            XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.GREEN));
            // 条形图，填充颜色
            series1.setFillProperties(fill);

            // 绘制
            chart.plot(bar);
            // CTBarSer ser = chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(0);
            // CTLegend legend2 = chart.getCTChartSpace().getChart().getLegend();//更详细的图例设置

            // 打印图表的xml
            System.out.println(chart.getCTChart());

            // 将输出写入excel文件
            String filename = "/Users/poi/生成图表/演示自动生成数据表.xlsx";
            fileOut = new FileOutputStream(filename);
            wb.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            wb.close();
            if (fileOut != null) {
                fileOut.close();
            }
        }

    }

}

image.png

调用api生成折线图
/**
 * POI EXCEL 图表-折线图
 */
public class ApachePoiLineChart4 {

    public static void main(String[] args) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook("/Users/poi/生成图表/曲线图.xlsx");
        String sheetName = "Sheet1";
        FileOutputStream fileOut = null;
        try {
            XSSFSheet sheet = wb.getSheet("汇总");
           /*
            // 第一行，国家名称
            Row row = sheet.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue("俄罗斯");
            cell = row.createCell(1);
            cell.setCellValue("加拿大");
            cell = row.createCell(2);
            cell.setCellValue("美国");
            cell = row.createCell(3);
            cell.setCellValue("中国");
            cell = row.createCell(4);
            cell.setCellValue("巴西");
            cell = row.createCell(5);
            cell.setCellValue("澳大利亚");
            cell = row.createCell(6);
            cell.setCellValue("印度");
            // 第二行，乡村地区
            row = sheet.createRow(1);
            cell = row.createCell(0);
            cell.setCellValue(17098242);
            cell = row.createCell(1);
            cell.setCellValue(9984670);
            cell = row.createCell(2);
            cell.setCellValue(9826675);
            cell = row.createCell(3);
            cell.setCellValue(9596961);
            cell = row.createCell(4);
            cell.setCellValue(8514877);
            cell = row.createCell(5);
            cell.setCellValue(7741220);
            cell = row.createCell(6);
            cell.setCellValue(3287263);
            // 第三行，农村人口
            row = sheet.createRow(2);
            cell = row.createCell(0);
            cell.setCellValue(14590041);
            cell = row.createCell(1);
            cell.setCellValue(35151728);
            cell = row.createCell(2);
            cell.setCellValue(32993302);
            cell = row.createCell(3);
            cell.setCellValue(14362887);
            cell = row.createCell(4);
            cell.setCellValue(21172141);
            cell = row.createCell(5);
            cell.setCellValue(25335727);
            cell = row.createCell(6);
            cell.setCellValue(13724923);
            // 第四行，面积平局
            row = sheet.createRow(3);
            cell = row.createCell(0);
            cell.setCellValue(9435701.143);
            cell = row.createCell(1);
            cell.setCellValue(9435701.143);
            cell = row.createCell(2);
            cell.setCellValue(9435701.143);
            cell = row.createCell(3);
            cell.setCellValue(9435701.143);
            cell = row.createCell(4);
            cell.setCellValue(9435701.143);
            cell = row.createCell(5);
            cell.setCellValue(9435701.143);
            cell = row.createCell(6);
            cell.setCellValue(9435701.143);
            // 第四行，人口平局
            row = sheet.createRow(4);
            cell = row.createCell(0);
            cell.setCellValue(22475821.29);
            cell = row.createCell(1);
            cell.setCellValue(22475821.29);
            cell = row.createCell(2);
            cell.setCellValue(22475821.29);
            cell = row.createCell(3);
            cell.setCellValue(22475821.29);
            cell = row.createCell(4);
            cell.setCellValue(22475821.29);
            cell = row.createCell(5);
            cell.setCellValue(22475821.29);
            cell = row.createCell(6);
            cell.setCellValue(22475821.29);*/

            // 创建一个画布
            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            // 前四个默认0，[0,5]：从0列5行开始;[7,26]:到7列26行结束
            // 默认宽度(14-8)*12
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 17, 13, 26, 25);
            // 创建一个chart对象
            XSSFChart chart = drawing.createChart(anchor);
            // 标题
            chart.setTitleText("隐患整改率--yh测试");
            // 标题覆盖
            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("隐患整改率");

            //设置X轴 从0开始
            XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, 25, 0, 0));

            //设置Y轴
            XDDFNumericalDataSource<Double> area = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 25, 11, 11));
            // XDDFNumericalDataSource<Integer> area = XDDFDataSourcesFactory.fromArray(new Integer[] {17098242,9984670,9826675,9596961,8514877,7741220,3287263});

            // 数据1，单元格范围位置[2, 0]到[2, 6]
            // XDDFNumericalDataSource<Double> population = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, 6));

            // LINE：折线图，
            XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);

            // 图表加载数据，折线1
            XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) data.addSeries(countries, area);
            // 折线图例标题
            series1.setTitle("隐患整改率", null);
            // 设置生成是曲线还是直线 true直线
            series1.setSmooth(true);
            // 设置标记大小
            series1.setMarkerSize((short) 3);
            // 设置标记样式，星星  设置曲线上标记的样式
            series1.setMarkerStyle(MarkerStyle.CIRCLE);
           // series1.setFillProperties(new XDDFSolidFillProperties());

            // 图表加载数据，折线2
   /*         XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) data.addSeries(countries, population);
            // 折线图例标题
            series2.setTitle("人口", null);
            // 曲线
            series2.setSmooth(true);
            // 设置标记大小
            series2.setMarkerSize((short) 6);
            // 设置标记样式，正方形
            series2.setMarkerStyle(MarkerStyle.SQUARE);*/

            // 图表加载数据，平均线3
            // 数据1，单元格范围位置[2, 0]到[2, 6]
           /* XDDFNumericalDataSource<Double> population3 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(3, 3, 0, 6));
            XDDFLineChartData.Series series3 = (XDDFLineChartData.Series) data.addSeries(countries, population3);
            // 折线图例标题
            series3.setTitle("面积平均", null);
            // 直线
            series3.setSmooth(false);
            // 设置标记大小
            // series3.setMarkerSize((short) 3);
            // 设置标记样式，正方形
            series3.setMarkerStyle(MarkerStyle.NONE);
            // 折线图LineChart
            // XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.CHARTREUSE));
            XDDFLineProperties line = new XDDFLineProperties();
            // line.setFillProperties(fill);
            // line.setLineCap(LineCap.ROUND);
            line.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));// 虚线
            // XDDFShapeProperties shapeProperties = new XDDFShapeProperties();
            // shapeProperties.setLineProperties(line);
            // series3.setShapeProperties(shapeProperties);
            series3.setLineProperties(line);

            // 图表加载数据，平均线3
            // 数据1，单元格范围位置[2, 0]到[2, 6]
            XDDFNumericalDataSource<Double> population4 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(4, 4, 0, 6));
            XDDFLineChartData.Series series4 = (XDDFLineChartData.Series) data.addSeries(countries, population4);
            // 折线图例标题
            series4.setTitle("人口平均", null);
            // 直线
            series4.setSmooth(false);
            // 设置标记大小
            // series4.setMarkerSize((short) 3);
            // 设置标记样式，正方形
            series4.setMarkerStyle(MarkerStyle.NONE);
            XDDFLineProperties line4 = new XDDFLineProperties();
            line4.setPresetDash(new XDDFPresetLineDash(PresetLineDash.DOT));// 虚线
            series4.setLineProperties(line);*/

            // 绘制
            chart.plot(data);

            // 打印图表的xml
             System.out.println(chart.getCTChart());

            // 将输出写入excel文件
            String filename = "/Users/poi/生成图表/演示生成曲线图.xlsx";
            fileOut = new FileOutputStream(filename);
            wb.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            wb.close();
            if (fileOut != null) {
                fileOut.close();
            }
        }

    }

}

image.png

JFree

引入POM
<dependency>
   <groupId>jfree</groupId>
   <artifactId>jfreechart</artifactId>
   <version>1.0.13</version>
</dependency>
实现柱状图
public class TestPoiAndJfreeChart2 {

    public static void main(String[] args) throws Exception {
        // excel2003工作表
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建工作表
        HSSFSheet sheet = wb.createSheet("Sheet 1");
        // 创建字节输出流
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        //如 果不使用Font,中文将显示不出来
        Font font = new Font("新宋体", Font.BOLD, 15);
        // 创建数据
        Map<String, Map<String, Double>> datas =new HashMap<String, Map<String,Double>>();

        Map<String, Double> map1=new HashMap<String, Double>();
        Map<String, Double> map2=new HashMap<String, Double>();
        Map<String, Double> map3=new HashMap<String, Double>();

        map1.put("故障数", (double) 1000);
        map2.put("故障数", (double) 1300);
        map3.put("故障数", (double) 1000);

        //压入数据
        datas.put("设备网络掉线", map1);
        datas.put("CPU利用率高", map2);
        datas.put("磁盘占用高", map3);

        JFreeChart chart = createPort("故障类型比例",datas,"故障类型","数量单位（个）",font);
        // 读取chart信息至字节输出流
        ChartUtilities.writeChartAsPNG(byteArrayOut, chart, 600, 300);
        // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // anchor主要用于设置图片的属性
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 2, (short) 1, (short) 12, (short) 15);
        anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
        // 插入图片
        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

        // excel2003后缀
        FileOutputStream fileOut = new FileOutputStream("/Users/jfree/生成数据表演示.xlsx");
        wb.write(fileOut);
        fileOut.close();
    }

    public static JFreeChart createPort(String title,Map<String,Map<String,Double>> datas,String type,String danwei,Font font){
        try {
            //种类数据集
            DefaultCategoryDataset ds = new DefaultCategoryDataset();


            //获取迭代器：
            Set<Map.Entry<String, Map<String, Double>>> set1 =  datas.entrySet();
            Iterator iterator1= set1.iterator();
            Iterator iterator2;
            HashMap<String, Double> map;
            Set<Map.Entry<String,Double>> set2;
            Map.Entry entry1;
            Map.Entry entry2;

            while(iterator1.hasNext()){
                entry1=(Map.Entry) iterator1.next();

                map=(HashMap<String, Double>) entry1.getValue();
                set2=map.entrySet();
                iterator2=set2.iterator();
                while (iterator2.hasNext()) {
                    entry2= (Map.Entry) iterator2.next();
                    ds.setValue(Double.parseDouble(entry2.getValue().toString()), entry2.getKey().toString(), entry1.getKey().toString());
                }
            }

            //创建柱状图,柱状图分水平显示和垂直显示两种
            JFreeChart chart = ChartFactory.createBarChart(title, type, danwei, ds, PlotOrientation.VERTICAL, true, true, true);

            //设置整个图片的标题字体
            chart.getTitle().setFont(font);

            //设置提示条字体
            font = new Font("宋体", Font.BOLD, 15);
            chart.getLegend().setItemFont(font);

            //得到绘图区
            CategoryPlot plot = (CategoryPlot) chart.getPlot();
            //得到绘图区的域轴(横轴),设置标签的字体
            plot.getDomainAxis().setLabelFont(font);

            //设置横轴标签项字体
            plot.getDomainAxis().setTickLabelFont(font);

            //设置范围轴(纵轴)字体
            plot.getRangeAxis().setLabelFont(font);

            plot.setForegroundAlpha(1.0f);
            return chart;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
}

image.png

e-iceblue

引入POM
<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <url>https://repo.e-iceblue.cn/repository/maven-public/</url>
    </repository>
</repositories>

<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.xls</artifactId>
    <version>13.2.4</version>
</dependency>
生成如数据表
/**
 * Java 给 Excel 图表添加数据表
 * https://www.e-iceblue.cn/spirexls_java_chart/java-create-a-bar-chart-in-excel.html#2
 */
public class AddDataTableToChart {

    public static void main(String[] args) {

        //创建一个Workbook类的对象
        com.spire.xls.Workbook workbook = new com.spire.xls.Workbook();

        //加载Excel文档
        workbook.loadFromFile("/Users/e-iceblue/生成图片/数据表.xlsx");

        //获取第一个工作表
        Worksheet sheet = workbook.getWorksheets().get(0);
        //设置工作表名称
        sheet.setName("汇总");

        //添加图表到工作表
        Chart chart = sheet.getCharts().add(ExcelChartType.Column3DClustered);
        //统计数据 Y轴
        chart.setDataRange(sheet.getRange().get("J1:J26"));
        chart.setSeriesDataFromRange(false);
        //设置位置信息
        chart.setTopRow(28);
        chart.setBottomRow(45);
        chart.setLeftColumn(1);
        chart.setRightColumn(12);
        chart.setChartTitle("一周设备检查量-yh演示效果");
        chart.getChartTitleArea().isBold(true);
        //设置字体大小
        chart.getChartTitleArea().setSize(15);
        ChartSerie cs1 = chart.getSeries().get(0);
        //添加数据标签
        ChartSeries series = chart.getSeries();
        for (int i = 0;i < series.size();i++)
        {
            ChartSerie cs = series.get(i);
           // cs.getFormat().getOptions().isVaryColor(true);
            //打印图形上面的数据
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }
        //X轴
        cs1.setCategoryLabels(sheet.getRange().get("A2:A26"));

        //添加数据表到图表
        chart.hasDataTable(true);

        //保存结果文件
        workbook.saveToFile("/Users/e-iceblue/生成图片/自动生成数据表.xlsx", ExcelVersion.Version2016);
    }
}

image.png

生成如折线图
/**
 * 生成曲线图
 */
public class AddCurveDiagramChart {


    public static void main(String[] args) {

        //创建一个Workbook类的对象
        Workbook workbook = new Workbook();

        //加载Excel文档
        workbook.loadFromFile("/Users/e-iceblue/生成图片/曲线图.xlsx");

        //获取第一个工作表
        Worksheet sheet = workbook.getWorksheets().get(0);
        //设置工作表名称
        sheet.setName("汇总");

        //添加图表到工作表
        Chart chart = sheet.getCharts().add(ExcelChartType.LineStacked);
        //统计数据 Y轴
        chart.setDataRange(sheet.getRange().get("L1:L26"));
        chart.setSeriesDataFromRange(false);
        //设置位置信息
        chart.setTopRow(28);
        chart.setBottomRow(45);
        chart.setLeftColumn(1);
        chart.setRightColumn(18);
        chart.setChartTitle("隐患整改率-yh演示效果");
        chart.getChartTitleArea().isBold(true);
        //设置字体大小
        chart.getChartTitleArea().setSize(15);
        ChartSerie cs1 = chart.getSeries().get(0);
        //添加数据标签
        ChartSeries series = chart.getSeries();
        for (int i = 0;i < series.size();i++)
        {
            ChartSerie cs = series.get(i);
            // cs.getFormat().getOptions().isVaryColor(true);
            //打印图形上面的数据
            cs.getDataPoints().getDefaultDataPoint().getDataLabels().hasValue(true);
        }
        //X轴
        cs1.setCategoryLabels(sheet.getRange().get("A2:A26"));

        //添加数据表到图表
        chart.hasDataTable(true);

        //保存结果文件
        workbook.saveToFile("/Users/e-iceblue/生成图片/自动生成曲线图.xlsx", ExcelVersion.Version2016);
    }
}

image.png

方案对比

e-iceblue
优 excel数据被修改时,生成的统计图会进行动态变化,可编辑

缺 非开源,支持的图表有局限性,开源免费包加密的,更丰富的图表需付费版;
   后端编码周期长,难点在需动态获取需要统计的数据行位置,需要一定的调试时间来做适配
POI
优 excel数据被修改时,生成的统计图会进行动态变化,可编辑
开源免费,社区活跃度高
图表方法丰富,如可生成的图表颜色,文字大小,对齐方式,等

缺 后端编码周期长,难点在需动态获取需要统计的数据行位置,需要一定的调试时间来做适配
Jfree
优 开源免费

缺 Jfree生成的是图片形式,excel数据被修改时,生成的统计图不会进行动态变化,不可编辑
社区活跃度低
个人看法
POI 能满足当前产品导出需求,并且图表方法丰富,知名度高,社区活跃度高,也是市面上大多公司的第一选择

问题解答/备注

图表的动态变化
上面对比poi与e-iceblue时,说图表会动态变化,指的是我excel数据被修改时,对应的统计图也会变化,如下图,我修改了数据,统计图也会变化

image.png

统计图的数据来源
一般可以直接读取已有的excel表,读取里面的数据到内存中,然后生成,如演示的
//创建一个Workbook类的对象
Workbook workbook = new Workbook();
//加载Excel文档
 workbook.loadFromFile("/Users/excel演示/e-iceblue/生成图片/曲线图.xlsx");
 
 或者自己从业务拿到数据,直接通过内存操作,可以看上面的一些注释操
 // 第一行，国家名称
 //Row row = sheet.createRow(0);
 //Cell cell = row.createCell(0);
 //cell.setCellValue("俄罗斯");
 //cell = row.createCell(1);
 //cell.setCellValue("加拿大");
 .........
源码下载

excel生成数据统计图表源码下载

avatar
文章
阅读
粉丝