SheetRender.ToImage Incorrectly Formats Cells

I'm using Aspose.Cells to create a spreadsheet, then SheetRender.ToImage() to create an image of the sheet which is then inserted into a Word document using Aspose.Words. The image quality is terrible - lines and columns are incorrectly rendered. I checked the .xls and .png (also .jpg) before insertion into the .doc file, so I can completely rule out Aspose.Words.

Here's a screen-shot of the table I'm trying to render, as displayed by Excel: SheetRender_Excel.jpg (attached).

This is the generated .png file: LeBow_GraphicsTmp.png. (Attached).

Here is the .xls (with all other sheets in the workbook removed for confidentiality reasons): 101_Gesamtdaten_14-10-2012_14-35.xls101_Gesamtdaten_14-10-2012_14-35.xls (attached).

Aspose.Cells version information: SheetRender_Version.jpg (attached). Visual Studio 2010, Excel 2007, all software patched to the latest security releases.

This is the code used to generate the .png file:

//
//Define ImageOrPrintOptions
//
Aspose.Cells.Rendering.ImageOrPrintOptions imgOptions = new Aspose.Cells.Rendering.ImageOrPrintOptions();
//
//Specify the image format
//
imgOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Png;
//
//Only one page for the whole sheet would be rendered
//
imgOptions.OnePagePerSheet = true;
imgOptions.Quality = 100;
imgOptions.VerticalResolution = 300;
imgOptions.HorizontalResolution = 300;
//
//Render the sheet with respect to specified image/print options
//
Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender (wSheet, imgOptions);

String sGraphicsTempFile = String.Format("{0}\\{1}_GraphicsTemp.png", m_RepositoryPath, m_sUserName);
//
//Render the image for the sheet
//
sr.ToImage (0, sGraphicsTempFile);

//
//Define ImageOrPrintOptions
//
Aspose.Cells.Rendering.ImageOrPrintOptions imgOptions = new Aspose.Cells.Rendering.ImageOrPrintOptions();
//
//Specify the image format
//
imgOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Png;
//
//Only one page for the whole sheet would be rendered
//
imgOptions.OnePagePerSheet = true;
imgOptions.Quality = 100;
imgOptions.VerticalResolution = 300;
imgOptions.HorizontalResolution = 300;
//
//Render the sheet with respect to specified image/print options
//
Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender (wSheet, imgOptions);

String sGraphicsTempFile = String.Format("{0}\\{1}_GraphicsTemp.png", m_RepositoryPath, m_sUserName);
//
//Render the image for the sheet
//
sr.ToImage (0, sGraphicsTempFile);

Note: the results are just as bad, with or without imgOptions.OnePagePerSheet set.

Hi,

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

We were able to notice the issues in rendering of the image taken from your worksheet using the latest version:
Aspose.Cells
for .NET v7.3.2.3


We have logged this issue in our database. We will look into it and fix the issue. Once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41128.

Please see the test code below. I have also attached the output image for your reference.

C#


string path = @“F:\Shak-Data-RW\Downloads\101_Gesamtdaten_14-10-2012_14-35.xls”;

Workbook workbook = new Workbook(path);


Worksheet worksheet = workbook.Worksheets[0];


//Apply different Image / Print options.

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

options.OnePagePerSheet = true;


SheetRender sr = new SheetRender(worksheet, options);


Bitmap bitmap = sr.ToImage(0);


bitmap.Save(path + “.out.png”, ImageFormat.Png);


Output Image:

Two weeks on - is there any movement here? I've got a $100K project waiting for deployment (and billing!).

This is a show-stopper.

Hi,


We are sorry for your inconvenience caused!

I am afraid your issue is still not fixed. I have asked the concerned developer to provide the latest update or share an eta for it. Once I have an update on it, we will let you know here.

Thank you.

So it's six weeks since this was posted and four weeks since you last replied.

I have recommended and/or implemented ASPOSE in seven high-profile projects due to your amazing customer service when an obvious bug is reported. This may well be the last time I stake my reputation on it.

I've got six figures waiting to be billed on this project because we can't get this fixed.

Any input on this issue?

Hi,

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

It seems like your issue is already fixed. Please download and try the latest version: Aspose.Cells
for .NET v7.3.4.2
and let us know your feedback.

I have tested your issue with this latest version and I see image is rendered fine and text is not cut.

Please see the following code. I have attached the output image generated by it for your reference.

C#


string path = @“F:\Shak-Data-RW\Downloads\101_Gesamtdaten_14-10-2012_14-35.xls”;

Workbook workbook = new Workbook(path);


Worksheet worksheet = workbook.Worksheets[0];


//Apply different Image / Print options.

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

options.OnePagePerSheet = true;


SheetRender sr = new SheetRender(worksheet, options);


Bitmap bitmap = sr.ToImage(0);


bitmap.Save(path + “.out.png”, ImageFormat.Png);


Output Image:


1) Wouldn't it have been useful for you to actually tell me that a fix had been implemented.

2) I would say that there's an improvement, but not yet that the problem can be considered resolved.

