<> one ,xlrd,xlwt Reading and writing Excel
<>1, Read operation
import xlrd # 1, Open workbook workbook xlsx = xlrd.open_workbook(r'.\7 Warehousing table in the last ten days of the month .xlsx') #
2, Open the table to be operated on sheet table = xlsx.sheet_by_index(0) # table =
xlsx.sheet_by_name('7 Warehousing table in the last ten days of the month ') # 3, Read the data of the specified cell print(table.cell_value(1,1)) print(
table.cell(1,1).value) print(table.row(1)[1].value)
<>2, Write operation
import xlwt # 1, Create a new workbook new_workbook = xlwt.Workbook() # 2, Add a worksheet to this workbook
worksheet= new_workbook.add_sheet('new_test') # 3, Write content to the specified cell worksheet.write(0,0
,'test') # 4, preservation new_workbook.save('./test.xls')
<>3, Write operation with text format
from xlutils.copy import copy import xlrd import xlwt #
1, Open the workbook to be operated , And copy it # be careful ： Use read xls All documents are used xlrd library , But this library can only operate .xls format , For later
.xlsx The version support of is not very good # formatting_info This parameter defaults to False, This saves memory ; When the value is True Hour , Can read information in various formats
tem_excel= xlrd.open_workbook(r'.\ Daily statistics .xls',formatting_info=True) # 2, Select the worksheet you want to work on
tem_sheet= tem_excel.sheet_by_index(0) # 3, Create a new workbook , By copying a template Workbook new_excel = copy
(tem_excel) # 4, Select the worksheet to be operated in the new workbook new_sheet = new_excel.get_sheet(0) # Initialize style
style= xlwt.XFStyle() # Initialize fonts font = xlwt.Font() font.name = ' Microsoft YaHei ' font.bold =
True font.height = 18 * 20 style.font = font # Initialize border , Thin border borders = xlwt.Borders(
) borders.top = xlwt.Borders.THIN borders.bottom = xlwt.Borders.THIN borders.
left= xlwt.Borders.THIN borders.right = xlwt.Borders.THIN style.borders =
borders# Initialize alignment alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.
HORZ_CENTER alignment.vert = xlwt.Alignment.VERT_CENTER style.alignment =
alignment# 5, Write data to the selected worksheet new_sheet.write(2,1,12,style) new_sheet.write(3,1,18,
style) new_sheet.write(4,1,19,style) new_sheet.write(5,1,15,style) # 6, Save file
new_excel.save('./ fill in .xls')
<>4, Comprehensive case ：
import xlrd import xlwt from xlutils.copy import copy xlsx = xlrd.open_workbook
(r'.\7 Warehousing table in the last ten days of the month .xlsx') table = xlsx.sheet_by_index(0) all_data = [] # Cycle through the data in each row
for n in range(1,table.nrows): company = table.cell(n,1).value # seller price =
table.cell(n,3).value # Unit Price weight = table.cell(n,4).value # Stock in quantity data = {
'company':company,'weight':weight,'price':price} all_data.append(data) #
The following can be used pandas of groupby Easy to achieve , Here's a stupid way a_weight = [] a_total_price = [] b_weight =
[] b_total_price = [] c_weight = [] c_total_price = [] d_weight = []
d_total_price= [] # Calculate the total stock in quantity of each vendor and Total price for i in all_data: if i['company'] ==
' Zhang San grain distribution ': a_weight.append(i['weight']) a_total_price.append(i['weight'] * i[
'price']) if i['company'] == ' Lisi grain ': b_weight.append(i['weight']) b_total_price.
append(i['weight'] * i['price']) if i['company'] == ' Wangwu wheat ': c_weight.append(i[
'weight']) c_total_price.append(i['weight'] * i['price']) if i['company'] ==
' Zhaoliu Maizi ': d_weight.append(i['weight']) d_total_price.append(i['weight'] * i[
'price']) tem_excel = xlrd.open_workbook(r'.\7 Statistics of the last ten days of the month .xls',formatting_info=True)
tem_sheet= tem_excel.sheet_by_index(0) new_excel = copy(tem_excel) new_sheet =
new_excel.get_sheet(0) style = xlwt.XFStyle() # Initialize fonts font = xlwt.Font() font.
name= ' Microsoft YaHei ' font.bold = True font.height = 18 * 20 style.font = font #
Initialize border , Thin border borders = xlwt.Borders() borders.top = xlwt.Borders.THIN borders.
bottom= xlwt.Borders.THIN borders.left = xlwt.Borders.THIN borders.right = xlwt.
Borders.THIN style.borders = borders # Initialize alignment alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER alignment.vert = xlwt.Alignment.
VERT_CENTER style.alignment = alignment new_sheet.write(2,1,len(a_weight),style)
new_sheet.write(2,2,round(sum(a_weight)),style) new_sheet.write(2,3,round(sum(
a_total_price),2),style) new_sheet.write(3,1,len(b_weight),style) new_sheet.
write(3,2,round(sum(b_weight)),style) new_sheet.write(3,3,round(sum(
b_total_price),2),style) new_sheet.write(4,1,len(c_weight),style) new_sheet.
write(4,2,round(sum(c_weight)),style) new_sheet.write(4,3,round(sum(
c_total_price),2),style) new_sheet.write(5,1,len(d_weight),style) new_sheet.
write(5,2,round(sum(d_weight)),style) new_sheet.write(5,3,round(sum(
d_total_price),2),style) new_excel.save('./7 Statistics of the last ten days of the month .xls')
Technology