hi all,
Can anyone help me with the procedure of how to export data into excel
hi all,
Can anyone help me with the procedure of how to export data into excel
Hi,
In Administration → Periodic—>**Data export/**import---->Excel speardsheets—>Template wizard
In this template wizard ---->Next—>Open workbook(create an excel file with it)—>next—>Available objects(tables) and move your required tables to Selected objects—>Next—>Select the fields(if required)—>Create Import definition group—>Check Export data and Finish…
with regards,
harivalla[Y]
hi hari,
thanks for your reply…
i want the the coding procedure…any example?
Hi Jasmine,
Use the code below…
static void ExportToExcel1(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;
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();
// 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”);
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”);
}
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);
}
workSheetAdded = false;
}
// 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();
}
Regards,
Madhan