Use X++ to create formatted Excel Sheets with merged cells, color and borders

In case you need to create an excelsheet with data from AX, you'll want to copy this job below and run it, it will present you how to create borders, color fields and borders, merge cells, hide columns and fit them to the correct size.

static void JLH_ExcelBordersAndFormat(Args _args)
{

    SysExcelApplication     excelApplication;
    SysExcelWorkBook        workBook;
    SysExcelWorkSheet       workSheet;
    SysExcelWorksheets      worksheets;
    SysExcelCells           cells;

    // Borders
    COM                     range;
    COM                     borders, border;
    SysExcelRange           sysExcelRange;

    // Style
    SysExcelInterior        interior;
    SysExcelStyles          styles;
    SysExcelStyle           styleColumnHeaders;
    SysExcelFont            fontColumnHeaders;
 
    Object                  languageSettings;
    int                     languageId;
    Hwnd                    hwnd;
    #Excel
    ;


    startLengthyOperation();

    excelApplication        = SysExcelApplication::construct();
    // It seems fun to see the Excelsheet being build up, but clicking the sheet when being built up, will occasionally result in an error, so hide the application
    // until the sheet is completely finished.
    excelApplication.visible(false);
    workBook                = excelApplication.workbooks().add();
    workSheet               = workBook.worksheets().itemFromNum(1);
    languageSettings        = excelApplication.comObject().LanguageSettings();
    // Use the language from excel the enter language specific formulas
    languageId              = languageSettings.LanguageId(2);   //2 -> UI Language Id

    // Start filling some cells

    workSheet.cells().item(2, 2).value("Cell 1");
    workSheet.cells().item(3, 2).value("Cell 2");

    workSheet.cells().item(2, 4).value("Cell 3");
    workSheet.cells().item(3, 4).value("Cell 4");

    // Now we start to create some borders

    /* LEFT BORDER FOR ALL CELLS in the RANGE- Item (1)*/
    sysExcelRange       = worksheet.range('B6:C8');
    range               = sysExcelRange.comObject();

    // then we define the range for the borders
    borders = range.Borders();
    // Item(1) is the left border:
    border = borders.Item(1);
    // LineStyle can be 1 to 13, play around to find the style you like. 1 is the default line
    border.lineStyle(1);
    // Weight can be 1 to 4, this defines the thickness of the borderline, 2 is the default thickness
    border.Weight(2);

    workSheet.cells().item(6, 2).value("Item 1");
    workSheet.cells().item(7, 2).value("Item 1");
    workSheet.cells().item(8, 2).value("Item 1");
    workSheet.cells().item(6, 3).value("Item 1");
    workSheet.cells().item(7, 3).value("Item 1");
    workSheet.cells().item(8, 3).value("Item 1");

    /* RIGHT BORDER FOR ALL CELLS in the RANGE - Item(2)*/
    sysExcelRange       = worksheet.range('E6:F8');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(2);
    border.lineStyle(1);
    border.Weight(2);

    workSheet.cells().item(6, 5).value("Item 2");
    workSheet.cells().item(7, 5).value("Item 2");
    workSheet.cells().item(8, 5).value("Item 2");
    workSheet.cells().item(6, 6).value("Item 2");
    workSheet.cells().item(7, 6).value("Item 2");
    workSheet.cells().item(8, 6).value("Item 2");

    /* TOP BORDER FOR ALL CELLS in the RANGE - Item(3) */
    sysExcelRange       = worksheet.range('H6:I8');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(3);
    border.lineStyle(1);
    border.Weight(2);

    workSheet.cells().item(6, 8).value("Item 3");
    workSheet.cells().item(7, 8).value("Item 3");
    workSheet.cells().item(8, 8).value("Item 3");
    workSheet.cells().item(6, 9).value("Item 3");
    workSheet.cells().item(7, 9).value("Item 3");
    workSheet.cells().item(8, 9).value("Item 3");

    /* BOTTOM BORDER FOR ALL CELLS in the RANGE - Item(4)*/
    sysExcelRange       = worksheet.range('K6:L8');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(4);
    border.lineStyle(1);
    border.Weight(2);

    workSheet.cells().item(6, 11).value("Item 4");
    workSheet.cells().item(7, 11).value("Item 4");
    workSheet.cells().item(8, 11).value("Item 4");
    workSheet.cells().item(6, 12).value("Item 4");
    workSheet.cells().item(7, 12).value("Item 4");
    workSheet.cells().item(8, 12).value("Item 4");

    /* CROSS T-B INNER - Item(5)*/
    sysExcelRange       = worksheet.range('N6:O8');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(5);
    border.lineStyle(1);
    border.Weight(2);

    workSheet.cells().item(6, 14).value("Item 5");
    workSheet.cells().item(7, 14).value("Item 5");
    workSheet.cells().item(8, 14).value("Item 5");
    workSheet.cells().item(6, 15).value("Item 5");
    workSheet.cells().item(7, 15).value("Item 5");
    workSheet.cells().item(8, 15).value("Item 5");


    /* CROSS B-T INNER - Item (6)*/
    sysExcelRange       = worksheet.range('B10:C12');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(6);
    border.lineStyle(1);
    border.Weight(2);

    workSheet.cells().item(10, 2).value("Item 6");
    workSheet.cells().item(11, 2).value("Item 6");
    workSheet.cells().item(12, 2).value("Item 6");
    workSheet.cells().item(10, 3).value("Item 6");
    workSheet.cells().item(11, 3).value("Item 6");
    workSheet.cells().item(12, 3).value("Item 6");

    /* LEFT BORDER FOR RANGE - Item(7)*/
    sysExcelRange       = worksheet.range('E10:F12');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(7);
    border.lineStyle(1);
    border.Weight(2);

    workSheet.cells().item(10, 5).value("Item 7");
    workSheet.cells().item(11, 5).value("Item 7");
    workSheet.cells().item(12, 5).value("Item 7");
    workSheet.cells().item(10, 6).value("Item 7");
    workSheet.cells().item(11, 6).value("Item 7");
    workSheet.cells().item(12, 6).value("Item 7");

    /* TOP BORDER FOR RANGE - Item(8) */
    sysExcelRange       = worksheet.range('H10:I12');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(8);
    border.lineStyle(1);
    border.Weight(2);

    workSheet.cells().item(10, 8).value("Item 8");
    workSheet.cells().item(11, 8).value("Item 8");
    workSheet.cells().item(12, 8).value("Item 8");
    workSheet.cells().item(10, 9).value("Item 8");
    workSheet.cells().item(11, 9).value("Item 8");
    workSheet.cells().item(12, 9).value("Item 8");


    /* BOTTOM BORDER FOR RANGE - Item(9)*/
    sysExcelRange       = worksheet.range('K10:L12');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(9);
    border.lineStyle(1);
    border.Weight(2);

    workSheet.cells().item(10, 11).value("Item 9");
    workSheet.cells().item(11, 11).value("Item 9");
    workSheet.cells().item(12, 11).value("Item 9");
    workSheet.cells().item(10, 12).value("Item 9");
    workSheet.cells().item(11, 12).value("Item 9");
    workSheet.cells().item(12, 12).value("Item 9");

    /* RIGHT BORDER FOR RANGE - Item(10)*/
    sysExcelRange       = worksheet.range('N10:O12');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(10);
    border.lineStyle(1);
    border.Weight(2);

    workSheet.cells().item(10, 14).value("Item 10");
    workSheet.cells().item(11, 14).value("Item 10");
    workSheet.cells().item(12, 14).value("Item 10");
    workSheet.cells().item(10, 15).value("Item 10");
    workSheet.cells().item(11, 15).value("Item 10");
    workSheet.cells().item(12, 15).value("Item 10");
 


    /* INNER LEFT BORDER FOR RANGE - Item(11)*/
 
    sysExcelRange       = worksheet.range('B14:C16');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(11);
    border.lineStyle(1);
    border.Weight(2);

    workSheet.cells().item(14, 2).value("Item 11");
    workSheet.cells().item(15, 2).value("Item 11");
    workSheet.cells().item(16, 2).value("Item 11");
    workSheet.cells().item(14, 3).value("Item 11");
    workSheet.cells().item(15, 3).value("Item 11");
    workSheet.cells().item(16, 3).value("Item 11");

    /* INNER TOP BORDER FOR RANGE - Item(12) */
    sysExcelRange       = worksheet.range('E14:F16');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(12);
    border.lineStyle(1);
    border.Weight(2);

    workSheet.cells().item(14, 5).value("Item 12");
    workSheet.cells().item(15, 5).value("Item 12");
    workSheet.cells().item(16, 5).value("Item 12");
    workSheet.cells().item(14, 6).value("Item 12");
    workSheet.cells().item(15, 6).value("Item 12");
    workSheet.cells().item(16, 6).value("Item 12");
 
    // PUTTING IT ALL TOGETHER:
    /* COLORED ALL-BORDERS FOR RANGE*/
    sysExcelRange       = worksheet.range('H14:I16');
    range               = sysExcelRange.comObject();

    borders = range.Borders();
    border = borders.Item(1);
    border.lineStyle(1);
    border.color(WinApi::rgb2int(83, 141, 213));
    border.Weight(2);
    border = borders.Item(3);
    border.lineStyle(1);
    border.color(WinApi::rgb2int(83, 141, 213));
    border.Weight(2);
    border = borders.Item(9);
    border.lineStyle(1);
    border.color(WinApi::rgb2int(83, 141, 213));
    border.Weight(2);
    border = borders.Item(10);
    border.lineStyle(1);
    border.color(WinApi::rgb2int(83, 141, 213));
    border.Weight(2);

    workSheet.cells().item(14, 8).value("allborder");
    workSheet.cells().item(15, 8).value("allborder");
    workSheet.cells().item(16, 8).value("allborder");
    workSheet.cells().item(14, 9).value("allborder");
    workSheet.cells().item(15, 9).value("allborder");
    workSheet.cells().item(16, 9).value("allborder");
 
 
    // Add style
    styles              = workbook.styles();
    styleColumnHeaders  = styles.add('HeaderColumn');
    interior            = styleColumnHeaders.interior();
    interior.color(WinApi::rgb2int(54, 96, 146));

    fontColumnHeaders   = styleColumnHeaders.font();
    fontColumnHeaders.bold(true);
    fontColumnHeaders.color(WinApI::rgb2int(255, 255, 255));
    workSheet.cells().range('B18:C18').style('HeaderColumn');
    workSheet.cells().item(18, 2).value("Format");
    workSheet.cells().item(18, 3).value("Format");

    // Merge two fields
    workSheet.cells().item(18, 5).value("Merged");
    sysExcelRange   = workSheet.cells().range('E18:F18');
           sysExcelRange.comObject().MergeCells(1);
           SysExcelRange.horizontalAlignment(#xlCenter);

    // Hide column Q
    workSheet.cells().item(18, 16).value("Column Q is hidden");
    worksheet.columns().item(17).hidden(true);

    // Resize all columns:
    worksheet.columns().autoFit();

    endLengthyOperation();


    // Show the excelsheet
    excelApplication.visible(true);
    // And bring sheet to the front
    hwnd = WinAPI::findWindow(#XLMAIN, '');
    WinAPI::bringWindowToTop(hwnd);
    WinAPI::setForegroundWindow(hwnd);
}

Hide Column in Excel through X++

Something I really need to share since we could not find any reference on how to achieve the following: we create a new excel file and input a lot of data. Based on some parameters, columns need to be hidden. But how to achieve this? 

After creating a new worksheet, use the following line to hide the column. The number 26 indicates the column number: 

worksheet.columns().item(26).hidden(true);

Add leading zero's / characters

It's been over a year since my last update, ouch, but here is a note to myself. I needed to add leading zero's to strings which were too short. In order to do so, use the strRFix function. It takes your string, counts the number of characters and adds leading characters (zero's in my case).

I had different lengths of strings but I always needed six characters, filled with leading zero's. The outcome in the first place was: 
51070
4690
372

I needed six characters, which can be accomplished with the following command: 
strRFix(inputString,6,'0');

it will result in: 
051070
004690
000372

Perfect :).