We often require to export data from a DataTable to an Excel or a CSV file. Here is a sample project that exports data and create Excel and CSV files. You can use same code with your required modifictions in your projects.
  1. # region Includes...
  2. using System;
  3. using System.Data;
  4. using System.Web;
  5. using System.Web.SessionState;
  6. using System.IO;
  7. using System.Text;
  8. using System.Xml;
  9. using System.Xml.Xsl;
  10. using System.Threading;
  11. # endregion // Includes...
  12. /// <summary>
  13. /// Summary description for Export
  14. /// </summary>
  15. public class Exporting
  16. {
  17. public enum ExportFormat : int { CSV = 1, Excel = 2 }; // Export format enumeration
  18. System.Web.HttpResponse response;
  19. private string appType;
  20. public Exporting()
  21. {
  22. appType = "Web";
  23. response = System.Web.HttpContext.Current.Response;
  24. }
  25. public Exporting(string ApplicationType)
  26. {
  27. appType = ApplicationType;
  28. if (appType != "Web" && appType != "Win") throw new Exception("Provide valid application format (Web/Win)");
  29. if (appType == "Web") response = System.Web.HttpContext.Current.Response;
  30. }
  31. #region ExportDetails OverLoad : Type#1
  32. // Function : ExportDetails
  33. // Arguments : DetailsTable, FormatType, FileName
  34. // Purpose : To get all the column headers in the datatable and
  35. // exorts in CSV / Excel format with all columns
  36. public void ExportDetails(DataTable DetailsTable, string FormatType, string FileName)
  37. {
  38. try
  39. {
  40. if (DetailsTable.Rows.Count == 0)
  41. throw new Exception("There are no details to export.");
  42. // Create Dataset
  43. DataSet dsExport = new DataSet("Export");
  44. DataTable dtExport = DetailsTable.Copy();
  45. dtExport.TableName = "Values";
  46. dsExport.Tables.Add(dtExport);
  47. // Getting Field Names
  48. string[] sHeaders = new string[dtExport.Columns.Count];
  49. string[] sFileds = new string[dtExport.Columns.Count];
  50. for (int i = 0; i < dtExport.Columns.Count; i++)
  51. {
  52. //sHeaders[i] = ReplaceSpclChars(dtExport.Columns[i].ColumnName);
  53. sHeaders[i] = dtExport.Columns[i].ColumnName;
  54. sFileds[i] = ReplaceSpclChars(dtExport.Columns[i].ColumnName);
  55. }
  56. if (appType == "Web")
  57. Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
  58. else if (appType == "Win")
  59. Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
  60. }
  61. catch (Exception Ex)
  62. {
  63. throw Ex;
  64. }
  65. }
  66. #endregion // ExportDetails OverLoad : Type#1
  67. #region ExportDetails OverLoad : Type#2
  68. // Function : ExportDetails
  69. // Arguments : DetailsTable, ColumnList, FormatType, FileName
  70. // Purpose : To get the specified column headers in the datatable and
  71. // exorts in CSV / Excel format with specified columns
  72. public void ExportDetails(DataTable DetailsTable, int[] ColumnList, string FormatType, string FileName)
  73. {
  74. try
  75. {
  76. if (DetailsTable.Rows.Count == 0)
  77. throw new Exception("There are no details to export");
  78. // Create Dataset
  79. DataSet dsExport = new DataSet("Export");
  80. DataTable dtExport = DetailsTable.Copy();
  81. dtExport.TableName = "Values";
  82. dsExport.Tables.Add(dtExport);
  83. if (ColumnList.Length > dtExport.Columns.Count)
  84. throw new Exception("ExportColumn List should not exceed Total Columns");
  85. // Getting Field Names
  86. string[] sHeaders = new string[ColumnList.Length];
  87. string[] sFileds = new string[ColumnList.Length];
  88. for (int i = 0; i < ColumnList.Length; i++)
  89. {
  90. if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
  91. throw new Exception("ExportColumn Number should not exceed Total Columns Range");
  92. sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
  93. sFileds[i] = ReplaceSpclChars(dtExport.Columns[ColumnList[i]].ColumnName);
  94. }
  95. if (appType == "Web")
  96. Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
  97. else if (appType == "Win")
  98. Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
  99. }
  100. catch (Exception Ex)
  101. {
  102. throw Ex;
  103. }
  104. }
  105. #endregion // ExportDetails OverLoad : Type#2
  106. #region ExportDetails OverLoad : Type#3
  107. // Function : ExportDetails
  108. // Arguments : DetailsTable, ColumnList, Headers, FormatType, FileName
  109. // Purpose : To get the specified column headers in the datatable and
  110. // exorts in CSV / Excel format with specified columns and
  111. // with specified headers
  112. public void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] Headers, string FormatType,
  113. string FileName)
  114. {
  115. try
  116. {
  117. if (DetailsTable.Rows.Count == 0)
  118. throw new Exception("There are no details to export");
  119. // Create Dataset
  120. DataSet dsExport = new DataSet("Export");
  121. DataTable dtExport = DetailsTable.Copy();
  122. dtExport.TableName = "Values";
  123. dsExport.Tables.Add(dtExport);
  124. if (ColumnList.Length != Headers.Length)
  125. throw new Exception("ExportColumn List and Headers List should be of same length");
  126. else if (ColumnList.Length > dtExport.Columns.Count || Headers.Length > dtExport.Columns.Count)
  127. throw new Exception("ExportColumn List should not exceed Total Columns");
  128. // Getting Field Names
  129. string[] sFileds = new string[ColumnList.Length];
  130. for (int i = 0; i < ColumnList.Length; i++)
  131. {
  132. if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
  133. throw new Exception("ExportColumn Number should not exceed Total Columns Range");
  134. sFileds[i] = ReplaceSpclChars(dtExport.Columns[ColumnList[i]].ColumnName);
  135. }
  136. if (appType == "Web")
  137. Export_with_XSLT_Web(dsExport, Headers, sFileds, FormatType, FileName);
  138. else if (appType == "Win")
  139. Export_with_XSLT_Windows(dsExport, Headers, sFileds, FormatType, FileName);
  140. }
  141. catch (Exception Ex)
  142. {
  143. throw Ex;
  144. }
  145. }
  146. #endregion // ExportDetails OverLoad : Type#3
  147. #region Export_with_XSLT_Web
  148. // Function : Export_with_XSLT_Web
  149. // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
  150. // Purpose : Exports dataset into CSV / Excel format
  151. private void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, string FormatType, string FileName)
  152. {
  153. try
  154. {
  155. // Appending Headers
  156. response.Clear();
  157. response.Buffer = true;
  158. if (FormatType == ExportFormat.CSV.ToString())
  159. {
  160. response.ContentType = "text/csv";
  161. response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
  162. }
  163. else
  164. {
  165. response.ContentType = "application/vnd.ms-excel";
  166. response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
  167. }
  168. // XSLT to use for transforming this dataset.
  169. MemoryStream stream = new MemoryStream();
  170. XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
  171. CreateStylesheet(writer, sHeaders, sFileds, FormatType);
  172. writer.Flush();
  173. stream.Seek(0, SeekOrigin.Begin);
  174. XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
  175. //dsExport.WriteXml("Data.xml");
  176. XslTransform xslTran = new XslTransform();
  177. xslTran.Load(new XmlTextReader(stream), null, null);
  178. System.IO.StringWriter sw = new System.IO.StringWriter();
  179. xslTran.Transform(xmlDoc, null, sw, null);
  180. //xslTran.Transform(System.Web.HttpContext.Current.Server.MapPath("Data.xml"), null, sw, null);
  181. //Writeout the Content
  182. response.Write(sw.ToString());
  183. sw.Close();
  184. writer.Close();
  185. stream.Close();
  186. response.End();
  187. }
  188. catch (ThreadAbortException Ex)
  189. {
  190. string ErrMsg = Ex.Message;
  191. }
  192. catch (Exception Ex)
  193. {
  194. throw Ex;
  195. }
  196. }
  197. #endregion // Export_with_XSLT
  198. #region Export_with_XSLT_Windows
  199. // Function : Export_with_XSLT_Windows
  200. // Arguments : dsExport, sHeaders, sFileds, FormatType, FileName
  201. // Purpose : Exports dataset into CSV / Excel format
  202. private void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds, string FormatType, string FileName)
  203. {
  204. try
  205. {
  206. // XSLT to use for transforming this dataset.
  207. MemoryStream stream = new MemoryStream();
  208. XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
  209. CreateStylesheet(writer, sHeaders, sFileds, FormatType);
  210. writer.Flush();
  211. stream.Seek(0, SeekOrigin.Begin);
  212. XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
  213. XslTransform xslTran = new XslTransform();
  214. xslTran.Load(new XmlTextReader(stream), null, null);
  215. System.IO.StringWriter sw = new System.IO.StringWriter();
  216. xslTran.Transform(xmlDoc, null, sw, null);
  217. //Writeout the Content
  218. StreamWriter strwriter = new StreamWriter(FileName);
  219. strwriter.WriteLine(sw.ToString());
  220. strwriter.Close();
  221. sw.Close();
  222. writer.Close();
  223. stream.Close();
  224. }
  225. catch (Exception Ex)
  226. {
  227. throw Ex;
  228. }
  229. }
  230. #endregion // Export_with_XSLT
  231. #region CreateStylesheet
  232. // Function : WriteStylesheet
  233. // Arguments : writer, sHeaders, sFileds, FormatType
  234. // Purpose : Creates XSLT file to apply on dataset's XML file
  235. private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, string FormatType)
  236. {
  237. try
  238. {
  239. // xsl:stylesheet
  240. string ns = "http://www.w3.org/1999/XSL/Transform";
  241. writer.Formatting = Formatting.Indented;
  242. writer.WriteStartDocument();
  243. writer.WriteStartElement("xsl", "stylesheet", ns);
  244. writer.WriteAttributeString("version", "1.0");
  245. writer.WriteStartElement("xsl:output");
  246. writer.WriteAttributeString("method", "text");
  247. writer.WriteAttributeString("version", "4.0");
  248. writer.WriteEndElement();
  249. // xsl-template
  250. writer.WriteStartElement("xsl:template");
  251. writer.WriteAttributeString("match", "/");
  252. // xsl:value-of for headers
  253. for (int i = 0; i < sHeaders.Length; i++)
  254. {
  255. writer.WriteString("\"");
  256. writer.WriteStartElement("xsl:value-of");
  257. writer.WriteAttributeString("select", "'" + sHeaders[i] + "'");
  258. writer.WriteEndElement(); // xsl:value-of
  259. writer.WriteString("\"");
  260. if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV.ToString()) ? "," : " ");
  261. }
  262. // xsl:for-each
  263. writer.WriteStartElement("xsl:for-each");
  264. writer.WriteAttributeString("select", "Export/Values");
  265. writer.WriteString("\r\n");
  266. // xsl:value-of for data fields
  267. for (int i = 0; i < sFileds.Length; i++)
  268. {
  269. writer.WriteString("\"");
  270. writer.WriteStartElement("xsl:value-of");
  271. writer.WriteAttributeString("select", sFileds[i]);
  272. writer.WriteEndElement(); // xsl:value-of
  273. writer.WriteString("\"");
  274. if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV.ToString()) ? "," : " ");
  275. }
  276. writer.WriteEndElement(); // xsl:for-each
  277. writer.WriteEndElement(); // xsl-template
  278. writer.WriteEndElement(); // xsl:stylesheet
  279. writer.WriteEndDocument();
  280. }
  281. catch (Exception Ex)
  282. {
  283. throw Ex;
  284. }
  285. }
  286. #endregion // WriteStylesheet
  287. #region ReplaceSpclChars
  288. // Function : ReplaceSpclChars
  289. // Arguments : fieldName
  290. // Purpose : Replaces special characters with XML codes
  291. private string ReplaceSpclChars(string fieldName)
  292. {
  293. // space -> _x0020_
  294. // % -> _x0025_
  295. // # -> _x0023_
  296. // & -> _x0026_
  297. // / -> _x002F_
  298. fieldName = fieldName.Replace(" ", "_x0020_");
  299. fieldName = fieldName.Replace("%", "_x0025_");
  300. fieldName = fieldName.Replace("#", "_x0023_");
  301. fieldName = fieldName.Replace("&", "_x0026_");
  302. fieldName = fieldName.Replace("/", "_x002F_");
  303. return fieldName;
  304. }
  305. #endregion // ReplaceSpclChars
  306. }
Export to CSV
Here is the code that passes a DataTable to the ExportDetails method and generates a .csv file.
  1. SqlCommand cmd = new SqlCommand("select top(20) [UserId],[FirstName],[Lastname],[UserEmail],[UserPwd],[RoleID],[status],[CreateDate],[ModifiedDate] FROM [pankaj].[dbo].[SMOUserMaster]", conn);
  2. SqlDataAdapter da = new SqlDataAdapter(cmd);
  3. DataTable dt = new DataTable();
  4. da.Fill(dt);
  5. expt.ExportDetails(dt, "CSV", "download.csv");
Export to Excel
Here is the code that passes a DataTable to the ExportDetails method and generates an Excel file.
  1. SqlCommand cmd = new SqlCommand("select top(20) [UserId],[FirstName],[Lastname],[UserEmail],[UserPwd],[RoleID],[status],[CreateDate],[ModifiedDate] FROM [pankaj].[dbo].[SMOUserMaster]", conn);
  2. SqlDataAdapter da = new SqlDataAdapter(cmd);
  3. DataTable dt = new DataTable();
  4. da.Fill(dt);
  5. expt.ExportDetails(dt, "Excel", "download.xls");
More details
Here are some more detailed tutorials: