Python reading and writing excel file operation example [Download with source code]

Time:2019-10-18

The example of this paper describes the operation of reading and writing excel file by python. To share with you for your reference, as follows:

For the operation of Excel files, python has the third-party toolkit support, xlutils. In this toolkit, xlrd, xlwt and other toolkits are included. With these tools, Excel can be operated conveniently.

1. Download xlutils: http://pypi.python.org/pypi/xlutils

2. Install. After decompressing the download file, you can use Python setup.py install.

3. Application (generate excel, traverse excel, modify excel, attribute control, date control, etc.).

1) create excel file


from tempfile import TemporaryFile
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
book.add_sheet('Sheet 2')
sheet1.write(0,0,'A1')
sheet1.write(0,1,'B1')
row1 = sheet1.row(1)
row1.write(0,'A2')
row1.write(1,'B2')
sheet1.col(0).width = 10000
sheet2 = book.get_sheet(1)
sheet2.row(0).write(0,'Sheet 2 A1')
sheet2.row(0).write(1,'Sheet 2 B1')
sheet2.flush_row_data()
sheet2.write(1,0,'Sheet 2 A3')
sheet2.col(0).width = 5000
sheet2.col(0).hidden = True
book.save('simple.xls')
book.save(TemporaryFile())

This generates the simple. XLS file.

2) loop through Excel file


import xlrd
import xlutils.copy
import os
if __name__ == '__main__':
  wb = xlrd.open_workbook('simple.xls')  
  for s in wb.sheets():
    print 'Sheet:',s.name
    for row in range(s.nrows):
      values = []
      for col in range(s.ncols):
        values.append(s.cell(row,col).value)
      print ','.join(values)
    print

Traverse the whole excel and print out the data

3) modify Excel


import xlrd
import xlutils.copy
import os
if __name__ == '__main__':
  template = "simple.xls"
  workBook = xlrd.open_workbook(template,formatting_info=True)
  workBook = xlutils.copy.copy(workBook)
  sheet = workBook.get_sheet(0)
  sheet.write(0, 0, '111')
  sheet.write(0, 1, '222')
  sheet.write(1, 0, '333')
  sheet.write(1, 1, '444')  
  workBook.save('simple.xls')

Complete instance code click hereDownload this website

For more information about Python related content, you can see our special topics: summary of Python operation excel table skills, summary of Python file and directory operation skills, summary of Python text file operation skills, python data structure and algorithm tutorial, summary of Python function use skills, summary of Python string operation skills, and introduction to Python. And advanced classics

I hope that this article is helpful for Python programming.