title: Database Data to Excel File (C#)
body: <p>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 <strong>read</strong> data from my database to a new excel file?</p>
  
  <p>Here is my code so far:</p>
  
  <pre><code>        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);
              }
  
          }
      }
  </code></pre>
  
  <p><strong>UPDATED:</strong></p>
  
  <pre><code>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);
  
                      }
                  }
              }
          }
      }
  </code></pre>