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.
- # region Includes...
- using System;
- using System.Data;
- using System.Web;
- using System.Web.SessionState;
- using System.IO;
- using System.Text;
- using System.Xml;
- using System.Xml.Xsl;
- using System.Threading;
- # endregion // Includes...
-
-
-
- public class Exporting
- {
- public enum ExportFormat : int { CSV = 1, Excel = 2 };
- System.Web.HttpResponse response;
- private string appType;
- public Exporting()
- {
- appType = "Web";
- response = System.Web.HttpContext.Current.Response;
- }
- public Exporting(string ApplicationType)
- {
- appType = ApplicationType;
- if (appType != "Web" && appType != "Win") throw new Exception("Provide valid application format (Web/Win)");
- if (appType == "Web") response = System.Web.HttpContext.Current.Response;
- }
- #region ExportDetails OverLoad : Type#1
-
-
-
-
- public void ExportDetails(DataTable DetailsTable, string FormatType, string FileName)
- {
- try
- {
- if (DetailsTable.Rows.Count == 0)
- throw new Exception("There are no details to export.");
-
- DataSet dsExport = new DataSet("Export");
- DataTable dtExport = DetailsTable.Copy();
- dtExport.TableName = "Values";
- dsExport.Tables.Add(dtExport);
-
- string[] sHeaders = new string[dtExport.Columns.Count];
- string[] sFileds = new string[dtExport.Columns.Count];
- for (int i = 0; i < dtExport.Columns.Count; i++)
- {
-
- sHeaders[i] = dtExport.Columns[i].ColumnName;
- sFileds[i] = ReplaceSpclChars(dtExport.Columns[i].ColumnName);
- }
- if (appType == "Web")
- Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
- else if (appType == "Win")
- Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
- }
- catch (Exception Ex)
- {
- throw Ex;
- }
- }
- #endregion // ExportDetails OverLoad : Type#1
- #region ExportDetails OverLoad : Type#2
-
-
-
-
- public void ExportDetails(DataTable DetailsTable, int[] ColumnList, string FormatType, string FileName)
- {
- try
- {
- if (DetailsTable.Rows.Count == 0)
- throw new Exception("There are no details to export");
-
- DataSet dsExport = new DataSet("Export");
- DataTable dtExport = DetailsTable.Copy();
- dtExport.TableName = "Values";
- dsExport.Tables.Add(dtExport);
- if (ColumnList.Length > dtExport.Columns.Count)
- throw new Exception("ExportColumn List should not exceed Total Columns");
-
- string[] sHeaders = new string[ColumnList.Length];
- string[] sFileds = new string[ColumnList.Length];
- for (int i = 0; i < ColumnList.Length; i++)
- {
- if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
- throw new Exception("ExportColumn Number should not exceed Total Columns Range");
- sHeaders[i] = dtExport.Columns[ColumnList[i]].ColumnName;
- sFileds[i] = ReplaceSpclChars(dtExport.Columns[ColumnList[i]].ColumnName);
- }
- if (appType == "Web")
- Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
- else if (appType == "Win")
- Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
- }
- catch (Exception Ex)
- {
- throw Ex;
- }
- }
- #endregion // ExportDetails OverLoad : Type#2
- #region ExportDetails OverLoad : Type#3
-
-
-
-
-
- public void ExportDetails(DataTable DetailsTable, int[] ColumnList, string[] Headers, string FormatType,
- string FileName)
- {
- try
- {
- if (DetailsTable.Rows.Count == 0)
- throw new Exception("There are no details to export");
-
- DataSet dsExport = new DataSet("Export");
- DataTable dtExport = DetailsTable.Copy();
- dtExport.TableName = "Values";
- dsExport.Tables.Add(dtExport);
- if (ColumnList.Length != Headers.Length)
- throw new Exception("ExportColumn List and Headers List should be of same length");
- else if (ColumnList.Length > dtExport.Columns.Count || Headers.Length > dtExport.Columns.Count)
- throw new Exception("ExportColumn List should not exceed Total Columns");
-
- string[] sFileds = new string[ColumnList.Length];
- for (int i = 0; i < ColumnList.Length; i++)
- {
- if ((ColumnList[i] < 0) || (ColumnList[i] >= dtExport.Columns.Count))
- throw new Exception("ExportColumn Number should not exceed Total Columns Range");
- sFileds[i] = ReplaceSpclChars(dtExport.Columns[ColumnList[i]].ColumnName);
- }
- if (appType == "Web")
- Export_with_XSLT_Web(dsExport, Headers, sFileds, FormatType, FileName);
- else if (appType == "Win")
- Export_with_XSLT_Windows(dsExport, Headers, sFileds, FormatType, FileName);
- }
- catch (Exception Ex)
- {
- throw Ex;
- }
- }
- #endregion // ExportDetails OverLoad : Type#3
- #region Export_with_XSLT_Web
-
-
-
- private void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, string FormatType, string FileName)
- {
- try
- {
-
- response.Clear();
- response.Buffer = true;
- if (FormatType == ExportFormat.CSV.ToString())
- {
- response.ContentType = "text/csv";
- response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
- }
- else
- {
- response.ContentType = "application/vnd.ms-excel";
- response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
- }
-
- MemoryStream stream = new MemoryStream();
- XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
- CreateStylesheet(writer, sHeaders, sFileds, FormatType);
- writer.Flush();
- stream.Seek(0, SeekOrigin.Begin);
- XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
-
- XslTransform xslTran = new XslTransform();
- xslTran.Load(new XmlTextReader(stream), null, null);
- System.IO.StringWriter sw = new System.IO.StringWriter();
- xslTran.Transform(xmlDoc, null, sw, null);
-
-
- response.Write(sw.ToString());
- sw.Close();
- writer.Close();
- stream.Close();
- response.End();
- }
- catch (ThreadAbortException Ex)
- {
- string ErrMsg = Ex.Message;
- }
- catch (Exception Ex)
- {
- throw Ex;
- }
- }
- #endregion // Export_with_XSLT
- #region Export_with_XSLT_Windows
-
-
-
- private void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds, string FormatType, string FileName)
- {
- try
- {
-
- MemoryStream stream = new MemoryStream();
- XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
- CreateStylesheet(writer, sHeaders, sFileds, FormatType);
- writer.Flush();
- stream.Seek(0, SeekOrigin.Begin);
- XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
- XslTransform xslTran = new XslTransform();
- xslTran.Load(new XmlTextReader(stream), null, null);
- System.IO.StringWriter sw = new System.IO.StringWriter();
- xslTran.Transform(xmlDoc, null, sw, null);
-
- StreamWriter strwriter = new StreamWriter(FileName);
- strwriter.WriteLine(sw.ToString());
- strwriter.Close();
- sw.Close();
- writer.Close();
- stream.Close();
- }
- catch (Exception Ex)
- {
- throw Ex;
- }
- }
- #endregion // Export_with_XSLT
- #region CreateStylesheet
-
-
-
- private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, string FormatType)
- {
- try
- {
-
- string ns = "http://www.w3.org/1999/XSL/Transform";
- writer.Formatting = Formatting.Indented;
- writer.WriteStartDocument();
- writer.WriteStartElement("xsl", "stylesheet", ns);
- writer.WriteAttributeString("version", "1.0");
- writer.WriteStartElement("xsl:output");
- writer.WriteAttributeString("method", "text");
- writer.WriteAttributeString("version", "4.0");
- writer.WriteEndElement();
-
- writer.WriteStartElement("xsl:template");
- writer.WriteAttributeString("match", "/");
-
- for (int i = 0; i < sHeaders.Length; i++)
- {
- writer.WriteString("\"");
- writer.WriteStartElement("xsl:value-of");
- writer.WriteAttributeString("select", "'" + sHeaders[i] + "'");
- writer.WriteEndElement();
- writer.WriteString("\"");
- if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV.ToString()) ? "," : " ");
- }
-
- writer.WriteStartElement("xsl:for-each");
- writer.WriteAttributeString("select", "Export/Values");
- writer.WriteString("\r\n");
-
- for (int i = 0; i < sFileds.Length; i++)
- {
- writer.WriteString("\"");
- writer.WriteStartElement("xsl:value-of");
- writer.WriteAttributeString("select", sFileds[i]);
- writer.WriteEndElement();
- writer.WriteString("\"");
- if (i != sFileds.Length - 1) writer.WriteString((FormatType == ExportFormat.CSV.ToString()) ? "," : " ");
- }
- writer.WriteEndElement();
- writer.WriteEndElement();
- writer.WriteEndElement();
- writer.WriteEndDocument();
- }
- catch (Exception Ex)
- {
- throw Ex;
- }
- }
- #endregion // WriteStylesheet
- #region ReplaceSpclChars
-
-
-
- private string ReplaceSpclChars(string fieldName)
- {
-
-
-
-
-
- fieldName = fieldName.Replace(" ", "_x0020_");
- fieldName = fieldName.Replace("%", "_x0025_");
- fieldName = fieldName.Replace("#", "_x0023_");
- fieldName = fieldName.Replace("&", "_x0026_");
- fieldName = fieldName.Replace("/", "_x002F_");
- return fieldName;
- }
- #endregion // ReplaceSpclChars
- }
Export to CSV
Here is the code that passes a DataTable to the ExportDetails method and generates a .csv file.
- SqlCommand cmd = new SqlCommand("select top(20) [UserId],[FirstName],[Lastname],[UserEmail],[UserPwd],[RoleID],[status],[CreateDate],[ModifiedDate] FROM [pankaj].[dbo].[SMOUserMaster]", conn);
- SqlDataAdapter da = new SqlDataAdapter(cmd);
- DataTable dt = new DataTable();
- da.Fill(dt);
- 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.
- SqlCommand cmd = new SqlCommand("select top(20) [UserId],[FirstName],[Lastname],[UserEmail],[UserPwd],[RoleID],[status],[CreateDate],[ModifiedDate] FROM [pankaj].[dbo].[SMOUserMaster]", conn);
- SqlDataAdapter da = new SqlDataAdapter(cmd);
- DataTable dt = new DataTable();
- da.Fill(dt);
- expt.ExportDetails(dt, "Excel", "download.xls");
More details
Here are some more detailed tutorials: