Here's the situation:
I have a web application that allows users to import thier own Excel Files via GridWeb and set up formulas labels etc. accordingly. Because this is open ended they could potentially upload any number of columns of any width. I do not have this formatting information in advance and it will be different for different users (important later). Eventually this spreadsheet is converted to a PDF file and merged with another standardized report PDF file.
I give the user an option to use the same header in the spreadsheet PDF that is available on the standardized report. I accomplish this task by saving the header via a 3rd party report engine to a Tiff file and convert that to a Jpeg file. I then open up the spreadsheet via Aspose.Cells, add a new row at the top, specify my margins as 0.0 inches on all sides, and add the header picture to the top left cell with the same height and width as my saved header image.
Which would be great, except that it doesn't work.
My original image, which is 200x196 DPI is converted to 81x81 DPI which changes it's size from 2.52 inches X 8 inches to 6.07 inches X 19.69 inches. Aspose.Cells attempts to scale this new image to the screen and while the height seems okay, my specified 8 inches width does not seem to fit on an 8.5 inch wide page. The image itself is rendered in a slightly distorted fashion. I also can't use the method of "pictures.add" that uses lower right row and column because as I said above, the spreadsheet is dynamically user designed and I do not know how many columns will fit on a page in advance.
I've attached a small project that demonstrates this behavior (you'll need to add your own license file via the security warning that pops up when I attach a file). Suggestions?
Hi,
Thanks for providing us the template project.
Well, I tried your sample code with template file. I think it works same as MS Excel does. When I add your image to a workbook and save the excel file, the image does become 96 DPI with 2.52 inches X 8 which looks to me fine. So, when I convert it to the pdf format, it remains the same.
I used the attached version. Also, attached are the generated xls and pdf files.
Do you get different results? Kindly post your files here. We will check it soon.
Thank you.
Unfortunately, it does not look the same… Attached is a PDF that is generated by the example I gave you. Also attached is a PDF generated by Excel (in my case Open Office, but it comes out the same). Look at both PDF’s at 50% They both render the image at 81 DPI (the conversion I spoke about). They were both set with 0.0 margins all the way around. Notice the difference?
Open both PDF’s. Size them down to 50% and click on the images so that it shows the outline of the image on the PDF.
On the OO/Excel generated page the image fits on the screen.
On the Aspose generated page notice how the image gets cut off? There’s about a quarter inch of it that hangs off the side of the page and continues on the next.
The original image is 8inches. It should fit horizontally on an 8.5 inch page right?
That in a nutshell is the issue.
Hi,
Please try my attached version v4.8.0.15 (in my previous post). Also, use Worksheet.PageSetup attributes to fit the page to one page wide for your need.
e.g
book.Worksheets[0].PageSetup.FitToPagesWide = 1;
For further reference about using pagesetup options, see the document:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-page-options.html
Thank you.
Please try my attached version v4.8.0.15 (in my previous post).
I've tried this and get the same results. The first PDF that I sent to you to compare is actually the same one you sent me. The second one is from Excel.
Also, use Worksheet.PageSetup attributes to fit the page to one page wide for your need.
e.g
book.Worksheets[0].PageSetup.FitToPagesWide = 1;
This would be fine if my users were guaranteed to only use spreadsheets that were 1 page wide (The final pages are going to include much more than this header) and that by doing so the rest of the page(s) would be unaffected. However, this is not the case and therefore is not a viable solution.
I've tried this:
book.Worksheets[0].Pictures[book.Worksheets[0].Pictures.Count - 1].WidthInch = 8;
I would expect that setting to 8 inches would fit on a 8.5 inch wide portrait page??? However to fit it on the PDF I have to change it to 7.5. Again, that would be fine except it skews the image quality and I'm likely to get complaints from my users. I'm not doing anything complicated here. Just trying to add one image to one page and have it look the same as Excel. It would seem to be pretty basic functionality.
Hi,
Well, it is strange. We get the results fine as you may see my attached xls and pdf files in one of my previous posts. Anyways we will further look into your issue and get back to you soon.
By the way, could you give us your environment, e.g OS, .NET framework, MS Excel version detail , other tools, etc.
Thank you.
After some further investigation I think I'm getting much closer to the answer.
1) I made a bad assumption that the page was defauting to Letter size (Aspose.cells actually defaults this to A4) I changed this:
book.Worksheets[0].PageSetup.PaperSize = PaperSizeType.PaperLetter;
And now the picture is a more reasonable size... though not quite correct yet.
2) There is a difference in Excel (in my case open office) in how an image is placed in a cell. When I manually insert a picture the right edge ends about half way through the "L" column. If I place that same picture in the same spot with the Aspose.Cells control. The right edge goes all the way to the right edge of the "L" column. This difference accounts for the rest of the difference in image size. I've attached an example of each Excel file with this post as demonstration. I think internally Aspose.cells is mapping the image uppper left cell(row, column) to Cell lower right cell(row, column) regardless if that variation of the Pictures.add method is called. If the image does not match up to the column size, it gets stretched to fit and thus causes the distortion of the image.
3) Even if I set my margins to 0.0 on all sides I'm still getting a left and top margin. This only happens if I use the command:
book.Save("Preview.pdf", Aspose.Cells.FileFormatType.Pdf, SaveType.OpenInExcel, System.Web.HttpContext.Current.Response);
If however, save from pdf manually from the exact same Excel file. Those margins appear properly. I've included an example of each PDF in this post as well.
So now that I know what's going on I need to:
1) Get rid of the extra margin space
2) Place the image without it stretching out to fit the edge of the cell (ie. keep the original dimensions of the image).
My enviornment is Asp.net 2.0. I'm using C# and Open office 3 to view the results of the control. Adobe Acrobat Reader 9 to open the PDF's
Hi,
Please try the attached version and let us know if it fixes the issue or not?
Thank you.
We're getting closer!
So now that I know what's going on I need to:
1) Get rid of the extra margin space
This is fixed. Thank you!
2) Place the image without it stretching out to fit the edge of the cell (ie. keep the original dimensions of the image).
This is still acting the same way. If you look at the Excel spreadsheet that Aspose saves. It stretches the image to fit the far edge of the cell (as described in my previous post).
Hi,
Thank you for considering Aspose.
Good to know that your first issue is resolved.
Well, for your second issue, I tried with both MS Excel 2003 & MS Excel 2007 and both operates the same way as Aspose (They stretches the image to fit the far edge of the cell). Which version of Excel are you using?
Thank You & Best Regards,
Ah... you are correct on that one. The difference is that I'm looking at it through open office 3. So another piece of the puzzle solved. 8 inches on Excel just happens to stretch to exactly the end of the "L" column and on Open office it's about half way through that column when I submit the image manually.
What I'm not understanding is that if I use these settings on a letter sized page (8.5x11.0 inches) This in the same project I submitted on my first post:
book.Worksheets[0].PageSetup.PaperSize = PaperSizeType.PaperLetter;
book.Worksheets[0].PageSetup.LeftMarginInch = 0.25; <= This stopped working after the last DLL update. The margin stays at zero regardless of the number entered here. You may want to revisit this.
book.Worksheets[0].PageSetup.RightMarginInch = 0.25;
I would expect to have my 8 inch image centered on the page with .25 inch margins on each side. (This is my goal). But the image is still larger than 8 inches. Even more oddly, if I change the image to 7.65x2.51 (as open office seems to suggest when I manually place the image) it seems to fit as an 8 inch image on the PDF. So maybe it's an Excel quirk? In any case, this change seems to make it "good enough" for my purposes so at least for now I'll drop the issue. Oh, and you'll want to look at the left margin again. It seems to be stuck at zero.
Thank you! You have been very helpful.
Hi,
Thank you for considering Aspose.
I tested the LeftMargin issue with the attached latest version and it works fine. Please try the attached version and check if it works fine for you.
Thank You & Best Regards,