public static Cell FindCell(string reference, WorksheetPart worksheetPart) { Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); string columnName = SpreadsheetReader.ColumnFromReference(reference); uint rowIndex = SpreadsheetReader.RowFromReference(reference); string cellReference = (columnName + rowIndex.ToString()); int columnIndex = SpreadsheetReader.GetColumnIndex(columnName); Row row = null; var match = sheetData.Elements <Row>().Where(r => r.RowIndex.Value == rowIndex); if (match.Count() != 0) { row = match.First(); IEnumerable <Cell> cells = row.Elements <Cell>().Where(c => c.CellReference.Value == cellReference); if ((cells.Count() > 0)) { return(cells.First()); } else { return(null); } } else { return(null); } }
public static List <Cell> FindCells(string Range, WorksheetPart worksheetPart) { Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); Range = Range.Replace("$", ""); string FirstReference = Range.Split(':')[0], LastReference = Range.Split(':')[1]; uint FirstrowIndex = SpreadsheetReader.RowFromReference(FirstReference); uint LastrowIndex = SpreadsheetReader.RowFromReference(LastReference); int FirstcolumnIndex = SpreadsheetReader.GetColumnIndex(SpreadsheetReader.ColumnFromReference(FirstReference)); int LastcolumnIndex = SpreadsheetReader.GetColumnIndex(SpreadsheetReader.ColumnFromReference(LastReference)); IEnumerable <Row> match = sheetData.Elements <Row>().Where(r => r.RowIndex.Value >= FirstrowIndex && r.RowIndex.Value <= LastrowIndex); List <Cell> listCells = new List <Cell>(); foreach (Row row in match) { foreach (Cell cell in row) { if (SpreadsheetReader.GetColumnIndex(SpreadsheetReader.ColumnFromReference(cell.CellReference.Value)) >= FirstcolumnIndex && SpreadsheetReader.GetColumnIndex(SpreadsheetReader.ColumnFromReference(cell.CellReference.Value)) <= LastcolumnIndex) { if (cell.CellValue != null) { double d; if (cell.DataType != null) { if (cell.DataType.Value == CellValues.Number || cell.DataType.Value == CellValues.SharedString) { listCells.Add(cell); } if (cell.DataType.Value == CellValues.String) { if (double.TryParse(cell.CellValue.Text, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.CreateSpecificCulture("en-US"), out d)) { listCells.Add(cell); } } } else if (double.TryParse(cell.CellValue.Text, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.CreateSpecificCulture("en-US"), out d)) { listCells.Add(cell); } } else if (cell.CellFormula != null) { listCells.Add(cell); } } } } return(listCells); }