Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Mon Aug 30, 2021 8:59 am

Hello,

I faced such a problem, when converting from Excel to PDF, automatic page breaks are not transferred (examples in the test.rar attachment)

I tried setting manual breaks in the place I wanted, but they only applied if IsFitToPage = false.

but at the same time, another problem arises at the scale I need, a few more automatic breaks are added, due to the fact that the content does not fit,

otherwise I have to zoom out in my example to 16%, which does not quite suit me

I am using the following code

Code: Select all

    public class XlsConvertSettings
    {
        public List<SheetConvertSettings> Sheets { get; set; } = new List<SheetConvertSettings>();       
    }
   
   public class SheetConvertSettings
    {
        public string SheetName { get; set; }
        public List<string> HPageBreakRanges { get; set; }
        public List<string> VPageBreakRanges { get; set; }
        public int Scale { get; set; }
        public int TempScale { get; set; }
        public bool IsConsvertToPdf { get; set; }
    }
...
      private float KSize { get; set; } = 0f;
        private void SetPageBreaks(Workbook workbook, XlsConvertSettings settings)
        {
            for (int i = 0; i < workbook.Worksheets.Count; i++)
            {
                var sheet = workbook.Worksheets[i];
                var sheetSettings = settings.Sheets.FirstOrDefault(s => string.Equals(sheet.Name, s.SheetName, StringComparison.InvariantCultureIgnoreCase)) ?? new SheetConvertSettings();
                if (sheetSettings.HPageBreakRanges.Any() && sheetSettings.VPageBreakRanges.Any()) continue;
                workbook.Worksheets[i].PageSetup.PrintArea = string.Empty;
                sheetSettings.VPageBreakRanges.ForEach(adress => workbook.Worksheets[i].VPageBreaks.Add(sheet.Range[adress]));
                sheetSettings.HPageBreakRanges.ForEach(adress => workbook.Worksheets[i].HPageBreaks.Add(sheet.Range[adress]));
                if (sheet.PageSetup.IsFitToPage) continue;
                workbook.Worksheets[i].PageSetup.Zoom = sheetSettings.IsConsvertToPdf ? sheetSettings.TempScale : sheetSettings.Scale;
                KSize = (sheetSettings.Scale > sheetSettings.TempScale) ? (sheetSettings.Scale / sheetSettings.TempScale) : KSize;
            }
        }



I also tried to transfer the generated workbook to PDfDocument, and there I try to correct the situation through the page size, while I deliberately passed the TempScale scale value 16 so that only one manual page break would be applied

Code: Select all

        private PdfDocument AddContentIntoDocument(byte[] content, PdfDocument document)
        {
            PdfDocument pdfDoc = new();
            pdfDoc.LoadFromBytes(content);
            foreach (PdfPageBase page in pdfDoc.Pages)
            {
                var size = KSize > 0f ? (page.Size/KSize) : page.Size;
                PdfPageBase p = document.Pages.Add(size, new Spire.Pdf.Graphics.PdfMargins(0));
                page.CreateTemplate().Draw(p, 0, 0);
            }
            return document;
        }
...
        private MemoryStream ConvertPdfFormatFromXls(Workbook workbook, string resultFileName)
        {
            using (var stream = new MemoryStream())
            {
                workbook.SaveToStream(stream, Spire.Xls.FileFormat.PDF);
                PdfDocument pdfDocument = new();
                pdfDocument = AddContentIntoDocument(stream.ToArray(), pdfDocument);
                using (var pdfStream = new MemoryStream())
                {
                    pdfDocument.SaveToStream(pdfStream);
                    return pdfStream;
                }
            }
        }



but unfortunately this did not help, I will be grateful to you for the solution proposed to me,

taking into account the fact that in my example the scale I want is preserved, and the set manual page breaks are carried over when converting to pdf

Best Regards, Stanislav Shevyakov
Attachments
test.rar
(109.93 KiB) Downloaded 248 times

s.shevyakov
 
Posts: 18
Joined: Thu Jul 15, 2021 8:42 am

Mon Aug 30, 2021 12:12 pm

Hello Stanislav,

Thanks for your inquiry.

Sorry I'm not very clear about your requirement.
Our Spire.XLS is based on Microsoft Excel. I open your file in Microsoft Excel and found that the page width is set to two pages, and the page break is between the "Ac" column and the "AD" column (as shown below). I did an initial test and found that when not manually setting page breaks, the PDF generated by our product is indeed paginated according to this. Also, I checked the PDF file you provided and found the same result.
screenshot.png
screenshot.png (45.97 KiB) Viewed 2985 times


Which column do you want the page break to be in? Could you please provide us with your complete code (or a runnable project) and your desired result for further investigation? You can send them to us (support@e-iceblue.com) via email. Thanks in advance.


Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Mon Aug 30, 2021 12:39 pm

What version are you using microsoft office? I am using microsoft office 2016

and a page break is set between AD and AE columns (screen.rar)
Attachments
screen.rar
(94.8 KiB) Downloaded 233 times

s.shevyakov
 
Posts: 18
Joined: Thu Jul 15, 2021 8:42 am

Tue Aug 31, 2021 10:32 am

Hello,

Thanks for your inquiry.
I am also using Microsoft Office 2016, but the page break shown at my side is indeed between the "AC" column and the "AD" column. To help us investigate further, please provide your OS information (eg Win7 64bit) and your region setting (eg China , Chinese). At the same time, we suggest you can also try another computer or use other versions of Microsoft Office to open this Excel file.

Besides, if you want to control page breaks by manually inserting page breaks, you need to change the scale of sheet to 22%, so that there is only one page without any extra automatic page breaks (see the attached screenshot). And then insert a page break to "AE1", like the code below.
Code: Select all
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("5007f812-a853-4508-86b8-eaa2e89eb482.xlsx");

            workbook.Worksheets[0].PageSetup.Zoom = 22;
            workbook.Worksheets[0].PageSetup.IsFitToPage = true;

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

            workbook.SaveToFile("result.pdf", FileFormat.PDF);


Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Wed Sep 08, 2021 2:35 am

Hello,

Greetings from E-iceblue!
Can the code we provided meet you needs? Could you please give us some feedback at your convenience?

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Thu Sep 16, 2021 12:24 pm

OS - Windows 10 Pro 64bit
Region - Russian

burz00m21
 
Posts: 12
Joined: Thu Sep 16, 2021 12:20 pm

Fri Sep 17, 2021 8:56 am

Hello,

Thank you for your reply.
I tried to open your Excel file on multiple computers in the same environment as yours, but it is strange that the page breaks were not the same. Some are between AC and AD columns, but some are between AD and AE. After repeatedly testing and comparing the configuration of the system, I found that this page break may be affected by the default printer. When the default printer of the same computer is changed, the position of the page break will be changed too. So we speculate that this should be related to Microsoft's internal mechanism. Sorry, we do not have any good solutions to solve this issue. We recommend that you manually insert page breaks to control page breaks.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS