Java 导出时序折线图到Excel

Java 导出时序折线图到Excel代码 记录下

pom.xml中加入相关依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </dependency>
        
        <!-- https://mvnrepository.com/artifact/jfree/jcommon -->
        <dependency>
            <groupId>jfree</groupId>
            <artifactId>jcommon</artifactId>
            <version>1.0.16</version>
        </dependency>
        
        <!-- https://mvnrepository.com/artifact/jfree/jfreechart -->

        <dependency>
            <groupId>jfree</groupId>
            <artifactId>jfreechart</artifactId>
            <version>1.0.13</version>
        </dependency>

相关测试代码

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.axis.DateAxis;
import org.jfree.chart.plot.XYPlot;
import org.jfree.data.time.Day;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;


import java.awt.*;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;


public class TestPoiAndJfreeChart {
    public static void main(String[] args) throws Exception {
        // excel2003工作表
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建工作表
        HSSFSheet sheet = wb.createSheet("超滤量");
        // 创建字节输出流
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        //如 果不使用Font,中文将显示不出来
        Font font = new Font("新宋体", Font.BOLD, 15);
        // 创建数据
        // 创建第一条时序线
        TimeSeries pop1 = new TimeSeries("实际超滤量", Day.class);
        pop1.add(new Day(25, 10, 2021), 1.43D);//Day(日,月,年) 
        pop1.add(new Day(27, 10, 2021), 1.75D);
        pop1.add(new Day(29, 10, 2021), 1.94D);
        pop1.add(new Day(1, 11, 2021), 2.55D);
        pop1.add(new Day(3, 11, 2021), 1.77D);
        pop1.add(new Day(5, 11, 2021), 1.64D);
        pop1.add(new Day(8, 11, 2021), 2.49D);
        pop1.add(new Day(10, 11, 2021), 1.26D);

        // 创建第二条时序线
        TimeSeries pop2 = new TimeSeries("目标超滤量", Day.class);
        pop2.add(new Day(25, 10, 2021), 3.2D);
        pop2.add(new Day(27, 10, 2021), 2.2D);
        pop2.add(new Day(29, 10, 2021), 2.2D);
        pop2.add(new Day(1, 11, 2021), 3D);
        pop2.add(new Day(3, 11, 2021), 2D);
        pop2.add(new Day(5, 11, 2021), 1.8D);
        pop2.add(new Day(8, 11, 2021), 2.8D);
        pop2.add(new Day(10, 11, 2021), 1.8D);

        // 创建一个时序集合
        TimeSeriesCollection dataset = new TimeSeriesCollection();
        dataset.addSeries(pop1);
        dataset.addSeries(pop2);

        JFreeChart chart = createPort("超滤量", dataset, "日期", "", font);


        // 读取chart信息至字节输出流
        ChartUtilities.writeChartAsPNG(byteArrayOut, chart, 1500, 600);
        // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // anchor主要用于设置图片的属性
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 2, 1, (short) 20,  25);
        anchor.setAnchorType(3);
        // 插入图片
        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
        // excel2003后缀  文件生成位置
        FileOutputStream fileOut = new FileOutputStream("D://myExcel10.xls");
        wb.write(fileOut);
        fileOut.close();
    }

    public static JFreeChart createPort(String title, TimeSeriesCollection dataset, String type, String unit, Font font) {
        try {

            JFreeChart chart = ChartFactory.createTimeSeriesChart(title,type,unit,dataset,true,true,false);
            // 设置日期显示格式
            XYPlot plot = chart.getXYPlot();
            DateAxis axis = (DateAxis) plot.getDomainAxis();
            axis.setDateFormatOverride(new SimpleDateFormat("yyyy-MM-dd"));

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

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

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

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

            //设置范围轴(纵轴)字体
            font = new Font("宋体", Font.BOLD, 18);
            plot.getRangeAxis().setLabelFont(font);

            return chart;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

}

运行测试结果截图如下：

其中导出到excel的图片大小调整代码：

// 读取chart信息至字节输出流 调整最后两个参数，影响横轴和纵轴的数据显示的疏密度。
ChartUtilities.writeChartAsPNG(byteArrayOut, chart, 1500, 600);

// anchor主要用于设置图片的属性 这里的属性说明参考：https://www.cnblogs.com/sunyl/p/7527703.html
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 2, 1, (short) 20,  25);

HSSFClientAnchor的属性参考 ：https://www.cnblogs.com/sunyl/p/7527703.html