Resources » .NET programming » ASP.NET/Web Applications

" )
Next

Using   force to field format as text, so if a got a content field with 02 in excel figure as 02 (text) and not like 2 (number).
I Hope you understand me and my poor english



Exporting Data to Excel with the Customized format. (in ASP.NET With C#)


Posted Date: 20-Aug-2004  Last Updated:   Category: ASP.NET/Web Applications    
Author: Member Level: Bronze    Points: 10


This article explains how to export data to Excel file (.xls) without using Excel Activex, custom control, and crystal report export option. Also this is not CSV(Comma-seperated Values) which doesn't have style formating in the output also you dont need to specify the content type for the output.



I have some data from a query, and I want to add header footer or any other information to put together to make an excel report. The normal ways to create excel file are using Excel control, Crystal Reports and exporting, or making a CSV file (here u cannot make the style formating)


The solution is: you make and format the content in the HTML format and save as XLS file, then open it(Response.redirect). It will open in Excel application with the format. Let us see how it Works


Here I have an Employee table



CREATE TABLE [dbo].[Empl1] (
[EmpNo] [int] NOT NULL ,
[EmpName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Sal] [int] NULL ,
[DeptId] [int] NULL
) ON [PRIMARY]
GO


It has the following data


1,Chris,35000,1
2,Priya,25000,1
3,Frederic Moni1,32000,3
4,srinivas,25000,1
5,Rajiv,34343,1


Now the following ASP.NET (C#) coding, makes a HTML formated output and saving in an XLS file. Finally it redirects to that file.



private void btnCreateExcelFile_Click(object sender, System.EventArgs e)
{
StringBuilder sbrHTML = new StringBuilder();
SqlConnection cn = new SqlConnection("Data Source=UrServer;uid=sa;pwd=pwd;Initial Catalog=UrDB");
SqlCommand dc = new SqlCommand("Select * From Empl",cn);
SqlDataReader dr ;
int i=0;

cn.Open();
dr = dc.ExecuteReader();
//Making HTML
sbrHTML.Append("<TABLE Border=1 ID="Table1">");
sbrHTML.Append("<TR><TD ColSpan=4><Font Size=5>Emp Info Report</Font></TD><TR>");
sbrHTML.Append("<TR><TH>Emp No</TH><TH>Emp Name</TH><TH>Salary</TH><TH>Dept Id</TH><TH></TR>");

while(dr.Read())
{
i++;
sbrHTML.Append("<TR><TD>" + dr.GetValue(0).ToString() + "</TD><TD>"
+ dr.GetValue(1).ToString() + "</TD><TD>"
+ dr.GetValue(2).ToString() + "</TD><TD>"
+ dr.GetValue(3).ToString() + "</TD><TD>"
+ "</TR>" );
}
sbrHTML.Append("</TABLE>");
sbrHTML.Append("<BR><BR><B>Total Number of Employees are " + i.ToString() + "</B>");
//ENDOF MAKING HTML
//WRITING AS AN XSL
StreamWriter swXLS =new StreamWriter( "FredXLS.xls");
swXLS.Write(sbrHTML.ToString());
swXLS.Close();

//OPENING THE XSL FILE
Response.Redirect( "FredXLS.xls");
}



Now It will show the output in Excel application.

When you want to create Lightweight reports with export in excel file with formatting you can follow this method. This is not only easy to implement but also less resource hungry.

Related Resources:


Read related articles: Export to Excel    

Did you like this resource? Share it with your friends and show your love!

Responses to "Exporting Data to Excel with the Customized format. (in ASP.NET With C#)"
Author: Sergio Mattioli    23 Aug 2004Member Level: Bronze   Points : 0
Good Article...
But how can do if want define properities of a cell, example wanna formating a cell of numbers as text field?
Tanks for your help, and sorry my poor english



Author: Sergio Mattioli    24 Aug 2004Member Level: Bronze   Points : 0
Tanks for answer my question of formating a excel field. But please can you explain me the question more detailed ?
Tanks in advance...
(my mail is smattioli@infosis-arg.com)



Author: Kamal Khan    24 Aug 2004Member Level: Bronze   Points : 0
Please can u tell me how i do the same thing using VB


Author: Frederic Moni    25 Aug 2004Member Level: Bronze   Points : 0
I dont know you are asking in VB.Net or VB
In VB.Net, only you have to do the syntax changes, and you can use the same classes.
In VB , the logic is same, but you may have to use ADO Recordset instead of DataReader.



Author: Sergio Mattioli    26 Aug 2004Member Level: Bronze   Points : 0
I am using VB.NET, and to do the formating thing put:

Dim drv As DataRowView
For Each drv In view
sbrHTML.Append("
 " + drv(0).ToString()+ "" + drv(1).ToString()+ "" + format(drv(2),"#.##") + "" + format(drv(3),"#.##")+ "" + "
Author: Sergio Mattioli    26 Aug 2004Member Level: Bronze   Points : 0
I am using VB.NET, and to do the formating thing use   force to field format as text, so if a got a content field with 02 in excel figure as 02 (text) and not like 2 (number).
I Hope you understand me and my poor english




Author: Sergio Mattioli    26 Aug 2004Member Level: Bronze   Points : 0
I am using VB.NET, and to do the formating thing use &n-b-s-p; (without "-") to force to field format as text, so if a got a content field with 02 in excel figure as 02 (text) and not like 2 (number).
I Hope you understand me and my poor english




Author: repalley    31 Aug 2004Member Level: Bronze   Points : 0
i tried this but its not showing content in excel sheet why???
in debug mode i noticed that its going into exception "thred has been aborted" it does'nt throw this error but its just going into exception block , do u think this was the reason how can i colve this

appreciate ur help



Author: Frederic Moni    01 Sep 2004Member Level: Bronze   Points : 0
Hi repalley
Can u tell me in which statement you are getting the error?? when u create the StreamWritter?
-Frederic



Author: repalley    03 Sep 2004Member Level: Bronze   Points : 0
figured it out

Thanks for reply



Author: repalley    03 Sep 2004Member Level: Bronze   Points : 0
figured it out

Thanks for reply



Author: Som    15 Sep 2004Member Level: Bronze   Points : 0
Excellent stuff. The output what we are getting is without any cell border in the Excel sheet and at first instatnce it doesn't seem to be an excel sheet at all. How can we add color to the cells through code. Thanx in advance.


Author: Som    17 Sep 2004Member Level: Bronze   Points : 0
Let me tell u the problem in detail. This page will be accessed by number of users. So I have concatenated the current date and the session Id with the file name. Now everyday if the report is generated the number of files will be piled up in the server. So after the excel files are generated it should be deleted also. My problem is how to delete the files after the reports are generated.
Thanks in advance.....



Author: Dimple Koshti    08 Oct 2004Member Level: Bronze   Points : 0
I have question regarding exporting ASP reports to Excel. The code works well. All text formatting are getting exported but the button format is not working, means the formatting of the button is not getting exported. Can u help for this?

Thanks in advance.

Dimple.



Author: Frederic Moni    08 Oct 2004Member Level: Bronze   Points : 0
you can delete those files in the session on end event.


Author: Adolfo López    28 Oct 2004Member Level: Silver   Points : 0
I saw that excel file is opened in a webform, is there a way to open it in an excel window with all excel control?


Author: Siódemak Przemyslaw    25 Nov 2004Member Level: Bronze   Points : 0
Hi, article is very good. I have a question related with this. Is it possible to create in this way some sheets in one excel file?



Author: Denny Eapen    09 Feb 2005Member Level: Bronze   Points : 0
"Access to the path "C:\WINNT\system32\FredXLS.xls" is denied."

This happenend at this statement.
Dim swXLS As New System.IO.StreamWriter("FredXLS.xls")



Author: Rajesh Singh    08 Oct 2008Member Level: Silver   Points : 2
I have also tried in my web application using c# with this concept. But My requirement has some changed. String Builder is loaded first then I have a Export Button and I want to export after clicking on that but "C:\WINNT\system32\FredXLS.xls" is denied." error is occured. Please give me proper solution.

Thanks In Advance.




Author: Filip    20 Aug 2009Member Level: Silver   Points : 2
Hi, you can try using GemBox, It is very easy to use and works very fast, much faster then Excel Interop/Automation. It also supports number of formats (xls,xlsx,csv,html,odf) and easily converts between them.


Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Submit Article     Return to Article Index

    Subscribe to Subscribers
    Active Members
    TodayLast 7 Daysmore...

    Awards & Gifts
    Talk to Webmaster Tony John

    Online Members

    Raju
    More...
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India