Solved

PRINT TO OOCALC PROBLEM

Posted on 2011-02-14
34
439 Views
Last Modified: 2012-06-27
Hi All,

I print report to OOCalc.

The problem is when regional setting to Indonesia.
Number printed became bigger than should be. ie 10 to 10,0000

How could I solve this problem ?

Thank you.
0
Comment
Question by:emi_sastra
  • 18
  • 14
  • 2
34 Comments
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 34893583
emi_sastra,

We need some more details to be able to assist you.  Could you please post the part of the code that is generating the number.  What kind of formatting are you assigning to the cell?  Is there a chance the regional setting is converting that number (ie. currency value or something?).

- Bear
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 34894407
Hi Bear,

Please see below code.

   Public Sub Print_Column_Nilai(ByVal oSheet As Object, _
                                  ByVal oCalcDoc As Object, _
                                  ByVal intCol As Integer, _
                                  ByVal intRow As Integer, _
                                  ByVal strData As String, _
                                  ByVal intDecimal As Integer, _
                                  Optional ByVal sngWidth As Single = 0, _
                                  Optional ByVal blnAlignRight As Boolean = True, _
                                  Optional ByVal blnFormatNumber As Boolean = True, _
                                  Optional ByVal strCharWeight As String = "", _
                                  Optional ByVal strCurrency As String = "")

        Dim dblData As Double = 0

        If Not IsNumeric(strData) Then strData = 0

        If IsNumeric(strData) Then
            dblData = CDbl(strData)
        End If

        Dim strNumberFormat As String = ""

        strNumberFormat = "#,##0"

        If intDecimal > 0 Then
            strNumberFormat &= "." & New String("0"c, intDecimal)
        End If

        If ERV_Global.Get_Decimal_Char() = "." Then
            strNumberFormat = "#,##0"

            If intDecimal > 0 Then
                strNumberFormat &= "." & New String("0"c, intDecimal)
            End If

        Else
            strNumberFormat = "#.##0"

            If intDecimal > 0 Then
                strNumberFormat &= "," & New String("0"c, intDecimal)
            End If
        End If

        Try

            oSheet.getCellByPosition(intCol, intRow).SetValue(dblData)

            If blnFormatNumber Then
                If strCurrency.Trim = "" Then
                    oSheet.getCellByPosition(intCol, intRow).NumberFormat = mdlOpenOffice.getNumberFormat(oCalcDoc, strNumberFormat)
                Else
                    oSheet.getCellByPosition(intCol, intRow).NumberFormat = mdlOpenOffice.getNumberFormat(oCalcDoc, strCurrency.Trim & strNumberFormat)
                End If
            End If

                If sngWidth > 0 Then
                    oSheet.getColumns().getByIndex(intCol).Width = sngWidth
                End If

                If blnAlignRight Then
                    oSheet.getCellByPosition(intCol, intRow).HoriJustify = ALIGNMENT_RIGHT
                End If

                If strCharWeight.Trim <> "" Then
                    oSheet.getCellByPosition(intCol, intRow).CharWeight = strCharWeight
                End If

                oSheet.getCellByPosition(intCol, intRow).VertJustify = ALIGNMENT_CENTER

        Catch ex As Exception

        End Try

    End Sub

Thank you.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34896157
Is it this

Optional ByVal sngWidth As Single = 0

that is the problem?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 34902376
Hi CodeCruiser,

I have no problem with sngWidth.

The problem is formating number.

Thank you.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 34902825
I believe your problem is with:
                If strCurrency.Trim = "" Then
                    oSheet.getCellByPosition(intCol, intRow).NumberFormat = mdlOpenOffice.getNumberFormat(oCalcDoc, strNumberFormat)
                Else
                    oSheet.getCellByPosition(intCol, intRow).NumberFormat = mdlOpenOffice.getNumberFormat(oCalcDoc, strCurrency.Trim & strNumberFormat)
                End If

Open in new window


My guess is that you are keeping a current value in strCurrency and when you change regional settings it is adjusting that value.  I suggest putting in some debug.print statements to see what values are getting passed and exactly what line of code is producing the wrong value.  It is hard to know exactly what is happening.  Perhaps you could post some information like what you have passed to this routine and then what some values have and what lines of code are actually being used when it generates the incorrect value.

