Epplus Export Error like this : 'System.Data.DataSet' the object type 'System.Data.DataTable' not taken in kind.

Question

I wanna convert a gridview to .xls but it throws error and when I click ok its give me " Failed to export to excel. Original error: 'System.Data.DataSet' the object type 'System.Data.DataTable' not taken in kind. " Here is my code;

My search button

        groupBox2.Visible = true;
        SqlConnection baglanti = new SqlConnection("Data Source=.; Initial Catalog=database; Trusted_Connection=yes; MultipleActiveResultSets=True");
        SqlDataAdapter da = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand();
        DataSet ds = new DataSet();
        baglanti.Open();
        cmd.CommandText = "SELECT * FROM hostes_tablo WHERE ayak_no=" + comboBox7.Text + "";

        da.SelectCommand = cmd;
        cmd.Connection = baglanti;
        da.Fill(ds, "hostes_tablo");

        dataGridView1.DataSource = ds;
        dataGridView1.DataMember = "hostes_tablo";
        baglanti.Close();

My Export button

var saveFileDialog1 = new SaveFileDialog();
        saveFileDialog1.Filter = "Excel File (*.xlsx)|*.xlsx";
        saveFileDialog1.FilterIndex = 1;
        if (saveFileDialog1.ShowDialog() == DialogResult.OK)
        {
            try
            {
                FileInfo file = new FileInfo(saveFileDialog1.FileName);
                if (file.Exists)
                {
                    file.Delete();
                }

                using (ExcelPackage pck = new ExcelPackage(file))
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
                    ws.Cells["A1"].LoadFromDataTable(((DataTable)dataGridView1.DataSource), true);
                    ws.Cells.AutoFitColumns();

                    using (ExcelRange rng = ws.Cells[1, 1, 1, dataGridView1.Columns.Count])
                    {
                        rng.Style.Font.Bold = true;
                        rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));
                        rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
                    }

                    pck.Save();
                    pck.Dispose();

                }

                MessageBox.Show(string.Format("Excel file \"{0}\" generated successfully.", file.Name));
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed to export to Excel. Original error: " + ex.Message);
            }
        }

i search then when am i click export button its give me error.

Answer 1

I think the casting from DataSet to DataTable is giving the error. You cannot cast DataSet to DataTable directly.

Use the code below to do that.

BindingSource bs = (BindingSource)dataGridView1.DataSource; 
DataTable dt= (DataTable) bs.DataSource;

 ws.Cells["A1"].LoadFromDataTable(dt, true);