{"id":664,"date":"2014-02-27T00:02:28","date_gmt":"2014-02-27T00:02:28","guid":{"rendered":"http:\/\/elbsolutions.com\/projects\/?p=664"},"modified":"2022-02-03T11:25:02","modified_gmt":"2022-02-03T17:25:02","slug":"simple-download-gridview-well-try","status":"publish","type":"post","link":"https:\/\/elbsolutions.com\/projects\/simple-download-gridview-well-try\/","title":{"rendered":"Simple download from GridView? Well, no – it should be – try this"},"content":{"rendered":"

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.<\/p>\n

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.<\/p>\n

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<\/a>. 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.<\/p>\n

Since it downloads CSV, I already knew the format that Excel likes<\/strong> – 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.<\/p>\n

One oddity was that empty Gridview cells export &nbsp; 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)<\/p>\n

Importing? That is another article!<\/p>\n

Just call the routine from a button or linked text asp item.<\/p>\n

\r\n\r\nprotected void ExportToExcel()\r\n {\r\n int ignoreXcols = 3;\r\n DataTable dt = new DataTable();\r\n List<string> cols = new List<string>();\r\n for (int i = 0; i < GridView1.Columns.Count; i++)\r\n {\r\n\r\n if (i >= ignoreXcols)\r\n {\r\n string colName = GridView1.Columns[i].HeaderText;\r\n\r\n if (dt.Columns.Contains(colName))\r\n {\r\n colName += i.ToString(); \/\/add i to the end so there will be no conflicts\r\n }\r\n cols.Add(colName);\r\n dt.Columns.Add(colName);\r\n\r\n }\r\n }\r\n for (int i = 0; i < GridView1.Rows.Count; i++)\r\n {\r\n DataRow rw = dt.NewRow();\r\n\r\nfor (int j = 0; j < GridView1.Columns.Count; j++)\r\n {\r\n\r\n if (j >= ignoreXcols)\r\n {\r\n\r\n string temp = GridView1.Rows[i].Cells[j].Text; \/\/improvement - this should be dependant on data type if we include the ' or not\r\n if ("&nbsp;" == temp)\r\n {\r\n temp = ""; \/\/null value\r\n }\r\n rw[cols[j - ignoreXcols]] = temp;\r\n }\r\n\r\n\/\/ xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;\r\n }\r\n dt.Rows.Add(rw);\r\n }\r\n\r\nDateTime dtime = DateTime.Now;\r\n string filename = "SourceData " + _setName + _moduleType + " " + dtime.ToString("yyyyMMdd HHmmss") + ".csv";\r\n UploadDataTableToExcel(dt, filename);\r\n }\r\n protected void UploadDataTableToExcel(DataTable dtEmp, string filename)\r\n {\r\n string attachment = "attachment; filename=" + filename;\r\n Response.ClearContent();\r\n Response.AddHeader("content-disposition", attachment);\r\n Response.ContentType = "application\/vnd.ms-excel";\r\n string tab = string.Empty;\r\n foreach (DataColumn dtcol in dtEmp.Columns)\r\n {\r\n Response.Write(tab + "\\"" + dtcol.ColumnName + "\\"");\r\n tab = ",";\r\n }\r\n Response.Write("\\r\\n");\r\n foreach (DataRow dr in dtEmp.Rows)\r\n {\r\n tab = "";\r\n for (int j = 0; j < dtEmp.Columns.Count; j++)\r\n {\r\n\r\nResponse.Write(tab + "\\"" + Convert.ToString(dr[j]).Replace("\\"", "\\"\\"") + "\\"");\r\n tab = ",";\r\n }\r\n Response.Write("\\r\\n");\r\n }\r\n Response.End();\r\n }\r\n\r\n<\/pre>\n
Other links – thanks to everyone<\/p>\n