C# - EPPlus sometimes only returning cells with values, breaking my data gathering-CodePudding

The long story short is I'm trying to convert the data from 3 different Excel documents into 5 separate CSV files, using a combination of data from all of them to do it. 2 of the 3 files are working without issues, but one of the files contains slightly different data - though there are 9 total columns being utilized (41,730 rows), only 3-5 of the columns will have data in each row other than the first (header row). The issue comes up in that it doesn't actually even include the columns without data...so throwing all of the data into an array list has varying numbers of segments in the individual arrays (so I can't associate the data properly).

Here's the code I'm running:

using (ExcelPackage xlPackage = new ExcelPackage(new System.IO.FileInfo(strInputFile)))
        {
            ExcelWorksheet myWorksheet = xlPackage.Workbook.Worksheets.First();
            int totalRows = myWorksheet.Dimension.End.Row;
            int totalColumns = myWorksheet.Dimension.End.Column;

            for (int rowNum = 1; rowNum <= totalRows; rowNum  )
            {
                var row = myWorksheet.Cells[rowNum, 1, rowNum, totalColumns].Select(c => c.Value == null ? string.Empty : c.Value.ToString());
                listOutput.Add(string.Join("~", row).Split('~'));
            }
        }

This works perfectly for the others, but in this file the first row has 9 segments, then every subsequent row has 3-5, depending on how many columns have values (the first 2 will always have values, then 1-2 additional columns will in each row). The other files are filling in the blank columns with empty strings, using the lambda in the Select, but I don't know why it's not doing it in this one. All 3 came from the same source (client environment export), and have the same formatting.

CodePudding user response：

Most likely myWorksheet.Cells[rowNum, 1, rowNum, totalColumns] simply isn't returning the cells that don't have values. Try something like this:

var row = Enumerable.Range(1, totalColumns)
    .Select(columnNum => myWorksheet.Cells[rowNum, columnNum])
    .Select(c => c?.Value?.ToString() ?? string.Empty);