-Bear
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 34902878
Hi ltlbearand3,

The problem is certainly from strNumberFormat.

  Dim strNumberFormat As String = ""

        strNumberFormat = "#,##0"

        If intDecimal > 0 Then
            strNumberFormat &= "." & New String("0"c, intDecimal)
        End If

        If ERV_Global.Get_Decimal_Char() = "." Then
            strNumberFormat = "#,##0"

            If intDecimal > 0 Then
                strNumberFormat &= "." & New String("0"c, intDecimal)
            End If

        Else
            strNumberFormat = "#.##0"

            If intDecimal > 0 Then
                strNumberFormat &= "," & New String("0"c, intDecimal)
            End If
        End If

Does the above format covered any regional setting (thousands separator and decimals point ?

Forget about strCurrency, this is often empty.

Thank you.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 34903447
Ok I think I figured out your problem.  I took a VM and changed the regional setting to Indonesia.  When doing so I noticed the Indonesia uses a decimal where US uses a comma and they use a comma where US uses a decimal.  Therefore when you tell it to format with #,##0, it will make a us number of 10 display 10 and 1000 display 1,000.  However, when you convert to Indonesia 10 becomes 10,000 (or the same as if your format was #.##0 in US which would display as 10.000).  You will need to adjust your code to have a format of #.##0 when using this for Indonesia.

-Bear
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 34903746
Yes, that is what I am going to find out.

Thank you.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34905076
What happens if you dont set the Cell format and just set the value?
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 34905205
There will be no thousand separator and decimal point.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 34923274
Hi All,

Please see this link http://www.codeproject.com/KB/cs/TblProc.aspx

It has the format number function, but I fail to compile it.

Thank you.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 34957874
emi_sanstra,

Sorry it has taken me a little bit to get back to this.  I don't know that code you linked at all, but we should be able to adjust your code.  I had to research this a little bit.  Setting Cell formats is a little tricky in code.  I have created a function that you can use to assist (based on snippets found at a few web sites).  Basically you have to look up your format to find the format number (which can be different per document and per regional setting ("locale" in calc)).  Then you can use my function to set the cells format.  I have put ???? in your snippet since I don't know what object your are using for the com.sun.star.ServiceManager.

My Function:
    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


Your adjusted code:
   Public Sub Print_Column_Nilai(ByVal oSheet As Object, _
                                  ByVal oCalcDoc As Object, _
                                  ByVal intCol As Integer, _
                                  ByVal intRow As Integer, _
                                  ByVal strData As String, _
                                  ByVal intDecimal As Integer, _
                                  Optional ByVal sngWidth As Single = 0, _
                                  Optional ByVal blnAlignRight As Boolean = True, _
                                  Optional ByVal blnFormatNumber As Boolean = True, _
                                  Optional ByVal strCharWeight As String = "", _
                                  Optional ByVal strCurrency As String = "")

        Dim dblData As Double = 0

        If Not IsNumeric(strData) Then strData = 0

        If IsNumeric(strData) Then
            dblData = CDbl(strData)
        End If

        Dim strNumberFormat As String = ""

        strNumberFormat = "#,##0"

        If intDecimal > 0 Then
            strNumberFormat &= "." & New String("0"c, intDecimal)
        End If

        If ERV_Global.Get_Decimal_Char() = "." Then
            strNumberFormat = "#,##0"

            If intDecimal > 0 Then
                strNumberFormat &= "." & New String("0"c, intDecimal)
            End If

        Else
            strNumberFormat = "#.##0"

            If intDecimal > 0 Then
                strNumberFormat &= "," & New String("0"c, intDecimal)
            End If
        End If

        Try

            oSheet.getCellByPosition(intCol, intRow).SetValue(dblData)

            If blnFormatNumber Then
                If strCurrency.Trim = "" Then
                    oSheet.getCellByPosition(intCol, intRow).NumberFormat = CalcGetFormatNumber(????, oCalcDoc, strNumberFormat)
                Else
                    oSheet.getCellByPosition(intCol, intRow).NumberFormat = CalcGetFormatNumber(????, oCalcDoc, strCurrency.Trim & strNumberFormat)
                End If
            End If

                If sngWidth > 0 Then
                    oSheet.getColumns().getByIndex(intCol).Width = sngWidth
                End If

                If blnAlignRight Then
                    oSheet.getCellByPosition(intCol, intRow).HoriJustify = ALIGNMENT_RIGHT
                End If

                If strCharWeight.Trim <> "" Then
                    oSheet.getCellByPosition(intCol, intRow).CharWeight = strCharWeight
                End If

                oSheet.getCellByPosition(intCol, intRow).VertJustify = ALIGNMENT_CENTER

        Catch ex As Exception

        End Try

    End Sub

Open in new window


Now I only tested my function not the adjustments to your code.  Let us know how it goes.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35090648
Hi ltlbearand3,

I am so sorry, just back again.

I will test it to day, will let you know soon.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35100355
Hi ltlbearand3,

Here are the result with each regional settings.

1. English (United State), no currency appear.
    10.00
    10

2. Indonesian, no currency appear.
    10.00000
    10

Caller :

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Open_WorkSheet_OOo()

        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 1, "10", 0)
        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 2, "10", 0, , , , , "Rp")

        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 1, "10", 2)
        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 2, "10", 2, , , , , "USD")

    End Sub

    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


