ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Export DataSet to Excel and CSV using WorkBook Template in C#<p>Hi&nbsp;itsjayshah,</p> <p>For CSV Refer below code.</p> <pre class="brush: csharp">public class ExcelHelper { //Row limits older excel verion per sheet, the row limit for excel 2003 is 65536 const int rowLimit = 65000; private static string getWorkbookTemplate() { var sb = new StringBuilder(818); sb.AppendFormat(@&#34;&lt;?xml version=&#34;&#34;1.0&#34;&#34;?&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34;&lt;?mso-application progid=&#34;&#34;Excel.Sheet&#34;&#34;?&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34;&lt;Workbook xmlns=&#34;&#34;urn:schemas-microsoft-com:office:spreadsheet&#34;&#34;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; xmlns:o=&#34;&#34;urn:schemas-microsoft-com:office:office&#34;&#34;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; xmlns:x=&#34;&#34;urn:schemas-microsoft-com:office:excel&#34;&#34;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; xmlns:ss=&#34;&#34;urn:schemas-microsoft-com:office:spreadsheet&#34;&#34;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; xmlns:html=&#34;&#34;http://www.w3.org/TR/REC-html40&#34;&#34;&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;Styles&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;Style ss:ID=&#34;&#34;Default&#34;&#34; ss:Name=&#34;&#34;Normal&#34;&#34;&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;Alignment ss:Vertical=&#34;&#34;Bottom&#34;&#34;/&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;Borders/&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;Font ss:FontName=&#34;&#34;Calibri&#34;&#34; x:Family=&#34;&#34;Swiss&#34;&#34; ss:Size=&#34;&#34;11&#34;&#34; ss:Color=&#34;&#34;#000000&#34;&#34;/&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;Interior/&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;NumberFormat/&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;Protection/&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;/Style&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;Style ss:ID=&#34;&#34;s62&#34;&#34;&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;Font ss:FontName=&#34;&#34;Calibri&#34;&#34; x:Family=&#34;&#34;Swiss&#34;&#34; ss:Size=&#34;&#34;11&#34;&#34; ss:Color=&#34;&#34;#000000&#34;&#34;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; ss:Bold=&#34;&#34;1&#34;&#34;/&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;/Style&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;Style ss:ID=&#34;&#34;s63&#34;&#34;&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;NumberFormat ss:Format=&#34;&#34;Short Date&#34;&#34;/&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;/Style&gt;{0}&#34;, Environment.NewLine); sb.AppendFormat(@&#34; &lt;/Styles&gt;{0}&#34;, Environment.NewLine); sb.Append(@&#34;{0}\r\n&lt;/Workbook&gt;&#34;); return sb.ToString(); } private static string replaceXmlChar(string input) { input = input.Replace(&#34;&amp;&#34;, &#34;&amp;amp&#34;); input = input.Replace(&#34;&lt;&#34;, &#34;&amp;lt;&#34;); input = input.Replace(&#34;&gt;&#34;, &#34;&amp;gt;&#34;); input = input.Replace(&#34;\&#34;&#34;, &#34;&amp;quot;&#34;); input = input.Replace(&#34;&#39;&#34;, &#34;&amp;apos;&#34;); return input; } private static string getCell(Type type, object cellData) { var data = (cellData is DBNull) ? &#34;&#34; : cellData; if (type.Name.Contains(&#34;Int&#34;) || type.Name.Contains(&#34;Double&#34;) || type.Name.Contains(&#34;Decimal&#34;)) return string.Format(&#34;&lt;Cell&gt;&lt;Data ss:Type=\&#34;Number\&#34;&gt;{0}&lt;/Data&gt;&lt;/Cell&gt;&#34;, data); if (type.Name.Contains(&#34;Date&#34;) &amp;&amp; data.ToString() != string.Empty) { return string.Format(&#34;&lt;Cell ss:StyleID=\&#34;s63\&#34;&gt;&lt;Data ss:Type=\&#34;DateTime\&#34;&gt;{0}&lt;/Data&gt;&lt;/Cell&gt;&#34;, Convert.ToDateTime(data).ToString(&#34;yyyy-MM-dd&#34;)); } return string.Format(&#34;&lt;Cell&gt;&lt;Data ss:Type=\&#34;String\&#34;&gt;{0}&lt;/Data&gt;&lt;/Cell&gt;&#34;, replaceXmlChar(data.ToString())); } private static string getWorksheets(DataSet source) { var sw = new StringWriter(); if (source == null || source.Tables.Count == 0) { sw.Write(&#34;&lt;Worksheet ss:Name=\&#34;Sheet1\&#34;&gt;\r\n&lt;Table&gt;\r\n&lt;Row&gt;&lt;Cell&gt;&lt;Data ss:Type=\&#34;String\&#34;&gt;&lt;/Data&gt;&lt;/Cell&gt;&lt;/Row&gt;\r\n&lt;/Table&gt;\r\n&lt;/Worksheet&gt;&#34;); return sw.ToString(); } foreach (DataTable dt in source.Tables) { if (dt.Rows.Count == 0) sw.Write(&#34;&lt;Worksheet ss:Name=\&#34;&#34; + replaceXmlChar(dt.TableName) + &#34;\&#34;&gt;\r\n&lt;Table&gt;\r\n&lt;Row&gt;&lt;Cell ss:StyleID=\&#34;s62\&#34;&gt;&lt;Data ss:Type=\&#34;String\&#34;&gt;&lt;/Data&gt;&lt;/Cell&gt;&lt;/Row&gt;\r\n&lt;/Table&gt;\r\n&lt;/Worksheet&gt;&#34;); else { //write each row data var sheetCount = 0; for (int i = 0; i &lt; dt.Rows.Count; i++) { if ((i % rowLimit) == 0) { //add close tags for previous sheet of the same data table if ((i / rowLimit) &gt; sheetCount) { sw.Write(&#34;\r\n&lt;/Table&gt;\r\n&lt;/Worksheet&gt;&#34;); sheetCount = (i / rowLimit); } sw.Write(&#34;\r\n&lt;Worksheet ss:Name=\&#34;&#34; + replaceXmlChar(dt.TableName) + (((i / rowLimit) == 0) ? &#34;&#34; : Convert.ToString(i / rowLimit)) + &#34;\&#34;&gt;\r\n&lt;Table&gt;&#34;); //write column name row sw.Write(&#34;\r\n&lt;Row&gt;&#34;); foreach (DataColumn dc in dt.Columns) sw.Write(string.Format(&#34;&lt;Cell ss:StyleID=\&#34;s62\&#34;&gt;&lt;Data ss:Type=\&#34;String\&#34;&gt;{0}&lt;/Data&gt;&lt;/Cell&gt;&#34;, replaceXmlChar(dc.ColumnName))); sw.Write(&#34;&lt;/Row&gt;&#34;); } sw.Write(&#34;\r\n&lt;Row&gt;&#34;); foreach (DataColumn dc in dt.Columns) sw.Write(getCell(dc.DataType, dt.Rows[i][dc.ColumnName])); sw.Write(&#34;&lt;/Row&gt;&#34;); } sw.Write(&#34;\r\n&lt;/Table&gt;\r\n&lt;/Worksheet&gt;&#34;); } } return sw.ToString(); } public static string GetExcelXml(DataTable dtInput, string filename) { var excelTemplate = getWorkbookTemplate(); var ds = new DataSet(); ds.Tables.Add(dtInput.Copy()); var worksheets = getWorksheets(ds); var excelXml = string.Format(excelTemplate, worksheets); return excelXml; } public static string GetExcelXml(DataSet dsInput, string filename) { var excelTemplate = getWorkbookTemplate(); var worksheets = getWorksheets(dsInput); var excelXml = string.Format(excelTemplate, worksheets); return excelXml; } public static void ToExcel(DataSet dsInput, string filename, HttpResponse response, string format) { response.Clear(); if (format == &#34;csv&#34;) { string data = DataTableToCSV(dsInput, &#39;,&#39;); filename = filename + &#34;.csv&#34;; response.AppendHeader(&#34;Content-Type&#34;, &#34;text/csv&#34;); response.Output.Write(data); } else { var excelXml = GetExcelXml(dsInput, filename); filename = filename + &#34;.xls&#34;; response.AppendHeader(&#34;Content-Type&#34;, &#34;application/vnd.ms-excel&#34;); response.Write(excelXml); } response.AppendHeader(&#34;Content-disposition&#34;, &#34;attachment; filename=&#34; + filename); response.Flush(); response.End(); } public static void ToExcel(DataTable dtInput, string filename, HttpResponse response, string format) { var ds = new DataSet(); ds.Tables.Add(dtInput.Copy()); ToExcel(ds, filename, response, format); } public static string DataTableToCSV(DataSet dataSet, char seperator) { DataTable datatable = new DataTable(); foreach (DataTable dt in dataSet.Tables) { datatable.Merge(dt); } StringBuilder sb = new StringBuilder(); for (int i = 0; i &lt; datatable.Columns.Count; i++) { sb.Append(datatable.Columns[i]); if (i &lt; datatable.Columns.Count - 1) { sb.Append(seperator); } } sb.AppendLine(); foreach (DataRow dr in datatable.Rows) { for (int i = 0; i &lt; datatable.Columns.Count; i++) { sb.Append(dr[i].ToString()); if (i &lt; datatable.Columns.Count - 1) { sb.Append(seperator); } } sb.AppendLine(); } return sb.ToString(); } }</pre> <p>Call it like this.</p> <pre class="brush: csharp">protected void Page_Load(object sender, EventArgs e) { DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[3] { new DataColumn(&#34;Id&#34;, typeof(int)), new DataColumn(&#34;Name&#34;, typeof(string)), new DataColumn(&#34;Country&#34;,typeof(string)) }); dt.Rows.Add(1, &#34;John Hammond&#34;, &#34;United States&#34;); dt.Rows.Add(2, &#34;Mudassar Khan&#34;, &#34;India&#34;); dt.Rows.Add(3, &#34;Suzanne Mathews&#34;, &#34;France&#34;); dt.Rows.Add(4, &#34;Robert Schidner&#34;, &#34;Russia&#34;); DataSet _ds = new DataSet(); _ds.Tables.Add(dt); ExcelHelper.ToExcel(_ds, &#34;test&#34;, Page.Response, &#34;csv&#34;); }</pre> <p>For xlsx refer below link.</p> <p><a href="https://www.codeproject.com/Tips/667392/Create-Excel-file-from-DataTable">https://www.codeproject.com/Tips/667392/Create-Excel-file-from-DataTable</a></p>https://www.aspforums.net:443/Threads/131502/Export-DataSet-to-Excel-and-CSV-using-WorkBook-Template-in-C/https://www.aspforums.net:443/Threads/131502/Export-DataSet-to-Excel-and-CSV-using-WorkBook-Template-in-C/Mon, 13 May 2019 08:09:51 GMT