从Excel文件中提取数据并存储在SQL Server数据库中

I am looking for advice on the best way to parse a Microsoft Excel file and update/store the data into a given SQL Server database. I using ASP.NET MVC so I plan on having a page/view take in an Excel spreadsheet and using that user given file I will need to use C# to parse the data from the columns and update the database based on matches with the spreadsheet column that contains the key column of the database table. The spreadsheet will always be in the same format so I will only need to handle on format. It seems like this could be a pretty common thing I am just looking for the best way to approach this before getting started. I am using Entity Framework in my current application but I don't have to use it.

我正在寻找有关解析Microsoft Excel文件和将数据更新/存储到给定SQL Server数据库的最佳方法的建议。我使用ASP.NET MVC所以我计划在Excel电子表格中使用该页面/视图,并使用该用户给定的文件,我将需要使用C#来解析列中的数据并根据与电子表格列的匹配来更新数据库它包含数据库表的键列。电子表格将始终采用相同的格式,因此我只需要处理格式。看起来这可能是一个非常普遍的事情,我只是在开始之前寻找解决这个问题的最佳方法。我在我当前的应用程序中使用Entity Framework,但我不必使用它。

I found this solution which seems like it could be a good option:

我发现这个解决方案似乎是个不错的选择:

public IEnumerable<MyEntity> ReadEntitiesFromFile( IExcelDataReader reader, string filePath )
{
   var myEntities = new List<MyEntity>();
   var stream = File.Open( filePath, FileMode.Open, FileAccess.Read );

   using ( var reader = ExcelReaderFactory.CreateOpenXmlReader( stream ) )
   {
     while ( reader.Read() )
     {
        var myEntity = new MyEntity():
        myEntity.MyProperty1 = reader.GetString(1);
        myEntity.MyProperty2 = reader.GetInt32(2);

        myEntites.Add(myEntity);
      }
   }

   return myEntities;
}

Here is an example of a what a file might look like (Clock# is the key)

这是一个文件可能是什么样的例子(时钟#是关键)

So given a file in this format I want to match the user to the data table record using the clock # and update the record with each of the cells information. Each of the columns in the spreadsheet have a relatable column in the data table. All help is much appreciated.

因此,给定一个这种格式的文件,我希望使用时钟#将用户与数据表记录相匹配,并用每个单元格信息更新记录。电子表格中的每个列在数据表中都有一个可关联的列。非常感谢所有帮助。

2 个解决方案

#1

You can use the classes in the namespace Microsoft.Office.Interop.Excel, which abstracts all the solution you found. Instead of me rewriting it, you can check out this article: http://www.codeproject.com/Tips/696864/Working-with-Excel-Using-Csharp.

您可以使用命名空间Microsoft.Office.Interop.Excel中的类,它抽象出您找到的所有解决方案。您可以查看以下文章而不是我重写它:http://www.codeproject.com/Tips/696864/Working-with-Excel-Using-Csharp。

Better yet, why not bypass the middle man? You can use an existing ETL tool, such as Pentaho, or Talend, or something to go straight from Excel to your database. These types of tools often offer a lot of customization, and are fairly straightforward to use. I've used Pentaho quite a lot for literally what you're describing, and it saved me the head ache of writing the code myself. Unless you want to/need to write it yourself, I think the latter is the best approach.

更好的是,为什么不绕过中间人呢?您可以使用现有的ETL工具(如Pentaho或Talend)或其他东西直接从Excel到数据库。这些类型的工具通常提供大量自定义,并且使用起来相当简单。我已经将Pentaho用于你所描述的字面上了很多,它让我免于自己编写代码的头痛。除非您想/需要自己编写,否则我认为后者是最好的方法。

#2

Try This public string GetDataTableOfExcel(string file_path) {

试试这个公共字符串GetDataTableOfExcel(string file_path){

            using (OleDbConnection conn = new OleDbConnection())
            {
                DataTable dt = new DataTable();
                string Import_FileName = Server.MapPath(file_path);
                //Import_FileName = System.IO.Path.GetDirectoryName(file_path);
                string fileExtension = Path.GetExtension(Import_FileName);
                if (fileExtension == ".xlsx")
                    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                using (OleDbCommand comm = new OleDbCommand())
                {
                    comm.CommandText = "Select * from [Sheet1$]";
                    comm.Connection = conn;
                    using (OleDbDataAdapter da = new OleDbDataAdapter())
                    {
                        da.SelectCommand = comm;
                        da.Fill(dt);
                                                    }
                }
            }
        }

Now Your Data in DataTable. You can create insert query from datatable's data.

现在您在DataTable中的数据。您可以从数据表的数据创建插入查询。

file_path is excel file's full path with directory name.

file_path是excel文件的完整路径,包含目录名称。