Solved

C# export to excel rename Excel sheets/tabs

Posted on 2011-09-26
33
3,254 Views
Last Modified: 2013-12-14
I have some code that exports contents of a gridview to excel, and I was just wondering how I would go about programmatically changing the name of the sheets/tabs in Excel when it is exported.  Thanks!
0
Comment
Question by:zintech
  • 18
  • 14
33 Comments
 
LVL 17

Expert Comment

by:andrewssd3
Comment Utility
It would help if you could post a snippet of your export code  - if we could see how you are referencing your Excel workbook, it should be quite easy to rename the tabs
0
 

Author Comment

by:zintech
Comment Utility
Here is the actual code:

public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();

        HttpContext.Current.Response.Cache.SetExpires(DateTime.UtcNow.AddDays(-1));
        HttpContext.Current.Response.Cache.SetValidUntilExpires(false);
        HttpContext.Current.Response.Cache.SetRevalidation(HttpCacheRevalidation.AllCaches);


        HttpContext.Current.Response.AddHeader(
            "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a form to contain the grid
                Table table = new Table();

                table.GridLines = gv.GridLines;


                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewRow temp = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Normal);
                    TableCell tempcell = new TableCell();
                    TableCell tempcell2 = new TableCell();
                    tempcell.Text = "column1";
                    temp.Cells.Add(tempcell);
                    tempcell2.Text = "column2";
                    temp.Cells.Add(tempcell2);
                    table.Rows.Add(temp);
                }

                  //add each of the data rows to the table
                gv.AllowPaging = false;
                foreach (GridViewRow row in gv.Rows)
                {
                    gv.AllowPaging = true;
                    GridViewRow temp = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Normal);
                    TableCell tempcell = new TableCell();
                    TableCell tempcell2 = new TableCell();
                    tempcell.Text = row.Cells[3].Text + "-";
                    tempcell2.Text = row.Cells[4].Text;
                    temp.Cells.Add(tempcell);
                    temp.Cells.Add(tempcell2);
                    table.Rows.Add(temp);
                }
                gv.AllowPaging = true;
                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Hello, you can't do that by using your posted code, if you want to be able to change the sheet name then you need to create a real excel file, you can do that easily with OLEDB, see the next example (you will need the System.Data.OleDb namespace):
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\test.xls;Extended Properties='Excel 8.0;HDR=Yes'"))
{
    conn.Open();
    // Create a sheet named "MyData"
    OleDbCommand cmd = new OleDbCommand("CREATE TABLE [MyData] ([MyColumn1] string, [MyColumn2] string, [MyColumn3] integer)", conn);
    cmd.ExecuteNonQuery();

    // Insert data in the sheet named "MyData".
    cmd = new OleDbCommand("INSERT INTO [MyData] ([MyColumn1], [MyColumn2], [MyColumn3]) VALUES (?, ?, ?)", conn);
    OleDbParameter param1 = cmd.Parameters.Add(null, OleDbType.BSTR);
    OleDbParameter param2 = cmd.Parameters.Add(null, OleDbType.BSTR);
    OleDbParameter param3 = cmd.Parameters.Add(null, OleDbType.Integer);
    for (int i = 0; i < 10; i++)
    {
        param1.Value = "My Col 1 - " + i;
        param2.Value = "My Col 2 - " + i;
        param3.Value = i;
        cmd.ExecuteNonQuery();
    }
}

Open in new window


That code will create an Excel file named Test.xls in your C: root with 3 columns and some example data.

Of course after create your excel file you will need to send it back to the client browser, you can use Response.TransmitFile to do that, something like this:
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Cache.SetExpires(DateTime.UtcNow.AddDays(-1));
HttpContext.Current.Response.Cache.SetValidUntilExpires(false);
HttpContext.Current.Response.Cache.SetRevalidation(HttpCacheRevalidation.AllCaches);

HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";

