How to add Horizontal Page Breaks to an Excel Worksheet using Interop. Excel?

Question

So I am trying to add horizontal page breaks after every 11 rows of my Excel worksheet created and populated using Interop. Excel but have been so far unsuccessful in doing so. Here is my code snippet:

ws.HPageBreaks.Add(ws.Range["A11","D11"]);//ws=wb. Worksheets[1]

but it isn't working. Any advice is appreciated.

Update: So after debugging and digging in the issue at hand, and using ExcelApp.Visible = true; , I have come to a very weird conclusion. The Horizontal Page Breaks are indeed being added at the intended locations in the worksheet using ws.HPageBreaks.Add(ws.Range) but upon saving and reopening the worksheet,the pagebreaks donot appear!

Answer 1

I think D11 has no effect there. You might add the breaks in a loop. ie:

for (int i = 12; i < ws.UsedRange.Rows.Count; i+=11)
{
    ws.HPageBreaks.Add(ws.Range["A"+i]);
}

PS: Parameter is "Before", therefore it is before 12.

EDIT: A full sample code:

void Main()
{

    // A Sample for getting a DataTable to Excel
    // Note: Normally I would use CopyFromRecordset or QueryTables
    // this is just a sample using a DataTable and array

    // ornek datatable
    // Sample DataTable
    var tbl = new System.Data.DataTable();
    new SqlDataAdapter(@"
      WITH  tally ( SiraNo, UniqueId, RandNumber, RandDateTime )
            AS (
                SELECT TOP 500
                        ROW_NUMBER() OVER ( ORDER BY t1.object_id ), 
                        NEWID(),
                        CAST(CAST(CAST(NEWID() AS VARBINARY(4)) AS INT) AS DECIMAL) / 1000,
                        Cast(DateAdd(second,-CAST(CAST(NEWID() AS VARBINARY(4)) AS INT), getdate()) as datetime)
                FROM   master.sys.all_columns t1
                CROSS JOIN master.sys.all_columns t2
                )
      select SiraNo, UniqueId, RandNumber, 
        RandDateTime, RandDateTime as DateCol, RandDateTime as TimeCol
      from tally;",
        @"server=.;Database=master;Trusted_Connection=yes;")
        .Fill(tbl);

    object[,] arr = new object[tbl.Rows.Count + 1, tbl.Columns.Count];
    for (int i = 0; i < tbl.Columns.Count; i++)
    {
        arr[0, i] = tbl.Columns[i].Caption;
    }
    for (int i = 0; i < tbl.Rows.Count; i++)
    {
        for (int j = 0; j < tbl.Columns.Count; j++)
        {
            arr[i + 1, j] = tbl.Rows[i][j].ToString();
        }
    }

    // Excel dosya yarat ve arrayi koy
    // Create Excel workbook and paste from array
    Excel.Application xl = new Excel.Application();
    var workbook = xl.Workbooks.Add();
    xl.Visible = true;

    Worksheet ws = ((Worksheet)workbook.ActiveSheet);
    Range target = (Range)ws.Range[(Range)ws.Cells[1, 1], (Range)ws.Cells[arr.GetUpperBound(0) + 1, arr.GetUpperBound(1) + 1]];
    target.Value = arr;
    ((Range)ws.Range["D:D"]).NumberFormat = "dd/mm/yyyy hh:mm;@";
    ((Range)ws.Range["E:E"]).NumberFormat = "dd/mm/yyyy;@";
    ((Range)ws.Range["F:F"]).NumberFormat = "hh:mm;@";
    
    
    // Add Horizontal page break after every 11 rows

    for (int i = 12; i < ws.UsedRange.Rows.Count; i += 11)
    {
        ws.HPageBreaks.Add(ws.Range["A" + i]);
    }

}

This is the partial result:

EDIT2: This is just to show the code is working:

void Main()
{

    // A Sample for getting a DataTable to Excel
    // Note: Normally I would use CopyFromRecordset or QueryTables
    // this is just a sample using a DataTable and array

    // ornek datatable
    // Sample DataTable
    var tbl = new System.Data.DataTable();
    new SqlDataAdapter(@"
      WITH  tally ( SiraNo, UniqueId, RandNumber, RandDateTime )
            AS (
                SELECT TOP 500
                        ROW_NUMBER() OVER ( ORDER BY t1.object_id ), 
                        NEWID(),
                        CAST(CAST(CAST(NEWID() AS VARBINARY(4)) AS INT) AS DECIMAL) / 1000,
                        Cast(DateAdd(second,-CAST(CAST(NEWID() AS VARBINARY(4)) AS INT), getdate()) as datetime)
                FROM   master.sys.all_columns t1
                CROSS JOIN master.sys.all_columns t2
                )
      select SiraNo, UniqueId, RandNumber, 
        RandDateTime, RandDateTime as DateCol, RandDateTime as TimeCol
      from tally;",
        @"server=.;Database=master;Trusted_Connection=yes;")
        .Fill(tbl);

    object[,] arr = new object[tbl.Rows.Count + 1, tbl.Columns.Count];
    for (int i = 0; i < tbl.Columns.Count; i++)
    {
        arr[0, i] = tbl.Columns[i].Caption;
    }
    for (int i = 0; i < tbl.Rows.Count; i++)
    {
        for (int j = 0; j < tbl.Columns.Count; j++)
        {
            arr[i + 1, j] = tbl.Rows[i][j].ToString();
        }
    }

    // Excel dosya yarat ve arrayi koy
    // Create Excel workbook and paste from array
    Excel.Application xl = new Excel.Application();
    var workbook = xl.Workbooks.Add();
    xl.Visible = true;

    Worksheet ws = ((Worksheet)workbook.ActiveSheet);
    Range target = (Range)ws.Range[(Range)ws.Cells[1, 1], (Range)ws.Cells[arr.GetUpperBound(0) + 1, arr.GetUpperBound(1) + 1]];
    target.Value = arr;
    ((Range)ws.Range["D:D"]).NumberFormat = "dd/mm/yyyy hh:mm;@";
    ((Range)ws.Range["E:E"]).NumberFormat = "dd/mm/yyyy;@";
    ((Range)ws.Range["F:F"]).NumberFormat = "hh:mm;@";


    // Add Horizontal page break after every 11 rows

    for (int i = 12; i < ws.UsedRange.Rows.Count; i += 11)
    {
        ws.HPageBreaks.Add(ws.Range["A" + i]);
    }
    xl.ActiveWorkbook.SaveAs(@"d:\temp\IWasWrong.xlsx");
    xl.Quit();
}

Print preview after opening the saved file