Excel API Library for Java - 튜토리얼 | Document Solutions

>( arrayOf("ITEM", "AMOUNT"), arrayOf("Income 1", 2500), arrayOf("Income 2", 1000), arrayOf("Income 3", 250), arrayOf("Other", 250) ) ) worksheet.getRange("B10:C23").setValue( arrayOf>( arrayOf("ITEM", "AMOUNT"), arrayOf("Rent/mortgage", 800), arrayOf("Electric", 120), arrayOf("Gas", 50), arrayOf("Cell phone", 45), arrayOf("Groceries", 500), arrayOf("Car payment", 273), arrayOf("Auto expenses", 120), arrayOf("Student loans", 50), arrayOf("Credit cards", 100), arrayOf("Auto Insurance", 78), arrayOf("Personal care", 50), arrayOf("Entertainment", 100), arrayOf("Miscellaneous", 50) ) ) worksheet.getRange("B2:C2").merge() worksheet.getRange("B2").setValue("MONTHLY INCOME") worksheet.getRange("B9:C9").merge() worksheet.getRange("B9").setValue("MONTHLY EXPENSES") worksheet.getRange("E2:G2").merge() worksheet.getRange("E2").setValue("PERCENTAGE OF INCOME SPENT") worksheet.getRange("E5:G5").merge() worksheet.getRange("E5").setValue("SUMMARY") worksheet.getRange("E3:F3").merge() worksheet.getRange("E9").setValue("BALANCE") worksheet.getRange("E6").setValue("Total Monthly Income") worksheet.getRange("E7").setValue("Total Monthly Expenses") ``` 행 높이 및 열 너비 설정 행 높이와 열 너비를 사용자 정의하고 레이아웃 및 데이터 프레젠테이션을 설정합니다. "StandardHeight" 및 "StandardWidth"를 사용하여 워크시트의 기본 행 높이와 열 너비를 설정할 수 있습니다. Java ```java worksheet.setStandardHeight(26.25); worksheet.setStandardWidth(8.43); worksheet.getRange("2:24").setRowHeight(27); worksheet.getRange("A:A").setColumnWidth(2.855); worksheet.getRange("B:B").setColumnWidth(33.285); worksheet.getRange("C:C").setColumnWidth(25.57); worksheet.getRange("D:D").setColumnWidth(1); worksheet.getRange("E:F").setColumnWidth(25.57); worksheet.getRange("G:G").setColumnWidth(14.285); ``` Kotlin ```kotlin worksheet.setStandardHeight(26.25) worksheet.setStandardWidth(8.43) worksheet.getRange("2:24").setRowHeight(27.0) worksheet.getRange("A:A").setColumnWidth(2.855) worksheet.getRange("B:B").setColumnWidth(33.285) worksheet.getRange("C:C").setColumnWidth(25.57) worksheet.getRange("D:D").setColumnWidth(1.0) worksheet.getRange("E:F").setColumnWidth(25.57) worksheet.getRange("G:G").setColumnWidth(14.285) ``` 표 만들기 두 개의 표("수입" 및 "비용")를 추가하고 각 표에 기본 표 스타일을 적용합니다. Java ```java ITable incomeTable = worksheet.getTables().add(worksheet.getRange("B3:C7"), true); incomeTable.setName("tblIncome"); incomeTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4")); ITable expensesTable = worksheet.getTables().add(worksheet.getRange("B10:C23"), true); expensesTable.setName("tblExpenses"); expensesTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4")); ``` Kotlin ```kotlin val incomeTable = worksheet.getTables().add(worksheet.getRange("B3:C7"), true) incomeTable.setName("tblIncome") incomeTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4")) val expensesTable = worksheet.getTables().add(worksheet.getRange("B10:C23"), true) expensesTable.setName("tblExpenses") expensesTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4")) ``` 함수 설정 사용자 정의 이름 2개를 만들어 월수입과 비용을 할당하고 월수입 합계, 월 비용 합계, 수입 대비 지출 백분율, 잔액을 계산하는 함수를 추가합니다. Java ```java worksheet.getNames().add("TotalMonthlyIncome", "=SUM(tblIncome[AMOUNT])"); worksheet.getNames().add("TotalMonthlyExpenses", "=SUM(tblExpenses[AMOUNT])"); worksheet.getRange("E3").setFormula("=TotalMonthlyExpenses"); worksheet.getRange("G3").setFormula("=TotalMonthlyExpenses/TotalMonthlyIncome"); worksheet.getRange("G6").setFormula("=TotalMonthlyIncome"); worksheet.getRange("G7").setFormula("=TotalMonthlyExpenses"); worksheet.getRange("G9").setFormula("=TotalMonthlyIncome-TotalMonthlyExpenses"); ``` Kotlin ```kotlin worksheet.getNames().add("TotalMonthlyIncome", "=SUM(tblIncome[AMOUNT])") worksheet.getNames().add("TotalMonthlyExpenses", "=SUM(tblExpenses[AMOUNT])") worksheet.getRange("E3").setFormula("=TotalMonthlyExpenses") worksheet.getRange("G3").setFormula("=TotalMonthlyExpenses/TotalMonthlyIncome") worksheet.getRange("G6").setFormula("=TotalMonthlyIncome") worksheet.getRange("G7").setFormula("=TotalMonthlyExpenses") worksheet.getRange("G9").setFormula("=TotalMonthlyIncome-TotalMonthlyExpenses") ``` 스타일 설정 범위의 스타일을 변경하는 방법은 두 가지입니다. 기본 제공 스타일 또는 사용자 정의 스타일을 이름으로 적용 각 요소에 개별 스타일 설정 "통화", "제목 1", "백분율" 기본 제공 스타일을 정의하여 셀 범위에 적용합니다. 다른 범위에 적용할 개별 스타일 요소도 정의합니다. Java ```java IStyle currencyStyle = workbook.getStyles().get("Currency"); currencyStyle.setIncludeAlignment(true); currencyStyle.setVerticalAlignment(VerticalAlignment.Bottom); currencyStyle.setNumberFormat("$#,##0.00"); IStyle heading1Style = workbook.getStyles().get("Heading 1"); heading1Style.setIncludeAlignment(true); heading1Style.setHorizontalAlignment(HorizontalAlignment.Center); heading1Style.setVerticalAlignment(VerticalAlignment.Center); heading1Style.getFont().setName("Century Gothic"); heading1Style.getFont().setBold(true); heading1Style.getFont().setSize(11); heading1Style.getFont().setColor(Color.GetWhite()); heading1Style.setIncludeBorder(false); heading1Style.setIncludePatterns(true); heading1Style.getInterior().setColor(Color.FromArgb(32, 61, 64)); IStyle percentStyle = workbook.getStyles().get("Percent"); percentStyle.setIncludeAlignment(true); percentStyle.setHorizontalAlignment(HorizontalAlignment.Right); percentStyle.setIncludeFont(true); percentStyle.getFont().setColor(Color.FromArgb(32, 61, 64)); percentStyle.getFont().setName("Century Gothic"); percentStyle.getFont().setBold(true); percentStyle.getFont().setSize(14); worksheet.getSheetView().setDisplayGridlines(false); worksheet.getRange("C4:C7, C11:C23, G6:G7, G9").setStyle(currencyStyle); worksheet.getRange("B2, B9, E2, E5").setStyle(heading1Style); worksheet.getRange("G3").setStyle(percentStyle); worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Medium); worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.FromArgb(32, 61, 64)); worksheet.getRange("E7:G7").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Medium); worksheet.getRange("E7:G7").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.FromArgb(32, 61, 64)); worksheet.getRange("E9:G9").getInterior().setColor(Color.FromArgb(32, 61, 64)); worksheet.getRange("E9:F9").setHorizontalAlignment(HorizontalAlignment.Left); worksheet.getRange("E9:G9").setVerticalAlignment(VerticalAlignment.Center); worksheet.getRange("E9:G9").getFont().setName("Century Gothic"); worksheet.getRange("E9:G9").getFont().setBold(true); worksheet.getRange("E9:G9").getFont().setSize(11); worksheet.getRange("E9:G9").getFont().setColor(Color.GetWhite()); worksheet.getRange("E3:F3").getBorders().setColor(Color.FromArgb(32, 61, 64)); ``` Kotlin ```kotlin val currencyStyle = workbook.getStyles().get("Currency") currencyStyle.setIncludeAlignment(true) currencyStyle.setVerticalAlignment(VerticalAlignment.Bottom) currencyStyle.setNumberFormat("$#,##0.00") val heading1Style = workbook.getStyles().get("Heading 1") heading1Style.setIncludeAlignment(true) heading1Style.setHorizontalAlignment(HorizontalAlignment.Center) heading1Style.setVerticalAlignment(VerticalAlignment.Center) heading1Style.getFont().setName("Century Gothic") heading1Style.getFont().setBold(true) heading1Style.getFont().setSize(11.0) heading1Style.getFont().setColor(Color.GetWhite()) heading1Style.setIncludeBorder(false) heading1Style.setIncludePatterns(true) heading1Style.getInterior().setColor(Color.FromArgb(32, 61, 64)) val percentStyle = workbook.getStyles().get("Percent") percentStyle.setIncludeAlignment(true) percentStyle.setHorizontalAlignment(HorizontalAlignment.Right) percentStyle.setIncludeFont(true) percentStyle.getFont().setColor(Color.FromArgb(32, 61, 64)) percentStyle.getFont().setName("Century Gothic") percentStyle.getFont().setBold(true) percentStyle.getFont().setSize(14.0) worksheet.getSheetView().setDisplayGridlines(false) worksheet.getRange("C4:C7, C11:C23, G6:G7, G9").setStyle(currencyStyle) worksheet.getRange("B2, B9, E2, E5").setStyle(heading1Style) worksheet.getRange("G3").setStyle(percentStyle) worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Medium) worksheet.getRange("E6:G6").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.FromArgb(32, 61, 64)) worksheet.getRange("E7:G7").getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Medium) worksheet.getRange("E7:G7").getBorders().get(BordersIndex.EdgeBottom).setColor(Color.FromArgb(32, 61, 64)) worksheet.getRange("E9:G9").getInterior().setColor(Color.FromArgb(32, 61, 64)) worksheet.getRange("E9:F9").setHorizontalAlignment(HorizontalAlignment.Left) worksheet.getRange("E9:G9").setVerticalAlignment(VerticalAlignment.Center) worksheet.getRange("E9:G9").getFont().setName("Century Gothic") worksheet.getRange("E9:G9").getFont().setBold(true) worksheet.getRange("E9:G9").getFont().setSize(11.0) worksheet.getRange("E9:G9").getFont().setColor(Color.GetWhite()) worksheet.getRange("E3:F3").getBorders().setColor(Color.FromArgb(32, 61, 64)) ``` 조건부 서식 추가 Document Solutions for Excel은 모든 유형의 조건부 서식을 지원합니다. 그라데이션 데이터 막대 서식으로 수입 대비 지출 백분율을 표시할 수 있습니다. 이 규칙은 값을 표시하지 않고 데이터 막대만을 표시합니다. Java ```java IDataBar dataBar = worksheet.getRange("E3").getFormatConditions().addDatabar(); dataBar.getMinPoint().setType(ConditionValueTypes.Number); dataBar.getMinPoint().setValue(1); dataBar.getMaxPoint().setType(ConditionValueTypes.Number); dataBar.getMaxPoint().setValue("=TotalMonthlyIncome"); dataBar.setBarFillType(DataBarFillType.Gradient); dataBar.getBarColor().setColor(Color.GetRed()); dataBar.setShowValue(false); ``` Kotlin ```kotlin val dataBar = worksheet.getRange("E3").getFormatConditions().addDatabar() dataBar.getMinPoint().setType(ConditionValueTypes.Number) dataBar.getMinPoint().setValue(1) dataBar.getMaxPoint().setType(ConditionValueTypes.Number) dataBar.getMaxPoint().setValue("=TotalMonthlyIncome") dataBar.setBarFillType(DataBarFillType.Gradient) dataBar.getBarColor().setColor(Color.GetRed()) dataBar.setShowValue(false) ``` 차트 추가 세로 막대형 차트를 만들어 수입과 비용의 차이를 비교합니다. 계열 겹침과 간격 너비를 변경하고 차트 영역, 축 선, 눈금 레이블, 데이터 요소 등 몇 가지 차트 요소의 서식을 사용자 정의하여 레이아웃을 만들수 있습니다. Java ```java IShape shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 339, 247, 316.5, 346); shape.getChart().getChartArea().getFormat().getLine().setTransparency(1); shape.getChart().getColumnGroups().get(0).setOverlap(0); shape.getChart().getColumnGroups().get(0).setGapWidth(37); IAxis category_axis = shape.getChart().getAxes().item(AxisType.Category); category_axis.getFormat().getLine().getColor().setRGB(Color.GetBlack()); category_axis.getTickLabels().getFont().setSize(11); category_axis.getTickLabels().getFont().getColor().setRGB(Color.GetBlack()); IAxis series_axis = shape.getChart().getAxes().item(AxisType.Value); series_axis.getFormat().getLine().setWeight(1); series_axis.getFormat().getLine().getColor().setRGB(Color.GetBlack()); series_axis.getTickLabels().setNumberFormat("$###0"); series_axis.getTickLabels().getFont().setSize(11); series_axis.getTickLabels().getFont().getColor().setRGB(Color.GetBlack()); ISeries chartSeries = shape.getChart().getSeriesCollection().newSeries(); chartSeries.setFormula("=SERIES(\"Simple Budget\",{\"Income\",\"Expenses\"},'Sheet1'!$G$6:$G$7,1)"); chartSeries.getPoints().get(0).getFormat().getFill().getColor().setRGB(Color.FromArgb(176, 21, 19)); chartSeries.getPoints().get(1).getFormat().getFill().getColor().setRGB(Color.FromArgb(234, 99, 18)); chartSeries.getDataLabels().getFont().setSize(11); chartSeries.getDataLabels().getFont().getColor().setRGB(Color.GetBlack()); chartSeries.getDataLabels().setShowValue(true); chartSeries.getDataLabels().setPosition(DataLabelPosition.OutsideEnd); ``` Kotlin ```kotlin val shape = worksheet.getShapes().addChart(ChartType.ColumnClustered, 339.0, 247.0, 3 shape.getChart().getChartArea().getFormat().getLine().setTransparency(1.0) shape.getChart().getColumnGroups().get(0).setOverlap(0) shape.getChart().getColumnGroups().get(0).setGapWidth(37) val category_axis = shape.getChart().getAxes().item(AxisType.Category) category_axis.getFormat().getLine().getColor().setRGB(Color.GetBlack()) category_axis.getTickLabels().getFont().setSize(11.0) category_axis.getTickLabels().getFont().getColor().setRGB(Color.GetBlack()) val series_axis = shape.getChart().getAxes().item(AxisType.Value) series_axis.getFormat().getLine().setWeight(1.0) series_axis.getFormat().getLine().getColor().setRGB(Color.GetBlack()) series_axis.getTickLabels().setNumberFormat("$###0") series_axis.getTickLabels().getFont().setSize(11.0) series_axis.getTickLabels().getFont().getColor().setRGB(Color.GetBlack()) val chartSeries = shape.getChart().getSeriesCollection().newSeries() chartSeries.setFormula("=SERIES(\"Simple Budget\",{\"Income\",\"Expenses\"},'Sheet1'! chartSeries.getPoints().get(0).getFormat().getFill().getColor().setRGB(Color.FromArgb chartSeries.getPoints().get(1).getFormat().getFill().getColor().setRGB(Color.FromArgb chartSeries.getDataLabels().getFont().setSize(11.0) chartSeries.getDataLabels().getFont().getColor().setRGB(Color.GetBlack()) chartSeries.getDataLabels().setShowValue(true) chartSeries.getDataLabels().setPosition(DataLabelPosition.OutsideEnd) ``` Excel로 저장 "SimpleBudget.xlsx"라는 이름으로 Excel 파일로 저장합니다. Java ```java workbook.save("SimpleBudget.xlsx"); ``` Kotlin ```kotlin workbook.save("SimpleBudget.xlsx") ``` 최종 결과물은 아래의 경로에서 SimpleBudget.xlsx확인하여 볼수 있습니다.듀토리얼 소스 프로젝트를 다운로드하고 코드를 직접 실행하고 싶으신 경우에는 경우 먼저 JAVA가 컴퓨터에 설치되어 있어야 합니다."/>

("ITEM", "AMOUNT"), arrayOf("Income 1", 2500), arrayOf("Income 2", 1000), arrayOf("Income 3", 250), arrayOf("Other", 250) ) val sourceData1 = arrayOf( arrayOf("ITEM", "AMOUNT"), arrayOf("Rent/mortgage", 800), arrayOf("Electricity", 120), arrayOf("Gas", 50), arrayOf("Cell phone", 45), arrayOf("Groceries", 500), arrayOf("Car payment", 273), arrayOf("Auto expenses", 120), arrayOf("Student loans", 50), arrayOf("Credit cards", 100), arrayOf("Auto insurance", 78), arrayOf("Personal care", 50), arrayOf("Entertainment", 100), arrayOf("Miscellaneous", 50) ) val worksheet: IWorksheet = workbook.getWorksheets().get(0) worksheet.getRange("B3:C7").value = sourceData worksheet.getRange("B10:C23").value = sourceData1 worksheet.name = "Tables" worksheet.getRange("B2:C2").merge() worksheet.getRange("B2").value = "MONTHLY INCOME" worksheet.getRange("B9:C9").merge() worksheet.getRange("B9").value = "MONTHLY EXPENSES" worksheet.getRange("E2:G2").merge() worksheet.getRange("E2").value = "PERCENTAGE OF INCOME SPENT" worksheet.getRange("E5:G5").merge() worksheet.getRange("E5").value = "SUMMARY" worksheet.getRange("E3:F3").merge() worksheet.getRange("E9").value = "BALANCE" worksheet.getRange("E6").value = "Total Monthly Income" worksheet.getRange("E7").value = "Total Monthly Expenses" //--------------------------------Set Height & Width-------------------------------- worksheet.standardHeight = 26.25 worksheet.standardWidth = 8.43 worksheet.getRange("2:24").rowHeight = 27.0 worksheet.getRange("A:A").columnWidth = 2.855 worksheet.getRange("B:B").columnWidth = 33.285 worksheet.getRange("C:C").columnWidth = 25.57 worksheet.getRange("D:D").columnWidth = 1.0 worksheet.getRange("E:F").columnWidth = 25.57 worksheet.getRange("G:G").columnWidth = 14.285 //------------------------------Set Table-------------------------------------- // Create the first table to show Income val incomeTable: ITable = worksheet.tables.add(worksheet.getRange("B3:C7"), true) incomeTable.name = "tblIncome" incomeTable.tableStyle = workbook.tableStyles.get("TableStyleMedium4") // Create the second table to show Expenses val expensesTable: ITable = worksheet.tables.add(worksheet.getRange("B10:C23"), true) expensesTable.name = "tblExpenses" expensesTable.tableStyle = workbook.tableStyles.get("TableStyleMedium4") //------------------------------Set Formulas----------------------------------- worksheet.names.add("TotalMonthlyIncome", "=SUM(tblIncome[AMOUNT])") worksheet.names.add("TotalMonthlyExpenses", "=SUM(tblExpenses[AMOUNT])") worksheet.getRange("E3").formula = "=TotalMonthlyExpenses" worksheet.getRange("G3").formula = "=TotalMonthlyExpenses/TotalMonthlyIncome" worksheet.getRange("G6").formula = "=TotalMonthlyIncome" worksheet.getRange("G7").formula = "=TotalMonthlyExpenses" worksheet.getRange("G9").formula = "=TotalMonthlyIncome-TotalMonthlyExpenses" //----------------------------Set Styles------------------------- val currencyStyle: IStyle = workbook.styles.get("Currency") currencyStyle.includeAlignment = true currencyStyle.horizontalAlignment = HorizontalAlignment.Left currencyStyle.verticalAlignment = VerticalAlignment.Bottom currencyStyle.numberFormat = "$#,##0.00" val heading1Style: IStyle = workbook.styles.get("Heading 1") heading1Style.includeAlignment = true heading1Style.horizontalAlignment = HorizontalAlignment.Center heading1Style.verticalAlignment = VerticalAlignment.Center heading1Style.includeFont = true heading1Style.font.name = "Century Gothic" heading1Style.font.bold = true heading1Style.font.size = 11.0 heading1Style.font.color = Color.GetWhite() heading1Style.includeBorder = false heading1Style.includePatterns = true heading1Style.interior.color = Color.FromArgb(255, 32, 61, 64) val percentStyle: IStyle = workbook.styles.get("Percent") percentStyle.includeAlignment = true percentStyle.horizontalAlignment = HorizontalAlignment.Center percentStyle.includeFont = true percentStyle.font.color = Color.FromArgb(255, 32, 61, 64) percentStyle.font.name = "Century Gothic" percentStyle.font.bold = true percentStyle.font.size = 14.0 worksheet.getSheetView().setDisplayGridlines(false) worksheet.getRange("C4:C7, C11:C23, G6:G7, G9").style = currencyStyle worksheet.getRange("B2, B9, E2, E5").style = heading1Style worksheet.getRange("G3").style = percentStyle worksheet.getRange("E6:G6").borders.get(BordersIndex.EdgeBottom).lineStyle = BorderLineStyle.Medium worksheet.getRange("E6:G6").borders.get(BordersIndex.EdgeBottom).color = Color.FromArgb(255, 32, 61, 64) worksheet.getRange("E7:G7").borders.get(BordersIndex.EdgeBottom).lineStyle = BorderLineStyle.Medium worksheet.getRange("E7:G7").borders.get(BordersIndex.EdgeBottom).color = Color.FromArgb(255, 32, 61, 64) worksheet.getRange("E9:G9").interior.color = Color.FromArgb(255, 32, 61, 64) worksheet.getRange("E9:G9").horizontalAlignment = HorizontalAlignment.Left worksheet.getRange("E9:G9").verticalAlignment = VerticalAlignment.Center worksheet.getRange("E9:G9").font.name = "Century Gothic" worksheet.getRange("E9:G9").font.bold = true worksheet.getRange("E9:G9").font.size = 11.0 worksheet.getRange("E9:G9").font.color = Color.GetWhite() worksheet.getRange("E3:F3").borders.color = Color.FromArgb(255, 32, 61, 64) //----------------------------Set Conditional Format------------------------- val dataBar: IDataBar = worksheet.getRange("E3").formatConditions.addDatabar() dataBar.minPoint.type = ConditionValueTypes.Number dataBar.minPoint.value = 1 dataBar.maxPoint.type = ConditionValueTypes.Number dataBar.maxPoint.value = "=TotalMonthlyIncome" dataBar.barFillType = DataBarFillType.Gradient dataBar.barColor.color = Color.GetRed() dataBar.showValue = false //--------------------------------Create pivot table-------------------------------- val worksheet2: IWorksheet = workbook.worksheets.add() worksheet2.name = "Pivot Table" sourceData = arrayOf( arrayOf("Order ID", "Product", "Category", "Amount", "Date", "Country"), arrayOf(1, "Carrots", "Vegetables", 4270, GregorianCalendar(2022, 9, 6), "United States"), arrayOf(2, "Broccoli", "Vegetables", 8239, GregorianCalendar(2022, 8, 7), "United States"), arrayOf(3, "Banana", "Fruit", 617, GregorianCalendar(2022, 10, 18), "United States"), arrayOf(4, "Banana", "Fruit", 8384, GregorianCalendar(2022, 11, 10), "Canada"), arrayOf(5, "Beans", "Vegetables", 2626, GregorianCalendar(2022, 10, 10), "Germany"), arrayOf(6, "Orange", "Fruit", 3610, GregorianCalendar(2022, 11, 11), "United States"), arrayOf(7, "Broccoli", "Vegetables", 9062, GregorianCalendar(2022, 10, 11), "Australia"), arrayOf(8, "Banana", "Fruit", 6906, GregorianCalendar(2022, 10, 16), "New Zealand"), arrayOf(9, "Apple", "Fruit", 2417, GregorianCalendar(2022, 11, 16), "France"), arrayOf(10, "Apple", "Fruit", 7431, GregorianCalendar(2022, 11, 16), "Canada"), arrayOf(11, "Banana", "Fruit", 8250, GregorianCalendar(2022, 10, 16), "Germany"), arrayOf(12, "Broccoli", "Vegetables", 7012, GregorianCalendar(2022, 10, 18), "United States"), arrayOf(13, "Carrots", "Vegetables", 1903, GregorianCalendar(2022, 11, 20), "Germany"), arrayOf(14, "Broccoli", "Vegetables", 2824, GregorianCalendar(2022, 9, 24), "Canada"), arrayOf(15, "Apple", "Fruit", 6946, GregorianCalendar(2022, 11, 24), "France") ) worksheet2.getRange("A1:F16").value = sourceData worksheet2.getRange("A:F").columnWidth = 15.0 val pivotCache: IPivotCache = workbook.pivotCaches.create(worksheet2.getRange("A1:F16")) val pivotTable: IPivotTable = worksheet2.pivotTables.add(pivotCache, worksheet2.getRange("H7"), "pivotTable1") //--------------------------------Configure pivot table's fields-------------------------------- val fieldCategory: IPivotField = pivotTable.pivotFields.get("Category") fieldCategory.orientation = PivotFieldOrientation.RowField val fieldProduct: IPivotField = pivotTable.pivotFields.get("Product") fieldProduct.orientation = PivotFieldOrientation.ColumnField val fieldAmount: IPivotField = pivotTable.pivotFields.get("Amount") fieldAmount.orientation = PivotFieldOrientation.DataField fieldAmount.numberFormat = "$#,##0" val fieldCountry: IPivotField = pivotTable.pivotFields.get("Country") fieldCountry.orientation = PivotFieldOrientation.PageField //--------------------------------Add chart-------------------------------- val worksheet3: IWorksheet = workbook.worksheets.add() worksheet3.name = "Chart" val shape: IShape = worksheet3.shapes.addChart(ChartType.ColumnClustered, 300.0, 10.0, 300.0, 300.0) shape.chart.chartTitle.text = "Sales Increases Over Previous Quarter" worksheet3.getRange("A1:D6").value = arrayOf( arrayOf(null, "Q1", "Q2", "Q3"), arrayOf("Belgium", 10, 25, 25), arrayOf("France", -51, -36, 27), arrayOf("Greece", 52, -85, -30), arrayOf("Italy", 22, 65, 65), arrayOf("UK", 23, 69, 69) ) shape.chart.seriesCollection.add(worksheet3.getRange("A1:D6"), RowCol.Columns, true, true) worksheet3.getRange("B1:D1").horizontalAlignment = HorizontalAlignment.Right worksheet3.getRange("B1:D1").font.bold = true worksheet3.getRange("B2:D6").numberFormat = "€#,##0" val valueAxis: IAxis = shape.chart.axes.item(AxisType.Value) valueAxis.tickLabels.numberFormat = "€#,##0" workbook.save("Tutorial.xlsx")