Export a Inner join Datagridview to Excel

Question

I would like to export a excel file with a inner join datagridview by using EPPLUS.

How should I do for this line? excelWorksheet1.Cells.LoadFromCollection<>(dataGridView4.DataSource as List<>, true);

    private void Form1_Load(object sender, EventArgs e)
    {
        using (DBEntities1 db = new DBEntities1())
        {
            var query = from p in db.P_DB
                        join d in db.P_Details on p.P_Id equals d.P_Id
                        select new
                        {
                            p.P_Id,
                            p.Case_number,
                            p.Client,
                            p.Model,
                            d.Details_Id
                        };

            dataGridView4.DataSource = query.ToList();

        }
    }

    private void btnExport_Click(object sender, EventArgs e)
    {
        using (SaveFileDialog saveFileDialog = new SaveFileDialog() { Filter = "Excel Workbook|*.xlsx" })
        {
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    var fileInfo = new FileInfo(saveFileDialog.FileName);
                    using (var package = new ExcelPackage(fileInfo))
                    {
                        ExcelWorksheet excelWorksheet1 = package.Workbook.Worksheets.Add("P_Details");
                        **excelWorksheet1.Cells.LoadFromCollection<>(dataGridView4.DataSource as List<>, true);**
                    }
                    MessageBox.Show("You have successfully exported your data to an excel file.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }
    }

user_16984486 · Answer

After lots of Google and Google and Google...

I found the answer!

First, transfer datagridview to datatable. Then everything fine...

    private void btnExport_Click(object sender, EventArgs e)
    {
        using (SaveFileDialog saveFileDialog = new SaveFileDialog() { Filter = "Excel Workbook|*.xlsx" })
        {
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    var fileInfo = new FileInfo(saveFileDialog.FileName);

                    DataTable dt = new DataTable();
                    foreach (DataGridViewColumn col in dataGridView4.Columns)
                    {
                        dt.Columns.Add(col.Name);
                    }

                    foreach (DataGridViewRow row in dataGridView4.Rows)
                    {
                        DataRow dRow = dt.NewRow();
                        foreach (DataGridViewCell cell in row.Cells)
                        {
                            dRow[cell.ColumnIndex] = cell.Value;
                        }
                        dt.Rows.Add(dRow);
                    }

                    using (var package = new ExcelPackage(fileInfo))
                    {
                        ExcelWorksheet excelWorksheet1 = package.Workbook.Worksheets.Add("P_Details");
                        excelWorksheet1.Cells.LoadFromDataTable(dt, true);
                        package.Save();
                    }
                    MessageBox.Show("You have successfully exported your data to an excel file.", "Message",

MessageBoxButtons.OK, MessageBoxIcon.Information); }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }
    }

Export a Inner join Datagridview to Excel

1 Answers