public IActionResult Export(string id)
{
var idCa = Guid.Empty;
if (!string.IsNullOrEmpty(id))
{
idCa = Guid.Parse(_protector.Unprotect(id));
}
string sWebRootFolder = _Env.WebRootPath;
var listado = _taskCampaignBusiness.ListTask(idCa).Select(x => new
{
x.StartDate,
x.Branch.Code,
x.Branch.ExternalCode,
x.Branch.TypeBusiness,
x.Branch.Name,
x.Branch.Cluster,
Propietario = x.Branch.PersonOwner.Name,
x.Branch.PersonOwner.Mobile,
x.Branch.PersonOwner.Phone,
Canton = x.Branch.District.Name,
Estado_Tarea = x.StatusTask.Name,
Encuestador = x.Pollster == null ? "Sin Indentificar" : x.Pollster.Name
, Estado_c = x.PollsTaskss.First().pollsstatus
, Commentario = x.PollsTaskss.First().Comment
}).ToList();
var log = DateTime.Now;
string LogFile = log.ToString("yyyyMMddHHmmss");
string sFileName = @"Listado.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
if (file.Exists)
{
file.Delete();
file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
}
using (ExcelPackage package = new ExcelPackage(file))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Datos");
//First add the headers
Color colFromHex = System.Drawing.ColorTranslator.FromHtml("#B7DEE8");
worksheet.Column(1).Width = 20;
worksheet.Column(2).Width = 20;
worksheet.Column(3).Width = 20;
worksheet.Column(4).Width = 20;
worksheet.Column(5).Width = 32;
worksheet.Column(6).Width = 20;
worksheet.Column(7).Width = 20;
worksheet.Column(8).Width = 20;
worksheet.Column(9).Width = 20;
worksheet.Column(10).Width = 20;
worksheet.Column(11).Width = 20;
worksheet.Column(12).Width = 40;
worksheet.Cells[1, 1].Value = "Ciudad";
worksheet.Cells[1, 1].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 1].Style.Font.Bold = true;
worksheet.Cells[1, 1].Style.Font.Size = 12;
worksheet.Cells[1, 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 2].Value = "Cluster";
worksheet.Cells[1, 2].Style.Font.Size = 12;
worksheet.Cells[1, 2].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 2].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 2].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 2].Style.Font.Bold = true;
worksheet.Cells[1, 3].Value = "Cod. Encuesta";
worksheet.Cells[1, 3].Style.Font.Size = 12;
worksheet.Cells[1, 3].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 3].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 3].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 3].Style.Font.Bold = true;
worksheet.Cells[1, 4].Value = "PT_INDICE";
worksheet.Cells[1, 4].Style.Font.Size = 12;
worksheet.Cells[1, 4].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 4].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 4].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 4].Style.Font.Bold = true;
worksheet.Cells[1, 5].Value = "Nombre local";
worksheet.Cells[1, 5].Style.Font.Size = 12;
worksheet.Cells[1, 5].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 5].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 5].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 5].Style.Font.Bold = true;
worksheet.Cells[1, 6].Value = "Tipo de Negocio";
worksheet.Cells[1, 6].Style.Font.Size = 12;
worksheet.Cells[1, 6].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 6].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 6].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 6].Style.Font.Bold = true;
worksheet.Cells[1, 7].Value = "Telefono";
worksheet.Cells[1, 7].Style.Font.Size = 12;
worksheet.Cells[1, 7].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 7].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 7].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 7].Style.Font.Bold = true;
worksheet.Cells[1, 8].Value = "Encuestador";
worksheet.Cells[1, 8].Style.Font.Size = 12;
worksheet.Cells[1, 8].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 8].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 8].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 8].Style.Font.Bold = true;
worksheet.Cells[1, 9].Value = "Fecha";
worksheet.Cells[1, 9].Style.Font.Size = 12;
worksheet.Cells[1, 9].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 9].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 9].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 9].Style.Font.Bold = true;
worksheet.Cells[1, 10].Value = "Estado";
worksheet.Cells[1, 10].Style.Font.Size = 12;
worksheet.Cells[1, 10].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 10].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 10].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 10].Style.Font.Bold = true;
worksheet.Cells[1, 11].Value = "Estado Campo";
worksheet.Cells[1, 11].Style.Font.Size = 12;
worksheet.Cells[1, 11].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 11].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 11].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 11].Style.Font.Bold = true;
worksheet.Cells[1, 12].Value = "Observaciones Encuesta";
worksheet.Cells[1, 12].Style.Font.Size = 12;
worksheet.Cells[1, 12].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
worksheet.Cells[1, 12].Style.Fill.BackgroundColor.SetColor(colFromHex);
worksheet.Cells[1, 12].Style.Font.Color.SetColor(Color.White);
worksheet.Cells[1, 12].Style.Font.Bold = true;
int rows = 2;
foreach (var t in listado)
{
worksheet.Cells[rows, 1].Value = t.Canton;
worksheet.Cells[rows, 2].Value = t.Cluster;
worksheet.Cells[rows, 3].Value = t.Code;
worksheet.Cells[rows, 4].Value = t.ExternalCode;
worksheet.Cells[rows, 5].Value = t.Name;
worksheet.Cells[rows, 6].Value = t.TypeBusiness;
worksheet.Cells[rows, 7].Value = t.Phone;
worksheet.Cells[rows, 8].Value = t.Encuestador;
worksheet.Cells[rows, 9].Value = t.StartDate;
worksheet.Cells[rows, 9].Style.Numberformat.Format = "yyyy-mm-dd";
worksheet.Cells[rows, 10].Value = t.Estado_Tarea;
worksheet.Cells[rows, 11].Value = t.Estado_c;
worksheet.Cells[rows, 12].Value = t.Commentario;
rows++;
}
//Add values
package.Save(); //Save the workbook.
}
var result = PhysicalFile(Path.Combine(sWebRootFolder, sFileName), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
Response.Headers["Content-Disposition"] = new ContentDispositionHeaderValue("attachment")
{
FileName = file.Name
}.ToString();
return(result);
}
public IActionResult Export(string id)
{
var idCa = Guid.Empty;
if (!string.IsNullOrEmpty(id))
{
idCa = Guid.Parse(Protector.Unprotect(id));
}
string sWebRootFolder = _hostingEnv.WebRootPath;
var listado = _taskCampaignBusiness.ListTask(idCa).Select(x => new
{
x.StartDate,
x.Code,
x.Branch.TypeBusiness,
x.Branch.Name,
x.Branch.MainStreet,
x.Branch.Reference,
Propietario = x.Branch.PersonAdministration.Name,
x.Branch.PersonAdministration.Mobile,
x.Branch.PersonAdministration.Phone,
Provincia = x.Branch.Province.Name,
Canton = x.Branch.District.Name,
Sector = x.Branch.Sector.Name,
Estado_Tarea = x.StatusTask.Name,
Cedula = x.Branch.PersonAdministration.Document,
DISTRIBUIDOR = x.Campaign.Comment,
x.CodeGemini
}).ToList();
string sFileName = @"Listado.xlsx";
string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
if (file.Exists)
{
file.Delete();
file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
}
using (ExcelPackage package = new ExcelPackage(file))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Datos");
//First add the headers
worksheet.Cells[1, 1].Value = "Fecha de Captación";
worksheet.Cells[1, 2].Value = "Código Titán";
worksheet.Cells[1, 3].Value = "Tipo de negocio";
worksheet.Cells[1, 4].Value = "Nombre local";
worksheet.Cells[1, 5].Value = "Dirección";
worksheet.Cells[1, 6].Value = "Referencia";
worksheet.Cells[1, 7].Value = "Propietario Administrador";
worksheet.Cells[1, 8].Value = "Celular";
worksheet.Cells[1, 9].Value = "Telefono";
worksheet.Cells[1, 10].Value = "Provincia";
worksheet.Cells[1, 11].Value = "Canton";
worksheet.Cells[1, 12].Value = "Sector";
worksheet.Cells[1, 13].Value = "Captador";
worksheet.Cells[1, 14].Value = "gemini";
worksheet.Cells[1, 15].Value = "Estado";
worksheet.Cells[1, 16].Value = "Cédula o RUC";
worksheet.Cells[1, 17].Value = "DISTRIBUIDOR";
int rows = 2;
foreach (var t in listado)
{
worksheet.Cells[rows, 1].Value = t.StartDate;
worksheet.Cells[rows, 1].Style.Numberformat.Format = "yyyy-mm-dd";
worksheet.Cells[rows, 2].Value = t.Code;
worksheet.Cells[rows, 3].Value = t.TypeBusiness;
worksheet.Cells[rows, 4].Value = t.Name;
worksheet.Cells[rows, 5].Value = t.MainStreet;
worksheet.Cells[rows, 6].Value = t.Reference;
worksheet.Cells[rows, 7].Value = t.Propietario;
worksheet.Cells[rows, 8].Value = t.Mobile;
worksheet.Cells[rows, 9].Value = t.Phone;
worksheet.Cells[rows, 10].Value = t.Provincia;
worksheet.Cells[rows, 11].Value = t.Canton;
worksheet.Cells[rows, 12].Value = t.Sector;
worksheet.Cells[rows, 13].Value = "";
worksheet.Cells[rows, 14].Value = t.CodeGemini;
worksheet.Cells[rows, 15].Value = t.Estado_Tarea;
worksheet.Cells[rows, 16].Value = t.Cedula;
worksheet.Cells[rows, 17].Value = t.DISTRIBUIDOR;
rows++;
}
//Add values
package.Save(); //Save the workbook.
}
var result = PhysicalFile(Path.Combine(sWebRootFolder, sFileName), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
Response.Headers["Content-Disposition"] = new ContentDispositionHeaderValue("attachment")
{
FileName = file.Name
}.ToString();
return(result);
}