Number Format of existing spreadsheet not taking effect until the cell is edited

Aspose.Cells Product Family
1

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.


I’ve gone through numerous forums regarding how to format a column and have even tried setting the style of every cell in a column, but none of them force the formatting to take effect prior to editing a cell. We had a similar issue when we used the excel interop (which we are trying to replace with Aspose.Cells), but were able to work around it by calling a TextToColumns on each column.

What am I missing??? I haven’t seen any additional code to do the formatting in other forums.

Andy

//Create a License object
License license = new License();

//Set the license of Aspose.Cells to avoid the evaluation
license.SetLicense(“Aspose.Cells.lic”);

//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[sheetName];

//set everything to autofit by default
worksheet.AutoFitColumns();

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);
workbook.Save(excelFilePath, SaveFormat.Xlsx);

2

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);

Screenshot:

3

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.


Attached are the input file I am using and the output file from the formatting. To better tell the difference I changed the number format of the second column to 14 from 22 to show that the Aspose formatting did get set, it just doesn’t display that way unless a cell is edited.

The input file is created by another process which currently uses the AccessDatabaseEngine to create the Excel 2007 xlsx file (I would like to replace this with Aspose as well, but cannot at this time).

I will be out of the office for a couple days, but will be able to respond again Monday, I just wanted to get this to you before I left.

Thanks,
Andy
4

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.