31

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?

VividD
  • 10,456
  • 6
  • 64
  • 111
salamey
  • 3,633
  • 10
  • 38
  • 71

1 Answers1

61

Here are some options to choose from:

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')
RobotHumans
  • 807
  • 10
  • 25
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • Hi, could you please tell me how to copy a column instead of a row? – Mike Issa Jun 15 '16 at 15:51
  • 2
    @MikeIssa sure, I think you just need to swap `index` and 0 (0 would be the column index you want to copy). – alecxe Jun 15 '16 at 16:11
  • Figured it out. `data = [sheet.cell_value(row, 0) for row in range(sheet.nrows)]` – Mike Issa Jun 15 '16 at 17:38
  • 1
    In 2020, the best/easiest option may be pandas dataframe.read_excel and pandas dataframe.to_excel. ref: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html -- pandas appears to use xlsxwriter under the hood, so that is probably the best maintained standalone package of the 4 now. – Marc Maxmeister May 14 '20 at 15:53
  • how to read the value of cell row=1,col=1 with xlwt package ? – Dipen Shah Apr 23 '21 at 16:33