Column width is not adapted when format is changed

Aspose.Cells Product Family
1

Hi,


In Excel, if you have a column with the default width set and you set a format that will make the text larger than the default width, Excel will resize that column to fit the new contents; Aspose, however, does not.

To illustrate in Excel:

* Create a new Worksheet and paste ‘41250’ into A1.
* Set the format to Short Date
* You will see the column width was automatically increased to accommodate the longer text.

In Aspose this does not happen and the column ends up being all '####'s.

var workbook = new Workbook();
Worksheet ws = workbook.Worksheets[0];

var cell = ws.Cells[“A1”];

cell.PutValue(41250);
cell.SetStyle(new Style { Custom = “dd/mm/yyyy” });

workbook.Save(“WidthSize.xlsx”);

Best regards,
2

Hi John,

Thanks for your posting and using Aspose.Cells.

Please use AutofitColumn(s) method to autofit column(s) after you entered the data.

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

C#


var workbook = new Workbook();

Worksheet ws = workbook.Worksheets[0];


var cell = ws.Cells[“A1”];


cell.PutValue(41250);

cell.SetStyle(new Style { Custom = “dd/mm/yyyy” });


ws.AutoFitColumns();

workbook.Save(“WidthSize.xlsx”);



3

Hi,


Well, we may not extend the column width to accommodate the formatted contents due to performance grounds. I think MS Excel auto-fit the column width based on the formatted value. You can simply add a line of code to your code segment, it will work similar to Ms Excel:
e.g
Sample code:

var workbook = new Workbook();
Worksheet ws = workbook.Worksheets[0];

var cell = ws.Cells[“A1”];

cell.PutValue(41250);
cell.SetStyle(new Style { Custom = “dd/mm/yyyy” });

ws.AutoFitColumn(0);

workbook.Save(“WidthSize.xlsx”);

Thank you.
4

Hi,


Thanks for your quick reply.

Your solution works except in the case where I want to only have the row that has actually been formatted properly to take on the AutoFit:

firstCell.PutValue(“aasdasdasdasdasdasdasdasdasd”);
secondCell.PutValue(41250);

firstCell.SetStyle(new Style { Custom = “dd/mm/yyyy” });
secondCell.SetStyle(new Style { Custom = “dd/mm/yyyy” });

ws.AutoFitColumn(0);

In that case the width will be taken from the firstCell, even though Excel behaves differently (as the firstCell’s text does not adopt the date format and automatically expands to the neighbours columns).

using:
ws.AutoFitColumn(firstCell.Column, 1, 200);

works, but I would need to know which row to start from (which could change).

Is there a way to automatically determine the correct row for the AutoFitColumn? As in, find out if a cell’s custom formatting was applied/the width would be impacted?

Thanks.
5

Hi,


I am afraid, there is no such APIs that automatically determine and extend the column width based on the formatted value a bit. I am afraid, you have to adopt some workaround to cope with it, see the sample code that may help you to accomplish your task.
e.g
Sample code:

var workbook = new Workbook();
Worksheet ws = workbook.Worksheets[0];

var firstCell = ws.Cells[“A1”];
var secondCell = ws.Cells[“A2”];
firstCell.PutValue(“aasdasdasdasdasdasdasdasdasd”);
secondCell.PutValue(41250);

firstCell.SetStyle(new Style { Custom = “dd/mm/yyyy” });
secondCell.SetStyle(new Style { Custom = “dd/mm/yyyy” });

//Insert the value to other cells or in some other sheets cells temporarily.
ws.Cells[“D1”].PutValue(41250);
ws.Cells[“D1”].SetStyle(new Style { Custom = “dd/mm/yyyy” });
//Get the original width.
double owidth = ws.Cells.GetColumnWidth(3);
ws.AutoFitColumn(3);
double width = ws.Cells.GetColumnWidth(3);
ws.Cells.SetColumnWidth(3, owidth);
//Remove the value.
ws.Cells[“D1”].PutValue(null);
//specify the first column width accordingly now
ws.Cells.SetColumnWidth(0, width);
workbook.Save(“e:\test2\out1.xlsx”);

Thank you.