/// <summary>
/// This method wil figure out the optimal paper size to use and sets it
/// </summary>
/// <param name="worksheet"></param>
/// <param name="printArea"></param>
private static void SetWorkSheetPaperSize(ExcelInterop._Worksheet worksheet, string printArea)
{
var pageSetup = worksheet.PageSetup;
var pages = pageSetup.Pages;
pageSetup.PrintArea = printArea;
pageSetup.LeftHeader = worksheet.Name;
var pageCount = pages.Count;
if (pageCount == 1)
{
return;
}
try
{
pageSetup.Order = ExcelInterop.XlOrder.xlOverThenDown;
foreach (var paperSize in PaperSizes)
{
var exitfor = false;
pageSetup.PaperSize = paperSize.PaperSize;
pageSetup.Orientation = paperSize.Orientation;
worksheet.ResetAllPageBreaks();
foreach (var zoomRatio in ZoomRatios)
{
// Yes these page counts look lame, but so is Excel 2010 in not updating
// the pages collection otherwise. We need to call the count methods to
// make this code work
pageSetup.Zoom = zoomRatio;
// ReSharper disable once RedundantAssignment
pageCount = pages.Count;
if (CountVerticalPageBreaks(worksheet.VPageBreaks) == 0)
{
exitfor = true;
break;
}
}
if (exitfor)
{
break;
}
}
}
finally
{
Marshal.ReleaseComObject(pages);
Marshal.ReleaseComObject(pageSetup);
}
}
