I am trying to write ~1000 cells via python macro. It takes a good amount of time to run. Is there some way to update all of them at once? Maybe pause rendering until everything is set? Maybe multiprocessing to update 10 or so at a time?
Ultimately the hope is to actively update these cells every second or so. Gathering the data takes less than a second when not writing the values to the cells. I’m just wondering if there was a way to do this natively in Calc.
Hallo
Without knowing the context, without your current python code, wihout some example.ods… we can’t do anything for you…sorry
Have you tried turning off Autocalcular.
It depends.
Suppopsing you update the cells one by one, and need to get access to every single cell first via spredsheet.getCellByPosition or similar, this will consume a lot of time (even if not every action causes recalculations).
If the (about) 1000 cells are cells of a single CellRange object you can perform an assignment to a variable (say myCopy) using range.getDataArray(), assign your calculated values to the elements of that copy, and finally do a range.setDataArray(myCopy). This is faster by a high factor.
Be sure to understand that you can’t assign values directly to the elements of the range.DataArray “in situ” without using a copy.
Are you saying that the getCellByPosition(or similar) is what takes the majority of the processing time? Is there a more efficient way to navigate the cells like nextRow or something?
The other issue is I’m trying to set formatting, value, and add row grouping. Essentially I’m creating a tree of data that I can fold and navigate. The tree contains multiple text labels as branches and the continuously updating values as leaves. The location and depth of each leaf varies. I’ll take a look at creating a copy of the data array and pasting it over the desired range. I really would only need to copy the branches once then update the leaves later.
I hadn’t yet. It doesn’t seem to change. I don’t know if it matters but I’m not setting the values of the cells to a formula, I am directly setting their value.
I’m still saying what I said already. In different words:
Essentially the efficiency depends on whether you can (and do) use the DataArray or not.
If you need to treat every single cell for whatever reasons, there won’t be a spell speeding it up by a factor of (say) 11.
If you want to get advice concerning your “tree” and “folding” or whatever, ask new questions and provide the needed information.
Concerning the “factort 11” try yourself. I made an example file for you.
Loads a new spreadsheet and dumps ~2000 configuration values in a few seconds.
import uno
def getLocaleString(oL):
s = '-'
a = [oL.Language]
if oL.Country:a.append(oL.Country)
if oL.Variant:a.append(oL.Variant)
return s.join(a)
def printAllLocalesToNewSpreadSheet():
ctx = uno.getComponentContext()
smgr = ctx.ServiceManager
StarDesktop = smgr.createInstanceWithContext("com.sun.star.frame.Desktop", ctx)
oDoc = StarDesktop.loadComponentFromURL("private:factory/scalc","_default",0,tuple())
oNF = oDoc.getNumberFormats()
oSheet = oDoc.getSheets().getByIndex(0)
iEndCol = 255 # too much: oSheet.RangeAddress.EndColumn
i18n = smgr.createInstance("com.sun.star.i18n.LocaleData")
a = i18n.getAllInstalledLocaleNames()
cCols = 7
r = list()
r.append(("Locale","Language","Country","Decimal","Date","Time","1000","List"))
for i in a:
oInfo = i18n.getLanguageCountryInfo(i)
oItem = i18n.getLocaleItem(i)
b = (
getLocaleString(i),
oInfo.LanguageDefaultName,
oInfo.CountryDefaultName,
oItem.decimalSeparator,
oItem.dateSeparator,
oItem.timeSeparator,
oItem.thousandSeparator,
oItem.listSeparator
)
r.append(b)
rc = len(r)
nf = oNF.getStandardIndex(i)
oSheet.getCellRangeByPosition(0, rc-1, iEndCol, rc-1).NumberFormat = nf
oSheet.getCellRangeByPosition(0, 0, cCols, rc-1).setDataArray(tuple(r))
g_exportedScripts = (printAllLocalesToNewSpreadSheet,)