public StringBuilder GetInwardExcelDataUserBased(string users_Json, string product_Json, string product_type_name, string division_name) { try { CreateFileName(product_type_name, division_name); string company_code = _objCurrentInfo.GetCompanyCode(); string[] hiddenColumns = new string[] { }; string[] editableColumnNames = new string[] { }; string[] hiddenColumnNames = new string[] { }; _objExcelConverter = new ExcelFactory(); BLMaster objBlMaster = new BLMaster(); DataSet dsInwardExcelData = new DataSet(); dsInwardExcelData = objBlMaster.GetInwardExcelDataUserBased(company_code, users_Json, product_Json); if (dsInwardExcelData.Tables != null && dsInwardExcelData.Tables[0].Rows != null && dsInwardExcelData.Tables[0].Rows.Count > 0) { dsInwardExcelData.Tables[0].Columns.Add("Quantity", typeof(string)); DataRowCollection dr = dsInwardExcelData.Tables[0].Rows; DataColumnCollection dc = dsInwardExcelData.Tables[0].Columns; _objExcelConverter.DataSetToExcel(DOWNLOAD_PATH_KEY_NAME, _fileNameString.ToString(), dsInwardExcelData, hiddenColumnNames, hiddenColumns, false, editableColumnNames); } else { StringBuilder str = new StringBuilder("No Data Found."); return(str); } StringBuilder MasterfilenameStrings = GetInwardLookupValuesExcel(); return(_fileNameString.Append("^").Append(MasterfilenameStrings)); } catch (Exception ex) { return(_fileNameString.Append(ex.Message)); } }
private string GetProductMasterExcel(DataSet dsProdMaster) { string company_code = _objCurrentInfo.GetCompanyCode(); string[] hiddenColumns = new string[] { }; string[] editableColumnNames = new string[] { }; string[] hiddenColumnNames = new string[] { }; _objExcelConverter = new ExcelFactory(); _objExcelConverter.DataSetToExcel(DOWNLOAD_PATH_KEY_NAME, PRODUCT_MASTER_FILE_NAME, dsProdMaster, hiddenColumnNames, hiddenColumns, false, editableColumnNames); return(PRODUCT_MASTER_FILE_NAME); }
private string GetEmployeeMasterExcel(DataSet dsEmployeeMaster) { string fileNamewithPath = string.Empty; try { string company_code = _objCurrentInfo.GetCompanyCode(); string[] hiddenColumns = new string[] { }; string[] editableColumnNames = new string[] { }; string[] hiddenColumnNames = new string[] { }; _objExcelConverter = new ExcelFactory(); _objExcelConverter.DataSetToExcel(DOWNLOAD_PATH_KEY_NAME, EMPLOYEE_USER_MASTER_FILE_NAME, dsEmployeeMaster, hiddenColumnNames, hiddenColumns, false, editableColumnNames); return(EMPLOYEE_USER_MASTER_FILE_NAME); } catch (Exception ex) { return(ex.Message); } }
public ActionResult UploadExcelFile(HttpPostedFileBase fileUpload, HttpPostedFileBase fileUploadUser, FormCollection collection) { string fileName = ""; try { // Declare and assign the variables. string errorCode = "-1"; string companyCode = _objCurrentInfo.GetCompanyCode(); string userCode = _objCurrentInfo.GetUserCode(); string subDomain = _objCurrentInfo.GetSubDomain(); string user_Type_Code = string.Empty; string product_Type_Code = string.Empty; string inward_Date = ""; string screenMode = collection["ScreenMode"].ToString(); if (screenMode == "USER TYPE") { inward_Date = collection["txtInward"].ToString(); fileName = fileUpload.FileName; } else { inward_Date = collection["txtUserInwardDate"].ToString(); fileName = fileUploadUser.FileName; } _objExcelConverter = new ExcelFactory(); // Save the file to server path. string fileNameWithPath = fileProvider.GetFilePathToSave("ExcelUploadPath", fileName); if (screenMode == "USER TYPE") { fileUpload.SaveAs(fileNameWithPath); } else { fileUploadUser.SaveAs(fileNameWithPath); } // Set Excel column Names. Hashtable hashExcelColumnsTable = new Hashtable(); hashExcelColumnsTable.Add("Row_No", ""); hashExcelColumnsTable.Add("User_Code", ""); hashExcelColumnsTable.Add("Product_Code", ""); hashExcelColumnsTable.Add("Product_Name", ""); hashExcelColumnsTable.Add("Employee_Name", ""); hashExcelColumnsTable.Add("Employee_Number", ""); hashExcelColumnsTable.Add("UOM_Name", ""); hashExcelColumnsTable.Add("Ref_Key1", ""); hashExcelColumnsTable.Add("Internal_Reference_Key", ""); hashExcelColumnsTable.Add("Region_Name", ""); hashExcelColumnsTable.Add("Quantity", ""); // Check column headers are correct. if (!_objExcelConverter.IsExcelColumnHeadrsAreCorrect(hashExcelColumnsTable, INWARD_EXCEL_SHEET_NAME, fileNameWithPath)) { errorCode = "1"; ViewBag.ErrorCode = errorCode; UploadedFileDelete(fileNameWithPath); return(View("UploadExcelFile")); } // Retrieve columns from excel. string[] excelRetrieveColumns = new string[] { "Row_No", "User_Code", "Product_Code", "Employee_Number", "Ref_Key1", "Internal_Reference_Key", "Quantity " }; string[] excelHiddenColumns = new string[] { "*" }; // Then convert the excel to datatable. DataTable dt = _objExcelConverter.ExcelToDataSet(excelRetrieveColumns, INWARD_EXCEL_SHEET_NAME, INWARD_EXCEL_WHERE_QRY, fileNameWithPath, "Quantity"); dt.Columns.Add("BPID", typeof(Guid)); dt.Columns["Internal_Reference_Key"].ColumnName = "Internal_Refer_Key"; dt.Columns["Quantity"].ColumnName = "Qty"; Guid NewId = Guid.NewGuid(); dt.Columns.Add("Company_Code", typeof(string)); dt.Columns.Add("BP_Type", typeof(string)); for (int index = 0; index < dt.Rows.Count; index++) { dt.Rows[index]["Company_Code"] = companyCode; dt.Rows[index]["BPID"] = NewId; dt.Rows[index]["BP_Type"] = BP_TYPE; } try { for (int index = 0; index < dt.Rows.Count; index++) { Convert.ToInt32(dt.Rows[index]["Row_No"]); } } catch { errorCode = "4"; UploadedFileDelete(fileNameWithPath); ViewBag.ErrorCode = errorCode; return(View("UploadExcelFile")); } // set the error code to view bag. if errorcode is -1 validation is pass, otherwise fail. ViewBag.ErrorCode = errorCode; if (errorCode == "-1") { // Call the Async method. Task task = Task.Factory.StartNew(() => _objSPData.InsertInwardBulkUploadAsync(subDomain, dt, companyCode, NewId, userCode, fileName, BP_TYPE, user_Type_Code, product_Type_Code, inward_Date)); } // _objSPData.InsertInwardBulkUploadAsync(dt, companyCode, Guid.NewGuid(), userCode, fileName, BP_TYPE, user_Type_Code, product_Type_Code, inward_Date); // Inserts the excel rows in to the Inward staging table. UploadedFileDelete(fileNameWithPath); return(View("UploadExcelFile")); } catch (Exception ex) { string fileNameWithPath = fileProvider.GetFilePathToSave("ExcelUploadPath", fileName); UploadedFileDelete(fileNameWithPath); if (ex.GetType() == typeof(System.Data.OleDb.OleDbException)) { ViewBag.ErrorCode = "3"; return(View("UploadExcelFile")); } ViewBag.ErrorCode = ex.Message; return(View("UploadExcelFile")); } }