python处理Excel实现自动化办公教学（数据筛选、公式操作、单元格拆分合并、冻结窗口、图表绘制等）【三】

from openpyx1. styles import Alignment
import openpyx1
wb =openpyxl.load workbook( './data/ cellBorder .xlsx')
sheet =wb[ '数据筛选']
#创建筛选器对象: auto_ filter
#ref:使得筛选器对象引用指定的区域
sheet.auto_filter.ref = 'B1:D7'
wb.save( './data/ cellBorder.xlsx' )

#设置筛选条件
from openpyx1. styles import Alignment
import openpyxl
wb = openpyxl.load workbook( ' . /data/ cellBorder .xlsx')
sheet=wb['数据筛选']

#创建筛选器对象: auto_ filter
#ref:使得筛选器对象引用指定的区域

sheet.auto_filter.ref ='B1:D7 '
#add_filter_column参数:参数1表示对指定区域那一列进行设置筛选条件，参数2: 筛选条件内容
sheet.auto_filter.add_filter_column(0,['北京'，'深圳'])
wb.save('./data/ cellBorder.xlsx')


#样式需要去excel里点击保存即可

#排序
from openpyxl. styles import Alignment
import openpyx1
wb = openpyxl.load workbook('. /data/ cellBorder .xlsx')
sheet = wb['数据筛选']
sheet.auto_filter.ref = 'B1:D7'
#参数1:排序列.参数2:升降序
sheet.auto_filter.add_sort_condition(ref= 'D2 :D9 '，descending=True)
wb.save('./data/ cellBorder . xlsx')

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=SUM(A1:A2)'
wb.save('./data/sum.xlsx')

import openpyxl
wb = openpyxl.load_workbook( '.[data/sum.xlsx',read only=True)
sheet = wb.active
#注意:如果返回的是None,则打开exce1.工作簿，将内容手动保存下即可，不方便，但是没有办法
print(sheet[ 'A3' ] .value )

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
sheet['A1'] = 'Tall row'
sheet['B2'] = 'Wide column'
wb.save('./data/dimensions.xlsx')

sheet.row_dimensions
#因为表里只有两行数据所以显示两行
{1: <openpyxl.worksheet.dimensions.RowDimension at 0x108af1f98>,
 2: <openpyxl.worksheet.dimensions.RowDimension at 0x108af1dd8>}

sheet.column_dimensions

DimensionHolder([('A',
                  <openpyxl.worksheet.dimensions.ColumnDimension at 0x108af1c88>),
                 ('B',
                  <openpyxl.worksheet.dimensions.ColumnDimension at 0x108af1978>)])

#设置行高
sheet.row_dimensions[2].height = 50
#设置列宽
sheet.column_dimensions['A'].width = 80
wb.save('./data/dimensions.xlsx')

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
sheet.merge_cells('A1:D7') #合并
sheet['A1'] = 'hello bobo'
wb.save('./data/merged.xlsx')

sheet.unmerge_cells('A1:D7')

sheet['A2'].value

#冻结
import openpyxl
wb = openpyxl.load_workbook('./data/produceSales.xlsx')
sheet = wb.get_active_sheet()
sheet.freeze_panes = 'A2'
wb.save('./data/produceSales.xlsx')

#解冻结
sheet.freeze_panes = None
wb.save('./data/produceSales.xlsx')

import openpyxl
wb = openpyxl.load_workbook(filename='./data/sampleChart.xlsx')
sheet = wb.get_active_sheet()

    
refObj = openpyxl.charts.Reference(sheet, (1, 2),(10,2))
seriesObj = openpyxl.charts.Series(refObj, title='First series')
chartObj = openpyxl.charts.BarChart()

sheet.add_data(refObj )  #图标中添加数据
chartObj.append(seriesObj) #图标中添加数据两种方式
chartObj.drawing.top = 200 # set the position
chartObj.drawing.left = 200
chartObj.drawing.width = 300 # set the size
chartObj.drawing.height = 200

chartObj.title = sheet_name        
chartObj.x_axis.title = '日期'
chartObj.y_axis.title = '营收额'

sheet.add_chart(chartObj,"G1") #添加到制定sheet中

wb.save('./data/sampleChart.xlsx')

import openpyxl
from openpyxl.charts import Reference,BarChart,Series
import os
for file_name in os.listdir('./data/Bobo公司2020-2030年营收报表'):
    if file_name != '.DS_Store':  #苹果电脑会有一个隐藏文件，去掉不处理
        file_name = os.path.join('./data/Bobo公司2020-2030年营收报表',file_name)
        ex_file = openpyxl.load_workbook(file_name)
        sheet_names = ex_file.get_sheet_names() #获取所有表
        for sheet_name in sheet_names:
            sheet_file = ex_file.get_sheet_by_name(sheet_name)
            data=Reference(sheet_file, min_col=2,min_row =1，max_row=32，max_col=4 ) #推荐这种写法方便
          """  
            data1 = Reference(sheet_file, (2, 2),(32,2))
            data2 = Reference(sheet_file, (2, 3),(32,3))
            data3 = Reference(sheet_file, (2, 4),(32,4))
            seriesObj1 = Series(data1,title='手机')
            seriesObj2 = Series(data2,title='彩妆')
            seriesObj3 = Series(data3,title='电脑')
            """
            bc = BarChart()
            bc.title = sheet_name
            bc.x_axis.title = '日期'
            bc.y_axis.title = '营收额'
            #bc.append(seriesObj1)
            #bc.append(seriesObj2)
            #bc.append(seriesObj3)
            bc.add_data(data, titles_from_data=True )

         
            bc.drawing.top = 200 # set the position
            bc.drawing.left = 400
            bc.drawing.width = 600 # set the size
            bc.drawing.height = 400
            #使用日期这一列作为x轴
            x_label=Reference(sheet_file, min_col=1,min_row= =2,max_row=32 )
            bc.set_categories(x_label )
            sheet_file.add_chart(bc,'E5' ) 

            #sheet_file.add_chart(bc)
    ex_file.save(file_name)