如何在C＃中读取单个excel中的多个工作表？ [英] How to read multiple worksheets within single excel in C#?

问题描述

您好，



我想阅读同一个excel文件中存在的多个工作表。以下是我的简单代码，它读取了第一个工作表（默认情况下），我也想读第二个。争论会有变化，但我不知道。非常感谢您的帮助！

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;

推荐答案

首先你的评论

"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

for

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

希望你现在可以看到你可以通过将索引更改为.Sheets [2]来获得第二张表（注意表格从索引[1]开始]不是[0]），和你可以通过使用foreach循环遍历所有工作表。



另外，如果你不介意我建议以下......微软。 Office.Interop.Excel。如果您在开始时使用Microsoft.Office.Interop.Excel;

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 

使用

 so the example above would become 

Excel.Worksheet workSheet = workBook.Sheets[1];

更容易阅读。

要读取页数，我们需要先计算阅读

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

int count = workBook.Sheets.Count; 

由于某些原因无法在此处看到评论，但我收到一封电子邮件，询问是否有方法可以阅读，直到工作表不再存在 - 我确实放了关于如何在解决方案1中执行此操作的线索！



但是，这是一个示例

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	
}

这篇关于如何在C＃中读取单个excel中的多个工作表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！