Resolves: #i121120 Support RightB(), LeftB(), LenB() and MidB()

Patch by: Zhang Lu
Review by: Wang Lei

(cherry picked from commit 424079cb8348d77e40f7d3291578bdffbe662f34)

Conflicts:
formula/inc/formula/compiler.hrc
formula/inc/formula/opcode.hxx
oox/source/xls/formulabase.cxx
sc/inc/helpids.h
sc/util/hidother.src
sc/util/makefile.mk

Change-Id: Ia04aa69aa2764842507b36ba7cc8c99e99a314e9

13 files changed, 367 insertions, 6 deletions

diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src
index 42d8b5301cb5..f4c5f9654e87 100644
--- a/formula/source/core/resource/core_resource.src
+++ b/formula/source/core/resource/core_resource.src
@@ -260,6 +260,10 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF
     String SC_OPCODE_RIGHT { Text = "RIGHT" ; };
     String SC_OPCODE_SEARCH { Text = "SEARCH" ; };
     String SC_OPCODE_MID { Text = "MID" ; };
+    String SC_OPCODE_LENB          { Text = "LENB" ; };
+    String SC_OPCODE_RIGHTB        { Text = "RIGHTB" ; };
+    String SC_OPCODE_LEFTB         { Text = "LEFTB" ; };
+    String SC_OPCODE_MIDB          { Text = "MIDB" ; };
     String SC_OPCODE_TEXT { Text = "TEXT" ; };
     String SC_OPCODE_SUBSTITUTE { Text = "SUBSTITUTE" ; };
     String SC_OPCODE_REPT { Text = "REPT" ; };
@@ -601,6 +605,10 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH
     String SC_OPCODE_RIGHT { Text = "RIGHT" ; };
     String SC_OPCODE_SEARCH { Text = "SEARCH" ; };
     String SC_OPCODE_MID { Text = "MID" ; };
+    String SC_OPCODE_LENB { Text = "LENB" ; };
+    String SC_OPCODE_RIGHTB { Text = "RIGHTB" ; };
+    String SC_OPCODE_LEFTB { Text = "LEFTB" ; };
+    String SC_OPCODE_MIDB { Text = "MIDB" ; };
     String SC_OPCODE_TEXT { Text = "TEXT" ; };
     String SC_OPCODE_SUBSTITUTE { Text = "SUBSTITUTE" ; };
     String SC_OPCODE_REPT { Text = "REPT" ; };
@@ -1578,6 +1586,22 @@ Resource RID_STRLIST_FUNCTION_NAMES
     {
         Text [ en-US ] = "MID" ;
     };
