python处理excel

1，合并单元格

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet("sheet1")
worksheet.write_merge(4, 6, 0, 5, '合并')#合并第五行到第七行，合并第一列到第六列
workbook.save('test.xls')

2，新增sheet，不覆盖原有sheet（参考：官方说明文档）

import pandas as pd
sheetnamelist=['sheet1','sheet2']
#for i in range(2):
    #方法1：插入不同sheet
with pd.ExcelWriter('output.xlsx') as writer:
    for a in range(len(sheetnamelist)):
        df = pd.DataFrame([[a, 'b'], ['c', 'd']], index=['row 1', 'row 2'], columns=['col 1', 'col 2'])
        df.to_excel(writer, sheet_name=sheetnamelist[a])
#方法2：插入不同sheet
with pd.ExcelWriter('output.xlsx',mode='a') as writer:
    df = pd.DataFrame([['c', 'd'], ['c', 'd']], index=['row 1', 'row 2'], columns=['col 1', 'col 2'])
    #同sheet插入数据
    df2 = pd.DataFrame([[5, 6], [7, 8]], index=['row 3', 'row 4'],columns=['col 1', 'col 2'])
    df=df.append(df2)
    df.to_excel(writer, sheet_name='sheet3')

3，DataFrame追加，可用于同sheet追加

import pandas as pd
df = pd.DataFrame([[1, 2], [3, 4]], index=['a', 'b'], columns=['A', 'B'])
print(df)
df2 = pd.DataFrame([[5, 6], [7, 8]],index=['c', 'd'],columns=['A', 'B'])
df1=df.append(df2)
print(df1)

4,excel样式（参考：官方说明文档）
方法1:pandas

import pandas as pd
import numpy as np

np.random.seed(24)
df = pd.DataFrame({'A': np.linspace(1, 10, 10)})
df = pd.concat([df, pd.DataFrame(np.random.randn(10, 4), columns=list('BCDE'))],
               axis=1)
df.iloc[3, 3] = np.nan
df.iloc[0, 2] = np.nan
#背景色
def highlight_max(s):
    is_max = s == s.max()
    #is_max = s >1.5
    return ['background-color: yellow' if v else '' for v in is_max]
#字体色
def color_negative_red(val):
    color = 'red' if val < 0 else 'black'
    return 'color: %s' % color

df.style.applymap(color_negative_red).apply(highlight_max).to_excel('styled.xlsx', engine='openpyxl')

方法2:xlwt，参考：链接

import xlwt
for sheetname in ['a','b']:
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet(sheetname, cell_overwrite_ok=True)
    pattern = xlwt.Pattern() # Create the Pattern
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
    pattern.pattern_fore_colour = 2 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
    style = xlwt.XFStyle() # Create the Pattern
    style.pattern = pattern # Add Pattern to Style

    for x in [2,4,3,6]:
        if x>3:
            worksheet.write(0, 2, x, style)
            workbook.save('Excel_Workbook.xls')
        else:
            worksheet.write(0, 2, x)
            workbook.save('Excel_Workbook.xls')