I want to traverse Excel cells to get data with C#

Question

I want to traverse Excel cells to get data, but the cells is not regular. For example: the red cell which is merged by multiple rows. how to traverse all rows?

I want to traverse rows to get data as follows:

081701-b-aaaa-y;
081702-c-aaaa-y;
081704-d-aaaa-y;
081703-e-ffff-k;
...

Is there any good ways to achieve it?

To parse excel, there is already a good implementation within .NET framework. I haven't tried exactly about parsing the merged cells, but you can play around with it and see. Its in another post: http://stackoverflow.com/questions/5282999/reading-csv-file-and-storing-values-into-an-array/33796861#33796861. More details about the parser is given here: http://codeskaters.blogspot.ae/2015/11/c-easiest-csv-parser-built-in-net.html — Habeeb, Dec 12 '16 at 05:39
@Habeeb, thank you very much! I'm sorry for replying to you so late! I can not open the link "codeskaters.blogspot.ae/2015/11/…", could you help me? — Superman, Dec 17 '16 at 09:28

score 1 · Accepted Answer · edited May 23 '17 at 12:25

1

I would recommend EPPlus: http://epplus.codeplex.com/

var package = new ExcelPackage(new FileInfo("sample.xlsx"));

ExcelWorksheet workSheet = package.Workbook.Worksheets[1];

for (int i = workSheet.Dimension.Start.Column;
        i <= workSheet.Dimension.End.Column;
        i++)
{
    for (int j = workSheet.Dimension.Start.Row;
            j <= workSheet.Dimension.End.Row;
            j++)
    {
        object cellValue = GetCellValueFromPossiblyMergedCell(workSheet,i, j);
    }
}

In the case of merged cells, I believe you refer to a merged cell by its top left cell reference. The following helper method will get you the value of a merged cell.

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();
        }
    }

Helper method sourced from: How to use Epplus with cells containing few rows

edited May 23 '17 at 12:25

Community

1
1

answered Dec 12 '16 at 03:52

Daniel

2,744
1
31
41

Thanks for your reply! I'm sorry for replying to you so late! The way can not resolve merged cells. For example: get the second row data is : 081702-c-""-y; workSheet.Cells[2, 3] is "". – Superman Dec 18 '16 at 08:20
@Superman I've updated the example code to show how to pull the value out of a merged range. – Daniel Dec 18 '16 at 23:48
Great！Thank you very much! – Superman Dec 20 '16 at 02:49
Hi Daniel, I have met a problem, Could you help me? Thank you very much! sheet3.Cells["A1:B5"].Merge = true; var mergedId = sheet3.MergedCells[1, 1]; sheet3.Cells[mergedId].First().Value = "123"; // error: System.InvalidOperationException : Sequence does not contain any elements The link: https://stackoverflow.com/questions/42408861/set-cells-merged-and-set-its-value-but-it-not-work – Superman Feb 24 '17 at 06:12

I want to traverse Excel cells to get data with C#

1 Answers1