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(); } }