java实现将数据生成图表至excel导出

一时失言乱红尘 2023-06-23 06:57 44阅读 0赞

## [https://www.jianshu.com/p/6c4f3832c396][https_www.jianshu.com_p_6c4f3832c396] ##

## 1. 目的 ##

> 根据已有数据，手动（java后台）生成图表至excel并导出。用于后台查询到数据后直接创建图表，可以代替直接使用图表信息字符串。

## 2. 说明 ##

>  *  使用jfree图表绘制类库绘制图表，并生成到本地或读取至输出流用于在excel中生成；
>  *  使用poi操作excel；

## 3. 使用jar包 ##

> 使用maven管理jar包，主要包含poi和jfree：

<properties>
            <poi-version>3.11</poi-version>
        </properties>
        <dependencies>
            <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>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>ooxml-schemas</artifactId>
                <version>1.1</version>
            </dependency>
    
            
            <dependency>
                <groupId>jfree</groupId>
                <artifactId>jcommon</artifactId>
                <version>1.0.16</version>
            </dependency>
    
            
            <dependency>
                <groupId>jfree</groupId>
                <artifactId>jfreechart</artifactId>
                <version>1.0.13</version>
            </dependency>
    
        </dependencies>

## 4. 实现 ##

4.1 生成饼图

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
    import org.apache.poi.hssf.usermodel.HSSFPatriarch;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.jfree.chart.ChartFactory;
    import org.jfree.chart.ChartUtilities;
    import org.jfree.chart.JFreeChart;
    import org.jfree.chart.labels.StandardPieSectionLabelGenerator;
    import org.jfree.chart.plot.PiePlot;
    import org.jfree.data.general.DefaultPieDataset;
    
    import java.awt.*;
    import java.io.ByteArrayOutputStream;
    import java.io.FileOutputStream;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.Map;
    import java.util.Set;
    
    public class TestPoiAndJfreeChart {
    
        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, Double> map=new HashMap<String, Double>();
            map.put("录播主机", (double) 1000);
            map.put("编码器", (double) 700);
            map.put("摄像机", (double) 600);
            map.put("流转码服务器", (double) 400);
            // 创建JFreeChart
            JFreeChart chart = createPort("故障设备类型比例", map, 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(3);
            // 插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
            // excel2003后缀
            FileOutputStream fileOut = new FileOutputStream("h://myExcel4.xls");
            wb.write(fileOut);
            fileOut.close();
        }
    
        public static JFreeChart createPort(String title, Map<String, Double> datas, Font font) {
            try {
                Set<Map.Entry<String, Double>> set = datas.entrySet();
                DefaultPieDataset pds = new DefaultPieDataset();
                Iterator iterator = set.iterator();
                Map.Entry entry;
                while (iterator.hasNext()) {
                    entry = (Map.Entry) iterator.next();
                    pds.setValue(entry.getKey().toString(), Double.parseDouble(entry.getValue().toString()));
                }
                // 生成一个饼图的图表：显示图表的标题、组装的数据、是否显示图例、是否生成贴士以及是否生成URL链接
                JFreeChart chart = ChartFactory.createPieChart(title, pds, true, false, true);
                // 设置图片标题的字体
                chart.getTitle().setFont(font);
                // 得到图块,准备设置标签的字体
                PiePlot plot = (PiePlot) chart.getPlot();
                //设置分裂效果,需要指定分裂出去的key
    //            plot.setExplodePercent("摄像机", 0.1);  分裂效果，可选
                // 设置标签字体
                plot.setLabelFont(font);
                // 设置图例项目字体
                chart.getLegend().setItemFont(font);
                // 设置开始角度
    //            plot.setStartAngle(new Float(3.14f / 2f));  开始角度，意义不大
                //设置plot的前景色透明度
                plot.setForegroundAlpha(0.7f);
                //设置plot的背景色透明度
                plot.setBackgroundAlpha(0.0f);
                //设置标签生成器(默认{0})
                //{0}:key {1}:value {2}:百分比 {3}:sum
                plot.setLabelGenerator(new StandardPieSectionLabelGenerator("{0}({1})/{2}"));  // 一般在{1}后面加单位，如：{0}({1}次)/{2}
                //将内存中的图片写到本地硬盘
    //            ChartUtilities.saveChartAsJPEG(new File("H:/a.png"), chart, 600, 300);
                return chart;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }

>  *  ![format_png][]
>     
>     饼图.png

4.2 生成折线图

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
    import org.apache.poi.hssf.usermodel.HSSFPatriarch;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.jfree.chart.ChartFactory;
    import org.jfree.chart.ChartUtilities;
    import org.jfree.chart.JFreeChart;
    import org.jfree.chart.plot.CategoryPlot;
    import org.jfree.chart.plot.PlotOrientation;
    import org.jfree.data.category.DefaultCategoryDataset;
    
    import java.awt.*;
    import java.io.ByteArrayOutputStream;
    import java.io.FileOutputStream;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.Map;
    import java.util.Set;
    
    public class TestPoiAndJfreeChart1 {
    
        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>();
            Map<String, Double> map4=new HashMap<String, Double>();
            Map<String, Double> map5=new HashMap<String, Double>();
    
            map1.put("故障数量", (double) 20);
            map2.put("故障数量", (double) 30);
            map3.put("故障数量", (double) 25);
            map4.put("故障数量", (double) 50);
            map5.put("故障数量", (double) 40);
    
            //压入数据
            datas.put("一月", map1);
            datas.put("二月", map2);
            datas.put("三月", map3);
            datas.put("四月", map4);
            datas.put("五月", map5);
    
            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(3);
            // 插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
            // excel2003后缀
            FileOutputStream fileOut = new FileOutputStream("h://myExcel5.xls");
            wb.write(fileOut);
            fileOut.close();
        }
    
        public static JFreeChart createPort(String title, Map<String, Map<String, Double>> datas, String type, String unit, 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());
                    }
                }
    
                //创建折线图,折线图分水平显示和垂直显示两种
                // //2D折线图
                JFreeChart chart = ChartFactory.createLineChart(title, type, unit, ds, PlotOrientation.VERTICAL, true, true, true);
                // //3D折线图
    //            JFreeChart chart2 = ChartFactory.createLineChart3D(title, type, unit, ds, PlotOrientation.VERTICAL, true, true, false);
    
                //设置整个图片的标题字体
                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);
    
                //设置范围轴(纵轴)字体
                font = new Font("宋体", Font.BOLD, 18);
                plot.getRangeAxis().setLabelFont(font);
    //            plot.setForegroundAlpha(1.0f);
                return chart;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }

