Excel数据汇总
数据汇总是Excel中常用的功能，而在Python中使用通过遍历所有单元格即可实现
代码
import xlrd from xlutils.copy import copy def read_data(): wb = xlrd.open_workbook('./base_data/data01.xlsx') sh = wb.sheet_by_index(0) fen_type = {} # {a:110,b:300} count_price = [] # [1,2,3,4,5,6] for r in range(sh.nrows): count = sh.cell_value(r,3) * sh.cell_value(r,4) count_price.append(count) key = sh.cell_value(r,0) if fen_type.get(key): fen_type[key] += count else: fen_type[key] = count return fen_type,count_price # 各分类的总价值, 每个单品的总价值 def save(fen,count): wb = xlrd.open_workbook('./base_data/data01.xlsx') sh_t = wb.sheet_by_index(0) wb2 = copy(wb) sh = wb2.get_sheet(0) for r in range(sh_t.nrows): sh.write(r,sh_t.ncols,count[r]) sh2 = wb2.add_sheet('汇总的数据') for i,key in enumerate(fen.keys()): sh2.write(i,0,key) sh2.write(i,1,fen.get(key)) wb2.save('./create_data/05_汇总数据.xlsx') if __name__ == "__main__": f,c = read_data() save(f,c)
问题
xlrd.biffh.XLRDError: Excel xlsx file; not supported
解决方案：
更换版本
pip uninstall xlrd
pip install xlrd==1.2.0
Excel数据拆分-xlrd
代码
import xlrd from xlutils.copy import copy def get_data(): wb = xlrd.open_workbook('./base_data/data01.xlsx') sh = wb.sheet_by_index(0) ''' { a: [{},{},{}], b:[{},{},{}], c:[{},{},{}], } ''' all_data = {} for r in range(sh.nrows): d = {'type':sh.cell_value(r,1),'name':sh.cell_value(r,2),'count':sh.cell_value(r,3),'price': sh.cell_value(r,4)} key = sh.cell_value(r,0) if all_data.get(key): all_data[key].append(d) else: all_data[key] = [d] return all_data def save(data): wb = xlrd.open_workbook('./base_data/data01.xlsx') wb2 = copy(wb) for key in data.keys(): temp_sheet = wb2.add_sheet(key) for i, d in enumerate(data.get(key)): temp_sheet.write(i,0,d.get('type')) temp_sheet.write(i,1,d.get('name')) temp_sheet.write(i,2,d.get('count')) temp_sheet.write(i,3,d.get('price')) wb2.save('./create_data/06_表格的拆分.xlsx') if __name__ == "__main__": all_data = get_data() # save(all_data) print(all_data)
Excel读取数据-openpyxl
Python操作Excel的一个常用库openpyxl，openpyl是第3方模块库，可以方便的对excel执行读取与写入操作
安装
pip install openpyxl
常用方法与属性
| 函数名&属性 | 含义 |
| openpyxl.load_workbook(path) | 加载Excel文件 |
| workbook.active | 激活第1个工作薄 |
| workbook[name] | 获取根据名字工作薄 |
| workbook.get_sheet_by_name(name) | 获取根据名字工作薄 |
| workbook.sheetnames | 获取所有工作薄名字 |
| sheet.title | 获取工作薄名字 |
| sheet.max_row | 获取行数 |
| sheet.max_column | 获取列数 |
| sheet.cell(row,col) | 获取指定单元格 |
| sheet[cell_name] | 获取指定单元格,例如: C2 |
| sheet[col/row_name] | 获取整行或整列单元格:填写num为整 行,row_name为整列 |
| sheet[col:col] | 获取指定范围整行单元格 |
| sheet[cell_name:cell_name] | 获取指定范围单元格 |
| sheet.iter_rows(min_row =0 , max_row =0, min_col =0, max_col =0) |
获取指定范围单元格 |
| sheet.rows | 获取所有行 |
| sheet.columns | 获取所有列 |
| cell.value | 获取单元格的值 |
代码
# pip install openpyxl def open(): from openpyxl import load_workbook wb = load_workbook('./base_data/data01.xlsx') # 获取工作薄 sh1 = wb.active sh2 = wb['Sheet1'] sh3 = wb.get_sheet_by_name('Sheet1') print(sh1 is sh2 is sh3) def show_sheets(): from openpyxl import load_workbook wb = load_workbook('./base_data/data01.xlsx') print(wb.sheetnames) for sh in wb: print(sh.title) def get_one_value(): from openpyxl import load_workbook wb = load_workbook('./base_data/data01.xlsx') sh1 = wb.active value1 = sh1.cell(2,3).value value2 = sh1['c2'].value print(value1,value2) def get_many_value(): from openpyxl import load_workbook wb = load_workbook('./base_data/data01.xlsx') sh1 = wb['Sheet1'] # 切片 cells1 = sh1['c2':'d3'] # print(cells1) # 整行，整列 cell_row3 = sh1[3] cell_col3 = sh1['c'] print(cell_row3) print(cell_col3) cell_row3_5 = sh1[3:5] print(cell_row3_5) # 通过迭代获取数据 # for row in sh1.iter_rows(min_row =2 , max_row =5, max_col =3): # for cell in row: # print(cell.value) for row in sh1.iter_rows(min_row =2 , max_row =5, min_col =2, max_col =4): for cell in row: print(cell.value) def get_all_data(): from openpyxl import load_workbook wb = load_workbook('./base_data/data01.xlsx') sh1 = wb['Sheet1'] for row in sh1.rows: for cell in row: print(cell.value) for column in sh1.columns: for cell in column: print(cell.value) def get_num(): from openpyxl import load_workbook wb = load_workbook('./base_data/data01.xlsx') sh1 = wb['Sheet1'] print(sh1.max_row) print(sh1.max_column) if __name__ == "__main__": # open() # show_sheets() # get_one_value() # get_many_value() # get_all_data() get_num()
Excel写入数据-openpyxl
常用方法与属性
| 函数名&属性 | 含义 |
| Workbook() | 创建Excel文件 |
| workbook.create_sheet(name, <num> ) | 在指定位置创建sheet表 |
| cell.value | 获取单元格值,也可直接赋值 |
| cell.font | 获取字体样式,也可直接赋值 |
| cell.alignment | 获取单元格内容位置,也可直接赋值 |
| openpyxl.styles.Font() | 创建字体样式 |
| openpyxl.styles.Alignment() | 创建单元格内容位置样式 |
| sheet.row_dimensions[num].height | 设置指定行高度 |
| sheet.column_dimensions[col_name].width | 设置指定列宽度 |
代码
from openpyxl import Workbook def create_excel(): # 创建excel wb = Workbook() # 激活第1个工作薄 ws1 = wb.active # 创建工作薄 ws2 = wb.create_sheet('数据') # 在指定位置创建工作薄、 ws3 = wb.create_sheet('人员',0) # 保存数据 wb.save('./create_data/09_创建excel文件.xlsx') def set_value1(): # 创建excel wb = Workbook() # 激活第1个工作薄 ws1 = wb.active # 写入数据 ws1['A1'] = 'Hello' ws1['B2'] = 'Excel' ws1['C3'] = 'Python' # 保存数据 wb.save('./create_data/10_创建excel文件_写入数据.xlsx') def set_value2(): # 创建excel wb = Workbook() # 激活第1个工作薄 ws1 = wb.active # 需要写入的数据 data = ['Hello','Excel','Python'] for i,d in enumerate(data): ws1.cell(i+1,i+1).value = d # 注意 openpyxl里面 记数是从1开始 # 保存数据 wb.save('./create_data/11_创建excel文件_写入数据2.xlsx') def set_value3(): # 创建excel wb = Workbook() # 激活第1个工作薄 ws1 = wb.active # 需要写入的数据 data = ['Hello','Excel','Python'] # 把数据追加到工作薄 ws1.append(data) # 保存数据 wb.save('./create_data/12_创建excel文件_写入数据3.xlsx') if __name__ == '__main__': # create_excel() # set_value1() # set_value2() set_value3()
Excel写入样式-openpyxl
常用方法与属性
| 函数名&属性 | 含义 |
| Workbook() | 创建Excel文件 |
| openpyxl.styles.Font() | 创建字体样式 |
| openpyxl.styles.Alignment() | 创建单元格内容位置样式 |
| sheet.column_dimensions[col_name].width | 设置指定列宽度 |
| sheet.row_dimensions[num].height | 设置指定行高度 |
| openpyxl.styles.Border() | 设置边框样式 |
| openpyxl.styles.Side() | 设置边的样式 |
| openpyxl.styles.PatternFill() | 设置背景样式 |
| cell.font | 获取字体样式,也可直接赋值 |
| cell.alignment | 获取单元格内容位置,也可直接赋值 |
| cell.border | 获取边框样式,也可直接赋值 |
| cell.fill | 获取背景样式,也可直接赋值 |
| openpyxl.styles.colors | 设置颜色 |
代码
字体样式与内容位置样式代码
from openpyxl import Workbook from openpyxl.styles import Font,colors,Alignment def set_font(): ''' 设置字体样式 ''' wb = Workbook() ws = wb.active ws['A1'] = 'Hello' ws['B2'] = 'Python' ws['C3'] = 'Excel' # 创建一个字体样式对象 ft = Font(name='微软雅黑',size=30,italic=True,bold=True,color=colors.BLUE) ft2 = Font(name='微软雅黑',size=30,italic=True,bold=True,color='CDAD00') # 将字体的样式应用到单元格 ws['B2'].font = ft ws['C3'].font = ft2 # 保存数据 wb.save('./create_data/13_openpyxl_字体样式.xlsx') def set_aligment(): ''' 设置内容对齐方式 ''' wb = Workbook() ws = wb.active ws['A1'] = 'Hello' ws['B2'] = 'Python' ws['C3'] = 'Excel' # 创建一个对齐方式对象 aligment = Alignment(horizontal='left',vertical='top') aligment2 = Alignment(horizontal='right',vertical='bottom') # 设置单元格的宽与高 ws.row_dimensions[1].height = 50 ws.column_dimensions['A'].width = 50 ws['B2'].alignment = aligment ws['C3'].alignment = aligment2 # 保存数据 wb.save('./create_data/14_openpyxl_位置样式.xlsx') if __name__ =='__main__': # set_font() set_aligment()
边框样式与背景样式代码
from openpyxl import Workbook from openpyxl.styles import Border,Side,colors,PatternFill def set_border(): ''' 设置边框样式 ''' wb = Workbook() ws = wb.active ws['A1'] = 'Hello' ws['B2'] = 'Python' ws['C3'] = 'Excel' # 创建一个线的样式对象 side = Side(color=colors.BLUE,style='medium') side2 = Side(color='00008000',style='double') # 创建边框样式对象 border = Border(left=side,right=side2,top=side,bottom =side2) # 将边框样式应用到单元格 ws['B2'].border = border # 保存数据 wb.save('./create_data/15_openpyxl_边框样式.xlsx') def set_pattern(): ''' 设置背景颜色 ''' wb = Workbook() ws = wb.active ws['A1'] = 'Hello' ws['B2'] = 'Python' ws['C3'] = 'Excel' # 创建背景的样式对象 pattern = PatternFill('solid',fgColor=colors.BLUE) # 将背景颜色样式应用单元格， 不要用pattern属性 ws['B2'].fill = pattern # AttributeError: 'Cell' object has no attribute 'pattern' # 保存数据 wb.save('./create_data/16_openpyxl_背景样式.xlsx') if __name__ =='__main__': # set_border() set_pattern()
