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.

Thu May 28, 2015 7:19 pm

Using the Spire.XLS/Spire.PDF and C#, I am successfully creating a PDF file from an several Excel (2013) worksheets however, the resulting PDF file has extra white space to the right of the main visible cells of the worksheet. The worksheets contain several 'hidden' lookup cells, which sit to the right of the visible cells and it appears that the width of the PDF file is set by: 'the width of the visible cells' + 'width of the hidden cells'.

How can I produce a PDF file whose width is set only by the visible cells and not the hidden cells? I have tried copying the Cell Range to a new worksheet and converted that, but the layout is different and doesn't look like the original.

I am using streams (with no disk io) and the code is:

public byte[] ExtractWorksheetsAsPdf(byte[] excelFile, int[] worksheets)
{
using (var excelStream = new MemoryStream(excelFile))
{
var workbook = new Workbook();
workbook.LoadFromStream(excelStream, ExcelVersion.Version2013);

var pdfDocument = new PdfDocument();
foreach (Spire.Xls.Worksheet worksheet in workbook.Worksheets.Where(x => worksheets.Contains(x.Index + 1)))
{
var pdfConverter = new PdfConverter(worksheet);
var settings = new PdfConverterSettings
{
TemplateDocument = pdfDocument,
FitSheetToOnePage = FitToPageType.NoScale,
};
pdfDocument = pdfConverter.Convert(settings);
}
using (var ms = new MemoryStream())
{
pdfDocument.SaveToStream(ms);
return ms.ToArray();
}
}
}

crushworth@westernforest.com
 
Posts: 6
Joined: Mon May 25, 2015 4:29 pm

Fri May 29, 2015 2:05 am

Hello,

Thanks for your inquiry.
Please try the new method below.
Code: Select all
Workbook wb = new Workbook();
            wb.LoadFromFile("d:\\test.xlsx");
            using (var stream = new MemoryStream())
            {
                wb.SaveToStream(stream, FileFormat.PDF);
                stream.Position = 0;
                File.WriteAllBytes("file.pdf", stream.ToArray());
            }

If you still have issue, please send us your sample excel file. It would be helpful to replicate the issue and work out the solution for you ASAP.

Best Regards,
Sweety

E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Tue Jun 02, 2015 9:44 am

Hello,

Have you tried the new method?
Has your issue been resolved?
Thanks for your feedback.

Best Regards,
Sweety

E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Wed Jun 03, 2015 5:40 pm

Hello Sweety,

I have tried the method you suggested. It creates a PDF file which cannot be read. I am using Adobe Reader XI Version 11.0.5.

Also I tried wb.SaveToPdf(file) which produced a PDF file that could be read by Adobe however, because there is no adjustment made to scaling each worksheet appears across multiple pdf pages.

In my original post I am only selecting a subset of worksheets from the workbook, and the worksheets are different layouts, with some in landscape and some in portrait. I would like each worksheet (regardless of orientation) to appear on a separate page within the output PDF. Hence the reason I was using the 'PdfConverter' and 'PdfConverterSettings' objects.

The original problem of hidden cells creating white space is therefore not resolved.

I have attached the spreadsheet with the hidden cells (see: worksheet 1 'Expense Report' columns M-AL). I would like to create a PDF (as a stream) of worksheets 1 & 2 with no hidden cells and no extra white space.

Thank you,

crushworth@westernforest.com
 
Posts: 6
Joined: Mon May 25, 2015 4:29 pm

Thu Jun 04, 2015 2:43 am

Hello,

Thank you for your reply.
Sorry that I didn't find your attachment. Please check it. Please kindly note that firstly you need to compress it and upload.

Best Regards,
Sweety

E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Fri Jun 05, 2015 9:35 am

Hello,

Has your issue been resolved?
If not, please offer us your sample file.

Best Regards,
Sweety

E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Wed Jun 10, 2015 3:25 pm

I have compressed the spreadsheet and uploaded it again. It contains some hidden cells to the right of and below the main image in the first worksheet.
Attachments
TravelExpenseForm May2015 - V4.zip
Excel Spread with hidden cells producing white space when exported to PDF.
(65.51 KiB) Downloaded 365 times

crushworth@westernforest.com
 
Posts: 6
Joined: Mon May 25, 2015 4:29 pm

Fri Jun 12, 2015 8:52 am

Hello,

Thanks for your reply.
Please refer to the code below.
Code: Select all
  Workbook wb = new Workbook();
            wb.LoadFromFile("d:\\test.xlsx");
            wb.ConverterSetting.SheetFitToPage = true;
            wb.SaveToFile("result.pdf", Spire.Xls.FileFormat.PDF);


Best Regards,
Sweety

E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Mon Jun 15, 2015 9:29 am

Hello,

Thanks for your response.
So sorry that currently there might be no other solution, and at present our product only saves the particular worksheets as PDF file with the following method, And I have tested it with the newest hotfix ( Spire.XLS Pack Version:7.7), there is no whitespace, here is the result in the attachment . Because the previous method you used is older, which will be obsoleted as you can see the warning when you used, so we won't maintain it, and we will add a new method to saves the particular worksheets as stream.
Code: Select all
Workbook wb = new Workbook();
            wb.LoadFromFile("d:\\TravelExpenseForm May2015 - V4.xlsx");       
            Worksheet sheet0 = wb.Worksheets[0];
            sheet0.SaveToPdf("result1.pdf", Spire.Xls.FileFormat.PDF);


Best Regards,
Sweety

E-iceblue support team
Attachments
result1.rar
(123.51 KiB) Downloaded 269 times
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Fri Jun 19, 2015 7:12 am

Hello,

Thanks for your waiting.
Now our product supports to save the specified worksheet to pdf stream. Please download the newest hotfix(spire.xls_7.7.12) and try the following method.
Code: Select all
 Workbook wb = new Workbook();
        wb.LoadFromFile("d:\\TravelExpenseForm May2015 - V4.xlsx");
        for (int i = 0; i < wb.Worksheets.Count; i++)
        {
            using(MemoryStream ms=new MemoryStream())
            {
                wb.Worksheets[i].SaveToPdfStream(ms, FileFormat.PDF);
            }
           
        }


Best Regards,
Sweety

E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Thu Jun 25, 2015 7:36 am

Hello,

Have your tried the newest hotfix (spire.xls_7.7.12) and the code?
Has your issue been resolved?
Could you please give us some feedback at your convenience?

Best Regards,
Sweety

E-iceblue support team
User avatar

sweety1
 
Posts: 539
Joined: Wed Mar 11, 2015 1:14 am

Return to Spire.XLS