如何仅将选定的行从datagridview导出到excel？ [英] How to export only selected rows from datagridview into excel?

问题描述

如何将选定的行从dgv导出到excel？以下是我导入excel并找到值的代码。

How can I export selected rows from the dgv into excel? Below is my code for importing the excel and finding the value.

    private void button1_Click(object sender, EventArgs e)
    {

        OpenFileDialog openFileDialog1 = new OpenFileDialog();
        if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
        {               
            //this.textBox1.Text = openFileDialog1.FileName;


            //string PathConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes:\";";
            String PathConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=\"Excel 12.0 XML;HDR=Yes:\";";                        
            OleDbConnection conn = new OleDbConnection(PathConn);
            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [sheet1$]", conn);
            DataTable dt = new DataTable();
            myDataAdapter.Fill(dt);
            dataGridView1.DataSource = dt;

        }
    }


    private void button2_Click(object sender, EventArgs e)
    {
        dataGridView1.ClearSelection();
        dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
        try
        {          
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                if (row.Cells["Name"].Value.ToString().ToUpperInvariant().Contains(textBox1.Text.ToUpperInvariant()))
                {
                    dataGridView1.Rows[row.Index].Selected = true;                        
                }

            }

        }
        catch (Exception)
        {

        }
    }

推荐答案

我假设你知道如何将DataTable导出到excel （您可以使用Aspose Cells或Interop等现有库），因此我只需编写创建该DataTable的代码：

I assume you know how to export a DataTable to excel(you can use existing libraries like Aspose Cells or using Interop), so I just write the code for creating that DataTable:

private DataTable CreateDataTableFromSelectedRows()
{
    var dtToExport = new DataTable();
    foreach(DataGridViewRow row in dataGridView1.SelectedRows)
    {
        var dr = row.DataBoundItem as DataRow;
        dtToExport.ImportRow(dr);
    }
    return dtToExport;
}

如果您使用 Aspose cells 它只会变成：

If you use Aspose cells it would simply become:

private void btnExportSelectedRows(object sender, EventArgs e)
{
        string filePath = "C:\\test.xlsx";
        Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
        wb.FileFormat = Aspose.Cells.FileFormatType.Xlsx;

        Worksheet sheet = wb.Worksheets[0];

        DataTable dt = CreateDataTableFromSelectedRows();
        sheet.Cells.ImportDataTable(dt, true, "A1");
        wb.Save(filePath);
}

但是，通过使用您提供的导出代码，所有这些都将成为：
（我删除了代码中的现有注释，以简洁）

But by using the export code you provide it all become: (I removed existing comments on the code for brevity)

private void button1_Click_1(object sender, EventArgs e) 
{
    Microsoft.Office.Interop.Excel._Application app  = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel._Workbook workbook =  app.Workbooks.Add(Type.Missing);
    Microsoft.Office.Interop.Excel._Worksheet worksheet = null;                   
    app.Visible = true;
    worksheet = workbook.Sheets["Sheet1"];
    worksheet = workbook.ActiveSheet;
    worksheet.Name = "Exported from gridview";
    for(int i=1;i<dataGridView1.Columns.Count+1;i++)
    {
         worksheet.Cells[1, i] = dataGridView1.Columns[i-1].HeaderText;
    }

    // It's the part which we are interested in
    // we just need to change dataGridView1.Rows to dataGridView1.SelectedRows
    for (int i=0; i < dataGridView1.SelectedRows.Count ; i++)
    {
        for(int j=0;j<dataGridView1.Columns.Count;j++)
        {
            worksheet.Cells[i + 2, j + 1] = dataGridView1.SelectedRows[i].Cells[j].Value.ToString();
        }
    }

    workbook.SaveAs("c:\\output.xls",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , Type.Missing, Type.Missing, Type.Missing, Type.Missing);

  app.Quit();
}

我想这会做的。

这篇关于如何仅将选定的行从datagridview导出到excel？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！