Tuesday, June 5, 2012

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";
        }

14 comments:

  1. nice post.
    I am trying to use your OLEdb approach above and using ACE provider. when I open the connection I get the exception that follows, have you seen this:
    "The Microsoft Access database engine cannot open or write to the file ''.
    It is already opened exclusively by another user,
    or you need permission to view and write its data

    ReplyDelete
    Replies
    1. Provide modify/write permission to the file or containing folder for the users IUSER, IWAM and Network Service. Also, make sure that only one instance of the file is open at a time. Hope this helps.

      Delete
    2. how to do it in the same in the windows application

      Delete
    3. Doing it in windows application is as easy as copying the file to target location. Ex: File.Copy(sourecePath, destinationPath).

      Delete
  2. how to open another sheet in the same excel file and write another table data

    ReplyDelete
    Replies
    1. Using OLEDB approach, write CREATE TABLE syntax with your specific page name. Ex: CREATE TABLE [MySheet] (Clm1 VARCHAR(255),Clm2 INT)
      This will create a new excel page with the name 'MySheet'. Note that, this syntax not includes '$' with the sheet name. However, if you are inserting value to this newly created sheet, then of-course '$' is required as usual with the sheet name. Ex: INSERT INTO [MySheet$] VALUES ('Naveen',123)

      Delete
  3. The code is working fine but when we export data to excel file and go to file save as that time default save as type is "Web Page" instead of "Excel 97-2003 work book".How can we set default save as type "Excel 97-2003 work book". please give your suggestion.

    ReplyDelete
    Replies
    1. Usually, the browsers will prompt save-as type depending on the MIME type of the file. If you set a proper MIME type then, the browsers should prompt proper save as type. In my example I used application/ms-excel which is a shortcut for application/vnd.ms-excel. It should work on all latest browsers (Including IE 6 & IE 6+), if there is not any problem with the browser (sometimes, some settings or some extension may cause the browser not function as expected). For all possible MIME types, check this wiki link: https://en.wikipedia.org/wiki/Internet_media_type

      Delete
  4. Sorry for misunderstanding. My question is when we export data to excel file it's working. the file save as in excel. but when i have open this file and again try to save as that time the default type is "Web Page" instead of "Excel 97-2003 work book". So can i change excel default save as type to using c#.

    ReplyDelete
  5. Hi, thanks for the solution.
    I have tried html table option and its working great.
    only problem is that excel is not showing gridlines.
    is there anyway, gridlines can be enabled?

    ReplyDelete
  6. The solutions above are not suitable for usage in offline applications (like WinForms), because there HttpContext.Current is null;

    ReplyDelete
    Replies
    1. The article is written by keeping web application in mind. By the way the HttpContext.Current is nothing to do with the logic. In winforms you can directly create/open a file and write to it, or append the contents to string builder object and dump to a file.

      Delete
  7. How to export gridviews to different sheets of excel with your 'HTML Table' method(because it is very simple)..??
    Can you help?

    ReplyDelete