B4J Question PoiExcel: Some import advice (date & money conversion)

[Mashiane]

Hi there...

Can someone advise me please.

This is the text that I have read from my excel spreadsheeet:

(MyMap) {seqnumber=10.0, finyear=2016/17, programme=1, classofgoods=SERVICES, description=Consultants and Professional Services: Business and Advisory Services, resource=All, status=Ad Hoc, budget=4.195E7, advertdate=Mon Apr 11 00:00:00 CAT 2016, completiondate=Fri Mar 31 00:00:00 CAT 2017}

I need to convert the budget amount to be normal e.g. nnn.nn and the date to be yyyy-MM-dd e.g. 2017-05-12. These are returned by cell.value

Can someone please advice?

Thanks a lot!

 

[EnriqueGonzalez]

in stackoverflow they recommend using a DateFormatter:

http://stackoverflow.com/questions/...-excel-cells-as-string-not-numbers-with-apach
http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html

So if my JavaObject skills are not wrong.

    Dim joDF As JavaObject
    joDF.InitializeNewInstance("org.apache.poi.ss.usermodel.DataFormatter",Null)
   
    Dim s As String = joDF.RunMethod("formatCellValue",Array(cell))

This will help you with the bugdet, but some more steps will be required for the date as this method will return the number of days since 1900/01/01

 

[Daestrum]

For the budget you can also use

Log(NumberFormat2("4.195E7",0,2,2,True)) ' just replace "4.195E7" with cell.value as a string or float or double

 

[EnriqueGonzalez]

The problem with that approach is that the E7 part will hide integer values.

 

[Daestrum]

Not sure I understand what you mean , java knows that 4.195E7 is 41,950,000, the format simply adds the decimal paces as it is a currency value as the OP requested.

 

[EnriqueGonzalez]

Sometimes 4.195E7 is trully 4195005.50 but the last digits are not shown because of the format.

 

[Mashiane]

Thanks everyone, besides the numeric numbers showing in scientific notation, saving them to SQL didnt ring any bells, they are saved as doubles. Good. The date issue however persisted until I found something on the net about the dates saved in excel as numbers. jPoi is returning the dates as numbers, which is accurate anyway.

All the numeric dates are actually the number of days since 1 Jan 1900, however, due to an apparent lotus notes 1-2-3 compatibilty that wanted to be maintained by MS, one needs to minus 2 to those days.

If your excel dates are returned as numbers with jPOI then convert them to date with...

public Sub DateFromExcelDate(sValue As String) As String
    Dim myperiod As Period
    myperiod.Days = sValue - 2
    Dim xlsDate As Long = DateUtils.SetDate(1900, 1, 1)
    Dim newDate As Long = DateUtils.AddPeriod(xlsDate,myperiod)
    DateTime.DateFormat = "yyyy-MM-dd"
    Return DateTime.Date(newDate)
End Sub

Ta!