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