B. Clay Shannon B. Clay Shannon - 17 days ago 5
C# Question

Is it possible to add manual page breaks to PivotTables (Excel Interop)?

When programmatically generating a "normal" Excel sheet, a Range's PageBreak property can be set to xlPageBreakManual and then you can specify where the page is to break with code like so:

workbook.Worksheets[0].VPageBreaks.Add(sheet.Range["G1"]);


...but is this possible when printing a PivotTable? I would think not, since the PivotTable is fed from a datasource (a page of "raw data" in my case, which I subsequently hide), but if it is, I would like to know how.

This is what I do now to prepare the PivotTablized sheet for printing:

private void FormatPivotTable()
{
. . .
ConfigureForPrinting(_xlPivotTableSheet.UsedRange.Rows.Count);
}

private void ConfigureForPrinting(int finalRow)
{
string lastColumn = GetExcelTextColumnName(_xlPivotTableSheet.UsedRange.Columns.Count);
string printArea = String.Format("A1:{0}{1}", lastColumn, finalRow);
_xlPivotTableSheet.PageSetup.PrintArea = printArea;
_xlPivotTableSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
_xlPivotTableSheet.PageSetup.Zoom = false;
_xlPivotTableSheet.PageSetup.FitToPagesWide = 1;
_xlPivotTableSheet.PageSetup.FitToPagesTall = 50;

_xlPivotTableSheet.PageSetup.LeftMargin = _xlApp.Application.InchesToPoints(0.5);
_xlPivotTableSheet.PageSetup.RightMargin = _xlApp.Application.InchesToPoints(0.5);
_xlPivotTableSheet.PageSetup.TopMargin = _xlApp.Application.InchesToPoints(0.5);
_xlPivotTableSheet.PageSetup.BottomMargin = _xlApp.Application.InchesToPoints(0.5);
_xlPivotTableSheet.PageSetup.HeaderMargin = _xlApp.Application.InchesToPoints(0.5);
_xlPivotTableSheet.PageSetup.FooterMargin = _xlApp.Application.InchesToPoints(0.5);

string rangeToRepeat = string.Format("$A$6:${0}$7", lastColumn);
_xlPivotTableSheet.PageSetup.PrintTitleRows = rangeToRepeat;
}


What the user wants is that each logical block of data be kept together on a sheet, so that a block (5 rows) does not span more than one sheet. IOW, if there are less than 5 rows of space on the current page when a block begins, skip to the next page with a page break.

Is this possible with PivotTables?

EEM EEM
Answer

I understand the that when you are referring to "logical blocks of data" it means data corresponding to a PivotFieldItem. If that is the case the try this:

Set these PivotTable properties to TRUE:

ActiveSheet.PivotTables("PivotTable1").PrintTitles = True
ActiveSheet.PivotTables("PivotTable1").RepeatItemsOnEachPrintedPage = True

Also set to TRUE the LayoutPageBreak property of the PivotField for which you want to set a page break every time there is a change of item:

ActiveSheet.PivotTables("PivotTable1").PivotFields("Class").LayoutPageBreak = True

Also need to set this property to TRUE in case there are items with only one record.

ActiveSheet.PivotTables("PivotTable1").PivotFields("Class").LayoutBlankLine = True

Replace "PivotTable1" and "Class" values as required

Although the commands above are in VBA they should give you a good idea of how to set these properties using C#

Comments