Getting a range of cell values into a list using closedXML and C# RRS feed

  • Question

  • User1059168712 posted

    The data is taken from Excel, using ClosedXML. The problem is that I have no idea how many values will be in the column. Maybe 5, maybe more. Therefore, I want ClosedXML to take into account all the filled cells in the range and add them to the list. Here's what my code looks like now: I take the values manually, from each cell.

    var tasks = workbook.Worksheet(1);
    Tasks = new List<Task>()
                                new Task()
                                    TaskName = tasks.Cell("A2").GetFormattedString(),
                                    TaskStart = tasks.Cell("B2").GetFormattedString(),
                                    TaskEnd = tasks.Cell("C2").GetFormattedString(),
                                    TaskStatus = tasks.Cell("D2").GetFormattedString()
                                new Task()
                                    TaskName = tasks.Cell("A3").GetFormattedString(),
                                    TaskStart = tasks.Cell("B3").GetFormattedString(),
                                    TaskEnd = tasks.Cell("C3").GetFormattedString(),
                                    TaskStatus = tasks.Cell("D3").GetFormattedString()

    Friday, January 22, 2021 1:30 PM

All replies

  • User-939850651 posted

    Hi misfowl,

    According to your description, if you want to get a range of cells values, you could use Range() function in OpenXML.

    For example, get the contents of all cells in the range from A2 to D3, this is the code:

    var TableList = new List<string>();
                using (var excelWorkbook = new XLWorkbook(@"D:\demoExcel.xlsx"))
                    var Ws = excelWorkbook.Worksheet("Sheet1");
                    TableList = Ws.Range("A2:D3")   // Get the cells in the range
                        .CellsUsed()                // Filter cells with values
                        .Select(c => c.Value.ToString()) // format cell values to string


    Hope this can help you.

    Best regards,

    Xudong Peng

    Monday, January 25, 2021 3:18 AM