formato exportar encabezados con c# asp.net excel gridview

encabezados - exportar datagridview a excel c# con formato



Problemas al exportar datos de GridView en Excel C# (5)

Problemas al exportar los datos de gridview en excel. Está exportando toda la página, no los datos de Gridview.

Mi código como a continuación:

Response.Clear(); Response.Buffer = true; Response.ClearContent(); Response.ClearHeaders(); Response.Charset = ""; StringWriter strwritter = new StringWriter(); HtmlTextWriter htmlwritter = new HtmlTextWriter(strwritter); Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = "application/ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", "DSR"+DateTime.Now.ToString("dd-MM-yyyy")+".xls")); GridView1.GridLines = GridLines.Both; GridView1.HeaderStyle.Font.Bold = true; GridView1.RenderControl(htmlwritter); Response.Write(strwritter.ToString()); Response.End();


Este código puede ser de ayuda

protected void btnExportExcel_Click(object sender, EventArgs e) { BindData(); GridView1.Visible = true; string FileName = "Deal Report_(" + DateTime.Now.AddHours(5).ToString("yyyy-MM-dd") + ").xls"; Response.Clear(); Response.Buffer = true; Response.AddHeader("content-disposition", "attachment;filename=" + FileName); Response.Charset = ""; Response.ContentType = "application/vnd.ms-excel"; StringWriter sw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(sw); GridView1.HeaderRow.Style.Add("color", "#FFFFFF"); GridView1.HeaderRow.Style.Add("background-color", "#1F437D"); for (int i = 0; i < GridView1.Rows.Count; i++) { GridViewRow row = GridView1.Rows[i]; row.BackColor = System.Drawing.Color.White; row.Attributes.Add("class", "textmode"); if (i % 2 != 0) { for (int j = 0; j < row.Cells.Count; j++) { //row.Cells[j].Style.Add("background-color", "#eff3f8"); } } } GridView1.RenderControl(hw); string style = @"<style> .textmode { mso-number-format:/@; } </style>"; Response.Write(style); Response.Output.Write(sw.ToString()); Response.End(); GridView1.Visible = false; }


Prueba esto, funcionará ...

private void Export_To_Excel() { Microsoft.Office.Interop.Excel._Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; try { worksheet = workbook.ActiveSheet; worksheet.Name = "ExportedFromDatGrid"; int cellRowIndex = 1; int cellColumnIndex = 1; ////Loop through each row and read value from each column. for (int i = 0; i < this.dGV.Rows.Count - 1; i++) { for (int j = 0; j < this.dGV.Columns.Count; j++) { //// Excel index starts from 1,1. As first Row would have the Column headers, adding a condition check. if (cellRowIndex == 1) { worksheet.Cells[cellRowIndex, cellColumnIndex] = this.dGV.Columns[j].HeaderText; worksheet.Cells[cellRowIndex, cellColumnIndex].Font.FontStyle = FontStyle.Bold; } else { worksheet.Cells[cellRowIndex, cellColumnIndex] = this.dGV.Rows[i].Cells[j].Value.ToString(); } cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; } worksheet.Columns.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; worksheet.Columns.AutoFit(); ////Getting the location and file name of the excel to save from user. SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx"; saveDialog.FilterIndex = 2; if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful", "Info", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1); } } catch (System.Exception ex) { MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1); } finally { excel.Quit(); workbook = null; excel = null; } }


Puedes intentar hacer algo como eso. Simple y directo.

Response.Clear(); Response.Buffer = true; Response.ClearContent(); Response.ClearHeaders(); Response.Charset = ""; StringWriter strwritter = new StringWriter(); HtmlTextWriter htmlwritter = new HtmlTextWriter(strwritter); Response.Cache.SetCacheability(HttpCacheability.NoCache); StringWriter strwritter = new StringWriter(); HtmlTextWriter htmlwritter = new HtmlTextWriter(strwritter); Response.Cache.SetCacheability(HttpCacheability.NoCache); Response.ContentType = "application/ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", "DSR"+DateTime.Now.ToString("dd-MM-yyyy")+".xls")); GridView1.RenderBeginTag(htmlwritter); GridView1.HeaderRow.RenderControl(htmlwritter); foreach (GridViewRow row in GridView1.Rows) { row.RenderControl(htmlwritter); } GridView1.FooterRow.RenderControl(htmlwritter); GridView1.RenderEndTag(htmlwritter); Response.Write(strwritter.ToString()); Response.End();


Una solución simple a este problema es hacer que su código C # escriba la fuente de datos de GridView en un archivo de Excel.

Aquí está la biblioteca C # que escribí para hacer exactamente eso: Exportar a Excel

Todo el código fuente se proporciona de forma gratuita, y solo necesita agregar algunas líneas a su código ASP.Net:

// The following ASP.Net code gets run when I click on my "Export to Excel" button. protected void btnExportToExcel_Click(object sender, EventArgs e) { // It doesn''t get much easier than this... CreateExcelFile.CreateExcelDocument(listOfEmployees, "Employees.xlsx", Response); }

La otra ventaja de esto es que creará un archivo .xlsx "real" (usando la biblioteca OpenDocument). El inconveniente es que esas bibliotecas de Microsoft pesan aproximadamente 5 Mb.


Aquí un ejemplo que usa EPPlus . Simplemente ingrese una consulta SQL (o nombre de procedimiento almacenado) y un nombre de archivo para exportToExcel a exportToExcel y obtendrá un archivo de Excel. exportToExcel("SELECT * FROM yourTable", "myFileName");

using OfficeOpenXml; using OfficeOpenXml.Style; using System; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Globalization; using System.Web; //=== create an excel document ========================================= public static void exportToExcel(string sqlQuery, string fileName) { HttpResponse Response = HttpContext.Current.Response; DataTable dt = loadExternalDataTable(sqlQuery); using (ExcelPackage p = new ExcelPackage()) { //create a new workbook p.Workbook.Properties.Author = "VDWWD"; p.Workbook.Properties.Title = fileName; p.Workbook.Properties.Created = DateTime.Now; //create a new worksheet p.Workbook.Worksheets.Add(fileName); ExcelWorksheet ws = p.Workbook.Worksheets[1]; ws.Name = fileName; ws.Cells.Style.Font.Size = 11; ws.Cells.Style.Font.Name = "Calibri"; createExcelHeader(ws, dt); createExcelData(ws, dt); ws.Cells[ws.Dimension.Address].AutoFitColumns(); //make all columms just a bit wider, they would sometimes not fit for (int col = 1; col <= ws.Dimension.End.Column; col++) { ws.Column(col).Width = ws.Column(col).Width + 1; } //send the file to the browser byte[] bin = p.GetAsByteArray(); Response.ClearHeaders(); Response.Clear(); Response.Buffer = true; Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-length", bin.Length.ToString()); Response.AddHeader("content-disposition", "attachment; filename=/"" + fileName + ".xlsx/""); Response.OutputStream.Write(bin, 0, bin.Length); Response.Flush(); Response.Close(); Response.End(); } } //=== create the excel sheet header row ========================================= private static void createExcelHeader(ExcelWorksheet ws, DataTable dt) { int colindex = 1; //loop all the columns foreach (DataColumn dc in dt.Columns) { var cell = ws.Cells[1, colindex]; //make the text bold cell.Style.Font.Bold = true; //make the background of the cell gray var fill = cell.Style.Fill; fill.PatternType = ExcelFillStyle.Solid; fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#BFBFBF")); //fill the cell with the text cell.Value = dc.ColumnName.ToUpper(); colindex++; } } //=== create the excel sheet data ========================================= private static void createExcelData(ExcelWorksheet ws, DataTable dt) { int colindex = 0; int rowindex = 1; //loop all the rows foreach (DataRow dr in dt.Rows) { colindex = 1; rowindex++; //loop all the columns foreach (DataColumn dc in dt.Columns) { var cell = ws.Cells[rowindex, colindex]; string datatype = dc.DataType.ToString(); //fill the cell with the data in the correct format, needs to be done here because the headder row makes every column a string otherwise if (datatype == "System.Decimal" || datatype == "System.Double" || datatype == "System.Float") { if (!string.IsNullOrEmpty(dr[dc.ColumnName].ToString())) cell.Value = Convert.ToDecimal(dr[dc.ColumnName]); cell.Style.Numberformat.Format = "0.00"; } else if (datatype == "System.Int16" || datatype == "System.Int32" || datatype == "System.Int64" || datatype == "System.Long") { if (!string.IsNullOrEmpty(dr[dc.ColumnName].ToString())) cell.Value = Convert.ToInt64(dr[dc.ColumnName]); } else if (datatype == "System.Bool" || datatype == "System.Boolean") { if (!string.IsNullOrEmpty(dr[dc.ColumnName].ToString())) cell.Value = Convert.ToBoolean(dr[dc.ColumnName]); ; } else if (datatype == "System.DateTime") { if (!string.IsNullOrEmpty(dr[dc.ColumnName].ToString())) cell.Value = Convert.ToDateTime(dr[dc.ColumnName]); cell.Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern; } else { cell.Value = dr[dc.ColumnName]; } colindex++; } } } //=== create a datatable from a query ========================================= public static DataTable loadExternalDataTable(string sqlQuery) { DataTable dt = new DataTable(); using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString())) using (SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, connection)) { try { adapter.Fill(dt); } catch { } } return dt; }