In some powershell scripts I have written, I can create an object of Excel.Application and set it to visible, then change some cell values after opening a workbook. Can I do the same in LO Calc, via Java? I’m hoping there is an API that does just this, but I have been unable to find anything. What I have been doing now, which is not elegant, is that I use a macro that runs on opening a file. Java copies something like “123;456;789;234;567” to the clipboard and when the calc file opens the macro splits this data and pastes it where I needs it to go; again not elegant. Since the workbook is protected I left specific cells unlocked to allow for this data manipulation and the cutting and pasting changes cell formatting too, so I had to account for that in the macro. The powershell code to manipulate the data in excel is much shorter than the macro for calc and works 100% of the time, the issue with my macro is that it depends on the clipboard and LO Calc opens not as quickly as excel, so if the user copies ANYTHING before the macro pulls the essential data, it fails to run properly. As such I don’t want to depend on the clipboard and I want to minimize human error.
I know macros can be in Java. Is this for a macro? What I am looking to do is have an app that users input some data, the app uses the Apache POI to pull data from other sources and then plugs them into a calc workbook for the user to see. There are plenty of complex ways of doing it, but if there is an easier way I’ll take it.
To add some extra info, the data is a site id which the java app resolve some IP info and then plugs it into a list of 200+ IPs for the site;eg,second octet is 186
so cell B4 has a formula as =“12.”&B2&".38.110" which pulls the octet from B4 that was supplied to the spreadsheet using the Java app. I can use the Apache POI to write set the values of cells B4-B256, but one, that would be a lot of typing and two, a larger JAR (or separate one) to write.