[Solved] Extract data from a sheet with cmd
[Solved] Extract data from a sheet with cmd
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
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
Last edited by MrProgrammer on Thu Aug 01, 2019 7:28 pm, edited 3 times in total.
Reason: Moved from Calc forum to Expernal Programs
Reason: Moved from Calc forum to Expernal Programs
Open office 4.1.6
Re: Extract data from a sheet with cmd
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.
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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Extract data from a sheet with cmd
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:
To use this, you will need python and the pyexcel_ods package.
Code: Select all
import pyexcel_ods
FILENAME = "test.ods"
SHEETNAME = "Sheet1"
ROW = 0
COLUMN = 1
print(pyexcel_ods.get_data(FILENAME)[SHEETNAME][ROW][COLUMN])
LibreOffice 5 on KDE Neon
Re: Extract data from a sheet with cmd
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.
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.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Extract data from a sheet with cmd
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!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 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)!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.
Ok sir, Tnx for the answer! this was the thing I was kinda lookin for.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:To use this, you will need python and the pyexcel_ods package.Code: Select all
import pyexcel_ods FILENAME = "test.ods" SHEETNAME = "Sheet1" ROW = 0 COLUMN = 1 print(pyexcel_ods.get_data(FILENAME)[SHEETNAME][ROW][COLUMN])
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
Open office 4.1.6
Re: Extract data from a sheet with cmd
I think the batch file must contain a command only for launching a python exacutable code file.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Extract data from a sheet with cmd
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:
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/...
LibreOffice 5 on KDE Neon
Re: Extract data from a sheet with cmd
Using the Python resources of your OpenOffice installation:
Start script to start OpenOffice in listening mode and start a python script:
Python script connecting to the listening office:
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
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())
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Extract data from a sheet with cmd
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
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
Open office 4.1.6