How to read multiple worksheets within single excel in C#?

Question

5.00/5 (1 vote)

See more:

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!

C#

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

Haseeb4328

Updated 14-Feb-18 0:36am

Add a Solution

3 solutions

Solution 3

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

C#

int count = workBook.Sheets.Count;

Posted 3-Dec-12 17:43pm

Haseeb4328

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	
}

Posted 3-Dec-12 22:24pm

CHill60

v2

Comments

Member 11403220 26-Sep-18 2:18am

I want to read the sheet 2 and sheet 1 data from same workbook .I tried this way

xl.Worksheet ws = (xl.Worksheet)wb.Sheets[1];
xl.Worksheet ws = (xl.Worksheet)wb.Sheets[2];

but any how its showing error for 2nd statement (xl.Worksheet ws = (xl.Worksheet)wb.Sheets[1];) bad index error occured.

Can you help me out ??

I want to read both sheets data i.e Sheet1 and Sheet2 both.

Its urgent please help me out.

CHill60 26-Sep-18 18:22pm

If the error is in the 2nd statement then you simply don't have a 2nd sheet. See the solution above

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2012-12-02T06:45:00

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.