Hi Team,
We are using Aspose.Cells (with development language as C#).
We are facing issues while converting excel to pdf ,in which printable area is setup and this printable area spans only one page (while printing the workbook (in hardcopy) we get only one page, as printable area is set for only one worksheet ) .
But when we are converting this workbook using Aspose it gives a pdf having more than one page please note here that excel had printable area which spanned 1sheet in print preview but the pdf generated is having more than one page (only printable area is converted but it spans across more than one page in the pdf.)
Please let us know why this issue is coming .
Thanks,
Ashish Sharma
Hi,
If you still find the issue, kindly provide your template Excel file here, we will check it soon.
Thank you.
Hi ,
I tried with the latest version of dll but still the pdf that is generated is not correct.
For your reference I have attached the xlsx and its generated pdf.
Thanks,
Ashish Sharma
Hi,
Thanks for the template files.
I have tested by simply converting your template file to PDF using Aspose.Cells APIs, I opened the file and saved it as PDF, it works fine. Please see the attached output PDF file which has only 1 page which it should.
Thank you.
Hi ,
I am using the following logic to get the pdf:-
private static void ExcelToPDF(string dataDir, string sampleFileName)
{
License license = new Aspose.Cells.License();
license.SetLicense("Aspose.Total.lic");
<SPAN style="COLOR: #2b91af">Workbook</SPAN> workbook = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">Workbook</SPAN>(dataDir + sampleFileName);
<SPAN style="COLOR: #2b91af">MemoryStream</SPAN> memStream;
<SPAN style="COLOR: blue">using</SPAN> (<SPAN style="COLOR: #2b91af">FileStream</SPAN> fileStream = <SPAN style="COLOR: #2b91af">File</SPAN>.OpenRead(dataDir + sampleFileName))
{
memStream = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">MemoryStream</SPAN>();
memStream.SetLength(fileStream.Length);
fileStream.Read(memStream.GetBuffer(), 0, (<SPAN style="COLOR: blue">int</SPAN>)fileStream.Length);
}
<SPAN style="COLOR: #2b91af">Workbook</SPAN> originalWorkbook = <SPAN style="COLOR: blue">null</SPAN>;
<SPAN style="COLOR: #2b91af">Workbook</SPAN> tempWorkbook = <SPAN style="COLOR: blue">null</SPAN>;
<SPAN style="COLOR: #2b91af">Document</SPAN> finalPdfDocument = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">Document</SPAN>();
tempWorkbook = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">Workbook</SPAN>();
originalWorkbook = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">Workbook</SPAN>(memStream);
tempWorkbook.Worksheets.Clear();
<SPAN style="COLOR: #2b91af">MemoryStream</SPAN> pdfMemoryStream=<SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">MemoryStream</SPAN>();
<SPAN style="COLOR: blue">int</SPAN> workSheetCounter = 0;
<SPAN style="COLOR: blue">foreach</SPAN> (<SPAN style="COLOR: #2b91af">Worksheet</SPAN> worksheet <SPAN style="COLOR: blue">in</SPAN> originalWorkbook.Worksheets)
{
<SPAN style="COLOR: blue">if</SPAN> (workSheetCounter >= 1)
<SPAN style="COLOR: blue">break</SPAN>;
<SPAN style="COLOR: #2b91af">MemoryStream</SPAN> tempPdfMemoryStream = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">MemoryStream</SPAN>();
tempWorkbook = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">Workbook</SPAN>();
Aspose.Cells.<SPAN style="COLOR: #2b91af">PdfSaveOptions</SPAN> saveOptions = <SPAN style="COLOR: blue">new</SPAN> Aspose.Cells.<SPAN style="COLOR: #2b91af">PdfSaveOptions</SPAN>(Aspose.Cells.<SPAN style="COLOR: #2b91af">SaveFormat</SPAN>.Pdf);
tempWorkbook.Worksheets.Add();
<SPAN style="COLOR: blue">int</SPAN> sheetcount = tempWorkbook.Worksheets.Count;
Aspose.Cells.<SPAN style="COLOR: #2b91af">Cells</SPAN> cells = worksheet.Cells;
cells.RemoveFormulas();
tempWorkbook.Worksheets[sheetcount - 1].Copy(worksheet);
tempWorkbook.Save(tempPdfMemoryStream, saveOptions);
<SPAN style="COLOR: #2b91af">Document</SPAN> pdfDocument = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">Document</SPAN>(tempPdfMemoryStream);
<SPAN style="COLOR: blue">if</SPAN> (<SPAN style="COLOR: blue">string</SPAN>.IsNullOrWhiteSpace(worksheet.PageSetup.PrintArea))
{
finalPdfDocument.Pages.Add(pdfDocument.Pages[1]);
}
<SPAN style="COLOR: blue">else</SPAN>
{
finalPdfDocument.Pages.Add(pdfDocument.Pages);
}
workSheetCounter++;
}
<SPAN style="COLOR: blue">if</SPAN> (finalPdfDocument.Pages.Count > 0)
{
finalPdfDocument.Save(pdfMemoryStream);
}
<SPAN style="COLOR: #2b91af">File</SPAN>.WriteAllBytes(dataDir + sampleFileName+ <SPAN style="COLOR: #a31515">".pdf"</SPAN>, pdfMemoryStream.ToArray());
}</PRE><PRE style="FONT-FAMILY: Consolas; BACKGROUND: white; COLOR: black; FONT-SIZE: 13px">If print area is setup then I am geting the print area otherwise i am getting the first page of the pdf.</PRE><PRE style="FONT-FAMILY: Consolas; BACKGROUND: white; COLOR: black; FONT-SIZE: 13px">Can you take a look at the above code as it is not working correctly incase printable area is configured for a particular work sheet</PRE><PRE style="FONT-FAMILY: Consolas; BACKGROUND: white; COLOR: black; FONT-SIZE: 13px">Thanks,</PRE><PRE style="FONT-FAMILY: Consolas; BACKGROUND: white; COLOR: black; FONT-SIZE: 13px">Ashish Sharma</PRE><PRE style="FONT-FAMILY: Consolas; BACKGROUND: white; COLOR: black; FONT-SIZE: 13px"> </PRE>
Hi,
I have tested your scenario/ case a bit. Well, I think you should look for your issue in Aspose.Pdf component as you are also using it, you may post a query with all the details in Aspose.Pdf forums. I could you find the issue on Aspose.Cells end using Aspose.Cells APIs only. I used the following sample code using Aspose.Cells APIs only, it works fine and generated correct PDF file. I have also attached the output PDF file for your reference.
e.g
Sample code:
FileStream fs = File.OpenRead("e:\\test2\\DummySheet.xlsx");
fs.Seek(0, SeekOrigin.Begin);
Workbook originalWorkbook = null;
Workbook tempWorkbook = null;
tempWorkbook = new Workbook();
originalWorkbook = new Workbook(fs);
Worksheet worksheet = originalWorkbook.Worksheets[0];
tempWorkbook.Worksheets.Clear();
MemoryStream tempPdfMemoryStream = new MemoryStream();
Aspose.Cells.PdfSaveOptions saveOptions = new Aspose.Cells.PdfSaveOptions(Aspose.Cells.SaveFormat.Pdf);
Aspose.Cells.Cells cells = worksheet.Cells;
cells.RemoveFormulas();
tempWorkbook.Copy(originalWorkbook);
tempWorkbook.Save(tempPdfMemoryStream, saveOptions);
string filePath = @"e:\test2\out_DummyTest1.pdf";
byte[] bytes = tempPdfMemoryStream.ToArray();
File.WriteAllBytes(filePath, bytes);
If you still think the issue is with Aspose.Cells, please separate the issue and give us sample code (runnable) with Aspose.Cells APIs only to reproduce the issue on our end, we will check it soon.
Thank you.
Hi ,
I can confirm that this is a issue with Aspose.Cells because I have tried converting the excel file using Aspose.Cells and not using Aspose.pdf.
Kindly see the attached working code which only uses Aspose.Cells for conversion.
Thanks,
Ashish Sharma
Hi,
Thanks for the sample project.
I have tested your scenario/ case further. I have evaluated your issue in MS Excel manually. I did test copying the only worksheet from the "DummyTest.xlsx" to another Workbook/ file. When the "test1" sheet is copied to other Workbook, I got the similar worksheet that has 4 pages when taking the print preview of the sheet. So, it is not an issue with the product rather similar behaviour as shown MS Excel when copying worksheets b/w workbooks. I also spotted that some new vertical/horizontal page breaks are inserted in the copied sheet e.g on cell CZ column, on row 647 row etc.
I think you may combine workbooks using Workbook.Copy() method if you need to copy sheets as per my previous code which works fine for the rendered XLSX or PDF file format.
Thank you.
Hi
This behaviour is affecting a lot of users in our environment.
Is there any API available for us so that we can pdf a single worksheet from the workbook without copying the worksheet to new workbook and then pdf the new workbook.
If this is not available can you please plan it in next release.
Thanks,
Ashish Sharma
Hi Ashish,
Thank you for writing back.
If you requirement is to render only one worksheet to PDF format, you may simply hide unwanted worksheets in the spreadsheet, and render the workbook to PDF format. The resultant PDF should only contain the contents from the visible worksheet(s).
C#
var book = new Workbook(“D:/temp/sample.xlsx”);
var worksheets = book.Worksheets;
foreach (Worksheet worksheet in worksheets)
{
if (worksheet.Name != “Any Name”)
{
worksheet.IsVisible = false;
}
}
book.Save(“D:/temp/output.pdf”, new PdfSaveOptions());
In case I have misunderstood your requirement then please elaborate it further.