It is working for Writer. Now please provide for Calc
There are some problems in Calc to keep the formatting in cells, algorithm from Writer tables failed. It seems it will take more days to solve it.
Take your time.
Your table conversion code has been made available as an extension
But this extension is unreliable now, because the arrays OriginalStrings and TargetStrings aren’t sorted according to length(s)
And it is problem! Because the replacements in arrays have different lengths, so for Kruti2Unicode it need the length-sorting according to lengths in TargetStrings, but for Unicode2Kruti according to lengths in OriginalStrings. Really good IQ test :-)! And really convenient for TreeSort, I will try to solve it with TreeSort.
What did I miss?
Is there ONE realistic short example typed/created in kruti dev?
I still also can’t find resonable explanations. There are “online services” and all sorts of suggestions or recommendations, but neither the examples nor the information.
Is kruti dev the secret of the ages?
2nd prototype for Writer, it should be functional also for normal selections in normal text and also for multiselections.
ind-find-replace-in-cells-KL2.odt (49.0 kB)
I removed duplicities from F&R arrays and sorted ones according to lengths, but the conversion still isn’t 100% reliable now 
.
F&R in Calc are still problematic
Not working at all.
It really easy to write only:
But where it doesn’t work? In some cases in my randomly created ODT? Or in some your documents? Where and at what activity?
Nowhere. I tried to type some kruti dev and some unicode text in your document. Then select it and try to run macro but it is not converting. I tried it in cells and also tried it in outside the text. After opening your document it is showing error "Please install the hyphenation package for locale “hi-IN”. Does it has something to do with the functioning of the macro.
Did you run the macro(s) KrutiUnicode or UnicodeKruti? Because it is fully functional for me
The message with hyphenation package is because you didn’t upload example ODT with KrutiDev and Unicode, so I randomly copied some parts from substitution-arrays and selected lines with ones as Hindu language with the font Lohit Devanagari (because without this font it wasn’t well-visible for me). But it hasn’t influence on macro running.
Edit: I discovered the conversion isn’t 100% reliable in prototype 2 :-(, sometimes there are some problems after conversion for Kruti2Unicode and then back Unicode2Kruti.
There are the collisions in substitution table! It is about 70 collisions that causes the bad transformation.
conversion-table.ods (33.9 kB)
Run the macro Module2 / findProblems from ODS to see the lines and Ascii codes of problematic substitutions. Then it need fix all these problematic substitutions.
It is working. You need to select Unicode text and choose the macro replaceInWriter macro. It will convert the Unicode text to Krutidev. But KrutiUnicode or UnicodeKruti macros are not working.
Install the second version of the extension. It is easier to use F4 (or Shift + F4) shortcut key that is linked to the correct macro name. Please improve the mapping.
It is strange, both macros are functional for me. You can try also the macros Lang1to2 and Lang2to1.
Because there are a more fonts on internet for KrutiDev like KrutiDev 010, KrutiDev 011, KrutiDev 055, so I suppose every this kind of KrutiDev has some differences in substitution table → and KrutiDev 010 probably has partialy other mapping than for example KrutiDev 055. And if there are some differences, then there should be the differentiation for what version of KrutiDev the conversion is, like Kruti055Unicode or UnicodeKruti055.
At the time of replacement, we know the font name. If we have several replacement tables (dictionaries), then we can use a single macro.
I cogitated the solution could be to have more TXT files with more replacement tables and add some dialog with listbox what file to use for current conversion. But maybe later.
Here is version 3 (whole Module from Basic editor), use the macros Lang1to2 or Lang2to1. It should be functional for selection(s) in Writer and also for selected cells in Calc. But I used only simple conversion of some letters and numbers for testing, because proper KrutiDev replacement table still isn’t created.
option explicit
Sub Lang1to2 'conversion from 1st to 2nd language
replaceText(false)
End Sub
Sub Lang2to1 'conversion from 2nd to 1st language
replaceText(true)
End Sub
rem ----------------------------------------------------------------
Type tLangs
sLang1$ sLang2$ arrLang1() arrLang2()
End Type
global CONVERSIONDATA as tLangs
rem !!! initSubstitutions is generated by the macro: conversion-table.ods > ShowMacroCode !!!
rem ↓↓↓↓↓ ---------------------------------------------------
Sub initSubstitutions
dim letters2numbers()
letters2numbers = Array( _
array("ddd", "7"), array("ccc", "5"), array("dd", "6"), array("bb", "3"), array("aa", "1"), array("e", "9"), array("d", "8"), array("c", "4"), _
array("b", "2"), array("a", "0") )
dim numbers2letters()
numbers2letters = Array( _
array("9", "e"), array("8", "d"), array("7", "ddd"), array("6", "dd"), array("5", "ccc"), array("4", "c"), array("3", "bb"), array("2", "b"), _
array("1", "aa"), array("0", "a") )
with CONVERSIONDATA
.sLang1="letters"
.sLang2="numbers"
.arrLang1=letters2numbers()
.arrLang2=numbers2letters()
end with
End Sub
rem ↑↑↑↑↑ ---------------------------------------------------
Sub replaceText(optional b2to1 as boolean) 'replace the text in selection(s)
if isMissing(b2to1) then b2to1=false 'default is conversion from lang 1 to 2
const bDebug=false 'TRUE turns off the .lockControllers and UndoManager
on local error goto bug
const cBound=30 'initial length for array with length of same-formatted parts of text in Calc's cell to speed up the Redim preserve
dim oDoc as object, oSel as object, i&, undoMgr as object, j&, OriginalStrings(), TargetStrings(), sRange$, sTable$, oTable as object, oRange as object, _
oCur as object, data(), sCell$, sCellStart$, sCellEnd$, arr(), iStartRow&, iStartColumn&, iEndRow&, iEndColumn&, oCell as object, calc as object, _
o as object, o2 as object, k&, s$, oStart as object, oEnd as object, oCur2 as object, iLen&, s1$, oStatusbar as object, iStatus&, sUndo$, sLang1$, sLang2$, _
arrLang1(), arrLang2(), iArr&, iBound&, iLen0&, iLen1&, iLen2&
dim replArray() 'array used for conversion
if ubound(CONVERSIONDATA.arrLang1)=-1 then initSubstitutions 'initialization of data for conversion
arrLang1=CONVERSIONDATA.arrLang1 : arrLang2=CONVERSIONDATA.arrLang2
sLang1=CONVERSIONDATA.sLang1 : sLang2=CONVERSIONDATA.sLang2
if b2to1 then 'convert 2nd lang to 1st
replArray=CONVERSIONDATA.arrLang2 'Unicode to KrutiDev
sUndo=sLang2 & " → " & sLang1
else '1st lang to 2nd
replArray=CONVERSIONDATA.arrLang1 'KrutiDev to Unicode
sUndo=sLang1 & " → " & sLang2
end if
if ubound(arrLang1)<>ubound(arrLang2) then 'test for same sizes of F&R arrays
msgbox("There is different count of items in OrginalStrings and TargetStrings!", 16)
exit sub
end if
oDoc=ThisComponent
oStatusbar=oDoc.CurrentController.StatusIndicator 'progress in statusbar
oSel=oDoc.CurrentController.Selection 'current selection (only for one selection, no multiselection via holding by Ctrl)
if NOT bDebug then
oDoc.lockControllers 'turn off the screen rendering (it is faster)
undoMgr=oDoc.UndoManager 'undo manager
undoMgr.enterUndoContext(sUndo) 'only one step in Undo
end if
rem CALC
if oDoc.supportsService("com.sun.star.sheet.SpreadsheetDocument") then 'Calc
oRange=oSel.queryContentCells(7) 'only cells with content (type of content: com.sun.star.sheet.CellFlags)
iStatus=ubound(oRange.Data) 'count of selected cells to progressbar in Statusbar
oStatusbar.start("", iStatus)
iStatus=0 'counter for replaced cells for Statusbar
rem traverse the cells in range
for each oCell in oRange.Cells
iArr=-1
redim arr(cBound)
for each o in oCell 'enumerate the parts of texts with different formatting (like bold, italic etc.)
for each o2 in o
s=o2.String
iArr=iArr+1
arr(iArr)=Len(s) 'put length of same-formatted part to array
ibound=ubound(arr)
if iArr=ibound then 'increase the array with lengths
redim preserve arr(ibound+cBound)
end if
next
next
redim preserve arr(iArr) 'truncate array to real size
rem replace in cell
oCur2=oCell.createTextCursor
iLen0=0
for i=lbound(arr) to ubound(arr)
with oCur2
.gotoStart(false)
.goRight(iLen0, false) 'cursor before current part
.goRight(arr(i), true) 'cursor is current part
s1=.String 'string from current part
end with
iLen1=Len(s1) 'length of found
s=replString(s1, replArray) 'convert the string
if s<>"" then 'there was replacement so set replaced string
iLen2=Len(s) 'length of replacement
oCur2.collapseToStart
if iLen1=1 then 'there is only 1 character in current part so double one and put cursor between these two letters
with oCur2
.goRight(1, false) 'cursor is after single character
.String=s1 'double the single character
.collapseToStart() 'cursor is between 2 characters
end with
else 'let only 1st and last character of current part and cursor between ones
with oCur2
.goRight(1, false) 'cursor is after 1st characters
.goRight(iLen1-2, true) 'cursor is before last character
.String="" 'cursor is between 2 characters
end with
end if
with oCur2
.String=s 'insert replacement between two characters (it keeps formatting)
rem delete 1st character
.collapseToEnd()
.goRight(1, true)
.String=""
rem delete last character
.goLeft(iLen2, false)
.goLeft(1, true)
.String=""
end with
end if
iLen0=iLen0 + iLen2
next i
iStatus=iStatus+1
oStatusbar.setValue(iStatus) 'update Statusbar
next
rem WRITER
elseif oDoc.supportsService("com.sun.star.text.TextDocument") then
if oSel.supportsService("com.sun.star.text.TextTableCursor") then 'cells in TextTable are selected
sRange=oSel.getRangeName 'remeber the range of selected cells
arr=Split(sRange, ":")
if ubound(arr)=1 then 'more cells are selected
sCellStart=arr(0)
sCellEnd=arr(1)
else 'only one cell is selected
sCellStart=sRange
sCellEnd=sRange
sRange=sRange & ":" & sRange
end if
calc=CreateUnoService("com.sun.star.sheet.FunctionAccess")
iStartRow=CLng( calc.callFunction("REGEX", array(sCellStart, "\d+") ) ) 'index of row of 1st cell
iStartRow=iStartRow-1
iEndRow=CLng( calc.callFunction("REGEX", array(sCellEnd, "\d+") ) ) 'index of row of last cell
iEndRow=iEndRow-1
uno("Escape", oDoc) : wait(20) 'deselect cells to detect the table
oSel=oDoc.CurrentSelection.getByIndex(0) 'there should be the normal visible cursor in 1st selected cell
oTable=oSel.TextTable 'current table
oRange=oTable.getCellRangeByName(sRange) 'selected primal range
arr=oTable.CellNames()
for i=lbound(arr) to ubound(arr) 'find the name of 1st selected cell
if arr(i)=sCellStart then exit for
next i
iStartColumn=i MOD oTable.Columns.Count 'index of column of 1st cell
data=oRange.getDataArray()
iEndColumn=iStartColumn+ubound(data(0)) 'index of column of last cell
iStatus=(iEndColumn-iStartColumn) * (iEndRow-iStartRow) 'count of replaced cells to progressbar in Statusbar
oStatusbar.start("", iStatus)
iStatus=0 'counter for replaced cells for Statusbar
rem traverse the cells in range or selections
for i=iStartColumn to iEndColumn
for j=iStartRow to iEndRow
oCell=oTable.getCellByPosition(i, j)
sCell=oCell.String 'string in cell to replace
oCur=oCell.createTextCursor() 'text cursor in cell
oCur.gotoEnd(true)
replCur(oCur, replArray, oCell) 'replacement in TextCursor in cell
iStatus=iStatus+1
oStatusbar.setValue(iStatus) 'update Statusbar
next j
next i
elseif oSel.supportsService("com.sun.star.text.TextRanges") then 'normal text ranges are selected
dim oPart as object
iStatus=oSel.Count
oStatusbar.start("", iStatus-1)
for i=0 to iStatus-1
oPart=oSel.getByIndex(i)
if oPart.supportsService("com.sun.star.text.TextRange") then 'current part of selection it is really TextRange
if isEmpty(oPart.Cell) then 'selection is in normal text
oCell=oDoc.Text 'standard TextCursor
else 'selection is inside some cell
oCell=oPart.Cell 'cell TextCursor
end if
oCur=oCell.createTextCursor() 'TextCursor for selection
with oCur
.gotoRange(oPart.Start, false)
.gotoRange(oPart.End, true)
end with
replCur(oCur, replArray, oCell) 'replacement in TextCursor
end if
iStatus=iStatus+1
oStatusbar.setValue(iStatus) 'update Statusbar
next i
end if
else 'no Writer nor Calc
exit sub
end if
if NOT bDebug then undoMgr.leaveUndoContext(sUndo)
with oStatusbar
.end
.reset
end with
oDoc.CurrentController.select(oRange) 'select again
goto konec
bug:
bug(Erl, Err, Error, "ReplaceInCells")
konec:
if oDoc.hasControllersLocked then oDoc.unlockControllers 'turn on the screen rendering
End Sub
Function replCur(oCur as object, replArray(), oSel as object) 'replacement in TextCursor
on local error goto bug
dim o as object, o2 as object, s$, s1$, oCur2 as object, iLen2&, oEnd as object, iLen1&
oCur2=oSel.createTextCursor() 'text cursor in current selection
for each o in oCur 'enumerate the parts of texts with different formatting (like bold, italic etc.)
for each o2 in o
s1=o2.String 'text from current part
iLen1=Len(s1) 'length of current part
s=replString(s1, replArray) 'convert the string
if s<>"" then 'there was replacement so set replaced string
oCur2.gotoRange(o2.Start, false)
if iLen1=1 then 'there is only 1 character in current part so double one and put cursor between these two letters
with oCur2
.goRight(1, false) 'cursor is after single character
.String=s1 'double the single character
.collapseToStart() 'cursor is between 2 characters
end with
else 'let only 1st and last character of current part and cursor between ones
with oCur2
.goRight(1, false) 'cursor is after 1st characters
.goRight(iLen1-2, true) 'cursor is before last character
.String="" 'cursor is between 2 characters
end with
end if
iLen2=Len(s) 'length of replacement
with oCur2 'cursor is between two letters now
.String=s 'insert replacement between two characters (it keeps formatting)
rem delete 1st character
.collapseToEnd()
.goRight(1, true)
.String=""
rem delete last character
.goLeft(iLen2, false)
.goLeft(1, true)
.String=""
end with
end if
next
next
exit function
bug:
bug(Erl, Err, Error, "replCur")
End Function
Function replString(ByVal s$, arr()) as string 'replace in string according to pairs in arr()
on local error goto bug
dim i&, iPos&, sFind$, bFound as boolean, oSearch, oSearchParam, oFound, oStart, oEnd, sOut$, iStart&, iEnd&
oSearch=CreateUnoService("com.sun.star.util.TextSearch")
oSearchParam=CreateUnoStruct("com.sun.star.util.SearchOptions")
With oSearchParam
.algorithmType=com.sun.star.util.SearchAlgorithms.ABSOLUTE 'REGEXP
End With
for i=lbound(arr) to ubound(arr) 'try replacements one by one
sFind=arr(i)(0)
oSearchParam.searchString=sFind
oSearch.setOptions(oSearchParam)
iPos=0 'search string from this position
do while true 'search all string for one replacement
oFound=oSearch.searchForward(s, iPos, len(s) )
if oFound.subRegExpressions<>0 then 'replace found place in string
bFound=true
oStart=oFound.startOffset() : iStart=oStart(0) 'start postion of found in string
oEnd=oFound.endOffset() : iEnd=oEnd(0) 'end position of ound in string
s=Left(s, iStart) & arr(i)(1) & Mid(s, iEnd+1)
iPos=iEnd 'new searching in string will be from position after last replacement
else
exit do
end if
loop
next i
if bFound then replString=s else replString="" 'return empty string if no replacement
exit function
bug:
bug(Erl, Err, Error, "replString")
End Function
Sub uno(s$, optional oDoc as object, optional args as variant) 'uno command
if isMissing(oDoc) then oDoc=thisComponent
if isMissing(args) then args=array()
s=".uno:" & s
createUnoService("com.sun.star.frame.DispatchHelper").executeDispatch(oDoc.CurrentController.Frame, s, "", 0, args)
End Sub
Sub bug(sErl$, sErr$, sError$, sFce$) 'show the error message
msgbox(sErr & ": " & sError & chr(13) & "Line: " & sErl & chr(13), 16, sFce)
stop
End Sub
Substitution table is whole Sub initSubstitutions that can be generated with the macro ShowMacroCode from conversion-table.ods (18.0 kB)
The following punctuation arrays are converted incorrectly.
array("]", ",")
array("-", ".")
array("¼", "(")
array("½", ")")
apart from the 4 arrays the / is also having it’s own problems.
original string:
izFke Js.kh]
¼U;k-dz-6½] vdksyk-
converted string:
प्रथम श्रेणीए
यन्याण्क्रण्६द्धए अकोलाण्
expected string:
प्रथम श्रेणी,
(न्या. क्र. ६), अकोला.
Update:
The only problem with this chat GPT generated macro is that it does not allow more than thousand values in the split array line.
There is serious bug in enumeration of content, so macro can do bad conversion
https://bugs.documentfoundation.org/show_bug.cgi?id=169135