如何使用epplus将过滤后的datagridview导出为ex​​cel [英] How to export filtered datagridview to excel using epplus

问题描述

我可以在datagridview中过滤日期列，但是当我尝试将其导出到excel时，它会导出整个数据表而不是过滤后的Datagridview。



这些是到目前为止我所做的部分工作



过滤日期栏目有效

I can filter a date column in my datagridview but when i try to export it to excel it exports the whole datatable and not the filtered Datagridview.

These are part of what i have done so far

Filtering for the Date column which works

private void BtnFilter_Click(object sender, EventArgs e)
       {
           bSource = new BindingSource();
           bSource.DataSource = eTable;
           dgv1.DataSource = bSource;
           bSource.Filter = string.Format("DateTime>= '{0:dd/MM/yyyy}' AND      DateTime <= '{1:dd/MM/yyyy}'", dtPickerStart.Value.Date, dtPickerStop.Value.Date);

导出整个数据表的导出事件而不是过滤的数据网格

Export event that exports whole datatable and not the filtered datagridview

}
                    using (ExcelPackage pck = new ExcelPackage(file))
                   {
                       bSource = new BindingSource();
                       bSource.DataSource = dgv1;
                       dgv1.DataSource = eTable;

                       ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
                       ws.Cells["A1"].LoadFromDataTable(((System.Data.DataTable)dgv1.DataSource), true, OfficeOpenXml.Table.TableStyles.Light1);

我怀疑我没有正确使用bindingsource。任何建议都会被赞赏

I suspect that i am not using the bindingsource correctly.Any suggestions would be appreciated

推荐答案

我在另一个站点上找到了解决方案。我必须使用DataTable.DefaultView属性来导出已过滤的datagridview。



这是导出filterd datagridview的代码

I found the solution on another site.I had to make use of the DataTable.DefaultView property to export the filtered datagridview.

This is the code to export the filterd datagridview

using (ExcelPackage pck = new ExcelPackage(file))
                   {
                       BindingSource bs = (BindingSource)dgv1.DataSource;
                       System.Data.DataTable table = (System.Data.DataTable)bs.DataSource;
                       System.Data.DataTable filtered = table.DefaultView.ToTable();

                       ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
                       ws.Cells["A1"].LoadFromDataTable(((System.Data.DataTable)filtered), true, OfficeOpenXml.Table.TableStyles.Light1);
                       using (ExcelRange rng = ws.Cells[1, 1, 1, dgv1.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);

                           ws.Column(3).Hidden = false;
                           ws.Cells["A1"].AutoFitColumns(25);
                           ws.Cells["B1"].Value = "Employee Number";
                           ws.Cells["B1"].AutoFitColumns(40);
                           ws.Cells["B1"].Value = "Employee Number";
                           ws.Cells["D1"].AutoFitColumns(60);
                           ws.Column(4).Style.Numberformat.Format = "dd/MM/yyyy";
                       }

                       pck.Save();
                   }

这篇关于如何使用epplus将过滤后的datagridview导出为ex​​cel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！