如何使用EPPlus在C＃中逐行读取Excel文件 [英] How to read excel files row by row in c# using EPPlus

问题描述

在我的MVC控制器中，我试图逐行读取Excel。但是有一个问题。

In my MVC controller I am trying to read an excel line by line. But there is a catch.

我希望能够将其映射到列表中。模型类包含29个字段。

I want to be able to map it to a list . And the model class contains 29 fields.

public class GroupMembershipUploadInput
{

    public string chpt_cd {get;set;}
    public string cnst_mstr_id {get;set;}
    public string cnst_prefix_nm {get;set;}
    public string cnst_first_nm {get;set;}
    public string cnst_middle_nm {get;set;}
    public string cnst_last_nm {get;set;}
    public string cnst_addr1_street1 {get;set;}
    public string cnst_addr1_street2 {get;set;}
    public string cnst_addr1_city {get;set;}
    public string cnst_addr1_state {get;set;}
    public string cnst_addr1_zip {get;set;}
    public string cnst_addr2_street1 {get;set;}
    public string cnst_addr2_street2 {get;set;}
    public string cnst_addr2_city {get;set;}
    public string cnst_addr2_state {get;set;}
    public string cnst_addr2_zip {get;set;}
    public string cnst_phn1_num {get;set;}
    public string cnst_phn2_num {get;set;}
    public string cnst_phn3_num {get;set;}
    public string cnst_email1_addr {get;set;}
    public string cnst_email2_addr {get;set;}
    public string job_title {get;set;}
    public string company_nm {get;set;}
    public string grp_cd {get;set;}
    public string grp_nm {get;set;}
    public string rm_ind {get;set;}
    public string notes {get;set;}
    public string stuart_cnst_grp_key {get;set;}
    public string created_by {get;set;}
    public string created_dt {get;set;}

}

public class ListGroupMembershipUploadInput
{
    public List<GroupMembershipUploadInput> GroupMembershipUploadInputList { get; set; }
}

收获是

我应该根据列标题将excel中的字段映射到此字段。像这里一样，excel中的列标题可以是

I should map the fields in the excel to this fields according to the column headers. Like here the column headers in excel can be

因此，无需硬编码，我应该能够将列标题 First Name下的值放入模型字段 cnst_first_nm。
等。

So, without hard coding I should be able to put the value under column header "First Name" to the model field "cnst_first_nm". and so on.

在excel中，此列也不必以此顺序排列。因此，它需要具有灵活性。

This column need not be in this order in excel also. So it is needed to be flexible.

我尝试过：

                ExcelPackage ep = new ExcelPackage(new FileInfo(savedFilePath));
                ExcelWorksheet ws = ep.Workbook.Worksheets["Sheet1"];

                ListGroupMembershipUploadInput gl = new ListGroupMembershipUploadInput();

                for (int i = 3; i <= ws.Dimension.End.Row; i++)
                {
                    GroupMembershipUploadInput gm = new GroupMembershipUploadInput();

                    for (int j = ws.Dimension.Start.Column; j <= ws.Dimension.End.Column; j++)
                    {
                            //gm.cnst_first_nm = ws.Cells[i, j].Value.ToString();
                    }
                    gl.Add(gm);

                }

我无法进行排序。我知道我在某处缺少什么。该怎么办？

I am not able to sort it out . I know somewhere I am missing something. What can be done ?

推荐答案

因此，无需进行硬编码，我就可以将值放在列标题 First名称添加到模型字段 cnst_first_nm。

So, without hard coding I should be able to put the value under column header "First Name" to the model field "cnst_first_nm". and so on.

我不确定您的意思，但是您将需要一些方法来告诉您的代码哪个excel标头值映射到哪个属性。

I'm not QUITE sure what you mean here but you will need some way to tell your code which excel header value maps to which property.

如果是我，我将使用从标头值到 Action< strng，GroupMembershipUploadInput> 在给定的 GroupMembershipUploadInput 对象上设置映射属性。

If it were me I would do this with a map from the header value to a Action<strng, GroupMembershipUploadInput> that sets the mapped property on a given GroupMembershipUploadInput object.

因此，我将从类似这样的内容开始，以映射所有设置操作：

So I'd start with something like this to map all the 'setter' actions:

Dictionary<string, Action<string, GroupMembershipUploadInput>> MapFieldSetters { get; set; } = 
    new Dictionary<string, Action<string, GroupMembershipUploadInput>>()
    {
        { "First Name", (s,g) => g.cnst_first_nm = s },
        { "Last Name", (s,g) => g.cnst_Last_nm = s },
        //... <snip>
    };

然后，我们需要在要导入的文件中找到每个列标题的索引。假设标题位于第一行，并且假设我们不必担心跳过任何字段，我们可以执行以下操作：

We then need to find the index of each column header in the file we are importing. Assuming the headers are on the first row, and assuming we don't need to worry about skipping any fields, we can just do something like:

var columnHeadersInOrder = new List<string>();

for (int col = 1; col <= ws.Dimension.End.Column; col++)
{
    var headerValue = ws.Cells[1, col].Value.ToString();
    columnHeadersInOrder.Add(headerValue);
}

这为我们提供了列表。现在，我们可以通过以下方法完成您的功能：首先从我们的有序列表中检索列标题，然后检索&调用映射的 Action< string，GroupMembershipUploadInput> 来设置对象的值：

This gives us a List of all the column headers in the order they appear in the excel worksheet. We can now finish off your function by first retrieving the column header from our ordered list then retrieving & calling the Mapped Action<string,GroupMembershipUploadInput> to set the value on our object:

for (int i = 3; i <= ws.Dimension.End.Row; i++)
{
    GroupMembershipUploadInput gm = new GroupMembershipUploadInput();

    for (int j = ws.Dimension.Start.Column; j <= ws.Dimension.End.Column; j++)
    {
        var columnHeader = columnHeadersInOrder[j - ws.Dimension.Start.Column];
        var cellValue = ws.Cells[i, j].Value.ToString();
        MapFieldSetters[columnHeader](cellValue, gm);
    }
    gl.Add(gm);

}

这篇关于如何使用EPPlus在C＃中逐行读取Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！