Delete column from xls file

Question

Using Python, I need to be able to do the following operations to a workbook for excel 2007:

1.delete colums

I am looking into xlrd; however.

Can anyone please tell me how could do this?

Answer 1

If you're working in Python 3.x, you'll find a lot of trouble using the xlrd/xlwt/xlutils family, as they're modules for Python 2.

You might consider openpyxl for working with Excel 2007 .xlsx files in Python 3.

If you just need to shift values over (disregarding formatting, etc) here's one way to do it. You can build on this:

from openpyxl import load_workbook
from openpyxl.cell import column_index_from_string as col_index
from openpyxl.cell import get_column_letter as col_letter

def del_col(s, col, cmax=None, rmax=None):
    col_num = col_index(col) - 1
    cols = s.columns
    if isinstance(cmax, str):
        cmax = col_index(cmax)
    cmax = cmax or s.get_highest_column()
    rmax = rmax or s.get_highest_row()
    for c in range(col_num, cmax + 1):
        # print("working on column %i" % c)
        for r in range(0, rmax):
            cols[c][r].value = cols[c+1][r].value
    for r in range(0, rmax):
        cols[c+1][r].value = ''

    return s

if __name__ == '__main__':
    wb = load_workbook('input_book.xlsx')
    ws = wb.active
    # or by name: ws = wb['SheetName']
    col = 'D'
    del_col(ws, col)
    wb.save('output_book.xlsx')