python 读写 Excel文件
一、用xlrd和xlwt读写excel
首先下载安装xlrd和xlwt这两个库。
1、打开excel
readbook = xlrd.open_workbook(r'\test\canying.xlsx')2、获取读入的文件的sheet
sheet = readbook.sheet_by_index(1)#索引的方式，从0开始
sheet = readbook.sheet_by_name('sheet2')#名字的方式3、获取sheet的最大行数和列数
nrows = sheet.nrows#行
ncols = sheet.ncols#列4、获取某个单元格的值
lng = table.cell(i,3).value#获取i行3列的表格值
lat = table.cell(i,4).value#获取i行4列的表格值5、打开将写的表并添加sheet
writebook = xlwt.Workbook()#打开一个excel
sheet = writebook.add_sheet('test')#在打开的excel中添加一个sheet6、将数据写入excel
sheet.write(i,0,result[0])#写入excel，i行0列
sheet.write(i,1,result[1])7、保存
writebook.save('answer.xls')#一定要记得保存相关代码
# coding=utf-8 import xlrd import xlwt import datetime import os class excelProcess: def __init__(self,keywordExcelFile,mainExcelFile): self.keywordExcelFile = keywordExcelFile self.mainExcelFile = mainExcelFile def WriteLog(self, message,date): fileName = os.path.join(os.getcwd(), date + '.txt') with open(fileName, 'a') as f: f.write(message) def WriteSheetRow(self,sheet, rowValueList, rowIndex, isBold): i = 0 style = xlwt.easyxf('font: bold 1') # style = xlwt.easyxf('font: bold 0, color red;')#红色字体 # style2 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow; font: bold on;') # 设置Excel单元格的背景色为黄色，字体为粗体 for svalue in rowValueList: if isBold: sheet.write(rowIndex, i, svalue, style) else: sheet.write(rowIndex, i, svalue) i = i + 1 def save_Excel(self): wbk = xlwt.Workbook() sheet = wbk.add_sheet('sheet1', cell_overwrite_ok=True) headList = ['IR_SITENAME', 'IR_AUTHORS', 'SY_INFOTYPE', 'RID', 'IR_URLTITLE','SY_KEYWORDS', 'IR_URLNAME', 'IR_URLTIME', 'IR_GROUPNAME', 'IR_CHANNEL', 'SY_BB_COMMON', 'summary', 'keyword' ] rowIndex = 0 self.WriteSheetRow(sheet, headList, rowIndex, True) for i in range(1, 11): rowIndex = rowIndex + 1 valueList = [] for j in range(1, 14): valueList.append(j * i) self.WriteSheetRow(sheet, valueList, rowIndex, False) fileName = os.path.join(os.getcwd(),'test.xlsx') wbk.save(fileName)
8、
二、使用openpyxl库读写excel
参考：https://www.cnblogs.com/hls-code/p/15674197.html
xlrd和xlwt处理的是xls文件，单个sheet最大行数是65535，如果有更大需要的，建议使用openpyxl函数，最大行数达到1048576。
如果数据量超过65535就会遇到：ValueError: row index was 65536, not allowed by .xls format
首先需要导入 import openpyxl
1、打开excel
2、获取打开的excel的sheet内容
3、获取sheet的最大行数和列数
4、获取某个单元格的值
print(ws.cell(1,1).value)
5、打开将写的表并添加sheet
6、保存
完整代码
def readExel(self): filename = r'D:\work\Excel_txtProcesss\new-微博-合并\58.xlsx' inwb = openpyxl.load_workbook(filename) # 读文件 sheetnames = inwb.get_sheet_names() # 获取读文件中所有的sheet，通过名字的方式 ws = inwb.get_sheet_by_name(sheetnames[0]) # 获取第一个sheet内容 # 获取sheet的最大行数和列数 rows = ws.max_row cols = ws.max_column for r in range(1,rows): for c in range(1,cols): print(ws.cell(r,c).value) if r==10: break def writeExcel(self): outwb = openpyxl.Workbook() # 打开一个将写的文件 outws = outwb.create_sheet(index=0) # 在将写的文件创建sheet for row in range(1,70000): for col in range(1,4): outws.cell(row, col).value = row*2 # 写文件 print(row) saveExcel = "D:\\work\\Excel_txtProcesss\\test.xlsx" outwb.save(saveExcel) # 一定要记得保存
三、用pandas库导出excel文件
import pandas as pd # 单纯导出一个数据集 def exportData_pandas(excelFile='output.xlsx'): try: # data = {'Name': ['Alice', 'Bob', 'Charlie'], # 'Age': [25, 28, 30]} name_list = [] age_list =[] for i in range(10): name_list.append('name'+str(i)) age_list.append('age' + str(i)) data = {'Name':name_list,'Age':age_list} df = pd.DataFrame(data) # 导出 DataFrame 到 Excel 文件 df.to_excel(excelFile, index=False) except Exception as e: print(e) # 以增加方式导出数据集合，在导出之前，output.xlsx已经有数据 def exportData_pandas_append(excelFile='output.xlsx'): try: original_data = pd.read_excel(excelFile) name_list = [] age_list =[] for i in range(10): name_list.append('name'+str(i)) age_list.append('age' + str(i)) data = {'Name':name_list,'Age':age_list} data2 = pd.DataFrame(data) # 将新数据与旧数据合并起来 save_data = original_data.append(data2) save_data.to_excel(excelFile, index=False) except Exception as e: print(e)
font = xlwt.Font() # 字体颜色 font.colour_index = 0x30 # 初始化样式 style2 = xlwt.XFStyle() style2.font = font #超链接相对路径 sheet1.write(0, 0, xlwt.Formula('HYPERLINK("{}", "{}"\r)'.format('./first/test.txt', '点击查看'),style2) #超链接绝对路径 sheet1.write(0, 0, xlwt.Formula('HYPERLINK("{}", "{}"\r)'.format('D:/future/first/test.txt', '点击查看'),style2) #超链接url sheet1.write(0, 0, xlwt.Formula('HYPERLINK("{}", "{}"\r)'.format('https://www.baidu.com/', '点击查看'),style2)
# 1、# 给表格内容设置样式，即设置字体等 import xlwt workbook = xlwt.Workbook(encoding='utf-8') worksheet = workbook.add_sheet('sheet1') # 创建字体样式对象 font = xlwt.Font() # 设置字体（常用字体都支持） font.name = '微软雅黑' # 设置下划线 font.underline = True # 设置加粗 font.bold = True # 设置斜体 font.italic = True # 设置字体为红色,这里不能使用16进制表示颜色的方法去设置 font.colour_index = xlwt.Style.colour_map['red'] # 创建样式对象 font_style = xlwt.XFStyle() # 设置字体样式 font_style.font = font # 往表中写内容,第四个参数：字体样式 worksheet.write(0, 0, '小红', font_style) workbook.save('students.xls') # 2、单元格设置（对齐方式、大小） workbook = xlwt.Workbook(encoding='utf-8') worksheet = workbook.add_sheet('sheet1') # 创建一个单元格设置格式对象 alignment = xlwt.Alignment() # 设置水平居中 alignment.horz = xlwt.Alignment.HORZ_CENTER # 设置垂直居中 alignment.vert = xlwt.Alignment.VERT_CENTER # 创建样式对象 style = xlwt.XFStyle() # 把水平\垂直居中放入样式对象 style.alignment = alignment # 设置单元格宽度 worksheet.col(0).width = 5000 # 设置单元格高度 worksheet.row(0).height_mismatch = True worksheet.row(0).height = 800 worksheet.write(0, 0, 'name', style) workbook.save('stu_info.xls') # 3、设置单元格边框（边框颜色、边框样式） import xlwt workbook = xlwt.Workbook() worksheet = workbook.add_sheet('sheet1') # 创建边框样式对象 borders = xlwt.Borders() # 设置上下左右边框的线条样式：DASHED 表示虚线 borders.top = xlwt.Borders.DASHED borders.bottom = xlwt.Borders.DASHED borders.left = xlwt.Borders.DASHED borders.right = xlwt.Borders.DASHED # 设置上下左右边框的颜色 0x0A 表示红色 borders.top_colour = 0x0A borders.bottom_colour = 0x0A borders.left_colour = 0x0A borders.right_colour = 0x0A # 创建样式对象 style = xlwt.XFStyle() # 把边框样式对象放入样式对象 style.borders = borders worksheet.write(1, 1, '小明', style) workbook.save('stu_info.xls') # 4、设置单元格背景色 import xlwt workbook = xlwt.Workbook() worksheet = workbook.add_sheet('My Sheet') # 创建样式对象 pattern = xlwt.Pattern() # 设置固定样式 pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12 # 设置背景颜色 0 黑色 1 白色 2 红色 3 绿色 4 蓝色 5 黄色 6 紫红色等等 pattern.pattern_fore_colour = 5
字体颜色表
aqua 0x31 black 0x08 blue 0x0C blue_gray 0x36 bright_green 0x0B brown 0x3C coral 0x1D cyan_ega 0x0F dark_blue 0x12 dark_blue_ega 0x12 dark_green 0x3A dark_green_ega 0x11 dark_purple 0x1C dark_red 0x10 dark_red_ega 0x10 dark_teal 0x38 dark_yellow 0x13 gold 0x33 gray_ega 0x17 gray25 0x16 gray40 0x37 gray50 0x17 gray80 0x3F green 0x11 ice_blue 0x1F indigo 0x3E ivory 0x1A lavender 0x2E light_blue 0x30 light_green 0x2A light_orange 0x34 light_turquoise 0x29 light_yellow 0x2B lime 0x32 magenta_ega 0x0E ocean_blue 0x1E olive_ega 0x13 olive_green 0x3B orange 0x35 pale_blue 0x2C periwinkle 0x18 pink 0x0E plum 0x3D purple_ega 0x14 red 0x0A rose 0x2D sea_green 0x39 silver_ega 0x16 sky_blue 0x28 tan 0x2F teal 0x15 teal_ega 0x15 turquoise 0x0F violet 0x14 white 0x09 yellow 0x0D
三、
style2 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow; font: bold on;')
在设置上Excel单元格的背景色时，fore_colour 支持的颜色是有限的，仅支持一下颜色
aqua 0x31
black 0x08
blue 0x0C
blue_gray 0x36
bright_green 0x0B
brown 0x3C
coral 0x1D
cyan_ega 0x0F
dark_blue 0x12
dark_blue_ega 0x12
dark_green 0x3A
dark_green_ega 0x11
dark_purple 0x1C
dark_red 0x10
dark_red_ega 0x10
dark_teal 0x38
dark_yellow 0x13
gold 0x33
gray_ega 0x17
gray25 0x16
gray40 0x37
gray50 0x17
gray80 0x3F
green 0x11
ice_blue 0x1F
indigo 0x3E
ivory 0x1A
lavender 0x2E
light_blue 0x30
light_green 0x2A
light_orange 0x34
light_turquoise 0x29
light_yellow 0x2B
lime 0x32
magenta_ega 0x0E
ocean_blue 0x1E
olive_ega 0x13
olive_green 0x3B
orange 0x35
pale_blue 0x2C
periwinkle 0x18
pink 0x0E
plum 0x3D
purple_ega 0x14
red 0x0A
rose 0x2D
sea_green 0x39
silver_ega 0x16
sky_blue 0x28
tan 0x2F
teal 0x15
teal_ega 0x15
turquoise 0x0F
violet 0x14
white 0x09
yellow 0x0D"""
另外一种方式是 用pyExcelerator
from pyExcelerator import * # excel 第一行数据 excel_headDatas = [u'发布时间', u'文章标题', u'文章链接', u'文章简介'] articles =[ {u'发布时间':u'2017年5月9日', u'文章标题':u'Python项目实战教程：国内就能访问的google搜索引擎', u'文章链接':'http://mp.weixin.qq.com/s?timestamp=1494557315', u'文章简介':u'大家可以留言、想了解python那个方向的知识、不然我也不知道'}, {u'发布时间':u'2017年5月4日', u'文章标题':u'对于学习Django的建议、你知道的有那些', u'文章链接':'http://mp.weixin.qq.com/s?timestamp=1494557323', u'文章简介':u'随着Django1.4第二个候选版的发布，虽然还不支持Python3，但Django团队已经在着手计划中，据官方博客所说，Django1.5将会试验性的支持python3'} ] # 定义excel操作句柄 excle_Workbook = Workbook() excel_sheet_name = time.strftime('%Y-%m-%d') excel_sheet = excle_Workbook.add_sheet(excel_sheet_name) index = 0 #标题 for data in excel_headDatas: excel_sheet.write(0, index, data) index += 1 index = 1 #内容 for article in articles: colIndex = 0 for item in excel_headDatas: excel_sheet.write(index, colIndex, article[item]) colIndex += 1 index += 1 #保存test.xlsx到当前程序目录 excle_Workbook.save('test.xlsx') # db = mongoDB.mongoDbBase() # db.Get_information_stat()
