Problem with AllColumnsInOnePagePerSheet option

Aspose.Cells Product Family
1

Hi, i’m using AllColumnsInOnePagePerSheet option in my excel conversion to pdf in a stream, but i have a problem related with this option

Let’s see this code

Aspose.Cells.PdfSaveOptions OptionsC = new Aspose.Cells.PdfSaveOptions();
OptionsC.AllColumnsInOnePagePerSheet = true;

((Aspose.Cells.Workbook)m_oDocument).Save(_oOutputStream, OptionsC);
var fileStream = File.Create(“D:/test/exportPDFaspose.pdf”);
oOutputStream.Seek(0, SeekOrigin.Begin);
oOutputStream.CopyTo(fileStream);
fileStream.Close();

((Aspose.Cells.Workbook)m_oDocument).Save(“D:/test/exportPDFaspose2.pdf”);


First Pdf file saved from stream differs from the second file saved directly from Aspose to file!
The difference is manifested with OptionsC.AllColumnsInOnePagePerSheet = true;
If i comment this line the two files are the same

See attached sample files we are proofing

We need to use OptionsC.AllColumnsInOnePagePerSheet = true; in our solution but we need also to obtain stream from aspose in the same way it saves direcly into file
(we work with streams).
For instance, in our sample files, 2.xls is wrong converted in stream but is right converted in file, so we need the same conversion in stream also.

Please, let me know about this

2

Hi,

Thanks for your posting and using Aspose.Cells.

If you set PdfSaveOptions.AllColumnsInOnePagePerSheet as true and save your Excel file as PDF with this option on the disk, it will generate the same results as you have shown in 2_savetostream.pdf file

Please see the following code. I have attached the output pdf which is same as your 2_savetostream.pdf for your reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\2.xlsx”;


Workbook workbook = new Workbook(filePath);


PdfSaveOptions options = new PdfSaveOptions();

options.AllColumnsInOnePagePerSheet = true;


workbook.Save(filePath + “.out.pdf”, options);
3

Hi, maybe I have not explained:
if convert 1.xlsx or 2.xlsx (reading it from stream when you instantiate the aspose cells object) to pdf direcly on disk obtain a different pdf than converting same files on stream.
Can you reproduce this?
As you can see in the sampl files i provided, if I convert 2.xls on disk and on stream i obtain two different results!
The right pdf result is pdf obtained saving to disk: we need the same result saving it on a stream (and reading xls files again from a stream), we cannot present a final pdf 250px * 1400px, is a non-sense…
Why results are not the same?

It seems the problem exists (or is more visible) when excel files use few columns, if you fill some other (column B, D, F etc…)… in case of, as example, using only A column Pdf resulting from saving to disk will be about 900 px width, instead of saving to stream and obtaining a 250 px width

4

Hi,


Thank you for the clarification.

We are able see the difference in PDF files generated through file path location & memory stream. We have performed these tests against the latest version of Aspose.Cells for .NET 8.1.2.2, and have noticed the difference in page size (as shown in the attached snapshots), when generated PDF files are viewed with Acrobat Reader. We need to further investigate the matter on our end to find a solution for it, therefore the problem has been logged in our bug tracking system under the ticket CELLSNET-42873. Please spare us little time to properly analyze the problem cause, and to provide the fix at earliest possible. In the meanwhile, we will keep you posted with updates in this regard.
5

Thank You
Just for clarity, for us the right page size is page size created when saving direcly on disk (For instance A4 pdf document 210 * 297 mm)

6

Hi,

Thanks for using Aspose.Cells.

If you want to compare difference between saving to disk and saving to
steam, you should use the same pdfSaveOptions (in your code,
saving to stream with a pdfSaveOptions, but saving to disk without it will produce different results )


We said clearly in our documents for the AllColumnsInOnePagePerSheet property:

If AllColumnsInOnePagePerSheet is true , all column content of one sheet will output to only one page in result. The width of paper size of pagesetup will be ignored, and the other settings of pagesetup will still take effect.

If you want the page size should not change and all columns are in one page. Please using the following code:

C#
Workbook wb = new Workbook(srcFile);

foreach (Worksheet ws in wb.Worksheets)
{
ws.PageSetup.FitToPagesWide = 1;
}

wb.Save(".out.pdf");

7

Yes, you’re right.
But the problem is using AllColumnsInOnePagePerSheet in some cases of Excel columns, as noticed by Babar Raza opening CELLSNET-42873 issue.

8

There is another problem related to AllColumnsInOnePagePerSheet = true

We are testing it and we’ll open other post for it.

9

Hi again,


Thank you for writing back.

Unfortunately, the ticket CELLSNET-42873 is currently pending for analysis therefore we may not be able to comment what could be causing the difference in page size when same file is saved to disc and to memory stream. We will keep you posted with updates in this regard.

In case you have stumbled upon another similar situation, we would encourage you to post in a new thread for better visibility and tracking.
10

Hi again,


We have completed the investigation on the ticket CELLSNET-42873. Unfortunately, it is not a bug of Aspose.Cells API, rather a misunderstanding on how to use the Workbook.Save feature. Let me explain it in a little detail. Please execute the below code on your side,

C#

var m_oDocument = new Workbook(myDir + “1.xlsx”);
Aspose.Cells.PdfSaveOptions OptionsC = new Aspose.Cells.PdfSaveOptions();
OptionsC.AllColumnsInOnePagePerSheet = true;
using (var oOutputStream = new System.IO.MemoryStream())
{
((Aspose.Cells.Workbook)m_oDocument).Save(oOutputStream, OptionsC);
var fileStream = System.IO.File.Create(myDir + “stream_saved.pdf”);
oOutputStream.Seek(0, System.IO.SeekOrigin.Begin);
oOutputStream.CopyTo(fileStream);
fileStream.Close();
}
m_oDocument.Save(myDir + “disk_saved.pdf”, OptionsC);


Upon reviewing the document properties, you will notice that both results (saved by disk path location & through the stream) are identical. Whereas, at the time of logging the ticket the results were different because I executed your provided code snippet, that lacks the second parameter (an object of PdfSaveOptions) while saving the PDF directly on disk. If you do not pass the object of PdfSaveOptions while saving the PDF directly on disk, the property AllColumnsInOnePagePerSheet will not take effect therefore the page width would be different. For more details, please review the post made by Shakeel here.

Moreover, the property AllColumnsInOnePagePerSheet forces the rendering engine to render all columns of the worksheet on a single page, while ignoring PageSetup settings for the paper size. So you have to choose between the paper size of your choice or rendering all columns on a single page as both settings cannot be applied simultaneously.

If you have any ambiguity, please feel free to write back.
11

ok We got it, and accomodate using some considerations on column number and width so we use or not AllColumnsInOnePagePerSheet parameter.
Thank’s

12

Hi,


Thank you for your understanding. Please feel free to write back in case you need our further assistance with Aspose.Cells APIs.