Thank you.
   

Module ERV_Global

    Public Const ALIGNMENT_CENTER As Integer = 2
    Public Const ALIGNMENT_RIGHT As Integer = 3

    Public Sub Print_Column_Nilai_New(ByVal oSheet As Object, _
                               ByVal oCalcDoc As Object, _
                               ByVal intCol As Integer, _
                               ByVal intRow As Integer, _
                               ByVal strData As String, _
                               ByVal intDecimal As Integer, _
                               Optional ByVal sngWidth As Single = 0, _
                               Optional ByVal blnAlignRight As Boolean = True, _
                               Optional ByVal blnFormatNumber As Boolean = True, _
                               Optional ByVal strCharWeight As String = "", _
                               Optional ByVal strCurrency As String = "")

        Dim dblData As Double = 0

        If Not IsNumeric(strData) Then strData = 0

        If IsNumeric(strData) Then
            dblData = CDbl(strData)
        End If

        Dim strNumberFormat As String = ""

        strNumberFormat = "#,##0"

        If intDecimal > 0 Then
            strNumberFormat &= "." & New String("0"c, intDecimal)
        End If

        If ERV_Global.Get_Decimal_Char() = "." Then
            strNumberFormat = "#,##0"

            If intDecimal > 0 Then
                strNumberFormat &= "." & New String("0"c, intDecimal)
            End If

        Else
            strNumberFormat = "#.##0"

            If intDecimal > 0 Then
                strNumberFormat &= "," & New String("0"c, intDecimal)
            End If
        End If

        Try

            oSheet.getCellByPosition(intCol, intRow).SetValue(dblData)

            If blnFormatNumber Then
                If strCurrency.Trim = "" Then
                    oSheet.getCellByPosition(intCol, intRow).NumberFormat = CalcGetFormatNumber(oCalcDoc, strNumberFormat)
                Else
                    oSheet.getCellByPosition(intCol, intRow).NumberFormat = CalcGetFormatNumber(oCalcDoc, strCurrency.Trim & strNumberFormat)
                End If
            End If

            If sngWidth > 0 Then
                oSheet.getColumns().getByIndex(intCol).Width = sngWidth
            End If

            If blnAlignRight Then
                oSheet.getCellByPosition(intCol, intRow).HoriJustify = ALIGNMENT_RIGHT
            End If

            If strCharWeight.Trim <> "" Then
                oSheet.getCellByPosition(intCol, intRow).CharWeight = strCharWeight
            End If

            oSheet.getCellByPosition(intCol, intRow).VertJustify = ALIGNMENT_CENTER

        Catch ex As Exception

        End Try

    End Sub

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

        Dim oServiceManager 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 = oServiceManager.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

    Public Function Get_Decimal_Char() As String
        Dim curNilai As Decimal
        curNilai = 1.5

        Get_Decimal_Char = Format(curNilai, "#.0")
        Get_Decimal_Char = Mid(Get_Decimal_Char, 2, 1)
    End Function

End Module

Open in new window

0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 35102316
You need this to be a currency format?  I may have to dig into this, but let us trying something simple and quick first.  Instead of sending "USD" in your call send "-$" as that should trigger the us dollar format in my sub function.  For Indonesia send "-Rp" instead of "Rp"

-Bear
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 35103143
I looked at this a little more.  The Rp should work as long as the regional setting is at Indonesia when the code is run.  Make sure you have your setting on Indonesia.  Then run your code and check the Calc File.  For the US, use just "$" instead of USD.

If I send "$#,##0.00" to my function, I get a nice currency format in the Calc document with my US regional setting.

-Bear
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35103417
Please see below result, something mising in previous code.

ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 1, "10", 0)
        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 2, "10", 0, , , , , "Rp")

        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 4, "10", 2)
        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 5, "10", 2, , , , , "USD")


English(United States)

10
10

10.00
10


Indonesian

Decimal point > thanset it.

10,000                 Should be 10
Rp10,000                             Rp10

10,00000                             10,00  
10                                       USD10,00


Thank you
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 20

Expert Comment

by:ltlbearand3
ID: 35104275
I don't know that I truly follow what you want with the USD10 as in US currency is $10.  However, try the following as I think it should get you what you want.

Instead of
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 1, "10", 0)
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 2, "10", 0, , , , , "Rp")

ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 4, "10", 2)
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 5, "10", 2, , , , , "USD")

Open in new window



Try
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 1, "10", 0)
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 2, "10", 0, , , , , "[$Rp]")

ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 4, "10", 2)
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 5, "10", 2, , , , , "[$USD]")

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35104307
Yes, it works for the currency, but still problem at decimal number.

Thank you.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 35104676
Well, you are basically defining your own number format with how you have this set up now.  I need to back up and define again what you really want as I don't appear to have the under control.  Do you want to have the numbers formatted in a Calc predefined currency format?  If so, we need to rework your code.  Or are you wanting it to start with USD or RP?  If that is the case, do you want the proper number and decimal formats for the respective countries?  If that is the case, then we need to force the format to be correct for that country as we have to use a user-defined cell format.

So do you want:

Option A
$10,000.00 / $10,000
Rp10.000,00 / Rp10.000

Option B
USD10,000.00 / USD10,000
Rp10.000,00 / Rp10.000

Option C
USD10,000.00 / USD10,000
Rp10,000.00 / Rp10,000

Option D
10,000.00 USD/ 10,000 USD
10.000,00 IDR/ 10.000 IDR

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35104806
Do you want to have the numbers formatted in a Calc predefined currency format?
Yes and No, depends on the parameter.

do you want the proper number and decimal formats for the respective countries?  
Yes, the number of decimal is formatted based on parameter.

Indonesian

1. 10.000
2. 10.000, (number of decimals)

3. [Currency) 10.000
4  [Currency] 10.000, (number of decimals)


English

1. 10,000
2. 10,000. (number of decimals)

3. [Currency) 10,000
4  [Currency] 10,000. (number of decimals)

Thank you.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 35114532
OK as I look at this more, I think we will have to force a custom format as it will just be too difficult to look up the correct currency formation.  If you add any more currency other than RP and USD, you will need to update the Case Statement with the appropriate values.  You can look them up in Calc to get an idea.  Try code below and call it like:
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 1, "10", 0)
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 2, "10", 0, , , , , "Rp")

ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 4, "10", 2)
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 5, "10", 2, , , , , "USD")

Open in new window

