在C# (范例)中,读取Excel文件

・5 分钟阅读

.NET 4 允许,读取和操作Microsoft Excel文件,对于安装Excel了(如果未安装Excel,请参见NPOI )的计算机。

首先添加对Microsoft Excel XX.X对象库的引用,在引用管理器的com 选项卡中,我已经给出了Excel的使用别名

using Excel = Microsoft.Office.Interop.Excel; //Microsoft Excel 14 object in references-> COM tab

接下来,你需要为访问的每个com对象创建引用,每次引用都必须在完成时有效地退出应用程序。


//Create COM Objects. Create a COM object for everything that is referenced
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"sandbox_test.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;

然后,你可以从表中读取,记住Excel中的索引不是0,这只是读取单元格,并且打印出来,就像它们在文件中一样。


//iterate over the rows and columns and print to the console as it appears in the file
//excel is not zero based!!
for (int i = 1; i <= rowCount; i++)
{
 for (int j = 1; j <= colCount; j++)
 {
 //new line
 if (j == 1)
 Console.Write("rn");

 //write the value to the console
 if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
 Console.Write(xlRange.Cells[i, j].Value2.ToString() +"t");

 //add useful things here! 
 }
}

最后,必须释放对非托管内存的引用,如果没有正确完成,那么将有一些延迟进程将文件访问写入Excel工作簿。


//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();

//rule of thumb for releasing com objects:
// never use two dots, all COM objects must be referenced and released individually
// ex: [somthing].[something].[something] is bad

//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);

//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);

//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);

进一步阅读:

StackOverflow

互操作封送处理

Excel和C#

完整代码:


using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices; 
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel; //microsoft Excel 14 object in references-> COM tab

namespace Sandbox
{
 public class Read_From_Excel
 {
 public static void getExcelFile()
 {

 //Create COM Objects. Create a COM object for everything that is referenced
 Excel.Application xlApp = new Excel.Application();
 Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:UsersE56626DesktopTeddyVS2012Sandboxsandbox_test - Copy - Copy.xlsx");
 Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
 Excel.Range xlRange = xlWorksheet.UsedRange;

 int rowCount = xlRange.Rows.Count;
 int colCount = xlRange.Columns.Count;

 //iterate over the rows and columns and print to the console as it appears in the file
 //excel is not zero based!!
 for (int i = 1; i <= rowCount; i++)
 {
 for (int j = 1; j <= colCount; j++)
 {
 //new line
 if (j == 1)
 Console.Write("rn");

 //write the value to the console
 if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
 Console.Write(xlRange.Cells[i, j].Value2.ToString() +"t");
 }
 }

 //cleanup
 GC.Collect();
 GC.WaitForPendingFinalizers();

 //rule of thumb for releasing com objects:
 // never use two dots, all COM objects must be referenced and released individually
 // ex: [somthing].[something].[something] is bad

 //release com objects to fully kill excel process from running in the background
 Marshal.ReleaseComObject(xlRange);
 Marshal.ReleaseComObject(xlWorksheet);

 //close and release
 xlWorkbook.Close();
 Marshal.ReleaseComObject(xlWorkbook);

 //quit and release
 xlApp.Quit();
 Marshal.ReleaseComObject(xlApp);
 }
 }
} 
Jiaoyang75 profile image