>  *  ![format_png 1][]
>     
>     折线图.png

4.3 生成柱状图

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
    import org.apache.poi.hssf.usermodel.HSSFPatriarch;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.jfree.chart.ChartFactory;
    import org.jfree.chart.ChartUtilities;
    import org.jfree.chart.JFreeChart;
    import org.jfree.chart.labels.StandardPieSectionLabelGenerator;
    import org.jfree.chart.plot.CategoryPlot;
    import org.jfree.chart.plot.PiePlot;
    import org.jfree.chart.plot.PlotOrientation;
    import org.jfree.data.category.DefaultCategoryDataset;
    import org.jfree.data.general.DefaultPieDataset;
    
    import java.awt.*;
    import java.io.ByteArrayOutputStream;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.Map;
    import java.util.Set;
    
    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(3);
            // 插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
            // excel2003后缀
            FileOutputStream fileOut = new FileOutputStream("h://myExcel6.xls");
            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;
            }
        }
    }

>  *  ![format_png 2][]
>     
>     柱状图.png

## 5. 补充 ##

> 上图是在excel中创建图表的简单方式。具体样式或者构造图表的方法可以参考API自行定义。参考链接：[http://www.jfree.org/jfreechart/api/javadoc/org/jfree/chart/ChartFactory.html][http_www.jfree.org_jfreechart_api_javadoc_org_jfree_chart_ChartFactory.html]  
> [https://www.open-open.com/wenku/tag/jfreechart.html][https_www.open-open.com_wenku_tag_jfreechart.html]

作者：AdoredU  
链接：https://www.jianshu.com/p/6c4f3832c396  
来源：简书  
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

[https_www.jianshu.com_p_6c4f3832c396]: https://www.jianshu.com/p/6c4f3832c396
[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNzA2MTIxNy0xMWNjZDJmMWE5N2Q3ZTNlLnBuZz9pbWFnZU1vZ3IyL2F1dG8tb3JpZW50L3N0cmlwfGltYWdlVmlldzIvMi93LzkyNi9mb3JtYXQvd2VicA?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNzA2MTIxNy0wYzdiYmRkNDVhY2NlYmUyLnBuZz9pbWFnZU1vZ3IyL2F1dG8tb3JpZW50L3N0cmlwfGltYWdlVmlldzIvMi93Lzg1Ni9mb3JtYXQvd2VicA?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy8xNzA2MTIxNy02Yjk1ZGZiYjlmZjMyNmM1LnBuZz9pbWFnZU1vZ3IyL2F1dG8tb3JpZW50L3N0cmlwfGltYWdlVmlldzIvMi93LzgyOC9mb3JtYXQvd2VicA?x-oss-process=image/format,png
[http_www.jfree.org_jfreechart_api_javadoc_org_jfree_chart_ChartFactory.html]: https://links.jianshu.com/go?to=http%3A%2F%2Fwww.jfree.org%2Fjfreechart%2Fapi%2Fjavadoc%2Forg%2Fjfree%2Fchart%2FChartFactory.html
[https_www.open-open.com_wenku_tag_jfreechart.html]: https://links.jianshu.com/go?to=https%3A%2F%2Fwww.open-open.com%2Fwenku%2Ftag%2Fjfreechart.html