c#/vb.net 使用 spire.xls 创建 excel 图表-j9官方网址

spire.xls 组件为用户提供了多种图表类型选择，其中涵盖了 excel 自带的各种图表。这些图表类型被定义在枚举 spire.xls.excelcharttype 中，共有以下 74 种：

该节中我们将从以上表格中选取三种常见图表来进行创建：clustered column chart（柱状图），pie chart （饼状图）及doughnut chart（环形图）。

clustered column chart 柱状图

//加载excel文档
workbook workbook = new workbook();
workbook.loadfromfile("sample.xlsx");
//获取工作表
worksheet sheet = workbook.worksheets[0];
//设置工作表的名称
sheet.name = "柱状图";
sheet.gridlinesvisible = false;
//创建柱状图
chart chart = sheet.charts.add(excelcharttype.columnclustered);
//指定用于生成图表的数据区域
chart.datarange = sheet.range["a1:b5"];
chart.seriesdatafromrange = false;
//指定图表所在的位置
chart.leftcolumn = 5;
chart.toprow = 2;
chart.rightcolumn = 15;
chart.bottomrow = 20;
//设置图表的名称以及x、y轴的名称
chart.charttitle = "部门信息";
chart.charttitlearea.isbold = true;
chart.charttitlearea.size = 12;
chart.primarycategoryaxis.title = "部门";
chart.primarycategoryaxis.font.isbold = true;
chart.primarycategoryaxis.titlearea.isbold = true;
chart.primaryvalueaxis.title = "人数";
chart.primaryvalueaxis.hasmajorgridlines = false;
chart.primaryvalueaxis.titlearea.textrotationangle = 90;
chart.primaryvalueaxis.minvalue = 0;
chart.primaryvalueaxis.titlearea.isbold = true;
//5.设置图例的位置
chart.legend.position = legendpositiontype.right;
//保存工作薄
workbook.savetofile("columnchart.xlsx", excelversion.version2013);

'加载excel文档
dim workbook as new workbook()
workbook.loadfromfile("sample.xlsx")
'获取工作表
dim sheet as worksheet = workbook.worksheets(0)
'设置工作表的名称
sheet.name = "柱状图"
sheet.gridlinesvisible = false
'创建柱状图
dim chart as chart = sheet.charts.add(excelcharttype.columnclustered)
'指定用于生成图表的数据区域
chart.datarange = sheet.range("a1:b5")
chart.seriesdatafromrange = false
'指定图表所在的位置
chart.leftcolumn = 5
chart.toprow = 2
chart.rightcolumn = 15
chart.bottomrow = 20
'设置图表的名称以及x、y轴的名称
chart.charttitle = "部门信息"
chart.charttitlearea.isbold = true
chart.charttitlearea.size = 12
chart.primarycategoryaxis.title = "部门"
chart.primarycategoryaxis.font.isbold = true
chart.primarycategoryaxis.titlearea.isbold = true
chart.primaryvalueaxis.title = "人数"
chart.primaryvalueaxis.hasmajorgridlines = false
chart.primaryvalueaxis.titlearea.textrotationangle = 90
chart.primaryvalueaxis.minvalue = 0
chart.primaryvalueaxis.titlearea.isbold = true
'5.设置图例的位置
chart.legend.position = legendpositiontype.right
'保存工作薄
workbook.savetofile("columnchart.xlsx", excelversion.version2013)

pie chart 饼状图