HttpContext.Current.Response.TransmitFile("PathToMyExcelFile");

Open in new window


I hope this help.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
I forgot:
HttpContext.Current.Response.End();

Open in new window

0
 

Author Comment

by:zintech
Comment Utility
I will attempt this method, thank you
0
 

Author Comment

by:zintech
Comment Utility
In looking at this code, it appears that you have the code reading from the contents of an excel file and simply writing it to another excel file.  My situation is different.  I have the data to be exported inside of a Gridview.  I will have to modify this code accordingly to see if it willl work
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Hello, in my example if the excel file don't exist, it is created, where do you see that i'm reading an excel file? the previous example just do this:
1. Create an empty Excel file.
2. Create a work sheet named MyData with 3 columns defined.
3. Insert sample data in MyData sheet.
0
 

Author Comment

by:zintech
Comment Utility
In the connection string named "conn", you are defining in the connection string that the data source itself is from an Excel file located on the C:\ drive.  The data I will be reading is coming from a GridView, not an excel file
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Hello zin, I'm not reading any excel file (unless this already exist in that location, if not, it will be created).

I will write a better example for you.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Hello buddy, I did this fully functional example for you, give it a try:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            // Test data...
            DataTable dtt = new DataTable();
            dtt.Columns.Add("Name", typeof(string));
            dtt.Columns.Add("Age", typeof(int));

            dtt.Rows.Add("Anny", 22);
            dtt.Rows.Add("Tom", 35);
            dtt.Rows.Add("Jason", 21);
            dtt.Rows.Add("Maria", 45);

            GridView1.DataSource = dtt;
            GridView1.DataBind();
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        ExportToExcel("MyDataSheet", GridView1);
    }

    void ExportToExcel(string sheetName, GridView gv)
    {
        string myTempFile = System.IO.Path.Combine(System.IO.Path.GetTempPath(), Guid.NewGuid().ToString() + ".xls");
        try
        {
            using (OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + myTempFile + "';Extended Properties='Excel 8.0;HDR=Yes'"))
            {
                cn.Open();
                
                // Get de column names.
                StringCollection myColumnNames = new StringCollection();
                foreach (TableCell cell in gv.HeaderRow.Cells)
                    myColumnNames.Add("[" + cell.Text + "]");

                // Build Sql for sheet creation                                
                StringBuilder sbSql = new StringBuilder();
                foreach (string columnName in myColumnNames)
                {
                    if (sbSql.Length > 0)
                        sbSql.Append(", ");

                    sbSql.Append(columnName).Append(" string");
                }
                sbSql.Insert(0, "CREATE TABLE [" + sheetName + "] (");
                sbSql.Append(")");

                // Create a work sheet
                OleDbCommand cmd = new OleDbCommand(sbSql.ToString(), cn);
                cmd.ExecuteNonQuery();


                // Build Sql for data insertion                                
                sbSql.Clear();
                StringBuilder sbSqlParameters = new StringBuilder();
                foreach (string columnName in myColumnNames)
                {
                    if (sbSql.Length > 0)
                        sbSql.Append(", ");

                    if (sbSqlParameters.Length > 0)
                        sbSqlParameters.Append(", ");

                    sbSql.Append(columnName);
                    sbSqlParameters.Append("?");
                }
                sbSql.Insert(0, "INSERT INTO [" + sheetName + "] (");
                sbSql.Append(") VALUES (");
                sbSql.Append(sbSqlParameters.ToString());
                sbSql.Append(")");
                cmd = new OleDbCommand(sbSql.ToString(), cn);
                
                // Create parameters
                foreach (string columnName in myColumnNames)
                    cmd.Parameters.Add(columnName, OleDbType.BSTR);

                // Copy data from your GridView to the Excel file
                foreach (GridViewRow row in gv.Rows)
                {
                    for (int i = 0; i < myColumnNames.Count; i++)
                        cmd.Parameters[i].Value = row.Cells[i].Text;
                    cmd.ExecuteNonQuery();
                }
            }

            // Send the excel file to the client.
            Response.Clear();
            Response.Cache.SetNoStore();
            Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(sheetName) + ".xls");
            Response.ContentType = "application/ms-excel";
            Response.BinaryWrite(System.IO.File.ReadAllBytes(myTempFile));
            Response.End();
        }
        finally
        {
            // Delete the temp file for cleanup purposes
            System.IO.File.Delete(myTempFile);
        }
    }
    
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        <br />
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Generate my excel file" />
    </div>
    </form>
