SetColumnWidthInch issue

Aspose.Cells Product Family
,
1

Hi,
see attached sample: it loads a template with a named style and writes some cell values. The column width is set using “SetColumnWidthInch”, and I convert a millimeter value to inch.
The result is saved to “bin\Debug\result.xlsx” - also contained in zip.
ColWidth.zip (32.5 KB)

Column B shall have a width of 35mm (1.378 inch)

Now I check the original column width: in the “Normal” view column B has a width of 21.17. In the “Page layout” view, column B has a width of 3,18cm. This is a bit away from the 3.5 cm specified in the code:
colwidth_original.png (26.8 KB)

So, in the “Page Layout” I modify the col width to “3.5 cm”. Excel rounds this value to “3.49cm”. The col width in “Normal” view is now “23.5”:
colwidth_modified.png (25.6 KB)
This is the col width that I expect as a result of my sample code.

Is this an excel issue?

Best regards

Wolfgang

2

@wknauf,

It looks like an MS Excel’s behavior and nothing to do with Aspose.Cells APIs. I tried to manually extend widths of a few columns in Excel manually and found the same thing as you mentioned.

Could you perform the task in MS Excel manually and check if you find the same thing or not and do let us know.

3

@wknauf

The column width is 132 pixel in “Normal” view. In 100% DPI setting of your machine, 3.5 cm = 3.5 /2.54 * 96 pixel = 132 pixel , it is right. See ColumnWidth_NormalView.png (71.5 KB)

The “Page layout” view is something like the printview.(In Microsoft Excel, File->Print). It has some scale in horizontal and vertical direction.

4

Well, I am not convinced that it is only an excel issue.
Attached is a “real” excel file. Unfortunately you would need some customers fonts, but I hope you see the issue anyway:
When creating the report, I set the width of col B to “3.5cm” and col C to “2.8cm”.
But in the “Page Layout” view, the widths are 3.11 cm and 2.49 cm. This is a difference of 10%.
Wochenplan (Excel)_2021-03-11_10-17-29.zip (9.3 KB)

When printing the file, the path widths of the printed paper match the “wrong” widths of the page layout view.

I expect a width of 3.5cm (or a value quite near) when I set the inch width of the column. The 35mm are 1.37795 inch in my code, which matches your value.

Best regards

Wolfgang

5

@wknauf

It seems that you want to set column width in “Page layout” view. Please first set Sheet.ViewType to ViewType.PageLayoutView, then use the API Cells.SetViewColumnWidthPixel(int column, int pixels) to set column witdth in “Page layout” view.
Code:

Workbook workbook = new Workbook("..\\..\\template_Calibri.xlsx");

Worksheet sheet = workbook.Worksheets[0];
//first set sheet ViewType to PageLayoutView
sheet.ViewType = ViewType.PageLayoutView;

...

//column B
sheet.Cells.SetViewColumnWidthPixel(1, (int)(35.0 / 25.4 * 96));
//column C
sheet.Cells.SetViewColumnWidthPixel(2, (int)(28.0 / 25.4 * 96));

workbook.Save("result.xlsx");
6

Hi,

I can confirm that my column widths seem to be correct if I do these steps:

sheet.ViewType = ViewType.PageLayoutView;
sheet.Cells.SetViewColumnWidthPixel(2, (int)(28.0 / 25.4 * 96));
sheet.ViewType = ViewType.NormalView;

Can you confirm that the column will now have the correct width when printing? This is my final goal - setting a column width so that it will have the correct mm width when printing it.

Do you have any explanation why this works different than “SetColumnWidthInch”?

Unfortunately, the API doc for “SetViewColumnWidthPixel” ( https://reference.aspose.com/cells/net/aspose.cells/cells/methods/setviewcolumnwidthpixel) and the release notes https://docs.aspose.com/cells/net/aspose-cells-for-net-19-4-release-notes/#adds-cellssetviewcolumnwidthpixel-method don’t explain this method.

Best regards

Wolfgang

7

@wknauf,
We are analyzing this information and will share our feedback soon.

8

@wknauf

Can you confirm that the column will now have the correct width when printing? This is my final goal - setting a column width so that it will have the correct mm width when printing it.

Yes, sheet.ViewType = ViewType.PageLayoutView; sheet.Cells.SetViewColumnWidthPixel(2, (int)(28.0 / 25.4 * 96)); sets column width in ‘Page layour’ view.

Do you have any explanation why this works different than “SetColumnWidthInch”?

The Cells.SetColumnWidthInch API is not aware of Sheet ViewType, it always set column width in ‘Normal’ view.

Unfortunately, the API doc for “SetViewColumnWidthPixel” ( Cells.SetViewColumnWidthPixel | Aspose.Cells for .NET API Reference) and the release notes Aspose.Cells for .NET 19.4 Release Notes|Documentation don’t explain this method.

We will give more details in api reference/docs of this API.

9

Hi,

sorry, I still don’t understand why “SetColumnWidthInch” does not work as expected. Could you please provide an explanation?
To me, it still seems to be a bug - so please convince me that I am wrong ;-).

It seems “SetColumnWidthInch” is quite useless, because it will never have the expected result.

In my sample, when calling “sheet.Cells.SetColumnWidth(1, 23.5);”, the resulting col width in excel is as expected, but calls to “GetColumnWidthInch” return wrong values. So I think that you do the conversion wrong?
Even when using an empty Workbook instead of a template file, the results are wrong.

Best regards

Wolfgang

10

@wknauf

I still don’t understand why “SetColumnWidthInch” does not work as expected. Could you please provide an explanation?

I have explained:

The Cells.SetColumnWidthInch API is not aware of Sheet ViewType , it always set column width in ‘Normal’ view.

In my sample, when calling “sheet.Cells.SetColumnWidth(1, 23.5);”, the resulting col width in excel is as expected, but calls to “GetColumnWidthInch” return wrong values. So I think that you do the conversion wrong?

Cells.SetColumnWidthInch and Cells.SetColumnWidth are different. The value set to Cells.SetColumnWidthInch is in inch. But the value set to Cells.SetColumnWidth is in numbers of charactors.

I tried the following code, and check the column width in “Normal” view in Microsoft Exce, it is OK.

Workbook wb = new Workbook();
Cells cells = wb.Worksheets[0].Cells;

cells.SetColumnWidthInch(0, 2);
Console.WriteLine(cells.GetColumnWidthInch(0));

cells.SetColumnWidth(1, 23.5);
Console.WriteLine(cells.GetColumnWidth(1));

wb.Save("output.xlsx");
11

I still don’t understand why Excel has this strange behavior. But I give up here and I will change our code to use “SetViewColumnWidthPixel” - this should set the exact columns widths for printing.

12

@wknauf,

Yes, using the API Cells.SetViewColumnWidthPixel(int column, int pixels) will set column width in “Page layout” view and it will give you correct width when printing.

In the event of further queries or issue, feel free to write us back.