python读写excel



读写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")