Click here to Skip to main content
12,954,276 members (74,066 online)
Rate this:
Please Sign up or sign in to vote.

I want to read multiple worksheets which exist within same excel file. Following is my simple code which read first worksheet (by default), i want to read second as well. There will be change in argument but i don't know. Your help is highly appreciated!

Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Open(@path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
            int index = 0;
            object rowIndex = 1;
            DataTable dt = new DataTable();
            DataRow row;
            while (((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2 != null)
                rowIndex = 2 + index;
                row = dt.NewRow();
                row[0] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 1]).Value2); 
                row[1] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 2]).Value2);
                row[2] = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)workSheet.Cells[rowIndex, 3]).Value2);
            dataGridView1.DataSource = dt;
Posted 30-Nov-12 20:55pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Firstly your comment
"which read first worksheet (by default)"
isn't quite accurate - it's simply that the first sheet was active the last time you saved the spreadsheet ... if you had left the 2nd sheet visible before saving and closing the spreadsheet then the second sheet would have been read "by default"!

So to access the first sheet deliberately you need to swap
Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;

Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets[1];

Hopefully you can see now that you can get to the second sheet by changing the index to .Sheets[2] (note that Sheets starts at index [1] not [0]), and that you could go through all of the sheets by using a foreach loop.

Also, if you don't mind me suggesting the following ... the "Microsoft.Office.Interop.Excel." scattered throughout your code isn't necessary if you use
using Microsoft.Office.Interop.Excel;

at the outset. If you are concerned that this could clash with Application in other namespaces then you could use
using Excel = Microsoft.Office.Interop.Excel;
so the example above would become
Excel.Worksheet workSheet = workBook.Sheets[1];
Much easier to read.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

To read upto number of sheets, we need to count before reading

int count = workBook.Sheets.Count; 
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

Can't see the comment here for some reason but I got an email asking if there was a method to read until worksheets no longer exist - I did put a clue on how to do this in solution 1 !

However, here is an example

using xl = Microsoft.Office.Interop.Excel;
// ... this code under a button on my sample
xl.Application xlapp = new xl.Application();
xl.Workbook wb = xlapp.Workbooks.Open(@"c:\test.xls", 0, true, 5, "", "", true, xl.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xl.Worksheet ws = (xl.Worksheet)wb.Sheets[1];
foreach (xl.Worksheet ws1 in wb.Sheets)
	// or whatever you want to do with the worksheet	

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 6,524
CHill60 3,490
Maciej Los 3,123
ppolymorphe 2,030
Jochen Arndt 1,975

Advertise | Privacy | Mobile
Web02 | 2.8.170525.1 | Last Updated 4 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100