Excel----python

打开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