none
How to apply RowFilter and display the data on Excel worksheet( not using data grid).. RRS feed

  • Question

  • HI, I am newbie for VSTO. I am doing coding for generating Excel file. I  wan to select data with filters. I am using DataView.RowFilter for filtering the data. After filtering the data, i need to display the data into the Excel file. But, I not using DataGird for doing this.. I need the data display on the Cells on the worksheet..Can anyone help in this..?

    Thank You.

    here is my coding...

                    

     DataBaseConnection dbc = new DataBaseConnection();

                SqlConnection conn = dbc.GetDBConnection();
                dbc.OpenDBConnection();

                SqlDataAdapter da = new SqlDataAdapter("Select * from emp",conn);
                System.Data.DataTable dtData = new System.Data.DataTable();
                da.Fill(dtData);

                DataView dvData = new DataView(dtData);
                dvData.RowFilter = "ename ='"+txtEmpName.Text+"'";


                Excel.Worksheet myDemoWorkSheet = Globals.ThisWorkbook.ActiveSheet as Excel.Worksheet;
                foreach (DataRowView drv in dvData)
                {

                }

    please give me some example on this ...thanks..

    • Moved by Lisa Zhu Tuesday, January 15, 2013 10:13 AM VSTO related
    Monday, January 14, 2013 6:18 AM

Answers

  • Hi Kathleen_Fish,

    Below is the complete code snippet for your requirement:

    using (SqlConnection conn1 = new SqlConnection(strConnectionstring)
                {
                    conn1.Open();
                    string strQuery = "SELECT top 10 * FROM emp";
                    SqlDataAdapter dadptr = new SqlDataAdapter();
                    dadptr.SelectCommand = new SqlCommand(strQuery, conn1);
                    DataSet dst = new DataSet();
                    dadptr.Fill(dst);
                    DataView dvData = new DataView(dst.Tables[0]);
                    int iTotalCols = dst.Tables[0].Columns.Count;
                    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                    Workbooks xlWBs = xlApp.Workbooks;
                    Workbook xlBook = xlWBs.Add(Type.Missing);
                    Sheets xlSheets=xlBook.Worksheets;
                    Worksheet xlNewSheet=xlSheets[1];
                    
                    int iRowCtr = 2;//You need to write headers in first row
                    int iColCtr;
                    foreach (DataRowView drv in dvData)
                    {
                        for (iColCtr = 1; iColCtr <= iTotalCols; iColCtr++)
                        {
                            xlNewSheet.Cells[iRowCtr, iColCtr] = drv[iColCtr - 1].ToString();
                        }
                        iRowCtr++;
                    }
                    xlBook.SaveAs("exported_Data.xlsx", XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,true,false,XlSaveAsAccessMode.xlNoChange,
                        XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlNewSheet);
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlSheets);
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlBook);
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWBs);
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
                }

    Let me know if you need further help in resolving this.

    Rajat.

    • Proposed as answer by A K B Wednesday, January 16, 2013 7:44 AM
    • Marked as answer by Quist ZhangModerator Tuesday, January 22, 2013 5:11 AM
    Tuesday, January 15, 2013 11:36 AM

All replies

  • Try like:

    string condition = txtEmpName.Text;
    dvData.RowFilter = string.Format("ename = '{0}'", condition);

    But I would suggest you to use Select method to filter DataTable - which as a result return array of data rows (DataRow[]):

    //...
    da.Fill(dtData);
    DataRow[] filteredRows = dtData.Select(string.Format("ename = '{0}'", txtEmpName.Text));
    //now loop through rows:
    foreach(DataRow row in filteredRows)
    {
       string valueOfColumn1= row[0].ToString();
       //or by column name:
       string valueOfColumn2 = row["Column2"].ToString();
    }


    Mitja

    Monday, January 14, 2013 7:18 AM
  • Save the filtered data to dataset and export to Excel

               dataAdapter.Fill(dataSet);
               DataTable t = dataSet.Tables[0];
               //export datatable to excel
               Workbook book = new Workbook();
               Worksheet sheet = book.Worksheets[0];
               sheet.InsertDataTable(t, true, 1, 1);
               book.SaveToFile("data.xls");    
    .NET Excel component is used.

    Tuesday, January 15, 2013 2:24 AM
  • Hi Kathleen_Fish,

    From your description, I ‘d like to move this post to the most related forum.

    There are more experts in this aspect, so you will get better support and may have more luck getting answers.

    Thanks for your understanding.

    Regards,


    Lisa Zhu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Proposed as answer by Rajat Kaushal Tuesday, January 15, 2013 11:14 AM
    • Unproposed as answer by Rajat Kaushal Tuesday, January 15, 2013 11:14 AM
    Tuesday, January 15, 2013 10:12 AM
  • Hi Kathleen_Fish,

    Below is the complete code snippet for your requirement:

    using (SqlConnection conn1 = new SqlConnection(strConnectionstring)
                {
                    conn1.Open();
                    string strQuery = "SELECT top 10 * FROM emp";
                    SqlDataAdapter dadptr = new SqlDataAdapter();
                    dadptr.SelectCommand = new SqlCommand(strQuery, conn1);
                    DataSet dst = new DataSet();
                    dadptr.Fill(dst);
                    DataView dvData = new DataView(dst.Tables[0]);
                    int iTotalCols = dst.Tables[0].Columns.Count;
                    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                    Workbooks xlWBs = xlApp.Workbooks;
                    Workbook xlBook = xlWBs.Add(Type.Missing);
                    Sheets xlSheets=xlBook.Worksheets;
                    Worksheet xlNewSheet=xlSheets[1];
                    
                    int iRowCtr = 2;//You need to write headers in first row
                    int iColCtr;
                    foreach (DataRowView drv in dvData)
                    {
                        for (iColCtr = 1; iColCtr <= iTotalCols; iColCtr++)
                        {
                            xlNewSheet.Cells[iRowCtr, iColCtr] = drv[iColCtr - 1].ToString();
                        }
                        iRowCtr++;
                    }
                    xlBook.SaveAs("exported_Data.xlsx", XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,true,false,XlSaveAsAccessMode.xlNoChange,
                        XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlNewSheet);
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlSheets);
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlBook);
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWBs);
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp);
                }

    Let me know if you need further help in resolving this.

    Rajat.

    • Proposed as answer by A K B Wednesday, January 16, 2013 7:44 AM
    • Marked as answer by Quist ZhangModerator Tuesday, January 22, 2013 5:11 AM
    Tuesday, January 15, 2013 11:36 AM