Need to Export Gridview1 box to Excel

[giasone777]

I have a webpage that is using ASP.net textboxes and an ASP.net GridView1. I am using C# to place the SQL data into the asp boxes, one of them being the GridView1 box.

On the web page there are several buttons that fetch VMware object information and then send the information to the gridview. What I want to do is have another button that sends the information from the GridView1 to an excel file.

None of the examples that I have searched for have worked - Please help, see below an example from my c# code - Thank-you - Jason.

Ok removed it.

 

[NoUserHere]

Come on now Jason...you have 10 posts behind you, it shows you are around long enough to know you should be using the code tags button when posting code to the forums....

 

[giasone777]

Here you go - Thanks again.

var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString);
connection.Open();
var command = new SqlCommand();
var query = new StringBuilder("SELECT db_owner.vms.name AS 'Vm Name', db_owner.vms.vmhost AS 'ESX Host', db_owner.host.Cluster, db_owner.vms.UID AS 'vCenter', db_owner.vms.PowerState AS 'Powered             ON/OFF', db_owner.vms.Guest AS 'Operating System'  FROM db_owner.vms JOIN db_owner.host ON db_owner.vms.VMHost = db_owner.host.Name");
var selectedIndices = objectList.GetSelectedIndices();
switch (selectedIndices.Length)
{
    case 0:
        break;
    case 1:
        query.Append(" WHERE vmhost LIKE @vmhost");
        command.Parameters.Add("@vmhost", SqlDbType.VarChar, 50).Value = $"%{objectList.SelectedItem.Text}%";
        break;
    default:
        for (var i = 0; i < selectedIndices.Length; i++)
        {
            var index = selectedIndices[i];
            //   var paramName = $"@UID";

            var paramName = $"@vmhost{index}";

            query.Append($" {(i == 0 ? "Where" : "OR")} VMHOST LIKE {paramName}");
            command.Parameters.Add(paramName, SqlDbType.VarChar, 50).Value = $"%{objectList.Items[index].Text}%";
        }
        break;
}
query.Append("  order by db_owner.host.Cluster ");
command.CommandText = query.ToString();
command.Connection = connection;
SqlDataReader dr = command.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();

 

[jmcilhinney]

I'm always amazed at the number people who think that it's a good idea to cut the leading whitespace of the first line of their code but leave it on the rest. Neither is a good idea. You can hold down the Alt key while selecting code in VS to remove whitespace from the whole block or the editor in this site will do it for you too. ALWAYS think about what you can do to help us help you, which includes making your code as readable as possible. I have fixed it for you on this occasion.

 

[jmcilhinney]

None of the examples that I have searched for have worked

You need to show us the code that think should have achieve what you're trying to do, so that we can see what might be wrong with that.

 

[giasone777]

I will remember the tip about the whitespace - Thank-you - Jason.

I found the following as an example and it doesn't work -

System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ClearContent();

Response.AddHeader("content-disposition", attachment);

dr.RenderControl(htw);
Response.Write(sw.ToString());
Response.Flush();
Response.End();

 

[Skydiver]

What? dr in post #3 is an SqlDataReader. That doesn't have a RenderControl() method. What is dr in post #6 such that it has an RenderControl() method? Again, as stated above, you need to give us enough code to see what you are doing.

Anyway, that approach there is for when you want to write the contents of an ASP.NET control into a file, and then let the user download that file. That is not what you want to do if you want to let the user download an Excel file.

The first question is does your user really need an Excel file or a just a .CSV file?

If it's just a .CSV file, then things are much easier. If it's an Excel file, do you need Excel 2003 and older for .XLS files, or can you use the newer .XLSX files? In both cases, it will be easier if you can find a library to let you build up the spreadsheet row by row.

If you need .XLS, DO NOT BE TEMPTED TO USE OFFICE AUTOMATION ON A WEB SERVER. Although it may seem to work when you try it out, it is not supported. Furthermore, it is against the Microsoft EULA that you agreed to when you installed Office. It will just give you headaches, and cause your operations guys to come roust you from your bed with torches and pitchforks when the web server starts acting really flakey and needs to be rebooted several times a day. Search around for various paid or free libraries for generating .XLS files.

If you need .XLSX, you could dig through the OpenXML documentation and figure out how to compose a spreadsheet. It is do-able if you have the time and inclination. I still recommend looking for a paid or free library to do this work for you, though.

 

[giasone777]

All I need is a .csv file, which can then be imported into Excel. Thanks. Jason.

 

[Skydiver]

Building a .CSV is easy. Instead of your call to dr.RenderControl() above you would simply have something like this pseudo-code:

sw.WriteLine("Column1Name, Column2Name, Column3Name");
foreach(var row in rowData)
{
    line = string.Format("{0}, {1}, {2}", row[0], row[1], row[2]);
    sw.WriteLine(line);
}

