python操作读写excel

一、读取Excel数据

1、没有合并单元格的情况

通过xlrd打开excel，并读取相应sheet的数据，存为二元数组中，具体代码如下：

def read(file, sheet_name):
	"""
	按行列读取所有数据，ctype判断类型，可进一步处理
	ctype类型: 0 empty 1 string 2 number 3 date 4 boolean 5
	"""
    workbook = xlrd.open_workbook(file)
    sheet_data = workbook.sheet_by_name(sheet_name)
    data = []
    ncols = sheet_data.ncols
    nrows = sheet_data.nrows

    for i in range(nrows):
        data_rows = []
        for j in range(ncols):
            cell_value = sheet_data.cell_value(i, j)
            # data_rows.append("%d" %cell_value)
            # ctype: 0 empty 1 string 2 number 3 date 4 boolean 5
            ctype = sheet_data.cell(i, j).ctype
            if ctype == 0:
                data_rows.append(0)
            else:
                data_rows.append(cell_value)
        data.append(data_rows)
    return data

2、有合并单元格的情况

通过xlrd打开excel，将所有sheet中所有合并单元格取出存在字典中，字典的key是sheet名字，值为对应sheet名字内所有的合并单元格信息；
merged_cells元组对应的含义为：（单元格的起始行，单元格的结束行，从单元格的起始列，单元格的结束列）

def read(file):
	"""
	将sheet中所有合并单元格取出存在字典中，字典的key是sheet名字，值为对应sheet名字内所有的合并单元格信息
	"""
    workbook = xlrd.open_workbook(file)
    sheet_names = workbook.sheet_names()
    table_data = {}
    for sheet in sheet_names:
        table = workbook.sheet_by_name(sheet)
        data = []
        ncols = table.ncols
        nrows = table.nrows
        colspan = []
        if table.merged_cells:
            for item in table.merged_cells:
                merge_value = table.cell_value(item[0], item[2])
                merge_list = list(item)
                # 注意：读取的单元格如果有写入操作，需要把结束行和结束列减1再写入
                merge_list[1] = merge_list[1] - 1
                merge_list[3] = merge_list[3] - 1
                merge_list.append(merge_value)
                colspan.append(tuple(merge_list))
        table_data[sheet] = colspan
    return table_data

二、写入Excel数据

1、生成新表写入(不包含合并单元格的写入)

使用xlwt生成新表，将上面读取的二维数组写入

def save_excel(data, file):
	"""
	将二维数组写入excel
	"""
    file_name = os.path.join(os.getcwd(), file)
    if os.path.exists(file_name):
        os.remove(file_name)
    book = xlwt.Workbook(encoding='utf-8')
    # cell_overwrite_ok设置是否覆盖写入
    sheet2 = book.add_sheet(str(sheet_name), cell_overwrite_ok=True)
    for i in range(len(data)):
        for j in range(len(data[0])):
            try:
                sheet2.write(i, j, str(data[i][j]))
            except IndexError:
                # print('')
                sheet2.write(i, j, '')
    book.save(file_name)

2、生成新表写入(只写合并单元格的数据)

def save_excel(data, file):
	"""
	将合并单元格写入excel
	"""
    file_name = os.path.join(os.getcwd(), file)
    if os.path.exists(file_name):
        os.remove(file_name)
    book = xlwt.Workbook(encoding='utf-8')
    if isinstance(data, dict):
        for sheetName, value_list in data.items():
            # write_sheet = book.add_sheet(sheetName, cell_overwrite_ok=True)
            write_sheet = book.add_sheet(sheetName)
            for item in value_list:
                try:
                    write_sheet.write_merge(*item)
                except Exception:
                    print(item)
                    continue
    book.save(file_name)

3、复制表格，追加写入

使用xlutils.copy复制表格再追加写入

def save_excel(data, file):
    """
    追加写入
    :param data:
    :param file:
    :return:
    """
    workbook = xlrd.open_workbook(file)
    all_sheet = workbook.sheet_names()
    first_sheet = workbook.sheet_by_name(all_sheet[0])
    new_workbook = copy(workbook)
    new_sheet = new_workbook.get_sheet(0)
    new_sheet.write(4, 1, "啊哈哈")
    new_workbook.save("record.xls")

4、判断是否是合并单元格

def is_merged(row, col, merged_list): 
	"""
	row,col为想要查询的行和列， merged_list需要提前取出表格的所有合并单元格的数据
	"""
    for (rlow, rhigh, clow, chigh) in merged_list:
        if row < rhigh and row >= rlow:
            if col < chigh and col >= clow:
                return True
    return False