C＃读取多个Excel文件 [英] C# read multiple Excel files

问题描述

是否可以让应用程序从文件夹中读取多个excel文件，并从中提取一些信息？

解决方案

是的，这里是如何使用Interop。您需要做的第一件事是将Excel Interop库添加到您的项目中。您可以通过创建一个新的Visual Studio解决方案，右键单击参考，选择添加参考，然后选择 Microsoft.Office.Interop.Excel 从.NET选项卡。

然后，您需要为Excel添加一个using语句，一个用于InteropServices（因为我们使用COM对象进行间接）：

 使用Excel = Microsoft.Office.Interop.Excel; 
使用System.Runtime.InteropServices; 
  

然后，在一个方法中，您需要创建一个应用程序对象：

  Excel.Application application = new Excel.Application（）; 
  

接下来，为要读取的每个工作簿创建一个工作簿对象，如下所示：

  Excel.Workbook工作簿一; 
 Excel.Workbook工作簿二; 
 Excel.Workbook工作簿三; 
  

现在使用Application对象打开每个工作簿，并将每个工作簿加载到相应的Workbook对象中： / p>

  workbookOne = application.Workbooks.Open（@C：\workbookOneLocation.xlsx）; 
 workbookTwo = application.Workbooks.Open（@C：\workbookTwoLocation.xlsx）; 
 workbookThree = application.Workbooks.Open（@C：\workbookThreeLocation.xlsx）; 
  

现在您需要决定要提取哪些信息。一旦你做了这个确定工作簿中的哪个工作表，然后找出数字，只需看看标签和计数。在下面的示例中， Sheet2 是数字1， Sheet1 是数字2， Sheet3 是3号：

创建一个您需要的每个信息的变量都是这样（任何值类型变量都需要为空）：

  string firstPieceOfInformationINeed; 
 string [] secondPieceOfInformationINeed; 
双？ thirdPieceOfInformationINeed;假设我们需要的第一条信息是工作簿中第一个单元格A1的一个字符串一个，第二个部分是工作表上的单元格B2  -  B4两个工作簿两个，第三个是工作表上三个工作簿三个单元格C5上的一个数字。我们会这样做：
  string firstPieceOfInformationINeed; 
 string [] secondPieceOfInformationINeed; 
双？ thirdPieceOfInformationINeed; 
 
 Excel.Worksheet工作表; 
 Excel.Range范围; 
 
工作表= workbookOne.Sheets [1]; 
 range = worksheet.Cells [1，1]; 
 
 firstPieceOfInformationINeed = range.Value as string; 
 
工作表= workbookTwo.Sheets [2]; 
 range = worksheet.Range [B2，B4]; 
 
 secondPieceOfInformationINeed = range.Value as string []; 
 
工作表= workbookThree.Sheets [3]; 
 range = worksheet.Cells [3，5]; 
 
 thirdPieceOfInformationINeed = range.Value as double？ 
  
然后我们关闭工作簿，使用布尔值来指示是否要保存更改： 
  workbookOne.Close（true）; 
 workbookTwo.Close（false）; 
 workbookThree.Close（true）; 
  
现在退出应用程序：
  application.Quit（）; 
  
并释放COM对象：
  Marshal.ReleaseComObject的（应用）; 
  
现在，您已经完成了Excel，并将您需要的所有不同的信息存储为C＃变量，你可以用这些你想做的。
 
Is it possible to make an application that reads multiple excel files from a folder and extracts some information from them?
解决方案
Yes it is, and here is how using Interop. The first thing you need to do is add the Excel Interop library to your project. You can do this by creating a new Visual Studio solution, right clicking on References, selecting Add Reference and then selecting Microsoft.Office.Interop.Excel from the .NET tab.

Then you need to add a using statement for Excel, and one for InteropServices (as we are interoping with a COM object):
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
Then, inside a method, you need to create an Application object:
Excel.Application application = new Excel.Application();
Next, create a Workbook object for each workbook you want to read from, like so:
Excel.Workbook workbookOne;
Excel.Workbook workbookTwo;
Excel.Workbook workbookThree;
Now use the Application object to open each workbook, and load each one into its respective Workbook object:
workbookOne = application.Workbooks.Open(@"C:\workbookOneLocation.xlsx");
workbookTwo = application.Workbooks.Open(@"C:\workbookTwoLocation.xlsx");
workbookThree = application.Workbooks.Open(@"C:\workbookThreeLocation.xlsx");
Now you need to decide what information you want to extract. Once you have done this determine which worksheet in the workbook it is on,and then figure out the number by simply looking at the tabs and counting. In the below example, Sheet2 is number 1, Sheet1 is number 2 and Sheet3 is number 3:



Create a variable for each piece of information you need like so (any value type variables will need to be nullable):
string firstPieceOfInformationINeed;
string[] secondPieceOfInformationINeed;
double? thirdPieceOfInformationINeed;
Let's say that the first piece of information we need is a string in Cell A1 on sheet one inside workbook one, the second piece is Cells B2 - B4 on worksheet two inside workbook two, and the third piece is a number on cell C5 on worksheet three inside workbook three. We would do this:
string firstPieceOfInformationINeed;
string[] secondPieceOfInformationINeed;
double? thirdPieceOfInformationINeed;

Excel.Worksheet worksheet;
Excel.Range range;

worksheet = workbookOne.Sheets[1];
range = worksheet.Cells["1", "1"];

firstPieceOfInformationINeed = range.Value as string;

worksheet = workbookTwo.Sheets[2];
range = worksheet.Range["B2", "B4"];

secondPieceOfInformationINeed = range.Value as string[];

worksheet = workbookThree.Sheets[3];
range = worksheet.Cells["3", "5"];

thirdPieceOfInformationINeed = range.Value as double?;
Then we close the workbook, using a boolean value to indicate whether or not we want to save changes:
workbookOne.Close(true);
workbookTwo.Close(false);
workbookThree.Close(true);
Now quit the application:
application.Quit();
And release the COM object:
Marshal.ReleaseComObject(application);
Now you are done with Excel, and have all of the different pieces of information you need stored as C# variables, and you can do with these what you wish.

                        
这篇关于C＃读取多个Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！