// Create a new workbook Workbook workbook = new Workbook(); IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("B2:E8").setValue(new Object[][] { { "Number", "Text", "Logical", "Mix" }, { 100, "Apple", true, 100.5 }, { 200, "Banana", true, -12.34 }, { 300, "Cherry", false, "Text123" }, { 400, "\"Hello\"", true, 1000000 }, { 500, "Grape", false, "Special@Symbol" }, { 600, "Tom", true, 1.23e2 } }); worksheet.getTables().add(worksheet.getRange("B2:E8"), true); worksheet.getRange("G1").setValue("Concise format"); worksheet.getRange("G2").setValue("[format] = 0"); worksheet.getRange("G7").setValue("Strict format"); worksheet.getRange("G8").setValue("[format] = 1"); worksheet.getRange("H1").setValue("Formula"); worksheet.getRange("I1").setValue("Result"); worksheet.getRange("H2").setFormula2("=FORMULATEXT(I2)"); worksheet.getRange("I2").setFormula2("=ARRAYTOTEXT(B3:B8, 0)"); worksheet.getRange("H3").setFormula2("=FORMULATEXT(I3)"); worksheet.getRange("I3").setFormula2("=ARRAYTOTEXT(C3:C8, 0)"); worksheet.getRange("H4").setFormula2("=FORMULATEXT(I4)"); worksheet.getRange("I4").setFormula2("=ARRAYTOTEXT(D3:D8, 0)"); worksheet.getRange("H5").setFormula2("=FORMULATEXT(I5)"); worksheet.getRange("I5").setFormula2("=ARRAYTOTEXT(E3:E8, 0)"); worksheet.getRange("H7").setValue("Formula"); worksheet.getRange("I7").setValue("Result"); worksheet.getRange("H8").setFormula2("=FORMULATEXT(I8)"); worksheet.getRange("I8").setFormula2("=ARRAYTOTEXT(B3:B8, 1)"); worksheet.getRange("H9").setFormula2("=FORMULATEXT(I9)"); worksheet.getRange("I9").setFormula2("=ARRAYTOTEXT(C3:C8, 1)"); worksheet.getRange("H10").setFormula2("=FORMULATEXT(I10)"); worksheet.getRange("I10").setFormula2("=ARRAYTOTEXT(D3:D8, 1)"); worksheet.getRange("H11").setFormula2("=FORMULATEXT(I11)"); worksheet.getRange("I11").setFormula2("=ARRAYTOTEXT(E3:E8, 1)"); worksheet.getColumns().autoFit(); // Save to an excel file workbook.save("ArrayToTextFunction.xlsx");
// Create a new workbook var workbook = Workbook() val worksheet = workbook.worksheets[0] worksheet.getRange("B2:E8").value = arrayOf( arrayOf("Number", "Text", "Logical", "Mix"), arrayOf(100, "Apple", true, 100.5), arrayOf(200, "Banana", true, -12.34), arrayOf(300, "Cherry", false, "Text123"), arrayOf(400, "\"Hello\"", true, 1000000), arrayOf(500, "Grape", false, "Special@Symbol"), arrayOf(600, "Tom", true, 1.23e2) ) worksheet.tables.add(worksheet.getRange("B2:E8"), true) worksheet.getRange("G1").value = "Concise format" worksheet.getRange("G2").value = "[format] = 0" worksheet.getRange("G7").value = "Strict format" worksheet.getRange("G8").value = "[format] = 1" worksheet.getRange("H1").value = "Formula" worksheet.getRange("I1").value = "Result" worksheet.getRange("H2").formula2 = "=FORMULATEXT(I2)" worksheet.getRange("I2").formula2 = "=ARRAYTOTEXT(B3:B8, 0)" worksheet.getRange("H3").formula2 = "=FORMULATEXT(I3)" worksheet.getRange("I3").formula2 = "=ARRAYTOTEXT(C3:C8, 0)" worksheet.getRange("H4").formula2 = "=FORMULATEXT(I4)" worksheet.getRange("I4").formula2 = "=ARRAYTOTEXT(D3:D8, 0)" worksheet.getRange("H5").formula2 = "=FORMULATEXT(I5)" worksheet.getRange("I5").formula2 = "=ARRAYTOTEXT(E3:E8, 0)" worksheet.getRange("H7").value = "Formula" worksheet.getRange("I7").value = "Result" worksheet.getRange("H8").formula2 = "=FORMULATEXT(I8)" worksheet.getRange("I8").formula2 = "=ARRAYTOTEXT(B3:B8, 1)" worksheet.getRange("H9").formula2 = "=FORMULATEXT(I9)" worksheet.getRange("I9").formula2 = "=ARRAYTOTEXT(C3:C8, 1)" worksheet.getRange("H10").formula2 = "=FORMULATEXT(I10)" worksheet.getRange("I10").formula2 = "=ARRAYTOTEXT(D3:D8, 1)" worksheet.getRange("H11").formula2 = "=FORMULATEXT(I11)" worksheet.getRange("I11").formula2 = "=ARRAYTOTEXT(E3:E8, 1)" worksheet.columns.autoFit() // Save to an excel file workbook.save("ArrayToTextFunction.xlsx")