1

I want to read data from an excel table that looks like this: Excel Table Screenshot

Worksheet name is "Data". I store the data into List<ExcelData>. Then I do a List.Count, and it is more than 11 even though I only have 11 non-empty rows.

    public class ExcelData
    {
        public string Id { get; set; }
        public string FgCmd { get; set; }
        public string SwCmd { get; set; }
    }

    public void PrintExcelTable()
    {
        var excelFile = new ExcelQueryFactory(@"C:\sample.xlsx");

        var tableData = from z in excelFile.Worksheet<ExcelData>("Data")
                        select z;

        var List = tableData.ToList<ExcelData>();

        Console.WriteLine(List.Count);
    }

I want my List to store only the non-empty row. Any workaround? Preferably be solved during the LinQ selection, not by removing empty data from List after.

Liren Yeo
  • 3,215
  • 2
  • 20
  • 41

1 Answers1

2

Try putting a where clause :

from z in excelFile.Worksheet<ExcelData>("Data") where z.id != ""
                        select z;
auburg
  • 1,373
  • 2
  • 12
  • 22
  • Tried that, I would get weird error saying `'Data' is not a valid worksheet name`. But `Data` is exactly my worksheet name! – Liren Yeo Mar 31 '16 at 10:25
  • I think it is something about the unary and member expression. – Liren Yeo Mar 31 '16 at 10:27
  • What if you try putting ToList() here : z in excelFile.Worksheet("Data").ToList() where... – auburg Mar 31 '16 at 10:28
  • Actually, the problem lies in excel, not the code. I'm so sorry. I'm not sure why, but after I deleted all the empty rows under the table, the code now works as intended. – Liren Yeo Mar 31 '16 at 10:35
  • Anyway, if user accidentally put down a empty space in `Id` column, `z.Id != ""` is not able to handle that. Can you suggest a better `.Where` condition? – Liren Yeo Mar 31 '16 at 10:36
  • I think `where (!String.IsNullOrWhiteSpace(z.Id))` solves that. Let me know if you have better suggestion. – Liren Yeo Mar 31 '16 at 10:39