How do I add header and footer in Excel with C#

Posted by Lacy under C# on 5/7/2012 | Points: 10 | Views : 13884 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Recently, I am bothered by inserting excel header and footer with C#. I tried, but I failed. The problem is that after adding the excel header and footer in my project. they do not show themselves. But when I view it in a print type, header and footer indeed exist. I do not know why. I use the below code and my Excel component is http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html .My code is shown below:


        Worksheet sheet = workbook.Worksheets[0];
        sheet.PageSetup.FullHeaderString = "Megan";
        sheet.PageSetup.IsPrintHeadings = true;
        sheet.Visibility = WorksheetVisibility.Visible;
        sheet.PageSetup.LeftFooter = "everlasting";

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Funda on: 5/8/2012 [Member] Starter | Points: 25

0	I dint get the clear picture of the objective, as far I understand we are trying to export the data as excel right ?. If so we can use this piece of code, which will do the simple excel download. Excel download Frame Work : 2.0,3.0 ,3.5,4.0 Inputs Can be: HTML Format. HTML Format: <html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:w='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'> <head> <title>Name</title> <body lang=EN-US style='mso-element:header' id=h1> <span style='mso--code:DATE'> </span> <div class=Section1> *************************Mandatory Fields Starts ****************************************************************** <table align='center' border='1' bordercolor='#00aeef' width='99%' class='reporttable1' cellspacing='0' cellpadding='0' style='font-size:10;'> <tr><td><b>Column 1</b></td> <td><b> Column 2</b></td></tr> <tr><td><b>Data 1</b></td><td><b>Data 2</b></td></tr> <tr><td><b> Data 3</b></td><td><b>Data 4</b></td></tr> </table> ***********************Mandatory Fields Ends******************************************************************** </body> </html> Code C#: Response.Buffer = false; Response.Expires = 0; Response.Clear(); Response.ContentType = "application/vnd.ms-excel"; Response.AppendHeader("Content-Length", InputData.Length.ToString()); Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName); Response.Write(str1.ToString()); Response.Flush(); Response.End(); Finaly : We are just writing the reponse in C# with following vlaues 1.Need to specfy the content type of the response 2.Need to give the length of the response 3.Need to tell the dispostion type as attachment to open as dailog box with [open save cancel]. Excel Download Data Source Provider : OleDb Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + full FileName Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'") ; OleDbSchemaGuid will return the Data Table. From Data Table we can do the DB work Lacy, if this helps please login to Mark As Answer. \| Alert Moderator

I dint get the clear picture of the objective, as far I understand we are trying to export the data as excel right ?.
If so we can use this piece of code, which will do the simple excel download.

Excel download
Frame Work : 2.0,3.0 ,3.5,4.0
Inputs Can be: HTML Format.
HTML Format:

<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:w='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>    

<head>

<title>Name</title>

<body lang=EN-US style='mso-element:header' id=h1>

<span style='mso--code:DATE'> </span>

<div class=Section1>

***************************Mandatory Fields Starts **********************************************************************

 <table  align='center' border='1' bordercolor='#00aeef' width='99%' class='reporttable1' cellspacing='0' cellpadding='0' style='font-size:10;'>

         <tr><td><b>Column 1</b></td> <td><b> Column 2</b></td></tr>

                 <tr><td><b>Data 1</b></td><td><b>Data 2</b></td></tr>

                <tr><td><b> Data 3</b></td><td><b>Data 4</b></td></tr>

</table>

***************************Mandatory Fields  Ends**********************************************************************

</body>

</html>





Code C#:

Response.Buffer = false;

Response.Expires = 0;

Response.Clear();

Response.ContentType = "application/vnd.ms-excel";

Response.AppendHeader("Content-Length", InputData.Length.ToString());

Response.AddHeader("Content-Disposition", "attachment; filename=" + FileName);

Response.Write(str1.ToString());

Response.Flush();

Response.End();

Finaly : We are just writing the reponse in C# with following vlaues
1.Need to specfy the content type of the response
2.Need to give the length of the response
3.Need to tell the dispostion type as attachment to open as dailog box with [open save cancel].

Excel Download

Data Source Provider : OleDb

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + full FileName Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'")

;
OleDbSchemaGuid will return the Data Table.
From Data Table we can do the DB work

Lacy, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Lacy on: 5/8/2012 [Member] Starter | Points: 25

0	Thanks Funda. I will give it a try. Really thanks for your patient reply. And I am a new member here. I will come back often. Lacy Lacy, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Lacy on: 5/10/2012 [Member] Starter | Points: 25

0	I am afraid that you misunderstood me, I just want to add header and footer in Excel file, not export the data. The problem that I met is that I add them,and they indeed exist, but they can not show in excel file. only use a print type, you can see them. Lacy, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Lacy on: 5/13/2012 [Member] Starter | Points: 25

0	Hi, please do not put your link which is absolutely not related to what I asked to be your answer.ok? Lacy, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts