//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); //class CustomObjectJsonSerializer : IJsonSerializer //{ // public object Deserialize(string json) // { // return Newtonsoft.Json.JsonConvert.DeserializeObject(json); // } // public string Serialize(object value) // { // return Newtonsoft.Json.JsonConvert.SerializeObject(value); // } //} //class PersonalAssets //{ // public string Name; // public int Savings; // public int Shares; // public int Stocks; // public int House; // public int Bonds; // public int Car; //} Excel.Workbook.ValueJsonSerializer = new CustomObjectJsonSerializer(); var sheet1 = workbook.ActiveSheet; var sheet2 = workbook.Worksheets.Add(); #region Init data var record1 = new PersonalAssets { Name = "Peyton", Savings = 25000, Shares = 55000, Stocks = 15000, House = 250000, Bonds = 11000, Car = 7500 }; var record2 = new PersonalAssets { Name = "Icey", Savings = 30000, Shares = 45000, Stocks = 25000, House = 20000, Bonds = 18000, Car = 75000 }; var record3 = new PersonalAssets { Name = "Walter", Savings = 20000, Shares = 4000, Stocks = 95000, House = 30000, Bonds = 10000, Car = 56000 }; var record4 = new PersonalAssets { Name = "Chris", Savings = 70000, Shares = 85000, Stocks = 35000, House = 20000, Bonds = 15000, Car = 45000 }; #endregion // Set binding path for cell. sheet2.Range["A1:C1"].Merge(); sheet2.Range["A1:C1"].HorizontalAlignment = HorizontalAlignment.Center; sheet2.Range["A1:C1"].VerticalAlignment = VerticalAlignment.Center; sheet2.Range["A1"].BindingPath = "Name"; sheet2.Range["A1"].Font.Name = "Arial"; sheet2.Range["A1"].Font.Size = 15; sheet2.Range["1:1"].RowHeight = 30; sheet2.Range["A2"].Value = "Asset Type"; sheet2.Range["B2"].Value = "Amount"; sheet2.Range["C2"].Value = "Rate"; sheet2.Range["A3"].Value = "Savings"; sheet2.Range["A3"].Interior.Color = Color.FromArgb(145, 159, 129); sheet2.Range["B3"].BindingPath = "Savings"; sheet2.Range["C3"].Formula = "=B3/B9"; sheet2.Range["A4"].Value = "Shares"; sheet2.Range["A4"].Interior.Color = Color.FromArgb(215, 145, 62); sheet2.Range["B4"].BindingPath = "Shares"; sheet2.Range["C4"].Formula = "=B4/B9"; sheet2.Range["A5"].Value = "Stocks"; sheet2.Range["A5"].Interior.Color = Color.FromArgb(206, 167, 34); sheet2.Range["B5"].BindingPath = "Stocks"; sheet2.Range["C5"].Formula = "=B5/B9"; sheet2.Range["A6"].Value = "House"; sheet2.Range["A6"].Interior.Color = Color.FromArgb(181, 128, 145); sheet2.Range["B6"].BindingPath = "House"; sheet2.Range["C6"].Formula = "=B6/B9"; sheet2.Range["A7"].Value = "Bonds"; sheet2.Range["A7"].Interior.Color = Color.FromArgb(137, 116, 169); sheet2.Range["B7"].BindingPath = "Bonds"; sheet2.Range["C7"].Formula = "=B7/B9"; sheet2.Range["A8"].Value = "Car"; sheet2.Range["A8"].Interior.Color = Color.FromArgb(114, 139, 173); sheet2.Range["B8"].BindingPath = "Car"; sheet2.Range["C8"].Formula = "=B8/B9"; sheet2.Range["A9"].Value = "Total"; sheet2.Range["B9:C9"].Merge(); sheet2.Range["B9:C9"].HorizontalAlignment = HorizontalAlignment.Center; sheet2.Range["B9:C9"].NumberFormat = "$#,##0_);($#,##0)"; sheet2.Range["B9:C9"].Formula = "=SUM(B3:B8)"; sheet2.Range["B3:B8"].NumberFormat = "$#,##0_);($#,##0)"; sheet2.Range["C3:C8"].NumberFormat = "0.00%"; sheet2.Range["C3:C8"].FormatConditions.AddDatabar(); // Set data source. sheet1.Range["A:B"].ColumnWidthInPixel = 300; sheet1.Range["1:2"].RowHeightInPixel = 200; sheet1.Range["A1"].Value = record1; sheet1.Range["B1"].Value = record2; sheet1.Range["A2"].Value = record3; sheet1.Range["B2"].Value = record4; // Create a range template celltype var rangeTemplateCelltype = new RangeTemplateCellType(sheet2); // Apply cell type to "A1:B2" sheet1.Range["A1:B2"].CellType = rangeTemplateCelltype; // Save to a pdf file workbook.Save("AddRangeTemplateCellType.pdf");
' Create a new Workbook Dim workbook As New Workbook 'class CustomObjectJsonSerializer : IJsonSerializer '{ ' public object Deserialize(string json) ' { ' return Newtonsoft.Json.JsonConvert.DeserializeObject(json); ' } ' public string Serialize(object value) ' { ' return Newtonsoft.Json.JsonConvert.SerializeObject(value); ' } '} 'class PersonalAssets '{ ' public string Name; ' public int Savings; ' public int Shares; ' public int Stocks; ' public int House; ' public int Bonds; ' public int Car; '} Excel.Workbook.ValueJsonSerializer = New CustomObjectJsonSerializer() Dim sheet1 = workbook.ActiveSheet Dim sheet2 = workbook.Worksheets.Add() #Region "Init data" Dim record1 = New PersonalAssets With { .Name = "Peyton", .Savings = 25000, .Shares = 55000, .Stocks = 15000, .House = 250000, .Bonds = 11000, .Car = 7500 } Dim record2 = New PersonalAssets With { .Name = "Icey", .Savings = 30000, .Shares = 45000, .Stocks = 25000, .House = 20000, .Bonds = 18000, .Car = 75000 } Dim record3 = New PersonalAssets With { .Name = "Walter", .Savings = 20000, .Shares = 4000, .Stocks = 95000, .House = 30000, .Bonds = 10000, .Car = 56000 } Dim record4 = New PersonalAssets With { .Name = "Chris", .Savings = 70000, .Shares = 85000, .Stocks = 35000, .House = 20000, .Bonds = 15000, .Car = 45000 } #End Region ' Set binding path for cell. sheet2.Range("A1:C1").Merge() sheet2.Range("A1:C1").HorizontalAlignment = HorizontalAlignment.Center sheet2.Range("A1:C1").VerticalAlignment = VerticalAlignment.Center sheet2.Range!A1.BindingPath = "Name" sheet2.Range!A1.Font.Name = "Arial" sheet2.Range!A1.Font.Size = 15 sheet2.Range("1:1").RowHeight = 30 sheet2.Range!A2.Value = "Asset Type" sheet2.Range!B2.Value = "Amount" sheet2.Range!C2.Value = "Rate" sheet2.Range!A3.Value = "Savings" sheet2.Range!A3.Interior.Color = Color.FromArgb(145, 159, 129) sheet2.Range!B3.BindingPath = "Savings" sheet2.Range!C3.Formula = "=B3/B9" sheet2.Range!A4.Value = "Shares" sheet2.Range!A4.Interior.Color = Color.FromArgb(215, 145, 62) sheet2.Range!B4.BindingPath = "Shares" sheet2.Range!C4.Formula = "=B4/B9" sheet2.Range!A5.Value = "Stocks" sheet2.Range!A5.Interior.Color = Color.FromArgb(206, 167, 34) sheet2.Range!B5.BindingPath = "Stocks" sheet2.Range!C5.Formula = "=B5/B9" sheet2.Range!A6.Value = "House" sheet2.Range!A6.Interior.Color = Color.FromArgb(181, 128, 145) sheet2.Range!B6.BindingPath = "House" sheet2.Range!C6.Formula = "=B6/B9" sheet2.Range!A7.Value = "Bonds" sheet2.Range!A7.Interior.Color = Color.FromArgb(137, 116, 169) sheet2.Range!B7.BindingPath = "Bonds" sheet2.Range!C7.Formula = "=B7/B9" sheet2.Range!A8.Value = "Car" sheet2.Range!A8.Interior.Color = Color.FromArgb(114, 139, 173) sheet2.Range!B8.BindingPath = "Car" sheet2.Range!C8.Formula = "=B8/B9" sheet2.Range!A9.Value = "Total" sheet2.Range("B9:C9").Merge() sheet2.Range("B9:C9").HorizontalAlignment = HorizontalAlignment.Center sheet2.Range("B9:C9").NumberFormat = "$#,##0_);($#,##0)" sheet2.Range("B9:C9").Formula = "=SUM(B3:B8)" sheet2.Range("B3:B8").NumberFormat = "$#,##0_);($#,##0)" sheet2.Range("C3:C8").NumberFormat = "0.00%" sheet2.Range("C3:C8").FormatConditions.AddDatabar() ' Set data source. sheet1.Range("A:B").ColumnWidthInPixel = 300 sheet1.Range("1:2").RowHeightInPixel = 200 sheet1.Range!A1.Value = record1 sheet1.Range!B1.Value = record2 sheet1.Range!A2.Value = record3 sheet1.Range!B2.Value = record4 ' Create a range template celltype Dim rangeTemplateCelltype = New RangeTemplateCellType(sheet2) ' Apply cell type to "A1:B2" sheet1.Range("A1:B2").CellType = rangeTemplateCelltype ' save to a pdf file workbook.Save("AddRangeTemplateCellType.pdf")