根据查询的数据生成excel和图表，excel并且支持分页，这次分页的方法和上一篇文章的方法不同，这次的方法是在查询出数据的时候就开始设置了，在将查询结果写到list集合的时候就分多个集合在存储，最后在将结果添加到另一个list中，如List<List<Object[]>>。最后遍历就可以实现分页的效果。代码：
public static void msgExportExcel(String code,SXSSFWorkbook wb, List<Object[]> title,List<List<Object[]>> resultList,List<Object[]> chartShow) {SXSSFSheet sheet =null; File file =null;int size = resultList.size();//sheet数try {for(int i =0;i<size;i++){sheet = (SXSSFSheet) wb.createSheet("Page " + i); Row hrow = sheet.createRow(0); Object[] obj = title.get(i);//写入标题 for(int k=0;k<obj.length;k++){ Cell cell = hrow.createCell(k); cell.setAsActiveCell();cell.setCellValue(obj[k] == null? "" : obj[k].toString()); //设置列宽度sheet.setColumnWidth(k, 3500);} //写入数据List<Object[]> list = resultList.get(i);for(int j=0;j<list.size();j++){Object[] o = list.get(j);Row row = sheet.createRow(j+1);for (int k = 0; k < o.length; k++) {Cell cell = row.createCell(k);cell.setCellValue(o[k] == null ? "" : o[k].toString());}if (i % 100 == 0) {sheet.flushRows(100);}}//创建图表file = ExportChartUtil.createChartLine(list,chartShow.get(i)[0].toString(),chartShow.get(i)[1].toString(),chartShow.get(i)[2].toString(),chartShow.get(i)[3].toString());imageOut(wb,sheet,file);}} catch (IOException e) {e.printStackTrace();logger.error(e.getMessage());}}
下面就是生成柱状图和折线图了，方法如：
折线图：
public static File createChartLine(List<Object[]> list,String title,String xTitle,String yTitle,String num){//public static void main(String args[]){//构造数据集合DefaultCategoryDataset dataset = new DefaultCategoryDataset();for(int i=0;list!=null && i<list.size();i++){Object [] obj = list.get(i);dataset.addValue(Integer.parseInt(obj[0].toString()),"数据",obj[1].toString()+"\n"+obj[2].toString());}//核心对象JFreeChart chart = ChartFactory.createLineChart(title, //图形的主标题xTitle, //X轴外标签的名称yTitle, //Y轴外标签的名称dataset, PlotOrientation.VERTICAL,//图形的显示方式（水平和垂直） true,//是否显示子标题 true,//是否在图形上显示数值的提示 true);//是否生成URL地址//解决主标题的乱码chart.getTitle().setFont(new Font("宋体", Font.BOLD, 18));//解决子标题的乱码chart.getLegend().setItemFont(new Font("宋体", Font.BOLD, 15));chart.getLegend().setPosition(RectangleEdge.RIGHT);//右侧显示子菜单//获取图表区域对象CategoryPlot categoryPlot = (CategoryPlot) chart.getPlot();//categoryPlot.setBackgroundPaint(null);//获取X轴对象CategoryAxis categoryAxis = (CategoryAxis) categoryPlot.getDomainAxis();//获取Y轴对象NumberAxis numberAxis = (NumberAxis) categoryPlot.getRangeAxis();//解决X轴上的乱码categoryAxis.setTickLabelFont(new Font("宋体", Font.BOLD, 15));//解决X轴外的乱码categoryAxis.setLabelFont(new Font("宋体", Font.BOLD, 15));//解决Y轴上的乱码numberAxis.setTickLabelFont(new Font("宋体", Font.BOLD, 15));//解决Y轴外的乱码numberAxis.setLabelFont(new Font("宋体", Font.BOLD, 15));//改变Y轴的刻度，默认值从1计算numberAxis.setAutoTickUnitSelection(false);NumberTickUnit unit = new NumberTickUnit(Integer.parseInt(num));numberAxis.setTickUnit(unit);//获取绘图区域对象LineAndShapeRenderer lineAndShapeRenderer = (LineAndShapeRenderer)categoryPlot.getRenderer();lineAndShapeRenderer.setBaseShapesVisible(true);//设置转折点//让数值显示到页面上lineAndShapeRenderer.setBaseItemLabelGenerator(new StandardCategoryItemLabelGenerator());lineAndShapeRenderer.setBaseItemLabelsVisible(true);lineAndShapeRenderer.setBaseItemLabelFont(new Font("宋体", Font.BOLD, 15));//显示图形//ChartFrame chartFrame = new ChartFrame("xyz", chart);//chartFrame.setVisible(true);//chartFrame.pack();String filename = DateFormatUtils.format(new Date(), "yyyyMMddHHmmss")+".png";//用时间作为文件名防止重名的问题发生File file = new File(filename);//保存文件到web容器中try {ChartUtilities.saveChartAsPNG(file,chart,600,500);} catch (IOException e) {e.printStackTrace();logger.error(e.getMessage());}return file;}
柱状图：
public static File createChartBar(List<Object[]> list,String title,String xTitle,String yTitle,String num){DefaultCategoryDataset dataset = new DefaultCategoryDataset(); for(int i=0;list!=null && i<list.size();i++){Object [] obj = list.get(i);dataset.addValue(Integer.parseInt(obj[0].toString()),"数据",obj[1].toString()+"\n"+obj[2].toString());}JFreeChart chart = ChartFactory.createBarChart(title, //图表的主标题 xTitle,//X轴（种类轴）外的标题yTitle,//Y轴（值轴）外的标题 dataset, //数据的集合PlotOrientation.VERTICAL, //图形的显示形式（水平/垂直）true, //是否生成子标题 true, //是否生成提示的工具true); //是否在图像上生成URL路径//处理乱码//处理主标题乱码chart.getTitle().setFont(new Font("宋体",Font.BOLD,18));//处理子标题乱码chart.getLegend().setItemFont(new Font("宋体",Font.BOLD,15));chart.getLegend().setPosition(RectangleEdge.RIGHT);//右侧显示子菜单//调出图表区域对象CategoryPlot categoryPlot = (CategoryPlot) chart.getPlot();//获取到X轴CategoryAxis categoryAxis = (CategoryAxis) categoryPlot.getDomainAxis();//获取到Y轴NumberAxis numberAxis = (NumberAxis) categoryPlot.getRangeAxis();//处理X轴外的乱码categoryAxis.setLabelFont(new Font("宋体",Font.BOLD,15));//处理X轴上的乱码categoryAxis.setTickLabelFont(new Font("宋体",Font.BOLD,15));//处理Y轴外的乱码numberAxis.setLabelFont(new Font("宋体",Font.BOLD,15));//处理Y轴上的乱码numberAxis.setTickLabelFont(new Font("宋体",Font.BOLD,15));//处理Y轴上的刻度，默认从1开始numberAxis.setAutoTickUnitSelection(false);NumberTickUnit unit = new NumberTickUnit(Integer.parseInt(num));numberAxis.setTickUnit(unit);//处理图形，先要获取绘图区域对象BarRenderer barRenderer = (BarRenderer) categoryPlot.getRenderer();//设置图形的宽度//barRenderer.setMaximumBarWidth(0.1);//在图形上显示对应数值barRenderer.setBaseItemLabelGenerator(new StandardCategoryItemLabelGenerator());barRenderer.setBaseItemLabelsVisible(true);barRenderer.setBaseItemLabelFont(new Font("宋体",Font.BOLD,15));String filename = DateFormatUtils.format(new Date(), "yyyyMMddHHmmss")+".png";File file = new File(filename);try {ChartUtilities.saveChartAsPNG(file,chart,600,500);} catch (IOException e) {e.printStackTrace();logger.error(e.getMessage());}return file;}
关键的问题就是如何将生成的图表写入到excel中呢？这个时候就需要IO流来完成了，代码如下：
private static void imageOut(SXSSFWorkbook wb,SXSSFSheet sheet,File file) {ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();BufferedImage bufferImg;try {bufferImg = ImageIO.read(file);ImageIO.write(bufferImg, "png", byteArrayOut);Drawing dp = sheet.createDrawingPatriarch();XSSFClientAnchor anchor = new XSSFClientAnchor(0,0,512,255,7,4,10,20);//设置图表在excel中位置anchor.setAnchorType(2);dp.createPicture(anchor,wb.addPicture(byteArrayOut.toByteArray(),wb.PICTURE_TYPE_PNG)).resize(0.8);} catch (IOException e) {e.printStackTrace();logger.error(e.getMessage());}}
在生成excel后最好将在容器中生成的图表删除：
if(file.exists()){file.delete();}
扫描下方二维码关注公众号，及时获取文章推送
如果觉得《POI导出excel+JFreeChart生成图表（柱状图和折线图）》对你有帮助，请点赞、收藏，并留下你的观点哦！