//创建工作簿
workbook workbook = new workbook();
//获取工作表
worksheet sheet = workbook.worksheets[0];
//设置工作表的名称
sheet.name = "饼状图";
sheet.gridlinesvisible = false;
//向工作表添加数据
sheet.range["a1"].value = "年份";
sheet.range["a2"].value = "2002";
sheet.range["a3"].value = "2003";
sheet.range["a4"].value = "2004";
sheet.range["a5"].value = "2005";
sheet.range["b1"].value = "销售额";
sheet.range["b2"].numbervalue = 4000;
sheet.range["b3"].numbervalue = 6000;
sheet.range["b4"].numbervalue = 7000;
sheet.range["b5"].numbervalue = 8500;
sheet.range["a1:b1"].style.font.isbold = true;
sheet.range["a2:b2"].style.knowncolor = excelcolors.lightyellow;
sheet.range["a3:b3"].style.knowncolor = excelcolors.lightgreen1;
sheet.range["a4:b4"].style.knowncolor = excelcolors.lightorange;
sheet.range["a5:b5"].style.knowncolor = excelcolors.lightturquoise;
sheet.range["a1:b5"].style.borders[borderslinetype.edgetop].color = color.fromargb(0, 0, 128);
sheet.range["a1:b5"].style.borders[borderslinetype.edgetop].linestyle = linestyletype.thin;
sheet.range["a1:b5"].style.borders[borderslinetype.edgebottom].color = color.fromargb(0, 0, 128);
sheet.range["a1:b5"].style.borders[borderslinetype.edgebottom].linestyle = linestyletype.thin;
sheet.range["a1:b5"].style.borders[borderslinetype.edgeleft].color = color.fromargb(0, 0, 128);
sheet.range["a1:b5"].style.borders[borderslinetype.edgeleft].linestyle = linestyletype.thin;
sheet.range["a1:b5"].style.borders[borderslinetype.edgeright].color = color.fromargb(0, 0, 128);
sheet.range["a1:b5"].style.borders[borderslinetype.edgeright].linestyle = linestyletype.thin;
sheet.range["b2:c5"].style.numberformat = "\"$\"#,##0";
//创建饼状图表
chart chart = sheet.charts.add(excelcharttype.pie);
//指定用于生成图表的数据区域
chart.datarange = sheet.range["b2:b5"];
chart.seriesdatafromrange = false;
//指定图表所在位置
chart.leftcolumn = 1;
chart.toprow = 7;
chart.rightcolumn = 9;
chart.bottomrow = 26;
//设置图表的名称
chart.charttitle = "年销售额";
chart.charttitlearea.isbold = true;
chart.charttitlearea.size = 12;
//设置图表系列的值
spire.xls.charts.chartserie cs = chart.series[0];
cs.categorylabels = sheet.range["a2:a5"];
cs.values = sheet.range["b2:b5"];
cs.datapoints.defaultdatapoint.datalabels.hasvalue = true;
chart.plotarea.fill.visible = false;
//保存工作薄
workbook.savetofile("piechart.xls");

