asked on

Export to excel using C#

I've got a datagrid on my page along with a button which when clicked should generate the excel sheet. I've got the code to work for generating the excel sheet the same manner the datagrid is displayed. But there is one column in the datagrid which i need to get value for and then save to excel. For instance if the column has "1" as value, before exporting i need to check that and display and answer for it. For "1" it would be "xxxx", "2" - xxxxx..and so on

here's what ive :

private void Button1_Click(object sender, System.EventArgs e)
            {
                  Response.Clear();
                  Response.Buffer= true;
                  Response.ContentType = "application/vnd.ms-excel";
                  Response.Charset = "";
                  this.EnableViewState = false;

                  System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
                  System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

                  this.ClearControls(ItemList);
                  datagrid.RenderControl(oHtmlTextWriter);

                  Response.Write(oStringWriter.ToString());

                  Response.End();

inexplicable

ASKER

There's also another part to the question :

How can i use StreamWriter to create the file automatically and save to a location (server.mappath(./abc.xls)) ..?

Swapnil

Hi inexplicable,

May following will help you.

C# class library for exporting data to CSV/Excel file
http://www.codeproject.com/aspnet/ExportClassLibrary.asp

Export Data to CSV ( Excel )
http://www.dotnetspider.com/technology/kbpages/963.aspx

Cheers!

inexplicable

ASKER

thanks but actually i needed it in C# .

The first link does have it but its quite different from what i'm trying to achieve. Like i said above, i just need to grab the values from the datagrid. There should be an iteration through the rows and then eventually population of the datagrid.

GavinMannion

Q:How can i use StreamWriter to create the file automatically and save to a location (server.mappath(./abc.xls)) ..?

A:StreamWriter sw;
sw = File.CreateText(filePath);
sw.Write(Contents);
sw.Close();
return true;

From what I understand about your first question it is that you are already creating the Excel file no problem but you want to change some of the values before creating the file?

If this is the case then you can iterate through the datagrid before creating the excel file and change the values, then create the file as required.

Basically something along the lines of

foreach( Row r in DataGrid.Rows )
{
switch r.cells[0].Text
{
case "1":
r.cells[0].Text = "xxxx";
break;
}
}

hth

inexplicable

ASKER

Thanks for the streamwriter reply. I managed to get the answer from another post :

public void WriteFile(string data)
            {
                  string FILENAME = Server.MapPath("abc.xls");
                  System.IO.StreamWriter objStreamWriter = new System.IO.StreamWriter(FILENAME);
                  objStreamWriter.WriteLine(data);
                  objStreamWriter.Close();
            }

I guess the iteration process is right, but im getting to frame it properly. Logically it should be like this :

for each (condition)
if condition for cell1 and display cell2=""
if condition for cell2 and display cell2=""
display cell3

GavinMannion

Okay so you are saying that if Cell 1's value is X and Cell 2's value is Y then only display Cell 3?

If that is the case it would be much easier to just hit the DB again and return the actual grid that you want to display in Excel.

Or you are going to be spending a lot of time manipulating the HTML to wrte properly to Excel.

inexplicable

ASKER

No. I need ot display all 3 cells.

But for each cell there's a condition based on what's been displayed. For instance :

if cell(1).text = "hi" then
cell(1).text = "bye"
elseif cell(1).text = "hi" then
cell(1).text = "no-bye"

something like that

GavinMannion

Okay then the I would go for the following

foreach (DataRow dr in DataGrid.Rows)
{
switch (dr.ItemArray[0].ToString()) //Cell 1
{
case "Hi":
dr.ItemArray[0] = "Bye";
break;
case "Good":
dr.ItemArray[0] = "no-bye";
break;
}
switch (dr.ItemArray[1].ToString()) //Cell 2
{
case "test":
dr.ItemArray[0] = "Bye";
break;
}
}

It's not pretty but you can split it into some smaller methods for ease of use.

inexplicable

ASKER

Great. thanks!

Now can you guide me on how to integrate this into the code i posted since thats directly creating the excel sheet based on the existing datagrid. Out here since we checking the values and changing it, i guess we need to store it into a datatable ??
some code may help

ASKER CERTIFIED SOLUTION

GavinMannion

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial