设为首页收藏本站
开启辅助访问

【POI】Java+poi+ooxml生成Excel（.xlsx）图表（折线图、柱状图、组合图）

 您所在的位置：网站首页 › excel如何制作柱状图并引入位置数据 › 【POI】Java+poi+ooxml生成Excel（.xlsx）图表（折线图、柱状图、组合图）

【POI】Java+poi+ooxml生成Excel（.xlsx）图表（折线图、柱状图、组合图）

2024-07-12 04:20| 来源: 网络整理| 查看: 265

这次开发使用到的是折线图、柱状图、折线+柱状组合图 颇费周折，记个笔记好了

仅通过POI的话，只能实现折线图和散点图 （https://blog.csdn.net/u011279583/article/details/105248887） 但POI还支持Open Xml，通过这种方式可以实现很多类型的chart

感觉ooxml用起来比较复杂，也很少有文档可以参照，再加上excel的图表功能本身就非常庞大，包含了很多细小的设置，所以想要提高效率，首先还是要会一些excel图表的操作，这样在用ooxml开发的时候才能在excel中找到对应，不会一头雾水

效果参考： 折线图 折线图柱状图、组合图 柱状图+组合图 POI和Open Xml版本

org.apache.poi poi 3.17 org.apache.poi poi-ooxml 3.17 org.apache.poi poi-scratchpad 3.17 org.apache.poi poi-ooxml-schemas 3.17 org.apache.poi ooxml-schemas 1.3

代码