Attached is my latest result. Note that letters with descenders (e.g., lower-case g) are still clipped. Also, the trailing column is also clipped.

I'll send this to the client to see if they experience the same effect.

One possible hint: my fonts are set to 125% (see attached).

Hi,

Thanks for your patience, feedback and using Aspose.Cells.

We were able to replicate your issue by changing the dpi settings. Below are the test results which I got by setting the dpi to 120. I have attached the output image and screenshot for your reference.

We have logged this issue replicable after changing dpi settings in our database with the issue id: CELLSNET-41237. We will look into it and fix this issue.

Once the issue is resolved or we have some other update for you, we will let you know asap.

DPI Settings & Output Image:

…so that was only seven months ago. Is there a status update?

Hi,

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

We are afraid, there is no update for you at this moment. However, we have logged your comments in our database. Once, there is some update for you, we will let you know asap.

YOU'RE afraid...? I've recommended your product to a number of customers and staked my reputation on it.

Nothing at all has happened in the seven months since you confirmed the problem. The user still reports the problem, including on systems where the fonts are set at 'normal' (100%). There's clearly an issue here.

Could you please give me an idea of the scheduling on this so that I can communicate it to my customer? They're about to give Aspose the boot and have me reprogram to the MS-Word/MS-Excel object models otherwise.

Hi,

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

Well, it seems your issue is difficult to be resolved. We have logged your comments and requested the development team to provide ETA for this issue. Once, we will have some update for you, we will let you know asap.

Hi,

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

Please try to autofit rows before converting the worksheet into image. If you do not set row height explicitly, MS-Excel will automatically does it.

Please refer to the following code.

C#


foreach (Worksheet ws in wb.Worksheets)

{

ws.AutoFitRows(new AutoFitterOptions() { });

}

I have added the suggested AutoFitRows. SheetRenderError_1.jpg is the sheet as viewed in Excel.

SheetRenderError_2.jpg is the sheet as merged into Word.

Adding an additional AutoFitColumns only makes things worse, see SheetRenderError_3.jpg

The problem remains.

...only by faking it out and adding a (n unnecessary, unwanted) column can I get the full output. Here's the new Excel spreadsheet SheetRenderError_4.jpg) and the new merge output (SheetRenderError_5.jpg).

What does SheetRender use to gauge the width of the final column? Is there a problem due to the left-justified column header and right-justified data?

Hi,

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

We have looked into this issue and found that the suggest solution is not working when dpi is set to 120%. We have logged your feedback and results in our database. We will look into it and provide you a solution asap.

The (now incredibly annoyed) customer asks: "...couldn't we just copy the Excel range into an MS-Word table"?

N.B.: I'm using Aspose Cells and Aspose Words.

Additional notes on this question: the architecture of the application is such, that we're replacing 'keywords' in the document with *something else* and doing so from within an IReplacingCallback.Replacing function. In other words, whatever it is needs to work in that context. The current replace that "works" (though the created image is faulty) looks like this from within the IReplacingCallback.Replacing function:

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

String sGraphicsTempFile = String.Format("{0}\\{1}_GraphicsTemp.png", m_RepositoryPath, m_sUserName);

sr.ToImage (0, sGraphicsTempFile);

m_docBuilder.MoveTo (currentNode);

Aspose.Words.Drawing.Shape shape = m_docBuilder.InsertImage (sGraphicsTempFile);

I hunted down and tried the Excel2Word sample code. In a nutshell, it creates a Words.Tables.Table object and then uses DocumentBuilder.CurrentSection.Body.AppendChild (newTable). But... that seems to confuse the running Document.Range.Replace function... Several keywords further down in the document (in the next section), it then throws an exception due to some uninitialized object.

Thinking the open document builder might be of some help, I tried m_docBuilder.StartTable (), tossed in a set of InsertCell(), Write(), EndRow() then an EndTable() instead of AppendChild.

...unfortunately, the result is the same (though, oddly, it makes it further through the remaining document before bailing).

Basically, all I'm trying to do is the equivalent of a cut (from an Excel table, lightly formatted) and a paste into a Word document at a certain location, replacing a <>.

Life shouldn't have to be so hard...

Bonus question: if the resulting image is large (a large number of lines in the source .xls), the image is of course large.

Here is the code I'm using to insert it into the open .doc file:

sr.ToImage (0, sGraphicsTempFile);

m_docBuilder.MoveTo (currentNode);

Aspose.Words.Drawing.

Shape shape = m_docBuilder.InsertImage (sGraphicsTempFile);

But... if the image is larger than the remaining space on the current page, it simply truncates. Is there an InsertImage overload (or other mechanism) with which the generated image could be auto-scaled to the remaining page space rather than simply truncated?

Hi,

Thanks for your postings and using Aspose.

We have already logged your issue. We cannot help you with Aspose.Words related issue. We will investigate your dpi 120% settings issue and update you with any progress. Once, there is some update for you, we will let you know asap.

Hi,

Please try our latest version/fix: Aspose.Cells for .NET 8.4.2

Your issue "CELLSNET-41237" should be fixed in it.

Let us know your feedback.

Thank you.