Writing to ms excel

M

Marin Brkic

Hello all,

please, let me apologize in advance. English is not my first language
(not even my second one), so excuse any errors with which I'm about to
embarass myself in front of the general public. Second, I'm relatively
new to python, so sorry if this seems like a stupid question.

I'm trying to find a way to write data to excel cells (or to be more
specific to an .xls file), let's say for the sake of argument, data
readen from a file (although it will be calculated in the process).
I've been searching, but couldn't find any examples which allows that.

Do anyone knows of any ? All help is appreciated on this matter.
Tutorials? Anything ...


Best regards
Marin
 
E

Eric Wertman

I'm trying to find a way to write data to excel cells (or to be more
specific to an .xls file), let's say for the sake of argument, data
readen from a file (although it will be calculated in the process).
I've been searching, but couldn't find any examples which allows that.

The answer will depend on your base os.. if you are on windows there
will be some options using the COM interfaces I bet.. but I don't know
anything about them.

If you are on a unix based os, your choices are limited. If you can,
I would just write to a csv file and open it with Excel. If you have
to interface with an exsisting excel file, you can try
http://pypi.python.org/pypi/xlrd , but it may not support writing xls
files, still.
 
M

Marco Bizzarri

Hello all,
I'm trying to find a way to write data to excel cells (or to be more
specific to an .xls file), let's say for the sake of argument, data
readen from a file (although it will be calculated in the process).
I've been searching, but couldn't find any examples which allows that.

Is it suitable for you to use a python program talking with a running
instance of openoffice? in that case, pyuno could help you.
 
L

Leonhard Vogt

Marin said:
I'm trying to find a way to write data to excel cells (or to be more
specific to an .xls file), let's say for the sake of argument, data
readen from a file (although it will be calculated in the process).
I've been searching, but couldn't find any examples which allows that.

If you have Excel installed you can use COM to access Excel and to
"remote-control" it in order to save a .xls file.

I have no Windows nor Excel at the moment, so I can only write something
approximative. Searching for win32com will help.

win32com.Coinitialize(...)
excel = win32com.client.Dispatch("Excel.Application")
wbook = excel.NewDocument()
sheet = wbook.Worksheets("Sheet1")
sheet.Cells(1,1) = "Abc"
wbook.Saveas("filename")
excel.close() or destroy()
win32com.coUninitialize()

The objects available are very similar to the VBA objects, so recording
a macro and translating its VBA source to python should not be hard.

HTH
Leo
 
K

Ken Starks

Marin said:
Hello all,

please, let me apologize in advance. English is not my first language
(not even my second one), so excuse any errors with which I'm about to
embarass myself in front of the general public. Second, I'm relatively
new to python, so sorry if this seems like a stupid question.

I'm trying to find a way to write data to excel cells (or to be more
specific to an .xls file), let's say for the sake of argument, data
readen from a file (although it will be calculated in the process).
I've been searching, but couldn't find any examples which allows that.

Do anyone knows of any ? All help is appreciated on this matter.
Tutorials? Anything ...


Best regards
Marin

Not specific to python, but if you have a recent version of excel, you
could write to the Excel xml format (if not, you could consider
the (or one of the) gnumeric xml formats.


The Excel format is verbose, but you can copy and paste most of it.
The critical bit you need your software to write looks
something like this:


<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="5"
x:FullColumns="1"
x:FullRows="1">
<Row>
<Cell><Data ss:Type="String">number</Data></Cell>
<Cell><Data ss:Type="String">square</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">1</Data></Cell>
<Cell><Data ss:Type="Number">1</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">2</Data></Cell>
<Cell><Data ss:Type="Number">4</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">3</Data></Cell>
<Cell><Data ss:Type="Number">9</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">4</Data></Cell>
<Cell><Data ss:Type="Number">16</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:eek:ffice:excel">
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>5</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
 
J

John Machin

I'm trying to find a way to write data to excel cells (or to be more
specific to an .xls file), let's say for the sake of argument, data
readen from a file (although it will be calculated in the process).
I've been searching, but couldn't find any examples which allows that.

Do anyone knows of any ? All help is appreciated on this matter.
Tutorials? Anything ...

It helps in situations like this to mention details of your
environment
(1) what version of what operating system (Linux, OS X, Windows, etc)
(2) what version of Python
as the available solutions are often dependent on the answers.

For Python version 2.[345] on any platform, you can use xlwt, which is
as simple as this for writing a 1-worksheet Excel 97-to-2003 XLS file
(without any formatting):

