Free Support Forum - aspose.com

Aspose rendered images has larger gaps between columns compared to manual copy&paste

Hi,

When I converted an Excel sheet to image I noticed the gaps between columns were much larger than when I copy&paste from Excel manually.

Code:

var workbook = new Workbook(@".\test.xlsx");
var ws = workbook.Worksheets[0];
ws.PageSetup.LeftMargin =
ws.PageSetup.RightMargin = ws.PageSetup.TopMargin = ws.PageSetup.BottomMargin = ws.PageSetup.HeaderMargin = ws.PageSetup.FooterMargin = 0;
var renderOptions = new ImageOrPrintOptions { ImageType = ImageType.Emf, TextRenderingHint = TextRenderingHint.ClearTypeGridFit, OnePagePerSheet = true, OnlyArea = false }; // equivalent to Office Copy as picture - as shown when printed
ws.PageSetup.PrintArea = “B5:H5”;
new SheetRender(ws, renderOptions).ToImage(0, @".\out.emf");

Also when I tried to auto-fit columns 4 using:

ws.AutoFitColumn(4)

it doesn’t seem to return the same width as when I did the auto-fit on column 4 in Excel.

I observed the issue happens when the ‘normal style’ is using font ‘Arial’ with size 13 (it appeared to be fine with other font/size like Calibri size 11). I’ve attached my test files for your reference. Could you please advise?

test.zip (22.6 KB)
image.png (11.2 KB)

Thanks,

@ServerSide527,
This issue is observed here and logged in our database for further investigation and a fix (if possible). You will be notified here once any update is ready to share.

This issue is logged as:
CELLSNET-47522 - Aspose rendered images has larger gaps between columns compared to manual copy & paste

@ServerSide527,

Please try our latest version/fix: Aspose.Cells for .NET v20.7.5:
Aspose.Cells20.7.5 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.7.5 For .Net4.0.Zip (5.4 MB)

Your issue should be fixed in it. Please note, the output of Microsoft Excel’s “Copy as picture -> As Shown When Printed” on our side(see attachment: “office copy as picture from Aspose side.docx”) is different with the one you shared
office copy as picture from Aspose side.zip (10.4 KB)

Let us know your feedback.

Hi @Amjad_Sahi thanks for the quick update.

I have tried 20.7.5 and noticed the new output indeed has less gap and is identical to the output you provided in your attachment.

As you already noticed that your copy&paste output is different than mine (still with a slightly larger gap), that results in the new Aspose output is still slightly different than the copy&paste result on my side.

Could you check the font used in ‘Normal’ style and see if yours is the same with mine?
image.png (34.2 KB)

Basically that is one of the factors that will affect the columns gap of “Copy as picture -> As Shown When Printed” (https://support.microsoft.com/en-gb/help/214394/the-column-width-is-not-the-same-when-printed-in-excel)

Could you check on your side and see if you are seeing the same font, and if your Normal style is also using ‘Arial 12’, the Aspose output is still the same as Excel?

Thanks

@ServerSide527,

Thanks for your feedback.

We will evaluate your concerns and get back to you soon.

@ServerSide527,

The font used in “Normal” style is also “Arial, 12”.(see attachment: “Screenshot_NormalStyle.png”). The copy/paste image in our shared file “office copy as picture from Aspose side.docx” is copied/pasted in this case.

Please check whether the DPI setting of your machine/environment is 100% or not?
Also, please share the default editing language of your Excel. The default editing language of our Excel is “English(United States)”(see attachment: “Screenshot_DefaultEditingLanguage.png”).

The Version of Microsoft Excel on our env. is MS Excel 2016.
Screenshot_NormalStyle.png (45.7 KB)
Screenshot_DefaultEditingLanguage.png (72.5 KB)

Thanks for your updates.

I’m using Windows 10, English (US) setting on the Excel application, my Office version is Office 365 (16.0.12527.20612) 32 bit, DPI 96 (I also always used CellsHelper.DPI = 96 to force the DPI to make my rendered result consistent on different screen, are you also using 96 DPI?).

However, when I tried with more versions (all with English(US), 96 DPI), it seemed it might be related to versions. Here are the versions I tried:

Office 2013: Your result (larger gap)
Office 2016 16.0.4549.1000 : Your result
Office 2016 16.0.5017.1000 : Your result
Office 365 16.0.11929.20836 : Your result
Office 365 16.0.12527.20612: My result (smaller gap)
Office 365 16.0.13001.20338: My result

It seems that it is related to Office versions, maybe could you try with the latest Office (same or later than the versions producing my result) and see if it is also the case?

Also, not sure if it is related, as I mentioned in my initial comment, I found some difference on the column width after I using auto-fit, when I ran

ws.AutoFitColumn(4);
double width = ws.Column(4);
the value of width is 12.
but when I saved the workbook by running
workbook.Save(@".\autofittedcolumnE.xlsx");
the value of width is 11.78. (file attached below)
but if I run autofit on column E in Excel (under 100% zoom), it became 11.33 (file attached below)

This behaviour appeared to be unrelated to which Office I used.

Attached the different Excel files for your reference, maybe the difference on the width would also affect the final image outputs.
test (2).zip (23.1 KB)

In any case, I think it would be nice to know:

  1. Which Office version Aspose currently mimics, and will Aspose mimic the latest versions. (if the copy&paste difference was indeed from Office versions)
  2. Why Aspose’s auto-fit column produce a different on debug/saved worksheet, and both results are different from running auto-fit column in Excel.

Thanks again for your investigation and prompt response.

UPDATE:

I finally figured out what is going on - it is not related to Office versions, it is related to the ‘Printer’ options in the local machine. I could only produce my outputs (with smaller gaps) with printer selected as my home printer (EPSON XP-6100), once I switched to other printers (e.g. the virtual printer ‘Microsoft Print to PDF’), I could produce the same output, regardless which version I used.

Therefore I think the copy&paste vs rendering not an issue after the new fix for now, as Aspose is now producing the same gap as the standard virtual printer.

I still do have the question regarding the auto-fit though as it doesn’t seem to be related to versions or printers, but I think that can be a separate issue. Would appreciate if you could also help on that.

Thanks for your time.

@ServerSide527,

It is good to know that you have figured out your issue now and confirmed Aspose is producing the same gap as the standard virtual printer.

Regarding your issue with auto-fit operation (for cols/rows), yes, there might be little difference. In the past, some users demanded auto-fit cols/rows operation should extend column/row a bit more than what Ms Excel does while others asked in opposite way. So, we did implement the feature using middle way where both types of users are ok with it. Anyways, we will evaluate it further though.

@ServerSide527,
Please try our latest version/fix: Aspose.Cells for .NET v20.7.6 (attached)

Let us know your feedback.
Aspose.Cells20.7.6 For .Net2_AuthenticodeSigned.Zip (5.4 MB)
Aspose.Cells20.7.6 For .Net4.0.Zip (5.4 MB)