Hi Everyone,
I am new to Dynamics Ax, i want to create an Excel file and then check if the file already exists, if it exists then append data to it. I tried writting data to Excel file and it worked but how to do "append" data to Excel file using Dynamics ax X++(through code).
Can anyone please tell me how to do it. Thanks.
Best Regards
Shah
Keeping it as simple as possible, this Job will write to an existing Excel sheet.
Filename in variable fileName
Row to write data to in variable excelRow
Share
Report
All responses (
Answers (
static void RSH003_AppendExcel(Args _args)
{
// Variables for Dialog
FileName fileName = @'C:\Test.xlsx';
// Variabler til Excel
SysExcelApplication sysExcelApplication;
SysExcelWorksheet sysExcelWorksheet;
SysExcelWorksheets sysExcelWorksheets;
SysExcelWorkbooks sysExcelWorkbooks;
SysExcelWorkbook sysExcelWorkbook;
SysExcelCell sysExcelCell;
counter excelRow;
// Open the Excel document
sysExcelApplication = SysExcelApplication::construct();
sysExcelWorkbooks = sysExcelApplication.workbooks();
//specify the file path that you want to read
try
{
sysExcelWorkbooks.open(filename,0,false);
}
catch (Exception::Error)
{
throw error("File cannot be opened.");
}
sysExcelWorkbook = sysExcelWorkbooks.item(1);
sysExcelWorksheets = sysExcelWorkbook.worksheets();
sysExcelWorksheet = sysExcelWorksheets.itemFromNum(1);
excelRow = 2;
SysExcelCell = SysExcelWorksheet.cells().item(excelRow,1);
SysExcelCell.value("Col1");
SysExcelCell = SysExcelWorksheet.cells().item(excelRow,2);
SysExcelCell.value("Col2");
SysExcelCell = SysExcelWorksheet.cells().item(excelRow,3);
SysExcelCell.value("Col3");
// Save the Excel file
sysExcelApplication.displayAlerts(false);
sysExcelWorkbook.saveAs(fileName);
sysExcelWorkBook.saved(true);
sysExcelApplication.quit();
}
Hi Rudi,
Yes, there was Problem with file opening but it was just a variable mistake. Can you plesae provide some Example code for appending data to Excel file. if you have it. or if you have tried it. Thanks
Best Regards
Shah
I think your problem is with the exportDataToExcel method.
First of all when i try this code it fails on opening the Excel document.
But the main problem i think is in the line:
xlsWorkBook = xlsWorkBookCollection.add();
I think you are adding an new WorkBook here, and then you have a blank workbook you are writing your data in.
Here is the Code, Function "createExcelFile" creates the Excel file with headings , then "exportDataToExcel" reopens the Excel file and tries to append new record to the Excel file and saves it. " this.findLastRow();" fucntion return the last row from the existing Excel document to write the new record in row.
Best Regards
Shah
public void createExcelFile()
{
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorkBook xlsWorkBook;
SysExcelWorkSheets xlsWorkSheetCollection;
SysExcelWorkSheet xlsWorkSheet;
SysExcelRange xlsRange;
InventTable inventTable;
int row = 1;
str fileName;
System.Exception ex;
try
{
// 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 ('Depot PostCode');
xlsWorkSheet.cells (). item (row,2). value ('To PostCode');
xlsWorkSheet.cells (). item (row,3). value ('Distance in Meters');
row ++;
// Save Excel document.
xlsWorkbook.saveAs(fileName);
// Close Excel.
xlsApplication.quit ();
xlsApplication.finalize ();
}
catch(Exception::Error)
{
info(strFmt("%1", Exception::Error));
}
}
public void exportDataToExcel()
{
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorkBook xlsWorkBook;
SysExcelWorkSheets xlsWorkSheetCollection;
SysExcelWorkSheet xlsWorkSheet;
SysExcelRange xlsRange;
InventTable inventTable;
int row;
str fileName;
System.Exception ex;
if (WINAPI:: fileExists ("C:\\test.xslx"))
{
row = this.findLastRow();
try
{
// Name of the Excel document.
xlsWorkBookCollection.open("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);
row ++;
// Excel Worksheet with data fill / (Excel cells fill).
xlsWorkSheet.cells (). item (row, 1). value ("Test Actual");
xlsWorkSheet.cells (). item (row, 2). value ("Test VIrtual");
xlsWorkSheet.cells (). item (row, 3). value ("Test VIrtual 3");
// row ++;
// Check whether the document already exists.
// Save Excel document.
xlsWorkbook.saveAs("C:\\test.xslx");
// Close Excel.
xlsApplication.quit ();
xlsApplication.finalize ();
}
catch(Exception::Error)
{
info(strFmt("%1", Exception::Error));
}
}
}
Are you able to share the code you use for this?
The bacis process for doing this would be to open the existing Excel sheet, then append the new data to it and save it again.
Hi Rudi,
Thaks for your reply, What i am doing is, i create a Excel file with heading and save it to disk. then i get a record and i want to insert it in to that Excel file, if i save data as using "SaveAs" method it replaces the current Excel file and the headings are removed , what i want is the data from file should not be removed and new data is inserted after the last record in the file.
Best Regards
Shah
You have already written data to an excel file, then you just have to open an existing file and write to it like before.
There is no append to excel file function like with text files.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,252
Super User 2024 Season 2
#2
Martin Dráb
228,089
Super User 2024 Season 2
#3
nmaenpaa
101,148