modifying excel sheets with Python +excel + mac environment
prasanna
I have a scenario here. I need to open an excel 08 and extract
selected columns from it and open another existing excel sheet to add
the extracted data.
I am amble to the readin part using xlrd and creating a new worksheet
and writing it using xlwt. But i do not want to create a new excel
sheet every time. i need append the data into this existing output
excel sheet. Could you please guide me through this. I would be really
greatful if you help me out with a sample code also.
Thanks
John Machin
> HI,
> I have a scenario here. I need to open an excel 08 and extract
> selected columns from it and open another existing excel sheet to add
> the extracted data.
Is "an excel 08" an xlsx file created by Excel 2008 on a Mac? If so, you
have a problem: xlrd support for xlsx files is only at the alpha stage
at the moment.
> I am amble to the readin part using xlrd and creating a new worksheet
> and writing it using xlwt. But i do not want to create a new excel
> sheet every time. i need append the data into this existing output
> excel sheet. Could you please guide me through this. I would be really
> greatful if you help me out with a sample code also.
Have you read the tutorial that is available from
http://www.python-excel.org ?
Have you considered whether this topic has already been addressed in
this forum? E.g. from a thread entitled "Regarding Working with Excel in
Python (Append record in file)" only about three days old:
"""Excel files have a complex structure. It is impossible to append data
to an Excel file. To update an Excel file, whether you are using COM or
xlutils and friends or any other kit, you need to make like a user: Open
file [reads whole file into memory], add/change/delete contents [in
memory], Save (As) file [writes file from memory]."""
prasanna
I am working with only .xls formatted files. ya . I read the tutorials
which helped me a lot in working with xlrd and xlwt packages. Right
now I have the scenario as regarding appending data. If you don't
mind could you please brief the procedure that you had explained. I
mean, i have no idea on how to proceed with what you have told me now.
I am totally new here. Please please guide me.
Thanks.
John Machin
> Hi John,
> I am working with only .xls formatted files. ya . I read the tutorials
> which helped me a lot in working with xlrd and xlwt packages. Right
> now I have the scenario as regarding appending data. If you don't
> mind could you please brief the procedure that you had explained. I
> mean, i have no idea on how to proceed with what you have told me now.
> I am totally new here. Please please guide me.
Now read the sections about the xlutils package, in particular the one
about xlutils.copy.
prasanna
What I actually want to do is, say, I have a file A.xls. Now every
time I read some new input file which is in .xls format, I need to
select just few of the columns and keep adding it in A.xls. So the
actually change that would be happening every time in A.xls is a new
column being added when a new input file is read.
Now,my A.xls would be having plots or graphs.
So as you said, if i copy the data of my input file and also the data
from my A.xls and do the modification in the memory, then write the
modified data in A.xls again, what iam supposed to do with the graph.
I won't be able to read the graph. I can either generate a new graph
for the modified data, which i have no idea how to do or don't even
know if its possible to do it. Or i need to retain the existing graph.
in A.xls after i have appended the data.
Hope i have given a clear picture of my scenario. Could you let me
know the possible ways of approaching this scenario?
John Machin
> hey John,
> What I actually want to do is,
About time you mentioned what you really want to do :-)
> say, I have a file A.xls. Now every
> time I read some new input file which is in .xls format, I need to
> select just few of the columns and keep adding it in A.xls. So the
> actually change that would be happening every time in A.xls is a new
> column being added when a new input file is read.
> Now,my A.xls would be having plots or graphs.
> So as you said, if i copy the data of my input file and also the data
> from my A.xls and do the modification in the memory, then write the
> modified data in A.xls again, what iam supposed to do with the graph.
> I won't be able to read the graph. I can either generate a new graph
> for the modified data, which i have no idea how to do or don't even
> know if its possible to do it. Or i need to retain the existing graph.
> in A.xls after i have appended the data.
> Hope i have given a clear picture of my scenario. Could you let me
> know the possible ways of approaching this scenario?
The only way that you could do that would be to get the pywin32 package
and use COM. However that is Windows only. And I'm reasonably sure that
you'd need to fiddle with the chart to tell it about the new column of
data otherwise it would blithely ignore it.
Do the users actually use this as a spreadsheet application? Or is ia a
summary database with a graph tacked on? Perhaps you could look for an
open source standalone charting package.
prasanna
want to reduce their work of manually copying data from different
sheets and and plotting it on the other.
anyway. Leave the graph generation part. I would not want to generate
the graph based on the new data. I would be more than happy if i get
to know a solution on how to retain the graph. i.e make modifications
on the excel sheet with the graph existing? Leaving the generation of
graph part, is there a way to do this in mac environment?
prasanna
do with xlutils on appending data into existing excel my copying the
contents and pasting it. A code for doing that is most welcome:)
John Machin
> Hmmm..This spreadsheet would not be used as an application. The users
> want to reduce their work of manually copying data from different
> sheets and and plotting it on the other.
> anyway. Leave the graph generation part. I would not want to generate
> the graph based on the new data. I would be more than happy if i get
> to know a solution on how to retain the graph. i.e make modifications
> on the excel sheet with the graph existing? Leaving the generation of
> graph part, is there a way to do this in mac environment?
Perhaps. However I've already told you all I know.
John Machin
> Also it would be of real great help if you let me know what exactly to
> do with xlutils on appending data into existing excel my copying the
> contents and pasting it. A code for doing that is most welcome:)
Which part of the example in the tutorial don't you understand?
prasanna
sheet in which iam gonna write the data.. is it possible to read from
2 excel sheets ( 1- which is the input from which i am going to read
new data and 2 - the one which iam copying and appending it back along
with input data) and have it in the memory and then write it bac to
the output file with util.copy property? i ma not fully understanding
its property.... could you please brief me on that....
prasanna
John Machin
> the part that is confusing me is - ia m gooing to make a copy of a
> sheet in which iam gonna write the data.. is it possible to read from
> 2 excel sheets ( 1- which is the input from which i am going to read
> new data and 2 - the one which iam copying and appending it back along
> with input data) and have it in the memory and then write it bac to
> the output file with util.copy property? i ma not fully understanding
> its property.... could you please brief me on that....
Please re-read the tutorial section. It is called xlutils.copy, it's a
function, not a property, and what it does is nothing like "write it
back to the output file". There is no writing BACK to any file --
xlwt.save() always creates a NEW file (destroying any existing file with
the same name).
Your brief:
step 1: xlrd_book1 = xlrd.open_workbook("old_master_file.xls")
step 2: xlwt_book = xlutils.copy(xlrd_book1)
step 3: del xlrd_book1 # don't need this any more
# You now have an xlwt.Workbook object which is equivalent (to the
extent described in the tutorial) to the xlrd.Book object you obtained
in step 1. You can now write more cell contents to it.
step 4: write some cell data to xlwt_book
step 5: xlwt_book.save("new_master_file.xls")
It is up to you where you get the data to use in step 4 (just like when
you are creating an xls file from scratch): you could extract it from an
Excel xls file using xlrd, read it from a CSV file, query it from a
database, input it from a ouija board with a USB interface, ...
HTH,
John
prasanna
I now understood what i exactly need to Do. I think this is pretty
helpful. But i have a doubt.?
now i have copied the xlrd_book1 into xlwt_book. Suppose iam opening
another .xls file and reading just 1 column of data, How do i append
it to the copied file i.e xlwt_book. Does copying the xlrd_book1 and
reading 1 coulumn of new data both get stored in the same buffer?
If i need to use a list to store and append the data, how do i do it
both?
Also how am i going to write the the copied and appended data ?
Please brief me on these queries.
Thank you.
John Machin
> Hi,
> I now understood what i exactly need to Do. I think this is pretty
> helpful. But i have a doubt.?
> now i have copied the xlrd_book1 into xlwt_book. Suppose iam opening
> another .xls file and reading just 1 column of data, How do i append
> it to the copied file i.e xlwt_book.
# step 1:
xlrd_book1 = xlrd.open_workbook("old_master_file.xls")
# step 2:
xlwt_book = xlutils.copy(xlrd_book1)
# step 3:
del xlrd_book1 # don't need this any more
# You now have an xlwt.Workbook object which is equivalent (to the
# extent described in the tutorial) to the xlrd.Book object you obtained
# in step 1. You can now write more cell contents to it.
# step 4: write some cell data to xlwt_book
# step 4.1: get some data
xlrd_book2 = xlrd.open_workbook("another.xls")
xlrd_sheet2 = xlrd_book_2.sheet_by_index(SOME_SHEET_INDEX)
some_data = xlrd_sheet2.col_values(SOME_COLUMN_INDEX)
# step 4.2: nut out where to put it
xlwt_sheet = xlwt_book.get_sheet(A_SHEET_INDEX)
next_colx = 1 + max(row_obj.get_max_col() for row_obj in
xlwt_sheet.rows.itervalues())
# Whew! That's not intuitively obvious. Looks like
# a "convenience method" or two might be an idea.
# Note 1: next_rowx = 1 + max(xlwt_sheet.rows)
# Note 2: each of those max() calls will raise ValueError if the sheet
is empty.
# Note 3: Those max() calls aren't cheap. Suggestion: stash
xlrd_sheet.nrows and xlrd_sheet.ncols while you are in step 1.
# step 4.3: put it
for rowx, value in enumerate(some_data):
xlwt_sheet.write(rowx, next_colx, value, A_STYLE)
#step 5:
xlwt_book.save("new_master_file.xls")
> Does copying the xlrd_book1 and
> reading 1 coulumn of new data both get stored in the same buffer?
(1) no such concept as "buffer" involved here
(2) see step 3 above
> If i need to use a list to store and append the data, how do i do it
> both?
As you should see from the above, you are not really "appending the
data" -- the model is a rectangle of 65K rows and 256 columns; you call
Worksheet.write() to place data in the grid.
> Also how am i going to write the the copied and appended data ?
see step 5 above
Cheers,
John