python uses xlrd and xlwt to read, write and format excel

Recently, some problems occurred when using python to process excel tables, so I wanted to record the final implementation method and problem solving method. For your own convenience or for everyone to refer to when needed, the following article mainly introduces the relevant information on Python's use of xlrd and xlwt to read, write and format Excel. Let's learn together.

Preface

Python mainly uses the two libraries xlrd and xlwt to operate excel, that is, xlrd is a library for reading excel, and xlwt is a library for writing excel. This article mainly introduces python's use of xlrd and xlwt to read, write and format excel. Without further ado, let's take a look at the detailed implementation process.

Please indicate # in the script first -*- coding:utf-8 -*-

1. Confirm that the source excel exists and use xlrd to read the first The value in the first column of each row in a form.

import xlrd, xlwt 
import os 
 
assert os.path.isfile('source_excel.xls'),"There is no timesheet exist. Exit..." 
 
book = xlrd.open_workbook('source_excel.xls') 
sheet=book.sheet_by_index(0) 
 
for rows in range(sheet.nrows): 
 value = sheet.cell(rows,0).value

2. Use xlwt to prepare the data read from the source table and write it into the new table, and set the row width and The format of the table. After merging 2 rows and 8 columns of cells, write the title and set the format to title_style defined previously.

Use write_merge.

wbk = xlwt.Workbook(encoding='utf-8') 
sheet_w = wbk.add_sheet('write_after', cell_overwrite_ok=True) 
sheet_w.col(3).width = 5000 
tittle_style = xlwt.easyxf('font: height 300, name SimSun, colour_index red, bold on; align: wrap on, vert centre, horiz center;') 
sheet_w.write_merge(0,2,0,8,u'这是标题',tittle_style)

3. When using global variables in a function, be sure to add global. Otherwise, UnboundLocalError: local variable'xxx' referenced before assignment.

check_num = 0 
 
def check_data(sheet): 
 global check_num 
 check_num=check_num+1

4. Write date and formatted values. The original date format read from the sheet is 2014/4/10. After processing, only the date is retained and made into an array separated by commas and written into a new excel.

date_arr = [] 
date=sheet.cell(row,2).value.rsplit('/')[-1] 
if date not in date_arr: 
  date_arr.append(date) 
sheet_w.write_merge(row2,row2,6,6,date_num, normal_style) 
sheet_w.write_merge(row2,row2,7,7,','.join(date_arr), normal_style)

5. When the date format read from excel is xldate, you need to use xldate_as_tuple of xlrd to process it as date Format. First determine that the ctype of the table is indeed xldate before starting the operation, otherwise an error will be reported. The date format can then be converted to string using strftime. For example: date.strftime("%Y-%m-%d-%H")

##

from datetime import date,datetime 
from xlrd import xldate_as_tuple 
 
if (sheet.cell(rows,3).ctype == 3): 
  num=num+1 
  date_value = xldate_as_tuple(sheet.cell_value(rows,3),book.datemode) 
  date_tmp = date(*date_value[:3]).strftime("%d")

wbk.save('new_excel.xls')