Skip to content

Instantly share code, notes, and snippets.

@hgirish
Created August 2, 2022 04:37
Show Gist options
  • Save hgirish/992a18df816f385d3a64946bc10337dd to your computer and use it in GitHub Desktop.
Save hgirish/992a18df816f385d3a64946bc10337dd to your computer and use it in GitHub Desktop.
Search string in OpenXml SpreadsheetDocument and return Cell Reference
private static StringValue? SearchString(string searchString, WorkbookPart workbookPart, WorksheetPart worksheetPart)
{
var sharedTable = workbookPart.SharedStringTablePart;
var sharedStringValues = new List<SharedStringItem>();
if (sharedTable != null)
{
sharedStringValues =
sharedTable.SharedStringTable.Elements<SharedStringItem>().ToList();
}
var cells = worksheetPart.Worksheet.Descendants<Cell>();
StringValue? cellRef = new();
foreach (Cell cell in cells)
{
if (sharedStringValues != null
&& sharedStringValues.Any()
&& cell.DataType != null
&& cell.DataType.Value == CellValues.SharedString)
{
if (int.TryParse(cell.CellValue?.Text, out int index))
{
var value = sharedStringValues[index].InnerText;
if (value == searchString)
{
cellRef = cell.CellReference;
break;
}
}
}
else
{
if (cell.CellValue?.Text == searchString)
{
cellRef = cell.CellReference;
break;
}
}
if (cell.CellFormula != null)
{
if (cell.CellFormula.Text == searchString)
{
cellRef = cell.CellReference;
break;
}
}
}
return cellRef;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment