Excel export: Formatting column to date type

[Megumi Takahashi]

Hi,

 

I'm trying to format an entire column in an excel file before exporting data to excel.  I'm doing the follwoing but it is not working as I expect.  Am I doing wrong?

 

I want to get the result (Number Type = Custom, formatted date = 'Mon, Mar 18, 2013 00:00') by formatting the entire column.  (strFormat='ddd, mmm dd, yyyy hh:mm')

 

The following code formats the date value correctly, but I have to set the numberformat property for each cell. (The resulting Number Type is Custom 'ddd, mmm dd, yyyy hh:mm', and resulting cell value is 'Mon, Mar 18, 2013 00:00')

objExcelSheet.Range(intRow, intCol).DateTime = dtDateTime

objExcelSheet.Range(intRow, intCol).NumberFormat = strFormat

 

The following code formats the date in a default Date format in excel (The resulting Number Type is Date '*3/14/2001' and resulting cell value is '3/18/2013') 

objExcelSheet.Range(intRow, intCol).DateTime = dtDateTime

objExcelSheet.Columns(intCol).NumberFormat = strFormat

or

objExcelSheet.Columns(intCol).CellStyle.NumberFormat = strFormat

 

Thanks in advance.

[Manikandan M]

Hi Megumi,

Thanks for using Syncfusion products.

We are afraid that we are not able to reproduce the issue "Applying the specified number format to 'EntireColumn' and any column of worksheet is not working properly" from side in Essential studio version '10.3', '10.4' and '11.1'.We have created the simplified sample to reproduce the issue and have attached the same in the below link. Please make use of this and let us know if this helps you.

Sample link:NumberFormat.zip

If the above sample doesn't meet with sample scenario, please modify the sample reproduce the issue and revert back us with same. It would helps you to analysis the issue and update you the solution. Also please share us the your Essential studio version.

Please let us know if you need any clarifications.

Thanks,
Manikandan M.

[Megumi Takahashi]

Hi Manikandan,

 

Thank you for the sample code.  It works fine now after I replaced my code with  'Range.EntireColumn.NumberFormat' as you had in your sample code.  I was not using EntireColumn property.  Thank you very much for your help!

[Manikandan M]

Hi Megumi,

Thanks for the update.

We are happy to hear that above provided solution is working fine at your side. Please let us know if you need any clarifications.

Thanks,
Manikandan M.