Aspose.Cells提供了强大的API可以帮助开发人员在Excel文件里插入图表对象，创建多种图表类型，直观地显示Excel工作表中的数据。在实际应用中图表的数据时来源于工作表中的单元格数据，客户一般需要图表的显示随数据源数据改变而改变，这就是所谓的动态图表。一般触发数据源改变的操作是过滤操作以及使用ComboBox 或 Dropdown list对数据源进行改变，这篇文章主要介绍怎么利用
Aspose.Cells来创建动态图表，以及如何在创建动态图表时使用Excel公式，具体可以参考下面的详细代码：
1.使用Excel表格作为数据源，所谓的Excel表格在Aspose.Cells中为ListObjects，通过该对象来为图表赋值
var book = new Workbook();
// Access first worksheet from the collection
var sheet = book.Worksheets[0];
// Access cells collection of the first worksheet
var cells = sheet.Cells;
// Insert data column wise
cells["A1"].PutValue("Category");
cells["A2"].PutValue("Fruit");
cells["A3"].PutValue("Fruit");
cells["A4"].PutValue("Fruit");
cells["A5"].PutValue("Fruit");
cells["A6"].PutValue("Vegetables");
cells["A7"].PutValue("Vegetables");
cells["A8"].PutValue("Vegetables");
cells["A9"].PutValue("Vegetables");
cells["A10"].PutValue("Beverages");
cells["A11"].PutValue("Beverages");
cells["A12"].PutValue("Beverages");
cells["B1"].PutValue("Food");
cells["B2"].PutValue("Apple");
cells["B3"].PutValue("Banana");
cells["B4"].PutValue("Apricot");
cells["B5"].PutValue("Grapes");
cells["B6"].PutValue("Carrot");
cells["B7"].PutValue("Onion");
cells["B8"].PutValue("Cabage");
cells["B9"].PutValue("Potatoe");
cells["B10"].PutValue("Coke");
cells["B11"].PutValue("Coladas");
cells["B12"].PutValue("Fizz");
cells["C1"].PutValue("Cost");
cells["C2"].PutValue(2.2);
cells["C3"].PutValue(3.1);
cells["C4"].PutValue(4.1);
cells["C5"].PutValue(5.1);
cells["C6"].PutValue(4.4);
cells["C7"].PutValue(5.4);
cells["C8"].PutValue(6.5);
cells["C9"].PutValue(5.3);
cells["C10"].PutValue(3.2);
cells["C11"].PutValue(3.6);
cells["C12"].PutValue(5.2);
cells["D1"].PutValue("Profit");
cells["D2"].PutValue(0.1);
cells["D3"].PutValue(0.4);
cells["D4"].PutValue(0.5);
cells["D5"].PutValue(0.6);
cells["D6"].PutValue(0.7);
cells["D7"].PutValue(1.3);
cells["D8"].PutValue(0.8);
cells["D9"].PutValue(1.3);
cells["D10"].PutValue(2.2);
cells["D11"].PutValue(2.4);
cells["D12"].PutValue(3.3);
// Create ListObject
//Get the List objects collection in the first worksheet.
var listObjects = sheet.ListObjects;
// Add a List based on the data source range with headers on.
var index = listObjects.Add(0, 0, 11, 3, true);
sheet.AutoFitColumns();
// Create chart based on ListObject
index = sheet.Charts.Add(Charts.ChartType.Column, 21, 1, 35, 18);
var chart = sheet.Charts[index];
chart.SetChartDataRange("A1:D12", true);
chart.NSeries.CategoryData = "A2:B12";
// Remove all series except Cost & Profit
ArrayList list = new ArrayList();
for (index = 0; index < chart.NSeries.Count; index++)
{
var series = chart.NSeries[index];
if (series.DisplayName != "Cost" && series.DisplayName != "Profit")
{
list.Add(index);
}
}
for (index = 0; index < list.Count; index++)
{
chart.NSeries.RemoveAt((int)list[index]);
}
// Calculate chart
chart.Calculate();
book.Save(dir + "output.xlsx");
2.如何在创建图表时使用Excel公式
var workbook = new Workbook();
// Get the first worksheet
var worksheet = workbook.Worksheets[0];
// Create a range in the second worksheet
var range = worksheet.Cells.CreateRange("C21", "C24");
// Name the range
range.Name = "MyRange";
// Fill different cells with data in the range
range[0, 0].PutValue("North");
range[1, 0].PutValue("South");
range[2, 0].PutValue("East");
range[3, 0].PutValue("West");
ComboBox comboBox = worksheet.Shapes.AddComboBox(15, 0, 2, 0, 17, 64);
comboBox.InputRange = "=MyRange";
comboBox.LinkedCell = "=B16";
comboBox.SelectedIndex = 0;
Cell cell = worksheet.Cells["B16"];
Style style = cell.GetStyle();
style.Font.Color = Color.White;
cell.SetStyle(style);
worksheet.Cells["C16"].Formula = "=INDEX(Sheet1!$C$21:$C$24,$B$16,1)";
// Put some data for chart source
// Data Headers
worksheet.Cells["D15"].PutValue("Jan");
worksheet.Cells["D20"].PutValue("Jan");
worksheet.Cells["E15"].PutValue("Feb");
worksheet.Cells["E20"].PutValue("Feb");
worksheet.Cells["F15"].PutValue("Mar");
worksheet.Cells["F20"].PutValue("Mar");
worksheet.Cells["G15"].PutValue("Apr");
worksheet.Cells["G20"].PutValue("Apr");
worksheet.Cells["H15"].PutValue("May");
worksheet.Cells["H20"].PutValue("May");
worksheet.Cells["I15"].PutValue("Jun");
worksheet.Cells["I20"].PutValue("Jun");
// Data
worksheet.Cells["D21"].PutValue(304);
worksheet.Cells["D22"].PutValue(402);
worksheet.Cells["D23"].PutValue(321);
worksheet.Cells["D24"].PutValue(123);
worksheet.Cells["E21"].PutValue(300);
worksheet.Cells["E22"].PutValue(500);
worksheet.Cells["E23"].PutValue(219);
worksheet.Cells["E24"].PutValue(422);
worksheet.Cells["F21"].PutValue(222);
worksheet.Cells["F22"].PutValue(331);
worksheet.Cells["F23"].PutValue(112);
worksheet.Cells["F24"].PutValue(350);
worksheet.Cells["G21"].PutValue(100);
worksheet.Cells["G22"].PutValue(200);
worksheet.Cells["G23"].PutValue(300);
worksheet.Cells["G24"].PutValue(400);
worksheet.Cells["H21"].PutValue(200);
worksheet.Cells["H22"].PutValue(300);
worksheet.Cells["H23"].PutValue(400);
worksheet.Cells["H24"].PutValue(500);
worksheet.Cells["I21"].PutValue(400);
worksheet.Cells["I22"].PutValue(200);
worksheet.Cells["I23"].PutValue(200);
worksheet.Cells["I24"].PutValue(100);
// Dynamically load data on selection of Dropdown value
worksheet.Cells["D16"].Formula = "=IFERROR(VLOOKUP($C$16,$C$21:$I$24,2,FALSE),0)";
worksheet.Cells["E16"].Formula = "=IFERROR(VLOOKUP($C$16,$C$21:$I$24,3,FALSE),0)";
worksheet.Cells["F16"].Formula = "=IFERROR(VLOOKUP($C$16,$C$21:$I$24,4,FALSE),0)";
worksheet.Cells["G16"].Formula = "=IFERROR(VLOOKUP($C$16,$C$21:$I$24,5,FALSE),0)";
worksheet.Cells["H16"].Formula = "=IFERROR(VLOOKUP($C$16,$C$21:$I$24,6,FALSE),0)";
worksheet.Cells["I16"].Formula = "=IFERROR(VLOOKUP($C$16,$C$21:$I$24,7,FALSE),0)";
// Create Chart
int index = worksheet.Charts.Add(ChartType.Column, 0, 3, 12, 9);
Chart chart = worksheet.Charts[index];
chart.NSeries.Add("='Sheet1'!$D$16:$I$16", false);
chart.NSeries[0].Name = "=C16";
chart.NSeries.CategoryData = "=$D$15:$I$15";
// Save result on disc
workbook.Save(dir + "output.xlsx");
