如何在没有oledb的情况下将Excel文件数据上载到SQL Server

本文介绍了如何在没有oledb的情况下将Excel文件数据上载到SQL Server的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我一直在尝试使用ASP.NET Core MVC将数据从Excel文件导入SQL Server.但是此代码无法运行:

I have been trying to import data from an Excel file into SQL Server with ASP.NET Core MVC. But this code just doesn't run:

[HttpPost] public IActionResult Index(ICollection<IFormFile> files) { string line; using (SqlConnection con = new SqlConnection(@"Data Source=NT;Initial Catalog=StudentDB;Integrated Security=True")) { con.Open(); using (StreamReader file = new StreamReader("TestFile.xlsx")) { while ((line = file.ReadLine()) != null) { string[] fields = line.Split(','); SqlCommand cmd = new SqlCommand("INSERT INTO Persons(ContactID, FirstName, SecondName, Age) VALUES (@contactid, @firstname, @secondname, @age)", con); cmd.Parameters.AddWithValue("@id", fields[0].ToString()); cmd.Parameters.AddWithValue("@firstname", fields[1].ToString()); cmd.Parameters.AddWithValue("@secondname", fields[2].ToString()); cmd.ExecuteNonQuery(); } } } return View(); }

推荐答案

我建议使用EPPlus开源免费库，您可以通过Nuget进行安装或访问( www.nuget/packages/EPPlus/)，该文件无需安装Excel或任何其他Interop即可读取Excel文件.

I would recommend using the EPPlus open source free library which you can install through Nuget or visit(www.nuget/packages/EPPlus/) which reads Excel files without having Excel or any other Interops installed.

以下是使用Epplus更新的代码.我假设您的excel文件中没有标题行(如果您只是将var row = 1更改为var row = 2，这将意味着从第2行开始，而忽略标题)

Below is the updated code using Epplus. I am assuming you don't have a header row in your excel file(if you do just change the var row = 1 to var row = 2 which would mean to start at Row 2 and ignore the header)

using (var con = new SqlConnection(@"Data Source=NT;Initial Catalog=StudentDB;Integrated Security=True")) { con.Open(); var excelFile = new FileInfo(@"TestFile.xlsx"); using (var epPackage = new ExcelPackage(excelFile)) { var worksheet = epPackage.Workbook.Worksheets.First(); for (var row = 1; row <= worksheet.Dimension.End.Row; row++) { var rowValues = worksheet.Cells[row, 1, row, worksheet.Dimension.End.Column]; var cmd = new SqlCommand("INSERT INTO Persons(ContactID, FirstName, SecondName, Age) VALUES (@contactid, @firstname, @secondname, @age)", con); cmd.Parameters.AddWithValue("@contactid", rowValues["A1"].Value); cmd.Parameters.AddWithValue("@firstname", rowValues["B1"].Value); cmd.Parameters.AddWithValue("@secondname", rowValues["C1"].Value); cmd.Parameters.AddWithValue("@age", rowValues["D1"].Value); cmd.ExecuteNonQuery(); } } }

AddWithValue中的某些参数与您在insert语句中声明的参数不匹配，我也清理了这些参数.使用该代码，我能够读取我创建的一个测试xlsx文件以匹配您的布局并将其插入数据库中.希望对您有所帮助.

Some of the parameters in your AddWithValue didn't match the parameter you declared in your insert statement, and I cleaned those up as well. Using the code I was able to read a test xlsx file I created to match your layout and insert into the database. Hope it helps.