import java.awt.Color; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.util.Units; import org.apache.poi.xssf.usermodel.XSSFChart; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFDrawing; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource; import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarChart; import org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer; import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean; import org.openxmlformats.schemas.drawingml.x2006.chart.CTCatAx; import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart; import org.openxmlformats.schemas.drawingml.x2006.chart.CTDLbls; import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend; import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart; import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer; import org.openxmlformats.schemas.drawingml.x2006.chart.CTMarker; import org.openxmlformats.schemas.drawingml.x2006.chart.CTMarkerStyle; import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource; import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef; import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea; import org.openxmlformats.schemas.drawingml.x2006.chart.CTScaling; import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx; import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef; import org.openxmlformats.schemas.drawingml.x2006.chart.CTValAx; import org.openxmlformats.schemas.drawingml.x2006.chart.STAxPos; import org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir; import org.openxmlformats.schemas.drawingml.x2006.chart.STBarGrouping; import org.openxmlformats.schemas.drawingml.x2006.chart.STCrosses; import org.openxmlformats.schemas.drawingml.x2006.chart.STDispBlanksAs; import org.openxmlformats.schemas.drawingml.x2006.chart.STGrouping; import org.openxmlformats.schemas.drawingml.x2006.chart.STLblAlgn; import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos; import org.openxmlformats.schemas.drawingml.x2006.chart.STMarkerStyle; import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation; import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos; import org.openxmlformats.schemas.drawingml.x2006.chart.STTickMark; public class XSSFUtils { public static void testForLineChart(XSSFSheet sheet) { Map arams = new HashMap(); // 图表位置（B36左上角：AA53左上角），偏移量均为0 int[] chartPositon = new int[] {getColumnIndexByAddress("B"), 35, getColumnIndexByAddress("AA"), 52, 0, 0, 0, 0}; // 设置x轴坐标区域（B60：B90），即“Day”列 int[] xAxisRange = new int[] {59, 89, XSSFUtils.getColumnIndexByAddress("B"), XSSFUtils.getColumnIndexByAddress("B")}; // 设置数据区域，即每个系列的数据（D60：D90、J60：J90、P60：P90） // 图例标题（D59、J59、P59） List seriesRangeList = new ArrayList(); seriesRangeList.add(new int[] { 58, 58, XSSFUtils.getColumnIndexByAddress("D"), XSSFUtils.getColumnIndexByAddress("D"), 59, 89, XSSFUtils.getColumnIndexByAddress("D"), XSSFUtils.getColumnIndexByAddress("D")}); seriesRangeList.add(new int[] { 58, 58, XSSFUtils.getColumnIndexByAddress("J"), XSSFUtils.getColumnIndexByAddress("J"), 59, 89, XSSFUtils.getColumnIndexByAddress("J"), XSSFUtils.getColumnIndexByAddress("J")}); seriesRangeList.add(new int[] { 58, 58, XSSFUtils.getColumnIndexByAddress("P"), XSSFUtils.getColumnIndexByAddress("P"), 59, 89, XSSFUtils.getColumnIndexByAddress("P"), XSSFUtils.getColumnIndexByAddress("P")}); arams.put("chartPosition", chartPositon); arams.put("chartTitle", ""); arams.put("dispBlanksAs", "zero"); arams.put("legendPosition", "t"); arams.put("xAxisDataCellRange", xAxisRange); arams.put("seriesDataCellRangeList", seriesRangeList); arams.put("lineColor", null); arams.put("isXAxisDelete", false); arams.put("yAxisPosition", "l"); createLineChart(sheet, arams); } public static void testForBarChart(XSSFSheet sheet) { Map params = new HashMap(); // 图表位置（G20自左上角向右偏移50个EMU point：N39自左上角向右偏移50个EMU point） int[] chartPositon = new int[] {XSSFUtils.getColumnIndexByAddress("G"), 19, XSSFUtils.getColumnIndexByAddress("N"), 38, 50, 0, 50, 0}; // 图表标题(A9) String chartTitle = sheet.getRow(8).getCell(XSSFUtils.getColumnIndexByAddress("A")).getStringCellValue(); // 设置x轴坐标区域（C8：N8），即“表头月份”行 int[] xAxisRange = new int[] {7, 7, XSSFUtils.getColumnIndexByAddress("C"), XSSFUtils.getColumnIndexByAddress("N")}; // 设置数据区域，即每个系列的数据（C11:N11） // 图例标题（B11） List seriesRangeList = new ArrayList(); seriesRangeList.add( new int[] { 10, 10, XSSFUtils.getColumnIndexByAddress("B"), XSSFUtils.getColumnIndexByAddress("B"), 10, 10, XSSFUtils.getColumnIndexByAddress("C"), XSSFUtils.getColumnIndexByAddress("N")}); params.put("chartPosition", chartPositon); params.put("chartTitle", chartTitle); params.put("dispBlanksAs", "gap"); params.put("legendPosition", "r"); params.put("xAxisDataCellRange", xAxisRange); params.put("seriesDataCellRangeList", seriesRangeList); params.put("barColor", new XSSFColor(new Color(247, 150, 70))); createBarChart(sheet, params); } public static void testForComboChart(XSSFSheet sheet) { Map params = new HashMap(); // 图表位置（A20自左上角向右偏移10个EMU point：G39自左上角向右偏移30个EMU point） int[] chartPosition = new int[] {XSSFUtils.getColumnIndexByAddress("A"), 19, XSSFUtils.getColumnIndexByAddress("G"), 38, 10, 0, 30, 0}; // 图表标题(A9) String chartTitle = sheet.getRow(8).getCell(XSSFUtils.getColumnIndexByAddress("A")).getStringCellValue(); // 设置x轴坐标区域（C8：N8），即“表头月份”行 int[] xAxisRange = new int[] {7, 7, XSSFUtils.getColumnIndexByAddress("C"), XSSFUtils.getColumnIndexByAddress("N")}; // 柱状图设置数据区域，即每个系列的数据（C9:N9） // 图例标题（B9） List barSeriesRangeList = new ArrayList(); barSeriesRangeList.add( new int[] { 8, 8, XSSFUtils.getColumnIndexByAddress("B"), XSSFUtils.getColumnIndexByAddress("B"), 8, 8, XSSFUtils.getColumnIndexByAddress("C"), XSSFUtils.getColumnIndexByAddress("N")}); // 折线图设置数据区域，即每个系列的数据（C10:N10） // 图例标题（B10） List lineSeriesRangeList = new ArrayList(); lineSeriesRangeList.add( new int[] { 9, 9, XSSFUtils.getColumnIndexByAddress("B"), XSSFUtils.getColumnIndexByAddress("B"), 9, 9, XSSFUtils.getColumnIndexByAddress("C"), XSSFUtils.getColumnIndexByAddress("N")}); params.put("chartPosition", chartPosition); params.put("chartTitle", chartTitle); params.put("dispBlanksAs", "gap"); params.put("legendPosition", "r"); params.put("barXAxisDataCellRange", xAxisRange); params.put("barSeriesDataCellRangeList", barSeriesRangeList); params.put("barColor", new XSSFColor(new Color(79, 129, 189))); params.put("lineXAxisDataCellRange", xAxisRange); params.put("lineSeriesDataCellRangeList", lineSeriesRangeList); params.put("lineColor", new XSSFColor(new Color(190, 75, 72))); params.put("lineIsXAxisDelete", true); params.put("lineYAxisPosition", "r"); XSSFUtils.createComboChart(sheet, params); } /** * create line chart * @param sheet * @param params * chartPosition int[]{startCol, startRow, endCol, endRow, * xOffsetInStartCell, yOffsetInStartCell, * xOffsetInEndCell, yOffsetInEndCell} * chartTitle String * xAxisDataCellRange int[]{startRow, endRow, startCol, endCol} * seriesDataCellRangeList List * lineColor XSSFColor */ @SuppressWarnings("unchecked") public static void createLineChart(XSSFSheet sheet, Map params) { // 创建绘图区 CTPlotArea ctPlotArea = createCTPlotArea(sheet, params); // 绘制图表 createCTLineChart(sheet, ctPlotArea, params); } /** * create bar chart * @param sheet * @param params * chartPosition int[]{startCol, startRow, endCol, endRow, * xOffsetInStartCell, yOffsetInStartCell, * xOffsetInEndCell, yOffsetInEndCell} * chartTitle String * xAxisDataCellRange int[]{startRow, endRow, startCol, endCol} * seriesDataCellRangeList List * barColor XSSFColor */ @SuppressWarnings("unchecked") public static void createBarChart(XSSFSheet sheet, Map params) { // 创建绘图区 CTPlotArea ctPlotArea = createCTPlotArea(sheet, params); // 绘制图表 createCTBarChart(sheet, ctPlotArea, params); } /** * create combination chart * @param sheet * @param params * chartPosition int[]{startCol, startRow, endCol, endRow, * xOffsetInStartCell, yOffsetInStartCell, * xOffsetInEndCell, yOffsetInEndCell} * chartTitle String * barXAxisDataCellRange int[]{startRow, endRow, startCol, endCol} * barSeriesDataCellRangeList List * barColor XSSFColor * lineXAxisDataCellRange int[]{startRow, endRow, startCol, endCol} * lineSeriesDataCellRangeList List * lineColor XSSFColor * lineIsXAxisDelete Boolean * lineYAxisPosition String */ @SuppressWarnings("unchecked") public static void createComboChart(XSSFSheet sheet, Map params) { Map barParams = new HashMap(); Map lineParams = new HashMap(); barParams.put("xAxisDataCellRange", params.get("barXAxisDataCellRange")); barParams.put("seriesDataCellRangeList", params.get("barSeriesDataCellRangeList")); barParams.put("barColor", params.get("barColor")); lineParams.put("xAxisDataCellRange", params.get("lineXAxisDataCellRange")); lineParams.put("seriesDataCellRangeList", params.get("lineSeriesDataCellRangeList")); lineParams.put("lineColor", params.get("lineColor")); lineParams.put("isXAxisDelete", params.get("lineIsXAxisDelete")); lineParams.put("yAxisPosition", params.get("lineYAxisPosition")); // 创建绘图区 CTPlotArea ctPlotArea = createCTPlotArea(sheet, params); // 绘制图表 createCTBarChart(sheet, ctPlotArea, barParams); createCTLineChart(sheet, ctPlotArea, lineParams); } /** * create line chart * @param params * xAxisDataCellRange int[]{startRow, endRow, startCol, endCol} * seriesDataCellRangeList List * lineColor XSSFColor * isXAxisDelete Boolean * yAxisPosition String */ private static void createCTLineChart(XSSFSheet sheet, CTPlotArea ctPlotArea, Map params) { int[] xAxisDataCellRange = (int[]) params.get("xAxisDataCellRange"); List seriesDataCellRangeList = (List) params.get("seriesDataCellRangeList"); XSSFColor lineColor = (XSSFColor) params.get("lineColor"); Boolean isXAxisDelete = (Boolean) params.get("isXAxisDelete"); String yAxisPosition = (String) params.get("yAxisPosition"); CTLineChart ctLineChart = ctPlotArea.addNewLineChart(); ctLineChart.addNewGrouping().setVal(STGrouping.STANDARD); ctLineChart.addNewVaryColors().setVal(true); // 绘制每条折线 for (int i = 0; i


【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3