python USES xlrd and xlwt to read write and format excel

preface

The python operation excel mainly USES the xlrd and xlwt libraries, xlrd is the library that reads excel, xlwt is the library that writes excel. This article mainly introduces the read-write and format setting of excel by python using xlrd and xlwt. Without further discussion, let's take a look at the detailed implementation process.

The script starts with # -* -coding: utf-8 -*-

1. Verify that the source excel exists and xlrd reads the value of the first column of each row in the first 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. Prepare to write the data read from the source table to the new table with xlwt, and format the row width and table. Merge 2 rows and 8 columns of the cell, write the header, and format it as tittle_style as defined previously.

write_merge is used.

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' This is a title, ',tittle_style) 

3. When global variables are used in functions, add global. Otherwise it will happen UnboundLocalError:local variable'xxx' referenced before assignment.

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

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

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, xldate_as_tuple needs to be processed into date format using xlrd's xldate_as_tuple. The operation can only start when you confirm that ctype of the form is indeed xldate, otherwise an error will be reported. The date format can then be converted to string using strftime. Such as: 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") 

6. Save the newly written table at last

wbk.save('new_excel.xls') 

conclusion

The above is the whole content of this article, I hope the content of this article to your study or work can bring 1 definite help, if you have questions you can leave a message to communicate.