Ç°ÑÔ

ÔÚ´¦ÀíexcelÊý¾Ýʱ·¢ÏÖÁËxlwtµÄ¾ÖÏÞÐÔ¨C²»ÄÜдÈ볬¹ý65535ÐС¢256ÁеÄÊý¾Ý£¨ÒòΪËüÖ»Ö§³ÖExcel 2003¼°Ö®Ç°µÄ°æ±¾£¬ÔÚÕâЩ°æ±¾µÄExcelÖÐÐÐÊýºÍÁÐÊýÓдËÏÞÖÆ£©£¬Õâ¶ÔÓÚʵ¼ÊÓ¦Óû¹ÊDz»¹»µÄ¡£Îª´Ë¾­¹ýÒ»·¬Ñ°ÕÒ·¢ÏÖÁËÒ»¸öÖ§³Ö07/10/13°æ±¾ExcelµÄopenpyxl£¬ËäÈ»¹¦ÄܺÜÇ¿´ó£¬µ«ÊDzÙ×÷ÆðÀ´¸Ð¾õûÓÐxlwt·½±ã¡£ÏÂÃæ·Ö±ð˵ϼ¸¸öÄ£¿éµÄ³£ÓòÙ×÷¡£

xlrd

xlrdÊÇÓÃÀ´´ÓExcelÖжÁдÊý¾ÝµÄ£¬µ«ÎÒƽ³£Ö»ÓÃËü½øÐжÁ²Ù×÷£¬Ð´²Ù×÷»áÓöµ½Ð©ÎÊÌâ¡£ÓÃxlrd½øÐжÁÈ¡±È½Ï·½±ã£¬Á÷³ÌºÍƽ³£ÊÖ¶¯²Ù×÷ExcelÒ»Ñù£¬´ò¿ª¹¤×÷²¾(Workbook)£¬Ñ¡Ôñ¹¤×÷±í(sheets)£¬È»ºó²Ù×÷µ¥Ôª¸ñ(cell)¡£ÏÂÃæ¾Ù¸öÀý×Ó£¬ÀýÈçÒª´ò¿ªµ±Ç°Ä¿Â¼ÏÂÃûΪ¡±data.xlsx¡±µÄExcelÎļþ£¬Ñ¡ÔñµÚÒ»ÕŹ¤×÷±í£¬È»ºó¶ÁÈ¡µÚÒ»ÐеÄÈ«²¿ÄÚÈݲ¢´òÓ¡³öÀ´¡£Python´úÂëÈçÏ£º

#´ò¿ªexcelÎļþ
data=xlrd.open_workbook('data.xlsx')  
#»ñÈ¡µÚÒ»ÕŹ¤×÷±í£¨Í¨¹ýË÷ÒýµÄ·½Ê½£©
table=data.sheets()[0] 
#data_listÓÃÀ´´æ·ÅÊý¾Ý
data_list=[] 
#½«tableÖеÚÒ»ÐеÄÊý¾Ý¶ÁÈ¡²¢Ìí¼Óµ½data_listÖÐ
data_list.extend(table.row_values(0))
#´òÓ¡³öµÚÒ»ÐеÄÈ«²¿Êý¾Ý
for item in data_list:
 print item

ÉÏÃæµÄ´úÂëÖжÁÈ¡Ò»ÐÐÓÃtable.row_values(number)£¬ÀàËƵĶÁÈ¡Ò»ÁÐÓÃtable.column_values(number)£¬ÆäÖÐnumberΪÐÐË÷Òý£¬ÔÚxlrdÖÐÐкÍÁж¼ÊÇ´Ó0¿ªÊ¼Ë÷ÒýµÄ£¬Òò´ËExcelÖÐ×î×óÉϽǵĵ¥Ôª¸ñA1ÊǵÚ0ÐеÚ0ÁС£

xlrdÖжÁȡij¸öµ¥Ôª¸ñÓÃtable.cell(row,col)¼´¿É£¬ÆäÖÐrowºÍcol·Ö±ðÊǵ¥Ôª¸ñ¶ÔÓ¦µÄÐкÍÁС£

ÏÂÃæ¼òµ¥¹éÄÉÒ»ÏÂxlrdµÄÓ÷¨

xlrdÓ÷¨×ܽá

´ò¿ªExcel¹¤×÷²¾

