How to add horizontal page break in interop excel worksheet

Question

I am trying to add page break in interop excel file but not able to do it yet. Sometimes nothing happens and sometimes i gets exception "Unable to set the PageBreak property of the Range class".

Here is template file.

Excel Template file

I am new to Interop Excel. I am using an excel file as template which is in PageBreakPreview. When i inserts data in new row then i want to shift page break after that row. I have seen many suggested answers but could not get it done.

Here is some code (coomented) i have tried.

object misValue = System.Reflection.Missing.Value;
Excel.Application xlApp = new Excel.Application();

Excel.Workbook xlWorkBook = null;
Excel.Worksheet worksheet = null;
try
{
    xlWorkBook = xlApp.Workbooks.Open(Server.MapPath("~//Templates//ExcelTemplate.xlsx")
        , 0, true, misValue, misValue, misValue, misValue, Excel.XlPlatform.xlWindows, misValue, misValue, true, misValue, misValue, misValue, misValue);
    worksheet = (Excel.Worksheet)xlWorkBook.Worksheets[1];
    worksheet.Activate();
    worksheet.Cells[6, 1] = "Item1";
    worksheet.Cells[6, 1].Font.Bold = true;
    worksheet.Cells[7, 1] = "content";
    //worksheet.HPageBreaks.Add(sheet.Range["A7"]);
    //worksheet.HPageBreaks.Add(sheet.Range["A13"]);
    //xlWorkBook.Worksheets[0].ViewMode = ViewMode.Preview;
    //worksheet.Rows[5].PageBreak = xlPageBreakManual;
    //xlApp.ActiveWindow.View = Excel.XlWindowView.xlNormalView;
    //worksheet.Cells.PageBreak = (int)Excel.XlPageBreak.xlPageBreakNone;
    //worksheet.Rows[4].PageBreak = Excel.XlPageBreak.xlPageBreakNone;
    //worksheet.Rows[8].PageBreak = Excel.XlPageBreak.xlPageBreakManual;
    //worksheet.ResetAllPageBreaks();
    //worksheet.HPageBreaks[1].Location = worksheet.Range["A8"];
    xlWorkBook.SaveAs(Server.MapPath("~//TempFiles//ExcelFile_Copy.xlsx"));
}
catch (Exception ex)
{
    //ex
}
finally
{
    xlWorkBook.Close(0);
    xlApp.Quit();

    Marshal.ReleaseComObject(worksheet);
    Marshal.ReleaseComObject(xlWorkBook);
    Marshal.ReleaseComObject(xlApp);
}

When data for new is added page break should get to the end of next row.

Any help is greatly appreciated.

Answer 1

I figured out that whitish area on sheet is print area actually so i just updated the xlWorkBook.Worksheets[1].PageSetup.PrintArea and assigned new print area value to move PageBreak under 7th row which is automatic page break i think. Any one correct me if i am wrong.

It is what i did to solve my problem:

xlWorkBook.Worksheets[1].PageSetup.PrintArea = "$A$1:$H$20";