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

you right i was casting from DataSet to DataTable

 var saveFileDialog1 = new SaveFileDialog();
        saveFileDialog1.Filter = "Excel Dosyası (*.xlsx)|*.xlsx";
        saveFileDialog1.FilterIndex = 1;
        if (saveFileDialog1.ShowDialog() == DialogResult.OK)
        {
            try
            {
                DataSet ds = dataGridView1.DataSource as DataSet;
                if (ds != null)
                {
                    DataTable tbl = ds.Tables["hostes_tablo"];

                    FileInfo file = new FileInfo(saveFileDialog1.FileName);
                    if (file.Exists)
                    {
                        file.Delete();
                    }

                    using (ExcelPackage pck = new ExcelPackage(file))
                    {
                        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("AjansRed Sorgu Sonuç");
                        ws.Cells["A1"].LoadFromDataTable(tbl, true);
                        ws.Cells.AutoFitColumns();
                        ws.Cells[1,dataGridView1.Columns.Count+2].Value = label81.Text.ToString();
                        using (ExcelRange rng = ws.Cells[1, 1, 1, dataGridView1.Columns.Count+1])//ws.cells[from row, from column, to row, to column]. sayıların anlamı
                        {
                            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(@"Sorgu Sonucunuzu İçeren  ""{0}"" Başarıyla Dışarıya Aktarıldı!", file.Name));
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Hata! Hata! Hata! Excel Dışarı Aktarma Esnasında Sorun Oluştu. Original error: " + ex.Message);
            }
        }

Answer 2

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