public ActionResult ExportExcelInventory(FileUploadModel blockData) { int month = blockData.currentMonth; int year = blockData.currentyear; string id = blockData.PermissionId.ToString(); string xmlFilePath = this.CONFIG_FOLDER + "\\excel_export_format.xml"; BI_Project.Helpers.ExcelHelper excelHelper = new ExcelHelper(); string excelFileName = ""; ExcelXmlModel model = excelHelper.GetUploadExcelXMLConfig(xmlFilePath, id); this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); this.oracleConnection.OpenDBConnect(); List <Dictionary <string, object> > data = services.GetStoreDataInventory(model, month, year); MemoryStream memoryStream = excelHelper.ExportExcel(model, data, ref excelFileName); this.oracleConnection.CloseDBConnect(); return(File(memoryStream.ToArray(), "application/vnd.ms-excel", excelFileName + ".xlsx")); }
public List <Dictionary <string, object> > GetStoreData(ExcelXmlModel model) { List <Dictionary <string, object> > output = new List <Dictionary <string, object> >(); try { string storeName = "shop_name"; string proceName = null; //get store name to choose store procedure List <ExcelXMLColumn> lstColumn = new List <ExcelXMLColumn>(); lstColumn = model.LstColumn; foreach (var column in lstColumn) { if (column.Name.Contains(storeName)) { proceName = "pws_getmetadata_excel"; break; } else { proceName = "pws_getdata_excel"; } } output = GetStoreData(model, "report_name_para", "p_recordset", proceName); } catch (Exception ex) { } return(output); }
public MemoryStream ExportExcelWithConfigReport(string id, string filePath, ExcelXmlModel model) { List <Dictionary <string, object> > data = GetExcelConfigData(model); ExcelHelper excelHelper = new ExcelHelper(); string excelFileName = null; MemoryStream memoryStream = excelHelper.ExportExcel(model, data, ref excelFileName); //save file excel exported var package = new ExcelPackage(memoryStream); FileInfo Path = new FileInfo(filePath); package.SaveAs(Path); return(memoryStream); }
public async Task <FileResult> ExportPLExcel(FileUploadModel uploadModel) { var memory = new MemoryStream(); string xmlFilePath = this.CONFIG_FOLDER + "\\excel_export_format.xml"; string id = uploadModel.PermissionId.ToString(); ExcelHelper excelHelper = new ExcelHelper(); ExcelXmlModel model = excelHelper.GetUploadExcelXMLConfig(xmlFilePath, id); try { /**************************************EXPORT EXCEL OFFLINE***************************************/ if (Convert.ToBoolean(uploadModel.ExportOff) == true) { //Get last time write file string path = System.Web.Hosting.HostingEnvironment.MapPath(this.EXCEL_EXPORT_FOLDER) + model.ExcelXmlCommon.exportDirectory; string fileName = excelHelper.GetNewFile(path); if (fileName == null) { throw new Exception(); } string filePath = path + "\\" + fileName; using (var stream = new FileStream(filePath, FileMode.Open)) { await stream.CopyToAsync(memory); } memory.Position = 0; } /**************************************EXPORT EXCEL ONLINE***************************************/ else { this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(this.oracleConnection, this.DBConnection); this.oracleConnection.OpenDBConnect(); string filePath = System.Web.Hosting.HostingEnvironment.MapPath(this.EXCEL_EXPORT_FOLDER) + model.ExcelXmlCommon.exportDirectory + "//" + model.ExcelXmlCommon.ExcelFileName + "_" + APIStringHelper.GenerateId() + ".xlsx"; memory = services.ExportExcelWithConfigReport(id, filePath, model); this.oracleConnection.CloseDBConnect(); } } catch (Exception ex) { this.ERRORS = ex.ToString(); } return(File(memory.ToArray(), "application/vnd.ms-excel", model.ExcelXmlCommon.ExcelFileName + ".xlsx")); }
public ActionResult ExportExcelWithConfigReport(string id) { string xmlFilePath = this.CONFIG_FOLDER + "\\excel_export_format.xml"; BI_Project.Helpers.ExcelHelper excelHelper = new ExcelHelper(); string excelFileName = ""; ExcelXmlModel model = excelHelper.GetUploadExcelXMLConfig(xmlFilePath, id); this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); this.oracleConnection.OpenDBConnect(); List <Dictionary <string, object> > data = services.GetExcelConfigData(model); MemoryStream memoryStream = excelHelper.ExportExcel(model, data, ref excelFileName); this.oracleConnection.CloseDBConnect(); return(File(memoryStream.ToArray(), "application/vnd.ms-excel", excelFileName + ".xlsx")); }
/// <summary> /// Get Store Name, Indicator /// </summary> /// <param name="model"></param> /// <returns></returns> public List <Dictionary <string, object> > GetExcelConfigData(ExcelXmlModel model) { //Dictionary<string, object> dicParas = new Dictionary<string, object>(); //Dictionary<string, object> dicParaOuts = new Dictionary<string, object>(); List <Dictionary <string, object> > lst_store = new List <Dictionary <string, object> >(); List <Dictionary <string, object> > lst_kpi = new List <Dictionary <string, object> >(); List <Dictionary <string, object> > ouput = new List <Dictionary <string, object> >(); try { string procedureName = null; if (model.ExcelXmlCommon.exportDirectory.Equals("PL_CongTy")) { procedureName = "pws_getreporsetupcompany_excel"; } else { procedureName = "pws_getreporsetupstore_excel"; } this.ConnectOracleDB.command.Parameters.Clear(); var op = new OracleParameter("PK_SRPTNBR_para", OracleDbType.Varchar2); op.Direction = ParameterDirection.Input; op.Value = model.ExcelXmlCommon.ReportName; this.ConnectOracleDB.command.Parameters.Add(op); this.ConnectOracleDB.command.Parameters.Add("p_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output; this.ConnectOracleDB.command.CommandText = procedureName; this.ConnectOracleDB.command.CommandType = CommandType.StoredProcedure; OracleDataReader objReader = this.ConnectOracleDB.command.ExecuteReader(); while (objReader.Read()) { var columns = Enumerable.Range(0, objReader.FieldCount).Select(objReader.GetName).ToList(); Dictionary <string, object> dic = new Dictionary <string, object>(); for (int i = 0; i < columns.Count; i++) { foreach (ExcelXMLColumn xmlColumn in model.LstColumn) { if (!xmlColumn.IsGetData) { continue; } if (xmlColumn.Name != columns[i].ToLower()) { continue; } if ((objReader[xmlColumn.Name] != null) && (xmlColumn.IsGetData)) { dic.Add(xmlColumn.Name, objReader[xmlColumn.Name]); } } } ouput.Add(dic); //objReader.NextResult(); } objReader.Close(); objReader.Dispose(); } catch (Exception ex) { } finally { this.ConnectOracleDB.CloseDBConnect(); } return(ouput); }
public List <Dictionary <string, object> > GetStoreDataInventory(ExcelXmlModel model, int month, int year) { //Dictionary<string, object> dicParas = new Dictionary<string, object>(); //Dictionary<string, object> dicParaOuts = new Dictionary<string, object>(); List <Dictionary <string, object> > lst_store = new List <Dictionary <string, object> >(); List <Dictionary <string, object> > lst_kpi = new List <Dictionary <string, object> >(); List <Dictionary <string, object> > ouput = new List <Dictionary <string, object> >(); try { this.ConnectOracleDB.command.Parameters.Clear(); var time = month.ToString() + "-" + year.ToString().Substring(2, 2); if (month < 10) { time = '0' + time; } var dateTime = new OracleParameter("dateTime", OracleDbType.Varchar2); dateTime.Direction = ParameterDirection.Input; dateTime.Value = time; this.ConnectOracleDB.command.Parameters.Add(dateTime); this.ConnectOracleDB.command.Parameters.Add("p_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output; this.ConnectOracleDB.command.CommandText = "PWS_GETINVENTORYDATA_EXCEL"; this.ConnectOracleDB.command.CommandType = CommandType.StoredProcedure; OracleDataReader objReader = this.ConnectOracleDB.command.ExecuteReader(); while (objReader.Read()) { var columns = Enumerable.Range(0, objReader.FieldCount).Select(objReader.GetName).ToList(); Dictionary <string, object> dic = new Dictionary <string, object>(); for (int i = 0; i < columns.Count; i++) { foreach (ExcelXMLColumn xmlColumn in model.LstColumn) { if (!xmlColumn.IsGetData) { continue; } if (xmlColumn.Name.ToLower() != columns[i].ToLower()) { continue; } if ((objReader[xmlColumn.Name] != null) && (xmlColumn.IsGetData)) { dic.Add(xmlColumn.Name, objReader[xmlColumn.Name]); } } } ouput.Add(dic); //objReader.NextResult(); } objReader.Close(); objReader.Dispose(); } catch (Exception ex) { } finally { this.ConnectOracleDB.CloseDBConnect(); } return(ouput); }