New to openpyxl, write lists to columns

[rn.l...@gmail.com]

HI there,

I'm new to both Python and Openpyxl. I went through the Openpyxl documentation and I couldn't find an answer to my question. I'm going to parse an XML file and will end up with 12 lists, each list should have 250 items. I need to write those eight lists to a new Excel sheet. Each list should be written to a column. I should end up with a sheet that has 12 columns and 250 rows.How do I accomplish this using openpyxl ? any help would be much appreciated.

Thanks

Rania

[Charlie Clark]

Hiya Rania,

openpyxl is row-oriented which means you're going to have to write one
item from each column per row. Fortunately, this is easy with Python and
openpyxl.

We'll call your lists l1, l2, l3, … l2 but how you make them from your XML
is up to you.

To write these as columns with openpyxl:

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

for row in zip(l1, l2, l3, l4, l5, l6, l7, l8, l9, l10, l11, l12):
ws.append(row)

wb.save("columns.xlsx")

Good luck!

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]

Wow, This worked great really well. I just tried it on my already parsed file. Thank you so much.

[to...@gmx.net]

I've been searching prrety much the whole web, to find a solution for this kind of problem. Thenk you very much, for that answer.

Unfortunately the zip method does'nt seem to work when I want to dump several lists in an excel file while write_only-mode is True.

Do you maybe have solution for this?

With most regards.

Vic

Am Mittwoch, 2. Dezember 2015 17:49:36 UTC+1 schrieb Charlie Clark:

[ssi.mari...@gmail.com]

Hi Charlie,

Sorry to reopen this old topic, but whenever I use your code, my row gets appended to a higher row. (see attached)

Instead of appending to row 7, it appends it to row 8. 

Do you have any clue on how to solve this?

[Charlie Clark]

On 9 Apr 2020, at 15:40, ssi.mari...@gmail.com wrote:

> Hi Charlie,
>
> Sorry to reopen this old topic, but whenever I use your code, my row
> gets
> appended to a higher row. (see attached)
> Instead of appending to row 7, it appends it to row 8.
> Do you have any clue on how to solve this?

Not really. You can check what the ws.max_row is and adjust accordingly.
If it's 7 then delete that row and try again.

[Charlie Clark]

On 15 Nov 2019, at 9:48, to...@gmx.net wrote:

> I've been searching prrety much the whole web, to find a solution for
> this
> kind of problem. Thenk you very much, for that answer.
> Unfortunately the zip method does'nt seem to work when I want to dump
> several lists in an excel file while write_only-mode is True.
> Do you maybe have solution for this?

I'm not sure why it wouldn't work. Write-only mode pretty much only
supports adding rows.

[ssi.mari...@gmail.com]

I tried to copy a row from Workbook 1 and append it to existing Workbook 2.

import openpyxl as xl

from openpyxl.utils import range_boundaries

min_cols, min_rows, max_cols, max_rows = range_boundaries('A:GH')

row_data = 0 #variable to store row position of the looked-up value.

for row in ws1.iter_rows():

    for cell in row:

        if cell.value == "Positive":

            row_data += cell.row

for row in ws1.iter_rows(min_row=row_data, min_col = 1, max_col=99, max_row = row_data):

    ws2.append((cell.value for cell in row[min_cols:max_cols]))  

This does the same thing. Appends the right row from Workbook 1 in Workbook 2 but on row 8 instead of 7(next empty row).
I've been struggling with this for quite a while and didn't found any solution.

Do you have any idea/feedback for me? 

Best,

M 

[Marius Tripa]

I will open a new thread with this topic, so there won't be any spamming and offtopic to the main post.