POI系列jar配合jfreeChart制作商业excel报表。附带柱状图源码

首先需要以下几个包

poi-3.14,

poi-oxxm-3.14,

poi-oxxm-schemas-3.12

xmlbeans-2.6.0

dom4j-1.6.1

jfreechart-1.0.19

jcommon-1.0.23

这里不提供下载。请自行搜索maven仓库。自己下

可能在操作过程中需要 org.jfree.ui.*

这个包实在 jcommon 下不在jfreechart中。如果缺少请注意

代码如下。请自己参考

package self.yzx.services;

import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.xssf.usermodel.*;
import org.jfree.chart.ChartColor;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.CategoryAxis;
import org.jfree.chart.axis.NumberAxis;
import org.jfree.chart.axis.NumberTickUnit;
import org.jfree.chart.axis.ValueAxis;
import org.jfree.chart.block.BlockBorder;
import org.jfree.chart.labels.ItemLabelAnchor;
import org.jfree.chart.labels.ItemLabelPosition;
import org.jfree.chart.labels.StandardCategoryItemLabelGenerator;
import org.jfree.chart.plot.CategoryPlot;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.renderer.category.BarRenderer;
import org.jfree.chart.title.LegendTitle;
import org.jfree.chart.title.TextTitle;
import org.jfree.data.category.DefaultCategoryDataset;

import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;

import org.jfree.ui.RectangleEdge;
import org.jfree.ui.TextAnchor;


