I have an excel file where visually one row = three rows with merged cells.
It works great.
However, when I go to convert to PDF the three rows get split between pages. How do I keep the three rows from splitting to other pages and then autofit them for printing?
Range rn = sheet.Cells.CreateRange("A" + cellNum1, "AF" + cellNum2);
if (count % 2 == 0)
rn.SetStyle(EvenRowStyle);
else
rn.SetStyle(OddRowStyle);
Here is a better explanation. I want to keep this range from being split across multiple pages when being saved as a PDF for printing.
static void Main(string[] args)
{
// Create a workbook.
Workbook wb = new Workbook();
// Obtaining the reference of the default(first) worksheet
Worksheet sheet = wb.Worksheets[0];
var BlueHeader = wb.CreateStyle();
BlueHeader.Pattern = BackgroundType.Solid;
BlueHeader.ForegroundColor = System.Drawing.Color.FromArgb(255, 70, 130, 180);
BlueHeader.Font.Color = System.Drawing.Color.White;
BlueHeader.Font.Size = 6;
BlueHeader.Font.Name = "Tahoma";
BlueHeader.Font.IsBold = true;
BlueHeader.HorizontalAlignment = TextAlignmentType.Center;
BlueHeader.VerticalAlignment = TextAlignmentType.Bottom;
BlueHeader.IsTextWrapped = true;
BlueHeader.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.LightGray);
BlueHeader.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.LightGray);
BlueHeader.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.LightGray);
var OddRowStyle = wb.CreateStyle();
OddRowStyle.Pattern = BackgroundType.Solid;
OddRowStyle.ForegroundColor = System.Drawing.Color.FromArgb(255, 220, 220, 220);
OddRowStyle.Font.Size = 6;
OddRowStyle.Font.Name = "Arial";
OddRowStyle.HorizontalAlignment = TextAlignmentType.Center;
OddRowStyle.VerticalAlignment = TextAlignmentType.Center;
OddRowStyle.IsTextWrapped = true;
OddRowStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.LightGray);
OddRowStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.LightGray);
OddRowStyle.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.LightGray);
OddRowStyle.IsTextWrapped = true;
var EvenRowStyle = wb.CreateStyle();
EvenRowStyle.Pattern = BackgroundType.Solid;
EvenRowStyle.ForegroundColor = System.Drawing.Color.White;
EvenRowStyle.Font.Size = 6;
EvenRowStyle.Font.Name = "Arial";
EvenRowStyle.HorizontalAlignment = TextAlignmentType.Center;
EvenRowStyle.VerticalAlignment = TextAlignmentType.Center;
EvenRowStyle.IsTextWrapped = true;
EvenRowStyle.SetBorder(BorderType.RightBorder, CellBorderType.Thin, System.Drawing.Color.LightGray);
EvenRowStyle.SetBorder(BorderType.LeftBorder, CellBorderType.Thin, System.Drawing.Color.LightGray);
EvenRowStyle.IsTextWrapped = true;
var blue = new List<header>();
blue.Add(new header { c = "A2", v = "#" });
blue.Add(new header { c = "B2", v = "ID" });
blue.Add(new header { c = "C2", v = "PC" });
blue.Add(new header { c = "D2", v = "Title" });
blue.Add(new header { c = "F2", v = "Location" });
blue.Add(new header { c = "E2", v = "ANumber" });
blue.Add(new header { c = "G2", v = "Type" });
blue.Add(new header { c = "H2", v = "Somthing1" });
blue.Add(new header { c = "I2", v = "Something2" });
blue.Add(new header { c = "J2", v = "Manager" });
blue.Add(new header { c = "K2", v = "Something3" });
blue.Add(new header { c = "L2", v = "Something4" });
blue.Add(new header { c = "M2", v = "Something5 / Something6" });
blue.Add(new header { c = "N2", v = "Notes" });
for (var i = blue.Count - 1; i >= 0; i--)
{
var cell = sheet.Cells[blue[i].c];
cell.SetStyle(BlueHeader);
cell.Value = blue[i].v;
}
var r = new Random();
var items = new List<LineItem>();
for(var i = 1; i< 50; i++)
{
var item = new LineItem()
{
ID = r.Next(1, 2),
Priority = r.Next(),
Title = r.Next(),
Location = r.Next(),
ANumber = r.Next(),
AType = r.Next(),
Something1 = r.Next(),
Something2 = r.Next(),
Something3 = r.Next(),
Something4 = r.Next(),
Something5 = r.Next(),
Something6 = r.Next(),
Notes = r.Next()
};
items.Add(item);
}
var count = 1;
var rowCount = 1;
foreach (var item in items)
{
var cellNum1 = count + 2;
var cellNum2 = count + 4;
Range rn = sheet.Cells.CreateRange("A" + cellNum1, "N" + cellNum2);
if (count % 2 == 0)
rn.SetStyle(EvenRowStyle);
else
rn.SetStyle(OddRowStyle);
rn.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, System.Drawing.Color.Black);
rn.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
var numRange = sheet.Cells.CreateRange("A" + cellNum1, "A" + cellNum2);
numRange.Merge();
numRange.Value = rowCount;
//ID
var idRange = sheet.Cells.CreateRange("B" + cellNum1, "B" + cellNum2);
idRange.Merge();
idRange.Value = item.ID;
//Priority
var pRange = sheet.Cells.CreateRange("C" + cellNum1, "C" + cellNum2);
pRange.Merge();
pRange.Value = item.Priority;
//Title
var titleRange = sheet.Cells.CreateRange("D" + cellNum1, "D" + cellNum2);
titleRange.Merge();
titleRange.Value = item.Title;
//location
var locationRange = sheet.Cells.CreateRange("E" + cellNum1, "E" + cellNum2);
locationRange.Merge();
locationRange.Value = item.Location;
//A Number
var aNumberRange = sheet.Cells.CreateRange("F" + cellNum1, "F" + cellNum2);
aNumberRange.Merge();
aNumberRange.Value = item.ANumber;
//A Type
var aTypeRange = sheet.Cells.CreateRange("G" + cellNum1, "G" + cellNum2);
aTypeRange.Merge();
aTypeRange.Value = item.AType;
//Something 1
var something1Range = sheet.Cells.CreateRange("H" + cellNum1, "H" + cellNum2);
something1Range.Merge();
something1Range.Value = item.Something1;
//Something 2
var something2Range = sheet.Cells.CreateRange("I" + cellNum1, "I" + cellNum2);
something2Range.Merge();
something2Range.Value = item.Something2;
//Manager
var mangerRange = sheet.Cells.CreateRange("J" + cellNum1, "J" + cellNum2);
mangerRange.Merge();
mangerRange.Value = item.Manager;
//Something 3
var something3Range = sheet.Cells.CreateRange("K" + cellNum1, "K" + cellNum2);
something3Range.Merge();
something3Range.Value = item.Something3;
//Something 4
var specRange = sheet.Cells.CreateRange("L" + cellNum1, "L" + cellNum2);
specRange.Merge();
specRange.Value = item.Something4;
//Something 5 / Something 6
var something5Range = sheet.Cells.CreateRange("M" + cellNum1, "M" + (cellNum1 + 1));
something5Range.Merge();
something5Range.Value = item.Something5;
something5Range.SetOutlineBorder(BorderType.BottomBorder, CellBorderType.Thin, System.Drawing.Color.Black);
var something6Cell = sheet.Cells["M" + (cellNum1 + 2)];
something6Cell.Value = item.Something6;
//Notes
var noteRange = sheet.Cells.CreateRange("N" + cellNum1, "N" + cellNum2);
noteRange.Merge();
noteRange.Value = item.Notes;
count = count + 3;
}
// Defining column numbers A & B as title columns
sheet.PageSetup.PrintTitleColumns = "$A:$A";
// Defining row numbers 1 & 2 as title rows
sheet.PageSetup.PrintTitleRows = "$1:$2";
Aspose.Cells.PdfSaveOptions options = new Aspose.Cells.PdfSaveOptions();
options.AllColumnsInOnePagePerSheet = true;
// Saving the Excel file
wb.Save(@"c:\test\" + "output.pdf", options);
// ExEnd:1
}
public class LineItem
{
public int ID { get; set; }
public int Priority { get; set; }
public int Title { get; set; }
public int Location { get; set; }
public int ANumber { get; set; }
public int AType { get; set; }
public int Something1 { get; set; }
public int Something2 { get; set; }
public int Manager { get; set; }
public int Something3 { get; set; }
public int Something4 { get; set; }
public int Something5 { get; set; }
public int Something6 { get; set; }
public int Notes { get; set; }
}
public class header
{
public string c { get; set; }
public string v { get; set; }
}
}
Is there a way to keep the range from being split between pages?
Thanks for using Aspose APIs.
We have generated the output Pdf but we don’t find any error. Please download the output Pdf and check it. If you find any issue, please highlight the issues by adding comments inside the output Pdf. Please also provide us expected Pdf for our investigation.
Download Link:
output.pdf (79.2 KB)
Thanks for using Aspose APIs.
We have looked into your issue further. Please change your code like the following and it should fix your issue. Please also check the output Pdf generated after making this change inside the code.
Download Link:
output.pdf (78.7 KB)
C#
// Defining column numbers A & B as title columns
sheet.PageSetup.PrintTitleColumns = "$A:$A";
// Defining row numbers 1 & 2 as title rows
sheet.PageSetup.PrintTitleRows = "$1:$2";
ImageOrPrintOptions imgOpt = new ImageOrPrintOptions();
//consistent with your PdfSaveOptions
imgOpt.AllColumnsInOnePagePerSheet = true;
//auto split pages at 2 + 3 * x row.
int checkCellAreaIndex = 0;
while (true)
{
CellArea[] cellAreas = sheet.GetPrintingPageBreaks(imgOpt);
//the CellArea being checked is the last CellArea;
if (cellAreas.Length - 1 <= checkCellAreaIndex)
{
break;
}
CellArea cellArea = cellAreas[checkCellAreaIndex];
//check endrow is 2 + 3 * x
//cellArea.EndRow is zero based.
int mod = (cellArea.EndRow + 1 - 2) % 3;
if (mod != 0)
{
//add next row of (cellArea.EndRow - mod) to horizontal page breaks.
sheet.HorizontalPageBreaks.Add(cellArea.EndRow - mod + 1);
}
//check next CellArea
checkCellAreaIndex++;
}
Aspose.Cells.PdfSaveOptions options = new Aspose.Cells.PdfSaveOptions();
options.AllColumnsInOnePagePerSheet = true;
// Saving the Excel file
wb.Save(@"c:\test\" + "output.pdf", options);