通过处理智能标记生成图表
Aspose.Cells API提供了WorkbookDesigner类来处理智能标记,其中格式和公式放置在设计人员的电子表格中,然后使用WorkbookDesigner类根据指定的智能标记填充数据。也可以通过处理智能标记来创建Excel图表,这将需要以下步骤。
- 创建设计者电子表格
- 对指定数据源处理设计人员的电子表格
- 基于填充数据创建图表
创建设计人员的电子表格
设计师电子表格是使用Microsoft Excel应用程序或Aspose.Cells API创建的简单Excel文件,其中包含视觉格式、公式和智能标记,内容可以在运行时填充。
为了简单起见,我们将使用Aspose.Cells for .NET API创建设计师电子表格,并在后来的演示中对其进行处理,以便展示动态创建的数据源。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create an instance of Workbook | |
var book = new Workbook(); | |
// Access the first, default Worksheet by passing its index | |
var dataSheet = book.Worksheets[0]; | |
// Name the Worksheet for later reference | |
dataSheet.Name = "ChartData"; | |
// Access the CellsCollection of first Worksheet | |
var cells = dataSheet.Cells; | |
// Insert static data (headers) | |
cells["B1"].PutValue("Item 1"); | |
cells["C1"].PutValue("Item 2"); | |
cells["D1"].PutValue("Item 3"); | |
cells["E1"].PutValue("Item 4"); | |
cells["F1"].PutValue("Item 5"); | |
cells["G1"].PutValue("Item 6"); | |
cells["H1"].PutValue("Item 7"); | |
cells["I1"].PutValue("Item 8"); | |
cells["J1"].PutValue("Item 9"); | |
cells["K1"].PutValue("Item 10"); | |
cells["L1"].PutValue("Item 11"); | |
cells["M1"].PutValue("Item 12"); | |
// Place Smart Markers | |
cells["A2"].PutValue("&=Sales.Year"); | |
cells["B2"].PutValue("&=Sales.Item1"); | |
cells["C2"].PutValue("&=Sales.Item2"); | |
cells["D2"].PutValue("&=Sales.Item3"); | |
cells["E2"].PutValue("&=Sales.Item4"); | |
cells["F2"].PutValue("&=Sales.Item5"); | |
cells["G2"].PutValue("&=Sales.Item6"); | |
cells["H2"].PutValue("&=Sales.Item7"); | |
cells["I2"].PutValue("&=Sales.Item8"); | |
cells["J2"].PutValue("&=Sales.Item9"); | |
cells["K2"].PutValue("&=Sales.Item10"); | |
cells["L2"].PutValue("&=Sales.Item11"); | |
cells["M2"].PutValue("&=Sales.Item12"); |
处理设计师电子表格
为了处理设计师电子表格,必须拥有与设计师电子表格中使用的智能标记相对应的数据源。例如,我们创建了一个智能标记条目 &=Sales.Year,代表数据表Sales中的年份列。如果数据源中没有对应的列,Aspose.Cells API将跳过该特定智能标记的处理,结果该特定智能标记的数据将不会被填充。
为了演示此用例,我们将从头开始创建数据源,并针对之前创建的设计师电子表格进行处理。然而,在实时场景中,数据可能已经可用进行进一步处理,因此如果数据已经可用,可以跳过创建数据源的步骤。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// Create an instance of DataTable and name is according to the Smart Markers | |
var table = new DataTable("Sales"); | |
/* | |
* Add columns to the newly created DataTable while specifying the column type | |
* It is important that the DataTable should have at least one column for each | |
* Smart Marker entry from the designer spreadsheet | |
*/ | |
table.Columns.Add("Year", typeof(string)); | |
table.Columns.Add("Item1", typeof(int)); | |
table.Columns.Add("Item2", typeof(int)); | |
table.Columns.Add("Item3", typeof(int)); | |
table.Columns.Add("Item4", typeof(int)); | |
table.Columns.Add("Item5", typeof(int)); | |
table.Columns.Add("Item6", typeof(int)); | |
table.Columns.Add("Item7", typeof(int)); | |
table.Columns.Add("Item8", typeof(int)); | |
table.Columns.Add("Item9", typeof(int)); | |
table.Columns.Add("Item10", typeof(int)); | |
table.Columns.Add("Item11", typeof(int)); | |
table.Columns.Add("Item12", typeof(int)); | |
// Add some rows with data to the DataTable | |
table.Rows.Add("2000", 2310, 0, 110, 15, 20, 25, 30, 1222, 200, 421, 210, 133); | |
table.Rows.Add("2005", 1508, 0, 170, 280, 190, 400, 105, 132, 303, 199, 120, 100); | |
table.Rows.Add("2010", 0, 210, 230, 140, 150, 160, 170, 110, 1999, 1229, 1120, 2300); | |
table.Rows.Add("2015", 3818, 320, 340, 260, 210, 310, 220, 0, 0, 0, 0, 122); |
如下的代码片段演示了智能标记的处理非常简单。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create an instance of Workbook | |
var book = new Workbook(); | |
// Access the first, default Worksheet by passing its index | |
var dataSheet = book.Worksheets[0]; | |
// Name the Worksheet for later reference | |
dataSheet.Name = "ChartData"; | |
// Access the CellsCollection of first Worksheet | |
var cells = dataSheet.Cells; | |
// Insert static data (headers) | |
cells["B1"].PutValue("Item 1"); | |
cells["C1"].PutValue("Item 2"); | |
cells["D1"].PutValue("Item 3"); | |
cells["E1"].PutValue("Item 4"); | |
cells["F1"].PutValue("Item 5"); | |
cells["G1"].PutValue("Item 6"); | |
cells["H1"].PutValue("Item 7"); | |
cells["I1"].PutValue("Item 8"); | |
cells["J1"].PutValue("Item 9"); | |
cells["K1"].PutValue("Item 10"); | |
cells["L1"].PutValue("Item 11"); | |
cells["M1"].PutValue("Item 12"); | |
// Place Smart Markers | |
cells["A2"].PutValue("&=Sales.Year"); | |
cells["B2"].PutValue("&=Sales.Item1"); | |
cells["C2"].PutValue("&=Sales.Item2"); | |
cells["D2"].PutValue("&=Sales.Item3"); | |
cells["E2"].PutValue("&=Sales.Item4"); | |
cells["F2"].PutValue("&=Sales.Item5"); | |
cells["G2"].PutValue("&=Sales.Item6"); | |
cells["H2"].PutValue("&=Sales.Item7"); | |
cells["I2"].PutValue("&=Sales.Item8"); | |
cells["J2"].PutValue("&=Sales.Item9"); | |
cells["K2"].PutValue("&=Sales.Item10"); | |
cells["L2"].PutValue("&=Sales.Item11"); | |
cells["M2"].PutValue("&=Sales.Item12"); | |
// Create an instance of DataTable and name is according to the Smart Markers | |
var table = new DataTable("Sales"); | |
/* | |
* Add columns to the newly created DataTable while specifying the column type | |
* It is important that the DataTable should have at least one column for each | |
* Smart Marker entry from the designer spreadsheet | |
*/ | |
table.Columns.Add("Year", typeof(string)); | |
table.Columns.Add("Item1", typeof(int)); | |
table.Columns.Add("Item2", typeof(int)); | |
table.Columns.Add("Item3", typeof(int)); | |
table.Columns.Add("Item4", typeof(int)); | |
table.Columns.Add("Item5", typeof(int)); | |
table.Columns.Add("Item6", typeof(int)); | |
table.Columns.Add("Item7", typeof(int)); | |
table.Columns.Add("Item8", typeof(int)); | |
table.Columns.Add("Item9", typeof(int)); | |
table.Columns.Add("Item10", typeof(int)); | |
table.Columns.Add("Item11", typeof(int)); | |
table.Columns.Add("Item12", typeof(int)); | |
// Add some rows with data to the DataTable | |
table.Rows.Add("2000", 2310, 0, 110, 15, 20, 25, 30, 1222, 200, 421, 210, 133); | |
table.Rows.Add("2005", 1508, 0, 170, 280, 190, 400, 105, 132, 303, 199, 120, 100); | |
table.Rows.Add("2010", 0, 210, 230, 140, 150, 160, 170, 110, 1999, 1229, 1120, 2300); | |
table.Rows.Add("2015", 3818, 320, 340, 260, 210, 310, 220, 0, 0, 0, 0, 122); | |
// Create an instance of WorkbookDesigner class | |
var designer = new WorkbookDesigner(); | |
// Assign the Workbook property to the instance of Workbook created in first step | |
designer.Workbook = book; | |
// Set the data source | |
designer.SetDataSource(table); | |
// Call Process method to populate data | |
designer.Process(); |
创建图表
一旦数据就位,我们只需要基于数据源创建一个图表。为了保持示例简单,我们将使用Chart.SetChartDataRange方法,这样我们就不必进一步配置图表。
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
// The path to the documents directory. | |
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
// Create an instance of Workbook | |
var book = new Workbook(); | |
// Access the first, default Worksheet by passing its index | |
var dataSheet = book.Worksheets[0]; | |
// Name the Worksheet for later reference | |
dataSheet.Name = "ChartData"; | |
// Access the CellsCollection of first Worksheet | |
var cells = dataSheet.Cells; | |
// Insert static data (headers) | |
cells["B1"].PutValue("Item 1"); | |
cells["C1"].PutValue("Item 2"); | |
cells["D1"].PutValue("Item 3"); | |
cells["E1"].PutValue("Item 4"); | |
cells["F1"].PutValue("Item 5"); | |
cells["G1"].PutValue("Item 6"); | |
cells["H1"].PutValue("Item 7"); | |
cells["I1"].PutValue("Item 8"); | |
cells["J1"].PutValue("Item 9"); | |
cells["K1"].PutValue("Item 10"); | |
cells["L1"].PutValue("Item 11"); | |
cells["M1"].PutValue("Item 12"); | |
// Place Smart Markers | |
cells["A2"].PutValue("&=Sales.Year"); | |
cells["B2"].PutValue("&=Sales.Item1"); | |
cells["C2"].PutValue("&=Sales.Item2"); | |
cells["D2"].PutValue("&=Sales.Item3"); | |
cells["E2"].PutValue("&=Sales.Item4"); | |
cells["F2"].PutValue("&=Sales.Item5"); | |
cells["G2"].PutValue("&=Sales.Item6"); | |
cells["H2"].PutValue("&=Sales.Item7"); | |
cells["I2"].PutValue("&=Sales.Item8"); | |
cells["J2"].PutValue("&=Sales.Item9"); | |
cells["K2"].PutValue("&=Sales.Item10"); | |
cells["L2"].PutValue("&=Sales.Item11"); | |
cells["M2"].PutValue("&=Sales.Item12"); | |
// Create an instance of DataTable and name is according to the Smart Markers | |
var table = new DataTable("Sales"); | |
/* | |
* Add columns to the newly created DataTable while specifying the column type | |
* It is important that the DataTable should have at least one column for each | |
* Smart Marker entry from the designer spreadsheet | |
*/ | |
table.Columns.Add("Year", typeof(string)); | |
table.Columns.Add("Item1", typeof(int)); | |
table.Columns.Add("Item2", typeof(int)); | |
table.Columns.Add("Item3", typeof(int)); | |
table.Columns.Add("Item4", typeof(int)); | |
table.Columns.Add("Item5", typeof(int)); | |
table.Columns.Add("Item6", typeof(int)); | |
table.Columns.Add("Item7", typeof(int)); | |
table.Columns.Add("Item8", typeof(int)); | |
table.Columns.Add("Item9", typeof(int)); | |
table.Columns.Add("Item10", typeof(int)); | |
table.Columns.Add("Item11", typeof(int)); | |
table.Columns.Add("Item12", typeof(int)); | |
// Add some rows with data to the DataTable | |
table.Rows.Add("2000", 2310, 0, 110, 15, 20, 25, 30, 1222, 200, 421, 210, 133); | |
table.Rows.Add("2005", 1508, 0, 170, 280, 190, 400, 105, 132, 303, 199, 120, 100); | |
table.Rows.Add("2010", 0, 210, 230, 140, 150, 160, 170, 110, 1999, 1229, 1120, 2300); | |
table.Rows.Add("2015", 3818, 320, 340, 260, 210, 310, 220, 0, 0, 0, 0, 122); | |
// Create an instance of WorkbookDesigner class | |
var designer = new WorkbookDesigner(); | |
// Assign the Workbook property to the instance of Workbook created in first step | |
designer.Workbook = book; | |
// Set the data source | |
designer.SetDataSource(table); | |
// Call Process method to populate data | |
designer.Process(); | |
/* | |
* Save the number of rows & columns from the source DataTable in seperate variables. | |
* These values will be used later to identify the chart's data range from DataSheet | |
*/ | |
int chartRows = table.Rows.Count; | |
int chartCols = table.Columns.Count; | |
// Add a new Worksheet of type Chart to Workbook | |
int chartSheetIdx = book.Worksheets.Add(SheetType.Chart); | |
// Access the newly added Worksheet via its index | |
var chartSheet = book.Worksheets[chartSheetIdx]; | |
// Name the Worksheet | |
chartSheet.Name = "Chart"; | |
// Add a chart of type ColumnStacked to newly added Worksheet | |
int chartIdx = chartSheet.Charts.Add(ChartType.ColumnStacked, 0, 0, chartRows, chartCols); | |
// Access the newly added Chart via its index | |
var chart = chartSheet.Charts[chartIdx]; | |
// Set the data range for the chart | |
chart.SetChartDataRange(dataSheet.Name + "!A1:" + CellsHelper.ColumnIndexToName(chartCols - 1) + (chartRows + 1).ToString(), false); | |
// Set the chart to size with window | |
chart.SizeWithWindow = true; | |
// Set the format for the tick labels | |
chart.ValueAxis.TickLabels.NumberFormat = "$###,### K"; | |
// Set chart title | |
chart.Title.Text = "Sales Summary"; | |
// Set ChartSheet an active sheet | |
book.Worksheets.ActiveSheetIndex = chartSheetIdx; | |
// Save the final result | |
book.Save(dataDir + "report_out.xlsx"); |