IT Pro Ramblings

30 April 2010

Links List April 2010

I decided to share all my interesting reads and resources month by month with my blog readers. You can find these posts by searching on the label Links. I'll try to order the resources in logical categories. If you would like to see some interesting stuff added in the next month, don't hesitate to post a comment.

So this is my Links post for April 2010.

by Patrik Luca 0 comments

14 April 2010

Export to Excel with X++ code: the sequel

Introduction

This post is a sequel to my post Export to Excel with X++ code. In this post I add some formatting to my Excel export file.

autofit column width
working with styles to set fonts to bold
working with style to set font color
setting the format of a column

X++ code

Add following extra variables in the beginning of the job:

SysExcelStyles                  styles;
SysExcelStyle                   style;
SysExcelFont                    font;
SysExcelCells                   cells;

After having created the workbook, create a new style as follows:

styles = sysExcelWorkBook.styles(); 
// Create new style
style = styles.add('Header'); 
// Set font for this style to bold
font = style.font(); 
font.bold(true);
font.color(255);

Right after having created the title row on the first worksheet, set its style to our created Header style.

// Set the title row in the Header style
sysExcelWorksheet.rows().item(1).style('Header');

For each added worksheet with backorder lines, set the style of the title row also to our created Header style.

// Set the title row in the Header style
sysExcelWorksheetBackOrder.rows().item(1).style('Header');

Set the column width to autofit for the first worksheet.

// Set the column width to autofit
sysExcelWorksheet.columns().autoFit();

Set the column width to autofit for each added worksheet with backorder lines. Make sure you do this after you have added all rows in the worksheet, so it can take into account the width of the largest value in your column.

// Set the column width to autofit
 sysExcelWorksheetBackOrder.columns().autoFit();

Format the fourth column (Deliver Remainder) for each added worksheet with backorder lines as a Number with two decimal places.

// Format the 4th column to a Number with 2 decimal places
cells = sysExcelWorksheetBackOrder.cells();
cells.range('D2:D99').numberFormat("0,00");

The complete job looks then like this:


