Looking for a tool for spreadsheet manipulation.
Sometimes I get lucky here. ;-)
I have this horrible spreadsheet that needs to be accessed by lots of people
from all over the galaxy. Adding entries to the spreadsheet is painful because
it's manual.
What I'd like to do is to use a command line interface to add entries to cells
instead of having to use Excel. Does such a beast exist?
I'm thinking of some sort of a command that either takes command args or a
configuration file to accomplish what I want. For example:
I have a cell that says FooBar, and I want to add stuff to the next two
cells to the right of it. For example, an awk-like syntax to accomplish this
might look like:
cmd '/Foobar/ {set(row, col+1, abc); set(row, col+2, 123)}' fn.xls
What I really want to do is somewhat more involved, but this is a great jump
point to get me started. Anyone have any ideas?
TIA
--
Time flies like the wind. Fruit flies like a banana. Stranger things have .0.
happened but none stranger than this. Does your driver's license say Organ ..0
Donor?Black holes are where God divided by zero. Listen to me! We are all- 000
individuals! What if this weren't a hypothetical question?
steveo at syslang.net
___
gnhlug-discuss mailing list
gnhlug-discuss@mail.gnhlug.org
http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On Wed, Jan 19, 2011 at 11:06 AM, Steven W. Orr ste...@syslang.net wrote: What I'd like to do is to use a command line interface to add entries to cells instead of having to use Excel. Does such a beast exist? You mention Excel but this is a Linux list. What OS and application are you actually running? :) If this was a Windoze box and MS Exsmell, I'd probabbly use VBA (Visual Basic for Applications, AKA macros) embedded in the Excel spreadsheet. Google results suggest you can get command line arguments from VBA (Google Excel GetCommandLine). Another approach would be to use an external Visual Basic Script. VBS can take command line arguments and can interface with all the Office apps. If this is something else, let us know what. :) -- Ben ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On Wed, Jan 19, 2011 at 11:06 AM, Steven W. Orr ste...@syslang.net wrote: I have this horrible spreadsheet that needs to be accessed by lots of people from all over the galaxy. Adding entries to the spreadsheet is painful because it's manual. What I'd like to do is to use a command line interface to add entries to cells instead of having to use Excel. Does such a beast exist? I'm thinking of some sort of a command that either takes command args or a configuration file to accomplish what I want. For example: Some possible approaches: 1. Look into OpenOffice macros to achieve this. For example, I used this recipe in a Makefile to use OO to convert an RTF into MS Word. It calls a macro that I defined and stored in BriansLibrary: %.doc: %.rtf soffice -invisible macro:///BriansLibrary.Conversion.ConvertRtfToWord(`pwd`/$) You may be able to do something similar with oocalc macros. If you really need MS Excel, I'm not sure you can compatibly store macros in it and still port between oocalc and Excel. 2. Back when I had a windows box I used a combination of python+win32com to access Word files. You could do something similar -- with just about any language (VB, python, etc). An ancient example that I once used to change Word document titles: http://bstpierre.org/Projects/msword.py ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On 1/19/2011 11:06 AM, Steven W. Orr wrote:
Sometimes I get lucky here. ;-)
I have this horrible spreadsheet that needs to be accessed by lots of people
from all over the galaxy. Adding entries to the spreadsheet is painful because
it's manual.
What I'd like to do is to use a command line interface to add entries to cells
instead of having to use Excel. Does such a beast exist?
I'm thinking of some sort of a command that either takes command args or a
configuration file to accomplish what I want. For example:
I have a cell that says FooBar, and I want to add stuff to the next two
cells to the right of it. For example, an awk-like syntax to accomplish this
might look like:
cmd '/Foobar/ {set(row, col+1, abc); set(row, col+2, 123)}' fn.xls
What I really want to do is somewhat more involved, but this is a great jump
point to get me started. Anyone have any ideas?
TIA
I'm getting some good feedback, including letting me know what info I did not
provide.
The deal is that we are releasing software whose src code is properly tagged
(or labeled). There are *lots* of labels. The binaries are constructed and
released into a common area. Each set of binaries might be composed of src
rpms, binary rpms, tarballs, iso images, plus other things. Multiple binary
files can have an integer called a Build Solution and a release version
number. The collection of all these files go into a unique Staging Area where
users get the files. There is currently a spreadsheet which contains columns
of content description, version numbers and Build Solution numbers. Different
people contribute to different sections of the spreadsheet. There might be a
linux section, one for winbloze, plus other more specific target platforms.
Right now, everyone is using Excel from windows to add their entries. I don't
actually know if using anything else (OOO, gnumeric, etc,) would cause
unintended ripples to the files.
All the devel work that I deal with is done from linux, but I get there mostly
(currently) using W7, putty and cygwin X server.
Since I don't know ruby, the suggestion to try a python interface sounds
attractive.
If someone wanted to send me some python example code (hint hint) I'd really
love to look it over.
Does this help with any further suggestions?
--
Time flies like the wind. Fruit flies like a banana. Stranger things have .0.
happened but none stranger than this. Does your driver's license say Organ ..0
Donor?Black holes are where God divided by zero. Listen to me! We are all- 000
individuals! What if this weren't a hypothetical question?
steveo at syslang.net
___
gnhlug-discuss mailing list
gnhlug-discuss@mail.gnhlug.org
http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On Wed, Jan 19, 2011 at 12:17 PM, Steven W. Orr ste...@syslang.net wrote: On 1/19/2011 11:06 AM, Steven W. Orr wrote: Sometimes I get lucky here. ;-) I have this horrible spreadsheet that needs to be accessed by lots of people from all over the galaxy. Adding entries to the spreadsheet is painful because it's manual. users get the files. There is currently a spreadsheet which contains columns of content description, version numbers and Build Solution numbers. Different people contribute to different sections of the spreadsheet. There might be a linux section, one for winbloze, plus other more specific target platforms. Right now, everyone is using Excel from windows to add their entries. I don't actually know if using anything else (OOO, gnumeric, etc,) would cause unintended ripples to the files. All the devel work that I deal with is done from linux, but I get there mostly (currently) using W7, putty and cygwin X server. So you guys have a shared spreadsheet that everyone opens and updates? Let's move the foundation 3 feet to the right after you've built the house. I'd suggest you convert it into a database with a web interface. It can scale to more users and make it harder for users to corrupt each other's stuff. The builds could automatically update it as they build. Of course if not too many people use it, that might be as much of an issue. If you can put it into some kind of revision control or CMS that prevents multiple users editing the file at once, that mitigates corruption too. Spreadsheets are quick and easy to setup but hard to maintain. ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On Wed, Jan 19, 2011 at 12:17 PM, Steven W. Orr ste...@syslang.net wrote: Right now, everyone is using Excel from windows to add their entries. I don't actually know if using anything else (OOO, gnumeric, etc,) would cause unintended ripples to the files. All the devel work that I deal with is done from linux, but I get there mostly (currently) using W7, putty and cygwin X server. Tom's suggestion to move to a db backend with a web frontend is sound advice, but will require upfront effort and buy-in from other stakeholders to achieve. If you want to stay invisible and you're willing to run from the windows command line, see http://code.activestate.com/recipes/573471-update-stock-quote-using-yahoo-finance-web-service/ for an example of manipulating Excel spreadsheets using python and win32com. ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On 01/19/2011 12:17 PM, Steven W. Orr wrote: I'm getting some good feedback, including letting me know what info I did not provide. The deal is that we are releasing software whose src code is properly tagged (or labeled). There are*lots* of labels. The binaries are constructed and released into a common area. Each set of binaries might be composed of src rpms, binary rpms, tarballs, iso images, plus other things. Multiple binary files can have an integer called a Build Solution and a release version number. The collection of all these files go into a unique Staging Area where users get the files. There is currently a spreadsheet which contains columns of content description, version numbers and Build Solution numbers. Different people contribute to different sections of the spreadsheet. There might be a linux section, one for winbloze, plus other more specific target platforms. Right now, everyone is using Excel from windows to add their entries. I don't actually know if using anything else (OOO, gnumeric, etc,) would cause unintended ripples to the files. All the devel work that I deal with is done from linux, but I get there mostly (currently) using W7, putty and cygwin X server. Since I don't know ruby, the suggestion to try a python interface sounds attractive. If someone wanted to send me some python example code (hint hint) I'd really love to look it over. Does this help with any further suggestions? Thank you for the further clues! At the risk of invoking Benjamin Disraeli (when all you have is a hammer, everything looks like a nail), I'd suggest that the problem you have is a multi-user database application, and not a spreadsheet. It doesn't sound like you are using the spreadsheet for its intended purpose of organizing columns, rows and blocks of numbers and performing mathematical functions on them, rather you're using the spreadsheet as a table editor. A spreadsheet is also single-user (The document is current locked by user Bob. If you want to make a copy...) which could be a problem. You don't mention if this spreadsheet goes on to play some important role in the build or distribution process directly, or if it is just used as a reference document. If the former, you'd need to expand on what you do with it. If the latter, I'd suggest a dynamically-generated web page could present the results to all in a table, column and row format. And a multi-user web application has the advantages of requiring (any, platform-neutral) browser to use it, built-in row-level locking/contention mechanisms and the ability to add in functionality like reporting, auditing, role-based access control, etc., as the need arises. Direct command-line updates into a such a system could be as easy as scripting SQL scripts and submitting them to the database. Needless to say, I've simplified my answer as you simplified your question. A high-schooler should be able to whip up a LAMP app to do this in an afternoon. For us adults, it might take longer ... ;) Or you could just throw the spreadsheet into Google Apps, since they seem to have worked out the multi-user document sharing aspects pretty well. However, I don't know of a command-line interface to that! -- Ted Roche Ted Roche Associates, LLC http://www.tedroche.com ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On Wed, Jan 19, 2011 at 1:40 PM, Ted Roche tedro...@tedroche.com wrote: Or you could just throw the spreadsheet into Google Apps, since they seem to have worked out the multi-user document sharing aspects pretty well. However, I don't know of a command-line interface to that! Google provides an API for Google Spreadsheets: http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html and a python client library: http://code.google.com/p/gdata-python-client/ -Brian ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On 01/19/2011 01:51 PM, Brian St. Pierre wrote: Google provides an API for Google Spreadsheets: http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html and a python client library: http://code.google.com/p/gdata-python-client/ -Brian ___ Alternatively, instead of editing the document, you could edit a CSV text file, tab delimited text file, or database table and then generate your XLS/google doc document from that - which is likely your most flexible approach, and still preserves your ability to have your preferred output format. -- David Berube Berube Consulting http://berubeconsulting.com (603) 574-4766 ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
If you want to stay invisible and you're willing to run from the windows command line, see http://code.activestate.com/recipes/573471-update-stock-quote-using-yahoo-finance-web-service/ for an example of manipulating Excel spreadsheets using python and win32com. Your file sounds like a Release Eng. nightmare ;) Right now, python is my hammer for almost every nail. I have had a lot of success accessing/automating COM objects with python -and- *I* would definitely look at the win32com as a possible solution here. A couple things to consider: 1. There is a steep learning curve if you are not already familiar with COM. It can be frustrating getting a bunch of COM exceptions bubbled up through traceback stack, with no real explanation as to what went wrong. There are plenty of examples out on the interwebs, but be prepared to get dirty. 2. All your users will have to install python and win32 extensions if they don't already have them - which could require you to become a python helpdesk at your company. You could use py2exe to create a binary distributable for windows, but again you run the risk of being tech-support for your app on a bunch of different machines. If it is truly a distributed document, I would probably go with the other suggestions of Google Apps for the short term and work on a DB with a web-interface as a more permanent solution (or maybe it's time to rethink the build/delivery process). -- Joel Burtram ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On Wed, Jan 19, 2011 at 11:06 AM, Steven W. Orr ste...@syslang.net wrote: Sometimes I get lucky here. ;-) I have this horrible spreadsheet that needs to be accessed by lots of people from all over the galaxy. Adding entries to the spreadsheet is painful because it's manual. What I'd like to do is to use a command line interface to add entries to cells instead of having to use Excel. Does such a beast exist? I'm thinking of some sort of a command that either takes command args or a configuration file to accomplish what I want. For example: Doing it with Perl on a Windows system is *superduper* cake. -- -- Thomas ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On Wed, Jan 19, 2011 at 4:00 PM, Thomas Charron twaf...@gmail.com wrote: On Wed, Jan 19, 2011 at 11:06 AM, Steven W. Orr ste...@syslang.net wrote: Sometimes I get lucky here. ;-) I have this horrible spreadsheet that needs to be accessed by lots of people from all over the galaxy. Adding entries to the spreadsheet is painful because it's manual. What I'd like to do is to use a command line interface to add entries to cells instead of having to use Excel. Does such a beast exist? I'm thinking of some sort of a command that either takes command args or a configuration file to accomplish what I want. For example: Doing it with Perl on a Windows system is *superduper* cake. I'm in a meeting, but a quick example search of the OLE interfaces I came across: http://www.ngbdigital.com/perl_ole_excel.html This is exactly how I've been using it when automating some robotics tests where the 'test log' was stored in an Excel spreadsheet. -- -- Thomas ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On Wed, Jan 19, 2011 at 02:13:58PM -0500, David Berube wrote: On 01/19/2011 01:51 PM, Brian St. Pierre wrote: Google provides an API for Google Spreadsheets: http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html and a python client library: http://code.google.com/p/gdata-python-client/ -Brian ___ Alternatively, instead of editing the document, you could edit a CSV text file, tab delimited text file, or database table and then generate your XLS/google doc document from that - which is likely your most flexible approach, and still preserves your ability to have your preferred output format. I have used this technique with great success using the perl module Spreadsheet::WriteExcel. It's really easy and worth a look. Andy KoreLogic Security 603.465.3236 (Office) 603.340.2498 (Mobile) http://www.korelogic.com GnuPG Fingerprint: 688A 79EC B1E5 5748 CE87 1F20 2C45 60E7 0583 23B6 pgpsF6MuldUhr.pgp Description: PGP signature ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
Seconded, this would be my answer as well. On Wed, Jan 19, 2011 at 1:51 PM, Brian St. Pierre br...@bstpierre.org wrote: On Wed, Jan 19, 2011 at 1:40 PM, Ted Roche tedro...@tedroche.com wrote: Or you could just throw the spreadsheet into Google Apps, since they seem to have worked out the multi-user document sharing aspects pretty well. However, I don't know of a command-line interface to that! Google provides an API for Google Spreadsheets: http://code.google.com/apis/spreadsheets/data/3.0/developers_guide.html and a python client library: http://code.google.com/p/gdata-python-client/ If you insist on a self-owned/maintained solution, depending on complexity, I have a few ideas, but none are as slick and comprehensive as the above. ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
Re: Looking for a tool for spreadsheet manipulation.
On Wed, Jan 19, 2011 at 2:13 PM, David Berube djber...@berubeconsulting.com wrote: Alternatively, instead of editing the document, you could edit a CSV text file, tab delimited text file, or database table and then generate your XLS/google doc document from that - which is likely your most flexible approach, and still preserves your ability to have your preferred output format. It should be noted that an xls file *CAN* simply be a csv file. Honest to god, try it. Handles equations in the cells and everything. Used it many times for the quick and dirty '*.XLS Output'. The users never know the difference. :-D -- -- Thomas ___ gnhlug-discuss mailing list gnhlug-discuss@mail.gnhlug.org http://mail.gnhlug.org/mailman/listinfo/gnhlug-discuss/