Module ERV_Global

    Public Const ALIGNMENT_CENTER As Integer = 2
    Public Const ALIGNMENT_RIGHT As Integer = 3

    Public Sub Print_Column_Nilai_New(ByVal oSheet As Object, _
                               ByVal oCalcDoc As Object, _
                               ByVal intCol As Integer, _
                               ByVal intRow As Integer, _
                               ByVal strData As String, _
                               ByVal intDecimal As Integer, _
                               Optional ByVal sngWidth As Single = 0, _
                               Optional ByVal blnAlignRight As Boolean = True, _
                               Optional ByVal blnFormatNumber As Boolean = True, _
                               Optional ByVal strCharWeight As String = "", _
                               Optional ByVal strCurrency As String = "")

        Dim dblData As Double = 0

        If Not IsNumeric(strData) Then strData = 0

        If IsNumeric(strData) Then
            dblData = CDbl(strData)
        End If

        Dim strNumberFormat As String = vbNullstring

	' Select the currency code sent and apply the appropriate cell format structure.
	Select strCurrency.Trim.ToUpper
            Case "USD"
                If intDecimal > 0 Then
                    strNumberFormat &= "[$$-409]#,###0." & New String("0"c, intDecimal)
                Else
                    strNumberFormat &= "[$$-409]#,###0"
                End If
            Case "RP"
                If intDecimal > 0 Then
                    strNumberFormat &= "[$Rp-421]#.###0," & New String("0"c, intDecimal)
                Else
                    strNumberFormat &= "[$Rp-421]#.###0"
                End If
	    Case Else
                If intDecimal > 0 Then
                    strNumberFormat &= "#,###0." & New String("0"c, intDecimal)
                Else
                    strNumberFormat &= "#,###0"
                End If
        End Select

        Try

            oSheet.getCellByPosition(intCol, intRow).SetValue(dblData)
	    oSheet.getCellByPosition(intCol, intRow).NumberFormat = CalcGetFormatNumber(oCalcDoc, strNumberFormat)

            If sngWidth > 0 Then
                oSheet.getColumns().getByIndex(intCol).Width = sngWidth
            End If

            If blnAlignRight Then
                oSheet.getCellByPosition(intCol, intRow).HoriJustify = ALIGNMENT_RIGHT
            End If

            If strCharWeight.Trim <> "" Then
                oSheet.getCellByPosition(intCol, intRow).CharWeight = strCharWeight
            End If

            oSheet.getCellByPosition(intCol, intRow).VertJustify = ALIGNMENT_CENTER

        Catch ex As Exception

        End Try

    End Sub

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

        Dim oServiceManager 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 = oServiceManager.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

    Public Function Get_Decimal_Char() As String
        Dim curNilai As Decimal
        curNilai = 1.5

        Get_Decimal_Char = Format(curNilai, "#.0")
        Get_Decimal_Char = Mid(Get_Decimal_Char, 2, 1)
    End Function

End Module

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35114769
English

10
Rp10.0000   Should be Rp10

10.00
$10.00


Indonesian

10,0000 Should be 10
Rp10

10,000000  Should be 10,00
$10,000000  Should be $10,00

Thank you.

0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 35119135
We will get this thing conquered yet.  I found what I needed to set the decimal and thousands separator based on the system values.  Now you just have to pass it the Calc code for the correct currency format.  You can look this up in Calc in the cell format under currency.

Pass these values:

ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 1, "10", 0)
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 2, "10", 0, , , , , "[$Rp-421]") ' Calc currency RP - Indonesia

ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 4, "10", 2)
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 5, "10", 2, , , , , "[$$-409]")  ' Calc currency $ - USD

Open in new window


and now the adapted code:
Module ERV_Global

    Public Const ALIGNMENT_CENTER As Integer = 2
    Public Const ALIGNMENT_RIGHT As Integer = 3

    Public Sub Print_Column_Nilai_New(ByVal oSheet As Object, _
                               ByVal oCalcDoc As Object, _
                               ByVal intCol As Integer, _
                               ByVal intRow As Integer, _
                               ByVal strData As String, _
                               ByVal intDecimal As Integer, _
                               Optional ByVal sngWidth As Single = 0, _
                               Optional ByVal blnAlignRight As Boolean = True, _
                               Optional ByVal blnFormatNumber As Boolean = True, _
                               Optional ByVal strCharWeight As String = "", _
                               Optional ByVal strCurrency As String = vbNullString)

        Dim dblData As Double = 0

        If Not IsNumeric(strData) Then strData = 0

        If IsNumeric(strData) Then
            dblData = CDbl(strData)
        End If

        Dim strNumberFormat As String = strCurrency

        If intDecimal > 0 Then
            strNumberFormat &= "#" & System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencyGroupSeparator
            strNumberFormat &= "###0" & System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencyDecimalSeparator & New String("0"c, intDecimal)
        Else
            strNumberFormat &= "#" & System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencyGroupSeparator & "###0"
        End If

        Try

            oSheet.getCellByPosition(intCol, intRow).SetValue(dblData)
            oSheet.getCellByPosition(intCol, intRow).NumberFormat = CalcGetFormatNumber(oCalcDoc, strNumberFormat)

            If sngWidth > 0 Then
                oSheet.getColumns().getByIndex(intCol).Width = sngWidth
            End If

            If blnAlignRight Then
                oSheet.getCellByPosition(intCol, intRow).HoriJustify = ALIGNMENT_RIGHT
            End If

            If strCharWeight.Trim <> "" Then
                oSheet.getCellByPosition(intCol, intRow).CharWeight = strCharWeight
            End If

            oSheet.getCellByPosition(intCol, intRow).VertJustify = ALIGNMENT_CENTER

        Catch ex As Exception

        End Try

    End Sub

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

        Dim oServiceManager 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 = oServiceManager.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

