Table of Contents

Intelligent page breaks (VB.Net / netframework)

Note

This demo is available in your FlexCel installation at <FlexCel Install Folder>\samples\vb\VS2022\netframework\10.API\72.Intelligent Page Breaks and also at https:​//​github.​com/​tmssoftware/​TMS-​FlexCel.​NET-​demos/​tree/​master/​vb/​VS2022/​netframework/​Modules/​10.​API/​72.​Intelligent Page Breaks

Overview

While there is no direct support in Excel for Widow/Orphan control, FlexCel has the capacity to add page breaks to your file, so you can keep interesting sections together.

Make sure to read the conceptual documentation about Intelligent Page Breaks to better understand what we are doing here.

Concepts

  • How to add automatic page breaks to a sheet. In this case, we are dumping a C# file to PDF, and we want to keep procedures in the same page is possible.

  • How to deal with different levels of "keep together". FlexCel allows you to make some rows more "keep together" than others, if it can't fit everything in the same page, it will try to keep the rows of higher "keep together". We use this here to try to keep full classes first, if it is not possible full procedures, if not full for/while loops, etc.
    Each "{" sign in the source file means higher level of "keep together", and each "}" decreases the level.

  • The method ExcelFile.AutoPageBreaks must be called after everything is done, so the sheet is in a final state when applying the page breaks.

Files

AssemblyInfo.vb

Imports System.Reflection
Imports System.Runtime.CompilerServices

'
' General Information about an assembly is controlled through the following 
' set of attributes. Change these attribute values to modify the information
' associated with an assembly.
'
<Assembly: AssemblyTitle("")>
<Assembly: AssemblyDescription("")>
<Assembly: AssemblyConfiguration("")>
<Assembly: AssemblyCompany("")>
<Assembly: AssemblyProduct("")>
<Assembly: AssemblyCopyright("(c) 2002 - 2014 TMS Software")>
<Assembly: AssemblyTrademark("")>
<Assembly: AssemblyCulture("")>

'
' Version information for an assembly consists of the following four values:
'
'      Major Version
'      Minor Version 
'      Build Number
'      Revision
'
' You can specify all the values or you can default the Revision and Build Numbers 
' by using the '*' as shown below:

<Assembly: AssemblyVersion("6.2.1.0")>

'
' In order to sign your assembly you must specify a key to use. Refer to the 
' Microsoft .NET Framework documentation for more information on assembly signing.
'
' Use the attributes below to control which key is used for signing. 
'
' Notes: 
'   (*) If no key is specified, the assembly is not signed.
'   (*) KeyName refers to a key that has been installed in the Crypto Service
'       Provider (CSP) on your machine. KeyFile refers to a file which contains
'       a key.
'   (*) If the KeyFile and the KeyName values are both specified, the 
'       following processing occurs:
'       (1) If the KeyName can be found in the CSP, that key is used.
'       (2) If the KeyName does not exist and the KeyFile does exist, the key 
'           in the KeyFile is installed into the CSP and used.
'   (*) In order to create a KeyFile, you can use the sn.exe (Strong Name) utility.
'       When specifying the KeyFile, the location of the KeyFile should be
'       relative to the project output directory which is
'       %Project Directory%\obj\<configuration>. For example, if your KeyFile is
'       located in the project directory, you would specify the AssemblyKeyFile 
'       attribute as [assembly: AssemblyKeyFile("..\\..\\mykey.snk")]
'   (*) Delay Signing is an advanced option - see the Microsoft .NET Framework
'       documentation for more information on this.
'
<Assembly: AssemblyDelaySign(False)>
<Assembly: AssemblyKeyFile("")>
<Assembly: AssemblyKeyName("")>

Form1.Designer.vb

Imports System.Collections
Imports System.ComponentModel
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Imports System.IO
Imports System.Reflection
Imports System.Text
Imports FlexCel.Render
Namespace IntelligentPageBreaks
	Partial Public Class mainForm
		Inherits System.Windows.Forms.Form

		''' <summary>
		''' Required designer variable.
		''' </summary>
		Private components As System.ComponentModel.Container = Nothing

		''' <summary>
		''' Clean up any resources being used.
		''' </summary>
		Protected Overrides Sub Dispose(ByVal disposing As Boolean)
			If disposing Then
				If components IsNot Nothing Then
					components.Dispose()
				End If
			End If
			MyBase.Dispose(disposing)
		End Sub

		#Region "Windows Form Designer generated code"
		''' <summary>
		''' Required method for Designer support - do not modify
		''' the contents of this method with the code editor.
		''' </summary>
		Private Sub InitializeComponent()
			Me.button1 = New System.Windows.Forms.Button()
			Me.saveFileDialog1 = New System.Windows.Forms.SaveFileDialog()
			Me.label1 = New System.Windows.Forms.Label()
			Me.SuspendLayout()
			' 
			' button1
			' 
			Me.button1.Anchor = System.Windows.Forms.AnchorStyles.Bottom
			Me.button1.Location = New System.Drawing.Point(132, 73)
			Me.button1.Name = "button1"
			Me.button1.TabIndex = 0
			Me.button1.Text = "GO!"
