Simple download from GridView? Well, no – it should be – try this

Posted on by

So the code in theory is super slick – just use a StringWriter and HtmlWriter and in 2-5 commands it is downloaded to the client (see this link and the caveats that people use to get things working!). NO SUCH LUCK HERE. The main reason is that I have 2 columns with buttons in them at the front – the whole grid is DYNAMICALLY created by myself in the code (that is not included below). The c# code is included below.

So using some other researching I found a couple of concepts that worked together. I am sure there are some other optimizations but this worked.

First, I converted the GridView contents to a DataTable and for all columns 3 or more columns over I filled the datatable. Thanks to this fellow. Because the Gridview has all the source info including column headers etc. it works out great. My version even takes care of duplicate column heading names.

Since it downloads CSV, I already knew the format that Excel likes – it is surrounded by quotes and comma separated. I also took care of escaping double quotes with two double quotes and also ensuring that a \r\n (vbCrLf) was included at the end. Then Excel will not complain about anything.

One oddity was that empty Gridview cells export   so I replaced this with an empty string. One irritant remains and I don’t think I need to fix it for my client but 001 turns into 1 but that is what Excel does to the data. Once I figure out how to ELEGANTLY know the datatype (cause the sql command that gets the data knows what datatype it is – perhaps I should just save it as I retrieve it)

Importing? That is another article!

Just call the routine from a button or linked text asp item.

[csharp]

protected void ExportToExcel()
{
int ignoreXcols = 3;
DataTable dt = new DataTable();
List<string> cols = new List<string>();
for (int i = 0; i < GridView1.Columns.Count; i++)
{

if (i >= ignoreXcols)
{
string colName = GridView1.Columns[i].HeaderText;

if (dt.Columns.Contains(colName))
{
colName += i.ToString(); //add i to the end so there will be no conflicts
}
cols.Add(colName);
dt.Columns.Add(colName);

}
}
for (int i = 0; i < GridView1.Rows.Count; i++)
{
DataRow rw = dt.NewRow();

for (int j = 0; j < GridView1.Columns.Count; j++)
{

if (j >= ignoreXcols)
{

string temp = GridView1.Rows[i].Cells[j].Text; //improvement – this should be dependant on data type if we include the ‘ or not
if ("&nbsp;" == temp)
{
temp = ""; //null value
}
rw[cols[j – ignoreXcols]] = temp;
}

// xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
}
dt.Rows.Add(rw);
}

DateTime dtime = DateTime.Now;
string filename = "SourceData " + _setName + _moduleType + " " + dtime.ToString("yyyyMMdd HHmmss") + ".csv";
UploadDataTableToExcel(dt, filename);
}
protected void UploadDataTableToExcel(DataTable dtEmp, string filename)
{
string attachment = "attachment; filename=" + filename;
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = string.Empty;
foreach (DataColumn dtcol in dtEmp.Columns)
{
Response.Write(tab + "\"" + dtcol.ColumnName + "\"");
tab = ",";
}
Response.Write("\r\n");
foreach (DataRow dr in dtEmp.Rows)
{
tab = "";
for (int j = 0; j < dtEmp.Columns.Count; j++)
{

Response.Write(tab + "\"" + Convert.ToString(dr[j]).Replace("\"", "\"\"") + "\"");
tab = ",";
}
Response.Write("\r\n");
}
Response.End();
}

[/csharp]

Other links – thanks to everyone