public static void List2Excel <T>(HttpResponseBase responsePage, IList <T> lista, String Titulo, String nombre, List <ReportColumnHeader> columnsNames, DataTable dt = null)
{
var filename = nombre + ".xls";
DataTable DataTablelista;
if (dt != null)
{
DataTablelista = dt;
}
else
{
DataTablelista = CollectionHelper.ConvertTo(lista);
}
List <DataColumn> lstIndexremoves = new List <DataColumn>();
for (int i = 0; i < DataTablelista.Columns.Count; i++)
{
DataColumn col = DataTablelista.Columns[i];
ReportColumnHeader mcolumn = columnsNames.Find(x => x.BindField == col.ColumnName);
if (mcolumn == null)
{
lstIndexremoves.Add(col);
}
else
{
DataTablelista.Columns[i].ColumnName = mcolumn.HeaderName;
}
}
for (int i = 0; i < lstIndexremoves.Count; i++)
{
DataTablelista.Columns.Remove(lstIndexremoves[i]);
}
var dgGrid = new DataGrid();
dgGrid.GridLines = GridLines.Both;
dgGrid.DataSource = DataTablelista;
dgGrid.HeaderStyle.BackColor = System.Drawing.ColorTranslator.FromHtml("#333333");
dgGrid.HeaderStyle.ForeColor = System.Drawing.Color.White;
dgGrid.DataBind();
var sb = new StringBuilder();
var Page = new Page();
var SW = new StringWriter(sb);
var htw = new HtmlTextWriter(SW);
var inicio = "<div> " +
"<table border=0 cellpadding=0 cellspacing=0>" +
"<tr>" +
"<td colspan=10 align=center><h1>Consulta de " + Titulo + "</h1></td>" +
"</tr>" +
"<tr>" +
"<td colspan=1 align=left style='font-weight:bold'>Fecha de generación:</td>" +
"<td colspan=2 align=left>" + DateTime.Now.ToLongDateString() + "</td>" +
"</tr>" +
"</table>" +
"</div>";
htw.Write(inicio);
var form = new HtmlForm();
responsePage.ContentType = "application/vnd.ms-excel";
responsePage.AddHeader("Content-Disposition", "attachment;filename=" + filename);
responsePage.Charset = string.Empty;
responsePage.ContentEncoding = Encoding.Default;
dgGrid.EnableViewState = false;
Page.EnableEventValidation = false;
Page.DesignerInitialize();
Page.Controls.Add(form);
form.Controls.Add(dgGrid);
Page.RenderControl(htw);
responsePage.Clear();
responsePage.Buffer = true;
responsePage.Write(sb.ToString());
responsePage.End();
}
public static void List2Excel3 <T>(HttpResponse responsePage, IList <T> lista, String Titulo, String nombre, List <ReportColumnHeader> columnsNames, DataTable dt = null)
{
var filename = nombre + ".xls";
DataTable DataTablelista;
if (dt != null)
{
DataTablelista = dt;
}
else
{
DataTablelista = CollectionHelper.ConvertTo(lista);
}
List <DataColumn> lstIndexremoves = new List <DataColumn>();
for (int i = 0; i < DataTablelista.Columns.Count; i++)
{
DataColumn col = DataTablelista.Columns[i];
ReportColumnHeader mcolumn = columnsNames.Find(x => x.BindField == col.ColumnName);
if (mcolumn == null)
{
lstIndexremoves.Add(col);
}
else
{
DataTablelista.Columns[i].ColumnName = mcolumn.HeaderName;
}
}
for (int i = 0; i < lstIndexremoves.Count; i++)
{
DataTablelista.Columns.Remove(lstIndexremoves[i]);
}
var dgGrid = new DataGrid();
//dgGrid.GridLines = GridLines.Both;
dgGrid.DataSource = DataTablelista;
//dgGrid.HeaderStyle.BackColor = System.Drawing.ColorTranslator.FromHtml("#333333");
//dgGrid.HeaderStyle.ForeColor = System.Drawing.Color.White;
dgGrid.DataBind();
foreach (DataGridItem gvrow in dgGrid.Items)
{
for (int k = 0; k < gvrow.Cells.Count; k++)
{
// gvrow.Cells[k].Style.Add("Height", "10");
//gvrow.Cells[k].Attributes.Add("class", "cost");
//string nombreColumna = dgGrid.Columns[k].HeaderText;
//if (nombreColumna == "CodSector")
//{
// var s = 2;
//}
gvrow.Cells[k].Style.Value = "mso-number-format:\\@;";
}
}
dgGrid.EnableViewState = false;
var sb = new StringBuilder();
var SW = new StringWriter(sb);
var htw = new HtmlTextWriter(SW);
var Page = new Page();
var form = new HtmlForm();
Page.EnableEventValidation = false;
Page.DesignerInitialize();
Page.Controls.Add(form);
form.Controls.Add(dgGrid);
Page.RenderControl(htw);
responsePage.Clear();
responsePage.Buffer = true;
responsePage.ContentType = "application/vnd.ms-excel";
responsePage.AddHeader("Content-Disposition", "attachment;filename=" + filename);
responsePage.Charset = "UTF-8";// string.Empty;
responsePage.Cache.SetCacheability(HttpCacheability.NoCache);
responsePage.ContentEncoding = System.Text.Encoding.Default;
int inicioTabla = 0;
int finTabla = 0;
string contenido = sb.ToString();
inicioTabla = contenido.IndexOf("<table");
finTabla = contenido.IndexOf("</table>");
contenido = contenido.Substring(inicioTabla, (finTabla + 8) - inicioTabla);
responsePage.Write(contenido);
// responsePage.Write(sb.ToString());
responsePage.End();
}
public static void List2Excel <T>(HttpResponseBase responsePage, IList <T> lista, String Titulo, String nombre, List <ReportColumnHeader> columnsNames, DataTable dt = null)
{
var filename = nombre;
DataTable DataTablelista;
if (dt != null)
{
DataTablelista = dt;
}
else
{
DataTablelista = CollectionHelper.ConvertTo(lista);
}
//ordena columnas dearcuerdo el backoffice
int contadorOrden = 0;
for (int i = 0; i < columnsNames.Count; i++)
{
string nombrecolumna = columnsNames[i].BindField;
DataColumn col = DataTablelista.Columns[nombrecolumna];
if (col != null)
{
DataTablelista.Columns[nombrecolumna].SetOrdinal(contadorOrden);
contadorOrden++;
}
}
//pone nombres a las columnas
List <DataColumn> lstIndexremoves = new List <DataColumn>();
for (int i = 0; i < DataTablelista.Columns.Count; i++)
{
DataColumn col = DataTablelista.Columns[i];
ReportColumnHeader mcolumn = columnsNames.Find(x => x.BindField == col.ColumnName);
if (mcolumn == null)
{
lstIndexremoves.Add(col);
}
else
{
if (mcolumn.FlgOculto == "1")
{
lstIndexremoves.Add(col);
}
else
{
DataTablelista.Columns[i].ColumnName = mcolumn.HeaderName;
}
}
}
//elimina columnas innecesarias.
for (int i = 0; i < lstIndexremoves.Count; i++)
{
DataTablelista.Columns.Remove(lstIndexremoves[i]);
}
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Hoja 1");
workSheet.Cells[1, 1].Value = "" + Titulo.Replace("_", " ").ToUpper().Trim() + "";
workSheet.Cells[1, 1].Style.Font.Bold = true;
workSheet.Cells[1, 1].Style.Font.Size = 24;
workSheet.Cells[1, 1].Style.Font.Name = "Calibri";
workSheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
workSheet.Cells["A1:J2"].Merge = true;
workSheet.Cells[3, 1].Value = "Fecha de generación:";
workSheet.Cells[3, 1].Style.Font.Bold = true;
workSheet.Cells[3, 1].Style.Font.Size = 11;
workSheet.Cells[3, 1].Style.Font.Name = "Calibri";
workSheet.Cells[3, 1].Style.WrapText = true;
workSheet.Cells[3, 2].Value = DateTime.Now.ToLongDateString();
workSheet.Cells[3, 2].Style.Font.Size = 11;
workSheet.Cells[3, 2].Style.Font.Name = "Calibri";
workSheet.Cells[3, 2].Style.WrapText = true;
workSheet.Cells[5, 1].LoadFromDataTable(DataTablelista, true);
workSheet.Cells.AutoFitColumns();
for (int i = 1; i <= DataTablelista.Columns.Count; i++)
{
workSheet.Column(i).AutoFit();
workSheet.Column(i).BestFit = true;
//workSheet.Cells[5, i].Style.Font.Bold = true;
workSheet.Cells[5, i].Style.Font.Color.SetColor(Color.White);
workSheet.Cells[5, i].Style.Fill.PatternType = ExcelFillStyle.Solid;
workSheet.Cells[5, i].Style.Fill.BackgroundColor.SetColor(Color.Black);
//workSheet.Cells[5, i].Style.Border.BorderAround(ExcelBorderStyle.Thin, System.Drawing.Color.Black);
workSheet.Cells[5, i].Style.Font.Size = 11;
workSheet.Cells[5, i].Style.Font.Name = "Calibri";
//workSheet.Cells[5, i].Style.WrapText = true;
}
workSheet.Column(1).Width = 20;
int maxpos = 6;
ExcelRange rangoTabla = workSheet.Cells[maxpos, 1, maxpos + DataTablelista.Rows.Count - 1, DataTablelista.Columns.Count];
//rangoTabla.Style.Font.Bold = true;
//rangoTabla.Style.Fill.BackgroundColor = colorNegro;
rangoTabla.Style.Fill.PatternType = ExcelFillStyle.Solid;
rangoTabla.Style.Font.Color.SetColor(Color.Black);
rangoTabla.Style.Fill.BackgroundColor.SetColor(Color.White);
rangoTabla.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
rangoTabla.Style.Font.Size = 11;
rangoTabla.Style.Font.Name = "Calibri";
rangoTabla.Style.WrapText = true;
responsePage.Clear();
using (MemoryStream memoryStream = new MemoryStream())
{
responsePage.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
responsePage.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", filename.Replace(" ", "_")));
excel.SaveAs(memoryStream);
memoryStream.WriteTo(responsePage.OutputStream);
responsePage.Flush();
responsePage.End();
}
}
