When the automatic scaling options FitToColumns and FitToRows are used, the vertical and horizontal breaks, respectively, should be ignored.
Workaround:
Remove the page breaks if the worksheet uses automatic scaling and restore them if necessary:
public void ExcelToPdf(string path)
{
var provider = new XlsxFormatProvider();
using (Stream input = File.OpenRead(path))
{
var workbook = provider.Import(input);
this.CachePageBreaks(workbook);
var pdfProvider = new PdfFormatProvider();
pdfProvider.ExportSettings = new PdfExportSettings(ExportWhat.EntireWorkbook, false);
using (Stream output = File.Open(@"C:\Users\velcheva\Desktop\26293903-c650-4d28-adee-58a542651f72_exceltopdf\exported.pdf", FileMode.Create))
{
pdfProvider.Export(workbook, output);
}
this.RestorePageBreaks(workbook);
}
}
private Dictionary<Worksheet,List<PageBreak>> verticalPageBreaksCache;
private void CachePageBreaks(Workbook workbook)
{
this.verticalPageBreaksCache = new Dictionary<Worksheet, List<PageBreak>>();
foreach (Worksheet worksheet in workbook.Worksheets)
{
WorksheetPageSetup pageSetup = worksheet.WorksheetPageSetup;
if (pageSetup.FitToPages)
{
this.verticalPageBreaksCache[worksheet] = new List<PageBreak>(pageSetup.PageBreaks.VerticalPageBreaks);
if(pageSetup.FitToPagesWide != 0)
{
List<PageBreak> breaksToRemove = new List<PageBreak>();
breaksToRemove.AddRange(pageSetup.PageBreaks.VerticalPageBreaks);
breaksToRemove.ForEach((br) => pageSetup.PageBreaks.TryRemoveVerticalPageBreak(0, br.Index));
}
// Record the horizontal page breaks if necessary.
}
}
}
private void RestorePageBreaks(Workbook workbook)
{
foreach (Worksheet worksheet in workbook.Worksheets)
{
WorksheetPageSetup pageSetup = worksheet.WorksheetPageSetup;
if (pageSetup.FitToPages)
{
List<PageBreak> cachedbreaks = this.verticalPageBreaksCache[worksheet];
if (pageSetup.FitToPagesWide != 0)
{
foreach (var pageBreak in cachedbreaks)
{
pageSetup.PageBreaks.TryInsertVerticalPageBreak(0, pageBreak.Index);
}
}
// Restore the horizontal page breaks if necessary.
}
}
}