PRINT DATE DATA TO OOCALC

Hi All,

I want to print date data to oocalc.
I want it formatted as date not string.
It could be sorted.

How could I do it ?

Thank you.
LVL 1
emi_sastraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

emi_sastraAuthor Commented:
Hi CodeCruiser,

I just need to format date data.

I have download all of them, but I can not find what I need.

Thank you.
CodeCruiserCommented:
How do you send the data to it?
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

emi_sastraAuthor Commented:
Please see below:

 oSheet.getCellByPosition(ColTitleOne.JatuhTempo, intRow).SetString(Format(dtRow("TglJatuhTempo"), "dd/MM/yyyy"))

It is in string format, can not be sorted as date.
I want it formatted as date.

Thank you.
CodeCruiserCommented:
What is the column type of the field in data table? You would have to change the type in OOCalc.
emi_sastraAuthor Commented:
--What is the column type of the field in data table?
It is a date type.

--You would have to change the type in OOCalc.
I know, but I don't know how.

Thank you.
CodeCruiserCommented:
What happens if you change this

SetString(Format(dtRow("TglJatuhTempo"), "dd/MM/yyyy"))

to

SetString(dtRow("TglJatuhTempo"))
emi_sastraAuthor Commented:
Worth to try.

Thank you.
emi_sastraAuthor Commented:
Nop, it is in number format, not date.

Thank you.
emi_sastraAuthor Commented:
When I see its cell format at OOCalc.

Thank you.
CodeCruiserCommented:
What is the format of the date in data table?
CodeCruiserCommented:
What is the type of oSheet here? Did you try finding a property like Cells(?).Format... etc?
emi_sastraAuthor Commented:
- What is the format of the date in data table?
It is date format.

--What is the type of oSheet here?
Object

-Did you try finding a property like Cells(?).Format... etc?
It is hard to find.

Thank you.
Public Sub Open_WorkSheet_OOo()

        oServiceManager = CreateObject("com.sun.star.ServiceManager")
        oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")
        oCalcDoc = oDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, aNoArgs)

        oSheet = oCalcDoc.getSheets().getByIndex(0)

        oCalcCtrl = oCalcDoc.getCurrentController()

        Dim oPageStyles As Object = oCalcDoc.StyleFamilies.getByName("PageStyles")
        Dim oDefault As Object = oPageStyles.getByName("Default")

        oDefault.HeaderIsOn = False
        oDefault.FooterIsOn = False

    End Sub

Open in new window

ltlbearand3Commented:
emi_sastra,

To set a date in vb.net for CALC, you need to set the sell value with a data variable use the cell.SetValue command.  Then you need to set the format of the cell using the cell.NumberFormat command.  Now the NumberFormat command wants an integer value that relates to the format.  Here is a sub routine that will help with that:

    Private Function CalcGetFormatNumber(ByVal OOoServiceManager As Object, _
        ByVal CalcDocument As Object, _
        ByVal FormatString As String) As Integer

        ' OOoServiceManager is an object defined like
        '   Dim oSM As Object = CreateObject("com.sun.star.ServiceManager")

        Dim intFormatNum As Integer
        ' Get a List of all Formats
        Dim objFormatList As Object = CalcDocument.getNumberFormats()
        ' Grab Calc's Language Object as each language has it's own sub-list
        Dim objLocale As Object = OOoServiceManager.createInstance("com.sun.star.lang.locale")

        ' Look for your specific Format
        intFormatNum = objFormatList.querykey(FormatString, objLocale, True)
        If intFormatNum = -1 Then
            ' Format does not exist.  Need to add it
            intFormatNum = objFormatList.addNew(FormatString, objLocale)
            If intFormatNum = -1 Then intFormatNum = 0
        End If

        Return intFormatNum
    End Function

Open in new window


