Using Python, write an Excel file with columns copied from another Excel file

Question

I have an Excel file containing a varying number of columns, I would like to loop through certain columns (from their header row value) of that file using Python, then write (copy) those columns to another Excel file.

Any examples on how I can do this please?

Answer 1

Here are some options to choose from:

• xlwt (writing xls files)
• xlrd (reading xls/xlsx files)
• openpyxl (reading/writing xlsx files)
• xlsxwriter (writing xlsx files)

If you need to copy only data (without formatting information), you can just use any combination of these tools for reading/writing. If you have an xls file, you should go with xlrd+xlwt option.

Here's a simple example of copying the first row from the existing excel file to the new one:

import xlwt
import xlrd

workbook = xlrd.open_workbook('input.xls')
sheet = workbook.sheet_by_index(0)

data = [sheet.cell_value(0, col) for col in range(sheet.ncols)]

workbook = xlwt.Workbook()
sheet = workbook.add_sheet('test')

for index, value in enumerate(data):
    sheet.write(0, index, value)

workbook.save('output.xls')