Column pixel width changes based on monitor resolution?

Aspose.Cells Product Family
1

Hi!

Here’s some simple code to set column width in pixels. I’m setting Column B to 200 pixels and Column C to 700 pixels:

        var workbook = new Workbook();
        var worksheet = workbook.Worksheets[0];
        worksheet.Cells.SetColumnWidthPixel(1, 200);
        worksheet.Cells.SetColumnWidthPixel(2, 700);
        workbook.Save(PATH_HERE);

However, the actual width of column B or C changes based on the resolution/size of the monitor I’m using (no joking!) For example, when I run this code from my laptop, Column B is 156 pixels wide and Column C is 544 pixels wide. However, if I dock my laptop (the dock is connected to 2 external monitors), then everything is fine - Column B is 200px and Column C is 700px.

Is there some global setting that I can change so that I always get the specified number of pixels?

2

@cometrics,

Thanks for your query.

I am afraid that I am not able to reproduce this issue at my end. For further analysis, could you please ensure that Arial font is installed on your system as this is default font when a Workbook is created from scratch using Aspose.Cells? Also please verify that Display setting is set to 100% during the testing. We will analyze the scenario and provide our feedback.

3

HI! I am using the same machine in both tests and Arial is installed. As mentioned, in one test my laptop is docked to 2 external monitors. In the second test, the same laptop is not docked and only the laptop’s monitor is being used.

Can you tell me which specific Display setting you are referring to?

4

@cometrics,

I have tried this scenario, but still not able to observe issue. Regarding display settings, you may please right click on desktop and select “Screen Resolution” and then click on “Make text and other items larger or smaller”. There you may set smaller - 100% (default) setting and provide the feedback.

5

Upon further investigation I’ve found the following:

When the code I pasted is executed within a C# Console EXE and run in the debugger, then the resolution doesn’t matter. It always produces the correct column widths. I ran this test with Aspose.Cells 9.0 and 19.2

When the same code is executed from an xUnit unit test, then the resolution DOES matter. At 125% (on my system Windows notes that as the “recommended” resolution), the problem occurs for both Aspose.Cells 9.0 and 19.2. When I set the resolution to 100%, then there’s no issue for both versions.

Why does the resolution matter? What code in Aspose depends on that? The execution environment (e.g. unit test versus console exe) must be “seeing” the resolution differently, which would explain the difference in outcomes.

6

@cometrics,

The stored column’s width is in unit of characters. The API converts the pixels to Chartacters with default DPI 96. If the DPI is not 96, the viewed pixels will be same as settings.

Hope, this helps.

7

Can I directly set the DPI myself to guarantee 96 regardless of execution harness/OS display resolution?

8

@cometrics,

Setting the DPI is allowed while rendering the worksheet to image as shown in the following sample code:

Workbook book = new Workbook(myDir + "Sample.xlsx");
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
imageOptions.ImageFormat = System.Drawing.Imaging.ImageFormat.Jpeg;
//Set Horizontal Resolution in Dots Per Inch
imageOptions.HorizontalResolution = 300;
//Set Vertical Resolution in Dots Per Inch
imageOptions.VerticalResolution = 300;
SheetRender render = new SheetRender(book.Worksheets[0], imageOptions);
render.ToImage(0, myDir + "output.jpeg");

I am afraid that setting the DPI in workbook is not available. If you find any such feature while working with Excel, please do let us know. We will analyze it and try to provide same feature using Aspose.Cells.

Also please try the function Worksheet.Cells.SetColumnWidthInch() which may fulfill your requirement to ignore the display resolution.

9

Can you add setting DPI in workbook as a feature?

Also, are there any workarounds or other ways to “trick” the code into setting DPI to the default value?

What .net calls do you use to determine the DPI? With that information I can investigate .net framework-level options.

10

@cometrics,

The API uses CellsHelper.DPI to set the DPI, but it can not change the setting of machine. So it is better to change DPI of your machine to avoid the differences.

11

I’m confused. I just tried to set CellsHelper.DPI = 96 and everything worked just fine! Meaning, at 125% resolution, my xUnit test produced the expected column widths (200px and 700px). But from your prior posts, it seems like there was no way to set DPI. Can you explain?

12

@cometrics,

We are sorry for the confusion. You may please disregard the previous posts and use the CellsHelper.DPI to set the desired value. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

13

thanks! I do believe this is a bug in Aspose, but I am happy to have a workaround. When I tell the system I want 700px I expect 700px, not 545px. Anyways, thanks for your help, the DPI solution will work just fine for me.

14

@cometrics,

Good to know that setting the DPI value works for your needs. Well, it is not an issue with the APIs. If the DPI is changed, the default column’s width would be changed too in MS Excel. Aspose.Cells works as MS Excel does. So we suggest you to specify the DPI at the start of your program (even if you are working in 125 dpi display settings or other) when reading the columns’ widths or other attributes:
e.g
Sample code:

.......
CellsHelper.DPI = 96;
//........
// your code goes here.
//.......