// Create a new workbook Workbook workbook = new Workbook(); //public class CustomObjectJsonSerializer implements IJsonSerializer { // Gson gson = new Gson(); // public final Object deserialize(String json) { // return this.json.fromJson(json, JsonElement.class); // } // // public final String serialize(Object value) { // return this.json.toJson(value); // } //} //public class PersonalAssets { // public String name; // public int savings; // public int shares; // public int stocks; // public int house; // public int bonds; // public int car; //} Workbook.setValueJsonSerializer(new CustomObjectJsonSerializer()); IWorksheet sheet1 = workbook.getActiveSheet(); IWorksheet sheet2 = workbook.getWorksheets().add(); PersonalAssets record1 = new PersonalAssets(); record1.name = "Peyton"; record1.savings = 25000; record1.shares = 55000; record1.stocks = 15000; record1.house = 250000; record1.bonds = 11000; record1.car = 7500; PersonalAssets record2 = new PersonalAssets(); record2.name = "Icey"; record2.savings = 30000; record2.shares = 45000; record2.stocks = 25000; record2.house = 20000; record2.bonds = 18000; record2.car = 75000; PersonalAssets record3 = new PersonalAssets(); record3.name = "Walter"; record3.savings = 20000; record3.shares = 4000; record3.stocks = 95000; record3.house = 30000; record3.bonds = 10000; record3.car = 56000; PersonalAssets record4 = new PersonalAssets(); record4.name = "Chris"; record4.savings = 70000; record4.shares = 85000; record4.stocks = 35000; record4.house = 20000; record4.bonds = 15000; record4.car = 45000; // Set binding path for cell. sheet2.getRange("A1:C1").merge(); sheet2.getRange("A1:C1").setHorizontalAlignment(HorizontalAlignment.Center); sheet2.getRange("A1:C1").setVerticalAlignment(VerticalAlignment.Center); sheet2.getRange("A1").setBindingPath("name"); sheet2.getRange("A1").getFont().setName("Arial"); sheet2.getRange("A1").getFont().setSize(15); sheet2.getRange("1:1").setRowHeight(30); sheet2.getRange("A2").setValue("Asset Type"); sheet2.getRange("B2").setValue("Amount"); sheet2.getRange("C2").setValue("Rate"); sheet2.getRange("A3").setValue("Savings"); sheet2.getRange("A3").getInterior().setColor(Color.FromArgb(145, 159, 129)); sheet2.getRange("B3").setBindingPath("savings"); sheet2.getRange("C3").setFormula("=B3/B9"); sheet2.getRange("A4").setValue("Shares"); sheet2.getRange("A4").getInterior().setColor(Color.FromArgb(215, 145, 62)); sheet2.getRange("B4").setBindingPath("shares"); sheet2.getRange("C4").setFormula("=B4/B9"); sheet2.getRange("A5").setValue("Stocks"); sheet2.getRange("A5").getInterior().setColor(Color.FromArgb(206, 167, 34)); sheet2.getRange("B5").setBindingPath("stocks"); sheet2.getRange("C5").setFormula("=B5/B9"); sheet2.getRange("A6").setValue("House"); sheet2.getRange("A6").getInterior().setColor(Color.FromArgb(181, 128, 145)); sheet2.getRange("B6").setBindingPath("house"); sheet2.getRange("C6").setFormula("=B6/B9"); sheet2.getRange("A7").setValue("Bonds"); sheet2.getRange("A7").getInterior().setColor(Color.FromArgb(137, 116, 169)); sheet2.getRange("B7").setBindingPath("bonds"); sheet2.getRange("C7").setFormula("=B7/B9"); sheet2.getRange("A8").setValue("Car"); sheet2.getRange("A8").getInterior().setColor(Color.FromArgb(114, 139, 173)); sheet2.getRange("B8").setBindingPath("car"); sheet2.getRange("C8").setFormula("=B8/B9"); sheet2.getRange("A9").setValue("Total"); sheet2.getRange("B9:C9").merge(); sheet2.getRange("B9:C9").setHorizontalAlignment(HorizontalAlignment.Center); sheet2.getRange("B9:C9").setNumberFormat("$#,##0_);($#,##0)"); sheet2.getRange("B9:C9").setFormula("=SUM(B3:B8)"); sheet2.getRange("B3:B8").setNumberFormat("$#,##0_);($#,##0)"); sheet2.getRange("C3:C8").setNumberFormat("0.00%"); sheet2.getRange("C3:C8").getFormatConditions().addDatabar(); // Set data source. sheet1.getRange("A:B").setColumnWidthInPixel(300); sheet1.getRange("1:2").setRowHeightInPixel(200); sheet1.getRange("A1").setValue(record1); sheet1.getRange("B1").setValue(record2); sheet1.getRange("A2").setValue(record3); sheet1.getRange("B2").setValue(record4); // Create a range template celltype RangeTemplateCellType rangeTemplateCelltype = new RangeTemplateCellType(sheet2); // Apply cell type to "A1:B2" sheet1.getRange("A1:B2").setCellType(rangeTemplateCelltype); // Save to an pdf file workbook.save("AddRangeTemplateCellType.pdf");
// Create a new workbook var workbook = Workbook() //public class CustomObjectJsonSerializer implements IJsonSerializer { // Gson gson = new Gson(); // public final Object deserialize(String json) { // return this.json.fromJson(json, JsonElement.class); // } // // public final String serialize(Object value) { // return this.json.toJson(value); // } //} //public class PersonalAssets { // public String name; // public int savings; // public int shares; // public int stocks; // public int house; // public int bonds; // public int car; //} Workbook.setValueJsonSerializer(CustomObjectJsonSerializer()) val sheet1 = workbook.activeSheet val sheet2 = workbook.worksheets.add() val record1 = PersonalAssets() record1.name = "Peyton" record1.savings = 25000 record1.shares = 55000 record1.stocks = 15000 record1.house = 250000 record1.bonds = 11000 record1.car = 7500 val record2 = PersonalAssets() record2.name = "Icey" record2.savings = 30000 record2.shares = 45000 record2.stocks = 25000 record2.house = 20000 record2.bonds = 18000 record2.car = 75000 val record3 = PersonalAssets() record3.name = "Walter" record3.savings = 20000 record3.shares = 4000 record3.stocks = 95000 record3.house = 30000 record3.bonds = 10000 record3.car = 56000 val record4 = PersonalAssets() record4.name = "Chris" record4.savings = 70000 record4.shares = 85000 record4.stocks = 35000 record4.house = 20000 record4.bonds = 15000 record4.car = 45000 // Set binding path for cell. sheet2.getRange("A1:C1").merge() sheet2.getRange("A1:C1").horizontalAlignment = HorizontalAlignment.Center sheet2.getRange("A1:C1").verticalAlignment = VerticalAlignment.Center sheet2.getRange("A1").bindingPath = "name" sheet2.getRange("A1").font.name = "Arial" sheet2.getRange("A1").font.size = 15.0 sheet2.getRange("1:1").rowHeight = 30.0 sheet2.getRange("A2").value = "Asset Type" sheet2.getRange("B2").value = "Amount" sheet2.getRange("C2").value = "Rate" sheet2.getRange("A3").value = "Savings" sheet2.getRange("A3").interior.color = Color.FromArgb(145, 159, 129) sheet2.getRange("B3").bindingPath = "savings" sheet2.getRange("C3").formula = "=B3/B9" sheet2.getRange("A4").value = "Shares" sheet2.getRange("A4").interior.color = Color.FromArgb(215, 145, 62) sheet2.getRange("B4").bindingPath = "shares" sheet2.getRange("C4").formula = "=B4/B9" sheet2.getRange("A5").value = "Stocks" sheet2.getRange("A5").interior.color = Color.FromArgb(206, 167, 34) sheet2.getRange("B5").bindingPath = "stocks" sheet2.getRange("C5").formula = "=B5/B9" sheet2.getRange("A6").value = "House" sheet2.getRange("A6").interior.color = Color.FromArgb(181, 128, 145) sheet2.getRange("B6").bindingPath = "house" sheet2.getRange("C6").formula = "=B6/B9" sheet2.getRange("A7").value = "Bonds" sheet2.getRange("A7").interior.color = Color.FromArgb(137, 116, 169) sheet2.getRange("B7").bindingPath = "bonds" sheet2.getRange("C7").formula = "=B7/B9" sheet2.getRange("A8").value = "Car" sheet2.getRange("A8").interior.color = Color.FromArgb(114, 139, 173) sheet2.getRange("B8").bindingPath = "car" sheet2.getRange("C8").formula = "=B8/B9" sheet2.getRange("A9").value = "Total" sheet2.getRange("B9:C9").merge() sheet2.getRange("B9:C9").horizontalAlignment = HorizontalAlignment.Center sheet2.getRange("B9:C9").numberFormat = "$#,##0_);($#,##0)" sheet2.getRange("B9:C9").formula = "=SUM(B3:B8)" sheet2.getRange("B3:B8").numberFormat = "$#,##0_);($#,##0)" sheet2.getRange("C3:C8").numberFormat = "0.00%" sheet2.getRange("C3:C8").formatConditions.addDatabar() // Set data source. sheet1.getRange("A:B").columnWidthInPixel = 300.0 sheet1.getRange("1:2").rowHeightInPixel = 200.0 sheet1.getRange("A1").value = record1 sheet1.getRange("B1").value = record2 sheet1.getRange("A2").value = record3 sheet1.getRange("B2").value = record4 // Create a range template celltype val rangeTemplateCelltype = RangeTemplateCellType(sheet2) // Apply cell type to "A1:B2" sheet1.getRange("A1:B2").cellType = rangeTemplateCelltype // Save to an pdf file workbook.save("AddRangeTemplateCellType.pdf")