[Solved] Extract data from a sheet with cmd

Java, C++, C#, Delphi, ??? - Using the UNO bridges

[Solved] Extract data from a sheet with cmd

Postby Marck_97 » Fri Jul 19, 2019 4:54 pm

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
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
Open office 4.1.6
Marck_97
 
Posts: 8
Joined: Sat Jun 08, 2019 6:40 pm

Re: Extract data from a sheet with cmd

Postby RusselB » Fri Jul 19, 2019 7:58 pm

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5589
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Extract data from a sheet with cmd

Postby Kovlev » Fri Jul 19, 2019 9:31 pm

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   Expand viewCollapse view
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.
LibreOffice 5 on KDE Neon
Kovlev
 
Posts: 5
Joined: Wed Apr 17, 2013 10:29 am

Re: Extract data from a sheet with cmd

Postby Zizi64 » Sat Jul 20, 2019 12:22 pm

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.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.3.2; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8464
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Extract data from a sheet with cmd

Postby Marck_97 » Sat Jul 20, 2019 1:12 pm

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   Expand viewCollapse view
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
Open office 4.1.6
Marck_97
 
Posts: 8
Joined: Sat Jun 08, 2019 6:40 pm

Re: Extract data from a sheet with cmd

Postby Zizi64 » Sat Jul 20, 2019 1:16 pm

I think the batch file must contain a command only for launching a python exacutable code file.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.3.2; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8464
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Extract data from a sheet with cmd

Postby Kovlev » Sat Jul 20, 2019 1:30 pm

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/...
LibreOffice 5 on KDE Neon
Kovlev
 
Posts: 5
Joined: Wed Apr 17, 2013 10:29 am

Re: Extract data from a sheet with cmd

Postby Villeroy » Sat Jul 20, 2019 5:00 pm

Using the Python resources of your OpenOffice installation:
Start script to start OpenOffice in listening mode and start a python script:
Code: Select all   Expand viewCollapse view
@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   Expand viewCollapse view
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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27295
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Extract data from a sheet with cmd

Postby Marck_97 » Tue Jul 23, 2019 10:01 am

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
Open office 4.1.6
Marck_97
 
Posts: 8
Joined: Sat Jun 08, 2019 6:40 pm


Return to External Programs

Who is online

Users browsing this forum: No registered users and 2 guests