static void ExportToExcel(Args _args)
{
    #AviFiles
    SysOperationProgress    progress = new SysOperationProgress();
    SysExcelApplication     sysExcelApplication;
    SysExcelWorkbooks       sysExcelWorkBooks;
    // Filename to which you will be writing your data
    FileName                fileName = "C:\\Windows\\Temp\\ExportToExcel.xlsx";
    SysExcelWorkbook        sysExcelWorkBook;
    SysExcelWorkSheets      sysExcelWorkSheets;
    SysExcelWorkSheet       sysExcelWorkSheet;
    SysExcelWorkSheet       sysExcelWorksheetBackOrder;
    SysExcelWorksheet       sysExcelWorkSheetToBeDeleted;
    SysExcelStyles          styles;
    SysExcelStyle           style;
    SysExcelFont            font;
    SysExcelCells           cells;
    int                     row = 1;
    int                     rowBackOrder;
    CustTable               custTable;
    SalesTable              salesTable;
    SalesLine               salesLine;
    boolean                 workSheetAdded = false;
    int                     nbrOfCustomers;
    ;

    // Initialising progress bar
    progress.setCaption("Export To Excel in progress...");
    progress.setAnimation(#AviTransfer);
    // Initialisation of some objects
    sysExcelApplication = SysExcelApplication::construct();
    // Create new workbook
    sysExcelWorkBooks = sysExcelApplication.workbooks();
    sysExcelWorkBook = sysExcelWorkBooks.add();
    // Create new style
    styles = sysExcelWorkBook.styles();
    style = styles.add("Header");
    // Set font for this style to bold and color to red
    font = style.font();
    font.bold(true);
    font.color(255);
    // Get worksheets collection
    sysExcelWorkSheets = sysExcelWorkbook.worksheets();
    // Excel visible on desktop running the job or not?
    sysExcelApplication.visible(false);
    // Newly created Excel files have by default some worksheets
    // Delete those worksheets created by default
    while(sysExcelWorkSheets.count() > 1)
    {
        sysExcelWorkSheetToBeDeleted = sysExcelWorkSheets.itemFromNum(2);
        sysExcelWorkSheetToBeDeleted.delete();
    }
    // Add as many worksheets as there are customers
    select count(RecId) from CustTable;
    sysExcelWorkSheet = sysExcelWorkSheets.add(null,null,CustTable.RecId);
    // Add another worksheet
    sysExcelWorkSheet = sysExcelWorkSheets.add();
    //Rename the first worksheet
    sysExcelWorkSheet.name("Customers");
    // Make a title row
    // set a value in cell on row 1 column 1
    sysExcelWorkSheet.cells().item(1,1).value("Customer account");
    // set a value in cell on row 1 column 2
    sysExcelWorksheet.cells().item(1,2).value("Name");
    // set the title row in the Header style
    sysExcelWorksheet.rows().item(1).style("Header");
    while select custTable
    {
        progress.setText(strfmt("Customer %1", custTable.Name));
        row++;
        rowBackOrder = 1;
        sysExcelWorksheet.cells().item(row,1).value(custTable.AccountNum);
        sysExcelWorksheet.cells().item(row,2).value(custTable.Name);
        while select salesLine
        where salesLine.SalesStatus             == salesStatus::Backorder
        && salesLine.ConfirmedDlv               < Today()
        && salesLine.RemainSalesPhysical        > 0
        join salesTable
        where salesTable.SalesId                == salesLine.SalesId &&
        salesTable.CustAccount                  == custTable.AccountNum
        {
            if(!workSheetAdded)
            {
                // Use the next Excel worksheet and rename it
                sysExcelWorksheetBackOrder = sysExcelWorkSheets.itemFromNum(
                    row);
                //Name of worksheet can have maximum 31 characters
                sysExcelWorksheetBackOrder.name(substr(custTable.Name,1,31));
                workSheetAdded = true;
                // Make a title row
                // set a value in cell on row 1 column 1
                sysExcelWorksheetBackOrder.cells().item(1,1).value(
                    "Ship Date");
                // set a value in cell on row 1 column 2
                sysExcelWorksheetBackOrder.cells().item(1,2).value(
                    "Item Number");
                // set a value in cell on row 1 column 3
                sysExcelWorksheetBackOrder.cells().item(1,3).value(
                    "Item Name");
                // set a value in cell on row 1 column 4
                sysExcelWorksheetBackOrder.cells().item(1,4).value(
                    "Deliver Remainder");
                // set the title row in the Header style
                sysExcelWorksheetBackOrder.rows().item(1).style("Header");
            }
            rowBackOrder++;
            sysExcelWorksheetBackOrder.cells().item(rowBackOrder,1).value(
                salesLine.ConfirmedDlv);
            sysExcelWorksheetBackOrder.cells().item(rowBackOrder,2).value(
                salesLine.ItemId);
            sysExcelWorksheetBackOrder.cells().item(rowBackOrder,3).value(
                InventTable::find(salesLine.ItemId).ItemName);
            sysExcelWorksheetBackOrder.cells().item(rowBackOrder,4).value(
                salesLine.RemainSalesPhysical);
        }
        // Set the column width to autofit
        sysExcelWorksheetBackOrder.columns().autoFit();
        // Format the 4th column as Number with 2 decimal places
        cells = sysExcelWorksheetBackOrder.cells();
        cells.range("D2:D99").numberFormat("0,00");
        workSheetAdded = false;
    }
    // Set the column width to autoFit
    sysExcelWorksheet.columns().autoFit();
    // Suppress the pop-up window:
    // A file named foo already exists in this location. Do you want to replace it?
    sysExcelApplication.displayAlerts(false);
    // Save the Excel file
    sysExcelWorkbook.saveAs(fileName);
    sysExcelWorkBook.comObject().save();
    sysExcelWorkBook.saved(true);
    // Make sure you close the Excel application
    // Especially if you run the job without showing Excel on the desktop
    // (sysExcelApplication.visible(false))
    sysExcelApplication.quit();

}

The result should look like this if you execute the job on the demo data in Microsoft Dynamics AX 2009 with Microsoft Excel 2007:

This is the first worksheet in my Excel workbook with the complete list of my customers in it.

This is one of the other worksheets, being the one created for customer Forest Wholesales and showing all the backorder lines for this particular customer.

by Patrik Luca 21 comments

05 April 2010

Fill Utility: some remarks

The Fill Utility is a handy new tool since Dynamics AX 2009 with which you can update multiple records at the same time. However in some cases, you’ll notice it isn’t available.

by Patrik Luca 0 comments

X++ Code Snippet: Copying records

This X++ Code Snippet post describes how you can easily copy records by running over all of the fields of the from-record. During this looping over all the fields, you can exclude some fields if needed.

X++ Code Snippet: Copying records

Create following method in a class:

static void CopyFields(Common _from, Common _to) 
{  
    DictTable           t; 
    DictField           f;
    int                 i;  
    fieldId             id;
    FieldName           fieldname;  
    ;

    t = new DictTable(_to.TableId);
    if (t)
    {
        for (i=1; i<=t.fieldCnt(); i++)
        {
            id = t.fieldCnt2Id(i);
            f  = new DictField(_to.TableId,t.fieldCnt2Id(i));
// excluding system fields (or add other fields to exclude during
// the copy action
            if (f && !f.isSystem())
            {
                _to.(id) = _from.(id);
            }
        }
    }
}

To copy for example a CustTrans to a new one, just call your class like this:

YourClass::CopyFields(custTrans, custTransTo);
custTransTo.insert();

by Patrik Luca 7 comments

04 April 2010

Incrementing Date in Repeating Table

Business Requirement: Incrementing Date in Repeating Table

The business requirement of this post covers the following topic: each time the user adds a line in a Repeating Table on an InfoPath form, the Date Field of the line should increment by one day automatically.

An example is elaborated in this post with the by default installed Expense Report Sample. Each time an Expense line is added, the Date of the Expense line will be incremented with one day.