Excel数据读取器问题，列名称和工作表选择

问题描述

我正在使用将一些数据读入实体框架数据库

I am using Excel Data Reader to read some data in to an Entity Framework Database

下面的代码正在工作，但我需要进一步的改进。

The code below is working but i need some further refinements

首先，IsFirstRowAsColumnNames似乎没有按预期工作，必须使用.Read代替。

First of all IsFirstRowAsColumnNames does not seem to be working as intended and I have to use .Read instead.

我原来选择特定工作表的软糖是有计划的，任何人都可以帮助这个excelReader.Name的时候是没有意义，除非我可以专门循环或选择一个我最初使用的工作表。因此实现冲突。

The fudge i had in originally to select a particular sheet was has scuppered plans, can anyone help with this excelReader.Name at the moment is pointless unless i can specifically loop through or select a sheet, which I originally used .Read to achieve hence the conflict.

还可以参考实际的列标头名称来检索数据，而不是索引，如var name = reader [applicationname]。在SQL客户端中的ToString（）;

It would also be nice to refer to the actual column header names to retrieve the data rather than indexes such as var name = reader["applicationname"].ToString() in SQL client;

是否有更好的扩展名如果我无法实现上述，可以使用excel数据读取。

Is there perhaps a better Extension i could use to read in excel data if i can't achieve the above.

public static void DataLoadAliases(WsiContext context)
    {
        const string filePath = @"Alias Master.xlsx";

        var stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

        var excelReader = filePath.Contains(".xlsx")
                      ? ExcelReaderFactory.CreateOpenXmlReader(stream)
                      : ExcelReaderFactory.CreateBinaryReader(stream);

       excelReader.IsFirstRowAsColumnNames = true;


        excelReader.Read(); //skip first row

        while (excelReader.Read())
        {

            if (excelReader.Name == "Alias Master")
            {
                var aliasId = excelReader.GetInt16(0);
                var aliasName = excelReader.GetString(1);

                //Prevent blank lines coming in from excel;
                if (String.IsNullOrEmpty(aliasName)) continue;

                context.Aliases.Add(new ApplicationAlias
                {
                    AliasId = aliasId,
                    Name = aliasName,
                });
            }
            else
            {
                excelReader.NextResult();
            }
        }

        excelReader.Close();
        context.SaveChanges();
    }

推荐答案

OpenXML SDK：

for .XLSX file i use OpenXML SDK :http://www.microsoft.com/en-us/download/details.aspx?id=30425

对于XLS文件，我使用OleDbConnection，如下所示：

for XLS file i use a OleDbConnection as see below :

 OleDbConnection oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath+ ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1;';");
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            DataTable dt = oledbConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string workSheetName = (string)dt.Rows[0]["TABLE_NAME"];

            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM [" + workSheetName + "]";

            oleda = new OleDbDataAdapter(cmd);

            oleda.Fill(ds, "Donnees");

            oledbConn.Close();
            return ds.Tables[0];

这篇关于Excel数据读取器问题，列名称和工作表选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！