'EPPlus C# - Add Separators Between Duplicate Rows

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.

wrong table screenshot

must have table screenshot



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source