Please help me to get cell in range (ex from A:1 to E:11 are all cells in rectangular). For now, my ideal is
Worksheet worksheet = GetWorksheet(document, sheetName);
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
IEnumerable<Cell> cells = sheetData.Descendants<Cell>().Where(c =>
c.CellReference >= A:1 &&
c.CellReference <= E:11 &&
);
int t = cells.Count();
But this code does not work. Thanks
It won't be that easy to compare cell's CellReference with a string. And yes, what you are currently doing is wrong. You simply cannot compare strings for Higher or Lower in such a way.
You have two options.
Option 1 :
You can take cell reference and break it down. That means separate characters and numbers and then give them values individually and compare
A1 - > A and 1 -> Give A =1 so you have 1 and 1
E11 -> E and 11 -> Give E = 5 so you have 5 and 11
So you will need to breakdown the CellReference and check the validity for your requirement.
Option 2 :
If you notice above it's simply we take a 2D matrix index (ex : 1,1 and 5,11 which are COLUMN,ROW format). You can simply use this feature in comparison. But catch is you cannot use LINQ for this, you need to iterate through rows and columns. I tried to give following example code, try it
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open("PATH", true))
{
//Get workbookpart
WorkbookPart workbookPart = myDoc.WorkbookPart;
// Extract the workbook part
var stringtable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
//then access to the worksheet part
IEnumerable<WorksheetPart> worksheetPart = workbookPart.WorksheetParts;
foreach (WorksheetPart WSP in worksheetPart)
{
//find sheet data
IEnumerable<SheetData> sheetData = WSP.Worksheet.Elements<SheetData>();
int RowCount = 0;
int CellCount = 0;
// This is A1
int RowMin = 1;
int ColMin = 1;
//This is E11
int RowMax = 11;
int ColMax = 5;
foreach (SheetData SD in sheetData)
{
foreach (Row row in SD.Elements<Row>())
{
RowCount++; // We are in a new row
// For each cell we need to identify type
foreach (Cell cell in row.Elements<Cell>())
{
// We are in a new Cell
CellCount++;
if ((RowCount >= RowMin && CellCount >= ColMin) && (RowCount <= RowMax && CellCount <= ColMax))
{
if (cell.DataType == null && cell.CellValue != null)
{
// Check for pure numbers
Console.WriteLine(cell.CellValue.Text);
}
else if (cell.DataType.Value == CellValues.Boolean)
{
// Booleans
Console.WriteLine(cell.CellValue.Text);
}
else if (cell.CellValue != null)
{
// A shared string
if (stringtable != null)
{
// Cell value holds the shared string location
Console.WriteLine(stringtable.SharedStringTable.ElementAt(int.Parse(cell.CellValue.Text)).InnerText);
}
}
else
{
Console.WriteLine("A broken book");
}
}
}
// Reset Cell count
CellCount = 0;
}
}
}
}
This actually work. I tested.