'创建工作簿
dim workbook as new workbook()
'获取工作表
dim sheet as worksheet = workbook.worksheets(0)
'设置工作表的名称
sheet.name = "饼状图"
sheet.gridlinesvisible = false
'向工作表添加数据
sheet.range("a1").value = "年份"
sheet.range("a2").value = "2002"
sheet.range("a3").value = "2003"
sheet.range("a4").value = "2004"
sheet.range("a5").value = "2005"
sheet.range("b1").value = "销售额"
sheet.range("b2").numbervalue = 4000
sheet.range("b3").numbervalue = 6000
sheet.range("b4").numbervalue = 7000
sheet.range("b5").numbervalue = 8500
sheet.range("a1:b1").style.font.isbold = true
sheet.range("a2:b2").style.knowncolor = excelcolors.lightyellow
sheet.range("a3:b3").style.knowncolor = excelcolors.lightgreen1
sheet.range("a4:b4").style.knowncolor = excelcolors.lightorange
sheet.range("a5:b5").style.knowncolor = excelcolors.lightturquoise
sheet.range("a1:b5").style.borders(borderslinetype.edgetop).color = color.fromargb(0, 0, 128)
sheet.range("a1:b5").style.borders(borderslinetype.edgetop).linestyle = linestyletype.thin
sheet.range("a1:b5").style.borders(borderslinetype.edgebottom).color = color.fromargb(0, 0, 128)
sheet.range("a1:b5").style.borders(borderslinetype.edgebottom).linestyle = linestyletype.thin
sheet.range("a1:b5").style.borders(borderslinetype.edgeleft).color = color.fromargb(0, 0, 128)
sheet.range("a1:b5").style.borders(borderslinetype.edgeleft).linestyle = linestyletype.thin
sheet.range("a1:b5").style.borders(borderslinetype.edgeright).color = color.fromargb(0, 0, 128)
sheet.range("a1:b5").style.borders(borderslinetype.edgeright).linestyle = linestyletype.thin
sheet.range("b2:c5").style.numberformat = """$""#,##0"
'创建饼状图表
dim chart as chart = sheet.charts.add(excelcharttype.pie)
'指定用于生成图表的数据区域
chart.datarange = sheet.range("b2:b5")
chart.seriesdatafromrange = false
'指定图表所在位置
chart.leftcolumn = 1
chart.toprow = 7
chart.rightcolumn = 9
chart.bottomrow = 26
'设置图表的名称
chart.charttitle = "年销售额"
chart.charttitlearea.isbold = true
chart.charttitlearea.size = 12
'设置图表系列的值
dim cs as spire.xls.charts.chartserie = chart.series(0)
cs.categorylabels = sheet.range("a2:a5")
cs.values = sheet.range("b2:b5")
cs.datapoints.defaultdatapoint.datalabels.hasvalue = true
chart.plotarea.fill.visible = false
'保存工作薄
workbook.savetofile("piechart.xls")

doughnut chart 环形图

//创建工作薄
workbook workbook = new workbook();
workbook.version = excelversion.version2013;
//获取工作表
worksheet sheet = workbook.worksheets[0];
sheet.gridlinesvisible = false;
//向工作表添加数据
sheet.range["a1"].value = "国家";
sheet.range["a1"].style.font.isbold = true;
sheet.range["a2"].value = "古巴";
sheet.range["a3"].value = "墨西哥";
sheet.range["a4"].value = "法国";
sheet.range["a5"].value = "德国";
sheet.range["b1"].value = "销售额";
sheet.range["b1"].style.font.isbold = true;
sheet.range["b2"].numbervalue = 6000;
sheet.range["b3"].numbervalue = 8000;
sheet.range["b4"].numbervalue = 9000;
sheet.range["b5"].numbervalue = 8500;
//创建环形图
chart chart = sheet.charts.add();
chart.charttype = excelcharttype.doughnut;
//指定用于生成图表的数据区域
chart.datarange = sheet.range["a1:b5"];
chart.seriesdatafromrange = false;
//指定图表的位置
chart.leftcolumn = 4;
chart.toprow = 2;
chart.rightcolumn = 12;
chart.bottomrow = 22;
//设置图表的名称
chart.charttitle = "销售额百分比";
chart.charttitlearea.isbold = true;
chart.charttitlearea.size = 10;
//设置图表系列的值
foreach (chartserie cs in chart.series)
{
    cs.datapoints.defaultdatapoint.datalabels.haspercentage = true;
}
//设置图例的位置
chart.legend.position = legendpositiontype.top;
//保存工作薄
workbook.savetofile("doughnutchart.xlsx");

'创建工作薄
dim workbook as new workbook()
workbook.version = excelversion.version2013
'获取工作表
dim sheet as worksheet = workbook.worksheets(0)
sheet.gridlinesvisible = false
'向工作表添加数据
sheet.range("a1").value = "国家"
sheet.range("a1").style.font.isbold = true
sheet.range("a2").value = "古巴"
sheet.range("a3").value = "墨西哥"
sheet.range("a4").value = "法国"
sheet.range("a5").value = "德国"
sheet.range("b1").value = "销售额"
sheet.range("b1").style.font.isbold = true
sheet.range("b2").numbervalue = 6000
sheet.range("b3").numbervalue = 8000
sheet.range("b4").numbervalue = 9000
sheet.range("b5").numbervalue = 8500
'创建环形图
dim chart as chart = sheet.charts.add()
chart.charttype = excelcharttype.doughnut
'指定用于生成图表的数据区域
chart.datarange = sheet.range("a1:b5")
chart.seriesdatafromrange = false
'指定图表的位置
chart.leftcolumn = 4
chart.toprow = 2
chart.rightcolumn = 12
chart.bottomrow = 22
'设置图表的名称
chart.charttitle = "销售额百分比"
chart.charttitlearea.isbold = true
chart.charttitlearea.size = 10
'设置图表系列的值
for each cs as chartserie in chart.series
  cs.datapoints.defaultdatapoint.datalabels.haspercentage = true
next
'设置图例的位置
chart.legend.position = legendpositiontype.top
'保存工作薄
workbook.savetofile("doughnutchart.xlsx")