[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