EPPlus C# - Add Separators Between Duplicate Rows

Question

I am transferring the data from the database to an excel document with EPPlus. I grouped the data from the database. No problem so far. I need to separate the grouped data with new row. I tried several methods myself but it doesn't work right. Because after the row is added, the row indexes are completely mixed. Can you help me?

DataTable dt = new DataTable();


        using (MySqlConnection conn = new MySqlConnection(connStr))
        {
            using (MySqlDataAdapter da = new MySqlDataAdapter("SELECT ki.musteriAdi, kv.vardiya, ki.isTuru, ki.isAdi, ki.liman, ki.booking, ki.parti, SUM(konteynerAdet) FROM konteynervardiya AS kv, konteynerisi AS ki WHERE ki.ID=kv.konteynerIsiID GROUP BY kv.vardiya, ki.isAdi ORDER BY kv.vardiya, ki.liman", conn))
            {
                conn.Open();
                da.Fill(dt);   
                conn.Close();
            }
        }

        List<String> vardiyalar = new List<String>();            

        using (MySqlConnection conn = new MySqlConnection(connStr))
        {
            using (MySqlCommand cmd = new MySqlCommand("SELECT kv.vardiya AS kvvardiya, SUM(kv.konteynerAdet) AS kvkonteyneradet FROM konteynervardiya AS kv, konteynerisi AS ki WHERE ki.ID=kv.konteynerIsiID GROUP BY kv.vardiya, ki.isAdi ORDER BY kv.vardiya, ki.liman",conn))
            {
                conn.Open();
                using (MySqlDataReader dr = cmd.ExecuteReader())
                {                        
                    while (dr.Read())
                    {
                        vardiyalar.Add(dr["kvvardiya"].ToString());                           
                    }
                    conn.Close();
                }
            }               
        }    

        using (ExcelPackage pck = new ExcelPackage())
        {
            var worksheet = pck.Workbook.Worksheets.FirstOrDefault(x => x.Name == "Attempts");
            worksheet = pck.Workbook.Worksheets.Add("Konteyner Çalışma Raporu");

            var cells = worksheet.Cells["A1:H1"];
            worksheet.Cells["A1"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.None);

            worksheet.TabColor = Color.Gold;
            worksheet.DefaultRowHeight = 12;
            worksheet.Row(1).Height = 18;
            worksheet.Row(1).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;

            using (ExcelRange Rng = worksheet.Cells[1, 1, 1, 8])
            {
                Rng.Style.Font.Bold = true;
                Rng.Style.Font.Color.SetColor(Color.White);
                Rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                Rng.Style.Fill.BackgroundColor.SetColor(Color.DeepSkyBlue);
            }

            worksheet.Cells[1, 1].Value = "MÜŞTERİ";
            worksheet.Cells[1, 2].Value = "TARİH/VARDİYA";
            worksheet.Cells[1, 3].Value = "YÜKLEME/TAHLİYE";
            worksheet.Cells[1, 4].Value = "İŞ İSMİ";
            worksheet.Cells[1, 5].Value = "LİMAN";
            worksheet.Cells[1, 6].Value = "BOOKING";
            worksheet.Cells[1, 7].Value = "PARTİ";
            worksheet.Cells[1, 8].Value = "YÜKLEME/LASHING";              


            var indices = from number in vardiyalar.Distinct()
                          select vardiyalar.ToList().LastIndexOf(number);
            int[] aIndices = indices.ToArray();


            foreach (int iIndex in aIndices)
            {
                int index = iIndex +1;

                worksheet.InsertRow(index, 1, 1);                                     

            }

            worksheet.Cells.AutoFitColumns();
            worksheet.Cells.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

            pck.Workbook.Properties.Title = "Attempts";
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "Konteyner Çalışma Raporu.xlsx"));
            Response.BinaryWrite(pck.GetAsByteArray());
            Response.End();
        }

Normally each date and time group should be separated by a . However, there are errors as seen in the image.

Answer 1

@Ulas KAYALAR , Welcome to Microsoft Q&A, based on my test, I reproduced your problem.

I think your ideas about getting the index of the last duplicated rows, but we need to note that the index of the rows will change after we already add empty row to the excel.

Here is a code example you could refer to.

var indices = from number in vardiyalar.Distinct()  
                              select vardiyalar.ToList().LastIndexOf(number);  
                int[] aIndices = indices.ToArray();  
  
                int i = 0;  
                foreach (int iIndex in aIndices)  
                {  
                    int index = iIndex + 3+i;  
  
                    worksheet.InsertRow(index, 1, 1);  
                    i++;  
  
                }  

Result:

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.