data=xlrd.open_workbook(filename)

²é¿´¹¤×÷²¾ÖÐËùÓÐsheetµÄÃû³Æ

data.sheet_names()

Ñ¡Ôñijһ¸ö¹¤×÷±í£¨Í¨¹ýË÷Òý»ò±íÃû³Æ£©

#»ñÈ¡µÚÒ»¸ö¹¤×÷±í
table=data.sheets()[0]

#ͨ¹ýË÷Òý»ñÈ¡µÚÒ»¸ö¹¤×÷±í
table=data.sheet_by_index(0)

#ͨ¹ý±íÃû³ÆÑ¡Ôñ¹¤×÷±í
table=data.sheet_by_name(u'¹þ¹þ')

»ñÈ¡±í¸ñµÄÐÐÊýºÍÁÐÊý

nrows=table.nrows
ncols=table.ncols

»ñÈ¡ÕûÐкÍÕûÁеÄÖµ

table.row_values(number)
table.column_values(number)

ͨ¹ýÑ­»·¶ÁÈ¡±í¸ñµÄËùÓÐÐÐ

for rownum in xrange(table.nrows):
 print table.row_values(rownum)

»ñÈ¡µ¥Ôª¸ñµÄÖµ

cell_A1=table.row(0)[0].value
#»òÕßÏñÏÂÃæÕâÑù
cell_A1=table.cell(0,0).value
#»òÕßÏñÏÂÃæÕâÑùͨ¹ýÁÐË÷Òý
cell_A1=table.col(0)[0].value

д²Ù×÷×Ô¼ººÜÉÙÓã¬ËùÒԾͲ»¹éÄÉÁË¡£

xlwt

Èç¹û˵xlrd²»ÊÇÒ»¸öµ¥´¿µÄReader£¨Èç¹û°ÑxlrdÖеĺóÁ½¸ö×Ö·û¿´³ÉReader£¬ÄÇôxlwtºóÁ½¸ö×Ö·ûÀàËÆ¿´³ÉWriter£©£¬ÄÇôxlwt¾ÍÊÇÒ»¸ö´¿´âµÄWriterÁË£¬ÒòΪËüÖ»ÄܶÔExcel½øÐÐд²Ù×÷¡£xlwtºÍxlrd²»¹âÃû×ÖÏñ£¬Á¬ºÜ¶àº¯ÊýºÍ²Ù×÷¸ñʽҲÊÇÍêÈ«Ïàͬ¡£ÏÂÃæ¼òÒª¹éÄÉһϳ£ÓòÙ×÷¡£

xlwt³£ÓòÙ×÷

н¨Ò»¸öExcelÎļþ£¨Ö»ÄÜͨ¹ýн¨Ð´È룩

data=xlwt.Workbook()

н¨Ò»¸ö¹¤×÷±í

table=data.add_sheet('name')

дÈëÊý¾Ýµ½A1µ¥Ôª¸ñ

table.write(0,0,u'ºÇºÇ')

×¢Ò⣺Èç¹û¶Ôͬһ¸öµ¥Ôª¸ñÖظ´²Ù×÷£¬»áÒý·¢overwrite Exception£¬ÏëҪȡÏû¸Ã¹¦ÄÜ£¬ÐèÒªÔÚÌí¼Ó¹¤×÷±íʱָ¶¨Îª¿É¸²¸Ç£¬ÏñÏÂÃæÕâÑù

table=data.add_sheet('name',cell_overwrite_ok=True)

±£´æÎļþ

data.save('test.xls')

ÕâÀïÖ»Äܱ£´æÀ©Õ¹ÃûΪxlsµÄ£¬xlsxµÄ¸ñʽ²»Ö§³Ö

xlwtÖ§³ÖÒ»¶¨µÄÑùʽ£¬²Ù×÷ÈçÏÂ

#³õʼ»¯Ñùʽ
style=xlwt.XFStyle()

#ΪÑùʽ´´½¨×ÖÌå
font=xlwt.Font()

#Ö¸¶¨×ÖÌåÃû×Ö
font.name='Times New Roman'

#×ÖÌå¼Ó´Ö
font.bold=True

#½«¸ÃfontÉ趨ΪstyleµÄ×ÖÌå
style.font=font

#дÈëµ½ÎļþʱʹÓøÃÑùʽ
sheet.write(0,1,'just for test',style)

