C# Export to Excel from class

Question

I am following the following an example to Export data from table/stored proc to Excel. https://www.c-sharpcorner.com/UploadFile/rahul4_saxena/how-to-export-multiple-data-tables-to-multiple-worksheets-in/

The only difference is that since I have an Angular/MVC project, I am using this code in the class. In the method 'Export_To_Excel()', there is Response.Clear(); and other Response methods. But I was getting error, "Response does not exist in current context." So I tried changing to fully qualified reference: HttpContext.Current.Response or System.Web.HttpContext.Current.Response but now I get error, "An object reference not set to an instance of Object"

Please guide what to do? Here is my complete code in cs file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ClosedXML.Excel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace MyProject.DAL
{
    public class CreateWorkBook
    {
        private DataTable getAllEmployeesList()
        {

            using (SqlConnection con = Connection.GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand(@"SELECT * FROM Employee ORDER BY ID;"))
                {
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        DataTable dt = new DataTable();
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = con;
                        da.SelectCommand = cmd;
                        da.Fill(dt);
                        return dt;
                    }
                }
            }
        }

        private DataTable getAllEmployeesOrderList()
        {

            using (SqlConnection con = Connection.GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand("SELECT * FROM OrderDetails ORDER BY Order_ID;"))
                {
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        DataTable dt = new DataTable();
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = con;
                        da.SelectCommand = cmd;
                        da.Fill(dt);
                        return dt;
                    }
                }
            }
        }

        public DataSet getDataSetExportToExcel()
        {
            DataSet ds = new DataSet();
            DataTable dtEmp = new DataTable("Employee");
            dtEmp = getAllEmployeesList();

            DataTable dtEmpOrder = new DataTable("Order List");
            dtEmpOrder = getAllEmployeesOrderList();
            ds.Tables.Add(dtEmp);
            ds.Tables.Add(dtEmpOrder);
            return ds;
        }
        public string SetToExport(string channel, string assets )
        {
            string status = Export_To_Excel();
            return "success";
        }
        protected string Export_To_Excel()
        {
            try
            {
                DataSet ds = getDataSetExportToExcel();
                using (XLWorkbook wb = new XLWorkbook())
                {
                    wb.Worksheets.Add(ds);
                    wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    wb.Style.Font.Bold = true;
// Error here – 
//An object reference not set to an instance of Object  
                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.Buffer = true;
                    System.Web.HttpContext.Current.Response.Charset = "";
                    System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename= SubmissionForm.xlsx");
                    using (MemoryStream MyMemoryStream = new MemoryStream())
                    {
                        wb.SaveAs(MyMemoryStream);
                        MyMemoryStream.WriteTo(System.Web.HttpContext.Current.Response.OutputStream);
                        System.Web.HttpContext.Current.Response.Flush();
                        System.Web.HttpContext.Current.Response.End();
                    }
                }

                return "success";
            }
            catch (Exception e)
            {
                throw e;
                //return "Export to Excel failed";
            }

        }

        }
}

`Response` will only be populated in the context of a web request, how are you calling your code? — sedders123, Jan 15 '19 at 23:32
I'd break this into steps. The part that creates the Excel should just return a byte array representing the file contents. It shouldn't care where the file is going - streaming to a web client, saving to a file, etc. Your web page calls that code, gets a byte array, and streams it to the client. — Scott Hannen, Jan 16 '19 at 02:45

haldo · Answer 1 · 2019-01-16T16:51:39.193

As @Scott mentioned in the comments you should break this up into smaller problems.

First create code to successfully generate the excel file. This should return a byte[]. To simplify things you could create a console app that saves the file locally to PC to begin with, test that and make sure its working.
Once part 1 is working copy the code that generates the byte[] into your web project. Then you just need to figure out how to download a file in MVC to the client.

The below code may help.

    // CreateWorkBook class
    public byte[] Export_To_Excel()
    {           
        DataSet ds = getDataSetExportToExcel();
        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(ds);
            wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            wb.Style.Font.Bold = true;

            using (MemoryStream myMemoryStream = new MemoryStream())
            {
                wb.SaveAs(myMemoryStream);

                // return memory stream as byte array
                return myMemoryStream.ToArray();
            }
        }
    }

Then in your controller you can use FileResult to return the excel file. Below is an example of how you might accomplish it in an MVC controller.

// your MVC controller
[HttpGet]
public FileResult DownloadExcel()
{
    var createExcel = new CreateWorkBook();
    byte[] excelFile = null;
    try
    {
        excelFile = createExcel.Export_To_Excel();
    }
    catch (Exception ex)
    {
        // handle exception
    }
    string fileType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    string fileName = "excel.xlsx";

    return File(excelFile, fileType, fileName);
}

score -1 · Answer 2 · answered Jan 15 '19 at 23:31

just don't use the commented code below which is not relevant with Excel export operation.

//HttpContext.Current.Response.Clear();
//HttpContext.Current.Response.Buffer = true;
//System.Web.HttpContext.Current.Response.Charset = "";
//System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename= SubmissionForm.xlsx");

using (MemoryStream MyMemoryStream = new MemoryStream())
{
    wb.SaveAs(MyMemoryStream);
    //you need to replace below line according to your need. **
    //MyMemoryStream.WriteTo(System.Web.HttpContext.Current.Response.OutputStream);
    //System.Web.HttpContext.Current.Response.Flush();
    //System.Web.HttpContext.Current.Response.End();
}

** Without knowing the structure of your project and your intend it is impossible to tell you the right way of downloading/saving this file.

I have Angular6/MVC project. I need to call some sqls based on user selection and create excel workbook with data from each dataset in a separate tab. Can you please give some suggestions? — SilverFish, Jan 16 '19 at 00:13
You can return byte[] using api endpoint. Are you using ApiConrollers? — Derviş Kayımbaşıoğlu, Jan 16 '19 at 00:15
No, I have MVC controllers. Not aware of ApiConrollers. How can it help? — SilverFish, Jan 16 '19 at 01:18

C# Export to Excel from class

2 Answers2