question

PengfeiLiu-9437 avatar image
0 Votes"
PengfeiLiu-9437 asked PengfeiLiu-9437 commented

How to import multiple Excel worksheets into one datagridview?

I am using C# to build a WinForm application. I would import the data from a Excel file, which has multiple worksheets. Those worksheets has different rows and different columns. I want to allow the user to select multiple worksheets and import them into a datagridview. So the user has the ability to fix the errors in the data. After that, the code could do the job using the correct data.

Now I can import one worksheet using Oledb. What I learn is Oledb does work with multiple worksheets if the worksheets have different 'structure' (different columns). Is there any other approach to achieve my goal? Thanks.

dotnet-csharp
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


If your procedures load particular worksheets to DataTable objects, then you can merge these objects, obtaining a single DataTable, which can be then shown in grid view. Did you or the users already decide how to merge and present the data? There is also a DataTable.Merge function.


1 Vote 1 ·

Thanks. DataTable.Merge function does help a lot.

0 Votes 0 ·

Hi Viorel-1,

May I reopen this topic as I got some unexpected data when using datatable.merge()?

Datatble1 looks like this:
114866-image.png

Datatable 2 looks like this:
114840-image.png

After Datatable1.merge(Datatable2), I got this:
114786-image.png

What I expected was this:
114873-image.png

Is there any way to ignore the header and just merge the data? Thanks.

0 Votes 0 ·
image.png (6.9 KiB)
image.png (7.2 KiB)
image.png (11.2 KiB)
image.png (7.2 KiB)

After loading the data, try assigning the same names to columns, for example:

for( int i = 0; i < dataTable1.Columns.Count; ++i ) dataTable1.Columns[i].ColumnName = "Col" + i;

Do this for the second data table too, then call Merge.


1 Vote 1 ·
Show more comments

0 Answers