The following as Excel 2007 or 2003 Import to AX 2009
remak by : Jimmy Jun 14th 2010
代码
void clicked()
{
SysExcelApplication Excel;
SysExcelWorkbooks Books;
SysExcelWorkbook Book;
SysExcelWorksheets Sheets;
SysExcelWorksheet Sheet;
SysExcelCells Cells;
SysExcelCell Cell;
SysExcelStyles Styles;
SysExcelStyle Style;
int RowCount,RecNum,n;
Filename _FilenameOpen;
QVS_GlueTraceability _Template;
Dialog _Dialog;
DialogField dlgPath;
System.Exception ex;
#WINAPI
FileNameFilter filter = ['Excel Files','*.xls;*.xlsx'];//['Image Files','*.bmp;*.jpg;*.gif;*.jpeg'];
;
try
{
_FilenameOpen = Winapi::getOpenFileName(element.hWnd(),filter,WinAPI::getFolderPath(#CSIDL_Personal), "@SYS53008", '','');
if(!_FilenameOpen)
return ;
if(!box::yesNo("Are you sure Import to AX 2009?",DialogButton::No,'Import to AX2009'))
return ;
infolog.startLengthyOperation();//busy .....start
Excel = SysExcelApplication::construct();
Books = Excel.workbooks();
Books.open(_FilenameOpen,true);
Book = Books.item(1);
Sheets = Book.worksheets();
Sheet = Sheets.itemFromNum(1);
Cells = Sheet.cells();
RowCount = 2; //from second line fect data
for(RowCount = 2;Cells.item(RowCount,1).value().bStr() != "";RowCount++)
{
n = 1;
_Template.CreatedDate = today();
_Template.CustAccount = Cells.item(RowCount,n).value().bStr(); n++;
_Template.SalesId = Cells.item(RowCount,n).value().bStr(); n++;
_Template.ItemId = Cells.item(RowCount,n).value().bStr(); n++;
_Template.ShipQty = Cells.item(RowCount,n).value().double();n++;
_Template.Ex_Factory = Cells.item(RowCount,n).value().date(); n++;
_Template.ProdId = Cells.item(RowCount,n).value().bStr(); n++;
_Template.ItemGlue = Cells.item(RowCount,n).value().bStr(); n++;
_Template.BatchCode = Cells.item(RowCount,n).value().bStr(); n++;
_Template.PurchId = Cells.item(RowCount,n).value().bStr();
if(InventTable::find(_Template.ItemId))
{
_Template.insert();
RecNum++;
}
}
infolog.endLengthyOperation();//busy .....end
}
catch(Exception::CLRError)
{
Excel.quit();
ex = CLRInterop::getLastException();
while( ex )
{
info( ex.get_Message() );
ex = ex.get_InnerException();
}
}
catch(Exception::Error)
{
Excel.quit();
global::exceptionTextFallThrough();
}
info(strfmt("Import into Test of %1 ",RecNum));
Excel.quit();
QVS_GlueTraceability_ds.executeQuery();
QVS_GlueTraceability_ds.research();
}
AX2009 export to Excel 2007 or excel 2003
code as below:
Remark by Jimmy Jun 14th 2010
代码
void clicked()
{
SysExcelApplication excel;
SysExcelWorkbooks books;
SysExcelWorkbook book;
SysExcelWorksheets sheets;
SysExcelWorksheet sheet;
SysExcelRange columns;
SysExcelRange column;
SysExcelCells cells;
SysExcelCell cell;
TMPMovementImport IT;
int i,j,counter;
FileName filename;
UserInfo UserInfo;
System.Exception ex;
#WINAPI
;
if(!Box::yesNo('Are you sure export to excel?',DialogButton::No,'Export to excel'))
return ;
try
{
infolog.startLengthyOperation();
excel = SysExcelApplication::construct();
books = excel.workbooks();
book = books.add();
Sheets = book.worksheets();
sheet = sheets.itemFromNum(1);
sheet.name('AX2009 export To excel');
select firstonly UserInfo where UserInfo.Id == curUserId();
filename = WinAPI::getFolderPath(#CSIDL_Personal) + '\\' + strupr(curext()) + '_' + date2str(systemdateget(),321, 2, -1, 2, -1, 4) + '_' + UserInfo.networkAlias + '_' + sheet.name() + '.xls';
if(WinAPI::fileExists(filename))
WinAPI::deleteFile(filename);
i = 1;j = 1;
sheet.cells().item(i,j).value('Item Number'); j++;
sheet.cells().item(i,j).value('Configuration'); j++;
sheet.cells().item(i,j).value('InventLocation'); j++;
sheet.cells().item(i,j).value('WmsLocation'); j++;
sheet.cells().item(i,j).value('Qty'); j++;
sheet.cells().item(i,j).value('Remark');
while select it
{
j = 1; i ++; counter++;
sheet.cells().item(i,j).value(IT.ItemId); j++;
sheet.cells().item(i,j).value(IT.ConfigId); j++;
sheet.cells().item(i,j).value(IT.WhsId); j++;
sheet.cells().item(i,j).value(IT.LocationId); j++;
sheet.cells().item(i,j).value(IT.Qty); j++;
sheet.cells().item(i,j).value(IT.Remark);
}
infolog.endLengthyOperation();
}
catch (Exception::Error)
{
excel.quit();
throw Error(strfmt("%1",global::exceptionTextFallThrough()));
}
catch(Exception::CLRError)
{
Excel.quit();
ex = CLRInterop::getLastException();
while( ex )
{
info( ex.get_Message() );
ex = ex.get_InnerException();
}
}
book.saveAs(filename);
excel.quit();
if(WinAPI::fileExists(filename))
WinAPI::shellExecute(filename);
//excel.visible(true);
info(strFmt("Total Export %1 records!",counter));
}
Using Excel Template export data from Dynamics AX 2009
static void Jimmy_ExcelTemplateExportData(Args _args)
{
SysExcelApplication excel;
SysExcelWorkbooks books;
SysExcelWorkbook book;
SysExcelWorksheets sheets;
SysExcelWorksheet sheet;
int i,m;
InventTable IT;
;
try
{
excel = SysExcelApplication::construct();
books = excel.workbooks();
books.open(@"\\192.168.10.26\public$\Department\IT\Jimmy\Excel Template.xlsx");
book = books.item(1);
Sheets = book.worksheets();
sheet = sheets.itemFromNum(1);
sheet.name("Excel 模板");
i++;
sheet.cells().item(i,1).value(companyInfo::find().Name);
i++;
sheet.cells().item(i,1).value("Item number");
sheet.cells().item(i,2).value("Item Type");
sheet.cells().item(i,3).value("NameAlias");
sheet.cells().item(i,4).value("ItemGroupId");
while select IT order by ItemId
{
i++;
m = 1;
sheet.cells().item(i,m).value(IT.ItemId); m++;
sheet.cells().item(i,m).value(enum2str(IT.ItemType)); m++;
sheet.cells().item(i,m).value(IT.NameAlias); m++;
sheet.cells().item(i,m).value(IT.ItemGroupId);
if(i > 30)
break;
}
//book.saveAs("Excel Template to my document.xlsx");
excel.visible(true);
if(i - 2 > 0)
info(strfmt('Exported a total of %1 data',i - 2));
else
info('Nothing Export any data');
excel.quit();
}
catch(Exception::Error)
{
excel.quit();
info("Already Exit thread of SysExcelApplication!");
}
}