如何在行数少的单元格中使用Epplus [英] How to use Epplus with cells containing few rows

问题描述

我想使用epplus导入一些Excel文件 问题是某些单元格包含多于一行(这会导致问题

I want to import some excel file using epplus the problem is that some cells contains more than one row (and that cause a problem

我的excel看起来像这样(实际上，他们是更多测试(test2，test3....)

My excel look like this (in realite their is more tests (test2,test3....)

我只能通过这种算法获得第一列..但是获得第二列会更加复杂

I can only get the first column by this algorithm..but it will be more complicated to get the seconde column

 //this is the list than contain applications (column 2)
ICollection<Application> applications = new  List<Application>();
            int i = 0;

            for (int j = workSheet.Dimension.Start.Row;
                    j <= workSheet.Dimension.End.Row;
                    j=i+1)
            {
                 //this is the object that contain the first column
                //and also a list of the second column  (foreach domain thei `is a list of applications (column 2)`          
                Domaine domaine = new Domaine();

                i += 1;

                //add here and  not last row
                while (workSheet.Cells[i, 1].Text == "" && i < workSheet.Dimension.End.Row)
                {

                    i++;
                }

                if (i > workSheet.Dimension.End.Row)
                    break;
                domaine.NomDomaine = workSheet.Cells[i, 1].Text;
                domaines.Add(domaine);

            }

换句话说，它们是获取一个单元格中行数的一种方法，还是复制该单元格中每一行的值的一种方法

Edit : in other words is their a way to get the number of rows in one cell , OR a way to duplicate the value of each row in the cell

(例如，如果我有一个从第1行到第14行的单元格，而第5行则有值) 我如何将文本复制到所有行(这将帮助我解决问题)

(for exemple if i have a cell from row 1 to 14 and the row number 5 have value) how can i duplicate that text to all the rows (that will help me solving the problem)

推荐答案

这些被称为合并单元格.来自合并单元格的值存储在合并范围内的 first 单元格的.Value属性中.这意味着我们需要做更多的工作才能使用EPPlus从合并的单元格中读取值.

Those are known as Merged cells. Values from merged cells are stored in the .Value property of the first cell in the merged range. This means we need to do just a little bit more work in order to read the value from a merged cell using EPPlus.

EPPlus为我们提供了一些属性，可帮助我们获得正确的参考.首先，我们可以使用单元格的.Merge属性来确定它是否属于合并范围.然后，我们可以使用工作表的.MergedCells属性来找到相关范围.然后只需查找该范围内的第一个单元格并返回值即可.

EPPlus provides us with a couple of properties that help us get to the correct reference though. Firstly we can use a cell's .Merge property to find out if it is part of a merged range. Then we can use the the worksheet's .MergedCells property to find the relevant range. It's then just a matter of finding the first cell in that range and returning the value.

因此，总而言之:

1. 使用.Merge
确定我们要读取的单元格是否属于合并范围的一部分
2. 如果是，请使用工作表的.MergedCells属性获取合并范围的索引
3. 从合并范围中的第一个单元格读取值

1. Determine if the cell we need to read from is part of a merged range using .Merge
2. If so, get the index of the merged range using the worksheet's .MergedCells property
3. Read the value from the first cell in the merged range

将其放在一起，我们可以派生一些辅助方法来获取工作表对象和行/列索引以返回值:

Putting this together we can derive a little helper method to take a worksheet object and row/col indices in order to return the value:

static string GetCellValueFromPossiblyMergedCell(ExcelWorksheet wks, int row, int col)
{
    var cell = wks.Cells[row, col];
    if (cell.Merge)                                              //(1.)
    {
        var mergedId = wks.MergedCells[row, col];                //(2.)
        return wks.Cells[mergedId].First().Value.ToString();     //(3.)
    }
    else
    {
        return cell.Value.ToString();
    }
}

可行示例

如果我有这样的域类:

class ImportedRecord
{
    public string ChildName { get; set; }
    public string SubGroupName { get; set; }
    public string GroupName { get; set; }
}

我想从看起来像这样的电子表格中读取的内容:

that I wanted to read from a spreadsheet that looked like this:

然后我可以使用这种方法:

Then I could use this method:

static List<ImportedRecord> ImportRecords()
{
    var ret = new List<ImportedRecord>();
    var fInfo = new FileInfo(@"C:\temp\book1.xlsx");
    using (var excel = new ExcelPackage(fInfo))
    {
        var wks = excel.Workbook.Worksheets["Sheet1"];
        var lastRow = wks.Dimension.End.Row;

        for (int i = 2; i <= lastRow; i++)
        {
            var importedRecord = new ImportedRecord
            {
                ChildName = wks.Cells[i, 4].Value.ToString(),
                SubGroupName = GetCellValueFromPossiblyMergedCell(wks,i,3),
                GroupName = GetCellValueFromPossiblyMergedCell(wks, i, 2)
            };
            ret.Add(importedRecord);
        }
    }

    return ret;
}

static string GetCellValueFromPossiblyMergedCell(ExcelWorksheet wks, int row, int col)
{
    var cell = wks.Cells[row, col];
    if (cell.Merge)
    {
        var mergedId = wks.MergedCells[row, col];
        return wks.Cells[mergedId].First().Value.ToString();
    }
    else
    {
        return cell.Value.ToString();
    }
}

这篇关于如何在行数少的单元格中使用Epplus的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！