SheetRender crops cells even when PrintArea is setup

ndsykes · July 31, 2012, 6:41am

I am testing the latest version of Aspose Cells to try to solve a problem we are seeing with an earlier version (where rendered worksheets don’t have the charts established before they are rendered, resulting in blank charts on the image).

In the new version, I find that my images are being cropped - i.e. they are not displaying the full selection set in the PrintArea. Here’s my test code:

Workbook book = new Workbook(“Test.xls”);

Worksheet sheet = book.Worksheets[0];

sheet.PageSetup.PrintArea = “A1:H31”;

sheet.PageSetup.LeftMargin = 0;

sheet.PageSetup.RightMargin = 0;

sheet.PageSetup.BottomMargin = 0;

sheet.PageSetup.TopMargin = 0;

Aspose.Cells.Rendering.ImageOrPrintOptions imgOptions = new Aspose.Cells.Rendering.ImageOrPrintOptions();

imgOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Jpeg;

imgOptions.OnePagePerSheet = true;

imgOptions.PrintingPage = PrintingPageType.IgnoreBlank;

Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, imgOptions);

sr.ToImage(0, “Test.jpg”);

What;s really weird is that the cropped image doesn’t even align with actual column boundaries in the source spreadsheet (in my case, it is behaving as if the Print Area were set to “A1:F/G25” - where “F/G” means the boundary is somewhere in the middle of Column G!).

What am I doing wrong? If I can’t use the old version, and I can’t use the new version, I’m pretty stuck!

I have attached my VS2008 test solution.

Thanks,

Nigel

amjad.sahi · July 31, 2012, 7:01am

Hi Nigel,

Thanks for sharing the project with template files.

I have tested your code with your template file using our latest version/fix v7.3.0.2: Aspose.Cells for .NET v7.3.0.2 it works fine. I have attached the output image here for your reference.

Sample code:

//Open the template file

Workbook book = new Workbook(@“E:\test2\TestExcelToJpeg\TestExcelToJpeg\bin\Debug\Test.xls”);

//Get the first worksheet

Worksheet sheet = book.Worksheets[0];

//Specify your print area if you want

sheet.PageSetup.PrintArea = “A1:H31”;

//To remove the white border around the image.

sheet.PageSetup.LeftMargin = 0;

sheet.PageSetup.RightMargin = 0;

sheet.PageSetup.BottomMargin = 0;

sheet.PageSetup.TopMargin = 0;

//Define ImageOrPrintOptions

Aspose.Cells.Rendering.ImageOrPrintOptions imgOptions = new Aspose.Cells.Rendering.ImageOrPrintOptions();

imgOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Jpeg;

//Set only one page would be rendered for the image

imgOptions.OnePagePerSheet = true;

imgOptions.PrintingPage = PrintingPageType.IgnoreBlank;

//Create the SheetRender object based on the sheet with its

//ImageOrPrintOptions attributes

Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, imgOptions);

//Convert the image

sr.ToImage(0, @“E:\test2\TestExcelToJpeg\TestExcelToJpeg\bin\Debug\outTest.jpg”);

Please try our latest version/fix v7.3.0.2.

Thank you.

shakeel.faiz · July 31, 2012, 7:10am

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please use the following code, it generates the image fine. I have also attached the output image for your reference.

Please download and use the latest version:
Aspose.Cells
for .NET v7.3.0.2

C#

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

//Instantiating a Workbook object

Workbook workbook = new Workbook(filePath);

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.Worksheets[0];

worksheet.PageSetup.PrintArea = “A1:H31”;

worksheet.PageSetup.TopMargin = 0;

worksheet.PageSetup.BottomMargin = 0;

worksheet.PageSetup.LeftMargin = 0;

worksheet.PageSetup.RightMargin = 0;

ImageOrPrintOptions opts = new ImageOrPrintOptions();

opts.OnePagePerSheet = true;

SheetRender sr = new SheetRender(worksheet, opts);

Bitmap bmp = sr.ToImage(0);

bmp.Save(filePath + “.out.jpg”);

Output Image:

ndsykes · July 31, 2012, 7:39am

Thanks for the prompt replies.

I have downloaded the latest version (I only downloaded the last one yesterday!), but my test app still produces the same, cropped result.

I then tried making the few minor modifications suggested by Shakeel, but the result was still the same.

Interestingly, and perhaps significant(?) is, if I try to open the resulting JPG file in Adobe PhotoShop (CS5 Extended), it refuses, saying:

"Could not complete your request because an unknown or invalid JPEG marker type is found"

