How to set the column format to General without affecting the field values

Aspose.Cells Product Family
1

Hi,

I need help with setting the column format to General without affecting the field values. The issue is I have a Text column where the value can be something like 3090-01. If I don’t specify a format on the column, excel automatically sets the format and sets the format of the cell to yyyy-mm (which is wrong because it is not a date value). If I set the column format to General by setting the style:number = 0 and setting the NumberFormat style flag to True, the value is being converted to something else e.g. 3090-01 is converted to 434641. Same happens when I use a Text format i.e. style:number = 49.

Please advise on how to resolve this issue.

Thanks,
Carlo

2

@cmendoza Could you please provide the Workbook or a code snippet that would allow us to reproduce the issue you are experiencing? I have attempted to reproduce the problem, but when I create the file using Aspose.Cells API or enter the number directly into the cell using MS Excel application, the cell value is not automatically converted to a date type.

3

Hi,

I am using OpenEdge, and here is my sample code:

USING Aspose.Cells.* FROM ASSEMBLY.

DEFINE VARIABLE WorkBook1       AS Aspose.Cells.Workbook       NO-UNDO.
DEFINE VARIABLE TxtLoadOptions1 AS Aspose.Cells.TxtLoadOptions NO-UNDO.
DEFINE VARIABLE RCRange1        AS Aspose.Cells.Range          NO-UNDO.
DEFINE VARIABLE RCStyle1        AS Style                       NO-UNDO.
DEFINE VARIABLE RCStyleFlag1    AS StyleFlag                   NO-UNDO. 
  
DEFINE VARIABLE cDataFile       AS CHARACTER                   NO-UNDO.        
DEFINE VARIABLE cDocName        AS CHARACTER                   NO-UNDO.

ASSIGN cDocName  = "c:\temp\test-excel.xlsx"
       cDataFile = "c:\temp\triba000192.dat".

TxtLoadOptions1 = NEW TxtLoadOptions(LoadFormat:TabDelimited).

WorkBook1 = NEW Workbook(cDataFile, TxtLoadOptions1) NO-ERROR.
  
RCRange1 =  WorkBook1:WorkSheets[WorkBook1:WorkSheets:ActiveSheetIndex]:Cells:CreateRange("A:A") NO-ERROR.

RCStyle1 = Workbook1:CreateStyle().
RCStyleFlag1 = NEW StyleFlag().
//RCStyle1:Custom = "#".  
//RCStyle1:Number = 0. //General
RCStyle1:Number = 49. //Text
RCStyleFlag1:NumberFormat = TRUE. 
RCRange1:ApplyStyle(RCStyle1, RCStyleFlag1) NO-ERROR.
    
WorkBook1:Save(cDocName, SaveFormat:Xlsx).

This is the content of my triba000192.dat file (It’s a tab delimited file, I can’t attached here)

"Nominal Code"	"Description"
"3090-01"	"Testing Hyphen1"
"3090-02"	"Testing Hyphen2"
"3090-13"	"Testing Hyphen2"

Attached is the screenshot of the generated excel file. test-excel.PNG (34.2 KB)

Note that the 3090-01 and 3090-02 values are being converted to 434641 and 434642 (3090-13 is not). This is happening whether I use style:number = 0 (General) or style:number = 49 (Text) or even if I use style:custom = “#”. If I don’t use a style, it is not converting the value but it is automatically changing the format to Custom (yyyy-mm) see attached test-excel1.PNG (41.2 KB)

Thank you.

4

@cmendoza

Please set TxtLoadOptions.ConvertDateTimeData = false; to avoid converting text to Date.

1 Like
5

Thank you very much.

6

@cmendoza,

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