def write_xls(file_name, sheet_name, data):
import xlwt
book = xlwt.Workbook()
sheet = book.add_sheet(sheet_name)
rowx = 0
for row in data:
rowx += 1
for colx, value in enumerate(row):
sheet.write(rowx, colx, value)
book.save(file_name)
# data can be any of the following Python types: int, long, float,
decimal.Decimal, datetime.date, datetime.datetime, bool, str, and
unicode.

xlwt is available from https://secure.simplistix.co.uk/svn/xlwt/trunk

I suggest that you join the python-excel group (http://
groups.google.com.au/group/python-excel?hl=en) or at least read some
of the questions and responses.

HTH,

John
 
J

John Machin

If you have
to interface with an exsisting excel file, you can try http://pypi.python.org/pypi/xlrd, but it may not support writing xls
files, still.

That remark appears to be an inverted cousin of the old joke question
"Have you stopped beating your wife?" :)

xlrd is still doing what it was designed to do: read (not "interface
with") Excel xls files. There is a currently active project to add
support for reading the xlsx (x=XML) files produced by Excel 2007.
This may be followed by Excel 2007 xlsb (b=binary) files and
OpenOffice ods files. Writing is not on the agenda.

Cheers,
John
 
E

Eric Wertman

Yes sorry, that's a really poorly formed sentence all the way
around... not a dig on xlrd, but a warning to the OP that they may not
find what they are looking for there.
 
M

Marin Brkic

On Sat, 30 Aug 2008 17:18:19 -0700 (PDT), John Machin


Hello John (and everyone else), thanks for answering.
It helps in situations like this to mention details of your
environment
(1) what version of what operating system (Linux, OS X, Windows, etc)
(2) what version of Python
as the available solutions are often dependent on the answers.

Yes, of course. I sometimes forget the most essential of things.
- winxp, sp2
- python 2.5.2
For Python version 2.[345] on any platform, you can use xlwt, which is
as simple as this for writing a 1-worksheet Excel 97-to-2003 XLS file
(without any formatting):

Actually, that might work. What I was needing (aiming for) was a way
to write to excel 2003 files. Formatting is not necessary, since what
I'm trying to write is some tabular data; results from fortran-python
simulation (I can explain, but the details seem irrelevant for this
case).
I'm trying to avoid the text file - import to excel - mechanism, since
there is quite a lot of files written.
def write_xls(file_name, sheet_name, data):
import xlwt
book = xlwt.Workbook()
sheet = book.add_sheet(sheet_name)
rowx = 0
for row in data:
rowx += 1
for colx, value in enumerate(row):
sheet.write(rowx, colx, value)
book.save(file_name)
# data can be any of the following Python types: int, long, float,
decimal.Decimal, datetime.date, datetime.datetime, bool, str, and
unicode.

xlwt is available from https://secure.simplistix.co.uk/svn/xlwt/trunk

I suggest that you join the python-excel group (http://
groups.google.com.au/group/python-excel?hl=en) or at least read some
of the questions and responses.

Please, one more question. As you have noticed, I posted my message to
comp.lang.python, using a newsreader. Is there a way to access google
groups through a similiar interface program as a newsreader. Never
used them before, and getting a lot of messages to my email every day
does not sound very appealing to me.

Best regards
Marin
 
M

Marin Brkic

Is it suitable for you to use a python program talking with a running
instance of openoffice? in that case, pyuno could help you.

Hello Marco, thanks for answering,

no, sorry. As much as I like OOffice, several other people will be
using the program I'm working on, and I can't cound on them having the
OOffice installed.
MS, as much as I hate to admit it, is the industry standard (or, at
least that's the one we're stuck with at the present time ;-)





Best regards
Marin
 
M

Marin Brkic

Hello Marco, thanks for answering,

no, sorry. As much as I like OOffice, several other people will be
using the program I'm working on, and I can't cound on them having the
*count*
 
S

Steven D'Aprano

Hello Marco, thanks for answering,

no, sorry. As much as I like OOffice, several other people will be using
the program I'm working on, and I can't cound on them having the OOffice
installed.

Of course you can. You could simply tell them that you need the
programming interface to OpenOffice and that's the format you will be
supplying the data. If they want your data, they will use what you tell
them to use *if you give them no choice*.

If they want your data, most people will just accept that OpenOffice is a
strange mysterious programming requirement, like all the other strange
mysterious things programmers and sys admins install on their PC. The
requirements are "a computer, Python and OpenOffice" instead of "a
computer and Python".

If there are exceptions who know enough to insist that Excel can do
everything OpenOffice can do (more or less), and they don't want to use
OpenOffice, then don't argue. Just say that you're working on support for
Excel, but it will take a few weeks, but as a temporary measure they can
use OpenOffice until the code is ready. You will be *amazed* at how much
people will accept change if you tell them it's only temporary.

You might even discover that by the time Excel support is ready, they
will prefer OpenOffice.


MS, as much as I hate to admit it, is the industry standard (or, at
least that's the one we're stuck with at the present time ;-)

Only because we treat it as standard. You had no hesitation to write code
that relies on people having Excel installed, and yet you didn't want to
rely on an open source free software package that anyone with a fast
Internet connection or a CD drive can install in just a couple of
minutes. You don't even need to reboot the PC.
 
M

Marin Brkic

Of course you can. You could simply tell them that you need the
programming interface to OpenOffice and that's the format you will be
supplying the data. If they want your data, they will use what you tell
them to use *if you give them no choice*.

If they want your data, most people will just accept that OpenOffice is a
strange mysterious programming requirement, like all the other strange
mysterious things programmers and sys admins install on their PC. The
requirements are "a computer, Python and OpenOffice" instead of "a
computer and Python".

If there are exceptions who know enough to insist that Excel can do
everything OpenOffice can do (more or less), and they don't want to use
OpenOffice, then don't argue. Just say that you're working on support for
Excel, but it will take a few weeks, but as a temporary measure they can
use OpenOffice until the code is ready. You will be *amazed* at how much
people will accept change if you tell them it's only temporary.

You might even discover that by the time Excel support is ready, they
will prefer OpenOffice.




Only because we treat it as standard. You had no hesitation to write code
that relies on people having Excel installed, and yet you didn't want to
rely on an open source free software package that anyone with a fast
Internet connection or a CD drive can install in just a couple of
minutes. You don't even need to reboot the PC.

As much as a lot of the above is true, and I agree with some of it,
things are not more often than not that simple. It would be true if I
was, for example, working in a private owned company where we could
choose what we use, install our own stuff, have liberties and people
generally interested in learning new software and ... that approach.

On the other hand, when you work in an institution that has people
with their own problems (technical, but not computer related) - on
which they want to spend their time, and not installing and adapting
to new software solutions; when you have system engineers who decide
what you use, and generally who maintain the computers we work on, and
when all licences are gotten and sponsored by someone else, ... then,
well, then it's a little different situation.
Rules exist - exceptions can be made, and are made if there is a need
for them, but switching to open office just for me, when everyone has
gotten used to this one, and ... well, let's just say that one's not
going to be on the exception list :)