Any ideas why it would be doing this on my PC and not yours then?

Thanks,

Nigel

shakeel.faiz · July 31, 2012, 7:46am

Hi,

Thanks for your feedback.

It’s strange that this issue is occurring on your machine and not ours. Please let us know your System Environment.

Are you using .NET Framework Client Profile? Because there is a separate Aspose.Cells dll which can work with .NET Framework Client Profile.

Also, please make sure, there is not any other Aspose.Cells dll in your GAC (Global Assembly Cache) and you have updated the reference to latest dll and using it.

ndsykes · July 31, 2012, 8:48am

Thanks Shakeel,

I am using:

Windows 7 Ultimate 64-bit, SP1

Visual Studio 2008 Ver 9

(I have also tried it unsucessfully on VS2010 Ver 10 SP1)

.NET Framework 3.5 SP1

I have now gone through and diligently overwritten all ocurrences of older Aspose.Cells.dll files on my entire PC with the newest one you sent me this afternoon. I have also double-checked that the References correctly point to this new version (they already did).

So… the result is still the same, I’m afraid.

Any more ideas?

Thanks,

Nigel

amjad.sahi · July 31, 2012, 12:45pm

Hi,

I also use almost the similar environment. Please create a separate sample console application using the latest version/fix: Aspose.Cells for .NET v7.3.0.2 with your template file, zip the project and attach it here, we will evaluate and run it on our environment and share the results.

Thank you.

ndsykes · August 1, 2012, 2:56am

Hi Amjad,

I can’t create this as a console application because it doesn’t recognise System.Drawing, so cannot handle the Imaging or Bitmap objects and I don’t know how to get around that.

Can you suggest the code I need to implement these?

Thanks,

Nigel

amjad.sahi · August 1, 2012, 3:17am

Hi Nigel,

Well, you need to Add Reference to System.Drawing namespece first (in Solution Explorer, right click on the References, then click “Add Reference”. In the Add Reference dialog box, select System.Drawing in .NET tab and click OK button).

Here is the complete sample code of my sample console application:

Sample code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Aspose.Cells;

using Aspose.Cells.Rendering;

using System.Drawing;

namespace ConsoleApplication3draing