openpyxl

¸ÃÄ£¿éÖ§³Ö×îаæµÄExcelÎļþ¸ñʽ£¬¶ÔExcelÎļþ¾ßÓÐÏìÓ¦µÄ¶Áд²Ù×÷£¬¶Ô´ËÓÐרÃŵÄReaderºÍWriterÁ½¸öÀ࣬±ãÓÚ¶ÔExcelÎļþµÄ²Ù×÷¡£ËäÈ»Èç´Ë£¬µ«ÎÒÒ»°ã»¹ÊÇÓÃĬÈϵÄworkbookÀ´½øÐвÙ×÷¡£³£ÓòÙ×÷¹éÄÉÈçÏ£º

openpyxl³£ÓòÙ×÷

¶ÁÈ¡ExcelÎļþ

from openpyxl.reader.excel import load_workbook

wb=load_workbook(filename)

ÏÔʾ¹¤×÷±íµÄË÷Òý·¶Î§

wb.get_named_ranges()

ÏÔʾËùÓй¤×÷±íµÄÃû×Ö

wb.get_sheet_names()

È¡µÃµÚÒ»Õűí

sheetnames = wb.get_sheet_names() 
ws = wb.get_sheet_by_name(sheetnames[0])

»ñÈ¡±íÃû

ws.title

»ñÈ¡±íµÄÐÐÊý

ws.get_highest_row()

»ñÈ¡±íµÄÁÐÊý

ws.get_highest_column()

µ¥Ôª¸ñµÄ¶ÁÈ¡£¬´Ë´¦ºÍxlrdµÄ¶ÁÈ¡·½Ê½ºÜÏà½ü£¬¶¼ÊÇͨ¹ýÐкÍÁеÄË÷ÒýÀ´¶ÁÈ¡

#¶ÁÈ¡B1µ¥Ôª¸ñÖеÄÄÚÈÝ
ws.cell(0,1).value

µ±È»Ò²Ö§³Öͨ¹ýExcel×ø±êÀ´¶ÁÈ¡Êý¾Ý£¬´úÂëÈçÏÂ

#¶ÁÈ¡B1µ¥Ôª¸ñÖеÄÄÚÈÝ
ws.cell("B1").value

дÎļþ£¬Ö»ÓÐÒ»ÖÖ²Ù×÷·½Ê½£¬¾ÍÊÇͨ¹ý×ø±ê¡£ÀýÈçÒªÏòµ¥Ôª¸ñC1дÊý¾Ý£¬¾ÍÒªÓÃÀàËÆws.cell(¡°C1¡±).value=somethingÕâÑùµÄ·½Ê½¡£

Ò»°ãÍƼöµÄ·½Ê½ÊÇÓÃopenpyxlÖеÄWriterÀàÀ´ÊµÏÖ¡£´úÂëÀàËÆÏÂÃæÕâÑù£º

from openpyxl.workbook import Workbook 
 
#ExcelWriter,ÀïÃæ·â×°ºÃÁ˶ÔExcelµÄд²Ù×÷
from openpyxl.writer.excel import ExcelWriter 

#get_column_letterº¯Êý½«Êý×Öת»»ÎªÏàÓ¦µÄ×Öĸ£¬Èç1-->A,2-->B 
from openpyxl.cell import get_column_letter 

#н¨Ò»¸öworkbook 
wb = Workbook() 

#н¨Ò»¸öexcelWriter 
ew = ExcelWriter(workbook = wb) 

#ÉèÖÃÎļþÊä³ö·¾¶ÓëÃû³Æ 
dest_filename = r'empty_book.xlsx' 

#µÚÒ»¸ösheetÊÇws 
ws = wb.worksheets[0] 

#ÉèÖÃwsµÄÃû³Æ 
ws.title = "range names"

#Ïòij¸öµ¥Ôª¸ñÖÐдÈëÊý¾Ý
ws.cell("C1").value=u'¹þ¹þ'

#×îºó±£´æÎļþ
ew.save(filename=dest_filename)

