1 //生成导入模板 2 void creatTemplate() 3 { 4 SysExcelApplication m_application; 5 SysExcelWorkbooks m_workbooks; 6 SysExcelWorkbook m_workbook; 7 SysExcelWorksheets m_worksheets; 8 SysExcelWorksheet m_worksheet; 9 SysExcelCells m_cells; 10 int m_row = 1; 11 ; 12 13 m_application = SysExcelApplication::construct(); 14 m_workbooks = m_application.workbooks(); 15 m_workbook = m_workbooks.add(); 16 m_worksheets = m_workbook.worksheets(); 17 m_worksheet = m_worksheets.itemFromNum(1); 18 m_cells = m_worksheet.cells(); 19 20 m_cells.item(1,1).value("Item number"); 21 m_cells.item(1,2).value("Config number"); 22 m_cells.item(1,3).value("Sales quantity"); 23 24 m_worksheet.name("CustomsReport"); 25 m_worksheet.columns().item(2).autoFit(); 26 m_application.visible(true); 27 } 28 29 //导入数据 30 void clicked() 31 { 32 SysExcelApplication Excel; 33 SysExcelWorkbooks Books; 34 SysExcelWorkbook Book; 35 SysExcelWorksheets Sheets; 36 SysExcelWorksheet Sheet; 37 SysExcelCells Cells; 38 SysExcelCell Cell; 39 SysExcelStyles Styles; 40 SysExcelStyle Style; 41 int RowCount,RecNum,n,i; 42 43 Filename _FilenameOpen; 44 InventTable InventTable; 45 ConfigTable ConfigTable; 46 BomVersion BomVersion; 47 InventDim t_InventDim; 48 InventBatch t_InventBatch; 49 InventStdPic t_InventStdPic; 50 51 str a_ItemId[],a_ConfigId[]; 52 real a_Qty[]; 53 54 ItemId ItemId; 55 Qty Qty; 56 ConfigId ConfigId; 57 58 Boolean isContinue = true,isBlocked; 59 60 Dialog _Dialog; 61 DialogField dlgPath; 62 System.Exception ex; 63 #WINAPI 64 FileNameFilter filter = ['Excel Files','*.xls;*.xlsx'];//['Image Files','*.bmp;*.jpg;*.gif;*.jpeg']; 65 ; 66 67 if(!SalesLine.ItemId) 68 SalesLine_DS.delete(); 69 try 70 { 71 if(!box::yesNo("Do you have import Excel template?",DialogButton::No,'Import template')) 72 { 73 this.creatTemplate(); 74 return; 75 } 76 _FilenameOpen = Winapi::getOpenFileName(element.hWnd(),filter,WinAPI::getFolderPath(#CSIDL_Personal), "@SYS53008", '',''); 77 if(!_FilenameOpen) 78 return ; 79 if(!box::yesNo("Are you sure Import to AX 2009?",DialogButton::No,'Import to AX2009')) 80 return ; 81 82 Excel = SysExcelApplication::construct(); 83 Books = Excel.workbooks(); 84 Books.open(_FilenameOpen,true); 85 86 Book = Books.item(1); 87 Sheets = Book.worksheets(); 88 Sheet = Sheets.itemFromNum(1); 89 Cells = Sheet.cells(); 90 91 RowCount = 2; //from second line fect data 92 93 for(RowCount = 2;Cells.item(RowCount,1).value().bStr() != "";RowCount++) 94 { 95 n = 1; 96 97 ItemId = Cells.item(RowCount,n).value().bStr(); n++; 98 ConfigId = Cells.item(RowCount,n).value().bStr(); n++; 99 Qty = Cells.item(RowCount,n).value().double(); 100 101 InventTable = InventTable::find(ItemId); 102 if(InventTable) 103 { 104 //if(InventTable.QVS_ItemBlock) 105 // isBlocked = true; 106 if(isBlocked) 107 { 108 isContinue = false; 109 info(strfmt("Item number %1 is blocked!",ItemId)); 110 } 111 select firstonly t_InventStdPic 112 where t_InventStdPic.ItemId == InventTable.ItemId; 113 if(!t_InventStdPic) 114 { 115 isContinue = false; 116 info(strfmt("Item number %1 has no picture!",ItemId)); 117 } 118 119 select firstonly ConfigTable 120 where ConfigTable.ItemId == InventTable.ItemId; 121 122 if(ConfigTable) 123 { 124 ConfigTable = ConfigTable::find(ItemId,ConfigId); 125 select firstonly BomVersion 126 where BomVersion.BOMId == ConfigTable.BOMId 127 && BomVersion.ItemId == ItemId 128 //&& BomVersion.Active == noYes::Yes 129 && BomVersion.Approved == noYes::Yes; 130 if(!ConfigTable) 131 { 132 isContinue = false; 133 info(strfmt("Item number %1 has no configId %2!",ItemId,ConfigId)); 134 } 135 if(ConfigTable.DisUsed) 136 { 137 isContinue = false; 138 info(strfmt("Item number %1's Config id %2 is 'DisUse'!",ItemId,ConfigId)); 139 } 140 //if(!BomVersion) 141 //{ 142 // isContinue = false; 143 // info(strfmt("Item number %1's BOM version %2 is DisUse''!",ItemId,ConfigTable.BOMId)); 144 //} 145 } 146 } 147 else 148 { 149 isContinue = false; 150 info(strfmt("Item number %1 can't been found!",ItemId,ConfigId)); 151 } 152 153 if(!isContinue) 154 { 155 isContinue = true; 156 continue; 157 } 158 159 if(ItemId && Qty) 160 { 161 RecNum++; 162 163 a_ItemId[RecNum] = ItemId; 164 a_ConfigId[RecNum] = ConfigId; 165 a_Qty[RecNum] = Qty; 166 } 167 } 168 } 169 catch(Exception::CLRError) 170 { 171 Excel.quit(); 172 ex = CLRInterop::getLastException(); 173 while( ex ) 174 { 175 info( ex.get_Message() ); 176 ex = ex.get_InnerException(); 177 } 178 } 179 catch(Exception::Error) 180 { 181 Excel.quit(); 182 global::exceptionTextFallThrough(); 183 } 184 Excel.quit(); 185 186 info("------------------------------------------------"); 187 for(i = 1;i <= RecNum;i++) 188 { 189 SalesLine_DS.create(); 190 //SalesLine.InventDimId 191 SalesLine.ItemId = strupr(a_ItemId[i]); 192 193 t_InventBatch.clear(); 194 select firstonly t_InventBatch 195 where t_InventBatch.itemId == SalesLine.ItemId && 196 (t_InventBatch.expDate == datenull() || 197 t_InventBatch.expDate >= today()); 198 199 t_InventDim.clear(); 200 t_InventDim.initValue(); 201 t_InventDim.configId = a_ConfigId[i]; 202 t_InventDim.inventBatchId = t_InventBatch.inventBatchId; 203 t_InventDim.InventLocationId = "FPW"; 204 205 SalesLine.InventDimId = InventDim::findOrCreate(t_InventDim).inventDimId; 206 SalesLine.SalesQty = a_Qty[i]; 207 208 SalesLine_DS.itemIdModified(); //这里面的逻辑非常重要 209 SalesLine_DS.Write(); 210 211 //info(strfmt("%1: %2",a_ItemId[i],a_Qty[i])); 212 } 213 214 info(strfmt("Import into total of %1 records!",RecNum)); 215 216 SalesLine_ds.executeQuery(); 217 SalesLine_ds.research(); 218 }