Python Excel文件操作

问题： python 如何读写Excel文件？

解决： 采用第三方包api读取excel

1、主要的第三方包如下：

2、以下示例采用的（读）xlrd 和（写）xlwt；

     test.xls 

   

 2.1读取Excel文件

from datetime import datetime
import xlrd
from xlrd import xldate_as_tuple


class ReadExcelData(object):
    """读取EXCel表数据"""
    def __init__(self):
        self.sheet = None

    def decode(self, filename, sheetname):
        try:
            filename = filename.decode('utf-8')
            sheetname = sheetname.decode('utf-8')
        except Exception:
            pass
        return filename, sheetname

    def type_change(self, rows, cols, start_col=0):
        all_content = []
        for i in range(rows):
            row_content = []
            for j in range(start_col, cols):                     # 初始列为0
            # for j in range(cols):
                ctype = self.sheet.cell(i, j).ctype        # 表格的数据类型EXCEL_TYPE={0:empty,1:string,2:number,3:date,4:boolean,5:Error}
                cell = self.sheet.cell_value(i, j)
                if ctype == 2 and cell % 1 == 0:           # 如果是整形
                    cell = int(cell)
                elif ctype == 3:
                    # 转成datetime对象
                    date = datetime(*xldate_as_tuple(cell, 0))
                    cell = date.strftime('%Y-%d-%m %H:%M:%S')
                elif ctype == 4:
                    cell = True if cell == 1 else False
                else:
                    pass
                row_content.append(cell)
            if not self.blank_line(row_content):
                continue
            all_content.append(row_content)
        return all_content

    def blank_line(self,row_content):
        return [val for val in row_content if val]

    def read_excel(self, filename, sheetname=None):
        """获取excel数据，默认读取第一张表"""
        filename, sheetname = self.decode(filename, sheetname)
        rbook = xlrd.open_workbook(filename)
        if sheetname:
            self.sheet = rbook.sheet_by_name(sheetname)
        else:
            self.sheet = rbook.sheet_by_index(0)
        rows = self.sheet.nrows
        cols = self.sheet.ncols
        return self.type_change(rows, cols, start_col=1)

    def read_all_excel(self, filename, sheetname=None):
        filename, sheetname = self.decode(filename, sheetname)
        rbook = xlrd.open_workbook(filename)
        if sheetname:
            self.sheet = rbook.sheet_by_name(sheetname)
        else:
            self.sheet = rbook.sheet_by_index(0)
        rows = self.sheet.nrows
        cols = self.sheet.ncols
        return self.type_change(rows, cols)



if __name__ == '__main__':
    eh = ReadExcelData()
    filename = r'./test.xls'
    sheetname = 'Sheet1'
    ret1 = eh.read_excel(filename)
    ret2 = eh.read_all_excel(filename)
    print('ret1:',ret1)
    print('ret2:',ret2)
    >>ret1: [[12,13],[22,23]]
    >>ret2: [[11,12,13],[21,22,23]]

    

 2.2 写入数据Excel

import xlwt

# Excel 单元格样式设置
VERT_TOP = 0x00         # 上端对齐
VERT_CENTER = 0x01      # 居中对齐（垂直方向上）
VERT_BOTTOM = 0x02      # 低端对齐
HORZ_LEFT = 0x01        # 左端对齐
HORZ_CENTER = 0x02      # 居中对齐（水平方向上）
HORZ_RIGHT = 0x03       # 右端对齐


class WriteExcel(object):
    """写入Excel数据"""
    def write_excel(self, data_list, excel_file, sheet_name='Sheet1'):
        """将数据写入Excel
        :param data_list 格式[[11,12,13],[21,22,23]]
        """
        # new一个文件
        workbook = xlwt.Workbook(encoding='utf-8')
        # new一个sheet
        worksheet = workbook.add_sheet(u'{}'.format(sheet_name))
        # 创建一个样式对象，初始化样式
        style = xlwt.XFStyle()  
        al = xlwt.Alignment()
        al.horz = HORZ_LEFT
        # al.vert = VERT_CENTER
        style.alignment = al
        for row, row_data in enumerate(data_list):
            for col, cell_data in enumerate(row_data):
                worksheet.write(row, col, cell_data, style)
                worksheet.col(col).width = 256 * 20    # 设置列宽20
        # worksheet.write_merge(0, 0, 0, 3, 'First Merge')  # 合并单元格Merges row 0's columns 0 through 3.
        workbook.save(excel_file)
if __name__ == '__main__':
    obj = WriteExcel()
    obj.write_excel(data_list=[[0-0,0-1,0-2],[1-0,1-1,1-2]], excel_file='./test2.xls')

写入结果截图如下：

django 无法导出Excel文件至前端？

    https://blog.youkuaiyun.com/LeonTom/article/details/102745665