Page 1 of 1

[Solved] Extract data from a sheet with cmd

Posted: Fri Jul 19, 2019 4:54 pm
by Marck_97
Hello everyone,
I want to ask if is possible to extract the value of a cell in a sheet with cmd ( or windows command shell).
My goal is to write a program that can "read" the value in a cell and thereafter open other programs but I need a way to read the value in the calc sheet!
Waiting for an answer,
Wish u the best.
Marck

Re: Extract data from a sheet with cmd

Posted: Fri Jul 19, 2019 7:58 pm
by RusselB
Quite simply this isn't possible with Calc.
The command line interface (cmd) does not have a compressed file extraction command and OpenOffice documents, when saved in the default Open Document Format (.ods for spreadsheets) are compressed files.

Re: Extract data from a sheet with cmd

Posted: Fri Jul 19, 2019 9:31 pm
by Kovlev
If you have access to some advanced shell, or programmable environment (python for example) this can be done with only a few lines of code, and will be callable from cmd:

Code: Select all

import pyexcel_ods

FILENAME = "test.ods"
SHEETNAME = "Sheet1"
ROW = 0
COLUMN = 1

print(pyexcel_ods.get_data(FILENAME)[SHEETNAME][ROW][COLUMN])
To use this, you will need python and the pyexcel_ods package.

Re: Extract data from a sheet with cmd

Posted: Sat Jul 20, 2019 12:22 pm
by Zizi64
When the Open/Libreoffice is present on the PC, then you can launch a AOO/LO macro from the command line:
viewtopic.php?f=20&t=83099

And you can open an ODF file by the macro, and then you can get a cell and cell content from it by the same macro. I do not know if the macro can return with the cell value or not.

Re: Extract data from a sheet with cmd

Posted: Sat Jul 20, 2019 1:12 pm
by Marck_97
RusselB wrote:Quite simply this isn't possible with Calc.
The command line interface (cmd) does not have a compressed file extraction command and OpenOffice documents, when saved in the default Open Document Format (.ods for spreadsheets) are compressed files.
Tnx for the fast answer sir and for the answer itself, I see that others suggested to use macros or implement some library to make this doable, I think I will try it before do something else!
Zizi64 wrote:When the Open/Libreoffice is present on the PC, then you can launch a AOO/LO macro from the command line:
viewtopic.php?f=20&t=83099

And you can open an ODF file by the macro, and then you can get a cell and cell content from it by the same macro. I do not know if the macro can return with the cell value or not.
tnx for the answer u2 sir, I will try this way too but I think I will try first with python and then macros, still tnx for the answer man x)!
Kovlev wrote:If you have access to some advanced shell, or programmable environment (python for example) this can be done with only a few lines of code, and will be callable from cmd:

Code: Select all

import pyexcel_ods

FILENAME = "test.ods"
SHEETNAME = "Sheet1"
ROW = 0
COLUMN = 1

print(pyexcel_ods.get_data(FILENAME)[SHEETNAME][ROW][COLUMN])
To use this, you will need python and the pyexcel_ods package.
Ok sir, Tnx for the answer! this was the thing I was kinda lookin for.
I installed python and installed the pyexcel_ods packages but when I paste all this into a .bat file, This one tells me that the "import" functions doesnt exists and other errors.
Did I do something wrong with the installation of python or I must paste all these commands in the python shell?
Lookin for a reply, wish to u and others best regards.
Cheers,
Marck

Re: Extract data from a sheet with cmd

Posted: Sat Jul 20, 2019 1:16 pm
by Zizi64
I think the batch file must contain a command only for launching a python exacutable code file.

Re: Extract data from a sheet with cmd

Posted: Sat Jul 20, 2019 1:30 pm
by Kovlev
The contents of the batch file should be python3 getdata.py where getdata.py is the python file with the contents I posted above and should be in the same folder as the batch file.
If that does not work you can:
  • Replace python3 with python
  • If python is not in the PATH environmental variable, either add to it, or specify the full path to python, which will be something like C:/Program Files/ etc.etc /python or C:/Users/...

Re: Extract data from a sheet with cmd

Posted: Sat Jul 20, 2019 5:00 pm
by Villeroy
Using the Python resources of your OpenOffice installation:
Start script to start OpenOffice in listening mode and start a python script:

Code: Select all

@echo off

rem Init start parameters
set OOO_PATH="C:\Program Files (x86)\OpenOffice.org4\program\"
set OOO_APP=%OOO_PATH%"soffice.exe"
set OOO_PY=%OOO_PATH%"python.exe"
set OOO_PARAM=-headless -norestore
set OOO_SOCKET="-accept=socket,host=localhost,port=8100;urp"


rem Start OpenOffice.org
echo start "OpenOffice.org" %OOO_APP% %OOO_PARAM% %OOO_SOCKET% >>oo.log
start "OpenOffice.org" %OOO_APP% %OOO_PARAM% %OOO_SOCKET% 
%OOO_PY% MySript.py
Python script connecting to the listening office:

Code: Select all

import uno
stest = 'file:///C:/Test/test.ods'
iS = 0
iR = 0
iC = 0
localContext = uno.getComponentContext()
resolver = localContext.ServiceManager.createInstanceWithContext("com.sun.star.bridge.UnoUrlResolver", localContext )
ctx = resolver.resolve("uno:socket,host=localhost,port=8100;urp;StarOffice.ComponentContext" )
dtp = ctx.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop",ctx)
doc = dtp.loadComponentFromURL(stest, "_default", 0, ())
sh = doc.Sheets.getByIndex(iS)
c = sh.getCellByPosition(iC, iR)
print(c.getValue(), ' | ', c.getString(), ' | ', c.getFormula())

Re: Extract data from a sheet with cmd

Posted: Tue Jul 23, 2019 10:01 am
by Marck_97
I could use python and code it to extract value from open office calc, it worked really well.
I think now I will do my program with python instead of cmd just to put it simple.
So, tnx to everyone for this, my request was answered pretty well :D.
Cheers and best you all the best!
Marck