openpyxl读写Excel

目录

一、根据指定列写入excel

二、逐行写入excel文件，表头只写一次

三、读取指定行、列数据

四、获取所有行数据

五、excel utils封装

六、清空sheet表所有内容

---

一、根据指定列写入excel

import openpyxl as oxl

path = r'./wirte.xlsx'
# 打开已经创建的工作薄
wb = oxl.load_workbook(path)
sheet = wb.worksheets[0]

row_list = ['小张', '100', '20', '30']
# 指定写入第2列数据min_col=2
cols_list = sheet.iter_cols(max_row=len(row_list), min_col=2)
n=0
for i in cols_list:
    for dan in i:
        print(n)
        dan.value=row_list[n]
        n = n + 1
wb.save(path)

path = r'./excel/more.xlsx'
wb = oxl.load_workbook(path)
sheet = wb.worksheets[0]
def write_to_excel(value_list, col_value):
    """
    按指定列写入
    :param value_list:
    :param col_value:
    :return:
    """
    for i in range(1, len(value_list) + 1):
        sheet.cell(i, col_value, value_list[i - 1])
    wb.save(path)

二、逐行写入excel文件，表头只写一次

import openpyxl as oxl

path = r'./wirte.xlsx'
# 打开已经创建的工作薄
wb = oxl.load_workbook(path)
sheet = wb.worksheets[0]
# 添加表头
biaotou = ['姓名', '性别', '岁数']
# 将内容循环写入单元格中
for col in range(len(biaotou)):
    c = col + 1
    sheet.cell(row=1, column=c).value = biaotou[col]


# 写入多行数据
def write_row_excel(row_data):
    sheet.append(row_data)
    wb.save(path)


if __name__ == '__main__':
    row_data = ['weiwei', '男', '28']
    write_row_excel(row_data)

三、读取指定行、列数据

import openpyxl as oxl

path = r'./test.xlsx'
# 打开已经创建的工作薄
wb = oxl.load_workbook(path)
sheet = wb.worksheets[0]

row_list = []
row_value = sheet['B']
print('获取指定一列中的单元格数据')
for i in row_value:
    row_list.append(i.value)

col_list = []
col_value = sheet[2]
print('获取指定一行中的单元格数据')
for i in col_value:
    col_list.append(i.value)

print(row_list)
print(col_list)

四、获取所有行数据

import openpyxl as oxl

import openpyxl as oxl

path = r'./test.xlsx'
# 打开已经创建的工作薄
wb = oxl.load_workbook(path)
sheet = wb.worksheets[0]
#获取1行2列的值
value=sheet.cell(1,2).value
print(value)

#获取最大行数
max_row=sheet.max_row
print(max_row)
#获取所有行数据
all_data=sheet.iter_rows(min_row=1,max_row=max_row,min_col=1, max_col=3, values_only=True)
#注意加上list
print(list(all_data))

五、excel utils封装

def write_to_head(wb, sheet, path, title_list):
    """
    按行表头数据写入
    :param wb: 工作薄
    :param sheet: 工作表对象
    :param path: 需要写入的excel文件路径
    :param title_list: 表头列表值
    :return:
    """
    for col in range(len(title_list)):
        c = col + 1
        sheet.cell(row=1, column=c).value = title_list[col]
        wb.save(path)


def write_row_excel(wb, sheet, path, row_data):
    """
    逐行写入数据
    :param wb: 工作薄
    :param sheet: 工作表对象
    :param path: 需要写入的excel文件路径
    :param row_data:每行需要写入的数据row_data = ['weiwei', '男', '28']
    :return:
    """
    sheet.append(row_data)
    wb.save(path)


def write_col_excel(wb, sheet, path, value_list, col_value):
    """
    按照指定列写入数据
    :param wb: 工作薄
    :param sheet:工作表对象
    :param path:需要写入的excel文件路径
    :param value_list:写入一列的list数据集合
    :param col_value:写入第几列，值为几，入2，为写入第2列
    :return:
    """
    for i in range(1, len(value_list) + 1):
        sheet.cell(i, col_value, value_list[i - 1])
    wb.save(path)


def read_to_excel(sheet, col_index):
    """
    读取指定excel列的数据
    :param sheet: 工作薄
    :param col_index: 列号
    :return:
    """
    col_list = []
    col_value = sheet[col_index]
    for i in col_value:
        col_list.append(i.value)
    return col_list[1:]

六、清空sheet表所有内容

import openpyxl
path = r'./excel/prod.xlsx'
wb = openpyxl.load_workbook(path)

sheet1 = wb.worksheets[0]
#清空所有数据
max_row=sheet1.max_row
sheet1.delete_rows(sheet1.min_row,max_row)
wb.save(path)