Chinaunix首页 | 论坛 | 博客
  • 博客访问: 157834
  • 博文数量: 31
  • 博客积分: 25
  • 博客等级: 民兵
  • 技术积分: 280
  • 用 户 组: 普通用户
  • 注册时间: 2011-06-28 06:36
个人简介

还在学习oracle的路上。

文章分类

全部博文(31)

文章存档

2020年(9)

2018年(3)

2017年(6)

2016年(11)

2015年(2)

我的朋友

分类: Python/Ruby

2020-03-18 01:25:47

主要功能就是把F列三行合并居中



点击(此处)折叠或打开

  1. #!/usr/bin/python3
  2. # -*- coding: utf-8 -*-
  3. # Time : 2020/3/5 19:49
  4. # Author : xuekai
  5. # Email : xuekai20080901@aliyun.com
  6. # File : excel.py
  7. # Project : python
  8. import os
  9. import openpyxl
  10. from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment


  11. # 函数:范围单元格求和
  12. def range_sum(worksheet, start, end):
  13.     sum = 0
  14.     for row in worksheet[start:end]:
  15.         for cell in row:
  16.             if cell.value != None:
  17.                 sum += cell.value
  18.     return sum


  19. # 函数:对某sheet求和并居中
  20. def total_amount(worksheet):
  21.     ws = worksheet
  22.     row, max_row = 2, ws.max_row
  23.     print("row:%s max_row:%s ws.max_row:%s" %(row,max_row,ws.max_row))
  24.     while row < ws.max_row:
  25.         sum_row_start, sum_row_end = row, row
  26.         for working_row in range(row + 1, max_row):
  27.             # 长条件,换行增强可读性
  28.             if (ws['A' + str(working_row)].value != None
  29.                     or (ws['C' + str(working_row)].value == None
  30.                         and ws['C' + str(working_row - 1)].value != None)):
  31.                 sum_row_end = working_row - 1
  32.                 #print(sum_row_end)
  33.                 # 求和
  34.                 ws['D' + str(sum_row_start)] = range_sum(ws, 'C' + str(sum_row_start), 'C' + str(sum_row_end))
  35.                 # 合并
  36.                 ws.merge_cells('D' + str(sum_row_start) + ':D' + str(sum_row_end))
  37.                 ws.merge_cells('A' + str(sum_row_start) + ':A' + str(sum_row_end))
  38.                 # 居中
  39.                 ws['D' + str(sum_row_start)].alignment = Alignment(horizontal="center", vertical="center")
  40.                 ws['A' + str(sum_row_start)].alignment = Alignment(horizontal="center", vertical="center")

  41.                 break;
  42.         row = sum_row_end + 1;


  43. # 根据情况修改代码
  44. os.chdir('D:\\')
  45. wb = openpyxl.load_workbook('in.xlsx')

  46. total_amount(wb['Sheet1'])
  47. wb.save('Out.xlsx')


操作完成之后的样子,



阅读(1402) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~