+    String SC_OPCODE_LENB
+    {
+        Text [ en-US ] = "LENB" ;
+    };
+    String SC_OPCODE_RIGHTB
+    {
+        Text [ en-US ] = "RIGHTB" ;
+    };
+    String SC_OPCODE_LEFTB
+    {
+        Text [ en-US ] = "LEFTB" ;
+    };
+    String SC_OPCODE_MIDB
+    {
+        Text [ en-US ] = "MIDB" ;
+    };
     String SC_OPCODE_TEXT
     {
         Text [ en-US ] = "TEXT" ;
diff --git a/include/formula/compiler.hrc b/include/formula/compiler.hrc
index 053947c2a796..e6c6ef3d0df3 100644
--- a/include/formula/compiler.hrc
+++ b/include/formula/compiler.hrc
@@ -403,9 +403,13 @@
 #define SC_OPCODE_COUNT_IFS         405
 #define SC_OPCODE_SKEWP             406
 #define SC_OPCODE_STOP_2_PAR        407
-#define SC_OPCODE_LAST_OPCODE_ID    406     /* last OpCode */
+#define SC_OPCODE_LENB              408
+#define SC_OPCODE_RIGHTB            409
+#define SC_OPCODE_LEFTB             410
+#define SC_OPCODE_MIDB              412
+#define SC_OPCODE_LAST_OPCODE_ID    413     /* last OpCode */
 
-#define SC_OPCODE_STOP_FUNCTION     407
+#define SC_OPCODE_STOP_FUNCTION     412
 
 /*** Internal ***/
 #define SC_OPCODE_INTERNAL_BEGIN   9999
diff --git a/include/formula/opcode.hxx b/include/formula/opcode.hxx
index b94c066a624b..47e2cdc2dff4 100644
--- a/include/formula/opcode.hxx
+++ b/include/formula/opcode.hxx
@@ -301,6 +301,10 @@ enum OpCodeEnum
         ocSubstitute        = SC_OPCODE_SUBSTITUTE,
         ocRept              = SC_OPCODE_REPT,
         ocConcat            = SC_OPCODE_CONCAT,
+        ocLenB              = SC_OPCODE_LENB,
+        ocRightB            = SC_OPCODE_RIGHTB,
+        ocLeftB             = SC_OPCODE_LEFTB,
+        ocMidB              = SC_OPCODE_MIDB,
         ocNumberValue       = SC_OPCODE_NUMBERVALUE,
     // Matrix functions
         ocMatValue          = SC_OPCODE_MAT_VALUE,
diff --git a/sc/CppunitTest_sc_ucalc.mk b/sc/CppunitTest_sc_ucalc.mk
index acf7bdb93401..dd1e64de4591 100644
--- a/sc/CppunitTest_sc_ucalc.mk
+++ b/sc/CppunitTest_sc_ucalc.mk
@@ -26,6 +26,10 @@ $(eval $(call gb_CppunitTest_use_externals,sc_ucalc,\
 	mdds_headers \
 	orcus \
 	orcus-parser \
+    icu_headers \
+    icudata \
+    icui18n \
+    icuuc \
 ))
 
 $(eval $(call gb_CppunitTest_use_libraries,sc_ucalc, \
diff --git a/sc/Library_sc.mk b/sc/Library_sc.mk
index 94ad19e17aec..189d7e2bab5c 100644
--- a/sc/Library_sc.mk
+++ b/sc/Library_sc.mk
@@ -38,6 +38,10 @@ $(eval $(call gb_Library_use_sdk_api,sc))
 $(eval $(call gb_Library_use_externals,sc,\
 	boost_headers \
 	mdds_headers \
+    icu_headers \
+    icudata \
+    icui18n \
+    icuuc \
 ))
 
 ifeq ($(ENABLE_TELEPATHY),TRUE)
diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index 15086f9e7ed8..2b2d5f7ebf16 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -665,6 +665,10 @@
 #define HID_FUNC_LINKS                                          "SC_HID_FUNC_LINKS"
 #define HID_FUNC_RECHTS                                         "SC_HID_FUNC_RECHTS"
 #define HID_FUNC_TEIL                                           "SC_HID_FUNC_TEIL"
+#define HID_FUNC_LENB                                           "SC_HID_FUNC_LENB"
+#define HID_FUNC_RIGHTB                                         "SC_HID_FUNC_RIGHTB"
+#define HID_FUNC_LEFTB                                          "SC_HID_FUNC_LEFTB"
+#define HID_FUNC_MIDB                                           "SC_HID_FUNC_MIDB"
 #define HID_FUNC_WIEDERHOLEN                                    "SC_HID_FUNC_WIEDERHOLEN"
 #define HID_FUNC_WECHSELN                                       "SC_HID_FUNC_WECHSELN"
 #define HID_FUNC_BASIS                                          "SC_HID_FUNC_BASIS"
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index 9c9977fd9c09..686521d3e245 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -5369,14 +5369,18 @@ void Test::testFunctionLists()
         "FIXED",
         "JIS",
         "LEFT",
+        "LEFTB",
         "LEN",
+        "LENB",
         "LOWER",
         "MID",
+        "MIDB",
         "NUMBERVALUE",
         "PROPER",
         "REPLACE",
         "REPT",
         "RIGHT",
+        "RIGHTB",
         "ROMAN",
         "SEARCH",
         "SUBSTITUTE",
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 81af5a1c7ff2..102b9b017b3b 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -788,6 +788,10 @@ void ScSTEXY();
 void ScSlope();
 void ScTrend();
 void ScInfo();
+void ScLenB();
+void ScRightB();
+void ScLeftB();
+void ScMidB();
 
 static const double fMaxGammaArgument;
 
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 0ff740c1d289..c4960c5e1194 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -34,6 +34,7 @@
 #include <unotools/transliterationwrapper.hxx>
 #include <rtl/ustring.hxx>
 #include <rtl/logfile.hxx>
+#include <unicode/uchar.h>
 
 #include "patattr.hxx"
 #include "global.hxx"
@@ -8620,6 +8621,182 @@ void ScInterpreter::ScLeft()
     }
 }
 
+typedef struct {
+    UBlockCode from;
+    UBlockCode to;
+} UBlockScript;
+
+static UBlockScript scriptList[] = {
+    {UBLOCK_HANGUL_JAMO, UBLOCK_HANGUL_JAMO},
+    {UBLOCK_CJK_RADICALS_SUPPLEMENT, UBLOCK_HANGUL_SYLLABLES},
+    {UBLOCK_CJK_COMPATIBILITY_IDEOGRAPHS,UBLOCK_CJK_RADICALS_SUPPLEMENT },
+    {UBLOCK_IDEOGRAPHIC_DESCRIPTION_CHARACTERS,UBLOCK_CJK_COMPATIBILITY_IDEOGRAPHS},
+    {UBLOCK_CJK_COMPATIBILITY_FORMS, UBLOCK_CJK_COMPATIBILITY_FORMS},
+    {UBLOCK_HALFWIDTH_AND_FULLWIDTH_FORMS, UBLOCK_HALFWIDTH_AND_FULLWIDTH_FORMS},
+    {UBLOCK_CJK_UNIFIED_IDEOGRAPHS_EXTENSION_B, UBLOCK_CJK_COMPATIBILITY_IDEOGRAPHS_SUPPLEMENT},
+    {UBLOCK_CJK_STROKES, UBLOCK_CJK_STROKES}
+};
+#define scriptListCount sizeof (scriptList) / sizeof (UBlockScript)
+bool SAL_CALL lcl_getScriptClass(sal_uInt32 currentChar)
+{
+    // for the locale of ja-JP, character U+0x005c and U+0x20ac should be ScriptType::Asian
+    if( (currentChar == 0x005c || currentChar == 0x20ac) &&
+          (MsLangId::getSystemLanguage() == LANGUAGE_JAPANESE) )
+        return true;
+    sal_uInt16 i;
+    static sal_Int16 nRet = 0;
+    UBlockCode block = (UBlockCode)ublock_getCode((sal_uInt32)currentChar);
+    for ( i = 0; i < scriptListCount; i++) {
+        if (block <= scriptList[i].to) break;
+    }
+    nRet = (i < scriptListCount && block >= scriptList[i].from);
+    return nRet;
+}
+bool IsDBCS(sal_Unicode ch)
+{
+    return lcl_getScriptClass(ch);
+}
+sal_Int32 getLengthB(String &str)
+{
+    sal_Int32 index = 0;
+    sal_Int32 length = 0;
+    if(0 == str.Len())
+        return 0;
+    while(index < str.Len()){
+        if(IsDBCS(str.GetChar(index)))
+            length += 2;
+        else
+            length++;
+        index++;
+    }
+    return length;
+}
+void ScInterpreter::ScLenB()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "luzhang", "ScInterpreter::ScLenB" );
+    String aStr( GetString() );
+    PushDouble( getLengthB(aStr) );
+}
+void lcl_RightB(String &aStr, sal_Int32 n)
+{
+    if( n < getLengthB(aStr) )
+    {
+        sal_Int32 index = aStr.Len();
+        while(index-- >= 0)
+        {
+            if(0 == n)
+            {
+                aStr.Erase( 0, index + 1);
+                break;
+            }
+            if(-1 == n)
+            {
+                aStr.Erase( 0, index + 2 );
+                aStr.InsertAscii(" ", 0);
+                break;
+            }
+            if(IsDBCS(aStr.GetChar(index)))
+                n -= 2;
+            else
+                n--;
+        }
+    }
+}
+void ScInterpreter::ScRightB()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "luzhang", "ScInterpreter::ScRightB" );
+    sal_uInt8 nParamCount = GetByte();
+    if ( MustHaveParamCount( nParamCount, 1, 2 ) )
+    {
+        sal_Int32 n;
+        if (nParamCount == 2)
+        {
+            double nVal = ::rtl::math::approxFloor(GetDouble());
+            if ( nVal < 0.0 || nVal > STRING_MAXLEN )
+            {
+                PushIllegalArgument();
+                return ;
+            }
+            else
+                n = (xub_StrLen) nVal;
+        }
+        else
+            n = 1;
+        String aStr( GetString() );
+        lcl_RightB(aStr, n);
+        PushString( aStr );
+    }
+}
+void lcl_LeftB(String &aStr, sal_Int32 n)
+{
+    if( n < getLengthB(aStr) )
+    {
+        sal_Int32 index = -1;
+        while(index++ < aStr.Len())
+        {
+            if(0 == n)
+            {
+                aStr.Erase( index );
+                break;
+            }
+            if(-1 == n)
+            {
+                aStr.Erase( index - 1 );
+                aStr.InsertAscii(" ");
+                break;
+            }
+            if(IsDBCS(aStr.GetChar(index)))
+                n -= 2;
+            else
+                n--;
+        }
+    }
+}
+void ScInterpreter::ScLeftB()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "luzhang", "ScInterpreter::ScLeftB" );
+    sal_uInt8 nParamCount = GetByte();
+    if ( MustHaveParamCount( nParamCount, 1, 2 ) )
+    {
+        sal_Int32 n;
+        if (nParamCount == 2)
+        {
+            double nVal = ::rtl::math::approxFloor(GetDouble());
+            if ( nVal < 0.0 || nVal > STRING_MAXLEN )
+            {
+                PushIllegalArgument();
+                return ;
+            }
+            else
+                n = (xub_StrLen) nVal;
+        }
+        else
+            n = 1;
+        String aStr( GetString() );
+        lcl_LeftB(aStr, n);
+        PushString( aStr );
+    }
+}
+void ScInterpreter::ScMidB()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "luzhang", "ScInterpreter::ScMidB" );
+    if ( MustHaveParamCount( GetByte(), 3 ) )
+    {
+        double fAnz    = ::rtl::math::approxFloor(GetDouble());
+        double fAnfang = ::rtl::math::approxFloor(GetDouble());
+        String rStr( GetString() );
+        if (fAnfang < 1.0 || fAnz < 0.0 || fAnfang > double(STRING_MAXLEN) || fAnz > double(STRING_MAXLEN))
+            PushIllegalArgument();
+        else
+        {
+
+            lcl_LeftB(rStr, (xub_StrLen)fAnfang + (xub_StrLen)fAnz - 1);
+            sal_Int32 nCnt = getLengthB(rStr) - (xub_StrLen)fAnfang + 1;
+            lcl_RightB(rStr, nCnt>0 ? nCnt:0);
+            PushString(rStr);
+        }
+    }
+}
 
 void ScInterpreter::ScRight()
 {
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 1f789b96b333..60383f0fa5e2 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4174,6 +4174,10 @@ StackVar ScInterpreter::Interpret()
                 case ocGetPivotData     : ScGetPivotData();             break;
                 case ocJis              : ScJis();                      break;
                 case ocAsc              : ScAsc();                      break;
+                case ocLenB             : ScLenB();                     break;
+                case ocRightB           : ScRightB();                   break;
+                case ocLeftB            : ScLeftB();                    break;
+                case ocMidB             : ScMidB();                     break;
                 case ocUnicode          : ScUnicode();                  break;
                 case ocUnichar          : ScUnichar();                  break;
                 case ocBitAnd           : ScBitAnd();                   break;
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index d4bdc8643a11..337b97131c70 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -203,6 +203,10 @@ static const XclFunctionInfo saFuncTable_2[] =
     { ocIsLogical,          198,    1,  1,  V, { VR }, 0, 0 },
     { ocDBCount2,           199,    3,  3,  V, { RO, RR }, 0, 0 },
     { ocCurrency,           204,    1,  2,  V, { VR }, EXC_FUNCFLAG_IMPORTONLY, 0 },
+    { ocLeftB,              208,    1,  2,  V, { VR }, 0, 0 },
+    { ocRightB,             209,    1,  2,  V, { VR }, 0, 0 },
+    { ocMidB,               210,    3,  3,  V, { VR }, 0, 0 },
+    { ocLenB,               211,    1,  1,  V, { VR }, 0, 0 },
     { ocRoundUp,            212,    2,  2,  V, { VR }, 0, 0 },
     { ocRoundDown,          213,    2,  2,  V, { VR }, 0, 0 },
     { ocExternal,           255,    1,  MX, R, { RO_E, RO }, EXC_FUNCFLAG_IMPORTONLY, 0 }
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index 947b785230b6..c7c358ef3a66 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -439,10 +439,10 @@ static const FunctionData saFuncTableBiff3[] =
     { 0/*"FIND"*/,              "FINDB",                205,    205,    2,  3,  V, { VR }, 0 },
     { 0/*"SEARCH"*/,            "SEARCHB",              206,    206,    2,  3,  V, { VR }, 0 },
     { 0/*"REPLACE"*/,           "REPLACEB",             207,    207,    4,  4,  V, { VR }, 0 },
-    { 0/*"LEFT"*/,              "LEFTB",                208,    208,    1,  2,  V, { VR }, 0 },
-    { 0/*"RIGHT"*/,             "RIGHTB",               209,    209,    1,  2,  V, { VR }, 0 },
-    { 0/*"MID"*/,               "MIDB",                 210,    210,    3,  3,  V, { VR }, 0 },
-    { 0/*"LEN"*/,               "LENB",                 211,    211,    1,  1,  V, { VR }, 0 },
+    { "LEFTB",                  "LEFTB",                208,    208,    1,  2,  V, { VR }, 0 },
+    { "RIGHTB",                 "RIGHTB",               209,    209,    1,  2,  V, { VR }, 0 },
+    { "MIDB",                   "MIDB",                 210,    210,    3,  3,  V, { VR }, 0 },
+    { "LENB",                   "LENB",                 211,    211,    1,  1,  V, { VR }, 0 },
     { "ROUNDUP",                "ROUNDUP",              212,    212,    2,  2,  V, { VR }, 0 },
     { "ROUNDDOWN",              "ROUNDDOWN",            213,    213,    2,  2,  V, { VR }, 0 },
     { "ASC",                    "ASC",                  214,    214,    1,  1,  V, { VR }, 0 },
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index e0659020a144..87b9cdd70f8f 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -9608,6 +9608,130 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
             Text [ en-US ] = "The integer number of bits the value is to be shifted." ;
         };
     };
+    Resource SC_OPCODE_LENB
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Calculates length of a text string, with DBCS" ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_TEXT;
+            U2S( HID_FUNC_LENB );
+            1;  0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "text" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The text in which the length is to be determined." ;
+        };
+    };
+    Resource SC_OPCODE_RIGHTB
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Returns the last character or characters of a text,with DBCS" ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_TEXT;
+            U2S( HID_FUNC_RIGHTB );
+            2;  0;  1;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "text" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The text in which the end partial words are to be determined." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "number" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The number of characters for the end text." ;
+        };
+    };
+    Resource SC_OPCODE_LEFTB
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Returns the first character or characters of a text,with DBCS" ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_TEXT;
+            U2S( HID_FUNC_LEFTB );
+            2;  0;  1;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "text" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The text where the initial partial words are to be determined." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "number" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The number of characters for the start text." ;
+        };
+    };
+    Resource SC_OPCODE_MIDB
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Returns a partial text string of a text, with DBCS" ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_TEXT;
+            U2S( HID_FUNC_MIDB );
+            3;  0;  0;  0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "text" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The text in which partial words are to be determined." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "start" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The position from which the part word is to be determined." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "number" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "The number of characters for the text." ;
+        };
+    };
 };
 
 #if defined(U2S)