My main goal is to parse emails from a spreadsheet. What I need is to detect if a cell is containing anything other than an Email (wrong format, boolean, number...) to add this cell to an array (assume invalids)
What I did:
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
Cell cell = new Cell();
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
while (reader.Read())
{
if (reader.ElementType == typeof(CellValue))
{
cell = worksheetPart.Worksheet.Descendants<Cell>().Where(c => c.CellReference == String.Format("A{0}", reader.GetText())).FirstOrDefault();
if (cell != null && cell.InnerText.Length > 0)
{
var value = cell.InnerText;
if (cell.DataType != null)
{
var stringTable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (cell.DataType.Value == CellValues.SharedString)
{
value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
values.Add(value);
}
else
{
// add the invalid to the array
}
}
}
}
}
spreadsheetDocument.Close();
}
Forget about email validation, my problem is that I want to extract non-string values. I have tried lots of things but the code is not reaching the else where it suppose to extract the numbers while there is booleans and numbers in the spreadsheet. Where is the problem? How to extract those non-string values?
