appending lists to an existing file starting from a specific cells

[Rania Labib]

Hi,

I'm going to append 12 lists to columns in an existing excel file and I have two questions:

1- I'm going to open the existing file using these lines of codes

from openpyxl import load_workbook

wb = load_workbook('C:\Users\Rania\Google Drive\ABS 2016\Results\Boxplots\All_Views_boxplot.xlsx')

rootdir1 = "C:\Users\R\Google Drive\2016\Results\Facade"

ws = wb['BoxPlot']

Do I use this line to save the file with the appended data? will this overwrite my existing file or append the new data and replace my existing file?

wb.save(('C:\Users\R\Google Drive\2016\Results\Boxplots\All_Views_boxplot.xlsx'))

2- I'm appending 12 lists to columns using this line 

for row in zip(m_list[1],m_list[2],m_list[3], m_list[4],m_list[5],m_list[6],m_list[7], m_list[8],m_list[9],m_list[10],m_list[11],m_list[12]):

    ws.append(row) 

This appends the lists starting from the cell at column 0, row 0. However, I want to append the lists starting at cell (row 50 and column 2).

Thanks

[Rania Labib]

Additionally, I'm iterating over a list of text files, I would like to save an individual Excel sheet every time I extract information from a text file , I used something like this :

wb.save(('C:\Users\Rania\Google Drive\ABS 2016\Results\PV_Facade_Boxplots\V'+ txt_file_name +'boxplot.xlsx'))

I got a syntax error. 

[Charlie Clark]

Am .08.2016, 14:55 Uhr, schrieb Rania Labib <rn.l...@gmail.com>:

> Hi,

Hiya,

