//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); // Define a json data source var dataSource = "{ \"ds\":" + "[\n" + " {\"Area\": \"North America\",\"City\": \"Chicago\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 92800},\n" + " {\"Area\": \"North America\",\"City\": \"New York\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 92800},\n" + " {\"Area\": \"South America\",\"City\": \"Santiago\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 19550},\n" + " {\"Area\": \"Europe\",\"City\": \"Berlin\",\"Category\": \"Consumer Electronics\",\"Name\": \"Sony WH-1000XM4\",\"Revenue\": 30000},\n" + " {\"Area\": \"Asia\",\"City\": \"Tokyo\",\"Category\": \"Consumer Electronics\",\"Name\": \"Sony WH-1000XM4\",\"Revenue\": 45000},\n" + " {\"Area\": \"North America\",\"City\": \"Los Angeles\",\"Category\": \"Consumer Electronics\",\"Name\": \"Apple AirPods\",\"Revenue\": 60000},\n" + " {\"Area\": \"Europe\",\"City\": \"Paris\",\"Category\": \"Consumer Electronics\",\"Name\": \"Apple AirPods\",\"Revenue\": 55000},\n" + " {\"Area\": \"Asia\",\"City\": \"Seoul\",\"Category\": \"Consumer Electronics\",\"Name\": \"Samsung Galaxy Buds\",\"Revenue\": 40000},\n" + " {\"Area\": \"South America\",\"City\": \"Buenos Aires\",\"Category\": \"Consumer Electronics\",\"Name\": \"Samsung Galaxy Buds\",\"Revenue\": 35000},\n" + " {\"Area\": \"North America\",\"City\": \"Toronto\",\"Category\": \"Consumer Electronics\",\"Name\": \"Bose 785593-0050\",\"Revenue\": 50000}\n" + " ]" + "}"; // Data source sheet var dataSourceSheet = workbook.Worksheets.Add(); dataSourceSheet.Name = "DataSource"; var table = dataSourceSheet.Tables.Add(dataSourceSheet.Range["A1:E4"], true); // Set binding path table.BindingPath = "ds"; table.Columns[0].DataField = "Area"; table.Columns[1].DataField = "City"; table.Columns[2].DataField = "Category"; table.Columns[3].DataField = "Name"; table.Columns[4].DataField = "Revenue"; // Set data source dataSourceSheet.DataSource = new JsonDataSource(dataSource); // Pivot table sheet var pivotSheet = workbook.Worksheets[0]; pivotSheet.Name = "PivotSheet"; // Create pivot table var pivotCache = workbook.PivotCaches.Create(table); var pivotTable = pivotSheet.PivotTables.Add(pivotCache, pivotSheet.Range["A1"], "pivottable1"); // Config pivot table's fields var fieldArea = pivotTable.PivotFields["Area"]; fieldArea.Orientation = PivotFieldOrientation.RowField; var fieldCity = pivotTable.PivotFields["City"]; fieldCity.Orientation = PivotFieldOrientation.RowField; var fieldName = pivotTable.PivotFields["Name"]; fieldName.Orientation = PivotFieldOrientation.ColumnField; var fieldRevenue = pivotTable.PivotFields["Revenue"]; fieldRevenue.Orientation = PivotFieldOrientation.DataField; pivotSheet.UsedRange.AutoFit(); pivotTable.ColumnGrand = false; pivotTable.RowGrand = false; pivotTable.Refresh(); var saveOptions = new SjsSaveOptions(); // Set the IncludeBindingSource property to false to exclude the binding source from being exported. saveOptions.IncludeBindingSource = false; // Document Solutions for Excel supports using SaveOptions to save .sjs file format. workbook.Save("IncludeBindingSourceOption.sjs", saveOptions);
' Create a new Workbook Dim workbook As New Workbook ' Define a json data source Dim dataSource As String = "{ ""ds"":" & "[ " & " {""Area"": ""North America"",""City"": ""Chicago"",""Category"": ""Consumer Electronics"",""Name"": ""Bose 785593-0050"",""Revenue"": 92800}," & " {""Area"": ""North America"",""City"": ""New York"",""Category"": ""Consumer Electronics"",""Name"": ""Bose 785593-0050"",""Revenue"": 92800}," & " {""Area"": ""South America"",""City"": ""Santiago"",""Category"": ""Consumer Electronics"",""Name"": ""Bose 785593-0050"",""Revenue"": 19550}," & " {""Area"": ""Europe"",""City"": ""Berlin"",""Category"": ""Consumer Electronics"",""Name"": ""Sony WH-1000XM4"",""Revenue"": 30000}," & " {""Area"": ""Asia"",""City"": ""Tokyo"",""Category"": ""Consumer Electronics"",""Name"": ""Sony WH-1000XM4"",""Revenue"": 45000}," & " {""Area"": ""North America"",""City"": ""Los Angeles"",""Category"": ""Consumer Electronics"",""Name"": ""Apple AirPods"",""Revenue"": 60000}," & " {""Area"": ""Europe"",""City"": ""Paris"",""Category"": ""Consumer Electronics"",""Name"": ""Apple AirPods"",""Revenue"": 55000}," & " {""Area"": ""Asia"",""City"": ""Seoul"",""Category"": ""Consumer Electronics"",""Name"": ""Samsung Galaxy Buds"",""Revenue"": 40000}," & " {""Area"": ""South America"",""City"": ""Buenos Aires"",""Category"": ""Consumer Electronics"",""Name"": ""Samsung Galaxy Buds"",""Revenue"": 35000}," & " {""Area"": ""North America"",""City"": ""Toronto"",""Category"": ""Consumer Electronics"",""Name"": ""Bose 785593-0050"",""Revenue"": 50000}" & " ]" & "}" ' Data source sheet Dim dataSourceSheet = workbook.Worksheets.Add() dataSourceSheet.Name = "DataSource" Dim table = dataSourceSheet.Tables.Add(dataSourceSheet.Range("A1:E4"), True) ' Set binding path table.BindingPath = "ds" table.Columns(0).DataField = "Area" table.Columns(1).DataField = "City" table.Columns(2).DataField = "Category" table.Columns(3).DataField = "Name" table.Columns(4).DataField = "Revenue" ' Set data source dataSourceSheet.DataSource = New JsonDataSource(dataSource) ' Pivot table sheet Dim pivotSheet = workbook.Worksheets(0) pivotSheet.Name = "PivotSheet" ' Create pivot table Dim pivotCache = workbook.PivotCaches.Create(table) Dim pivotTable = pivotSheet.PivotTables.Add(pivotCache, pivotSheet.Range("A1"), "pivottable1") ' Config pivot table's fields Dim fieldArea = pivotTable.PivotFields("Area") fieldArea.Orientation = PivotFieldOrientation.RowField Dim fieldCity = pivotTable.PivotFields("City") fieldCity.Orientation = PivotFieldOrientation.RowField Dim fieldName = pivotTable.PivotFields("Name") fieldName.Orientation = PivotFieldOrientation.ColumnField Dim fieldRevenue = pivotTable.PivotFields("Revenue") fieldRevenue.Orientation = PivotFieldOrientation.DataField pivotSheet.UsedRange.AutoFit() pivotTable.ColumnGrand = False pivotTable.RowGrand = False pivotTable.Refresh() Dim saveOptions = New SjsSaveOptions() ' Set the IncludeBindingSource property to false to exclude the binding source from being exported. saveOptions.IncludeBindingSource = False ' Document Solutions for Excel support using SaveOptions to save .sjs file format. workbook.Save("IncludeBindingSourceOption.sjs", saveOptions)