StandardWidth is not working properly

[namto11]

Hello!

The title said it, i don’t know what is wrong. I am new to aspose.cells and a noob about excel

Here is my code testing multiple way to set width

byte[] filesData = File.ReadAllBytes(“D:\New folder\ImportFile_2Mem_Valid.xlsx”);
MemoryStream ms = new MemoryStream(filesData);
ms.Position = 0;
using (Aspose.Cells.Workbook workbook1 = new Aspose.Cells.Workbook(ms))
{
workbook1.Worksheets[0].Cells.StandardHeight = 30f;
workbook1.Worksheets[0].Cells.StandardWidth = 15f;
workbook1.Worksheets[0].Cells.SetColumnWidth(0, 50f);
workbook1.Worksheets[0].Cells.SetRowHeight(0, 60f);
var range = workbook1.Worksheets[1].Cells.CreateRange(0,0, workbook1.Worksheets[1].Cells.MaxRow + 1, workbook1.Worksheets[1].Cells.MaxColumn + 1);
range.ColumnWidth = 15f;
workbook1.Worksheets.Add(“new”);
workbook1.Worksheets[“new”].Cells.StandardHeight = 30f;
workbook1.Worksheets[“new”].Cells.StandardWidth = 15f;
workbook1.Worksheets[“new”].Cells.SetColumnWidth(0, 50f);
workbook1.Worksheets[“new”].Cells.SetRowHeight(0, 60f);
ms.Seek(0, SeekOrigin.Begin);
workbook1.Save(“D:\New folder\output6.xlsx”, SaveFormat.Xlsx);
}

I onpen the output file and here is what happening.

• Height is fine for all sheet,
• In worksheet[0] has standardWidth = 14,89 instead of 15, column[0] has width = 14.89 instead of 15.
• In worksheet[1] columns which has data has width = 14.89 instead of 15.
• In worksheet[“new”] has standardWidth = 49,89 instead of 50, column[0] has width = 14.89 instead of 15.

In some other case (different code, different file), it all way to set very near to target width but never exactly it (example: if i set width to 8.43f and then it could be 8.11, 8.22, 8.33).

Im working in aspose.cells 23.6.0, here is the input and output file New folder.zip (28.8 KB)

[simon.zhao]

@namto11
In MS Excel, the column width in the file is in unit of characters, but it in the viewer is in unit of pixels.
So when calculating the column width (in pixels) based on the number of characters in the file, MS Excel will adjust the number of characters based on pixels.
3.png (3.5 KB)
And it shows 50 in my machine.

[namto11]

Hi @simon.zhao
Thank you for your reply. I check the column width of the file in my computer, but it is different with your result. Is it normal? I don’t really understand how it’s work and why you get 50 in your computer. I’m very confused.
image.png (28.3 KB)

[amjad.sahi]

@namto11,

Thanks for the screenshot.

We will look into it and get back to you. By the way, what is the display settings of os on your machine? Is it not 100% scaling and something else?

[amjad.sahi]

@namto11,

Hi @amjad.sahi.

I use 100% scaling.
I test again this morning in the office and it work flawless (I check the file uploaded yesterday, and even run the code again to double check). Note that, yesterday I was sitting in my house and remoting to the computer. So maybe it something when wrong if you remote (I can not see the scaling setting when remote, but i find that every icon is bigger than normal).

Or could scaling setting cause the problem?

Thank you for your feedback. Zoom and resolution settings both have an impact on StandardWidth. If you have any further questions, please feel free to contact us at any time.

[amjad.sahi]

@namto11,

I test in advanced and find something else.

This code is working properly

byte[] filesData = File.ReadAllBytes(“D:\New folder\ImportFile_2Mem_Valid.xlsx”);
MemoryStream ms = new MemoryStream(filesData);
ms.Position = 0;
using (Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(ms))
{
CellsFactory factory = new CellsFactory();
Style fontSizeAndNameStyle = factory.CreateStyle();
fontSizeAndNameStyle.Font.Size = 11;
fontSizeAndNameStyle.Font.Name = “Calibri”;
Style boldStyle = factory.CreateStyle();
boldStyle.Font.Size = 11;
boldStyle.Font.Name = “Calibri”;
boldStyle.Font.IsBold = true;
var styleFlag = new StyleFlag()
{
Font = true,
};
foreach (var sheet in workbook.Worksheets)
{
//Apply style
sheet.Cells.StandardHeight = 15f;
sheet.Cells.StandardWidth = 8.43f;
sheet.Cells.ApplyStyle(fontSizeAndNameStyle, styleFlag);
sheet.Cells.Rows[0].ApplyStyle(boldStyle, styleFlag);
}
ms.Seek(0, SeekOrigin.Begin);
workbook.Save(“D:\New folder\output6.xlsx”, SaveFormat.Xlsx);}

And this is not (Sheet[0] and sheet[3] have standardWidth = 8.29 instead of 8.43).

byte[] filesData = File.ReadAllBytes(“D:\New folder\ImportFile_2Mem_Valid.xlsx”);
MemoryStream ms = new MemoryStream(filesData);
ms.Position = 0;
using (Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(ms))
{
CellsFactory factory = new CellsFactory();
Style fontSizeAndNameStyle = factory.CreateStyle();
fontSizeAndNameStyle.Font.Size = 11;
fontSizeAndNameStyle.Font.Name = “Calibri”;
Style boldStyle = factory.CreateStyle();
boldStyle.Font.Size = 11;
boldStyle.Font.Name = “Calibri”;
boldStyle.Font.IsBold = true;
var styleFlag = new StyleFlag()
{
Font = true,
};
foreach (var sheet in workbook.Worksheets)
{
//Apply style
sheet.Cells.ApplyStyle(fontSizeAndNameStyle, styleFlag);
sheet.Cells.StandardHeight = 15f;
sheet.Cells.StandardWidth = 8.43f;
sheet.Cells.Rows[0].ApplyStyle(boldStyle, styleFlag);
}
ms.Seek(0, SeekOrigin.Begin);
workbook.Save(“D:\New folder\output6.xlsx”, SaveFormat.Xlsx);}

Any reason why apply the standardWidth before the style could make the different?
P/S: Thank @simon.zhao and @amjad.sahi and @John.He for very fast response.

Yes, we noticed this behavior/issue. Please note, usually, StandardWidth and StandardHeight attributes should be set at the start after opening or creating the workbook, so you may set these attributes at the start or before applying styles/formatting.

[namto11]

Im out of questions, thank you guys for the support.
Have a great day!

[amjad.sahi]

@namto11,

You are welcome. Feel free to write us back if you have further queries or issue, we will be happy to assist you soon.

[johnson.shi]

@namto11
And for the issue of different behaviors for worksheet’s standard width, we have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-53659

[amjad.sahi]

@namto11,

This is to inform you that your issue has been resolved. The fix will be included in an upcoming release (Aspose.Cells v23.7) that we plan to publish in this week. You will be notified when the next version is released.

[aspose.notifier]

The issues you have found earlier (filed as CELLSNET-53659) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi