I have an existing spreadsheet in which all of the columns are formatted as text. Based on some configuration data, I need to change the format to number/datetime/etc. The code runs fine, except when I open the spreadsheet, nothing appears to have formatted. When I actually go into a cell, edit the data, then leave the cell - the formatting that I set programatically finally takes effect.
Hi,
I have run your code and modified it a little, I am pasting the code, so you can view it. In order to run it, I created a source.xlsx file manually using Ms-Excel. I found, your number code format is taking effect.
If you find any problem, then please provide me your source xls/xlsx files and screenshots.
Please use the latest version:
Aspose.Cells
for .NET v6.0.1.4
Please see the code below and the output xlsx file and also see the screenshot.
C#
string excelFilePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;
//Creating a Workbook object and opening an Excel file using its file path
Workbook workbook = null;
Worksheet worksheet = null;
//Instantiate LoadOptions specified by the LoadFormat.
LoadOptions loadOptions = new LoadOptions(LoadFormat.Xlsx);
//Creating a Workbook object and opening an Excel file using its file path
workbook = new Workbook(excelFilePath, loadOptions);
worksheet = workbook.Worksheets[0];
Style styleCol = worksheet.Cells.Columns[1].Style;
//Creating StyleFlag
StyleFlag styleFlag = new StyleFlag();
styleFlag.NumberFormat = true;
styleCol.Number = 22;
worksheet.Cells.ApplyColumnStyle(1, styleCol, styleFlag);
//set everything to autofit by default
worksheet.AutoFitColumns();
workbook.Save(excelFilePath+“.out.xlsx”, SaveFormat.Xlsx);
I updated my dll to use the 6.0.1.4 version you supplied and moved the auto fit to the bottom, but got the same results.
Hi,
The issue is caused by, the value of the cell is not a datetime value and it’s a string value in the template file, so the number format will not effect.
If you apply the number in MS Excel, you will see it does not work too.
If the file is generated by Aspose.Cells, please use Cell.PutValue(string, true) method to convert the string value to numeric value or datetime value.