concatenate data in excel using openpyxl in Python

Question

Here comes again, I have managed to collect specific data from resx file (xml) to generate an Excel file. Now, the task is to concatenate all the data to lowest row in this excel file.

First opening the input file to read(r) and write(w).

wb = load_workbook('Excel.xlsx')

access the worksheet named 'Sheet'

ws=get_sheet_by_name('Sheet')

So now, I need to Concatenate the data from all the cells in one column to the last empty cell of that column. Then generate this new excel file.

for example, Column Name: any column row1: ABC row2: EFG row3: HIJ

last row after concatenation should look like,

row4 : ABC EFG HIJ

As a python beginner this seems to be a pretty hard stuff for me. Please help to improve.

Thanks a lot.

ChrisP · Accepted Answer · 2012-08-10 14:40:01Z

1

Something like the following should work...

max_row = ws.get_highest_row() # find last row of worksheet
reff = "A1:A" + str(max_row) # build an Excel range covering the data
values = [cell.value for cell in ws.range(reff)] # collect the data
ws.cell('A' + str(max_row + 1)).value = ' '.join(values) # write values

The documentation for this module is pretty good. Look it over and experiment.

answered Aug 10, 2012 at 14:40

ChrisP

5,9721 gold badge36 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Indigo Over a year ago

thanks a lot for help, I just made slight changes according ti my need and it is a go.... nrows= ws2.get_highest_row() ncol = ws2.get_highest_column() # some code in between.. for column in range(1,ncol): for row in range(1, nrows): abc.append(ws2.cell(row = row, column=column).value) abc.insert(1, '\n') concatABC = '\n'.join(abc) ws2.cell(row=(nrows), column=(column)).value = concatABC

Collectives™ on Stack Overflow

concatenate data in excel using openpyxl in Python

First opening the input file to read(r) and write(w).

access the worksheet named 'Sheet'

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

First opening the input file to read(r) and write(w).

access the worksheet named 'Sheet'

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related