打开Excel并激活active
import openpyxl
wb = openpyxl.Workbook() #做一个虚拟的excelbook,后面wb.save()建立文件
或者
wb = xlrd.open_workbook(“H:\......”) #打开excel
ws = wb.active #激活wb
Ws.title=”课程表” #改表名
Ws['A1'].value = “lebo.com” #修改A1的值
Wb.save(r”cours.xlsx”) #保存并改名
Values_line = sheet.row_values(9) #指定第9行
worksheet.set_column('A:A',10) # 设置列宽
bold = workbook.add_format({'bold':True}) # 设置粗体
money = workbook.add_format({'num_format':'$#,##0'})# 定义数字格式
worksheet.write('A1','data',bold) # 写入带粗体的数据
worksheet.write('B1','work')
worksheet.write(0, 0, 'Hello') # write_string()
worksheet.write(3, 0, 3.00001) # write_number()
worksheet.write(4, 0, '=SIN(PI()/4)') # write_formula()
worksheet.write(5, 0, '') # write_blank()
worksheet.write('A3',15)
worksheet.write_row('A4',headings) # 按行插入数据
worksheet.write_column('A5',data[0]) # 按列插入数据
ws.append([1,2,3]) #在数据的最后一行依次添加1,2, 3
worksheet.write('E3','=SUM(A3:D3)') # xlsx计算数据
for each in result:
ws.append(each) #添加所有
ws= wb.get_sheet_by_name(‘sheet’) #获取工作表
ws1= wb.create_sheeet() #创建工作表
单元格属性:
row列,column行,.value值,.offset偏移
c=ws['A2']
>>>c.row
2
>>>c.column
A
>>>c.coordinate
A2
>>>ws['A2'].value #获取A2的值,等于c.value
‘肖申克的救赎’
>>>d = c.offset(2,0) #从单元格c,向下走2行0列
>>>d.value
‘这个杀手不太冷’
文本对齐:
Openpyxl.styles.Alignment
合并单元格:
Ws.merge_cells(‘A1:C2’)
设置样式:
From openpyxl.styles import NamedStyle
创建并设置样式:
Highlight = NamedStyle(name = “highlight”)
Highlight.font = Font(bold = True,size = 20)
Highlight.alignment = Alignment(horizontal = “center” , vertical = “center”)
添加样式到wb中
Wb.add_named_stytle(highlight)
定义A1单元格的样式
Ws[‘A1’].STYLE = highlight
Ws['A1'].style = highlight