468,117 Members | 1,452 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,117 developers. It's quick & easy.

Struggling to add filtered data from a excel file to datagridview using C# interop?

2 2Bits
Say I've got a big excel file with multiple row & column (sample attached):

Expand|Select|Wrap|Line Numbers
  1. Row ID        Order ID        Order Date            State
  2. 1            CA-2016-152156    8 November 2016        Kentucky
  3. 2            CA-2016-152156    8 November 2016        Kentucky
  4. 3            CA-2016-138688    12 June 2016            California
  5. 4            US-2015-108966    11 October 2015        Florida
  6. 5            US-2015-108966    11 October 2015        Florida
  7. 6            CA-2014-115812    9 June 2014            California
  8. 7            CA-2014-115812    9 June 2014            California
  9. 8            CA-2014-115812    9 June 2014            California
  10. 9            CA-2014-115812    9 June 2014            California
  11. 10            CA-2014-115812    9 June 2014            California
  12. 11            CA-2014-115812    9 June 2014            California
  13. 12            CA-2014-115812    9 June 2014            California
  14.  
I'm trying to open this file in read-only mode then apply a autofilter and then add that filtered data to a datagridview (minus some columns e.g. Row ID and State). I've created a windows form app with a datagridview and 2 buttons namely Show and Exit

I've done:
Expand|Select|Wrap|Line Numbers
  1.  using System;
  2.  using System.Collections.Generic;
  3.  using System.IO;
  4.  using System.Linq;
  5.  using System.Runtime.InteropServices;
  6.  using System.Data;
  7.  using System.Threading.Tasks;
  8.  using Excel = Microsoft.Office.Interop.Excel;
  9.  using System.Windows.Forms;
  10.  
  11.  namespace FinalExcel
  12.  {
  13.      /// <summary>
  14.      /// Description of MainForm.
  15.      /// </summary>
  16.      public partial class MainForm : Form
  17.      {
  18.          public MainForm()
  19.          {
  20.              InitializeComponent();
  21.          }
  22.  
  23.          private Excel.Range filteredRange=null;
  24.  
  25.  
  26.          void ButtonShowClick(object sender, EventArgs e)
  27.          {
  28.  
  29.              try {
  30.  
  31.                  //Create COM Objects. Create a COM object for everything that is referenced
  32.                  Excel.Application xlApp = new Excel.Application();
  33.                  Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:\DD21\tempdata.xlsx");
  34.                  Excel.Worksheet xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkbook.Sheets[1];
  35.                  Excel.Range xlRange = xlWorksheet.UsedRange;
  36.  
  37.  
  38.  
  39.                  xlRange.AutoFilter(4, "Florida", Excel.XlAutoFilterOperator.xlFilterValues, Type.Missing, true);
  40.  
  41.                  filteredRange = xlRange.SpecialCells(Excel.XlCellType.xlCellTypeVisible, Excel.XlSpecialCellsValue.xlTextValues);
  42.  
  43.                  if (filteredRange != null)
  44.                  {
  45.                      DataTable dt = ConvertRangeToDataTable();
  46.                      if (dt != null) { dataGridView1.DataSource = dt; }
  47.                  }
  48.  
  49.                  //cleanup
  50.                  GC.Collect();
  51.                  GC.WaitForPendingFinalizers();
  52.  
  53.                  //release com objects to fully kill excel process from running in the background
  54.                  Marshal.ReleaseComObject(xlRange);
  55.                  Marshal.ReleaseComObject(xlWorksheet);
  56.  
  57.                  //close and release
  58.                  xlWorkbook.Close();
  59.                  Marshal.ReleaseComObject(xlWorkbook);
  60.  
  61.                  //quit and release
  62.                  xlApp.Quit();
  63.                  Marshal.ReleaseComObject(xlApp);
  64.              }
  65.  
  66.              catch (Exception ex)
  67.              {
  68.  
  69.                  MessageBox.Show(ex.ToString());
  70.              }
  71.          }
  72.          private DataTable ConvertRangeToDataTable() {
  73.              try {
  74.                  DataTable dt = new DataTable();
  75.                  int ColCount = filteredRange.Columns.Count;
  76.                  int RowCount = filteredRange.Rows.Count;
  77.  
  78.                  for (int i = 0; i < ColCount; i++) {
  79.                      DataColumn dc = new DataColumn();
  80.                      dt.Columns.Add(dc);
  81.                  }
  82.                  for (int i = 1; i <= RowCount; i++) {
  83.                      DataRow dr = dt.NewRow();
  84.                      for (int j = 1; j <= ColCount; j++) { dr[j - 1] = ((Excel.Range)filteredRange.Cells[i, j]).Value2; }
  85.                      dt.Rows.Add(dr);
  86.                  }
  87.                  return dt;
  88.              }
  89.              catch { return null; }
  90.          }
  91.          void ButtonExitClick(object sender, EventArgs e)
  92.          {
  93.  
  94.              this.Close();
  95.          }
  96.      }
  97.  }
  98.  
But when I hit the Show button, the datagridview shows only the header columns and the program freezes. I think there is a problem in my ConvertRangeToDataTable method but being new to this I cannot figure out how to solve this.

Please help...

Further, I wish to show only few of the columns from the excel after the autofilter to the datagridview, how can I do that as well?

Thanks in advance
Feb 28 '21 #1
1 1761
SioSio
238 128KB
If you want to work with non-contiguous ranges, use the Areas property.

Expand|Select|Wrap|Line Numbers
  1.         private DataTable ConvertRangeToDataTable() {
  2.             try {
  3.                 DataTable dt = new DataTable();
  4.                 int ColCount = filteredRange.Columns.Count;
  5.                 int RowCount = filteredRange.Rows.Count;
  6.  
  7.                 for (int i = 0; i < ColCount; i++) {
  8.                     DataColumn dc = new DataColumn();
  9.                     dt.Columns.Add(dc);
  10.                 }
  11.  
  12.                 foreach (Excel.Range item in filteredRange.Areas)
  13.                 {
  14.                     int i = 1;
  15.                     foreach(Excel.Range row in item.Rows)
  16.                     {
  17.                         Excel.Range range1 = (Excel.Range)row.get_Range(row.Address);
  18.                         DataRow dr = dt.NewRow();
  19.                         for (int j = 1; j <= ColCount; j++) { dr[j - 1] = ((Excel.Range)range1.Cells[i, j]).Value2; }
  20.                         dt.Rows.Add(dr);
  21.                         i++;
  22.                         Marshal.ReleaseComObject(range1);
  23.                     }
  24.                 }
  25.                 return dt;
  26.             }
  27.             catch { return null; }
  28.         }
1 Days ago #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by shaahid786 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.