Export Data to Excel Without Using Interop

Exporting the data to Excel(Spreadsheet) is one of the frequent requirement. There are plenty of ways to do this task in c#. However, for licence issues people would like to do this task without using interop library. If you just Google about this, you will get lots of different approaches along with the sample code. Since these approaches are spread over different websites, I planned to write them together.

RenderControl

Using this approach, we can easily export the data from GridView. This will be useful when we want our exported data in the same format as in the GridView.

       public void ExportToExcel(DataTable table)
        {
            StringWriter strWriter = new StringWriter();
            HtmlTextWriter htmlWriter = new HtmlTextWriter(strWriter);

            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=MyFileName.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";

            MyGridView.DataSource = table;
            MyGridView.DataBind();
            MyGridView.RenderControl(htmlWriter);
            Response.Write(strWriter.ToString());
            Response.Flush();
            Response.End();
        }

HTML Table

Here the sheets are treated as a html table. The advantage of this approach is  easy customization. You can customize the output using any html tags.

        public void ExportToExcel(DataTable table)
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear();

            //Begin Table
            context.Response.Write("<table><tr>");

            //Write Header
            foreach (DataColumn column in table.Columns)
            {
                context.Response.Write("<th>" + column.ColumnName + "</th>");
            }
            context.Response.Write("</tr>");

            //Write Data
            foreach (DataRow row in table.Rows)
            {
                context.Response.Write("<tr>");
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    context.Response.Write("<td>" + row[i].ToString().Replace(",", string.Empty) + "</td>");
                }
                context.Response.Write("</tr>");
            }

            //End Table
            context.Response.Write("</table>");

            context.Response.ContentType = "application/ms-excel";
            context.Response.AppendHeader("Content-Disposition", "attachment;filename=MyFileName.xls");
            context.Response.End();
        }

OLEDB

In this approach, Excel is treated as a database and sheets as a table. The ability to write query is the key feature of this approach. This will be useful for complex operations, such as appending data to the existing file and then exporting.

        public void ExportToExcel(DataTable table, string fileName)
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear();

            string query;
            OleDbCommand cmd;
            OleDbConnection cnn;

            try
            {
                string cnStr = GetConnectionString(fileName, Types.Excel_97_2000_2003_xls, true, true);

                cnn = new OleDbConnection(cnStr);
                cnn.Open();

                //Drop the existing sheet(first Sheet)
                query = "DROP TABLE [Sheet1$]";
                cmd = new OleDbCommand(query, cnn);
                cmd.ExecuteNonQuery();

                //Create new sheet with our requirements
                query = "CREATE TABLE [Sheet1$] (";
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    query += table.Columns[i].ColumnName;
                    if (i + 1 == table.Columns.Count)
                        if (table.Columns[i].DataType == System.Type.GetType("System.Int32"))
                            query += " INT)";
                        else
                            query += " VARCHAR(255))";
                    else
                        if (table.Columns[i].DataType == System.Type.GetType("System.Int32"))
                            query += " INT,";
                        else
                            query += " VARCHAR(255),";
                }
                cmd = new OleDbCommand(query, cnn);
                cmd.ExecuteNonQuery();

                //Insert Data
                foreach (DataRow row in table.Rows)
                {
                    string values = "(";
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        if (i + 1 == table.Columns.Count)
                        {
                            if (table.Columns[i].DataType == System.Type.GetType("System.Int32"))
                                values += String.IsNullOrEmpty(row[i].ToString()) ? "0)" : row[i] + ")";
                            else
                                values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "')";
                        }
                        else
                        {
                            if (table.Columns[i].DataType == System.Type.GetType("System.Int32"))
                                values += String.IsNullOrEmpty(row[i].ToString()) ? "0," : row[i] + ",";
                            else
                                values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "',";
                        }
                    }
                    query = String.Format("Insert into [Sheet1$] VALUES {0}", values);
                    cmd = new OleDbCommand(query, cnn);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                context.Response.Write(ex.Message);
                return;
            }
            finally
            {
                cmd = null;
                if (cnn != null)
                    cnn.Close();
            }

            context.Response.ContentType = "application/ms-excel";
            context.Response.AppendHeader("Content-Disposition", "attachment;filename=MyFileName.xls");
            context.Response.WriteFile(fileName);
        }

        private static string GetConnectionString(string fileName, string Type, bool isHeaderExists, bool TreatIntermixedAsText)
        {
            string cnnStr;
            string provider;

            if (Type == "Excel 5.0" || Type == "Excel 8.0")
                provider = "Microsoft.Jet.OLEDB.4.0";
            else
                provider = "Microsoft.ACE.OLEDB.12.0";

            cnnStr = "Provider=" + provider +
                        ";Data Source=" + fileName +
                        ";Extended Properties=\"" + Type +
                                               ";HDR=" + (isHeaderExists ? "Yes;\"" : "No;\"");

            return cnnStr;
        }

        struct Types
        {
            /// <summary>
            /// Excel 2007 XML (*.xlsx)
            /// </summary>
            public const string Excel_2007_XML_xlsx = "Excel 12.0 Xml";

            /// <summary>
            /// Excel 2007 Binary (*.xlsb)
            /// </summary>
            public const string Excel_2007_Binary_xlsb = "Excel 12.0";

            /// <summary>
            /// Excel 2007 Macro-enabled (*.xlsm)
            /// </summary>
            public const string Excel_2007_MacroEnabled_xlsm = "Excel 12.0 Macro";

            /// <summary>
            /// Excel 97/2000/2003 (*.xls)
            /// </summary>
            public const string Excel_97_2000_2003_xls = "Excel 8.0";

            /// <summary>
            /// Excel 5.0/95 (*.xls)
            /// </summary>
            public const string Excel_95_xls = "Excel 5.0";
        }