P10.编程生成Excel内图表

P10.编程生成Excel内图表.md

#插入图片
    #openpyxl插入图片
    openpyxl.drawing.image
    sheet.add_image()
    例：
            from openpyxl.drawing.image import Image
            from openpyxl import load_workbook
            workbook = load_workbook(filename='这是一个表格.xlsx')
            sheet = workbook.active
            
            logo = Image('梅克豆.png')
            logo.height = 100
            logo.width = 100

            sheet.add_image(logo,'C1')
            workbook.save(filename='这是一个表格.xlsx')


#生成图表
    #插入柱状图
    BarChart() Reference()
    例:
        from openpyxl import load_workbook
        from openpyxl.chart import BarChart, Reference

        workbook = load_workbook(filename='简单数据.xlsx')
        sheet = workbook.active
        #新建一个柱状图
        chart = BarChart()
        #设定数据引用范围
        data = Reference(worksheet=sheet, min_row=1, max_row=7, min_col=2, max_col=3)#
        #设定X轴项目名称
        categories = Reference(sheet, min_col=1, min_row=2, max_row=7)
        
        #给柱状图添加数据，titles_from_data数据源中是否有标题
        chart.add_data(data, titles_from_data=True)
        #设定x轴类目
        chart.set_categories(categories)
        sheet.add_chart(chart, 'E2')

        workbook.save('简单数据.xlsx')


    #插入折线图
        Linechart()
        例:
        from openpyxl import load_workbook
        from openpyxl.chart import LineChart, Reference

        workbook = load_workbook(filename='简单数据.xlsx')
        sheet = workbook.active
        #新建一个折线图
        chart = LineChart()
        #设定数据引用范围
        data = Reference(worksheet=sheet, min_row=20, max_row=21, min_col=1, max_col=13)#
        #设定X轴项目名称
        categories = Reference(sheet, min_col=19, min_row=2, max_row=13)
        
        #给折线添加数据，from_rows数据源是横向的,titles_from_data数据源中是否有标题
        chart.add_data(data, from_rows=True,titles_from_data=True)
        #设定x轴类目
        chart.set_categories(categories)
        sheet.add_chart(chart, 'B23')

        workbook.save('简单数据.xlsx')

    #插入散点图
        ScatterChart()
        例：
        from openpyxl.chart import Reference,ScatterChart,Series
        chart_xy = ScatterChart()#散点图
        chart_xy.title = "XY坐标散点图"
        chart_xy.y_axis.title = 'x'
        chart_xy.x_axis.title = 'y'
        chart_xy.legend = None
        chart_xy.x_axis.scaling.min = -5
        chart_xy.y_axis.scaling.min = -3
        chart_xy.x_axis.scaling.max = 30
        chart_xy.y_axis.scaling.max = 3
        chart_xy.height = 10
        chart_xy.width = 20
        x = Reference(sheet, min_col=3, min_row=table_row_start+1, max_row=row_index)
        y = Reference(sheet, min_col=4, min_row=table_row_start+1, max_row=row_index)
        s = Series(y, xvalues=x)
        chart_xy.append(s)

        s.marker.symbol = "circle"#'square', 'x', 'star', 'plus', 'diamond', 'circle', 'triangle', 'dash', 'auto', 'picture', 'dot'
        s.marker.graphicalProperties.solidFill = "FF0000"  # Marker filling
        s.marker.graphicalProperties.line.solidFill = "FF0000"  # Marker outline
        s.marker.size = 3
        s.graphicalProperties.line.noFill = True#是否连线

        position = f"B2"
        sheet.add_chart(chart_xy, position)


    #列坐标字母和行数之间进行装换
        get_column_letter(数字)
        column_index_from_string(字母)
        例：
        from openpyxl.utils import get_column_letter, column_index_from_string
        print(get_column_letter(2)) #b
        print(column_index_from_string('B')) #2