How Can I Export Filtered Data from SQL Server to Excel?

Question

I have a WinForm that is meant to filter and display the data from my database to its DataGridView. To filter, I placed a combobox where the names of the columns from the database are displayed and can be selected, and a textbox where the user can type a keyword or phrase. Then the user clicks the Filter button to execute the filtering.

I also have a button to export the database table to an Excel file.

My method for the filtering:

protected void searchFilter()
    {
        DataTable dt;
        BindingSource bs = new BindingSource();

        _db.conn();
        _db.cmd.CommandText = "SELECT * FROM IncomingLog";
        dt = _db.executeDT();

        DataView dv = new DataView(dt);
        incomLogTableS.DataSource = dv;

        String cmbCat = cmbFilterIDLS.GetItemText(cmbFilterIDLS.SelectedValue.ToString());
        String keyID = keyIDLS.Text;

        if (cmbCat != "Select Category")
        {
            if (cmbCat == "Received Date")
            {
                dv.RowFilter = string.Format("[Date Received] LIKE '%{0}%'", keyID);
            }
            else if (cmbCat == "Reference Number")
            {
                dv.RowFilter = string.Format("[Reference Number] LIKE '%{0}%'", keyID);
            }
            else if (cmbCat == "Received Time")
            {
                dv.RowFilter = string.Format("[Time Received] LIKE '%{0}%'", keyID);
            }
            else if (cmbCat == "Title/Description")
            {
                dv.RowFilter = string.Format("[Title/Description] LIKE '%{0}%'", keyID);
            }
            else if (cmbCat == "Originating Office")
            {
                dv.RowFilter = string.Format("[Originating Office] LIKE '%{0}%'", keyID);
            }
            else if (cmbCat == "Received By")
            {
                dv.RowFilter = string.Format("[Receiving Person] LIKE '%{0}%'", keyID);
            }
            else if (cmbCat == "Filed Under")
            {
                dv.RowFilter = string.Format("[Filed Under] LIKE '%{0}%'", keyID);
            }
            else
            {
                dv.RowFilter = string.Format("[Encoded By] LIKE '%{0}%'", keyID);
            }
        }
        else
        {
            MessageBox.Show("Please select a category to search!", "Select category", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        }

        if (dv.Count == 0)
        {
            MessageBox.Show("No records found! \nPlease try with different keyword(s).", "(0) Records Found", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

The method for the export button:

private void exportIDLS_Click(object sender, EventArgs e)
    {
        searchFilter();

        string data = null;
        int i = 0;
        int j = 0;

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        Excel.Range range;
        object misValue = System.Reflection.Missing.Value;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        DataSet ds = new DataSet();
        ds.Tables.Add(dt);

        for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
        {
            for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
            {
                xlWorkSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                range = xlWorkSheet.Cells[1, j + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                xlWorkSheet.Cells[i + 2, j + 1] = data;
            }
        }

        Microsoft.Office.Interop.Excel.Range columns = xlWorkSheet.UsedRange.Columns;
        columns.AutoFit();

        xlApp.StandardFont = "Arial";
        xlApp.StandardFontSize = 11;

        xlWorkSheet.Rows[1].Insert();
        Excel.Range newRow = xlWorkSheet.Rows[1];
        Excel.Range newCell = newRow.Cells[1];
        newCell.Value = "Incoming Documents Log Summary";
        newCell.Font.Size = 12;
        newCell.Font.Bold = true;

        xlWorkBook.SaveAs("Incoming Documents Log Summary.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);

        MessageBox.Show("Success! Incoming Documents Log Summary.xls is created! \nPlease look at the Documents folder.", "Excel file created!", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }

This is working now, but the exported Excel file gets all of the data from the database. What can I do so the Export button gets the data from the filtered DataGridView?

Please help me. Thank you very much!

Answer 1

Use the DataView.ToTable method to get the filtered result:

DataTable dtFiltered = dv.ToTable();

for (i = 0; i <= dtFiltered.Rows.Count - 1; i++)
{
     for (j = 0; j <= dtFiltered.Columns.Count - 1; j++)
     {

---

To access the DataView dv in your exportIDLS_Click event, declare the variable at the class level making dv a member variable:

private DataView m_dv = null;  
protected void searchFilter()
{
  ...
  m_dv = new DataView(dt);

Or in the exportIDLS_Click event, get the DataView from the DataGridViews DataSource:

DataView dv = (DataView)incomLogTableS.DataSource;
DataTable dtFiltered = dv.ToTable();

---

If you want to keep the Grid Style's in Excel you can see my answer here:

Export the dataGridView to Excel with all the cells format :