2

I am trying to read some .xslx files with the open xml sdk, but I'm really struggeling finding any good examples.

What I want to do is to read the entire XSLX file and loop through all of the rows and extract the cellvalue/celltext from the columns i specify.

Like the following:

GetCellText(rowId, ColumnLetter)

Is this possible?

user1828871
  • 181
  • 1
  • 2
  • 10

2 Answers2

3

Helpers:

private static string GetColumnName(string cellReference)
{
    if (ColumnNameRegex.IsMatch(cellReference))
        return ColumnNameRegex.Match(cellReference).Value;

    throw new ArgumentOutOfRangeException(cellReference);
}

private static readonly Regex ColumnNameRegex = new Regex("[A-Za-z]+");

Code:

using (var document = SpreadsheetDocument.Open(stream, true))
        {
            var sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();

            foreach (Sheet sheet in sheets)
            {

                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
                Worksheet worksheet = worksheetPart.Worksheet;
                var rows = worksheet.GetFirstChild<SheetData>().Elements<Row>();
                foreach (var row in rows)
                {
                    var cells = row.Elements<Cell>();
                    foreach (var cell in cells)
                    {
                        if(GetColumnName(cell.CellReference) == "A")
                        {
                            var str = cell.CellValue.Text;
                            // do whatewer you want
                        }
                    }
                }
            }

        }
Encarmine
  • 453
  • 3
  • 13
  • If I understand this correctly this returns the first ten rows of the worksheet, but I want to loop through the whole sheet, so how can I get the length? – user1828871 Nov 16 '12 at 09:20
  • Edited. What I want to do is to read the entire XSLX file and loop through all of the rows and extract the cellvalue/celltext from the columns i specify. – user1828871 Nov 16 '12 at 09:28
0

Your question is similar to this one

1) Open xml excel read cell value

You can get the row by ID and look-up the value by column name.

Hope that helps

Community
  • 1
  • 1
Akanksha Gaur
  • 2,636
  • 3
  • 26
  • 50