</body>
</html>

Open in new window


Also you can download the full aspx page from this link:
ExportGridViewToExcel.zip

I hope this help you to solve the problem.
0
 

Author Comment

by:zintech
Comment Utility
My only question is the following line of code:

string myTempFile = System.IO.Path.Combine(System.IO.Path.GetTempPath(), Guid.NewGuid().ToString() + ".xls");

Where does this create the file at?  On the server?
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Yes, also this file is temporary, at the end of the code I delete it.
0
 

Author Comment

by:zintech
Comment Utility
The only issue I am facing is the StringBuilder and StringCollection namespaces.  I do not know which namespaces I need to use the import directives for
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
OK, namespaces:
System.Collections.Specialized (StringCollection)
System.Text (StringBuilder)
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
For the example you will need to add this lines at the top:
<%@ Import Namespace="System.Collections.Specialized" %>
<%@ Import Namespace="System.Text" %>

Open in new window

0
 

Author Comment

by:zintech
Comment Utility
I am clear on how all of the code works and what it does.  The only statement that is puzzling to me is

sbSql.Clear();

As the Stringbuilder namespace does not define a method called "Clear" this will not work so I simply removed it from the code.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Hello, buddy, sorry, i'm using the framework 4.0 (I guest that you are using 2.0), that is a new method, please, replace that lines with:
sbSql.length=0;

Open in new window

if you only delete that line the execution will fail.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
sbSql.Length = 0;

Open in new window

0
 

Author Comment

by:zintech
Comment Utility
When I run the code, I receive the error  

"'' is not a valid name.  Make sure that it does not include invalid characters or punctuation and that it is not too long."

The error location is the first occurrence of the statement

"cmd.ExecuteNonQuery();"

I don't understand the error as the query string is built in the code
0
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 500 total points
Comment Utility
Ok, I think that there are two reasons for that:
1. the parameter sheetName can't be empty.
2. A column in your GridView dont has a HeaderText, so that break the logic of my code, I dont check that scenario...