'			Me.button1.Click += New System.EventHandler(Me.button1_Click)
			' 
			' saveFileDialog1
			' 
			Me.saveFileDialog1.Filter = "Pdf Files|*.pdf"
			Me.saveFileDialog1.RestoreDirectory = True
			Me.saveFileDialog1.Title = "Select PDF file to save"
			' 
			' label1
			' 
			Me.label1.Anchor = (CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) Or System.Windows.Forms.AnchorStyles.Left) Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles))
			Me.label1.BackColor = System.Drawing.Color.FromArgb((CByte(255)), (CByte(255)), (CByte(192)))
			Me.label1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
			Me.label1.Location = New System.Drawing.Point(16, 16)
			Me.label1.Name = "label1"
			Me.label1.Size = New System.Drawing.Size(312, 32)
			Me.label1.TabIndex = 1
			Me.label1.Text = "A  demo on how to have FlexCel paginate your documents so they keep their section" & "s together."
			' 
			' mainForm
			' 
			Me.AutoScaleDimensions = New System.Drawing.SizeF(6F, 13F)
			Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
			Me.ClientSize = New System.Drawing.Size(336, 110)
			Me.Controls.Add(Me.label1)
			Me.Controls.Add(Me.button1)
			Me.Name = "mainForm"
			Me.Text = "Intelligent Page Breaks"
			Me.ResumeLayout(False)

		End Sub
		#End Region

		Private WithEvents button1 As System.Windows.Forms.Button
		Private saveFileDialog1 As System.Windows.Forms.SaveFileDialog
		Private label1 As System.Windows.Forms.Label

	End Class
End Namespace

Form1.vb

Imports System.ComponentModel
Imports FlexCel.Core
Imports FlexCel.XlsAdapter
Imports System.IO
Imports System.Reflection
Imports System.Text

Imports FlexCel.Render