> I'm going to append 12 lists to columns in an existing excel file and I
> have two questions:
>
> 1- I'm going to open the existing file using these lines of codes
>
> from openpyxl import load_workbook
>
> wb = load_workbook('C:\Users\Rania\Google Drive\ABS
> 2016\Results\Boxplots\All_Views_boxplot.xlsx')
>
> rootdir1 = "C:\Users\R\Google Drive\2016\Results\Facade"
>
> ws = wb['BoxPlot']
>
>
> Do I use this line to save the file with the appended data? will this
> overwrite my existing file or append the new data and replace my
> existing file?
>
> wb.save(('C:\Users\R\Google
> Drive\2016\Results\Boxplots\All_Views_boxplot.xlsx'))

wb.save() will create / overwrite a file.

I suggest you learn to use os.path.join() to manage you filenames

> 2- I'm appending 12 lists to columns using this line
>
> for row in zip(m_list[1],m_list[2],m_list[3],
> m_list[4],m_list[5],m_list[6],m_list[7],
> m_list[8],m_list[9],m_list[10],m_list[11],m_list[12]):
>
> ws.append(row)

Looks ugly!

> This appends the lists starting from the cell at column 0, row 0.
> However,
> I want to append the lists starting at cell (row 50 and column 2).

Well, you can always just use ws.cell(row=x, column=y, value=v) But this
requires you to have a counter. Your loop could probably be written using
something like this:

for idx, v in enumerate(m_list, 1):
ws.append(m_list[idx:idx+1])

or

for idx, v in enumerate(m_list, 50):
ws.cell(row=idx, column=2, value=m_list[idx-49]) # adjust as required

ws.append() itself depends upon the ws.max_row which is a property that
will be calculated if unknown, but can otherwise be set using a private
attribute.

I'm not sure I understand your follow up question. Do you want to save a
new *workbook* for each text file? Or add a new sheet for each text file?
Saving workbooks always creates a new file and should probably be avoided
in the latter case.

As I said above, use os.path to manage filenames and hopefully keep
problems related to them to a minimum.

folder = r"C:\Users\R\Google Drive\2016\Results\Boxplots") # Windows path
so use a "raw" string
fname = "{0}_boxplot.xlsx".format(text_filename)
wb.save(os.path.joing(folder, fname))

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[Rania Labib]

Charlie,

Awesome, thank you so much, everything works great now. However, the code doesn't work if I use the attached sheet, I get an encoding error. I'm guessing this excel file has some kind of macro embedded in it. Is there a way to overcome this?

Thanks

I know I write ugly code,  learned python couple of months ago and I don't use it frequently.

[Charlie Clark]

Am .08.2016, 23:17 Uhr, schrieb Rania Labib <rn.l...@gmail.com>:

>
> Charlie,
> Awesome, thank you so much, everything works great now. However, the code
> doesn't work if I use the attached sheet, I get an encoding error. I'm
> guessing this excel file has some kind of macro embedded in it. Is there
> a way to overcome this?

And the encoding error is related to macros because…? (Hint, the two are
entirely unrelated).

Please provide the full traceback. You've probably got some encoded
strings in your CSV that need converting to unicode: all text in openpyxl
should unicode.

> Thanks
> I know I write ugly code, learned python couple of months ago and I
> don't use it frequently.

You don't need to make excuses. Just talk with your prof and see if you
can't squeeze more programming in your favourite language into your
project! ;-)

[Rania Labib]

Hi Charlie,

Thank you very much for your help! this time, I'm using programming for my own research (PhD dissertation) because I'm extracting data from Building Simulation calculation files. I have hundreds of files, each file has thousands of numeric values. 

Anyways, below is the full error I receive when I use the excel sheet with macro:

Traceback (most recent call last):

  File "C:/Users/Rania/Google Drive/ABS 2016/Results/Python_Files/PV_Facade_Boxplot.py", line 74, in <module>

    wb.save('C:\Users\Rania\Google Drive\ABS 2016\Results\PV_Facade_Boxplots\All_Views_By_Hour.xlsx')

  File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\workbook\workbook.py", line 294, in save

    save_workbook(self, filename)

  File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\writer\excel.py", line 270, in save_workbook

    writer.save(filename)

  File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\writer\excel.py", line 251, in save

    self.write_data()

  File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\writer\excel.py", line 82, in write_data

    self._write_worksheets()

  File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\writer\excel.py", line 200, in _write_worksheets

    xml = ws._write()

  File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\worksheet\worksheet.py", line 851, in _write

    return write_worksheet(self)

  File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\writer\worksheet.py", line 147, in write_worksheet

    xf.write(ws.HeaderFooter.to_tree())

  File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\descriptors\serialisable.py", line 138, in to_tree

    node = obj.to_tree(child_tag)

  File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\worksheet\header_footer.py", line 199, in to_tree

    el.text = str(self)

  File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\worksheet\header_footer.py", line 182, in __str__

    txt.append("&{0}{1}".format(key, str(part)))

UnicodeEncodeError: 'ascii' codec can't encode character u'\xa9' in position 10: ordinal not in range(128)

[Charlie Clark]

Am .08.2016, 18:00 Uhr, schrieb Rania Labib <rn.l...@gmail.com>:

> UnicodeEncodeError: 'ascii' codec can't encode character u'\xa9' in
> position 10: ordinal not in range(128)

I think that if you switch to using a checkout of the 2.4 branch that the
problem has been resolved. See #642

[Rania Labib]

Thanks for the prompt response!

I tried to install the checkout using this command:

        $pip install hg+https://bitbucket.org/openpyxl/openpyxl@2.4#egg=openpyxl

And this is what I got

Requirement already satisfied (use --upgrade to upgrade): openpyxl from hg+https://bitbucket.org/openpyxl/openpyxl@2.4#egg=openpyxl in c:\python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg

Requirement already satisfied (use --upgrade to upgrade): jdcal in c:\python27\lib\site-packages\jdcal-1.2-py2.7.egg (from openpyxl)

Requirement already satisfied (use --upgrade to upgrade): et_xmlfile in c:\python27\lib\site-packages\et_xmlfile-1.0.1-py2.7.egg (from openpyxl)

I think I'm not installing the checkout correctly because nothing has changed. Is the link in the command line correct?

Thanks!

[Charlie Clark]

Am .08.2016, 19:12 Uhr, schrieb Rania Labib <rn.l...@gmail.com>:

> I think I'm not installing the checkout correctly because nothing has
> changed. Is the link in the command line correct?

Try uninstalling openpyxl first.

[Sagar Patil]

Hello Charlie I have to do same appending lists in existing file im new in openpyxl library so could please help me