Hi,
I need to export and import the data to excel and from excel files respectively
through X++ CODE …
pls help me guys…
Hello Chaitanya,
Create a class .
class CreateExcelFile
{
}
public static void main(Args args)
{
CustTable custTable;
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
int row;
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
workbook = workbooks.add();
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
cells.range(‘A:A’).numberFormat(’@’);
while select custTable
{
row++;
cell = cells.item(row, 1);
cell.value(custTable.AccountNum);
cell = cells.item(row, 2);
cell.value(custTable.Name);
}
application.visible(true);
}
hi rajkumar,
it is fine while i am exporting but am not getting the column names at the top of excel columns…
also please send code for excel data import … i tried many ways but i am not getting the output…
Hi ,
This is for read the execl file .
class ReadExcelFile
{
}
public static void main(Args args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row;
CustAccount account;
CustName name;
#define.Filename(’\customers.xlsx’)
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
try
{
workbooks.open(#Filename);
}
catch (Exception::Error)
{
throw error(“File cannot be opened.”);
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
do
{
row++;
account = cells.item(row, 1).value().bStr();
name = cells.item(row, 2).value().bStr();
info(strfmt(’%1 - %2’, account, name));
type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
Hi,
For import data from excel first convert it into .csv file then follow this X++ job:
static void loadCSVFile(Args _args)
{
Dialog dialog;
DialogField dialogFileName;
SysOperationProgress simpleProgress;
Filename filename;
FileIOPermission permission;
TextIO textIO;
NumberSeq numSeq;
InventTable inventTable;
str s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12;
Real r1,r2,r3,r4,r5,r6;
int i1,i2,i3,i4,i5,i6,i;
Addressing a1,a2,a3;
TransDate d1,d2,d3;
salesTable salesTable,salesTable1,salesTable2;
salesLine salesLine,salesLine1,salesLine2,salesLine3,salesLine4;
InventDim inventDim,inventDim1,inventDim2;
Container c1,c2;
CompanyInfo companyInfoLoc = CompanyInfo::find();
Container filterCriteria;
#File
#avifiles
;
dialog = new Dialog(“Importing Text File”);
dialogFileName = dialog.addField(typeid(Filenameopen), “File Name”);
filterCriteria = [’*.txt’];
filterCriteria = dialog.filenameLookupFilter(filterCriteria);
dialog.run();
if (dialog.run())
filename = dialogFileName.value();
if(!filename)
{
info(“Filename must be filled”);
throw("");
}
permission = new fileIOpermission(filename,#io_read);
permission.assert();
textIO = new TextIO(filename,#io_read);
textIO.inFieldDelimiter(’;’);///Change the Delimeter if it is , or ; etc
simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, ‘Importing sales data’,100);
if(textIO)
{
while(textIO.status() == IO_Status::Ok)
{
c1 = textIO.read();
s11 = conpeek(c1,2);
if(strlen(s11) > 1)
{
numSeq = numberSeq::newGetNum(SalesParameters::numRefSalesId());
salesTable.SalesId = numSeq.num();///SalesId
salesTable.initValue();
salestable.CustAccount = Conpeek(c1,1);// Cust Account
salesTable.InvoiceAccount = Conpeek(c1,1);//Cust Account
salestable.SalesId = salestable.SalesId;
salesTable.initFromCustTable();
salestable.ShippingDateRequested = systemdateget();
salesTable.ShippingDateConfirmed = systemdateget();
salesTable.insert();
salesLine.SalesId = salesTable.SalesId;
salesLine.initFromSalesTable(SalesTable);
salesLine.initValue();
salesLine.ItemId = Conpeek(c1,2);//ItemId
inventTable=salesLine.inventTable();
salesLine.initFromInventTable(inventTable);
inventDim1.InventSiteId = Conpeek(c1,3);//// InventSiteId
inventDim1.InventLocationId = Conpeek(c1,4);//// InventLocationId
salesLine.inventDimId = InventDim::findOrCreate(inventDim1).inventDimId;
salesLine.initFromCustTable();
salesLine.SalesQty = conpeek(c1,5);//Sales Quantity
salesLine.QtyOrdered = conpeek(c1,5);// Sales Quantity
salesLine.SalesPrice = conpeek(c1,6);//Sales Price
salesLine.LineAmount = salesLine.SalesPrice * salesLine.SalesQty;//Sales Amount
salesLine.AssessableValue_IN = salesLine.LineAmount;
salesLine.Address_IN = companyInfoLoc.Address;
salesLine.ExciseType_IN = companyInfoLoc.ExciseType_IN;
salesLine.TIN_IN = companyInfoLoc.TIN_IN;
salesLine.ECCNumber_IN = companyInfoLoc.ECCNumber_IN;
salesLine.IECNumber_IN = companyInfoLoc.IECNumber_IN;
salesLine.STCNumber_IN = companyInfoLoc.STCNumber_IN;
salesLine.SalesTaxRegistrationNumber_IN = companyInfoLoc.SalesTaxRegistrationNumber_IN;
salesLine.TAN_IN = companyInfoLoc.TAN_IN;
salesLine.State_IN = companyInfoLoc.State;
salesLine.RemainSalesPhysical = salesLine.SalesQty;
salesLine.RemainSalesFinancial = 0;
salesLine.RemainInventPhysical = salesLine.QtyOrdered;
salesLine.LineNum = SalesLine::lastLineNum(salesLine.SalesId) + 1.0;
salesLine.insert();
i++;
simpleProgress.incCount();
simpleprogress.setText(strfmt(“Lines imported: %1”, i));
info(strfmt(“Sales Order : %1 has been created”,salesTable.SalesId));
sleep(10);
}
}
}
}
hi dhananjay,
tnks for ur quick reply, but how to convert an excel file with data to csv format …
and do u have any idea of importing the data into ax tables from excel files … without
using CSV format…
Hi Chaitanya,
Open your excel file then go to file>save as>saveastype>CSV(comma delimited) save it.
So this file you need to upload.
If you want to see this file then open as notepad.
Best Regards,
Dhananjay Verma