Excel的读写

最新推荐文章于 2022-07-04 00:22:09 发布

铽可喏

最新推荐文章于 2022-07-04 00:22:09 发布

阅读量165

点赞数

分类专栏： Python 文章标签： Excel

本文链接：https://blog.csdn.net/tekenuo/article/details/84563391

版权

Python 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

openpyxl模块是一个读写Excel 2010文档的Python库，如果要处理更早格式的Excel文档，需要用到额外的库，openpyxl是一个比较综合的工具，能够同时读取和修改Excel文档。其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能。

安装openpyxl模块

pip install openpyxl

2. 源码开发

# -*- coding:UTF-8 -*-

import sys, os
import openpyxl
import openpyxl.styles as sty
from openpyxl import Workbook
from openpyxl.reader.excel import load_workbook
from openpyxl.styles import Color, Font, Alignment, Fill, Border, Side
from openpyxl.styles.colors import BLUE, RED, GREEN, YELLOW, BLACK, WHITE

reload(sys)
sys.setdefaultencoding('utf-8')

def cellstyle(cell, font, align, border, bgcolor):
    cell.font = font
    cell.alignment = align
    cell.border = border
    cell.fill = sty.PatternFill(fill_type='solid', fgColor=bgcolor)


def makecell(ws, row, col, content, bgcolor): # worksheet , 行, 列, 内容，背景色
	cell = ws.cell(row=row, column=col)
	cell.value = content

	font = Font(name=u'宋体', size=10, color=BLUE, bold=False)
	align = Alignment(horizontal='left', vertical='center', wrap_text=True)
	border = Border(left=Side(style='medium', color='FF000000'), right=Side(style='medium', color='FF000000'),
                    top=Side(style='medium', color='FF000000'), bottom=Side(style='medium', color='FF000000'),
                    diagonal=Side(style='medium', color='FF000000'), diagonal_direction=0,
                    outline=Side(style='medium', color='FF000000'), vertical=Side(style='medium', color='FF000000'),
                    horizontal=Side(style='medium', color='FF000000'))

	cellstyle(cell, font, align, border, bgcolor)

	ws.row_dimensions[row].height = 100
	a = openpyxl.utils.get_column_letter(col)
	ws.column_dimensions[a].width = 50


dir = "./"
fname = "target.xlsx"

if not os.path.isfile(dir + fname):
	print("{} No file existed".format(fname))
	sys.exit()

wb = openpyxl.load_workbook(dir + fname)#载入excel表格
sheet_names = wb.sheetnames #excel表格的sheet名，可用于遍历各个sheet


def excute(start, end, read_col, write_col):  # [start,end)sheet表起止,读read_col列，写入write_col列
    # 打印所需sheet name 2～8
    for num in range(start, end):
        sheet_name = sheet_names[num]
        ws = wb[sheet_name]

        nrows = ws.max_row  # 行数
        ncols = ws.max_column  # 列数
        print("max_row = %d ,cols = %d" % (nrows, ncols))

        for num2 in range(2, nrows + 1):  ##从第三行开始所有列
            print('*' * 30 + "START" + '*' * 30)
            colsName = ws.cell(row=num2, column=read_col).value  ##说法第几列
            colsNameUTF8 = colsName.encode('utf8')
            print("{}: {}".format(num2,colsNameUTF8))
            after_content = colsNameUTF8+" **********"
            bgcolor = YELLOW
            makecell(ws, num2, write_col, after_content, bgcolor)
            wb.save(dir + fname) #保存excel文件

excute(0,1,2,3) #第1个表格，读入第2列，处理的结果写到第3列