Please try with this and see if it adapt to your scenario:
void ExportToExcel(string sheetName, GridView gv)
{
    if (string.IsNullOrEmpty(sheetName))
        throw new ArgumentException("sheetName is required");

    string myTempFile = System.IO.Path.Combine(System.IO.Path.GetTempPath(), Guid.NewGuid().ToString() + ".xls");
    try
    {
        using (OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + myTempFile + "';Extended Properties='Excel 8.0;HDR=Yes'"))
        {
            cn.Open();

            // Get de column names.
            StringCollection myColumnNames = new StringCollection();
            int emptyHeaderCount = 0;
            foreach (TableCell cell in gv.HeaderRow.Cells)
            {
                if (!string.IsNullOrEmpty(cell.Text.Trim()))
                {
                    myColumnNames.Add("[" + cell.Text + "]");
                }
                else
                {
                    emptyHeaderCount++;
                    myColumnNames.Add("[NO_NAME_" + emptyHeaderCount + "]");
                }
            }

            // Build Sql for sheet creation                                
            StringBuilder sbSql = new StringBuilder();
            foreach (string columnName in myColumnNames)
            {
                if (sbSql.Length > 0)
                    sbSql.Append(", ");

                sbSql.Append(columnName).Append(" string");
            }
            sbSql.Insert(0, "CREATE TABLE [" + sheetName + "] (");
            sbSql.Append(")");

            // Create a work sheet
            OleDbCommand cmd = new OleDbCommand(sbSql.ToString(), cn);
            cmd.ExecuteNonQuery();


            // Build Sql for data insertion                                
            sbSql.Length = 0;
            StringBuilder sbSqlParameters = new StringBuilder();
            foreach (string columnName in myColumnNames)
            {
                if (sbSql.Length > 0)
                    sbSql.Append(", ");

                if (sbSqlParameters.Length > 0)
                    sbSqlParameters.Append(", ");

                sbSql.Append(columnName);
                sbSqlParameters.Append("?");
            }
            sbSql.Insert(0, "INSERT INTO [" + sheetName + "] (");
            sbSql.Append(") VALUES (");
            sbSql.Append(sbSqlParameters.ToString());
            sbSql.Append(")");
            cmd = new OleDbCommand(sbSql.ToString(), cn);

            // Create parameters
            foreach (string columnName in myColumnNames)
                cmd.Parameters.Add(columnName, OleDbType.BSTR);

            // Copy data from your GridView to the Excel file
            foreach (GridViewRow row in gv.Rows)
            {
                for (int i = 0; i < myColumnNames.Count; i++)
                    cmd.Parameters[i].Value = row.Cells[i].Text;
                cmd.ExecuteNonQuery();
            }
        }

        // Send the excel file to the client.
        Response.Clear();
        Response.Cache.SetNoStore();
        Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(sheetName) + ".xls");
        Response.ContentType = "application/ms-excel";
        Response.BinaryWrite(System.IO.File.ReadAllBytes(myTempFile));
        Response.End();
    }
    finally
    {
        // Delete the temp file for cleanup purposes
        System.IO.File.Delete(myTempFile);
    }
}

Open in new window

0
 

Author Comment

by:zintech
Comment Utility
That's what it is.  Using the original export code that I had, the first 3 columns were blank, or only had a space " " as the column name.  Now the error is "Field '&nbsp;' already exists in table 'Report.xls'. "  This means that the space character, ' ' is already used.  I guess I will have to come up with logic to tell it to add an extra space on to the end
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Change the line #18 of the previous example to:
if (!string.IsNullOrEmpty(cell.Text.Trim()) && !cell.Text.Equals("&nbsp;", StringComparison.InvariantCultureIgnoreCase))

Open in new window

And check the result.
0
 

Author Comment

by:zintech
Comment Utility
The results displayed, the only strange thing was that all column names displayed as "NO_NAME_x" which is a little strange.  I will check the logic
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
That is intentional, that is the default column name that I set for those columns that has an empty HeaderText property, by using this method (oledb) you can't create columns with a empty name, but may be you can use blank spaces to workaround that instead of .NO_NAME_x
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Try this, change the line #25 with this:
myColumnNames.Add("[" + new string(' ', emptyHeaderCount) + "]");

Open in new window

See if that work for you
0
 

Author Comment

by:zintech
Comment Utility
Changing that line threw an error, saying "' ' is not a valid name.  Make sure that it does not include invalid characters or punctuation and that it is not too long."
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Then by using this method, the columns of your excel file can't be empty.
0
 

Author Comment

by:zintech
Comment Utility
The way I did it in the old method was that I explicitly created two columns, and then filled the data from the two columns on the GridView I wanted.  I should be able to do this.  I will let you know when I am finished
0
 

Author Comment

by:zintech
Comment Utility
Then I suppose if I wanted to create a new Sheet in the Excel file all I would do is execute a new SQL statement with the "CREATE TABLE" directive correct?
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Yes, that is.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Of course the name of the sheets has to be unique.
0
 

Author Comment

by:zintech
Comment Utility
I should be able to take it from here.  Thank you greatly for all of your kind help throughout the process
0
 
LVL 17

Expert Comment

by:Carlos Villegas
Comment Utility
Glad to help buddy
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now