Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Mon Apr 29, 2019 7:26 am

Hi. I have a problem which are in my excel file, I have one column that contain date but in text format only, the entire column are like this,
| Date ........ | Aging Date |
|08.01.2019|
|10.01.2019|
|11.01.2019|
|12.01.2019|
|13.01.2019|
|14.01.2019|
|15.01.2019|
|16.01.2019|

My problem is, I want to convert from text format to Date dd-mmm-yyyy , 08-Jan-2019, for entire column.
how I need to do to make it like this,

| Date..... ..... | Aging Date |
|08-jan-2019|
|10-jan-2019|
|11-jan-2019|
|12-jan-2019|
|13-jan-2019|


As for me, I do it like this,
Code: Select all
sheet.Columns(30).EntireColumn.Style.NumberFormat = "dd-MMM-yyyy"


but unfortunately, it doesnt work. I mean, it doesnt format it to entire column, and doesnt change the date from 08-01-2019 to 08-Jan-2019.

Please someone do help me, and thanks in advance.

sitizalekoh
 
Posts: 15
Joined: Thu Apr 04, 2019 2:29 am

Mon Apr 29, 2019 7:38 am

Hi Siti,

Thanks for your inquiry. This is Betsy from E-iceblue support team.
Kindly note if the format is text format, there is no change for the text when using custom format “dd-mmm-yy” in MS Excel. Please change text format to date format then set the custom format. Below code for your kind reference:
Code: Select all
        Dim Workbook As Workbook = New Workbook()
        Workbook.LoadFromFile("17405.xlsx")
        Dim sheet As Worksheet = Workbook.Worksheets(0)
        Dim range = sheet.Columns(30)
        For i As Integer = 1 To range.Cells.Length - 1
            If range.Cells(i).Text IsNot Nothing Then
                Dim tx As String() = range.Cells(i).Text.Trim().Split(".")
                Dim day As Integer = Integer.Parse(tx(0))
                Dim month As Integer = Integer.Parse(tx(1))
                Dim year As Integer = Integer.Parse(tx(2))
                Dim dateValue As DateTime = New DateTime(year, month, day)
                range.Cells(i).DateTimeValue = dateValue
            End If
        Next
        sheet.Columns(30).NumberFormat = "dd-mmm-yy"
        Workbook.SaveToFile("17405.xlsx", ExcelVersion.Version2013)


Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Mon Apr 29, 2019 8:41 am

Hi Betsy,
Thanks for the reply, I tried to run the code, but it stuck and cannot run. So I changed the code to make it loop start from row=1 until lastrow. Here is my code that I edited,
Code: Select all
   Dim Workbook As Workbook = New Workbook()
        Workbook.LoadFromFile("1234567.xlsx")
        Dim sheet As Worksheet = Workbook.Worksheets(0)
        Dim range = sheet.Columns(1)

        Dim lastcell = sheet.Columns(1).LastRow

        For i As Integer = 1 To lastcell
            If range.Cells(i).Text IsNot Nothing Then
                Dim tx As String() = range.Cells(i).Text.Trim().Split(".")
                Dim day As Integer = Integer.Parse(tx(0))
                Dim month As Integer = Integer.Parse(tx(1))
                Dim year As Integer = Integer.Parse(tx(2))
                Dim dateValue As DateTime = New DateTime(year, month, day)
                range.Cells(i).DateTimeValue = dateValue
            End If
        Next
        sheet.Columns(1).NumberFormat = "dd-mmm-yy"


Unfortunately, I still have the same problem which is I cannot debug and cannot run.

sitizalekoh
 
Posts: 15
Joined: Thu Apr 04, 2019 2:29 am

Mon Apr 29, 2019 9:52 am

Hi,

Sorry to hear that.
In order to help us solve your issue quickly, please provide your input file.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Tue Apr 30, 2019 5:28 am

Hi, just want to share, it works already. Thank you for your guide.
here is the code that I do.

Code: Select all
    Dim workbook As Workbook = New Workbook
        workbook.Version = ExcelVersion.Version2007
        workbook.LoadFromFile(("test1.xlsx"))
        Dim sheet As Worksheet = workbook.Worksheets(0)
        Dim range = sheet.Columns(0)
        'For r As Integer = 2 To sheet.AllocatedRange.Rows.Count()

        For i As Integer = 1 To range.LastRow - 1
            If range.Cells(i).Text IsNot Nothing Then
                Dim tx As String() = range.Rows(i).Text.Trim().Split(".")
                Dim day As Integer = Integer.Parse(tx(0))
                Dim month As Integer = Integer.Parse(tx(1))
                Dim year As Integer = Integer.Parse(tx(2))
                Dim dateValue As Date = New Date(year, month, day)
                range.Cells(i).DateTimeValue = dateValue

            End If

        Next
        sheet.Columns(0).NumberFormat = "dd-mmm-yy"
        workbook.SaveToFile("\test1.xlsx", ExcelVersion.Version2013)

sitizalekoh
 
Posts: 15
Joined: Thu Apr 04, 2019 2:29 am

Tue Apr 30, 2019 5:38 am

Hi,

Glad to hear that.
If there is any question while using our product, just feel free to contact us.

Sincerely,
Betsy
E-iceblue support team
User avatar

Betsy.jiang
 
Posts: 3099
Joined: Tue Sep 06, 2016 8:30 am

Return to Spire.XLS