End Module

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35119252
Please see below info.

 ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 1, "10", 0)
        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 2, "10", 0, , , , , "[$Rp-421]") ' Calc currency RP - Indonesia

        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 4, "10", 2)
        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 5, "10", 2, , , , , "[$Rp-421]") ' Calc currency RP - Indonesia

        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 7, "10", 0)
        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 8, "10", 0, , , , , "[$$-409]")  ' Calc currency $ - USD

        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 10, "10", 2)
        ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 11, "10", 2, , , , , "[$$-409]")  ' Calc currency $ - USD


Thank you.

ENGLISH	
	10,0000
	Rp10,0000
	
	10,000000
	Rp10,000000
	
	10,0000
	$10,0000
	
	10
	$10,000000

Open in new window

INDONESIAN	
	10
	Rp10
	
	10,00
	Rp10,00
	
	10
	$10
	
	10
	$10,00

Open in new window

0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 total points
ID: 35134914
When you change to Indonesian, what exactly are you changing?  Your system must be returning different values than mine.  What is stored in in the settings for currency and number in the OS?  (Start menu >> control Panel >> Regional and Language >> Regional Options Tab)  Is this changed when you change between English and Indonesian?

I did see one mistake in the code, please change:
        If intDecimal > 0 Then
            strNumberFormat &= "#" & and System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencyGroupSeparator
            strNumberFormat &= "###0" & System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencyDecimalSeparator & New String("0"c, intDecimal)
        Else
            strNumberFormat &= "#" & System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencyGroupSeparator & "###0"
        End If

Open in new window


To

        If intDecimal > 0 Then
            strNumberFormat &= "#" & System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencyGroupSeparator
            strNumberFormat &= "##0" & System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencyDecimalSeparator & New String("0"c, intDecimal)
        Else
            strNumberFormat &= "#" & System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencyGroupSeparator & "##0"
        End If

Open in new window


However, that will not change the results drastically.  When I change my regional settings completely over to Indonesian, I get the right results with the changes above.  Therefore, I need to know what all you are changing to get switch between English and Indonesian.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35134966
Please see the screen shot.

What should I do after I change the regional setting ?
Should I close project first?

Thank you.
REGIONAL-SETTING.PNG
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35134967
Should I change something at Advance Tab?

Thank you.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 35135040
You have to reboot your computer after you change the regional settings for the changes to completely take affect.  OOo Calc will not recognize (and I believe any other program) the new regional setting until after reboot.  Please make my last change to your code.  Then set it to English (US), reboot the PC and run the code.  Then change to Indonesia, reboot and retest the code.  See how it works with the reboot in-between.

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35135055
Ok. I try it now.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35136242
It works.

How to write custom currency like 'USD' not '$'

Thank you.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 35137750
Just change the value you send your subroutine (you can make it any setting that you can clean from the formatting in calc (Cntrl -1 >>Numbers Tab >> Currency)

Instead of :
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 11, "10", 2, , , , , "[$$-409]")  ' Calc currency $ - USD

Open in new window


send:
ERV_Global.Print_Column_Nilai_New(oSheet, oCalcDoc, 1, 11, "10", 2, , , , , "[$USD]")  ' Calc currency USD

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
ID: 35137782
Ok.

Great help and working great.

Thank you very much for your help.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 35137824
Your welcome.  Glad we got it to work.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Gridview User Control event not firing 4 45
Handling arrays 14 33
System32Int Error 8 44
Hide Tab Page 3 19
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This video discusses moving either the default database or any database to a new volume.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now