python导入excel数据-python读取数据写入excel的四种操作

Python对Excel的读写主要有:xlrd、xlwt、xlutils、openpyxl、xlsxwriter几种

xlutils结合xlrd: 操作的是以xls后缀的excel，读取文件保留原格式：加：formatting_info=True

常用方法：1、打开xls：open_workbook(filePath)

2、 x1.sheet_names() # 获取所有sheet名字

3、 x1.nsheets # 获取sheet数量

4、 x1.sheets() # 获取所有sheet对象

5、 x1.sheet_by_name("test") # 通过sheet名查找

6、 x1.sheet_by_index(3) # 通过索引查找

一、xlutils结合xlrd可以达到修改excel文件目的

import xlrd

from xlutils.copy import copy

workbook = xlrd.open_workbook(u"有趣装逼每日数据及趋势.xls",formatting_info=True)

workbooknew = copy(workbook)

ws = workbooknew.get_sheet(0)

ws.write_merge(1,1,2,2,"测试测试"，style)

ws.write(3, 0, "changed!")

workbooknew.save(u"有趣装逼每日数据及趋势copy.xls")

二、xlwt操作的是以xls后缀的excel

import xlwt

wk = xlwt.Workbook()

sheet = wk.add_sheet("sheet 1")#创建一个sheet1

sheet.write(0,1,"test text")#第0行第一列写入内容

wk.save("test1.xls")

三、xlsxwriter 操作的是xlsx

import xlsxwriter

workbook = xlsxwriter.Workbook("demo1.xlsx") # 创建工作簿并添加工作表

worksheet = workbook.add_worksheet()

worksheet.write("A1", "Hello world")# 写入数据到A1

worksheet.merge_range("B4:D4", "Merged text", merge_format) #合并单元格，方法2

worksheet.merge_range(3, 1, 3, 3, "Merged Range", merge_format)

format1 = worksheet.add_format({"bold": True}) #写入样式

worksheet.write(row,1,"=SUM(B1:B4)") #写入公式

workbook.close() # 关闭excel文件

四、openpyxl：openpyxl可以对excel文件进行读写操作，xlsx

from openpyxl import Workbook

from openpyxl import load_workbook

from openpyxl.writer.excel import ExcelWriter

workbook = load_workbook(u"新歌检索失败1477881109469.xlsx")

sheetnames =workbook.get_sheet_names() #获得表单名字

sheet = workbook.get_sheet_by_name(sheetnames[0])

sheet["A1"] = "47"

workbook.save(u"新歌检索失败1477881109469_new.xlsx")

wb = Workbook()

ws = wb.active

ws["A1"] = 4

wb.save("新歌检索失败.xlsx")

原文地址：https://www.cnblogs.com/1314520xh/p/13216823.html