I remember an older coleague who said; "open, free and whatever
licence type ... software is free, only up to some amount of $$/per
hour". After that you just want things to work, and if they don't
work, there are people who are paid $/per hour to make it work.
And generally, when you look at the industry sector, ms IS the
standard - not because we treat it, but because for now, it just is.
When OOffice is used by 60% of all people I deal with, then maybe it
will be the standard.
Sorry for a little rough-but-straight-to-the-point-explanation, but
it's usually the quickest way to deal with
free-vs-commercial-starting-to-arise-flame-war :) which usually
happens after a post like this.

Best regards
Marin
 
S

Steven D'Aprano

I remember an older coleague who said; "open, free and whatever licence
type ... software is free, only up to some amount of $$/per hour".
After that you just want things to work, and if they don't work, there
are people who are paid $/per hour to make it work.

And that applies *exactly* the same to Excel as OpenOffice, except that
you're not paying the cost for the software and the licences and tracking
the licences.

If you can find a package that "just works" for writing to Excel, great.
Otherwise you have to build it yourself. And that's when you have to
decide whether you want to spend 40 hours of programmer time (less than
one programmer-week) trying to get write support for Excel in order to
save two hours of support time for OpenOffice.
 
J

John Machin

On Sat, 30 Aug 2008 17:18:19 -0700 (PDT), John Machin

For Python version 2.[345] on any platform, you can use xlwt, which is
as simple as this for writing a 1-worksheet Excel 97-to-2003 XLS file
(without any formatting):

Actually, that might work. What I was needing (aiming for) was a way
to write to excel 2003 files.

"write to a file" has connotations of updating an existing file;
"write a file" or "create a file" are less ambiguous.
Formatting is not necessary, since what
I'm trying to write is some tabular data; results from fortran-python
simulation (I can explain, but the details seem irrelevant for this
case).
I'm trying to avoid the text file - import to excel - mechanism, since
there is quite a lot of files written.


Please, one more question. As you have noticed, I posted my message to
comp.lang.python, using a newsreader.

I hadn't noticed; what makes you think so?
Is there a way to access google
groups through a similiar interface program as a newsreader.

I don't know (question has never arisen before).
Never
used them before, and getting a lot of messages to my email every day
does not sound very appealing to me.

Either (1) you have not looked at the messages at the link that I gave
you or (2) your idea of "a lot of messages" every day differs wildly
from mine. Email alternatives are (a) one message per posting (b)
daily digest (c) none (use your web browser).

