Python 读写excel表格

本篇复制了以下两篇博客并做了一点整理，厚着脸皮标了个原创

1. https://blog.csdn.net/asialee_bird/article/details/105497121
2. https://www.cnblogs.com/ilovepython/p/11068841.html

本实例从一份excel文件中读取并写入另一个文件中，话不多说，直接上代码：

import xlrd
import xlwt
import datetime


from xlrd import xldate_as_tuple


'''
xlrd中单元格的数据类型
数字一律按浮点型输出，日期输出成一串小数，布尔型输出0或1，所以我们必须在程序中做判断处理转换
成我们想要的数据类型
0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
'''


class ExcelUtils():

    def __init__(self, file_path, sheet_name):
        #定义一个属性接收文件路径
        self.data_path = data_path
        self.sheet_name = sheet_name
        self.data = xlrd.open_workbook(self.data_path)
        self.table = self.data.sheet_by_name(self.sheet_name)
        self.keys = self.table.row_values(0)
        self.row_num = self.table.nrows
        self.col_num = self.table.ncols


    def read_excel_data(self):

        datas = []
        for i in range(1, self.row_num):
            for j in range(self.col_num):
                # 获取单元格数据类型
                c_type = self.table.cell(i,j).ctype
                # 获取单元格数据
                c_cell = self.table.cell_value(i, j)

                if c_type == 2 and c_cell % 1 == 0:  # 如果是整形
                    c_cell = int(c_cell)

                elif c_type == 3:
                    # 转成datetime对象
                    date = datetime.datetime(*xldate_as_tuple(c_cell,0))
                    c_cell = date.strftime('%Y/%d/%m %H:%M:%S')

                elif c_type == 4:
                    c_cell = True if c_cell == 1 else False
                
                d = {
                    'i':i,
                    'j':j,
                    'value':c_cell
                }
                datas.append(d)

        return datas



def save_data(datas):

    # 创建一个workbook 设置编码
    workbook = xlwt.Workbook(encoding='utf-8')
    worksheet = workbook.add_sheet('Sheet1')
 
    #字体样式设置
    style = xlwt.XFStyle()  # 初始化样式
    font = xlwt.Font()  # 为样式创建字体
    font.name = 'Times New Roman'
    font.height = 20 * 11  # 字体大小，11为字号，20为衡量单位
    font.bold = True  # 黑体
    font.underline = True  # 下划线
    font.italic = True  # 斜体字
    style.font = font  # 设定样式

    # 数据写入excel,参数对应 行, 列, 值
    worksheet.write(20, 0, 'test')
    worksheet.write(20, 1, 2)

    #设置单元格背景颜色
    pattern = xlwt.Pattern()
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = 13
    style = xlwt.XFStyle()  # Create the Pattern
    style.pattern = pattern  # Add Pattern to Style
    worksheet.write(20, 2, 'color', style)

    # 边框
    borders = xlwt.Borders()
    borders.left = xlwt.Borders.DASHED   #DASHED虚线，NO_LINE没有，THIN实线
    borders.right = xlwt.Borders.DASHED  #borders.right=1 表示实线
    borders.top = xlwt.Borders.DASHED
    borders.bottom = xlwt.Borders.DASHED
    borders.left_colour=0x40
    borders.right_colour = 0x40
    borders.top_colour = 0x40
    borders.bottom_colour = 0x40
    style = xlwt.XFStyle()  # Create Style
    style.borders = borders  # Add Borders to Style
    worksheet.write(20, 3 , 'border1', style)

    
    #给单元格添加边框方法二
    # 细实线:1，小粗实线:2，细虚线:3，中细虚线:4，大粗实线:5，双线:6，细点虚线:7，大粗虚线:8，细点划线:9，粗点划线:10，细双点划线:11，粗双点划线:12，斜点划线:13
    borders = xlwt.Borders()
    borders.left = 1    #设置为细实线
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    borders.left_colour = 2  #颜色设置为红色
    borders.right_colour = 2
    borders.top_colour = 2
    borders.bottom_colour = 2
    style = xlwt.XFStyle()  # Create Style
    style.borders = borders  # Add Borders to Style
    worksheet.write(20, 4, 'border2', style)

    # 写入时间
    style = xlwt.XFStyle()
    style.num_format_str = 'M/D/YY'  # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
    worksheet.write(20, 5, datetime.datetime.now(), style)

    # 添加公式
    worksheet.write(21, 1, xlwt.Formula('B1*C1')) 
    worksheet.write(21, 2, xlwt.Formula('SUM(B1,C1)'))
    
    # 合并
    worksheet.write_merge(20, 21, 10, 15, 'First Merge')   #合并20到21行的10到15列

    # 设置对齐方式
    alignment=xlwt.Alignment()
    alignment.horz=xlwt.Alignment.HORZ_CENTER
    alignment.vert=xlwt.Alignment.VERT_CENTER
    style=xlwt.XFStyle()
    style.alignment=alignment
    worksheet.write(20, 6, 'alignment', style)

    for data in datas:
        # print('i is {},j is {},value is {}'.format(data.get('i'),data.get('j'),data.get('value')))
        worksheet.write(data.get('i'),data.get('j'),data.get('value'))
    

    workbook.save('F:\\test\\data_test11.xls')



if __name__ == "__main__":
    data_path = "F:\\test\\data_test.xls"
    sheetname = "Sheet1"
    get_data = ExcelUtils(data_path, sheetname)
    datas = get_data.read_excel_data()
    save_data(datas)