none
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

    Tuesday, February 12, 2013 6:11 PM

Answers

  • 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.


    Wednesday, February 20, 2013 3:00 AM
    Moderator

All replies

  • 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.

    • Marked as answer by Tracey Macias Tuesday, February 19, 2013 4:29 PM
    • Unmarked as answer by Tracey Macias Tuesday, February 19, 2013 6:32 PM
    Wednesday, February 13, 2013 2:04 AM
    Moderator
  • 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

    Wednesday, February 13, 2013 6:28 PM
  • 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.

    Thursday, February 14, 2013 3:29 AM
    Moderator
  • 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

    Tuesday, February 19, 2013 4:34 PM
  • 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.


    Wednesday, February 20, 2013 3:00 AM
    Moderator
  • Humm... still not what I need .... now that I am returning to this problem

    IF I opened the Excel spread sheet using OLE DB classes I could use a SQL Select clause, such as

    'Select * where Vol > 50'

    This is the type of 'filter' I need for the row set ....

    I now assume my only option is to open using OLE classes (which I was trying to avoid) ... as I cannot determine how to apply a filter to the sheetData (or RowSet)

    My end user will enter a filter using 'text' .... and it will refer to a column header such as 'Vol'

    they may enter 'Vol<30' or 'Vol=10' etc.


    Tracey

    Thursday, August 22, 2013 9:40 PM