CEamonn CEamonn - 3 years ago 199
Python Question

Write a new column to existing xlsx file

I've an .xlsx file with a basic format of across multiple sheets (sheet1, sheet2, sheet3)

Col1 | Col2 | Col3 | Col4 | Col5

abc1 | abc2 | abc3 | abc4 | abc5

where the number of columns varies. I want to be able to write a new given a sheet's name. At the minute I have:

import openpyxl

def get_sheets():
wb = openpyxl.load_workbook('C:/Users/mydir/Desktop/myfile.xlsx')
sheets = wb.get_sheet_names()

sheet3 = "Sheet3"
sheet4 = "Sheet4"
for i in sheets:
if i == sheet3:
# Write column
elif i == sheet4:
# Write column in different sheet

def main():

print "Script finished."

if __name__ == "__main__":

Does openpyxl allow a new column to be written to an existing xlsx file with a header and rows equaling the number of rows after the header?

Answer Source

To add a new column to an existing xlsx file, you could write an add_column() function as follows. This takes the sheet name and a list of values that you wish to add:

import openpyxl

def add_column(sheet_name, column):
    ws = wb[sheet_name]
    new_column = ws.max_column + 1

    for rowy, value in enumerate(column, start=1):
        ws.cell(row=rowy, column=new_column, value=value)

wb = openpyxl.load_workbook('C:/Users/mydir/Desktop/myfile.xlsx')
add_column('Sheet3', ['new header', 'value1', 'value2'])
wb.save('output.xlsx')   # use the same name if required

You can get the worksheet by name simply by using wb['Sheet3']. Then to get the number of existing columns in the worksheet use ws.max_column to get he last column index. Add 1 for you newly appended column.

Next use PYthon's enumerate() function to work through the list of column values. For each it gives you the row number to use and the value to write to the cell.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download