How to add empty rows in excel sheet data table using c#

Question

How to add empty rows in my data table and then loop through the column heading and split by- Then year insert to the empty row and After that merge the year row

Here is my data table code:

       private void button1_Click(object sender, EventArgs e)
       {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            Excel.Range chartRange;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        xlWorkSheet.Cells[2, 1] = "Retailer";

        xlWorkSheet.Cells[2, 2] = "Brand";
        xlWorkSheet.Cells[2, 3] = "2019_WK1";

        xlWorkSheet.Cells[2, 4] = "2019_WK2";

            xlWorkSheet.Cells[2, 5] = "2019_WK3";


            xlWorkSheet.Cells[3, 1] = "LuLu";
            xlWorkSheet.Cells[3, 2] = "Perisil";
            xlWorkSheet.Cells[3, 3] = "25";
            xlWorkSheet.Cells[3, 4] = "26";
            xlWorkSheet.Cells[3, 5] = "23";

            xlWorkSheet.Cells[4, 1] = "Lulu";
            xlWorkSheet.Cells[4, 2] = "Ariel";
            xlWorkSheet.Cells[4, 3] = "26";
            xlWorkSheet.Cells[4, 4] = "28";
            xlWorkSheet.Cells[4, 5] = "29";

            xlWorkSheet.Cells[5, 1] = "Danube";
            xlWorkSheet.Cells[5, 2] = "Omo";
            xlWorkSheet.Cells[5, 3] = "27";
            xlWorkSheet.Cells[5, 4] = "28";
            xlWorkSheet.Cells[5, 5] = "30";


            xlWorkSheet.Cells[6, 1] = "Danube";
            xlWorkSheet.Cells[6, 2] = "Tide";
            xlWorkSheet.Cells[6, 3] = "24";
            xlWorkSheet.Cells[6, 4] = "23";
            xlWorkSheet.Cells[6, 5] = "29";


            xlWorkSheet.Cells[7, 1] = "Bin Dawood";
            xlWorkSheet.Cells[7, 2] = "Persil";
            xlWorkSheet.Cells[7, 3] = "26";
            xlWorkSheet.Cells[7, 4] = "27";
            xlWorkSheet.Cells[7, 5] = "28";


        xlWorkBook.SaveAs("F:\\CTR_Data", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlApp);
            releaseObject(xlWorkBook);
            releaseObject(xlWorkSheet);

            MessageBox.Show("File created !");
        }    

I want to add five empty rows in ("A1" to "E1" cells) and loop through the column heading and split by-

I want a excel output like above picture

Answer 1

You must use the merge method (xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[2, 1]].Merge();)

Application xlApp;
        Workbook xlWorkBook;
        Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        Range chartRange;

        xlApp = new Application();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);

        xlWorkSheet.Cells[1, 1] = "Retailer";
        xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[2, 1]].Merge();

        xlWorkSheet.Cells[2, 2] = "Brand";
        xlWorkSheet.Range[xlWorkSheet.Cells[1, 2], xlWorkSheet.Cells[2, 2]].Merge();

        xlWorkSheet.Cells[2, 3] = "2019_WK1";

        xlWorkSheet.Cells[2, 4] = "2019_WK2";

        xlWorkSheet.Cells[2, 5] = "2019_WK3";


        xlWorkSheet.Cells[3, 1] = "LuLu";
        xlWorkSheet.Cells[3, 2] = "Perisil";
        xlWorkSheet.Cells[3, 3] = "25";
        xlWorkSheet.Cells[3, 4] = "26";
        xlWorkSheet.Cells[3, 5] = "23";

        xlWorkSheet.Cells[4, 1] = "Lulu";
        xlWorkSheet.Cells[4, 2] = "Ariel";
        xlWorkSheet.Cells[4, 3] = "26";
        xlWorkSheet.Cells[4, 4] = "28";
        xlWorkSheet.Cells[4, 5] = "29";

        xlWorkSheet.Cells[5, 1] = "Danube";
        xlWorkSheet.Cells[5, 2] = "Omo";
        xlWorkSheet.Cells[5, 3] = "27";
        xlWorkSheet.Cells[5, 4] = "28";
        xlWorkSheet.Cells[5, 5] = "30";


        xlWorkSheet.Cells[6, 1] = "Danube";
        xlWorkSheet.Cells[6, 2] = "Tide";
        xlWorkSheet.Cells[6, 3] = "24";
        xlWorkSheet.Cells[6, 4] = "23";
        xlWorkSheet.Cells[6, 5] = "29";


        xlWorkSheet.Cells[7, 1] = "Bin Dawood";
        xlWorkSheet.Cells[7, 2] = "Persil";
        xlWorkSheet.Cells[7, 3] = "26";
        xlWorkSheet.Cells[7, 4] = "27";
        xlWorkSheet.Cells[7, 5] = "28";


        xlWorkSheet.Cells[1, 3] = "Year";
        xlWorkSheet.Range[xlWorkSheet.Cells[1, 3], xlWorkSheet.Cells[1, 5]].Merge();

        xlWorkBook.SaveAs("test", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();