CODEWITHSUNDEEP
c#excel-2007openxml-sdk
Liao
Liao

Reputation: 53

OpenXML SDK - Setting excel page break to a certain number of columns

Does anyone know how to set the excel page break to include a certain number of columns using C# with the OpenXML SDK? What I want to do is make x columns appear on one page. I had originally thought setting the print area would do it but it doesn't. I can't find any references to do this.

This is done manually in an excel spreadsheet's "Page Break View" where you drag the vertical dotted line to include more columns.

Thanks

Upvotes: 3

Views: 4460

Answers (1)

Hans
Hans

Reputation: 13040

The OpenXML SDK distinguish between manual horizontal page breaks and manual vertical page breaks.

  • A manual horizontal page break allows you to specify a break above a given row Id (index).
  • A vertical page break allows you to specify a break to the left of the specified column Id (index).

To programmatically insert a horizontal page break use the RowBreaks and Break class. The RowBreaks class represents a collection of all horizontal page breaks in a worksheet.

The ColumnBreaks and Break class allow you to insert a vertical page break. The ColumnBreaks class holds all vertical page breaks for a worksheet.

The following example demonstrate the insertion of a vertical page break. The function InsertVerticalPageBreak() takes a columnIndex (where the page break should be inserted) and the WorksheetPart. This function first checks if the worksheet already contains a ColumnBreaks collection. If not, one will be created. Then the function creates an instance of the Break class and sets the Id property to the column index. I've also set the Max property to the maximum number of rows Excel is able to handle to get a continuing vertical page break. By setting the property ManualPageBreak to true we specifing a manual page break.

I've also added a InsertHorizontalPageBreak() function to the sample to show how to add a horizontal page break.

private void InsertPageBreaks()
{
  uint columnIndex = 17U;
  uint rowIndex = 51U;

  using (SpreadsheetDocument sd = SpreadsheetDocument.Open("c:\\temp\\spreadsheet.xlsx", true))
  {
    WorkbookPart workbookPart = sd.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last();

    // Uncomment the following line to insert row page breaks.
    // InsertHorizontalPageBreak(rowIndex, worksheetPart);
    InsertColumnVerticalBreak(columnIndex, worksheetPart);       
  }
}

private void InsertHorizontalPageBreak(uint rowIndex, WorksheetPart worksheetPart)
{
  Break rowBreak =
      new Break() { Id = (UInt32Value)rowIndex, Max = (UInt32Value)16383U, ManualPageBreak = true };

  RowBreaks rb = worksheetPart.Worksheet.GetFirstChild<RowBreaks>();

  if (rb == null)
  {
    rb = new RowBreaks();
    rb.ManualBreakCount = (UInt32Value)0;
    rb.Count = (UInt32Value)0;

    worksheetPart.Worksheet.Append(rb);
  }

  rb.Append(rowBreak);
  rb.ManualBreakCount++;
  rb.Count++;
}

private void InsertVerticalPageBreak(uint columnIndex, WorksheetPart worksheetPart)
{
  ColumnBreaks cb = worksheetPart.Worksheet.GetFirstChild<ColumnBreaks>();

  if (cb == null)
  {
    cb = new ColumnBreaks();

    cb.ManualBreakCount = (UInt32Value)0;
    cb.Count = (UInt32Value)0;    

    worksheetPart.Worksheet.Append(cb);
  }

  Break br = 
    new Break() { Id = (UInt32Value)columnIndex, Max = (UInt32Value)1048575U, ManualPageBreak = true };

  cb.Append(br);

  cb.ManualBreakCount++;
  cb.Count++;
}

Upvotes: 3

Related Questions