如何使用c# [duplicate]从excel文件中读取数据

This question already has an answer here:

这个问题已经有了答案:

Reading Excel files from C# 32 answers
从c# 32的答案中读取Excel文件

My application needs to read data from an excel file. I am using .Net and c# for development. I cannot install MS office in the system. Because of that the my application fails to read excel file and throws an error while loading the dll for excel.

我的应用程序需要从excel文件中读取数据。我正在使用。net和c#进行开发。我无法在系统中安装MS office。因为我的应用程序无法读取excel文件，并在加载excel的dll时抛出错误。

How can i access excel file in my application in a system where ms office is not installed?

如何在没有安装ms office的系统中访问应用程序中的excel文件?

5 个解决方案

#1

There is the option to use OleDB and use the Excel sheets like datatables in a database...

可以选择使用OleDB并使用Excel表，比如数据库中的datatable…

Just an example.....

只是一个例子.....

string con =
  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\test.xls;" + 
  @"Extended Properties='Excel 8.0;HDR=Yes;'";    
using(OleDbConnection connection = new OleDbConnection(con))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection); 
    using(OleDbDataReader dr = command.ExecuteReader())
    {
         while(dr.Read())
         {
             var row1Col0 = dr[0];
             Console.WriteLine(row1Col0);
         }
    }
}

This example use the Microsoft.Jet.OleDb.4.0 provider to open and read the Excel file. However, if the file is of type xlsx (from Excel 2007 and later), then you need to download the Microsoft Access Database Engine components and install it on the target machine.

本例使用Microsoft.Jet.OleDb.4.0提供程序打开和读取Excel文件。但是，如果文件是xlsx类型(来自Excel 2007和稍后)，那么您需要下载Microsoft Access数据库引擎组件并将其安装到目标机器上。

The provider is called Microsoft.ACE.OLEDB.12.0;. Pay attention to the fact that there are two versions of this component, one for 32bit and one for 64bit. Choose the appropriate one for the bitness of your application and what Office version is installed (if any). There are a lot of quirks to have that driver correctly working for your application. See this question for example.

提供程序称为microsoft.ac . oledb .12.0;注意这个组件有两个版本，一个为32位，一个为64位。选择适合您的应用程序的位以及安装了什么Office版本(如果有的话)。让这个驱动程序正确地为您的应用程序工作有很多奇怪的地方。看这个问题。

Of course you don't need Office installed on the target machine.

当然，您不需要在目标机器上安装Office。

While this approach has some merits, I think you should pay particular attention to the link signaled by a comment in your question Reading excel files from C#. There are some problems regarding the correct interpretation of the data types and when the length of data, present in a single excel cell, is longer than 255 characters

虽然这种方法有一些优点，但我认为您应该特别注意在您的问题中，从c#中读取excel文件时的一个注释所发出的链接。对于正确解释数据类型以及在单个excel计算单元中显示的数据长度超过255个字符时，存在一些问题

#2

CSharpJExcel for reading Excel 97-2003 files (xls): http://sourceforge.net/projects/jexcelapi/

用于读取Excel 97-2003文件的CSharpJExcel: http://sourceforge.net/projects/jexcelapi/

and ExcelPackage for reading Excel 2007/2010 files (Office Open XML format, xlsx): http://excelpackage.codeplex.com/

和ExcelPackage for reading Excel 2007/2010文件(Office Open XML格式，xlsx): http://excelpackage.codeplex.com/

and ExcelDataReader, that seems to have the ability to handle both formats: https://github.com/ExcelDataReader/ExcelDataReader

而ExcelDataReader，它似乎能够处理两种格式:https://github.com/exceldatareader/exceldatareareareareareerader

Good luck!

好运！

#3

I don't have a machine available to test this but it should work. First you will probably need to install the either the 2007 Office System Driver: Data Connectivity Components or the Microsoft Access Database Engine 2010 Redistributable. Then try the following code, note you will need to change the name of the Sheet in the Select statement below to match sheetname in your excel file:

我没有机器可以测试这个，但是它应该可以工作。首先，您可能需要安装2007年的Office系统驱动程序:数据连接组件或Microsoft Access数据库引擎2010 Redistributable。然后尝试以下代码，注意您将需要更改Select语句中的表名，以匹配excel文件中的sheetname:

using System.Data;
using System.Data.OleDb;

namespace Data_Migration_Process_Creator
{
    class Class1
    {
        private DataTable GetDataTable(string sql, string connectionString)
        {
            DataTable dt = null;

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    using (OleDbDataReader rdr = cmd.ExecuteReader())
                    {
                        dt.Load(rdr);
                        return dt;
                    }
                }
            }
        }

        private void GetExcel()
        {
            string fullPathToExcel = "<Path to Excel file>"; //ie C:\Temp\YourExcel.xls
            string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=yes'", fullPathToExcel);
            DataTable dt = GetDataTable("SELECT * from [SheetName$]", connString);

            foreach (DataRow dr in dt.Rows)
            {
                //Do what you need to do with your data here
            }
        }
    }
}

Note: I don't have an environment to test this in (One with Office installed) so I can't say if it will work in your environment or not but I don't see why it shouldn't work.

注意:我没有一个环境来测试它(一个安装了Office的环境)，所以我不能说它是否在您的环境中工作，但是我不明白为什么它不应该工作。

#4

Save the Excel file to CSV, and read the resulting file with C# using a CSV reader library like FileHelpers.

将Excel文件保存为CSV，并使用CSV reader库(如filehelper)使用c#读取结果文件。

#5

Convert the excel file to .csv file (comma separated value file) and now you can easily be able to read it.

将excel文件转换为.csv文件(逗号分隔值文件)，现在可以轻松读取它。

#1