i am trying to read the data from excel sheet using open xml sdk,I have column structure in excel sheet like as in below image
I need to get the values for the cell station and WMO# when the Country is USA and i do have data for country USA in the excel sheet but could not be able to figure it out how to get the data.
I am using below code to get the data
string filePath = @"C:\data.xlsx";
using FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
using SpreadsheetDocument doc = SpreadsheetDocument.Open(fs, false);
WorkbookPart workbookPart = doc.WorkbookPart;
SharedStringTablePart sstpart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
SharedStringTable sst = sstpart.SharedStringTable;
Sheet firstSheet = workbookPart.Workbook.Descendants<Sheet>().First();
Worksheet sheet = ((WorksheetPart)workbookPart.GetPartById(firstSheet.Id)).Worksheet;
var cells = sheet.Descendants<Cell>();
var rows = sheet.Descendants<Row>();
foreach (Row row in rows.Skip(5))
{
int cellCount = 0;
foreach (Cell c in row.Elements<Cell>())
{
string stringValue = "";
if ((c.DataType != null) && (c.DataType == CellValues.SharedString))
{
int ssid = int.Parse(c.CellValue.Text);
stringValue = sst.ChildElements[ssid].InnerText;
}
else if (c.CellValue != null)
{
stringValue = c.CellValue.Text;
}
cellCount++;
Console.WriteLine("Cell Number:"+ cellCount + "CellValue:" + stringValue);
}
}
if country is USA only i need to grab the remaining cell data in a row,I am using open XML library for this.
could any one please let me know how can i achieve this?
Thanks in advance.
