public ActionResult Export() { var masterData = new SMOScopeBLL().Todos_completos_action_plan().Where(smo => smo.O_C.ToUpper() == "O" && (smo.ActionPlan != string.Empty && smo.ActionPlan != null)).ToList(); var file = new ExcelCore().GenerarActionReport("LARCA Action Plan Report", masterData); DownloadFile(file); return(Content("<script language='javascript' type='text/javascript'>alert('Exported!');document.location = 'Index';</script>")); }
public void TestClearToolSheet() { using (ExcelCore excelCore = new ExcelCore()) { excelCore.OpenExcel(@"C:\Users\Administrator\Desktop\WorkFiles\Letter Automation\Control turnaround.xlsx", false); excelCore.SelectSheet("Sheet1"); IList <Row> rows = excelCore.GetSheetByRow(); } }
private void RunSqlQueryProc(object o) { try { using (new WaitCursor()) { Task runQuery = new Task(() => { using (ExcelCore excelIn = new ExcelCore(Model.BaseModel.ExcelFileName, AppDataCore.Settings.AceVersion, Model.BaseModel.HDR)) { DataTable queryResult = new DataTable(); // Run query excelIn.RunSql(Model.BaseModel.SqlQuery, ref queryResult); // Populate result Model.QueryResult = queryResult; } }); runQuery.Start(); runQuery.Wait(); } WindowsUI.RunWindowDialog(() => { MessageBox.Show( StringsFunctions.ResourceString("resQueryCompleted"), StringsFunctions.ResourceString("resInfo"), MessageBoxButton.OK, MessageBoxImage.Information ); } ); } catch (Exception ex) { WindowsUI.RunWindowDialog(() => { MessageBox.Show( StringsFunctions.ResourceString("resErrorDuringExecution") + Environment.NewLine + Environment.NewLine + ex.InnerException.Message, StringsFunctions.ResourceString("resError"), MessageBoxButton.OK, MessageBoxImage.Hand ); } ); } }
/// <summary> /// Verbose output -- show result from datatable /// </summary> /// <param name="data"></param> public void DisplayResult(DataTable data) { using (ExcelCore excel = new ExcelCore()) { excel.IterateOverData(data, delegate(string value, int x, int y) { if (x == 1) { Console.WriteLine(); } Console.Write(value + "\t"); } ); } }
public void ExcelCoreTest() { using (ExcelCore excelCore = new ExcelCore()) { excelCore.OpenExcel(@"C:\Users\Administrator\Desktop\TestExcel\Letter Automation V1.19.1.xlsm", false); IList <string> sheetNames = excelCore.GetSheetNames(); excelCore.SelectSheet("Data"); //DeptComboBox IList <Cell> language = excelCore.GetRangeByName("Language"); IList <Cell> departmentLevel = excelCore.GetRange(new Cell() { ColumnName = "K", RowIndex = 42 }, new Cell() { ColumnName = "K", RowIndex = 46 }); IList <Cell> clientLevel = excelCore.GetRange(new Cell() { ColumnName = "L", RowIndex = 40 }, new Cell() { ColumnName = "L", RowIndex = 41 }); IList <Cell> EELetterlevel = excelCore.GetRange(new Cell() { ColumnName = "M", RowIndex = 40 }, new Cell() { ColumnName = "M", RowIndex = 48 }); IList <Cell> EETransactionlevel = excelCore.GetRange(new Cell() { ColumnName = "N", RowIndex = 40 }, new Cell() { ColumnName = "N", RowIndex = 72 }); } }
/// <summary> /// Display common information about Excel worksheets /// </summary> /// <param name="excel"></param> public void DisplayWorksheetInfo(ExcelCore excel) { List <string> worksheets = excel.GetWorksheets(); Console.WriteLine("List of available worksheets in file \"{0}\":", excel.FileName); foreach (String name in worksheets) { Console.WriteLine("\t\"{0}\"", name); ExcelObject.Worksheet worksheet = excel.GetWorksheet(name); Console.WriteLine("\tLast row with data: {0}; Last column with data: {1}\n", excel.GetCountOfRows(worksheet), excel.GetCountOfCols(worksheet) ); } }
private void RunAnalyticsProc(object o) { try { using (new WaitCursor()) { using (ExcelCore excelIn = new ExcelCore(Model.BaseModel.ExcelFileName, AppDataCore.Settings.AceVersion, Model.BaseModel.HDR)) { List <string> worksheets = excelIn.GetWorksheets(); Model.WorksheetItems.Clear(); foreach (String name in worksheets) { WorksheetItemType worksheetItem = new WorksheetItemType(); worksheetItem.WorksheetName = name; ExcelObject.Worksheet worksheet = excelIn.GetWorksheet(name); worksheetItem.RowCount = excelIn.GetCountOfRows(worksheet); worksheetItem.ColCount = excelIn.GetCountOfCols(worksheet); Model.WorksheetItems.Add(worksheetItem); } } } } catch (Exception ex) { WindowsUI.RunWindowDialog(() => { MessageBox.Show( String.Format(StringsFunctions.ResourceString("resErrorDuringOpening"), Model.BaseModel.ExcelFileName) + Environment.NewLine + Environment.NewLine + ex.Message, StringsFunctions.ResourceString("resError"), MessageBoxButton.OK, MessageBoxImage.Hand ); } ); } }
public ActionResult Upload(Larca2.Views.ViewModels.ManualUploadViewModel model) { if (Request.Files.Count > 0) { var file = Request.Files[0]; if (file != null && file.ContentLength > 0 && new MailingCore().GetXmlMimes().Exists(s => s == file.ContentType)) { // Generamos el archivo fisico y lo guardamos en un directorio var date = DateTime.Now.ToString("ddMMMyyy"); var random = new Random().Next(0, 1000); string fileName = String.Format("Manual_{0}_MasterData_{1}.xlsx", date, random); string filePath = Path.Combine(Server.MapPath("~/App_Data/"), fileName); file.SaveAs(filePath); ViewBag.Result = "File Uploaded and Processed!"; if (!model.Parcial) { var rows = new ExcelCore().ProcesarExcel(fileName, TipoProceso.Total); ViewData["MasterRows"] = rows; ViewBag.Result = rows.Count > 0 ? "Complete File Uploaded and Processed!" : "No Rows Processed. Check XLS Reading Parameters."; } else { var rows = new ExcelCore().ProcesarExcel(fileName, TipoProceso.Parcial); ViewData["MasterRows"] = rows; ViewBag.Result = rows.Count > 0 ? "Partial File Uploaded and Processed!" : "No Rows Processed. Check XLS Reading Parameters."; } } else { ViewBag.Result = "Invalid File! Try again."; } } if (ViewData["MasterRows"] == null) { ViewData["MasterRows"] = new List <MasterRow>(); } return(View("Index")); }
public ActionResult Export() { var model = new Larca2.Views.ViewModels.SMOScopeViewModel(); /*try * { * ViewData["MasterRows"] = new SMOScopeBLL().Todos().ToList(); * } * catch * { * ViewData["MasterRows"] = new List<LARCA20_SmoScope>(); * }*/ //Declaro BLLs e inicializo viewModel Larca2.Views.ViewModels.SMOScopeViewModel viewModel = new Larca2.Views.ViewModels.SMOScopeViewModel(); LARCA2.Business.Services.UsuariosBLL repoUsuarios = new LARCA2.Business.Services.UsuariosBLL(); LARCA2.Business.Services.UserOwnerBLL uobll = new LARCA2.Business.Services.UserOwnerBLL(); LARCA2.Business.Services.SMOScopeBLL ssbll = new LARCA2.Business.Services.SMOScopeBLL(); //Reviso el usuario logueado, sino como prueba traigo al de ID 2 LARCA2.Data.DatabaseModels.LARCA20_Users user = (LARCA2.Data.DatabaseModels.LARCA20_Users)Session["Usuario"]; if (user == null) { user = repoUsuarios.Traer(2); } //determino el rol del usuario para entender qué filtros y funcionalidades disponer LARCA2.Business.Services.RolesBLL robll = new LARCA2.Business.Services.RolesBLL(); LARCA2.Business.Services.UsuariosRolesBLL urbll = new LARCA2.Business.Services.UsuariosRolesBLL(); viewModel.userRole = robll.Traer(urbll.Traer(user.Id).RefIdRoles).Description; viewModel.idRole = robll.Traer(urbll.Traer(user.Id).RefIdRoles).Id; //Obtengo los registros de User Owner con IdUser igual al del usuario logueado List <LARCA2.Data.DatabaseModels.LARCA20_User_Owner> luo = uobll.TraerPorIdUsuario(user.Id); // Quito de la lista de SMO y BU de los filtros aquellos no contemplados por un registro existente de UserOwner para el usuario logueado // viewModel.SMOList = viewModel.SMOList.Where(x => luo.Exists(y => y.IdSmo.ToString() == x.Value) || x.Value == "0").ToList(); // viewModel.BUList = viewModel.BUList.Where(x => luo.Exists(y => y.IdBU.ToString() == x.Value) || x.Value == "0").ToList(); //Filtro los registros de la tabla SmoScope en función del rol y los permisos para cada uno //Aquellos cuyos RefIdSMO, RefIdBU, y RefIdOwner coinciden con los de un registro de la tabla UserOwner para el usuario logueado, permanecen //Con que algunos de los campos en cuestion difiera, el registro de SmoScope ya no será mostrado. if (viewModel.idRole != 1 && viewModel.idRole != 3) { List <LARCA2.Data.DatabaseModels.LARCA20_SmoScope> smoscopeact; viewModel.RegistrosSMO = new List <LARCA2.Data.DatabaseModels.LARCA20_SmoScope>(); foreach (LARCA2.Data.DatabaseModels.LARCA20_User_Owner actualLuo in luo) { smoscopeact = ssbll.Filtrar(actualLuo.IdBU.ToString(), actualLuo.IdSmo.ToString(), viewModel.idRole.ToString()).Where(x => x.RefIdOwner == actualLuo.IdOwner).ToList(); if (smoscopeact != null) { viewModel.RegistrosSMO.AddRange(smoscopeact); } } } else { viewModel.RegistrosSMO = ssbll.Filtrar("0", "0", viewModel.idRole.ToString()).ToList(); } viewModel.RegistrosSMO = viewModel.RegistrosSMO.Distinct().ToList(); var masterData = viewModel.RegistrosSMO.ToList(); //var masterData = new SMOScopeBLL().Todos().ToList(); var file = new ExcelCore().GenerarExcelPendingAnalysis("LARCA Pending Analysis", masterData); DownloadFile(file); return(Content("<script language='javascript' type='text/javascript'>alert('Exported!');document.location = 'Index';</script>")); }
static void Main(string[] args) { CoreFunctions coreFunctions = new CoreFunctions(); // Parse command line params AppData appData = coreFunctions.ParseCommandLineParams(args); appData = coreFunctions.ValidateCommandLineParams(appData); if (appData.showHelp) { coreFunctions.ShowHelp(); } else { // Open file and ... try { using (ExcelCore excelIn = new ExcelCore(appData.inFile, appData.acever, appData.useHdr == "Y" ? true : false)) { if (appData.showInfo) { coreFunctions.DisplayWorksheetInfo(excelIn); } else { DataTable queryResult = new DataTable(); CancellationTokenSource tokenSource = new CancellationTokenSource(); try { Console.WriteLine("\nSQL Query execution running at: {0}", DateTime.Now.ToString()); // Start progress... coreFunctions.ShowProgressInConsole(tokenSource.Token); // Start query excelIn.RunSql(appData.query, ref queryResult); // Cancel progress coreFunctions.StopProgress(tokenSource); tokenSource = null; Console.WriteLine("\nSQL Query execution completed at: {0}", DateTime.Now.ToString()); if (appData.resultToFile) { excelIn.SaveResultToExcelFile(appData.outFile, queryResult); } else { coreFunctions.DisplayResult(queryResult); } } catch (Exception ex) { // Cancel progress if (tokenSource != null) { coreFunctions.StopProgress(tokenSource); } Console.WriteLine( "\nThe an error has been occured during executing the SQL query: \nSQL Query: \"{0}\"\nFile: \"{1}\"\nError message: {2}", appData.query, appData.inFile, ex.Message ); } } } } catch (Exception ex) { Console.WriteLine("\nThe an error has been occured during accessing Excel file \"{0}\"\nError message: {1}", appData.inFile, ex.Message); } } coreFunctions.ByeMessage(); }
public ActionResult LarcaNewsProcess(Larca2.Views.ViewModels.LAScopeViewModel model, string command, string txtType) { var smo = model.smo; var rbu = model.rbu; var type = txtType; var masterRows = new List <ReportRow>(); switch (command) { case "Filter by BU": masterRows = new ReportsBLL().LARCANews(string.Empty, rbu, string.Empty); break; case "Filter by SMO": if (smo != "1" && smo != "2") { masterRows = new ReportsBLL().LARCANews(smo, string.Empty, string.Empty); } else { masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, smo); } break; } if (command.Equals("Export Excel")) { if (rbu != "0") { masterRows = new ReportsBLL().LARCANews(string.Empty, rbu, string.Empty); } else { if (smo != "1" && smo != "2") { masterRows = new ReportsBLL().LARCANews(smo, string.Empty, string.Empty); } else { masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, smo); } } var file = new ExcelCore().GenerarExcel("LARCA News", masterRows); // Download DownloadFile(file); } else { if (command.Equals("Send Email")) { string datafin = ""; if (rbu != "0") { masterRows = new ReportsBLL().LARCANews(string.Empty, rbu, string.Empty); datafin = rbu; } else { if (smo != "1" && smo != "2") { masterRows = new ReportsBLL().LARCANews(smo, string.Empty, string.Empty); datafin = smo; } else { masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, smo); datafin = smo; } } var file = new ExcelCore().GenerarExcel("LARCA News", masterRows); new MailingCore().EnviarReporte(file, datafin); ViewBag.Result = "File Exported and sent!."; } else { if (command.Equals("Send All Reports")) { foreach (var item in model.SMOList) { if (item.Text == "LA") { item.Value = "1"; masterRows = new ReportsBLL().LARCANews(string.Empty, string.Empty, item.Value); var file = new ExcelCore().GenerarExcel("LARCA News", masterRows); new MailingCore().EnviarReporte(file, item.Text); } else { masterRows = new ReportsBLL().LARCANews(item.Value, string.Empty, string.Empty); var file2 = new ExcelCore().GenerarExcel("LARCA News", masterRows); new MailingCore().EnviarReporte(file2, item.Text); } } foreach (var item in model.BUList) { masterRows = new ReportsBLL().LARCANews(string.Empty, item.Value, string.Empty); var file = new ExcelCore().GenerarExcel("LARCA News", masterRows); new MailingCore().EnviarReporte(file, item.Text); } ViewBag.Result = "Multiple Files Exported and sent!."; } } } ViewData["MasterRows"] = masterRows; return(View("Index", model)); }
/// <summary> /// Metodo que se encarga de leer todos los emails de la casilla suministrada por P&G, levantara solo los que pertenezcan a LARCA /// </summary> /// <returns></returns> public List <MasterRow> ProcesarEmails() { var list = new List <MasterRow>(); const string subject = "XLS SAMPLE"; const int port = 110; var attachments = new ApplicationDataBLL().TraerCantidadAdjuntos(); var emailsService = new EmailsBLL(); using (var client = new Pop3Client()) { try { // Conexion al servidor de emails client.Connect(ServerPop, port, false); client.Authenticate(UserName, Password); int messageCount = client.GetMessageCount(); int tope = messageCount - 1000; if (tope < 0) { tope = 0; } for (int i = messageCount; i > tope; i--) { try { Message message = client.GetMessage(i); // Si el asunto es EL indicado (Capitalizando todo) if (message.Headers.Subject.ToUpper() == subject.ToUpper()) { // Si el email no se encuentra ya procesado en nuestra base if (!emailsService.EmailExiste(message.Headers.MessageId)) { // Recorremos los adjuntos y los descargamos (en caso que sea la cantidad indicada) var adjuntos = message.FindAllAttachments(); if (adjuntos.Count == attachments) { foreach (var adjunto in adjuntos) { // Si el adjunto contiene el MIME tipo Excel if (GetXmlMimes().Exists(s => s == adjunto.ContentType.MediaType)) { // Generamos el archivo fisico y lo guardamos en un directorio var date = DateTime.Now.ToString("ddMMMyyy"); var random = new Random().Next(0, 1000); string fileName = String.Format("{0}_MasterData_{1}.xlsx", date, random); string filePath = Path.Combine(HttpContext.Current.Server.MapPath("~/App_Data/"), fileName); var stream = new FileStream(filePath, FileMode.Create); var binaryStream = new BinaryWriter(stream); binaryStream.Write(adjunto.Body); stream.Close(); binaryStream.Close(); // Detectamos que tipo de proceso hay que aplicarle al archivo en base a su dia de la semana var email = new LARCA20_Emails(); if (DateTime.Now.DayOfWeek == DayOfWeek.Tuesday) { email.processtype = (int)TipoProceso.Parcial; } else { email.processtype = (int)TipoProceso.Total; } // Completamos la info del email para guardar en la base email.MessageID = message.Headers.MessageId; email.date = message.Headers.DateSent; email.deleted = false; email.filename = fileName; // Enviamos a procesar el Excel descargado del email try { list = new ExcelCore().ProcesarExcel(email.filename, (TipoProceso)email.processtype); email.processed = true; emailsService.Guardar(email); } catch { list = new List <MasterRow>(); email.processed = false; emailsService.Guardar(email); } break; } } } else { EnviarAlertaAdjuntos_Admin(message); // Enviar email que no esta la cantidad de adjuntos que corresponde } } } } catch (Exception ex) { // Logggear Excepcion } } } catch (Exception ex) { // Logggear Excepcion } return(list); } }