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

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()
                            }}

Answer 1

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
                    .ToList();
            }
            TableList.ForEach(Console.WriteLine);
            Console.ReadLine();

Result:

Hope this can help you.

Best regards,

Xudong Peng