How To Import From Excel Using X++?
---------------------CODE-----------------------------------
public void ImportVendor()
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
System.DateTime ShlefDate;
FilenameOpen filename;
dialogField dialogFilename;
Dialog dialog;
//Table Declarations Starts
VendTable _VendTable;
DirPartyTable dirPartyTab;
Address addTab;
//Table Declartions Ends
VendAccount AccNum= "50000";
int iAccNum= 10000;
int row=0;
VendName vendorName;
Addressing adress;
Phone phone;
TeleFax teleFax;
VendGroupId vGroupId;
smmSegmentId segmentId;
VendCurrencyCode currency;
AddressCity city;
AddressStreet street;
VendTableAlias nameAlias;
#Excel
// convert into str from excel cell value
str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}
;
dialog = new Dialog("Excel Upoad");
dialogFilename = dialog.addField(typeId(FilenameOpen));
dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
dialog.filenameLookupTitle("Upload from Excel");
dialog.caption("Excel Upload");
dialogFilename.value(filename);
if(!dialog.run())
return;
filename = dialogFilename.value();
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
{
try
{
ttsbegin;
row++;
AccNum = COMVariant2Str(cells.item(row,1).value())+"_Ora";
vendorName = COMVariant2Str(cells.item(row,2).value());
adress = COMVariant2Str(cells.item(row,5).value())+" "+COMVariant2Str(cells.item(row,6).value())+" "+ COMVariant2Str(cells.item(row,7).value());
city = COMVariant2Str(cells.item(row, 8).value());
phone = COMVariant2Str(cells.item(row, 14).value());
teleFax = COMVariant2Str(cells.item(row,16).value());
if(row > 1)
{
//Insert into _VendTable Table
select firstonly _VendTable where _VendTable.Name == vendorName ;
if(!_VendTable)
{
_VendTable.initValue();
_VendTable.AccountNum = AccNum;
_VendTable.Name = vendorName;
_VendTable.Address = adress;
_VendTable.Phone = phone;
_VendTable.VendGroup ="MOH";
_VendTable.SegmentId="COM";
_VendTable.insert();
this.UpdateVendAddressType(AccNum);
this.UpdateVendAddressName(AccNum);
}
else
{
warning(strfmt("Vendor already exists %1 : ",_VendTable.Name));
}
// Insert into InventBatch Table
// Insert into InventSerial Table
// info(strfmt("Vendors(%1) uploaded successfully",_VendTable.Name));
}
ttscommit;
}
catch
{
Error(strfmt("Upload Failed in row %1", row));
}
type = cells.item(row+1, 1).value().variantType();
}while (type != COMVariantType::VT_EMPTY);
info(strfmt("Vendors uploaded successfully"));
application.quit();
}
--------------------------------End Of Code------------------
put this code in any event EX: Button Click ,this code when you click on the button it will open the browse dialog to choose your excel file to read it and then the code will work on the data that comes from the excel
I Hope the Code really Help you
Best Regards,
Comments