[Solved] Extract data from a sheet with cmd

[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

[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.

[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.

[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.

[Marck_97]

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!

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)!

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

[Zizi64]

I think the batch file must contain a command only for launching a python exacutable code file.

[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/...

[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())

[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 .
Cheers and best you all the best!
Marck