Basically output the header row first with names of the columns. Then after that output each row.
You can get very fancy with using string interpolation and String.Join() to minimize the amount of code written, but what I should above is to show you things in the simplest way possible.

 

[jmcilhinney]

All I need is a .csv file

If what you're actually trying to achieve is writing to a CSV file then it shouldn't take until post #8 for you to tell us that. Writing good forum questions is not hard but too many people try to be as brief as possible and seem to assume that we know what's in their head and in their project. We know only what you tell us so you have to tell us everything that is relevant and try to obscure it with as little as possible that is not. A good question should take the form "this is EXACTLY what I'm trying to achieve, this is EXACTLY how I'm trying to achieve it (including relevant code) and this is what happens when I try it (including error messages and where they occur".

 

[jmcilhinney]

Building a .CSV is easy. Instead of your call to dr.RenderControl() above you would simply have something like this pseudo-code:

sw.WriteLine("Column1Name, Column2Name, Column3Name");
foreach(var row in rowData)
{
    line = string.Format("{0}, {1}, {2}", row[0], row[1], row[2]);
    sw.WriteLine(line);
}

Basically output the header row first with names of the columns. Then after that output each row.
You can get very fancy with using string interpolation and String.Join() to minimize the amount of code written, but what I should above is to show you things in the simplest way possible.

One thing to keep in mind is that that code assumes that none of the data contains field or line delimiters. If that is or might be the case then you need to quote the field values, which also means that you need to escape quotes. In that case, string.Join is a good option:

line = @"""" + string.Join(@""",""", row.Select(f => f.ToString().Replace(@"""", @""""""))) + @"""";

That looks a bit crazy but it's basically getting each field value, replacing each double-quote with two double-quotes, combining them with a double-quote, a comma and another double-quote between each pair and then prepending and appending another double-quote. If you started with these values:

100

He entered, then said "Hello" to me

999

then you would end up with this:

"100","He entered, then said ""Hello"" to me","999"

 

[giasone777]

The problem is the columns for each of the buttons have different names, they are not static, each button is pulling a different query - select, name AS 'Vm Name' , vmhost where name like '%name%' so each button will have its own column names.

 

[Skydiver]

And how is that an issue? Here's more pseudo-code when using a SqlDataReader as the data source:

var names = new List<string>();
for(int i = 0; i < dataReader.FieldCount; i++)
    names.Add(dataReader.GetName(i));
writer.WriteLine(string.Join(", ", names));

while (dataReader.Read())
{
    var values = new List<string>();
    for(int i = 0; < dataReader.FieldCount; i++)
        values.Add(dataReader[i].ToString());
    writer.WriteLine(string.Join(", ", values));
}

The first chunk collects the column names and then writes out the header row. The second chunk collects and writes out the row data. (As noted above, you'll need to take measures to escape quotes, and protect spaces and delimiters.) The point of this pseudo code, though is to show that you can get dynamic data and still build a CSV.

 

[jmcilhinney]

Here are some extension methods that you can use to write CSV data directly from a data reader or DataTable to a file or stream:

public static class DataReaderExtensions
{
    public static void ToCsv(this DbDataReader source, Stream stream)
    {
        using (var writer = new StreamWriter(stream))
        {
            var columnIndexes = Enumerable.Range(0, source.FieldCount).ToArray();

            writer.WriteLine(string.Join(",", columnIndexes.Select(source.GetName)));

            while (source.Read())
            {
                writer.WriteLine(@"""" + string.Join(@""",""", columnIndexes.Select(i => source.GetValue(i).ToString().Replace(@"""", @""""""))) + @"""");
            }
        }
    }

    public static void ToCsv(this DbDataReader source, string filePath)
    {
        using (var stream = File.Create(filePath))
        {
            source.ToCsv(stream);
        }
    }
}

public static class DataTableExtensions
{
    public static void ToCsv(this DataTable source, Stream stream)
    {
        using (var writer = new StreamWriter(stream))
        {
            var columns = source.Columns.Cast<DataColumn>().ToArray();

            writer.WriteLine(string.Join(",", columns.Select(c => c.ColumnName)));

            foreach (DataRow row in source.Rows)
            {
                writer.WriteLine(@"""" + string.Join(@""",""", columns.Select(c => row[c].ToString().Replace(@"""", @""""""))) + @"""");
            }
        }
    }

    public static void ToCsv(this DataTable source, string filePath)
    {
        using (var stream = File.Create(filePath))
        {
            source.ToCsv(stream);
        }
    }
}

With those classes in your project or a referenced library, you can do this sort of thing:

using (var reader = command.ExecuteReader())
{
    reader.ToCsv(filePath);
}

or this:

adapter.Fill(table);

using (var stream = new MemoryStream())
{
    table.ToCsv(stream);
   
    // Use stream here.
}

 

[giasone777]

Thanks I will try them.