Database Data to Excel File (C#)

Question

I want to include an option in my program to export some data from the database to an excel file. There are multiple columns in my database table, but only two will be exported (item and quantity). The 'category' indicates which dgv the data is part of. But anyways, back to the issue...how do I read data from my database to a new excel file?

Here is my code so far:

        private void excelToolStripMenuItem_Click(object sender, EventArgs e)
        {
        //EXPORT TO EXCEL
        string fname = "Inventory Report.xls";
        Workbook wb = new Workbook();
        Worksheet ws1 = new Worksheet("Electrical");

        Connection();
        sqlconnection.Open();

        //string dbQuery = "SELECT * FROM inventory_table WHERE category= 0";
        sqlcmd = new SqlCommand("SELECT * FROM inventory_table WHERE category= 0", sqlconnection);

        using (SqlDataReader sqldr = sqlcmd.ExecuteReader())
        {
            if (sqldr.HasRows)
            {
                while (sqldr.Read())
                {
                    //HOW DO I READ TO EXCEL???
                }

                sqldr.Close();
                wb.Worksheets.Add(ws1);
                wb.Save(fname);
            }

        }
    }

UPDATED:

private void excelToolStripMenuItem_Click(object sender, EventArgs e)
    {
        Connection();
        sqlconnection.Open();

        using (sqlcmd = new SqlCommand("SELECT * FROM inventory_table WHERE category= 0"))
        {
            using (SqlDataAdapter sqlda = new SqlDataAdapter())
            {
                sqlcmd.Connection = sqlconnection;
                sqlda.SelectCommand = sqlcmd;

                using (DataTable dt = new DataTable())
                {
                    sqlda.Fill(dt);

                    using (XLWorkbook wb = new XLWorkbook())
                    {
                        string fname = "Inventory Report.xlsx";
                        wb.Worksheets.Add(dt, "inventory_table");
                        wb.SaveAs(fname);

                    }
                }
            }
        }
    }

Answer 1

If you are creating an excel doc via a web app or something similar you might check out EPPlus which is available as a NuGet package and seems to do what you need.

Otherwise take a look at the MS docs regarding using office interop objects. Start by adding a reference for Microsoft.Office.Interop.Excel, then adding a using statement to your code:

using Excel = Microsoft.Office.Interop.Excel;

Then create a new excel document like this:

var excelDoc = new Excel.Application();
excelDoc.Visible = true;
excelDoc.Workbooks.Add();
Excel._Worksheet wksht = (Excel.Worksheet)excelDoc.ActiveSheet;

Finally, you'll need to loop through the data you fetched from the DB and insert it somewhere:

wksht.Cells[1, "A"] = "Some data";

Granted, you'll likely want to use index variables that you can increment as you loop through. Hope this helps!

Edit

As far as getting the appropriate data is concerned, just make a quick edit to your SQL statement. Right now you're using:

SELECT * FROM inventory_table WHERE category = 0

Using * in your select statement will pull in every single column in the table. Instead you should tell it exactly what you want:

SELECT things, stuff, junk FROM inventory_table WHERE category = 0

Alternatively you could use Linq which I find makes cleaner code for queries:

var getThings = (from i in db.inventory_table select i.things);

With that you'll need to add a using statement for System.Linq as well as declare db as an ApplicationDbContext.