Remain the decimal point

[sitizalekoh]

Hi. Good day everyone. I want to ask one question, I have a data in two column like this,
Data1| data2|
12.22| 12.22|
12.22| 12.22|
12.22| 12.22|
12.22| 12.22|

I want to remain the two decimal point, but not round the number.
so the data will be like this,
Data1| data2|
1222| 1222|
1222| 1222|
1222| 1222|
1222| 1222|

so as I read at the tutorial, and my code do like this,

Code: Select all

    Dim workbook As Workbook = New Workbook
        workbook.Version = ExcelVersion.Version2007
        workbook.LoadFromFile(("4.xlsx"))
        Dim sheet As Worksheet = workbook.Worksheets(1)
        Dim range = sheet.Columns(23)
        '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
                Exit For
            End If
            If range.Cells(i).NumberFormat = "#,##0.00" Then
                'range.Cells(i).NumberFormat = "0"
                sheet.Columns(23).NumberFormat = "0"
            End If

        Next

but this code are rounded my data. I want it to maintain the after decimal point. thanks all. really sorry

[Lisa.Li]

Hello,

Thanks for you post.
Please kindly note that our Spire.XLS is based on Microsoft Excel. It also rounds the data if you custom the NumberFormat type as "0" in Microsoft Excel. Sorry there is no good way to achieve your requirement in this way. However, you can try the following code snippet to achieve the same effect. If there is any question, welcome to write back.

Code: Select all

.....
If range.Cells(i).NumberFormat = "#,##0.00" Then
    Dim cellText As String = range.Cells(i).Value
    cellText = cellText.Replace(".", "")
    range.Cells(i).Value = cellText
End If
.....


Sincerely,
Lisa
E-iceblue support team

[sitizalekoh]

This one is manually does in excel, but can I convert to number like the picture in the attachment spire .xls?
Thanks

[Lisa.Li]

Hello,

Thank you for contacting.
You can use the following code snippet to achieve it. If there is any question, welcome to write back.

Code: Select all

.....
range.Cells(i).IgnoreErrorOptions = IgnoreErrorType.NumberAsText
......


Sincerely,
Lisa
E-iceblue support team

[sitizalekoh]

Sorry but I don't think that is work. The output that I want it to be is like in the first picture at attachment. for example, the number is 12.200, then the output is number. such as 12.2.
Thanks and sorry. I did try any code but it cannot convert to number.

[sitizalekoh]

I think in this case, maybe I should do this method,

Code: Select all

Dim range = sheet.Columns(19)
        For i As Integer = 1 To range.LastRow - 1

            Dim convert As Integer = Integer.Parse(range.Cells(i).Value)
        Next

But its not working, but I think this method will convert from the string 12.300 to 12.3, ?

[Lisa.Li]

Hello,

Thanks for your feedback.
Sorry that our Spire.XLS doesn't support converting the text format value to number format directly at present. We will consider adding the new feature in our upgrade list. Once there is any update, we will let you know. Besides, could you please share your input Excel file so that we could test your case when the new feature is done? You could send it to us via email(support@e-iceblue.com).

Sincerely,
Lisa
E-iceblue support team

[Lisa.Li]

Hello,

Glad to inform that we just released Spire.XLS Pack(Hotfix) Version:9.5.11 which supports converting the text format value to number format directly. Please download it from the following links and refer to the code snippet to have a test.
Website: https://www.e-iceblue.com/Download/download-excel-for-net-now.html
Nuget: https://www.nuget.org/packages/Spire.XLS/

Code: Select all

Dim workbook As New Workbook()
workbook.LoadFromFile("ConvertToNumber.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Range("A2").ConvertToNumber()
.....


Sincerely,
Lisa
E-icbelue support team

[Lisa.Li]

Hello,

Greetings from E-iceblue.
Did the hotfix version work for you? Thanks in advance for your valuable feedback and time.

Sincerely,
Lisa
E-icbelue support team