string strFileName = "LoginData.xls";
ds.Tables[0].TableName = "LoginUserData";
var stringXml = ExcelHelper.GetExcelXml(ds, strFileName);
Response.Clear();
Response.AppendHeader("content-Type", "application/vnt.ms-excel");
Response.AppendHeader("content-disposition", "attachment; filename=" + strFileName);
string headerTable = @"<Table><tr><td>Report Header</td></tr><tr><td></Table>";
Response.Write(headerTable);
Response.Write(stringXml);
Response.Flush();
Response.End();
-
string headerTable = @"<Table><tr><td>Report Header</td></tr><tr><td></Table>"; Response.Write(headerTable); after adding these the dataset value is coming one line not in table formatDinup– Dinup2017-03-31 07:39:54 +00:00Commented Mar 31, 2017 at 7:39
-
1How can you expect writing some HTML table tags to result in a valid Excel document?Uwe Keim– Uwe Keim2017-03-31 07:42:19 +00:00Commented Mar 31, 2017 at 7:42
-
Internet explorer allows you to export to excel when right-clicking on a table.Hanno– Hanno2017-03-31 07:52:12 +00:00Commented Mar 31, 2017 at 7:52
-
The Report i have is dynamic and and it come from database , which i have Stored in dataset .. from dataset But I not getting option add custom header in SQL ..Dinup– Dinup2017-03-31 08:15:35 +00:00Commented Mar 31, 2017 at 8:15
-
Possible duplicate of stackoverflow.com/a/9653194/1602237Hanno– Hanno2017-03-31 08:17:38 +00:00Commented Mar 31, 2017 at 8:17
|
Show 1 more comment
1 Answer
A better approach would probably be to use epplus and create the Excel document that way.
See this for the example below.
private void DumpExcel(DataTable tbl)
{
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true);
//Format the header for column 1-3
using (ExcelRange rng = ws.Cells["A1:C1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
}
//Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//Write it back to the client
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=ExcelDemo.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
}
}
