CODEWITHSUNDEEP
pythonxlsxopenpyxl
CEamonn
CEamonn

Reputation: 925

Write a new column to existing xlsx file

I have 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
    else:
        continue


def main():
    get_sheets()

    print
    print "Script finished."


if __name__ == "__main__":
    main()

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

Upvotes: 2

Views: 7944

Answers (1)

Martin Evans
Martin Evans

Reputation: 46759

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 the last column index. Add 1 for your 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.

Upvotes: 6

Related Questions