How can I use an SQL like statement to filter a row set when reading from a spread sheet

Question

rather than reading every row on the sheet and comparing a cell value for interest .... I would prefer to use a select statement that returns a filtered row set ... is this possible?

e.g. if column 4 contains a value and I only want row sets that contain distict values (for counting) ...

I would use an SQL statement like: SELECT DISTINCT COL4 WHERE COL1 = '50' 

---

Tracey

Answer 1

Hi Tracey,

Thanks for posting in the MSDN Forum.

Would you please clarify your scenario more detailed. Open xml is quite different technic than SQL query language. I need more information for further research.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi,

I have included a snippet of code ... which shows how I would examine a cell value to determine if I need to skip the row or not ...

I'd prefer to just have a valid row 'set'

 SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false);
            WorkbookPart bookPart = doc.WorkbookPart;
            Workbook workbook = bookPart.Workbook;
            Sheet s = workbook.Descendants<Sheet>().Where(sht => sht.Name == "Sheet1").FirstOrDefault();
            s = workbook.Descendants<Sheet>().Where(sht => sht.SheetId == "1").FirstOrDefault();
            WorksheetPart wsPart = (WorksheetPart)bookPart.GetPartById(s.Id);
            SheetData sheetdata = wsPart.Worksheet.Elements<DocumentFormat.OpenXml.Spreadsheet.SheetData>().FirstOrDefault();

            // here I need to filter the row set by the contents of a particular column
            // e.g. say column "C" contains numeric values, and I want all values > 0.5
            foreach (Row row in sheetdata.Elements<Row>().Where(r=> ???? ))
            {

                // now if contents of cell > 0.5 ... process, else skip
                string cellRef = "C" + row.RowIndex.ToString();
                Cell cell = (Cell)row.ChildElements.OfType<Cell>().Where(c => c.CellReference == cellRef).FirstOrDefault();
            }

- do you see what I mean?

---

Tracey

Answer 3

Hi Tracey,

I think your goal is to finish this syntax:

             foreach (Row row in sheetdata.Elements<Row>().Where(r=> ???? ))

Do you want use Linq to implement the function like sql's like operator?

If it is, I think you need use Contaoms. StartsWith or EndsWith to approach your goal.

In this thread you need

var SelectRow = from R in sheetdata.Elements<Row>() where R.CellRef.StartsWith("C") select R;
Foreach(Row row in SelectRow){
...
}

I hope it can help you.

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

I tried the above ... but it will not compile

First I get the error on 'CellRef' >> 'DocumentFormat.OpenXml.Spreadsheet.Row' does not contain a definition for CellRef

and I cannot see that I am missing any assembly reference?

then when I tries to change this up to compile I get the error on 'StartsWith' (same error as above ... no definition)

---

Tracey

Answer 5

Hi Tracey,

Sorry, it's my mistake.

Please try this:

                var RC = from C in SD.Descendants<Cell>().ToList()
                         where
                             C.CellReference.Value.StartsWith("C")
                         select C;
                foreach (Cell cell in RC)
                {
                    ...
                }

Have a good day,

Tom

---

Tom Xu [MSFT]
MSDN Community Support | Feedback to us
Develop and promote your apps in Windows Store
Please remember to mark the replies as answers if they help and unmark them if they provide no help.