python学习记录之---------Excel 操作大全

工具/版本

（1）安装环境：Windows7 64bit

（2）使用版本Python3.6

 

注释很详细，直接上代码

#!/usr/bin/env python
# encoding: utf-8
"""
+----------------------------------------------------------------------------+
 ╱◥██◣         ∧_∧　　　 ∧_∧　　    ∧_∧　　   ∧_∧     ╱◥██◣
|田︱田田|      （^ .^）　 （^ 、^）　 （^ 0^）　 （^ Д^）  |田︱田田|
╬╬╬╬╬╬╬╬╬╬-------∪-∪-------∪-∪--------∪-∪-------∪-∪---╬╬╬╬╬╬╬╬╬╬╬
+----------------------------------------------------------------------------+
License (C) Copyright 2017-2017,  Corporation Limited.
File Name         :    Excel_wr.py
Auther            :    samenmoer
Software Version  :    Python3.6
Email Address     :    gpf192315@163.com
Creat Time        :    2018-09-08   15:11:24
优快云 blog         :    https://blog.youkuaiyun.com/samenmoer
Description       :
------------------------------------------------------------------------------
Modification History
Data          By           Version       Change Description
==============================================================================
${time}            |                |                 |
==============================================================================
¤╭⌒╮ ╭⌒╮¤╭⌒╮ ╭⌒╮¤╭⌒╮ ╭⌒╮¤╭⌒╮  ╭⌒╮¤╭⌒╮╭⌒╮¤╭⌒╮╭⌒╮¤╭⌒╮╭⌒╮¤╭⌒╮╭⌒╮¤╭⌒╮╭⌒╮¤╭⌒╮
------------------------------------------------------------------------------
"""
import datetime
import os, sys
import xlrd
import xlwt

Print_En = True  # 开启打印
PyPath = os.path.basename(sys.argv[0]).split(".")[0] + ".py"


class excel_ro():
    # excel 只读相关操作
    def __init__(self, ExcelName):
        # 初始化，给出要打开的文件（包含路径）
        self.ExcelName = ExcelName;
        self.WorkBook = xlrd.open_workbook(ExcelName)
        self.SheetNames = self.get_all_sheets()

    def get_all_sheets(self):
        # 获取全部sheet名
        self.SheetNames = self.WorkBook.sheet_names()
        return self.SheetNames

    def get_sheet_name(self, SheetNum):
        # 给出第几个sheet，返回sheet名称
        self.SheetNum = SheetNum
        if self.SheetNum > len(self.SheetNames) - 1:
            print_err("Input Sheet Num Error, This excel have %r sheet" % len(self.SheetNames))
            return False
        elif type(self.SheetNum) is not int:
            print_err("Please input int type")
            return False
        else:
            return self.SheetNames(self.SheetNum)

    def get_sheet(self, SheetName):
        # 给出sheet名，返回一个操作对象
        self.SheetName = SheetName
        self.SheetCur = self.WorkBook.sheet_by_name(self.SheetName)
        return self.SheetCur

    def get_col(self, Sheet):
        # 获取sheet的列数
        return Sheet.ncols

    def get_row(self, Sheet):
        # 获取sheet的行数
        return Sheet.nrows

    def get_col_value(self, Sheet, Col):
        # 获取某一列的值
        return Sheet.col_values(Col)

    def get_row_value(self, Sheet, Row):
        # 获取某一行的值
        return Sheet.row_values(Row)

    def get_cell(self, Sheet, Row, Col):
        # 获取某一个单元格的值,共如下三种方法
        # self.Cell = Sheet.cell(Row, Col).value.encode('utf-8')
        self.Cell = Sheet.cell_value(Row, Col).encode('utf-8')
        # self.Cell = Sheet.row(Row)[Col].value.encode('utf-8')
        return self.Cell


