// Create a new workbook Workbook workbook = new Workbook(); //set worksheet layout and data IWorksheet worksheet = workbook.getWorksheets().get(0); worksheet.getRange("A:A").setColumnWidth(2); worksheet.getRange("B:C").setColumnWidth(15); worksheet.getRange("D:F").setColumnWidth(25); worksheet.getRange("4:13").setRowHeight(57); worksheet.getRange("B3").setValue("Type"); worksheet.getRange("C3").setValue("Data"); worksheet.getRange("B2").setValue("Barcode"); worksheet.getRange("B2:F2").setMergeCells(true); worksheet.getRange("D3:F3").setValue(new Object[][] { {"Default", "Change showLabel", "Change labelPosition"} }); worksheet.getPageSetup().setPrintTitleColumns("$A:$C"); worksheet.getRange("B4:C13").setHorizontalAlignment(HorizontalAlignment.Center); worksheet.getRange("B4:C13").setVerticalAlignment(VerticalAlignment.Center); worksheet.getRange("B2:F3").setHorizontalAlignment(HorizontalAlignment.Center); worksheet.getRange("B2:F3").setVerticalAlignment(VerticalAlignment.Center); worksheet.getRange("B4:C13").setValue(new Object[][] { {"QR code", "Policy:411"}, {"Data Matrix", "Policy:411"}, {"PDF417", "6935205311092"}, {"EAN-8", "4137962"}, {"EAN-13", "6920312296219"}, {"Code39", "3934712708295"}, {"Code93", "6945091701532"}, {"Code128", "465465145645"}, {"Codabar", "9787560044231"}, {"gs1128", "010123456789012815051231"} }); String[] types = {"BC_QRCODE", "BC_DataMatrix", "BC_PDF417", "BC_EAN8", "BC_EAN13", "BC_CODE39", "BC_CODE93", "BC_CODE128", "BC_CODABAR", "BC_GS1_128"}; worksheet.getPageSetup().setPrintGridlines(true); //use formula to add barcode for (int i = 0; i < types.length; i++) { String columnD = "D" + (i + 4); worksheet.getRange(columnD).setFormula("=" + types[i] + "(C" + (i + 4) + ")"); } for (int i = 3; i < types.length; i++) { String columnE = "E" + (i + 4); String columnF = "F" + (i + 4); worksheet.getRange(columnE).setFormula("=" + types[i] + "(C" + (i + 4) + ",,,0)"); worksheet.getRange(columnF).setFormula("=" + types[i] + "(C" + (i + 4) + ",,,,\"top\")"); } // Convert all barcode formula results to pictures. workbook.convertBarcodeToPicture(); // Save to an excel file workbook.save("ConvertBarcodetoPicture.xlsx");
// Create a new workbook var workbook = Workbook() //set worksheet layout and data val worksheet = workbook.worksheets.get(0) worksheet.getRange("A:A").columnWidth = 2.0 worksheet.getRange("B:C").columnWidth = 15.0 worksheet.getRange("D:F").columnWidth = 25.0 worksheet.getRange("4:14").rowHeight = 57.0 worksheet.getRange("B3").value = "Type" worksheet.getRange("C3").value = "Data" worksheet.getRange("B2").value = "Barcode" worksheet.getRange("B2:F2").mergeCells = true worksheet.getRange("D3:F3").value = arrayOf(arrayOf("Default", "Change showLabel", "Change labelPosition")) worksheet.pageSetup.printTitleColumns = "\$A:\$C" worksheet.getRange("B4:C14").horizontalAlignment = HorizontalAlignment.Center worksheet.getRange("B4:C14").verticalAlignment = VerticalAlignment.Center worksheet.getRange("B2:F3").horizontalAlignment = HorizontalAlignment.Center worksheet.getRange("B2:F3").verticalAlignment = VerticalAlignment.Center worksheet.getRange("B4:C14").value = arrayOf(arrayOf("QR code", "Policy:411"), arrayOf("Data Matrix", "Policy:411"), arrayOf("PDF417", "6935205311092"), arrayOf("EAN-8", "4137962"), arrayOf("EAN-13", "6920312296219"), arrayOf("Code39", "3934712708295"), arrayOf("Code93", "6945091701532"), arrayOf("Code49", "6901668002433"), arrayOf("Code128", "465465145645"), arrayOf("Codabar", "9787560044231"), arrayOf("gs1128", "010123456789012815051231")) val types = arrayOf("BC_QRCODE", "BC_DataMatrix", "BC_PDF417", "BC_EAN8", "BC_EAN13", "BC_CODE39", "BC_CODE93", "BC_CODE49", "BC_CODE128", "BC_CODABAR", "BC_GS1_128") worksheet.pageSetup.printGridlines = true //use formula to add barcode for (i in types.indices) { val columnD = "D" + (i + 4) worksheet.getRange(columnD).formula = "=" + types[i] + "(C" + (i + 4) + ")" } for (i in 3 until types.size) { val columnE = "E" + (i + 4) val columnF = "F" + (i + 4) worksheet.getRange(columnE).formula = "=" + types[i] + "(C" + (i + 4) + ",,,0)" worksheet.getRange(columnF).formula = "=" + types[i] + "(C" + (i + 4) + ",,,,\"top\")" } // Convert all barcode formula results to pictures. workbook.convertBarcodeToPicture() // Save to an excel file workbook.save("ConvertBarcodetoPicture.xlsx")