I'm using VS2010 VB.NET and Aspose Version 7.4.0.1.
I'm trying to convert 9 small worksheets to images and then paste the images together in a new worksheet so that when it's complete it looks like a 3 x 3 grid with each grid cell holding one of the small worksheet images. (see attached)
I'm having several issues:
(1) when i convert the worksheets to an image, the columns to the right are missing from the image. I tried using the ImageOrPrintOptions OnlyArea, and also setting the print area on the template but that didnt seem to work.
(2) the images have large margins on the top and left. Can i get elimimate the margins?
(3) I want to resize the row that I put the image(s) in to be the height of the tallest image. Auto-resize doesn't seem to work in this case. Is there a way to set the rowheight of the column containing an image to equal the height of the image? I need to have 3 images across the top, aligned at the top...then under that the next 3, then under that the next 3.
(4) Whats the best way to scale these images without losing quality? Sometimes I may need to shrink them.
I've attached the source code, the excel template and some screen shots of the resulting pdf.
I would greatly appreciate any help you could provide on these questions/issues.
Thanks,
- Steve
Hi Steve,
Thank you for providing your sample files and source code.
I would recommend you to use the latest version of Aspose.Cells for .NET v7.6.0 for your testing, because the latest assemblies resolve the problem 1) and 2) from your post. Please check the below provided source code snippet and attached result for your reference.
VB
'Load input file
Dim book
As New Workbook(myDir & “aspose_test.xlsx”)
'Load Worksheet with input data for image conversion
Dim sheet
As Worksheet = book.Worksheets(1)
'Create an instance of ImageOrPrintOptions and set properties
Dim imageOption
As New ImageOrPrintOptions()
imageOption.ImageFormat = System.Drawing.Imaging.ImageFormat.Bmp
imageOption.OnlyArea =
True 'Create an instance of SheetRender and initialize it with Worksheet and ImageOrPrintOptions
Dim render
As New SheetRender(sheet, imageOption)
'Render to Bitmap
Dim bitmap
As Bitmap = render.ToImage(0)
'Create an instance of MemoryStream
Dim stream
As New MemoryStream()
'Save the bitmap to MemoryStream
bitmap.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp)
'Get Size of bitmap; Size contains image Height and Width,
'Image Height and Width can be later used to set the Row Height and Column Width
Dim size
As Size = bitmap.Size
sheet =
Nothing
'Load Worksheet where you want to put the image
sheet = book.Worksheets(2)
'Set the Height of Row 0 to Height of image
sheet.Cells.SetRowHeightPixel(0, size.Height)
'Set the Width of Column 0 to Width of image
sheet.Cells.SetColumnWidthPixel(0, size.Width)
'Add the image to Worksheet’s Picture Collection
Dim index
As Integer = sheet.Pictures.Add(0, 0, stream)
'In order to resize the image, get the newly inserted image
'Change the HeightScale and WidthScale as per requirement
'Picture picture = sheet.Pictures[index];
'picture.HeightScale = 20; // Any value
'picture.WidthScale = 20; // Any Value
'Save output
book.Save(myDir & “output.xlsx”)
3) You can use the
Worksheet.Cells.SetRowHeightPixel &
Worksheet.Cells.SetColumnWidthPixel methods to set the height and width of a cell according to the height and width of the image. Above code demonstrates the same.
4) In order to scale an image, I would recommend you to use the
Picture.HeightScale &
Picture.WidthScale properties. If you assign the same value to the both properties then the resultant image will shrink or enlarge proportionally.
Please feel free to write back if you have more questions.
Thanks for your help. We previously ran into some backward compatibility issues when attempting to go to a newer version, so I may not be able to update to the latest version any time soon. My biggest issue right now is that when rendering a worksheet to an image, its not picking up all the rows and columns. It seems to be inconsistent as to what its picking up. Do you have a workaround I can use with 7.4 to force it to pick up the full range of my data? If not, can you tell me what criteria it is using to decide the range of data to pick up, maybe I can come up with something. Thanks.
- Steve
Hi Steve,
Thank you for your response.
Please check the attached sample project that uses Aspose.Cells for .NET v7.4.0.1. You will observe from the output file that your current version of Aspose.Cells component is producing desirable results while using the same code snippet provided in my previous reply.
Please note that I have used the input as provided by you in your original post. In case you find the same issue with any of your other files, we would recommend you provide us these problematic samples so we could sort this out for you.