//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); var fileStream = GetResourceStream("xlsx\\SalesReport_sourcedata.xlsx"); workbook.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["G3"].Value = "Product"; worksheet.Range["H3"].Value = "Sales"; worksheet.Range["G3"].Font.Bold = true; worksheet.Range["H3"].Font.Bold = true; worksheet.Range["G4"].Formula2 = "=SORTBY(FILTER(B4:B13,E4:E13=1),FILTER(D4:D13,E4:E13=1),-1)"; worksheet.Range["H4"].Formula2 = "=VLOOKUP(G4#,B4:D13,3,FALSE)"; workbook.Names.Add("Product", "=Sheet1!$G$4#"); workbook.Names.Add("Sales", "=Sheet1!$H$4#"); IShape chartShape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 700, 200, 500, 300); IChart chart = chartShape.Chart; ISeries series = chart.SeriesCollection.NewSeries(); series.Formula = "=SERIES(\"Sales\", Product, Sales, 1)"; // Save to an excel file workbook.Save("SalesReport.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim fileStream = GetResourceStream("xlsx\SalesReport_sourcedata.xlsx") workbook.Open(fileStream) Dim worksheet = workbook.Worksheets(0) With worksheet.Range !G3.Value = "Product" !H3.Value = "Sales" !G3.Font.Bold = True !H3.Font.Bold = True !G4.Formula2 = "=SORTBY(FILTER(B4:B13,E4:E13=1),FILTER(D4:D13,E4:E13=1),-1)" !H4.Formula2 = "=VLOOKUP(G4#,B4:D13,3,FALSE)" End With With workbook.Names .Add("Product", "=Sheet1!$G$4#") .Add("Sales", "=Sheet1!$H$4#") End With Dim chartShape As IShape = worksheet.Shapes.AddChart( ChartType.ColumnClustered, 700, 200, 500, 300) Dim chart As IChart = chartShape.Chart Dim series As ISeries = chart.SeriesCollection.NewSeries() series.Formula = "=SERIES(""Sales"", Product, Sales, 1)" ' save to an excel file workbook.Save("SalesReport.xlsx")