{

class Program

{

static void Main(string[] args)

{

string filePath = @“e:\test\test.xls”;

//Instantiating a Workbook object

Workbook workbook = new Workbook(filePath);

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.Worksheets[0];

worksheet.PageSetup.PrintArea = “A1:H31”;

worksheet.PageSetup.TopMargin = 0;

worksheet.PageSetup.BottomMargin = 0;

worksheet.PageSetup.LeftMargin = 0;

worksheet.PageSetup.RightMargin = 0;

ImageOrPrintOptions opts = new ImageOrPrintOptions();

opts.OnePagePerSheet = true;

SheetRender sr = new SheetRender(worksheet, opts);

Bitmap bmp = sr.ToImage(0);

bmp.Save(filePath + “.out.jpg”);

}

Hope, it helps you.

ndsykes · August 1, 2012, 4:32am

Hi Amjad,

He - sorry. Was having a brain shut-down when I posted that. However… I’ve used your code (which was the same as mine anyway, except for the filenames and locations) and I still have the same issue.

I have attached my new sample app. The only difference with your code is that I’m storing the files in “C:\Temp” (I don’t have an “E” drive). Also, I have removed the Aspose.Cells.dll and xml files from the bin\Debug directory to save on bandwidth.

Regards,

Nigel

amjad.sahi · August 1, 2012, 5:12am

Hi,

I just unzipped your archive to a folder and then opened the project into VS.NET 2008, now updated the filePath variable’s value accordingly. Added reference to v7.3.0.2 and run the project, it works absolutely fine, you may check the output image in the archive. I have zipped your updated project and attach the archive here for your reference.

It looks strange that you are not getting fine results. Please just unzip my attached archive and open the project into VS.NET, update the filePath variable’s value and just run it, it should work fine to produce the image same as mine.

Thank you.

ndsykes · August 1, 2012, 2:51pm

Hi Amjad,

I’m afraid that the result remains the same for me - i.e. cropped weirdly.

All I did was open the project you sent, change the path to point to the Test.xls on my PC and ran it.

What could possibly be causing this to be different between our PCs?

Nigel

shakeel.faiz · August 1, 2012, 10:51pm

ndsykes:
Hi Amjad,

I'm afraid that the result remains the same for me - i.e. cropped weirdly.

All I did was open the project you sent, change the path to point to the Test.xls on my PC and ran it.

What could possibly be causing this to be different between our PCs?

Nigel

Hi,

I have checked your projects and I found no problem. All seems ok to me.

Could you please download the client profile dll from the major release: Aspose.Cells for .NET 7.3.0

Once, you will extract the msi file, you will find 3 directories inside Bin directory namely

net1.1
net2.0
net3.5_ClientProfile (try this one)

Update your project i.e ExcelToJpeg2.zip dll reference to one as present inside the net3.5_ClientProfile folder and check the output.

Let us know your feedback.

ndsykes · August 2, 2012, 2:59am

Hi Shakeel,

I already tried that version of the DLL - that was the one I was trialling when I started to see this problem. I only switched to the most recent one when instructed by yourselves.

Maybe it’s worth noting that even the ‘correct’ JPG that you are creating won’t open in Photoshop on my PC (see previous comment).

Regards,

Nigel

ndsykes · August 7, 2012, 7:58am

Hi guys,

Any ideas about what I can do with this, or have we reached an impasse?

Maybe we can solve my problems by looking instead at the original one that caused me to try this new version? To whit:

I found (and this is by no means particular to my PC this time), that if I used Aspose Cells to open an existing Excel Workbook which had data to update within it, then saved a selection to JPG, the charts weren’t being updated before the JPGs were created - resulting in blank charts on the bitmaps.

So, I need to know how to guarantee that all auto-operations (e.g. Macros) have completed on the workbook before making the image copies - some sort of ‘Refresh’ etc.

Having tried the newest version of Aspose Cells, this problem seems to have gone away, so that would have solved my problems, but then I ran into the weird cropping problem above.

Help!

Thanks,

Nigel

shakeel.faiz · August 7, 2012, 10:39am

Hi,

In order to diagnose this issue further, could you please try the following code on number of other xls/xlsx files and share your results?

Please post all your source files and their output images. It will help us see if the problem is related to single file or it occurs with all files.

Please also download and use the latest version:
Aspose.Cells
for .NET v7.3.0.3

C#

//Try number of xls/xlsx files by changing this path

string filePath = @“F:\shapes.xls”;

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

ImageOrPrintOptions opts = new ImageOrPrintOptions();

opts.OnePagePerSheet = true;

opts.ImageFormat = ImageFormat.Jpeg;

SheetRender render = new SheetRender(worksheet, opts);

render.ToImage(0, filePath + “.out.jpg”);

ndsykes · August 8, 2012, 3:34am

Hi Shakeel,

Just to check - the link to the “latest version” that you included above is titles “v7.3.0.3”, but the version it links to is actually v7.3.0.2 (which is the same one I already downloaded before. Please confirm if I should actually be using v7.3.0.3, and if so provide me a link to that instead.

Thanks,

Nigel

ndsykes · August 8, 2012, 3:51am

Hi Shakeel,

FYI - I tried your latest, stripped-down code suggestion (with v7.3.0.2) multiple Excel workbooks and the results were the same - the right-hand and bottom edges of the resulting JPEG images were being cropped.

One other question: I am using Excel 2010 (version 14.0.6112.500 - 32-bit). Could this have any bearing on the differences we are seeing between my PC and yours?

Nigel

shakeel.faiz · August 8, 2012, 4:57am

Hi,

Aspose.Cells for .NET does not depend upon MS-Excel at all. It generates images of your workbook without the need of MS-Excel.

The cropping issue did exist in the older versions but it does not exist in the latest version, so it is difficult to tell what sort of problem and why you are facing it.

All I can think is that you are accidentally using some older version.

In order to check it, could you please run this code, if the following code is runnable at your end, then it means you are surely using the latest version because these two new properties have been introduced just recently and does not exist in older versions.

Please download and use the latest fix:
Aspose.Cells
for .NET v7.3.0.3
Now the link is correct. Thanks.

C#

HtmlSaveOptions opts = new HtmlSaveOptions();

opts.HiddenRowDisplayType = HtmlHiddenRowDisplayType.Remove;

opts.HiddenColDisplayType = HtmlHiddenColDisplayType.Remove;

Debug.WriteLine(opts.HiddenColDisplayType);

Debug.WriteLine(opts.HiddenRowDisplayType);

ndsykes · August 8, 2012, 5:28am

Hi Shakeel,

I figured that was the case with Excel, but thought it worth mentioning just in case (since we son’t seem to be finding any other reasons!).

I downloaded v7.3.0.3 and have used that, together with your new code segment. The following results are displayed in the Debug Window:

Remove

…so I guess this proves that I am definitely using the correct DLL?

The cropping problem persists, however…

Regards,

Nigel