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