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.

Sun Feb 10, 2019 5:53 pm

Hi, when exporting a worksheet or the entire document to a PDF, is it possible to include the worksheet name stamped in the top of the resultant page/s?

ie. i have "sales", "order", "returns".. i'd like a PDF like


Code: Select all
                       Sales
[A1]
[A2]
[A3]


                               Returns
[A1]
[A2]
[A3]

etc.

Thanks

jtmp
 
Posts: 3
Joined: Sun Feb 10, 2019 5:50 pm

Mon Feb 11, 2019 3:41 am

Hello jtmp,

Thanks for your inquiry.
As for your requirement, please try the following code.
Code: Select all
Workbook workbook = new Workbook();
workbook.LoadFromFile(inputfile);
foreach(Worksheet sheet in workbook.Worksheets)
{
  sheet.PageSetup.CenterHeader = "&A";
}
string output = "result.pdf";
workbook.SaveToFile(output,FileFormat.PDF);


Sincerely,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Mon Feb 11, 2019 10:09 am

Hi Amy,

Thank you so much for this. I can confirm it works 100%. However I do have one other question, which i am happy to move/start a new thread for if needs be. When saving the workbook to a PDF, is there any way to set a custom page size? For example, currently, when I save the spreadsheet to a PDF it makes everything fit perfectly horizontally, but the vertical page sizes are increased massively. If i wanted to scale the data to fit to a landscape A4 page, how would I do this please? The examples i've tried (and all combinations of sheetfittopage, papersize, fittopageswide, etc.) do not seem to have worked. My current code is below, and I am using an example spreadsheet (hxxps://community.tableau.com/docs/DOC-1236) as a proof of concept.

The first attachment (sheetfittopage) is perfect. This was generated by just setting SheetFitToPage as true, and nothing else in the worksheet settings. However the pages are way too long - see left hand side of screenshot. The second is what happens when I set fittopageswide/fittopagestall. It doesn't matter if I set one or the other, or both, but get the same result. My code is below:

Code: Select all
            using (var fsi = System.IO.File.OpenRead(@"aa.xls"))
            {
                using (var wb = new Spire.Xls.Workbook())
                {
                    wb.LoadFromStream(fsi);
                    // wb.ConverterSetting.SheetFitToPage = true;
                   

                    using (var fs = File.Create(@"res.pdf"))
                    {
                        foreach (Worksheet ws in wb.Worksheets)
                        {
                            //ws.ViewMode = ViewMode.Layout;
                            ws.PageSetup.CenterHeader = "&A";
                            ws.PageSetup.PaperSize = PaperSizeType.PaperA4;
                            // ws.PageSetup.IsFitToPage = true;
                            ws.PageSetup.FitToPagesWide = 1;
                            ws.PageSetup.FitToPagesTall = 1;
                           

                            ws.PageSetup.PrintArea = null;
                            ws.GridLinesVisible = true;
                            ws.PageSetup.IsPrintGridlines = true;
                            ws.PageSetup.IsPrintHeadings = true;
                            ws.PageSetup.Orientation = PageOrientationType.Landscape;

                            ws.AllocatedRange.AutoFitColumns();
                            ws.AllocatedRange.AutoFitRows();
                        }

                        wb.SaveToStream(fs, Spire.Xls.FileFormat.PDF);

                        Console.WriteLine($"Saved workbook");
                    }
                }
            }


Ideally i'd want this like my first attachment, just with more normal vertical page breaks. Any ideas?


Thanks again
Attachments
sheetfittopage.png
sheetfittopage
sheetfittopage.png (1.83 MiB) Viewed 785 times
Last edited by jtmp on Mon Feb 11, 2019 10:11 am, edited 1 time in total.

jtmp
 
Posts: 3
Joined: Sun Feb 10, 2019 5:50 pm

Mon Feb 11, 2019 10:10 am

Just replying here with the second attachment, as it was too large to do both in one post.
Attachments
fittopage.png
fittopage
fittopage.png (473.08 KiB) Viewed 785 times

jtmp
 
Posts: 3
Joined: Sun Feb 10, 2019 5:50 pm

Tue Feb 12, 2019 2:33 am

Hello jtmp,

Thanks for your feedback.
I am glad to hear that this works.
For the new problem, please set ws.PageSetup.FitToPagesTall = 0 while setting ws.PageSetup.FitToPagesWide = 1.
The following is my test code. But I found that the converted pdf file could not be opened successfully by Adobe Reader on my side. I have forwarded it to our dev team for further investigation. We will inform you once there is any good news. Please first try to use browser to open it and check the result.
Code: Select all
using (var fsi = System.IO.File.OpenRead(@"..\\..\\Sample - Superstore.xls"))
                {
                    using (var wb = new Spire.Xls.Workbook())
                    {
                        wb.LoadFromStream(fsi);
   
                        using (var fs = File.Create(@"res3.pdf"))
                        {
                            foreach (Worksheet ws in wb.Worksheets)
                            {
                                ws.AllocatedRange.AutoFitColumns();
                                ws.AllocatedRange.AutoFitRows();

                                ws.PageSetup.CenterHeader = "&A";
                                ws.PageSetup.PaperSize = PaperSizeType.PaperA4;
                             
                                ws.PageSetup.FitToPagesWide = 1;
                                ws.PageSetup.FitToPagesTall = 0;
 
                                ws.GridLinesVisible = true;
                                ws.PageSetup.IsPrintGridlines = true;
                                ws.PageSetup.IsPrintHeadings = true;
                                ws.PageSetup.Orientation = PageOrientationType.Landscape;
                            }

                            wb.SaveToStream(fs, Spire.Xls.FileFormat.PDF);                 
                        }
                    }
                }


Sincerely,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Thu Feb 21, 2019 7:58 am

Hi jtmp,

Greetings from e-iceblue!
We just released Spire.XLS Pack(Hotfix) Version:9.2.8 which fixed the PDF opening issue. Please download it from the following links:
Website link: https://www.e-iceblue.com/Download/down ... t-now.html
Nuget link: https://www.nuget.org/packages/Spire.XLS/

Sincerely,
Jane
E-iceblue support team
User avatar

Jane.Bai
 
Posts: 1156
Joined: Tue Nov 29, 2016 1:47 am

Mon Feb 25, 2019 6:30 am

Hello jtmp,

Hope you are doing well!
How did Spire.XLS Pack(Hotfix) Version:9.2.8 work on your side?
I will really appreciate it if you can give us some feedback.

Sincerely,
Amy
E-iceblue support team
User avatar

amy.zhao
 
Posts: 2766
Joined: Wed Jun 27, 2012 8:50 am

Return to Spire.XLS