Using Python, write an Excel file with columns copied from another Excel file
101,117
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')
Author by
salamey
Updated on November 07, 2020Comments
-
salamey over 3 years
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?
-
Mike Issa almost 8 yearsHi, could you please tell me how to copy a column instead of a row?
-
alecxe almost 8 years@MikeIssa sure, I think you just need to swap
index
and 0 (0 would be the column index you want to copy). -
Mike Issa almost 8 yearsFigured it out.
data = [sheet.cell_value(row, 0) for row in range(sheet.nrows)]
-
Marc Maxmeister about 4 yearsIn 2020, the best/easiest option may be pandas dataframe.read_excel and pandas dataframe.to_excel. ref: pandas.pydata.org/pandas-docs/stable/reference/api/… -- pandas appears to use xlsxwriter under the hood, so that is probably the best maintained standalone package of the 4 now.
-
Dipen Shah about 3 yearshow to read the value of cell row=1,col=1 with xlwt package ?