Thanks a lot for your help! That does work, however I found out 1 more obstacle to getting exactly what I need.
When I print or export to PDF any run-time generated sheets, they are oversized. Only my first sheet (which is prepared in actual Excel) is printed or exported to PDF correctly.
I have the following codes when generating new sheets, to which none helped:
worksheets[index].WorksheetPageSetup.Margins = new PageMargins(0, 0, 0, 0);
worksheets[index].WorksheetPageSetup.CenterVertically = true;
worksheets[index].WorksheetPageSetup.CenterHorizontally = true;
worksheets[index].WorksheetPageSetup.PageOrder = PageOrder.DownThenOver;
worksheets[index].WorksheetPageSetup.PageOrientation = PageOrientation.Portrait;
worksheets[index].WorksheetPageSetup.PaperType = PaperTypes.A4;
Here are my printing and export to PDF codes:
PrintQueue pq = LocalPrintServer.GetDefaultPrintQueue();
pq.UserPrintTicket.PageOrientation = System.Printing.PageOrientation.Portrait;
pq.UserPrintTicket.PageMediaType = PageMediaType.Plain;
pq.UserPrintTicket.PageMediaSize = new PageMediaSize(PageMediaSizeName.ISOA4);
var pd = new PrintDialog
PrintQueue = pq
for (int i = 0; i != currWSIndex + 1; i++)
//setting no. of sheets (total)
worksheets[i].Cells[7, 8].SetValue(currWSIndex >= 9 ? "" : "0" + (currWSIndex + 1));
sheet1211.ActiveSheet = worksheets[i];
sheet1211.Print(new PrintWhatSettings(ExportWhat.ActiveSheet, false), pd);
var pdf = new PdfFormatProvider
ExportSettings = new PdfExportSettings(ExportWhat.EntireWorkbook, false)
Note that the printing results look totally exactly like the PDF. The only thing I can think of is that, at the real Excel where I saved the xlsx template, there's a print option "shrink sheet to 1 page". I thought this should applies to the workbook such that when I generate new sheets at runtime, this print option will also apply. Is there a workaround or am I missing anything else?