By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,909 Members | 2,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,909 IT Pros & Developers. It's quick & easy.

Writing to ms excel

P: n/a
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
Aug 30 '08 #1
Share this Question
Share on Google+
20 Replies


P: n/a
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.
Aug 30 '08 #2

P: n/a
On Sat, Aug 30, 2008 at 4:41 PM, Marin Brkic <mbrkic@invalid_mail.adresswrote:
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.
--
Marco Bizzarri
http://notenotturne.blogspot.com/
http://iliveinpisa.blogspot.com/
Aug 30 '08 #3

P: n/a
Marin Brkic wrote:
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:office:excel">
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>5</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
Aug 30 '08 #4

P: n/a
On Aug 31, 12:41 am, Marin Brkic <mbrkic@invalid_mail.adresswrote:
>
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
Aug 31 '08 #5

P: n/a
On Aug 31, 12:57 am, "Eric Wertman" <ewert...@gmail.comwrote:
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

Aug 31 '08 #6

P: n/a
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.

On Aug 31, 12:57 am, "Eric Wertman" <ewert...@gmail.comwrote:
>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?" :-)
Aug 31 '08 #7

P: n/a
On Sat, 30 Aug 2008 17:18:19 -0700 (PDT), John Machin
<sj******@lexicon.netwrote:
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
>
HTH,

John
Aug 31 '08 #8

P: n/a
On Sat, 30 Aug 2008 19:37:16 +0200, "Marco Bizzarri"
<ma************@gmail.comwrote:
>
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
Aug 31 '08 #9

P: n/a
On Sun, 31 Aug 2008 03:36:39 +0200, Marin Brkic
<mbrkic@invalid_mail.adresswrote:
>On Sat, 30 Aug 2008 19:37:16 +0200, "Marco Bizzarri"
<ma************@gmail.comwrote:
>>
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
*count*
Aug 31 '08 #10

P: n/a
On Sun, 31 Aug 2008 03:36:39 +0200, Marin Brkic wrote:
On Sat, 30 Aug 2008 19:37:16 +0200, "Marco Bizzarri"
<ma************@gmail.comwrote:

>>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.
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.

--
Steven
Aug 31 '08 #11

P: n/a
On 31 Aug 2008 02:37:16 GMT, Steven D'Aprano
<st***@REMOVE-THIS-cybersource.com.auwrote:
>
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.
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
Aug 31 '08 #12

P: n/a
On Aug 31, 11:32 am, Marin Brkic <mbrkic@invalid_mail.adresswrote:
On Sat, 30 Aug 2008 17:18:19 -0700 (PDT), John Machin

<sjmac...@lexicon.netwrote:
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.
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.
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
Aug 31 '08 #13

P: n/a
On Aug 31, 12:37 pm, Steven D'Aprano <st...@REMOVE-THIS-
cybersource.com.auwrote:
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
Aug 31 '08 #14

P: n/a
John Machin wrote:
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
Aug 31 '08 #15

P: n/a
John Machin wrote:
On Aug 31, 11:32 am, Marin Brkic <mbrkic@invalid_mail.adresswrote:
>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
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 )
Aug 31 '08 #16

P: n/a
Marin Brkic wrote:

<snip ... lots>
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.

Aug 31 '08 #17

P: n/a
On Aug 31, 7:21 pm, Alessandro <nos...@nospam.nospamwrote:
John Machin wrote:
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?
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
Aug 31 '08 #18

P: n/a
John Machin wrote:
"""xlrd is still doing what it was designed to do: read Excel ... xls
files."""
oops... I had downloaded the sources from
"https://secure.simplistix.co.uk/svn/xlwt/trunk"

eh, xlWT .. obviously I cant find an "open" function in it..
Could you possibly be viewing the source for xlwt (wt being an
abbreviation of WriTe)? xlwt is a fork of pyExcelerator.
I have written about pyExcelerator 'cause the code refers to it! :-)

I think I will try to use xlrd/xlwt.
I have joined the group, I will let you know
Thanks
Alessandro
Aug 31 '08 #19

P: n/a
On Sat, 30 Aug 2008 22:09:31 -0700 (PDT), John Machin
<sj******@lexicon.netwrote:
>
"write to a file" has connotations of updating an existing file;
"write a file" or "create a file" are less ambiguous.
Hmm, yes, maybe you're right. Write to a file, as in, create a file
and then write to it is what I ment in the previous post.
>>
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?
Oh, sorry, I thought the message header was visible somehow to you.
Well, never mind.
>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).
Yes, this one does seem a little better concerning the number of
messages. So I subscribed.
Sorry about this, but I remember once subscribing to some google
group, and so many messages started arriving to my mail, that it was
impossible to use that account for anything else.

Best regards
Marin
Aug 31 '08 #20

This discussion thread is closed

Replies have been disabled for this discussion.