如何读取XLS和使用C＃XLSX文件 [英] How to read the xls and xlsx files using c#

问题描述

如何阅读XLS和XLSX使用C＃的文件，OpenXML的格式不使用OLEDB连接即可。我找了Open XML格式的过程。

How to read the xls and xlsx files using c# with OpenXML format Without using the OLEDB connection. I am looking for Open XML format procedure.

下面是我使用的OLEDB preocedure的代码。但是我正在寻找的OpenXML格式。

Below is the code in which I used the OLEDB preocedure. But I am looking for OpenXML format.

public static DataTable ConvretExcelToDataTable(string FilePath)
{
    string strConn = string.Empty;

     if (FilePath.Trim().EndsWith(".xlsx"))
     {
         strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", FilePath);
     }
     else if (FilePath.Trim().EndsWith(".xls"))
     {
         strConn = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", FilePath);
     }

    OleDbConnection conn = null;
    OleDbCommand cmd = null;
    OleDbDataAdapter da = null;
    DataTable dt = new DataTable();
    try
    {
        conn = new OleDbConnection(strConn);
        conn.Open();
        cmd = new OleDbCommand(@"SELECT * FROM [Sheet1$]", conn);
        cmd.CommandType = CommandType.Text;
        da = new OleDbDataAdapter(cmd);
        da.Fill(dt);
    }
    catch (Exception exc)
    {
        Console.WriteLine(exc.ToString());
        Console.ReadLine();
    }
    finally
    {
        if (conn.State == ConnectionState.Open)
            conn.Close();
        conn.Dispose();
        cmd.Dispose();
        da.Dispose();
    }
    return dt;
}

要求，是落实的OpenXML格式。谢谢

推荐答案

您会希望OpenXML的SDK为XLSX：

You'll want the OpenXml SDK for the xlsx:

http://www.microsoft.com/en- GB /下载/ details.aspx？ID = 30425

但对于XLS，你将无法使用这个XLS格式不化。在XML

But for the XLS, you won't be able to use this the XLS format is not based on xml.

我使用NPOI库访问旧文件：

I use the NPOI library for accessing older files:

的 http://npoi.codeplex.com/

NPOI库还支持XLSX ，所以这会给你访问他们的一致方法。缺点是你必须手动通过张/行/列循环，并建立数据集，如果你有大量的工作簿，这将可能影响性能。如果你想使用查询来访问数据，OLEDB是我发现的唯一方法。

The NPOI library also supports xlsx, so this would give you a consistent way of accessing them. Downside is you'll have to loop through sheets/rows/columns manually, and build up the dataset which will probably affect performance if you have large workbooks. If you want to use queries to access the data, OLEDB is the only method I've found.

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