HTH,
John
 
J

John Machin

Only because we treat it as standard. You had no hesitation to write code
that relies on people having Excel installed, and yet you didn't want to
rely on an open source free software package that anyone with a fast
Internet connection or a CD drive can install in just a couple of
minutes. You don't even need to reboot the PC.

Consider that there are parallel universes to yours, where big brother
severely limits access to the Internet, where staff have to sign
rather draconian agreements about their use of the company facilities,
where desktops are scanned nightly for contraband (the finding of
which will cause the brownshirts to drop in for a quick game of hands-
knees-and-bump-your-exe), where even the mention of seditious material
like OpenOffice might result in a trip to the desert provinces for re-
education ... the cause is better advanced IMHO by staying under the
radar and crawling under the wire; tub-thumping soapbox-mounting
ranters however correct and righteous are likely to suffer a fate
similar to that of Michael Servetus.

Marin is allowed to use Python; he's doing very well compared to some.

They-scrubbed-all-programming-languages-off-the-production-machine-
that's-why-I-have-csv-routines-written-in-awk-ly yours,
John
 
A

Alessandro

John said:
xlrd is still doing what it was designed to do: read (not "interface
with") Excel xls files. There is a currently active project to add

Can xlrd *read* xls files?
As far as I have used PyExecelerator, it can only *create* xls file.

I'm viewing the xlrd sources, but I can't find the file loading function
and there is no examples about it.

I'm going to join the python-excel group


Alessandro
 
K

Ken Starks

John said:
I don't know (question has never arisen before).


Either (1) you have not looked at the messages at the link that I gave
you or (2) your idea of "a lot of messages" every day differs wildly
from mine. Email alternatives are (a) one message per posting (b)
daily digest (c) none (use your web browser).

HTH,
John

I use thunderbird for private email, mailing lists and newsgroups.
It is easy enough to set up filters to divert messages from specific
mailing lists to their own directory.

Is this adequate for your needs ? (You do get the whole message, not
just the header )
 
K

Ken Starks

Marin Brkic wrote:

Actually, that might work. What I was needing (aiming for) was a way
to write to excel 2003 files. Formatting is not necessary, since what
I'm trying to write is some tabular data; results from fortran-python
simulation (I can explain, but the details seem irrelevant for this
case).
I'm trying to avoid the text file - import to excel - mechanism, since
there is quite a lot of files written.
Best regards
Marin

Again, not python ( hope I don't start a flame war,
I've just joined the list John Machin suggested--it
looks very interesting).

I have used Apache Cocoon for this kind of task. Everything
important happens server-side.
Your raw data could be stored in a database, or a flat file, or
not stored persistently at all--just be created as a virtual stream
if you can use your python/fortran utility as a web service.

It goes into the cocoon pileline, and is first turned into
XML.
Then it is turned into other XML (in this case most likely the
gnumeric format).
Lastly it is serialized into Excel format, given the appropriate
Mime type, and sent to your browser.

It is only when it gets to the Browser, that a decision is made
as to what to do with it. You can set up your Browser
to open it is MS Excel (whichever one you have), Open Office,
Gnumeric, or whatever. Most of them will cope with it perfectly,
and will be able to save it locally in their most up-to-the-minute
variation, if that is what you want.

Cheers,

Ken.
 
J

John Machin

Can xlrd *read* xls files?

Follow the bouncing ball and sing along with me:

"""xlrd is still doing what it was designed to do: read Excel ... xls
files."""

The "rd" in xlrd is a contraction of ReaD.
As far as I have used PyExecelerator, it can only *create* xls file.

I can't imagine why you would think that the extent to which you have
used pyExcelerator has any bearing on its capabilities. In any case
pyExcelerator has an ImportXLS.parse_xls function, which is rather
embryonic compared to that of xlrd.
I'm viewing the xlrd sources, but I can't find the file loading function
and there is no examples about it.

The "file loading" function of xlrd is called "open_workbook" and is
in __init__.py. Have you considered reading the documentation for
xlrd? Have a look at the file runxlrd.py, which not only acts as a
diagnostic and dump utility, but also is a fairly rich source of
examples of what you can do with the Book object returned by
xlrd.open_work_book().

Could you possibly be viewing the source for xlwt (wt being an
abbreviation of WriTe)? xlwt is a fork of pyExcelerator. In the
current version in svn, ImportXLS has been so severely deprecated that
it has vanished, which might explain why you can't find a "file
loading" function.
I'm going to join the python-excel group

I'm going to look forward to our next communication.

Cheers,
John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,755
Messages
2,569,534
Members
45,007
Latest member
obedient dusk

Latest Threads

Top