Ïòij¸öµ¥Ôª¸ñÄÚдÎļþʱҪÏÈÖªµÀËü¶ÔÓ¦µÄÐÐÊýºÍÁÐÊý£¬ÕâÀï×¢ÒâÐÐÊýÊÇ´Ó1¿ªÊ¼¼ÆÊýµÄ£¬¶øÁÐÔòÊÇ´Ó×ÖĸA¿ªÊ¼£¬Òò´ËµÚÒ»ÐеÚÒ»ÁÐÊÇA1£¬Õâʵ¼ÊÉÏÊDzÉÓÃ×ø±ê·½Ê½²Ù×÷Excel¡£ÀýÈ磬ÏëÏò±í¸ñµÄµÚÈýÐеÚÒ»ÁвåÈëÒ»¸öÊýÖµ1.2£¬ÓÃxlwtд¾ÍÊÇtable.write(2,0,1.2),ÒòΪxlwtÖÐÐÐÁÐË÷Òý¶¼´Ó0¿ªÊ¼£»¶øÈç¹ûÓÃopenpyxlд¾ÍÊÇws.cell(¡°A3¡±).value=1.2¡£Ò»°ã¶ÔÓÚÒ»¸ö½Ï´óµÄÁÐÊý£¬ÐèҪͨ¹ýget_column_letterº¯ÊýµÃµ½ÏàÓ¦µÄ×Ö·û£¬È»ºóÔÙµ÷ÓÃcellº¯ÊýдÈë¡£

ÏÂÃæÊÇÎÒ֮ǰдµÄÒ»¸ö´úÂëµÄÒ»²¿·Ö£¬¿ÉÒÔÓÃÀ´ÑÝʾ½«¶àλÊý×é±£´æµ½ExcelÎļþÖС£ÎªÁËÌåÏÖ¶àάÊý×飬ÕâÀïÓõ½ÁËnumpy£¬ÁíÍâÕâÀïΪÁ˼ò»¯¹ý³Ì£¬Ã»ÓÐÓÃExcelWriter¡£´úÂëÈçÏ£º

#coding:utf-8

from openpyxl import Workbook
from openpyxl.cell import get_column_letter

import numpy as np
#Éú³ÉÒ»¸ö¶Ô½ÇÕó
a=np.diag([1,2,3,4,5])

#н¨Ò»¸ö¹¤×÷²¾
wb=Workbook()
#ʹÓõ±Ç°¼¤»îµÄ¹¤×÷±í£¨Ä¬ÈϾÍÊÇExcelÖеĵÚÒ»ÕÅ±í£©
ws=wb.active
#ÏÂÃæÊǶÔaµÄ±éÀú£¬×¢ÒâcellÖÐÐкÍÁдÓ1¿ªÊ¼£¬aÖÐË÷Òý´Ó0¿ªÊ¼¡£
for row in xrange(1,a.shape[0]+1):
  for col in xrange(1,a.shape[1]+1):
    col_letter=get_column_letter(col)
    ws.cell('%s%s'%(col_letter,row)).value=a[row-1,col-1]
wb.save('test.xlsx')

ÔÝʱ½éÉÜÕâô¶à£¬»ù±¾¹»ÓÃÁË¡£

×ܽá

¶ÁÈ¡Excelʱ£¬Ñ¡ÔñopenpyxlºÍxlrd²î±ð²»´ó£¬¶¼ÄÜÂú×ãÒªÇó

дÈëÉÙÁ¿Êý¾ÝÇÒ´æΪxls¸ñʽÎļþʱ£¬ÓÃxlwt¸ü·½±ã

дÈë´óÁ¿Êý¾Ý£¨³¬¹ýxls¸ñʽÏÞÖÆ£©»òÕß±ØÐë´æΪxlsx¸ñʽÎļþʱ£¬¾ÍÒªÓÃopenpyxlÁË¡£

³ýÒÔÉϼ¸¸öÄ£¿éÍ⣬»¹ÓÐWin32comµÈÄ£¿é£¬µ«Ã»ÓÐÓùý£¬¾Í²»ËµÁË¡£

ºÃÁË£¬ÒÔÉϾÍÊÇÕâƪÎÄÕµÄÈ«²¿ÄÚÈÝÁË£¬Ï£Íû±¾ÎĵÄÄÚÈݶԴó¼ÒµÄѧϰ»òÕß¹¤×÷¾ßÓÐÒ»¶¨µÄ²Î¿¼Ñ§Ï°¼ÛÖµ£¬Ð»Ð»´ó¼Ò¶Ô½Å±¾Ö®¼ÒµÄÖ§³Ö¡£