读写Excel使用openpyxl库
wb = openpyxl.load_workbook(path) 打开一个excel
wn = wb.get_sheet_names() 得到该excel的所有表名
ws=wb.get_sheet_by_name(wn[0]) 得到第一个sheet的数据
r=ws.get_highest_row() 得到行总数
c=ws.get_highest_column() 得到列总数
get_column_letter(i+1) 得到列i+1对应的字母
wb=Workbook() 新建一个excel文件
ws = wb.create_sheet(0) 创建一个新sheet
wb.save(path) 将excel存为文件
ws.cell(row=i+1,column=j+1).value 行i,列j的值
ws.title='wohao' 设置sheet名
例子:
#coding=utf-8
import wx
from openpyxl.workbook import Workbook
from openpyxl.writer.excel import ExcelWriter
from openpyxl.cell import get_column_letter
import openpyxl
import os
import chardet
def readExcel(path):
wb = openpyxl.load_workbook(path)
# ws = wb.get_active_sheet()
wn = wb.get_sheet_names()
ws=wb.get_sheet_by_name(wn[0])
print toUtf8(ws.title)
r=ws.get_highest_row()
c=ws.get_highest_column()
for i in range(r):
print "---------------",i
for j in range(c):
# v = ws.cell(row=i+1,column=j+1).value
v = ws.cell('%s%s'%(get_column_letter(i+1),j+1)).value
print toUtf8(v)
def toUtf8(v):
isstr=isinstance(v, str)
isuni=isinstance(v, unicode)
if isuni:
print "unicode"
return v.encode('utf8')
elif isstr and v:
print "str"
return v.decode(chardet.detect(v)['encoding']).encode('utf8')
# print chardet.detect(v)['encoding']
else:
print "none"
return v
def save2Excel(path):
wb = Workbook()
ws = wb.create_sheet(0)
ws.cell('A2').value=5
wb.save(path)
def modifyExcel(path):
wb = openpyxl.load_workbook(path)
# ws1 = wb.create_sheet() #last sheet
ws = wb.create_sheet(1)
ws.cell('A2').value=4
ws.title='wohao'
# ws = wb.get_active_sheet()
#ws.append({4:'new3'})
# ws.append({1:'new2'})
wb.save(path)
readExcel("F:"+os.sep+"aa"+os.sep+"aaaa.xlsx")
save2Excel("F:"+os.sep+"aa"+os.sep+"cccc.xlsx")
modifyExcel("F:"+os.sep+"aa"+os.sep+"aaaa.xlsx")