Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hello,
 
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();
            dt.Columns.Add("Std_ID");
            dt.Columns.Add("Name");
            dt.Columns.Add("Father");
            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);
               
                index++;
                dt.Rows.Add(row);
            }
            app.Workbooks.Close();
            dataGridView1.DataSource = dt;
Posted 30-Nov-12 20:55pm
Rate this: bad
good
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;
for
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.
  Permalink  
Rate this: bad
good
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; 
  Permalink  
Rate this: bad
good
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)
{
	MessageBox.Show(ws1.Name);
	// or whatever you want to do with the worksheet	
}
 
  Permalink  
v2

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 359
1 _Amy 230
2 OriginalGriff 200
3 Manfred R. Bihy 200
4 Peter Leow 180
0 OriginalGriff 7,495
1 Sergey Alexandrovich Kryukov 6,402
2 Maciej Los 3,849
3 Peter Leow 3,568
4 CHill60 2,702


Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 4 Dec 2012
Copyright © CodeProject, 1999-2014
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