class excel_wo:
    # excel 只写相关操作
    def __init__(self):
        self.wbk, self.sheet = self.creat_excel()

    def creat_excel(self, SheetName=None):
        # 创建excel，没有输入SheetName，默认sheet 1
        self.wbk = xlwt.Workbook(encoding='utf-8')
        if SheetName is None:
            SheetName = u"sheet 1"

        self.sheet = self.creat_sheet(SheetName)
        return self.wbk, self.sheet

    def creat_sheet(self, SheetName, OverWrite=True):
        # 新建sheet 表，cell_overwrite_ok用于确认同一个cell单元是否可以重设值
        self.sheet = self.wbk.add_sheet(SheetName, cell_overwrite_ok=OverWrite)
        return self.sheet

    def set_col_width(self, Sheet, Col, ColWidth):
        # 设置单元格列宽，行的宽度会根据字体大小自动设定
        self.Col = Sheet.col(Col)
        # 默认每个字节长度256，需输入需要几个字节的宽度
        self.Col.width = 256*ColWidth

    def write_cell(self, Sheet, Row, Col, Value, Style=None):
        if Style is None:
            Style = self.set_cell_style()

        Sheet.write(Row, Col, Value, Style)

    def write_cells(self, Sheet, Row1, Row2, Col1, Col2, Value, Style=None):
        # 以合并单元格的形式写入
        if Style is None:
            Style = self.set_cell_style()

        Sheet.write_merge(Row1, Row2, Col1, Col2, Value, Style)
    def insert_image(self, Sheet, Cell, ImageName):
        # 这里cell需要给出单元格所在的名称例如D4,A1
        if os.path.exists(ImageName):
            print(u'python.bmp图片存在')
        else:
            print(u'python.bmp图片不存在')
        # 需要插入bup格式图片
        Sheet.insert_bitmap(ImageName, 2, 9) 
    def set_cell_style(self, BackColor=1, FntName=u'宋体', FntColor=0, FntSize=10, FntBold=False, Border=2, BorderColor=0x40):
        self.style = xlwt.XFStyle()
        """*******************************
                定义背景颜色
        *******************************"""
        # 创建一个模式
        self.pattern = xlwt.Pattern()
        # 设置其模式为实型
        self.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        # 设置单元格背景颜色，范围：0~63
        # 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta,
        self.pattern.pattern_fore_colour = BackColor

        self.style.pattern = self.pattern
        """*******************************
                定义文本格式
        *******************************"""
        # 创建一个文本格式，包括字体、字号和颜色样式特性
        self.fnt = xlwt.Font()
        # 设置其字体
        self.fnt.name = FntName
        # 设置其字体颜色，范围：0~63
        self.fnt.colour_index = FntColor
        # 设置是否为粗体
        self.fnt.bold = FntBold
        # 设置字体大小,excel显示的数字字号乘20即为设置字号大小
        self.fnt.height = FntSize*20
        self.style.font = self.fnt
        """*******************************
                定义单元格下框线格式
        *******************************"""
        # 边框选择线条，范围0~13
        self.borders = xlwt.Borders()
        self.borders.left = Border
        self.borders.right = Border
        self.borders.top = Border
        self.borders.bottom = Border
        # 边框颜色选择 ,使用该选项必须保证有边框线条
        self.borders.left_colour = BorderColor
        self.borders.right_colour = BorderColor
        self.borders.top_colour = BorderColor
        self.borders.bottom_colour = BorderColor

        self.style.borders = self.borders
        """*******************************
                定义写入格式居中
        *******************************"""
        self.alignment = xlwt.Alignment()
        # 水平方向
        self.alignment.horz = xlwt.Alignment.HORZ_CENTER    # 居中
        # self.alignment.horz = xlwt.Alignment.HORZ_LEFT    # 居左
        # self.alignment.horz = xlwt.Alignment.HORZ_RIGHT   # 居右

        # 垂直方向
        # self.alignment.vert = xlwt.Alignment.VERT_TOP       # 居顶
        # self.alignment.vert = xlwt.Alignment.VERT_BOTTOM    # 居底
        self.alignment.vert = xlwt.Alignment.VERT_CENTER      # 居底
        self.style.alignment = self.alignment

        return self.style

    def excel_save(self, ExcelName):
        self.wbk.save(ExcelName)


def test_xlwt():
    ex = excel_wo()
    wbk, sheet = ex.creat_excel(SheetName=u"test")

    ex.set_col_width(sheet,0,20)
    ex.set_col_width(sheet,1,1)
    for i in range(14):
        style = ex.set_cell_style(Border=i)
        ex.write_cell(sheet,2*i,0,u"边框值=%s"%i,Style=style)

    ex.set_col_width(sheet,2,10)
    ex.write_cells(sheet,0,2,2,3,u"合并单元格测试")

    ex.set_col_width(sheet,4,10)
    for i in range(64):
        style = ex.set_cell_style(FntColor=i)
        ex.write_cell(sheet,i,4,u"颜色=%s"%i,Style=style)
    ex.set_col_width(sheet,5,10)
    for i in range(64):
        style = ex.set_cell_style(BackColor=i,FntColor=i+5)
        ex.write_cell(sheet,i,6,u"颜色=%s"%i,Style=style)

    ex.set_col_width(sheet,8,30)
    for i in range(64):
        style = ex.set_cell_style(Border=2, BorderColor=i,FntSize=30)
        ex.write_cell(sheet,i,8,u"颜色=%s"%i,Style=style)
    

    ex.excel_save("test.xls")
    return
def print_err(message):
    """输入'head + TAB'输出注释
    ===========================================================================
    * Founction Name    :   print_err
    * Create Author     :   samenmoer
    * Create Time       :   2018/7/15 12:08
    * Description       :   1、通过开关控制是否打印print信息
                            2、记录log信息方便出错查看
    * Parameter         :   message ：输入的打印信息
    * Return            :   None
    ===========================================================================
    """
    FileName = "../BlackBox/Running.log"
    nowTime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')#开始运行时间
    message = "[" + nowTime + "]: " + str(message)
    if Print_En is True:
        print(message)
    f = open(FileName, "a")
    f.write(message + "\n")
    return


def GetRunTime(start_time):
    # begin = datetime.datetime.now()
    end = datetime.datetime.now()
    elapsed = end - start_time
    elapsed_time = "running %r take time is " % PyPath + \
        str(elapsed.total_seconds() * 1000) + "ms"
    elapsed_int = elapsed.total_seconds() * 1000
    print_err(elapsed_time)
    return elapsed_int


if __name__ == '__main__':
    start = datetime.datetime.now()
    # ---------------------------------------------
    test_xlwt()

    # ---------------------------------------------
    GetRunTime(start)

 

附测试结果图