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);

                    }
                }
            }
        }
    }

It looks like it'd be simpler to write to a csv file like [this](http://stackoverflow.com/questions/18757097/writing-data-into-csv-file) or if you'd rather excel check out this [question](http://stackoverflow.com/questions/23041021/how-to-write-some-data-to-excel-file-xlsx) — scurrie, Mar 25 '15 at 20:44
@scurrie I've already looked into that option, and its not a plausible solution for me. — John, Mar 25 '15 at 20:47
To clarify, is the issue fetching data from the DB or writing the data to an excel file? Or both? — scurrie, Mar 25 '15 at 20:57
@scurrie I have no problem getting the data from the db. I've been able to fetch the data from the db and display it onto a dgv without problems. However, what I don't understand is how to "transfer" (aka read) the data to an excel file. — John, Mar 25 '15 at 21:01
I found tasks like this are more easily done in Excel VBA. You have to revert to ADO (or DAO) to get the recordset, but once you have it you have a lot more control over the process. I developed a simple recordset to worksheet set of code and just modify the SQL. — rheitzman, Mar 25 '15 at 21:31
ditto scurrie's write to csv. To do a native Excel file you need MS Office app or a library. I would guess there are some sample Excel XML code snippets out there. — rheitzman, Mar 25 '15 at 21:33

scurrie · Accepted Answer · 2015-03-25T22:07:02.297

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.

Thats the thing, I never fetched any data. I simply want to read directly from the database, to the excel file. — John, Mar 25 '15 at 21:48
I've update my question with new code that works. However, the problem with the code is that I get all the columns from the database table into the DataTable, which in turn, causes some issues with excel. How do I get only specific columns from the database? — John, Mar 25 '15 at 21:54
@John Ahhh I got it now lol. Sorry about that, it took me a second to get what you were asking. Anyways, I updated my answer; I think a quick edit to your SQL statement should do the trick — scurrie, Mar 25 '15 at 22:11

Database Data to Excel File (C#)

1 Answers1