使用openpyxl写excel文档及画图样例

# encoding=utf8
from openpyxl import Workbook
from openpyxl.chart import AreaChart, Reference
from openpyxl.styles import Protection, Alignment, PatternFill, Font, Side, Border, colors

from lianxi import MysqlHelper


def pull_data():
    db=MysqlHelper('192.168.80.112', '***', 'root', 'mysql')
    res=db.all('select * from ceshi where name="wang";')
    return res

class Create_excel(object):

    def __init__(self, ziduans):
        self.ziduans = ziduans
        self.temp_dict = {1: 'A', 2: 'B', 3: 'C', 4: 'D'}

    def run(self, data):
        wb=Workbook()
        ws1=wb.create_sheet("Mysheet")
        ws1.sheet_properties.tabColor="1072BA"
        # for row in range(1,40):
        #     ws1.append(range(20))
        # ws1['B40']='= SUM(B1: B39)'
        font = Font(name='Calibri', size=11, bold=True, italic=False, vertAlign=None, underline='none', strike=False,
                    color='FF000000')
        fill = PatternFill("solid", fgColor="B0C4DE") # fgColor单元格背景色
        border = Border(left=Side(border_style="thin", color='FF000000'), right=Side(border_style="thin", color='FF000000'),
                        top=Side(border_style="thin", color='FF000000'), bottom=Side(border_style="thin", color='FF000000'),
                        diagonal=Side(border_style=None, color='FF000000'), diagonal_direction=0,
                        outline=Side(border_style=None, color='FF000000'),
                        vertical=Side(border_style="thin", color='FF000000'),
                        horizontal=Side(border_style="thin", color='FF000000'))
        alignment = Alignment(horizontal='center', vertical='bottom', text_rotation=0, wrap_text=False,
                              shrink_to_fit=False, indent=0)
        alignment1 = Alignment(horizontal='left', vertical='bottom', text_rotation=0, wrap_text=False,
                              shrink_to_fit=False, indent=0)
        for i in range(1, len(self.ziduans)+1):
            ws1[self.temp_dict.get(i)+'1'] = self.ziduans[i-1]
            ws1[self.temp_dict.get(i)+'1'].font = font
            ws1[self.temp_dict.get(i)+'1'].fill = fill
            ws1[self.temp_dict.get(i)+'1'].border = border
            ws1[self.temp_dict.get(i)+'1'].alignment = alignment

        j = 2
        for i in data:
            ws1['A{}'.format(j)] = i[1]
            ws1['B{}'.format(j)] = i[2]
            ws1['C{}'.format(j)] = i[3]
            ws1['A{}'.format(j)].border = border
            ws1['B{}'.format(j)].border = border
            ws1['C{}'.format(j)].border = border
            ws1['A{}'.format(j)].alignment = alignment1
            ws1['B{}'.format(j)].alignment = alignment1
            ws1['C{}'.format(j)].alignment = alignment1
            # 调整行高
            ws1.row_dimensions[j].height = 20
            j += 1
        # 调整列宽
        ws1.column_dimensions['A'].width = 40.0
        ws1.column_dimensions['B'].width = 40.0
        ws1.column_dimensions['C'].width = 40.0

        chart = AreaChart()
        chart.title = "Area Chart"
        chart.style = 13
        chart.x_axis.title = 'Test'
        chart.y_axis.title = 'Percentage'

        cats = Reference(ws1, min_col=1, min_row=2, max_row=j)
        data = Reference(ws1, min_col=2, min_row=2, max_col=2, max_row=j)
        chart.add_data(data, titles_from_data=True)
        chart.set_categories(cats)

        ws1.add_chart(chart, "E2")

        wb.save("area.xlsx")
        wb.save('./template6.xlsx')

if __name__ == '__main__':
    data = pull_data()
    c = Create_excel(['name', 'size', 'time'])
    c.run(data)