主要功能就是把F列三行合并居中
-
#!/usr/bin/python3
-
# -*- coding: utf-8 -*-
-
# Time : 2020/3/5 19:49
-
# Author : xuekai
-
# Email : xuekai20080901@aliyun.com
-
# File : excel.py
-
# Project : python
-
import os
-
import openpyxl
-
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
-
-
-
# 函数:范围单元格求和
-
def range_sum(worksheet, start, end):
-
sum = 0
-
for row in worksheet[start:end]:
-
for cell in row:
-
if cell.value != None:
-
sum += cell.value
-
return sum
-
-
-
# 函数:对某sheet求和并居中
-
def total_amount(worksheet):
-
ws = worksheet
-
row, max_row = 2, ws.max_row
-
print("row:%s max_row:%s ws.max_row:%s" %(row,max_row,ws.max_row))
-
while row < ws.max_row:
-
sum_row_start, sum_row_end = row, row
-
for working_row in range(row + 1, max_row):
-
# 长条件,换行增强可读性
-
if (ws['A' + str(working_row)].value != None
-
or (ws['C' + str(working_row)].value == None
-
and ws['C' + str(working_row - 1)].value != None)):
-
sum_row_end = working_row - 1
-
#print(sum_row_end)
-
# 求和
-
ws['D' + str(sum_row_start)] = range_sum(ws, 'C' + str(sum_row_start), 'C' + str(sum_row_end))
-
# 合并
-
ws.merge_cells('D' + str(sum_row_start) + ':D' + str(sum_row_end))
-
ws.merge_cells('A' + str(sum_row_start) + ':A' + str(sum_row_end))
-
# 居中
-
ws['D' + str(sum_row_start)].alignment = Alignment(horizontal="center", vertical="center")
-
ws['A' + str(sum_row_start)].alignment = Alignment(horizontal="center", vertical="center")
-
-
break;
-
row = sum_row_end + 1;
-
-
-
# 根据情况修改代码
-
os.chdir('D:\\')
-
wb = openpyxl.load_workbook('in.xlsx')
-
-
total_amount(wb['Sheet1'])
-
wb.save('Out.xlsx')
操作完成之后的样子,
阅读(1464) | 评论(0) | 转发(0) |