使用ASP.NET Core MVC上传CSV数据到SQL数据库

Question

我正在尝试将数据从.csv文件插入我的数据库中，但是每当我上传数据时，记录都是空的。

目前

[HttpPost]
public async Task<IActionResult> ImportFromExcel(IFormFile formFile)
{
    var data = new MemoryStream();
    await formFile.CopyToAsync(data);

    data.Position = 0;
    TextReader reader = new StreamReader(data);

    var csvReader = new CsvReader(reader, new CsvConfiguration(System.Globalization.CultureInfo.CurrentCulture)
                        {
                            HasHeaderRecord = true,
                            HeaderValidated = null,
                            MissingFieldFound = null
                        });

    var Name = csvReader.GetField(0).ToString();
    var dep = "cccccccccc";
    var pos = "bbbbbbbbbbb";

    await dcx.Participants.AddAsync(new Participant
                                        {
                                            Name = Name,
                                            Position = pos,
                                            Department = dep,
                                        });
    dcx.SaveChanges();
    
    return ViewComponent("ViewParticipants");
}

到

​

Answer 1

只要您的CSV的标题与数据库中的列的名称匹配，您就应该能够执行类似的操作。如果名称不匹配，则可以使用.name（“ csvcolumnname”） in 参与者在CSV文件中添加列的名称。示例：map（r =＆gt; r.description）.name（“ mycsvdescription”）;。

[HttpPost]
public async Task<IActionResult> ImportFromExcel(IFormFile formFile)
{
    var data = new MemoryStream();
    await formFile.CopyToAsync(data);

    data.Position = 0;

    var conf = new CsvConfiguration(CultureInfo.InvariantCulture)
    {
         HasHeaderRecord = true,
         HeaderValidated = null,
         MissingFieldFound = null,
         BadDataFound = context =>
         {
              bad.Add(context.RawRecord);
         }
    };
    using (TextReader reader = new StreamReader(data))
    using (var csvReader = new CsvReader(reader, config)) {

        csvReader.Context.RegisterClassMap<ParticipantMap>();
        var records = csvReader.GetRecords<Participant>().ToList();

        var dep = "cccccccccc";
        var pos = "bbbbbbbbbbb";

        records.ForEach(r =>
        {
            r.Department = dep;
            r.Position = pos;
        });

        await dcx.Participants.AddRangeAsync(records);
        dcx.SaveChanges();
    }
    
    return ViewComponent("ViewParticipants");
}

public class ParticipantMap : ClassMap<Participant>
{
    public ParticipantMap()
    {
        AutoMap(CultureInfo.InvariantCulture);
        Map(r => r.Department).Ignore();
        Map(r => r.Position).Ignore();
    }
}

Answer 2

我认为，您应该调用csvreader.read（）首先读取文件行。

您可以参考以下测试代码，它可以正常工作。

[HttpPost]
public async Task<IActionResult> ImportFromExcel(IFormFile formFile)
{
     var data = new MemoryStream();
     await formFile.CopyToAsync(data);

     data.Position = 0;
     using (var reader = new StreamReader(data))
     {
           var bad = new List<string>();
           var conf = new CsvConfiguration(CultureInfo.InvariantCulture)
           {
                HasHeaderRecord = true,
                HeaderValidated = null,
                MissingFieldFound = null,
                BadDataFound = context =>
                {
                     bad.Add(context.RawRecord);
                }
           };
           using (var csvReader = new CsvReader(reader, conf))
           {
                while (csvReader.Read())
                {
                     var Name = csvReader.GetField(0).ToString();
                     var pos = csvReader.GetField(1).ToString();
                     var dep = csvReader.GetField(2).ToString();
                        
                     await dcx.Participants.AddAsync(new Participant
                     {
                          Name = Name,
                          Position = pos,
                          Department = dep,
                     });
                     dcx.SaveChanges();
                }
           }
     }
     return ViewComponent("ViewParticipants");
}

测试结果：