private void PopulateRecords(ImportCLassModel rec, ObservableCollection <GoodsReceive> temp) { if (grCollection.Any(sap => sap.Material == rec.Material) == false) { temp.Add(new GoodsReceive { DocumentDate = rec.DocumentDate, PostingDate = null, PurchaseOrder = rec.PurchaseOrder, Vendor = rec.Vendor ?? string.Empty, DeliveryNote = rec.DeliveryNote ?? string.Empty, BillOfLading = rec.BillOfLading ?? string.Empty, HeaderText = rec.HeaderText ?? string.Empty, Material = rec.Material, MaterialShortText = rec.MaterialShortText, Ok = Convert.ToBoolean(rec.Ok), Ok2 = Convert.ToBoolean(rec.Ok), Quantity = rec.Quantity, Quantity2 = rec.Quantity, Eun = rec.Eun, MvmtType = rec.MvmtType ?? string.Empty, StorageLoc = rec.StorageLoc, Plant = rec.Plant, StorageBin = rec.StorageBin, CreatedOn = DateTime.Now, CreatedBy = AuthenticatedUser, ModifiedOn = DateTime.Now, ModifiedBy = AuthenticatedUser, IsChecked = true }); } }
private void ExportGR(object ignored) { ExcelNPOIStorage storage = new ExcelNPOIStorage(typeof(ImportCLassModel), 0, 0); string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Export GR"); if (!Directory.Exists(path)) { try { Directory.CreateDirectory(path); } catch (IOException ie) { Console.WriteLine("IO Error: " + ie.Message); } catch (Exception e) { Console.WriteLine("General Error: " + e.Message); } } storage.FileName = path + string.Concat(@"\GoodsReceiveExport_" + DateTime.Now.ToString("yyyy-MM-dd_HHmm", System.Globalization.CultureInfo.InvariantCulture)) + ".xlsx"; storage.ColumnsHeaders.Add("Document Date"); storage.ColumnsHeaders.Add("Posting Date"); storage.ColumnsHeaders.Add("Purchase Order"); storage.ColumnsHeaders.Add("Vendor"); storage.ColumnsHeaders.Add("Delivery Note"); storage.ColumnsHeaders.Add("Bill Of Lading"); storage.ColumnsHeaders.Add("Header Text"); storage.ColumnsHeaders.Add("Material"); storage.ColumnsHeaders.Add("Material Short Text"); storage.ColumnsHeaders.Add("OK"); storage.ColumnsHeaders.Add("Quantity"); storage.ColumnsHeaders.Add("Eun"); storage.ColumnsHeaders.Add("Mvmt Type"); storage.ColumnsHeaders.Add("Storage Loc"); storage.ColumnsHeaders.Add("Plant"); storage.ColumnsHeaders.Add("Storage Bin"); storage.ColumnsHeaders.Add("Quantity Received"); storage.ColumnsHeaders.Add("Transaction Reason"); storage.ColumnsHeaders.Add("Received By"); storage.ColumnsHeaders.Add("Received On"); ObservableCollection <ImportCLassModel> importObj = new ObservableCollection <ImportCLassModel>(); ImportCLassModel temp = new ImportCLassModel(); using (var db = new InventoryContext()) { foreach (var rec in grCollection) { var grTxn = db.GRTransactions.Where(p => p.GRID == rec.ID); if (grTxn != null && grTxn.Count() > 0) { foreach (var item in grTxn) { temp = new ImportCLassModel() { DocumentDate = rec.DocumentDate, PostingDate = rec.PostingDate, PurchaseOrder = rec.PurchaseOrder, Vendor = rec.Vendor ?? string.Empty, DeliveryNote = rec.DeliveryNote ?? string.Empty, BillOfLading = rec.BillOfLading ?? string.Empty, HeaderText = rec.HeaderText ?? string.Empty, Material = rec.Material, MaterialShortText = rec.MaterialShortText, Ok = rec.Ok == null || rec.Ok == false ? "Not Complete" : "Completed", Quantity = rec.Quantity, Eun = rec.Eun, MvmtType = rec.MvmtType ?? string.Empty, StorageLoc = rec.StorageLoc, Plant = rec.Plant, StorageBin = rec.StorageBin, QuantityReceived = item.Quantity, ReasonDesc = item.Reason != null ? item.Reason.ReasonDesc : string.Empty, ReceivedBy = item.CreatedBy, ReceivedOn = Convert.ToString(item.CreatedOn), }; importObj.Add(temp); } } else { temp = new ImportCLassModel() { DocumentDate = rec.DocumentDate, PostingDate = rec.PostingDate, PurchaseOrder = rec.PurchaseOrder, Vendor = rec.Vendor ?? string.Empty, DeliveryNote = rec.DeliveryNote ?? string.Empty, BillOfLading = rec.BillOfLading ?? string.Empty, HeaderText = rec.HeaderText ?? string.Empty, Material = rec.Material, MaterialShortText = rec.MaterialShortText, Ok = rec.Ok == null || rec.Ok == false ? "Not Complete" : "Completed", Quantity = rec.Quantity, Eun = rec.Eun, MvmtType = rec.MvmtType ?? string.Empty, StorageLoc = rec.StorageLoc, Plant = rec.Plant, StorageBin = rec.StorageBin, QuantityReceived = 0, ReasonDesc = string.Empty, ReceivedBy = string.Empty, ReceivedOn = string.Empty }; importObj.Add(temp); } } } if (importObj != null) { storage.InsertRecords(importObj.ToArray()); this.confirmDeleteInteractionRequest.Raise( new Confirmation { Content = "Exported successfully. Open file?", Title = "Notification" }, c => { InteractionResultMessage = c.Confirmed ? OpenExportedFile(storage.FileName) : "NOT OK!"; }); } }
private void ReadExcelFile() { try { ISheet sheet = grWorkbook.GetSheet("GR"); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); #region Cells Comparison while (rows.MoveNext()) { IRow row = (XSSFRow)rows.Current; if (row.RowNum == 0) { continue; } if (row.Cells.Any(d => d.CellType == CellType.Error)) { continue; } ImportCLassModel obj = new ImportCLassModel(); for (int i = 0; i < row.LastCellNum; i++) { ICell cell = row.GetCell(i); switch (i) { case 0: if (cell == null) { obj.DocumentDate = null; } else { obj.DocumentDate = cell.DateCellValue; } break; case 1: if (cell == null) { obj.DocumentDate = null; } else { obj.PostingDate = cell.DateCellValue; } break; case 2: if (cell == null) { continue; } else { obj.PurchaseOrder = SetCellValue(cell); } break; case 3: if (cell == null) { obj.Vendor = null; } else { obj.Vendor = SetCellValue(cell); } break; case 4: if (cell == null) { obj.DeliveryNote = null; } else { obj.DeliveryNote = SetCellValue(cell); } break; case 5: if (cell == null) { obj.BillOfLading = null; } else { obj.BillOfLading = SetCellValue(cell); } break; case 6: if (cell == null) { obj.HeaderText = null; } else { obj.HeaderText = SetCellValue(cell); } break; case 7: if (cell == null) { continue; } else { obj.Material = SetCellValue(cell); } break; case 8: if (cell == null) { continue; } else { obj.MaterialShortText = SetCellValue(cell); } break; case 9: if (cell == null) { obj.Ok = null; } else { obj.Ok = cell.BooleanCellValue.ToString(); } break; case 10: if (cell == null) { continue; } else { obj.Quantity = Convert.ToDecimal(cell.NumericCellValue); } break; case 11: if (cell == null) { continue; } else { obj.Eun = SetCellValue(cell); } break; case 12: if (cell == null) { obj.MvmtType = null; } else { obj.MvmtType = SetCellValue(cell); } break; case 13: if (cell == null) { continue; } else { obj.StorageLoc = SetCellValue(cell); } break; case 14: if (cell == null) { continue; } else { obj.Plant = Convert.ToInt32(cell.NumericCellValue); } break; case 15: if (cell == null) { continue; } else { obj.StorageBin = SetCellValue(cell); } break; } } PopulateRecords(obj, tempCollection); } #endregion Cells Comparison if (tempCollection.Count() > 0) { ImportBtn = true; IsEnableAutoRefresh = false; _ImportGRCommand.RaiseCanExecuteChanged(); _ExportGRCommand.RaiseCanExecuteChanged(); GoodReceives = new ListCollectionView(tempCollection); GoodReceives.SortDescriptions.Add(new SortDescription("CreatedOn", ListSortDirection.Descending)); CollectionViewSource.GetDefaultView(GoodReceives).Filter = Filter; } else { throw new Exception("There was no matched data detected."); } } catch (Exception ex) { MessageBox.Show("Unable to read contents:\n\n" + ex.Message, "Error"); } }