// Create a new workbook Workbook workbook = new Workbook(); IWorksheet worksheet = workbook.getActiveSheet(); Object[][] objects = new Object[][] { {"Day", "Rain (in.)"}, {1, 1}, {2, 0.8}, {3, 0.2}, {4, CalcError.NA}, {5, 0.6}, {6, 0.8}, {7, 0.8 } }; worksheet.getRange("A1:B8").setValue(objects); // Set style. worksheet.getRange("A1:B1").getInterior().setColor(Color.FromArgb(142, 169, 219)); worksheet.getRange("A2:B8").getInterior().setColor(Color.FromArgb(217, 225, 242)); worksheet.getRange("A1:B8").getBorders().setLineStyle(BorderLineStyle.Thin); // Add chart. IShape shape = worksheet.getShapes().addChart(ChartType.Line, 150, 0, 250, 200); shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:B8"), RowCol.Columns,true,true); // Set show blank as zero. shape.getChart().setDisplayBlanksAs(DisplayBlanksAs.Zero); shape.getChart().getChartTitle().setText("DisplayNaAsBlank:False"); // Copy the chart. worksheet.getRange("D1:I14").copy(worksheet.getRange("J1")); // Set show #N/A as empty cell. shape.getChart().setDisplayNaAsBlank(true); shape.getChart().getChartTitle().setText("DisplayNaAsBlank:True"); // Save to an excel file workbook.save("DisplayNaAsBlank.xlsx");
// Create a new workbook var workbook = Workbook() val worksheet: IWorksheet = workbook.getActiveSheet() val objects = arrayOf(arrayOf("Day", "Rain (in.)"), arrayOf(1, 1), arrayOf(2, 0.8), arrayOf(3, 0.2), arrayOf(4, CalcError.NA), arrayOf(5, 0.6), arrayOf(6, 0.8), arrayOf(7, 0.8)) worksheet.getRange("A1:B8").setValue(objects) // Set style. worksheet.getRange("A1:B1").getInterior().setColor(Color.FromArgb(142, 169, 219)) worksheet.getRange("A2:B8").getInterior().setColor(Color.FromArgb(217, 225, 242)) worksheet.getRange("A1:B8").getBorders().setLineStyle(BorderLineStyle.Thin) //Add chart. val shape: IShape = worksheet.getShapes().addChart(ChartType.Line, 150.0, 0.0, 250.0, 200.0) shape.getChart().getSeriesCollection().add(worksheet.getRange("A1:B8"), RowCol.Columns, true, true) // Set show blank as zero. shape.getChart().setDisplayBlanksAs(DisplayBlanksAs.Zero) shape.getChart().getChartTitle().setText("DisplayNaAsBlank:False") // Copy the chart. worksheet.getRange("D1:I14").copy(worksheet.getRange("J1")) // Set show #N/A as empty cell. shape.getChart().setDisplayNaAsBlank(true) shape.getChart().getChartTitle().setText("DisplayNaAsBlank:True") // Save to an excel file workbook.save("DisplayNaAsBlank.xlsx")