Blog » Easy Export to Excel with C#

Easy Export to Excel with C#

imageUsing a class provided by Chris on stackoverlow, I finally found a quick and dirty way to download data to a CSV file locally.

By adding the attached class file, I was able to easily add a button to a report page, that when pressed, will load the each line of the CsvExport object into a byte[] suitable for quick and dirty downloads.

The code looks something like this:

        /// <summary>
        /// btnDownloadInExcel_Click() this method, which relies on GetByteArray(), will force a download of a CSV file that opens properly in Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnDownloadInExcel_Click(object sender, EventArgs e)
        {
            byte[] fileContents = GetByteArray();
            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("Content-Length", fileContents.Length.ToString());
            Response.AppendHeader("Content-Disposition", "attachment; filename=NameOfFileToDownload.csv");
            Response.BinaryWrite(fileContents);
            Response.Flush();
            Response.End();
        }

        protected byte[] GetByteArray()
        {
            CsvExport myExport = new CsvExport();
            
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["DBConn"]))
            {
                string query = "SELECT somedata FROM sometable WHERE somefield=@somevalue";
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@somevalue", true);
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.HasRows)
                        {
                            while (rdr.Read())
                            {
                                myExport.AddRow();
                                myExport["ProductId"] =Convert.ToInt32( rdr["ProductID"].ToString());
                                myExport["VariantId"] = Convert.ToInt32(rdr["VariantID"]);
                                myExport["SKU"] = rdr["SKU"].ToString();
                                myExport["FullName"] = rdr["FullName"].ToString();
                                myExport["Price"] = Convert.ToDecimal(rdr["Price"]);
                            }
                        }
                    }
                }

            }
            
            byte[] ret_value = myExport.ExportToBytes();
            return ret_value;
        }

Obviously you would modify the lines following myExport.AddRow(); lines to reflect the columns of data that you want to include in the spreadsheet.

 

Anyone else have code they prefer to use for this purpose? Use the comments below to share will all of us and thanks again to Chris and stackoverflow!

csvexport.cs (3.43 kb)

Michael Gibbs
Stalk Me...
Latest posts by Michael Gibbs (see all)