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));
}
}
}