Now in your code send it your service manager object and calc document along with the format and it will return the appropriate value.  You could adjust your code like such:

    Public Sub Open_WorkSheet_OOo()

        Dim oServiceManager = CreateObject("com.sun.star.ServiceManager")
        Dim oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")
        Dim oCalcDoc = oDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, aNoArgs)

        Dim oSheet = oCalcDoc.getSheets().getByIndex(0)

        Dim oCalcCtrl = oCalcDoc.getCurrentController()

        Dim oPageStyles As Object = oCalcDoc.StyleFamilies.getByName("PageStyles")
        Dim oDefault As Object = oPageStyles.getByName("Default")

        oDefault.HeaderIsOn = False
        oDefault.FooterIsOn = False

        Dim oCell As Object = oSheet.getCellRangeByName("A1")

        oCell.setValue(#5/11/2011#)
        oCell.NumberFormat = CalcGetFormatNumber(oServiceManager, oCalcDoc, "MM/DD/YYYY")

        oCell = oSheet.getCellRangeByName("A2")
        oCell.setValue(#5/1/2011#)
        oCell.NumberFormat = CalcGetFormatNumber(oServiceManager, oCalcDoc, "MM/DD/YYYY")

    End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
emi_sastraAuthor Commented:
Hi ltlbearand3,

I am so sorry, just back again.

Does it support for any regional and language setting ?

Thank you.
ltlbearand3Commented:
The subroutine looks up the format sent it from the list in Calc.  Calc's list changes based on the regional settings of the machine.  Now if you send it a format that is not in the current list based on the regional settings, it will make is a custom format and display it as sent.  Therefore, you need to make sure the string containing the date format that you send the sub routine contains an appropriate data format for the regional settings of the machine on which the code is being run.

I hope that helps.  If not, let me know.

-Bear
emi_sastraAuthor Commented:
I have tried, when the regional setting is different from the date format then it cause error.

Could we do print :

1. Print depends on the regional setting.
2. Set the date format regardless of any regional setting (custom format)

Thank you.
ltlbearand3Commented:
OK.  I will have to test on a virtual machine.  It will probably be later in the week before I can run the tests.  I will get back to you when I find out something.

-Bear
emi_sastraAuthor Commented:
Thank you.
ltlbearand3Commented:
emi_sastra,

Sorry it has taken me a lot longer to get time to set up and test.  I ran the code below on a system with regional settings as follows:

Standards and Formats = Indonesian
Location = Indonesia

I also ran it with

Standards and Formats = English
Location = United States

I had no problems with either.

-Bear
Dim objServiceManager As Object = CreateObject("com.sun.star.ServiceManager")
        Dim objOODesktop As Object = objServiceManager.createInstance("com.sun.star.frame.Desktop")
        Dim arg(-1) As Object
        Dim objCalc As Object = objOODesktop.LoadComponentFromUrl("private:factory/scalc", "_blank", 0, arg)
        Dim objSheet As Object = objCalc.Sheets.getByIndex(0)

        Dim oCell As Object = objSheet.getCellRangeByName("A1")

        oCell.setValue(#5/11/2011#)
        oCell.NumberFormat = CalcGetFormatNumber(objServiceManager, objCalc, "MM/DD/YYYY")

        oCell = objSheet.getCellRangeByName("A2")
        oCell.setValue(#5/1/2011#)
        oCell.NumberFormat = CalcGetFormatNumber(objServiceManager, objCalc, "nnnn mmmm yyyy")


        oCell = objSheet.getCellRangeByName("A3")
        oCell.SetValue(#7/5/2012#)
        oCell.NumberFormat = CalcGetFormatNumber(objServiceManager, objCalc, "NN DD/MMMM YY")

        oCell = objSheet.getCellRangeByName("A4")
        oCell.SetValue(#5/5/2011#)
        oCell.NumberFormat = CalcGetFormatNumber(objServiceManager, objCalc, "MM/DD/YYYY")

        objCalc.storetoURL("file:///c:/EETest/vbtest.ods", arg)
        objCalc.Close(False)

Open in new window

emi_sastraAuthor Commented:
Hi Bear,

I also confuse. At my computer works both.
But at client computer, unpredictable.
Some works and some not.

What could be the problem, could it be the dll ?

Thank you.
ltlbearand3Commented:
emi_sastra,

I am not really sure what it could be.  I would verify all the regional settings.  Also check the version of Open Office.  Make sure it is the same version.  Check the language settings in Open Office.

What kind of results are you seeing at the client?

-Bear
emi_sastraAuthor Commented:
Hi Bear,

Yes, it works on both English and Indonesia.

Thank you very much for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.