使用Apache poi生成excel并绘制折线统计图，扇形统计图
使用Apache poi生成excel并绘制折线统计图，扇形统计图
1.Gradle dependencies
// https://mvnrepository.com/artifact/org.apache.poi/poicompile group: 'org.apache.poi', name: 'poi', version: '4.0.1'// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxmlcompile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.0.1'// https://mvnrepository.com/artifact/org.projectlombok/lombokcompile group: 'org.projectlombok', name: 'lombok', version: '1.18.4'
2.import
import java.awt.Color;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xddf.usermodel.chart.AxisCrosses;import org.apache.poi.xddf.usermodel.chart.AxisPosition;import org.apache.poi.xddf.usermodel.chart.ChartTypes;import org.apache.poi.xddf.usermodel.chart.LegendPosition;import org.apache.poi.xddf.usermodel.chart.MarkerStyle;import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;import org.apache.poi.xddf.usermodel.chart.XDDFLineChartData;import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;import org.apache.poi.xssf.usermodel.XSSFCellStyle;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.XSSFFont;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.openxmlformats.schemas.drawingml.x2006.chart.CTAxDataSource;import org.openxmlformats.schemas.drawingml.x2006.chart.CTBoolean;import org.openxmlformats.schemas.drawingml.x2006.chart.CTChart;import org.openxmlformats.schemas.drawingml.x2006.chart.CTLegend;import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumDataSource;import org.openxmlformats.schemas.drawingml.x2006.chart.CTNumRef;import org.openxmlformats.schemas.drawingml.x2006.chart.CTPieChart;import org.openxmlformats.schemas.drawingml.x2006.chart.CTPieSer;import org.openxmlformats.schemas.drawingml.x2006.chart.CTPlotArea;import org.openxmlformats.schemas.drawingml.x2006.chart.CTSerTx;import org.openxmlformats.schemas.drawingml.x2006.chart.CTStrRef;import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;import org.openxmlformats.schemas.drawingml.x2006.main.CTShapeProperties;import org.springframework.stereotype.Service;import lombok.extern.slf4j.Slf4j;
3.class
public class MyExcleChart2{private static Color titleBackColor = new Color(155,194,230); //表头背景色public static void doWork(List<String> title,List<String> styleList, Map<String, List<Object>> day2ColValueList, File file,String sheetName,XSSFWorkbook wb,int dateNum) throws IOException {OutputStream out = null;try{int sheetIndex = wb.getSheetIndex(sheetName);if(sheetIndex >= 0){wb.removeSheetAt(sheetIndex);}int sheetNum = wb.getNumberOfSheets();XSSFSheet sheet = wb.createSheet();wb.setSheetName(sheetNum, sheetName);out = new FileOutputStream(file); //设置内容样式XSSFCellStyle style = setBorder(wb);//设置表头字体XSSFFont font = wb.createFont();font.setBold(true); //加粗//设置表头样式XSSFCellStyle headStyle = setBorder(wb);headStyle.setFillForegroundColor(new XSSFColor(titleBackColor));headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);headStyle.setFont(font);//隐藏列hiddenColumn(sheet,sheetName);Row row;Cell cell = null;row = sheet.createRow(0);//写入表头int titleColIndex=0;for(String t:title) {cell = row.createCell((short) titleColIndex);cell.setCellValue(t);cell.setCellStyle(headStyle);titleColIndex++;} //写入数据int rowIndex = 1;for(String key:day2ColValueList.keySet()){row = sheet.createRow(rowIndex);List<Object> dataList = day2ColValueList.get(key);cell = row.createCell(0);cell.setCellStyle(style);cell.setCellValue(rowIndex);int cellIndex = 1;for(Object s : dataList){//填充单元格String cellstyle = styleList.get(dataList.indexOf(s)+1);cell = row.createCell(cellIndex);cell.setCellStyle(style);//此处可以对特殊的行进行处理if("speciaRowName".equals(key)&&cellIndex>9){cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue("speciaRowName");cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("");cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("");cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue("");cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue("");cell = row.createCell(cellIndex); cell.setCellStyle(style); double dble = (double)s; cell.setCellValue(dble);}else if("int".equals(cellstyle)&&null!=s){int num = (int) s;cell.setCellValue(num);}else if("double".equals(cellstyle)&&null!=s){double dble = (double)s;cell.setCellValue(dble);}else{cell.setCellValue(null==s?"":(String)s);}cellIndex ++;}rowIndex ++;}//绘制图表if(day2ColValueList.size()>0){drawChart(sheet, sheetName, day2ColValueList, titleColIndex,dateNum);}wb.write(out);} catch (Exception e) {e.printStackTrace();} finally { try {out.close();} catch (IOException e) {e.printStackTrace();}} }private static void drawChart(XSSFSheet sheet, String sheetName, Map<String, List<Object>> day2ColValueList,int titleSize,int dateNum) {Map<String, Integer> paramMap = new HashMap<String, Integer>();// 折线图入参Map<String, Object> pieParamMap = new HashMap<String, Object>();// 扇形图入参//此处可以分sheet处理数据if ("sheetname1".equals(sheetName)) {//sheetname1，需要显示折线图和扇形图paramMap.put("numstartcol", 5);paramMap.put("numendcol", 5+dateNum-1);paramMap.put("prostartcol", 5+dateNum);paramMap.put("proendcol", 5+dateNum+dateNum-1);// 业务汇总表，需要统计身份核查、高清人像、银行卡、手机实名四个业务的折线图for (String key : day2ColValueList.keySet()) {if ("类型1业务1".equals(key)) {// 人像比对认证走势折线图// 折线图x轴单位起止列（numstartcol，numendcol），以及计费笔数数据所在行列paramMap.put("col1", 0);paramMap.put("col2", 7);paramMap.put("row1", day2ColValueList.size()+2);paramMap.put("row2", day2ColValueList.size()+19);paramMap.put("numstartrow", 1);paramMap.put("numendrow", 1);paramMap.put("numstartrow2", 2);paramMap.put("numendrow2", 2);// 折线图净收入所在行列paramMap.put("prostartrow", 1);paramMap.put("proendrow", 1);paramMap.put("prostartrow2", 2);paramMap.put("proendrow2", 2);drawLineChart(sheet, "业务1", paramMap);} else if ("类型1业务2".equals(key)) {// 银行卡认证走势折线图paramMap.put("col1", 8);paramMap.put("col2", 15);paramMap.put("row1", day2ColValueList.size()+2);paramMap.put("row2", day2ColValueList.size()+19);paramMap.put("numstartrow", 3);paramMap.put("numendrow", 3);paramMap.put("numstartrow2", 4);paramMap.put("numendrow2", 4);paramMap.put("prostartrow", 3);paramMap.put("proendrow", 3);paramMap.put("prostartrow2", 4);paramMap.put("proendrow2", 4);drawLineChart(sheet, "业务2", paramMap);} }//柱状图paramMap.put("col1", 15);paramMap.put("col2", 23);paramMap.put("row1", day2ColValueList.size()+2);paramMap.put("row2", day2ColValueList.size()+19);paramMap.put("numstartrow", 1);paramMap.put("numendrow", 1);paramMap.put("prostartrow", 1);paramMap.put("proendrow", 1);
// drawBarChart(sheet, "业务1", paramMap);// 扇形图pieParamMap.put("col1", 0);pieParamMap.put("col2", 7);pieParamMap.put("row1", day2ColValueList.size()+20);pieParamMap.put("row2", day2ColValueList.size()+39);pieParamMap.put("data1", "sheetname1!$C$1");pieParamMap.put("data2", "sheetname1!$C$2:$C$3");pieParamMap.put("data3", "sheetname1!$D$2:$D$3");drawPieChart(sheet, "类型1各业务交易量占比", pieParamMap);pieParamMap.put("col1", 8);pieParamMap.put("col2", 15);pieParamMap.put("data3", "sheetname1!$E$2:$E$3");drawPieChart(sheet, "类型1各业务净收入占比", pieParamMap);pieParamMap.put("col1", 0);pieParamMap.put("col2", 7);pieParamMap.put("row1", day2ColValueList.size()+40);pieParamMap.put("row2", day2ColValueList.size()+59);pieParamMap.put("data2", "sheetname1!$C$4:$C$5");pieParamMap.put("data3", "sheetname1!$D$4:$D$5");drawPieChart(sheet, "类型2各业务交易量占比", pieParamMap);pieParamMap.put("col1", 8);pieParamMap.put("col2", 15);pieParamMap.put("data3", "sheetname1!$e$4:$e$5");drawPieChart(sheet, "类型2各业务净收入占比", pieParamMap);} }/*** 绘制扇形图* @param sheet sheet* @param string 标题* @param paramMap 各种起始截止行列* col1 col2 row1 row2 图片坐标* data1 种类划分标志所在列* data2 各分类名* data3 各分类数值*/private static void drawPieChart(XSSFSheet sheet, String title, Map<String, Object> pieParamMap) {int col1 = (int) pieParamMap.get("col1");int col2 = (int) pieParamMap.get("col2");int row1 = (int) pieParamMap.get("row1");int row2 = (int) pieParamMap.get("row2");String data1 = (String) pieParamMap.get("data1");String data2 = (String) pieParamMap.get("data2");String data3 = (String) pieParamMap.get("data3");XSSFDrawing drawing = sheet.createDrawingPatriarch();XSSFClientAnchor anchor = (XSSFClientAnchor) drawing.createAnchor(0, 0, 0, 0, col1, row1, col2, row2);XSSFChart chart = drawing.createChart(anchor);chart.setTitleText(title);chart.setTitleOverlay(false);CTChart ctChart = ((XSSFChart) chart).getCTChart();CTPlotArea ctPlotArea = ctChart.getPlotArea();CTPieChart ctPieChart = ctPlotArea.addNewPieChart();CTBoolean ctBoolean = ctPieChart.addNewVaryColors();ctBoolean.setVal(true);CTPieSer ctPieSer = ctPieChart.addNewSer();CTSerTx ctSerTx = ctPieSer.addNewTx();CTStrRef ctStrRefTx = ctSerTx.addNewStrRef();ctStrRefTx.setF(data1);ctPieSer.addNewIdx().setVal(0);CTAxDataSource cttAxDataSource = ctPieSer.addNewCat();CTStrRef ctStrRef = cttAxDataSource.addNewStrRef();ctStrRef.setF(data2); // 第一行为标题CTNumDataSource ctNumDataSource = ctPieSer.addNewVal();CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();ctNumRef.setF(data3); // 第一行为标题ctPieSer.addNewDLbls().addNewShowLeaderLines();// 有无此行代码,图上是否显示文字// legend图注CTLegend ctLegend = ctChart.addNewLegend();ctLegend.addNewLegendPos().setVal(STLegendPos.TR);ctLegend.addNewOverlay().setVal(true);ctPieSer.addNewExplosion().setVal(1);// 各块之间间隙大小ctPieSer.addNewOrder().setVal(0);//CTShapeProperties cTShapeProperties = CTShapeProperties.Factory.newInstance();ctPieSer.addNewSpPr().set(cTShapeProperties);}/*** 绘制折线图* @param sheet sheet* @param desc 横轴描述* @param 各种起始截止行列，包含如下内容：* int numstartrow,int numendrow,int numstartcol,int numendcol,//需要绘图的计费笔数起始截止行列int prostartrow,int proendrow,int prostartcol,int proendcol,//需要绘图的业务净收入起始截止行列int col1,int row1,int row2 //绘图的起始行列*/private static void drawLineChart(XSSFSheet sheet,String desc,Map<String,Integer> paramMap) {int col1 = paramMap.get("col1"),col2 = paramMap.get("col2"), row1=paramMap.get("row1"),row2 = paramMap.get("row2"),//绘图所在坐标，默认宽度为12列//双折线图x轴单位起止列（numstartcol，numendcol），以及第一类数据所在行列 numstartrow = paramMap.get("numstartrow"),numendrow = paramMap.get("numendrow"),numstartcol = paramMap.get("numstartcol"),numendcol = paramMap.get("numendcol"),//双折线图数据2所在行列prostartrow = paramMap.get("prostartrow"),proendrow = paramMap.get("proendrow"),prostartcol = paramMap.get("prostartcol"),proendcol = paramMap.get("proendcol");int dx1=0;int dy1=0;int dx2=0;int dy2=0;XSSFDrawing drawing = sheet.createDrawingPatriarch();XSSFClientAnchor anchor = drawing.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);XSSFChart chart = drawing.createChart(anchor);XDDFChartLegend legend = chart.getOrAddLegend();legend.setPosition(LegendPosition.TOP_RIGHT);// Use a category axis for the bottom axis.XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.TOP);//底部X轴bottomAxis.setTitle(desc+"交易情况汇总"); // https://stackoverflow.com/questions/32010765XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);//左侧Y轴leftAxis.setTitle("交易量/金额");leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);XDDFLineChartData leftdata = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);/填充数据CellRangeAddress cellRangeAddress=new CellRangeAddress(0, 0, numstartcol, numendcol);XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet, cellRangeAddress);//日期CellRangeAddress dataCellRangeAddress=new CellRangeAddress(numstartrow, numendrow, numstartcol, numendcol);XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress);//纵轴为各个数据XDDFLineChartData.Series series1 = (XDDFLineChartData.Series) leftdata.addSeries(xs, ys1);series1.setTitle("交易量总计（笔）", null); // https://stackoverflow.com/questions/21855842series1.setSmooth(false); // https://stackoverflow.com/questions/29014848series1.setMarkerStyle(MarkerStyle.DASH); // https://stackoverflow.com/questions/39636138CellRangeAddress dataCellRangeAddress2=new CellRangeAddress(prostartrow, proendrow,prostartcol,proendcol);XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress2);//纵轴为各个数据XDDFLineChartData.Series series2 = (XDDFLineChartData.Series) leftdata.addSeries(xs, ys2);series2.setTitle("收入总计（元）", null); // https://stackoverflow.com/questions/21855842series2.setSmooth(false); // https://stackoverflow.com/questions/29014848series2.setMarkerStyle(MarkerStyle.DASH); // https://stackoverflow.com/questions/39636138if(null!=paramMap.get("numstartrow2")&&null!=paramMap.get("numendrow2")&&null!=paramMap.get("prostartrow2")&&null!=paramMap.get("proendrow2")){int numstartrow2 = paramMap.get("numstartrow2"), numendrow2 = paramMap.get("numendrow2"),prostartrow2 = paramMap.get("prostartrow2"), proendrow2 = paramMap.get("proendrow2");series1.setTitle("类型1交易量总计（笔）", null); // https://stackoverflow.com/questions/21855842series2.setTitle("类型1收入总计（元）", null); // https://stackoverflow.com/questions/21855842CellRangeAddress dataCellRangeAddress3=new CellRangeAddress(numstartrow2, numendrow2, numstartcol, numendcol);XDDFNumericalDataSource<Double> ys3 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress3);//纵轴为各个数据XDDFLineChartData.Series series3 = (XDDFLineChartData.Series) leftdata.addSeries(xs, ys3);series3.setTitle("类型2交易量总计（笔）", null); // https://stackoverflow.com/questions/21855842series3.setSmooth(false); // https://stackoverflow.com/questions/29014848series3.setMarkerStyle(MarkerStyle.DASH); // https://stackoverflow.com/questions/39636138CellRangeAddress dataCellRangeAddress4=new CellRangeAddress(prostartrow2, proendrow2,prostartcol,proendcol);XDDFNumericalDataSource<Double> ys4 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress4);//纵轴为各个数据XDDFLineChartData.Series series4 = (XDDFLineChartData.Series) leftdata.addSeries(xs, ys4);series4.setTitle("类型2收入总计（元）", null); // https://stackoverflow.com/questions/21855842series4.setSmooth(false); // https://stackoverflow.com/questions/29014848series4.setMarkerStyle(MarkerStyle.DASH); // https://stackoverflow.com/questions/39636138chart.plot(leftdata);chart.plot(leftdata); }chart.plot(leftdata);chart.plot(leftdata);}/*** 柱状图* @param sheet* @param desc* @param paramMap*/private static void drawBarChart(XSSFSheet sheet,String desc,Map<String,Integer> paramMap) {int col1 = paramMap.get("col1"),col2 = paramMap.get("col2"), row1=paramMap.get("row1"),row2 = paramMap.get("row2"),//绘图所在坐标，默认宽度为12列numstartrow = paramMap.get("numstartrow"),numendrow = paramMap.get("numendrow"),numstartcol = paramMap.get("numstartcol"),numendcol = paramMap.get("numendcol"),prostartrow = paramMap.get("prostartrow"),proendrow = paramMap.get("proendrow"),prostartcol = paramMap.get("prostartcol"),proendcol = paramMap.get("proendcol");int dx1=0;int dy1=0;int dx2=0;int dy2=0;XSSFDrawing drawing = sheet.createDrawingPatriarch();XSSFClientAnchor anchor = drawing.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);XSSFChart chart = drawing.createChart(anchor);XDDFChartLegend legend = chart.getOrAddLegend();legend.setPosition(LegendPosition.TOP_RIGHT);// Use a category axis for the bottom axis.XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.TOP);//底部X轴bottomAxis.setTitle(desc+"交易情况汇总"); // https://stackoverflow.com/questions/32010765XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);//左侧Y轴leftAxis.setTitle("交易量/金额");leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);XDDFBarChartData data = (XDDFBarChartData) chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);CellRangeAddress cellRangeAddress=new CellRangeAddress(numstartrow, numendrow, numstartcol, numendcol);XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet, cellRangeAddress);//横轴为第一列日期CellRangeAddress dataCellRangeAddress=new CellRangeAddress(numstartrow, numendrow, numstartcol, numendcol);XDDFNumericalDataSource<Double> ys1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress);//纵轴为各个数据XDDFBarChartData.Series series1 = (XDDFBarChartData.Series) data.addSeries(xs, ys1);series1.setTitle("交易量总计（笔）", null); // https://stackoverflow.com/questions/21855842CellRangeAddress dataCellRangeAddress2=new CellRangeAddress(prostartrow, proendrow,prostartcol,proendcol);XDDFNumericalDataSource<Double> ys2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress2);//纵轴为各个数据XDDFBarChartData.Series series2 = (XDDFBarChartData.Series) data.addSeries(xs, ys2);series2.setTitle("收入总计（元）", null); // https://stackoverflow.com/questions/21855842
/* for(int col=1;col<2;col++) {//数据列数：第一列为日期，其他列为对应数据。CellRangeAddress dataCellRangeAddress=new CellRangeAddress(prostartrow, proendrow, prostartcol, proendcol);XDDFNumericalDataSource<Double> ys = XDDFDataSourcesFactory.fromNumericCellRange(sheet,dataCellRangeAddress);//纵轴为各个数据XDDFChartData.Series series1 = data.addSeries(xs, ys);series1.setTitle("111", null);}*/chart.plot(data);// in order to transform a bar chart into a column chart, you just need to change the bar directionXDDFBarChartData bar = (XDDFBarChartData) data;bar.setBarDirection(BarDirection.COL);}/*** 隐藏列* @param sheet* @param sheetName*/private static void hiddenColumn(XSSFSheet sheet, String sheetName) {/*if(sheetName.equals("sheetname2")){//需要隐藏列sheet.setColumnHidden(3, true);sheet.setColumnHidden(4, true);sheet.setColumnHidden(5, true);sheet.setColumnHidden(6, true);sheet.setColumnHidden(7, true);sheet.setColumnHidden(8, true);}*/}/*** 设置边框*/private static XSSFCellStyle setBorder(XSSFWorkbook wb){XSSFCellStyle style = wb.createCellStyle();style.setBorderBottom(BorderStyle.THIN); //边框style.setBorderTop(BorderStyle.THIN); //边框style.setBorderLeft(BorderStyle.THIN); //边框style.setBorderRight(BorderStyle.THIN); //边框return style;}}
4.测试类
import java.io.File;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class MyBarChartTest {public static void main(String args[]) {String sheetName = "sheetname1";try (XSSFWorkbook wb = new XSSFWorkbook()){String filePathDaily = "C:/Users/Administrator/Desktop/jfree/";String fileName = "exceltest.xlsx";File filePath = new File(filePathDaily);if(!filePath.exists()&&!filePath.isDirectory()){filePath.mkdir();}//1.统计昨日交易量File file = new File(filePathDaily+fileName);List<String> title=Arrays.asList("序号","类型","业务","合计：交易量","合计：收入","2019-01-01","2019-01-02","2019-01-03","2019-01-04","2019-01-05","2019-01-01收入","2019-01-02收入","2019-01-03收入","2019-01-04收入","2019-01-05收入");List<String> titleStyle=Arrays.asList("int","String","String","int","double","int","int","int","int","int","double","double","double","double","double");List<String> dates = Arrays.asList("2019-01-01","2019-01-02","2019-01-03","2019-01-04","2019-01-05");Map<String, List<Object>> day2ColValueList=new LinkedHashMap<String, List<Object>>();day2ColValueList.put("类型1业务1",Arrays.asList("类型1","业务1",500,1000.00,100,50,100,150,100,200.00,150.00,200.00,250.00,200.00));day2ColValueList.put("类型1业务2",Arrays.asList("类型1","业务2",400,800.00,80,60,80,100,80,160.00,130.00,160.00,190.00,160.00));day2ColValueList.put("类型2业务1",Arrays.asList("类型2","业务1",600,1200.00,120,70,120,170,120,240.00,170.00,240.00,290.00,240.00));day2ColValueList.put("类型2业务2",Arrays.asList("类型2","业务2",200,400.00,40,140,40,80,40,80.00,100.00,80.00,60.00,80.00));MyExcleChart2.doWork(title, titleStyle, day2ColValueList,file,sheetName,wb,dates.size());} catch (Exception e) {e.printStackTrace();}}}5.结果展示
6.生成柱状折线图
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-scratchpad</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>fr.opensagres.xdocreport</groupId><artifactId>xdocreport</artifactId><version>1.0.6</version></dependency><dependency> <groupId> fr.opensagres.xdocreport</groupId> <artifactId> org.apache.poi.xwpf.converter.core</artifactId> <version> 1.0.6</version> </dependency> <dependency><groupId>fr.opensagres.xdocreport</groupId><artifactId>org.apache.poi.xwpf.converter.xhtml</artifactId><version>1.0.6</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>ooxml-schemas</artifactId><version>1.3</version></dependency>
package excel.test.com;import java.io.FileOutputStream;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Chart;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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.CTLegend;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineChart;
import org.openxmlformats.schemas.drawingml.x2006.chart.CTLineSer;
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.STCrosses;
import org.openxmlformats.schemas.drawingml.x2006.chart.STLegendPos;
import org.openxmlformats.schemas.drawingml.x2006.chart.STOrientation;
import org.openxmlformats.schemas.drawingml.x2006.chart.STTickLblPos;public class BarAndLineChart2 {public static void main(String[] args) throws Exception {XSSFWorkbook wb = new XSSFWorkbook();XSSFSheet sheet = wb.createSheet("Sheet1");Row row;Cell cell;row = sheet.createRow(0);row.createCell(0);row.createCell(1).setCellValue("Bars");row.createCell(2).setCellValue("Lines");for (int r = 1; r < 7; r++) {row = sheet.createRow(r);cell = row.createCell(0);cell.setCellValue("C" + r);cell = row.createCell(1);cell.setCellValue(new java.util.Random().nextDouble());cell = row.createCell(2);cell.setCellValue(new java.util.Random().nextDouble()*10d);}XSSFDrawing drawing = sheet.createDrawingPatriarch();
// ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 0, 11, 15);XSSFClientAnchor anchor = (XSSFClientAnchor) drawing.createAnchor(0, 0, 0, 0, 4, 0, 11, 15);Chart chart = drawing.createChart(anchor);CTChart ctChart = ((XSSFChart)chart).getCTChart(); CTPlotArea ctPlotArea = ctChart.getPlotArea();//the bar chartCTBarChart ctBarChart = ctPlotArea.addNewBarChart();CTBoolean ctBoolean = ctBarChart.addNewVaryColors();ctBoolean.setVal(true);ctBarChart.addNewBarDir().setVal(STBarDir.COL);//the bar seriesCTBarSer ctBarSer = ctBarChart.addNewSer();CTSerTx ctSerTx = ctBarSer.addNewTx();CTStrRef ctStrRef = ctSerTx.addNewStrRef();ctStrRef.setF("Sheet1!$B$1");ctBarSer.addNewIdx().setVal(0); CTAxDataSource cttAxDataSource = ctBarSer.addNewCat();ctStrRef = cttAxDataSource.addNewStrRef();ctStrRef.setF("Sheet1!$A$2:$A$7"); CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();ctNumRef.setF("Sheet1!$B$2:$B$7");//at least the border lines in Libreoffice Calc ;-)ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0}); //telling the BarChart that it has axes and giving them IdsctBarChart.addNewAxId().setVal(123456); //cat axis 1 (bars)ctBarChart.addNewAxId().setVal(123457); //val axis 1 (left)//the line chartCTLineChart ctLineChart = ctPlotArea.addNewLineChart();ctBoolean = ctLineChart.addNewVaryColors();ctBoolean.setVal(true);//the line seriesCTLineSer ctLineSer = ctLineChart.addNewSer();ctSerTx = ctLineSer.addNewTx();ctStrRef = ctSerTx.addNewStrRef();ctStrRef.setF("Sheet1!$C$1");ctLineSer.addNewIdx().setVal(1); cttAxDataSource = ctLineSer.addNewCat();ctStrRef = cttAxDataSource.addNewStrRef();ctStrRef.setF("Sheet1!$A$2:$A$7"); ctNumDataSource = ctLineSer.addNewVal();ctNumRef = ctNumDataSource.addNewNumRef();ctNumRef.setF("Sheet1!$C$2:$C$7");//at least the border lines in Libreoffice Calc ;-)ctLineSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0}); //telling the LineChart that it has axes and giving them IdsctLineChart.addNewAxId().setVal(123458); //cat axis 2 (lines)ctLineChart.addNewAxId().setVal(123459); //val axis 2 (right)//cat axis 1 (bars)CTCatAx ctCatAx = ctPlotArea.addNewCatAx(); ctCatAx.addNewAxId().setVal(123456); //id of the cat axisCTScaling ctScaling = ctCatAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAx.addNewDelete().setVal(false);ctCatAx.addNewAxPos().setVal(STAxPos.B);ctCatAx.addNewCrossAx().setVal(123457); //id of the val axisctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//val axis 1 (left)CTValAx ctValAx = ctPlotArea.addNewValAx(); ctValAx.addNewAxId().setVal(123457); //id of the val axisctScaling = ctValAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctValAx.addNewDelete().setVal(false);ctValAx.addNewAxPos().setVal(STAxPos.L);ctValAx.addNewCrossAx().setVal(123456); //id of the cat axisctValAx.addNewCrosses().setVal(STCrosses.AUTO_ZERO); //this val axis crosses the cat axis at zeroctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//cat axis 2 (lines)ctCatAx = ctPlotArea.addNewCatAx(); ctCatAx.addNewAxId().setVal(123458); //id of the cat axisctScaling = ctCatAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAx.addNewDelete().setVal(true); //this cat axis is deletedctCatAx.addNewAxPos().setVal(STAxPos.B);ctCatAx.addNewCrossAx().setVal(123459); //id of the val axisctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//val axis 2 (right)ctValAx = ctPlotArea.addNewValAx(); ctValAx.addNewAxId().setVal(123459); //id of the val axisctScaling = ctValAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctValAx.addNewDelete().setVal(false);ctValAx.addNewAxPos().setVal(STAxPos.R);ctValAx.addNewCrossAx().setVal(123458); //id of the cat axisctValAx.addNewCrosses().setVal(STCrosses.MAX); //this val axis crosses the cat axis at max valuectValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);//legendCTLegend ctLegend = ctChart.addNewLegend();ctLegend.addNewLegendPos().setVal(STLegendPos.B);ctLegend.addNewOverlay().setVal(false);FileOutputStream fileOut = new FileOutputStream("BarAndLineChart2.xlsx");wb.write(fileOut);fileOut.close();}
}
支付宝扫一扫
微信扫一扫
.png)
.png)
