CODEWITHSUNDEEP
excelvstoexcel-interop
SamBerk
SamBerk

Reputation: 531

Excel table to DataTable (VSTO)

I have an Excel VSTO Add-in, and I have to get excel table data into a DataTable class, I've already implemented it by looping through the cells but its a bit slow:

            DataTable dataTable = new DataTable(table.Name);

            foreach (Excel.ListColumn column in table.ListColumns)
                dataTable.Columns.Add(column.Name);

            for (int r = 2; r < table.ListRows.Count + 2; r++)
            {
                DataRow row = dataTable.NewRow();

                for (int c = 1; c < table.ListColumns.Count + 1; c++)
                    row[c - 1] = ((Excel.Range)table.Range[r, c]).Value;

                dataTable.Rows.Add(row);
            }

So my question is, is there any Method (or fast way) in Excel interop to export table data to a DataTable class?

Thanks

Upvotes: 2

Views: 479

Answers (1)

Excelosaurus
Excelosaurus

Reputation: 2849

You can read all values in the DataBodyRange of your table (a ListObject I deduce) at once, into a 1-based 2D object array, like so:

var theValues = table.DataBodyRange.Value;

It's then just a matter of scanning this array.

Upvotes: 3

Related Questions