python openpyxl打开xlsx excel sheet【新建、添加数据、保存、读取】

0. 创建

workbook = openpyxl.Workbook()

参考3.

1. 读取

def read_excel_xlsx(path, sheet_name):workbook = openpyxl.load_workbook(path)# sheet = wb.get_sheet_by_name(sheet_name)这种方式已经弃用，不建议使用sheet = workbook[sheet_name]for row in sheet.rows:for cell in row:print(cell.value, "\t", end="")print()

2. 添加sheet，写入，保存

def read_and_add_xlsx(path, new_sheet_name, new_value):assert os.path.exists(path), 'no such xlsx:%s'%pathworkbook = openpyxl.load_workbook(path)print('Current existed sheet:', workbook.sheetnames)n = len(workbook.sheetnames)if new_sheet_name in workbook.sheetnames:print('Sheet %s exist, not override'% new_sheet_name)return 0index = len(new_value)new_sheet = workbook.create_sheet(title=new_sheet_name,index=n)workbook.active = nnew_sheet.title = new_sheet_namefor i in range(0, index):for j in range(0, len(new_value[i])):# write string for 1st lineif i == 0: new_sheet.cell(row=i+1, column=j+1, value=str(new_value[i][j]))else:new_sheet.cell(row=i+1, column=j+1, value=float(new_value[i][j]))workbook.save(path)print("xlsx格式表格写入数据成功！")

3. 覆盖保存

def write_excel_xlsx(path, sheet_name, value):index = len(value)workbook = openpyxl.Workbook()sheet = workbook.activesheet.title = sheet_namefor i in range(0, index):for j in range(0, len(value[i])):# write string for 1st lineif i == 0: sheet.cell(row=i+1, column=j+1, value=str(value[i][j]))else:sheet.cell(row=i+1, column=j+1, value=float(value[i][j]))workbook.save(path)print("xlsx格式表格写入数据成功！")