28 September 2010

Write EXCEL Document, export data to excel with X++

static void CreateExcelDocument (Args _args)
{
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorkBook xlsWorkBook;
SysExcelWorkSheets xlsWorkSheetCollection;
SysExcelWorkSheet xlsWorkSheet;
SysExcelRange xlsRange;
CustTable custTable;
int row = 1;
str fileName;
;

// Name of the Excel document.
fileName = "C:\\test.xslx";

// Excel open and initialize.
xlsApplication = SysExcelApplication:: construct ();
xlsApplication.visible (true);

// Create an Excel Worksheet produce.
xlsWorkBookCollection = xlsApplication.workbooks();
xlsWorkBook = xlsWorkBookCollection.add();
xlsWorkSheetCollection = xlsWorkBook.worksheets();
xlsWorkSheet = xlsWorkSheetCollection.itemFromNum (1);




// Write to the worksheet cells headings.
xlsWorkSheet.cells (). item (row, 1). value ('Account Number');
xlsWorkSheet.cells (). item (row,2). value ('name');

row ++;

// Excel Worksheet with data fill / (Excel cells fill).
while select custTable
{
xlsWorkSheet.cells (). item (row, 1). value (custTable.AccountNum);
xlsWorkSheet.cells (). item (row, 2). value (custTable.Name);
row ++;
}

// Check whether the document already exists.
if (WINAPI:: fileExists (fileName))
{
Winapi:: DeleteFile (fileName);
}

// Save Excel document.
xlsWorkbook.saveAs(fileName);

// Close Excel.
xlsApplication.quit ();
xlsApplication.finalize ();
}

3 comments:

  1. Hi,
    there is another approach to export data to excel using the clipboard to paste the data into the file.
    My experience is that the code above is more stable but slower while handling large amounts of data. Just take a look at the code and decide for yourself in which business case what approach works better

    PurchTable purch
    SysExcelApplication excel;
    SysExcelWorkBooks books;
    SysExcelWorkbook book;
    SysExcelWorkSheets sheets;
    SysExcelWorkSheet sheet;

    Com com;
    Textbuffer tb;
    ;
    excel = SysExcelApplication::contruct();
    books = excel.workbooks();
    book = books.add();
    sheets = book.workSheets();
    sheet = sheets.itemFromNum(1);
    com = sheet.comObject()M

    tb = new TextBuffer();
    tb.setText("Purchase Order ID \t VendorAccount \n");

    while select purch
    {
    tb.setText(tb.getText() + strFmt("%1 \t %2 \n", purch.PurchId, purch.OrderAccount));
    }
    tb.toClipboard();
    com.paste();
    excel.visible(true);

    ReplyDelete
  2. TO be more precise even no need to declare so many class instances..Just declare only SysExcelApplication use it as below in single shot.

    excel = SysExcelApplication::construct();
    com = excel.workbooks().add().worksheets().itemFromNum(1).comObject();

    ReplyDelete
  3. How to transfer (ItemImage table) images excel ?

    ReplyDelete

Give me the commetns and solutions

Ledger Voucher creation Framework and x++ code to create ledger voucher

 Please click her for MS reference file Below is the out of the box example reference and code. SalesInvoiceJournalPostSubBill_Extension->...