Namespace IntelligentPageBreaks
	''' <summary>
	''' Demo showing how to create intelligent page breaks with the API.
	''' </summary>
	Partial Public Class mainForm
		Inherits System.Windows.Forms.Form

		Public Sub New()
			InitializeComponent()
		End Sub

		Private Keywords As Dictionary(Of String, String) = CreateKeywords()


		Private Shared Function CreateKeywords() As Dictionary(Of String, String)
			' A very silly syntax highlighter. We don't have any context here, so for example "get" will be highlighted when it is a property or when it is not, but it is ok for this demo.
			Dim Result As New Dictionary(Of String, String)()

			Result.Add("private", Nothing)
			Result.Add("public", Nothing)
			Result.Add("protected", Nothing)
			Result.Add("internal", Nothing)
			Result.Add("static", Nothing)
			Result.Add("void", Nothing)
			Result.Add("get", Nothing)
			Result.Add("set", Nothing)
			Result.Add("return", Nothing)
			Result.Add("while", Nothing)
			Result.Add("for", Nothing)
			Result.Add("foreach", Nothing)
			Result.Add("using", Nothing)
			Result.Add("true", Nothing)
			Result.Add("false", Nothing)

			Return Result
		End Function

		Private ReadOnly Property PathToExe() As String
			Get
				Return Path.Combine(Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location), ".."), "..") & Path.DirectorySeparatorChar
			End Get
		End Property

		Private Function SyntaxColor(ByVal Xls As ExcelFile, ByVal NormalFont As Integer, ByVal CommentFont As Integer, ByVal HighlightFont As Integer, ByVal line As String) As TRichString
			Dim RTFRunList As New List(Of TRTFRun)()

			Dim i As Integer = 0
			Do While i < line.Length
				If i > 0 AndAlso line.Chars(i - 1) = "/"c AndAlso line.Chars(i) = "/"c Then
					Dim rtf As TRTFRun
					rtf.FirstChar = i - 1
					rtf.FontIndex = CommentFont
					RTFRunList.Add(rtf)
					Return New TRichString(line, RTFRunList.ToArray(), Xls)

				End If

				Dim start As Integer = i
				Do While i < line.Length AndAlso Char.IsLetterOrDigit(line.Chars(i))
					i += 1
				Loop

				If i > start AndAlso Keywords.ContainsKey(line.Substring(start, i - start)) Then
					Dim rtf As TRTFRun
					rtf.FirstChar = start
					rtf.FontIndex = HighlightFont
					RTFRunList.Add(rtf)
					rtf.FirstChar = i
					rtf.FontIndex = NormalFont
					RTFRunList.Add(rtf)
				End If

				i += 1
			Loop


			Return New TRichString(line, RTFRunList.ToArray(), Xls)
		End Function

		Private Sub DumpFile(ByVal Xls As ExcelFile, ByRef Row As Integer)
			Dim fnt As TFlxFont = Xls.GetDefaultFont
			fnt.Color = Color.Blue
			Dim HighlightFont As Integer = Xls.AddFont(fnt)
			fnt.Color = Color.Green
			Dim CommentFont As Integer = Xls.AddFont(fnt)

			Dim Level As Integer = 0
			Dim LevelStart As New Stack(Of Integer)()
			LevelStart.Push(Row)

			Using sr As New StreamReader(Path.Combine(PathToExe, "Form1.cs"))
				Dim line As String
				line = sr.ReadLine()
				Do While line IsNot Nothing
					'Find the level of "keep together" for the row. We will use #region and "{" delimiters
					'to increase the level. If possible, we would want those blocks together in one page.
					Dim s As String = line.Trim()
					If s.StartsWith("#region") Then
						Level += 1
						LevelStart.Push(Row)
					End If
					If s = "{" Then
						Level += 1
						LevelStart.Push(Row - 1) 'On {} blocks, we want to keep lines together starting with the previous statement.
					End If

					If s = "#endregion" OrElse s = "}" Then
						Level -= 1
						Xls.KeepRowsTogether(LevelStart.Pop(), Row, Level + 1, False)
					End If

					Xls.KeepRowsTogether(Row, Row, Level, True)


					Xls.SetCellValue(Row, 1, SyntaxColor(Xls, 0, CommentFont, HighlightFont, line.Replace(vbTab, "    ")))
					Row += 1
					line = sr.ReadLine()
				Loop
			End Using
		End Sub

		Private Sub AddData(ByVal Xls As ExcelFile)

			'Fill the file with the contents of this c# file, many times so we can see many page breaks.
			Dim Row As Integer = 3
			DumpFile(Xls, Row)

			Xls.AutofitRowsOnWorkbook(False, True, 1)
			Xls.AutoPageBreaks(50, 100) ' we will use a 100% of page scale since we are printing to pdf.
										 'If this was to create an Excel file, pagescale should be lower to 
										 'compensate the differences between page sizes in diiferent printers in Excel

			'Export the file to PDF so we can see the page breaks.
			Using pdf As New FlexCelPdfExport(Xls, True)
				pdf.Export(saveFileDialog1.FileName)
			End Using

		End Sub


		Private Sub button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.Click
			AutoRun()
		End Sub

		Public Sub AutoRun()
			If saveFileDialog1.ShowDialog() <> System.Windows.Forms.DialogResult.OK Then
				Return
			End If
			Dim Xls As ExcelFile = New XlsFile(True)
			Xls.NewFile(1, TExcelFileFormat.v2019)
			Xls.SetColWidth(1, 78 * 256) ';make longer lines wrap in the cell.
			Dim fmt As TFlxFormat = Xls.GetFormat(Xls.GetColFormat(1))
			fmt.WrapText = True

			Xls.SetColFormat(1, Xls.AddFormat(fmt))
			AddData(Xls)
			If MessageBox.Show("Do you want to open the generated file?", "Confirm", MessageBoxButtons.YesNo) = System.Windows.Forms.DialogResult.Yes Then
				Process.Start(saveFileDialog1.FileName)
			End If

		End Sub
	End Class
End Namespace

Program.vb

Namespace IntelligentPageBreaks
	Friend NotInheritable Class Program

		Private Sub New()
		End Sub

		''' <summary>
		''' The main entry point for the application.
		''' </summary>
		<STAThread> _
		Shared Sub Main()
			Application.EnableVisualStyles()
			Application.SetCompatibleTextRenderingDefault(False)
			Application.Run(New mainForm())
		End Sub
	End Class
End Namespace