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

Mashiane

Expert
Licensed User
Longtime User
Hi there...

Can someone advise me please.

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

B4X: 
(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!
 
Sort by date Sort by votes

EnriqueGonzalez

Expert
Licensed User
Longtime User
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.

B4X: 
    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
 
Upvote 0

Daestrum

Expert
Licensed User
Longtime User
For the budget you can also use
B4X: 
Log(NumberFormat2("4.195E7",0,2,2,True)) ' just replace "4.195E7" with cell.value as a string or float or double
 
Upvote 0

Daestrum

Expert
Licensed User
Longtime User
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.
 
Upvote 0

Mashiane

Expert
Licensed User
Longtime User
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...

B4X: 
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!
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Article
B4J Tutorial [B4XTurtle]L-Systems and The Money Tree
Replies
4
Views
2K
William Lancee
  • Article
B4J Library PerfectMoney
Replies
2
Views
3K
MichalK73
  • Question
B4J Question Localizing Dates and Money
Replies
1
Views
934
Erel
  • Question
B4J Question Open money box
Replies
3
Views
2K
emexes
E
  • Article
B4J Library MoneyEditText (class)
Replies
5
Views
4K
Star-Dust
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…