write to excel - loop through rows&columns missing data - C#

Question

The following code write to excel the right count of rows, but the data inside the file contains only the last data of the list, why? (in my case 84 rows with repeate data that inside arr[list.Capacity -1]) Thanks!!

  List<string> titles = new List<string>() {   "מספר רשיון", "אזור" ,"ישוב", "תאריך אחרון להגשת השגה","מין העץ", "ערך מין העץ", "סה'כ להעתקה" , "סה'כ לכריתה"
            ,"סה'כ לשימור", "מבקש" , "רחוב" , "מספר", "גוש" , "חלקה"};

            List<string[]> list = new List<string[]>(pagedList.Capacity);
            foreach (var result in pagedList)
            {
                
                list.Add(new[] {result.LicenseId?.ToString(),result.ZoneName, result.CityName?.ToString(),
                result.AppealLastDate?.ToString(),  result.TreeName?.ToString(),  result.TreeType.HasValue ? result.TreeType.Value.ToString() :"", result.Copying?.ToString(),
                    result.Unproot?.ToString(), result.Conservation?.ToString(), result.ExpandRows.Select(p => p.CustomerName).FirstOrDefault(), result.ExpandRows.Select(p => p.Street).FirstOrDefault(),
                    result.ExpandRows.Select(p => p.HomeNumber).FirstOrDefault(),result.ExpandRows.Select(p => p.Block).FirstOrDefault(),result.ExpandRows.Select(p => p.Parcel).FirstOrDefault(),

                }); ;
            }
            
            using (var pck = new ExcelPackage())
            {
                var workSheet = pck.Workbook.Worksheets.Add("Sheet1");

                foreach (var arr in list)
                {
                    for (int i = 1; i <= list.Capacity; i++)
                    {
                        for (int j = 1; j <= titles.Count(); j++)
                        {
                            workSheet.Cells[i, j].Value = arr[j-1];
                        }
                    }
                }
             }

The 2 inner `for` loops are always writing to the exact same cells for each `arr` in the `list`. This is the line that is always overwriting the prior values: `workSheet.Cells[i, j].Value = arr[j-1];` — Metro Smurf, Sep 04 '22 at 21:01
@MetroSmurf ok so how should I code it right for every row,column? — bob2 habanay, Sep 05 '22 at 05:00
Is the `foreach()` line necessary @bob2habanay? You seem to be iterating over the rows in the first `for()` loop. Try commenting it out and testing — Daniel, Sep 05 '22 at 11:43

score 0 · Accepted Answer · answered Sep 05 '22 at 13:21

From my comments, the 2 inner for loops are always writing to the exact same cells, effectively overwriting the prior values which is why only the last row of data is being added to the worksheet.

Only one list is needed to hold all the rows, including the titles. The following commented code should be close enough to help resolve the issue:

// only one list is needed to hold all the rows
var rows = new List<string[]>();

// add the headers first
rows.Add( new[] { "מספר רשיון", "אזור" ,"ישוב", "תאריך אחרון להגשת השגה",
                  "מין העץ", "ערך מין העץ", "סה'כ להעתקה" , "סה'כ לכריתה",
                  "סה'כ לשימור", "מבקש" , "רחוב" , "מספר", "גוש" , "חלקה" } );

// then add the values to the list
foreach (var result in pagedList)
{
    rows.Add( new[] { result.LicenseId?.ToString(),result.ZoneName, result.CityName?.ToString(),
                      result.AppealLastDate?.ToString(),  result.TreeName?.ToString(), 
                      result.TreeType.HasValue ? result.TreeType.Value.ToString() : "",
                      result.Copying?.ToString(), result.Unproot?.ToString(), result.Conservation?.ToString(),
                      result.ExpandRows.Select(p => p.CustomerName).FirstOrDefault(),
                      result.ExpandRows.Select(p => p.Street).FirstOrDefault(),
                      result.ExpandRows.Select(p => p.HomeNumber).FirstOrDefault(),
                      result.ExpandRows.Select(p => p.Block).FirstOrDefault(),
                      result.ExpandRows.Select(p => p.Parcel).FirstOrDefault() } );
}

using (var pck = new ExcelPackage())
{
    var workSheet = pck.Workbook.Worksheets.Add("Sheet1");

    // go through each of the rows
    foreach (int r = 0; r < rows.Count; r++)
    {
        string[] row = rows[r];

        // now add the contents of each row to the worksheet
        for (int i = 0; i < row.Length; i++)
        {
            workSheet.Cells[r, i].Value = row[i];
        }
    }
}

write to excel - loop through rows&columns missing data - C#

1 Answers1