EPPlus C# Export data to Excel. Table data overwrites Namebox instead of "Insert" and preserving other cells

Question

I am using EPPlus C# library to generate Excel file. An Excel template is to be filled with SQL Server data that is coming in a DataTable. Template file uses NameBoxes, the data is filled within those nameboxes. The issue is that for eg. I have namebox at position A1

and another namebox at position B1

when A1 is filled with a single row data.. it is fine. But if data in A1 has multple rows it overwrites A2 namebox (namebox at A2 is gone).

The namebox at A2 should be shifted down to A6.

(Same behaviour we see when doing "Insert" in a cell that shifts other cells ahead instead of overwriting them).

Answer 1

I solved it by inserting new rows using the following logic:

   // Get current cell's address
                            string oldAddress = nameBox.Start.Address;
                            // insert new rows in worksheet to preserve other nameboxes
                            nameBox.Worksheet.InsertRow(nameBox.Start.Row, dt.Rows.Count);
                            // move to old address
                            nameBox.Address = oldAddress;

                            // Fill Name box with Datatable
                            nameBox.LoadFromDataTable(dt, printHeaders);