如何加快EPPlus上传Excel文件的速度

Question

我有一个ASP。. NET Core应用程序，带有模型，目的是允许用户上传excel文件，然后将文件保存到模型/表中。我有下面的方法

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Upload(IFormFile file)
{
string webRootPath = _hostEnvironment.WebRootPath;
var uploads = Path.Combine(webRootPath, "Upload");
var files = HttpContext.Request.Form.Files;
var extension = Path.GetExtension(files[0].FileName);
using (var filesStream = new FileStream(Path.Combine(uploads, file.FileName), FileMode.Create))
{ 
files[0].CopyTo(filesStream);
}
var list = new List<User>();
using (var stream = new MemoryStream())
{
await file.CopyToAsync(stream);
using (var package = new ExcelPackage(stream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];  
var rowcount = worksheet.Dimension.Rows;  
for (int row = 2; row <= rowcount; row++)
{
list.Add(new User
{
Name = worksheet.Cells[row, 1]?.Value?.ToString().Trim(),
Address1 = worksheet.Cells[row, 2]?.Value?.ToString().Trim(),
PostCode = worksheet.Cells[row, 3]?.Value?.ToString().Trim(),
Mobile = worksheet.Cells[row, 4]?.Value?.ToString().Trim(),
});
}
}
}
foreach (var user in list)
{
_db.User.AddAsyncy(user);
}
_db.SaveChangesAsyncy();
return View();
}

这段代码可以很好地处理用户上传的excel文件，但我遇到的问题是，当文件很大时，比如超过3mb，上传需要8分钟以上。

你知道怎么加快速度吗?谢谢。

Answer 1

你可以做两件事来提高速度。

1)不要用ExcelWorksheet类来读取excel文件，而要用一个叫做ExcelDataReader的库，它可以在一分钟内读取大约60万条记录。

示例代码

Model
class Person
{
public int id,
public string name
}
//and excel file has both columns in model ,the we can read with below code
using ExcelDataReader;
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
var fileName = "./Person.xlsx";
var timer = new Stopwatch();
timer.Start();
int counter=0;
List<Person> persons = new List<Person>();
using (var stream = System.IO.File.Open(fileName, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
while (reader.Read()) //Each row of the file
{
var person = new Person
{
id = reader.GetValue(0).ToString(),
name = reader.GetValue(1).ToString()
}
persons.Add(person)
counter++;
}
timer.Stop();
duration = timer.ElapsedMilliseconds / 1000;
//to check performace print duration and persons list
}
}

https://github.com/ExcelDataReader/ExcelDataReader

2)一旦你在列表中读取和存储数据，你可以将数据存储在DataTable类中，并使用Oracle.ManagedDataAccess.Client Nuget包而不是EFcore插入到数据库中。这种方法很快。请通过下面的链接对Oracle数据库进行此操作。

https://www.c-sharpcorner.com/article/two-ways-to-insert-bulk-data-into-oracle-database-using-c-sharp/

var db_timer = new Stopwatch();
db_timer.Start();

DataTable dt = new DataTable();
dt.Columns.Add("id");
dt.Columns.Add("name");
for (int i = 0; i < counter; i++)
{
DataRow dr = dt.NewRow();
dr["id"] = persons[i].id;
dr["name"] = persons[i].name;
dt.Rows.Add(dr);
}
using (var connection = new OracleConnection(oracleConString))
{
connection.Open();
int[] ids = new int[dt.Rows.Count];
string[] names = new string[dt.Rows.Count];

for (int j = 0; j < dt.Rows.Count; j++)
{
ids[j] = Convert.ToString(dt.Rows[j]["id"]);
names[j] = Convert.ToString(dt.Rows[j]["name"]);
}
OracleParameter id = new OracleParameter();
id.OracleDbType = OracleDbType.Int32;
id.Value = ids;
OracleParameter name = new OracleParameter();
name.OracleDbType = OracleDbType.Varchar2;
name.Value = names;
OracleCommand cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO TEST(id,name) VALUES (:1,:2)"; 
cmd.ArrayBindCount = ids.Length;
cmd.Parameters.Add(id);
cmd.Parameters.Add(name);
cmd.ExecuteNonQuery();
}

只是示例代码，您可以使用timer来检查执行所需的时间。