CODEWITHSUNDEEP
c#excel-2010
Superman
Superman

Reputation: 285

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

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?

enter image description here

I want to traverse rows to get data as follows:

  1. 081701-b-aaaa-y;
  2. 081702-c-aaaa-y;
  3. 081704-d-aaaa-y;
  4. 081703-e-ffff-k;
  5. ...

Is there any good ways to achieve it?

Upvotes: 1

Views: 431

Answers (1)

Daniel
Daniel

Reputation: 2804

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

Upvotes: 1

Related Questions