public class writeExcel {
    public void buildExcel(HttpServletResponse response) {
        try {
            //第一步，创建一个webbook，对应一个Excel文件
            XSSFWorkbook wb = new XSSFWorkbook();
            //第二步，在webbook中添加一个sheet，对应Excel文件中的 sheet
            XSSFSheet sheet = wb.createSheet("测试");
            //第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制
            //XSSFRow row = sheet.createRow(0);
            /*//第四步，创建单元格样式：居中
            XSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            style.setWrapText(true);
*/

            //创建 图表
            DefaultCategoryDataset dataset = new DefaultCategoryDataset();

            dataset.addValue(100, "北京", "苹果");
            dataset.addValue(120, "上海", "苹果");
            dataset.addValue(160, "广州", "苹果");
            dataset.addValue(210, "北京", "梨子");
            dataset.addValue(220, "上海", "梨子");
            dataset.addValue(230, "广州", "梨子");
            dataset.addValue(330, "北京", "葡萄");
            dataset.addValue(340, "上海", "葡萄");
            dataset.addValue(340, "广州", "葡萄");
            dataset.addValue(420, "北京", "香蕉");
            dataset.addValue(430, "上海", "香蕉");
            dataset.addValue(400, "广州", "香蕉");
            dataset.addValue(510, "北京", "荔枝");
            dataset.addValue(530, "上海", "荔枝");
            dataset.addValue(510, "广州", "荔枝");

            JFreeChart chart = buildImg(dataset);
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            ChartUtilities.writeChartAsPNG(byteArrayOut, chart, 1200, 1200);


            XSSFDrawing patriarch = sheet.createDrawingPatriarch();

            // 8代表左上角点 起始位置， 5代表水平 占有几个单元格，20代表 图片的高度跨越单元格到 第20行。注意是 ---  则该图大小是  A8-E20
            XSSFClientAnchor anchor  = new XSSFClientAnchor(0,0,255,255,(short) 0, (short) 8, (short) 5, (short) 20);
            anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
            if(byteArrayOut.size()>0) {
                patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
            }


            response.setContentType("octets/stream");
            response.addHeader("Content-Disposition", "attachment;filename=" + new String("测试excel".getBytes("gb2312"), "ISO8859-1") + ".xlsx");
            OutputStream outputStream = response.getOutputStream();

            wb.write(outputStream);
            wb.close();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    private JFreeChart buildImg(DefaultCategoryDataset dataset) {
        // 设置图片中的字体和颜色以及字号
        Font titleFont = new Font("宋体", Font.PLAIN, 20);
        Font xfont = new Font("宋体", Font.PLAIN, 14);
        Font labelFont = new Font("宋体", Font.PLAIN, 14);

        JFreeChart chart = ChartFactory.createBarChart("我是一个标题", "", "", dataset, PlotOrientation.VERTICAL, true, true, false);
        // 设置图例字体
        chart.getLegend().setItemFont(xfont);
        // 3:设置抗锯齿，防止字体显示不清楚
        //ChartUtils.setAntiAlias(chart);// 抗锯齿
        //ChartUtils.setBarRenderer(chart.getCategoryPlot(), true);
        // 设置标注无边框
        chart.getLegend().setFrame(new BlockBorder(Color.WHITE));
        // 设置标题字体
        chart.setTitle(new TextTitle(chart.getTitle().getText(), titleFont));
        // 图形的绘制结构对象
        CategoryPlot plot = chart.getCategoryPlot();

        plot.setRangeGridlinesVisible(true);

        BarRenderer customBarRenderer = (BarRenderer) plot.getRenderer();
        //设定柱子上面的颜色
        //customBarRenderer.setSeriesPaint(0, Color.decode("#00cc99")); // 给series1 Bar
        //customBarRenderer.setSeriesPaint(1, Color.decode("#0066cc")); // 给series2 Bar
        customBarRenderer.setSeriesPaint(0, new Color(0, 204, 153));
        customBarRenderer.setSeriesPaint(1, new Color(0, 114, 204));
        customBarRenderer.setBaseItemLabelGenerator(new StandardCategoryItemLabelGenerator());
        customBarRenderer.setBaseItemLabelsVisible(true);

        //默认的数字显示在柱子中，通过如下两句可调整数字的显示
        //注意：此句很关键，若无此句，那数字的显示会被覆盖，给人数字没有显示出来的问题
        customBarRenderer.setBasePositiveItemLabelPosition(new ItemLabelPosition(ItemLabelAnchor.OUTSIDE12, TextAnchor.TOP_CENTER));
        customBarRenderer.setItemLabelAnchorOffset(10D);
        // 获取显示线条的对象
/*      LineAndShapeRenderer lasp = (LineAndShapeRenderer) plot.getRenderer();
        // 设置拐点是否可见/是否显示拐点
        lasp.setBaseShapesVisible(true);
        // 设置拐点不同用不同的形状
        lasp.setDrawOutlines(true);
        // 设置线条是否被显示填充颜色
        lasp.setUseFillPaint(false);
        LineAndShapeRenderer renderer = (LineAndShapeRenderer) plot.getRenderer();
        // 设置折线大小以及折线的颜色
        renderer.setSeriesStroke(0, new BasicStroke(1.0F));
        renderer.setSeriesPaint(0, new Color(210, 105, 30));
        renderer.setSeriesStroke(1, new BasicStroke(1.0F));
        renderer.setSeriesPaint(1, new Color(0, 191, 255));
        // 设置折点的大小
        lasp.setSeriesOutlineStroke(0, new BasicStroke(0.025F));
        lasp.setSeriesOutlineStroke(1, new BasicStroke(0.05F));
        // 设置网格线
        plot.setDomainGridlinePaint(Color.gray);
        plot.setDomainGridlinesVisible(true);
        plot.setRangeGridlinePaint(Color.gray);
        plot.setRangeGridlinesVisible(true);*/
        // x轴
        CategoryAxis domainAxis = plot.getDomainAxis();
        // 设置x轴不显示，即让x轴和数据区重合
        domainAxis.setAxisLineVisible(false);
        // x轴标题
        domainAxis.setLabelFont(xfont);
        // x轴数据倾斜
        //domainAxis.setCategoryLabelPositions(CategoryLabelPositions.createUpRotationLabelPositions(0.95D));
        // X轴坐标上数值字体
        domainAxis.setTickLabelFont(labelFont);
        // 设置Y轴间隔
        NumberAxis numAxis = (NumberAxis) plot.getRangeAxis();
        numAxis.setTickUnit(new NumberTickUnit(50));
        numAxis.setVisible(false);

        // y轴
        ValueAxis rangeAxis = plot.getRangeAxis();
        rangeAxis.setLabelFont(xfont);
        // 设置y轴不显示，即和数据区重合
        rangeAxis.setAxisLineVisible(false);
        // y轴坐标上数值字体
        rangeAxis.setTickLabelFont(labelFont);
        rangeAxis.setFixedDimension(0);

        CategoryPlot cp = chart.getCategoryPlot();
        // 背景色设置
        cp.setBackgroundPaint(ChartColor.WHITE);
        cp.setRangeGridlinePaint(ChartColor.WHITE);
        // 创建图例，设置图例的位置，这里的设置实际不起作用，怎么设都在下边
        LegendTitle legendTitle = new LegendTitle(chart.getPlot());
        legendTitle.setPosition(RectangleEdge.BOTTOM);
        return chart;
    }

}

最终的结果展示为：