1.使用第三方库
python中处理excel表格，常用的库有xlrd（读excel）表、xlwt（写excel）表、openpyxl（可读写excel表）等。 xlrd读数据较大的excel表时效率高于openpyxl，所以我在写脚本时就采用了xlrd和xlwt这两个库。介绍及下载地址为：http://www.python-excel.org/ 这些库文件都没有提供修改现有excel表格内容的功能。一般只能将原excel中的内容读出、做完处理后，再写入一个新的excel文件。
1.安装
pip install xlwt
pip install xlrd
2.使用
1.导入模块
import xlrd #导入模块
2.获取一个工作表的数据
table = data.sheets()[0] #通过索引顺序获取，这个是个地址列表
#[u'\u65e5\u671f', u'\u5c0f\u65f6', u'\u697c\u5c42', u'\u5e97\u94fa\u53f7', u'\u5e97\u94fa\u540d\u79f0', u'\u4eba\u6570']
table = data.sheet_by_index(0) #通过索引顺序获取
table = data.sheet_by_name(u'Sheet1')#通过名称获取 #获取整行和整列的值（数组）
table.row_values(i)
table.col_values(i) #获取行数和列数
nrows = table.nrows
ncols = table.ncols
#循环行列表数据
for i in range(nrows ):
print table.row_values(i)
#单元格
cell_A1 = table.cell(0,0).value
cell_C4 = table.cell(2,3).value #使用行列索引
cell_A1 = table.row(0)[0].value cell_A2 = table.col(1)[0].value
3.写入数据到一个sheel中
w = xlwt.Workbook()#创建一个写入的exel对象
sheet = w.add_sheet('sheet1')#添加一个sheet页
for i in range(len(buf)):
print buf[i]
sheet.write(i,0,buf[i].decode('utf8'))#写入数据
w.save(outEfile) #将数据写入到保存文件，没有这一步数据在内存中
4.设置格式
创建一个样式对象，对其进行设置，之后应用到exel格式中。样式对象有很多设置，举例常用的几个，一个是字体front设置，一个是边框borders设置，一个是Pattern
style = xlwt.XFStyle() # 初始化样式
创建一个front设置，设置其字体等：
font = xlwt.Font() # 为样式创建字体
font.name = name # 'Times New Roman'#设置字体
font.bold = bold#设置字体颜色
font.color_index = 4
font.height = height#高度
创建一个borders设置：
borders = xlwt.Borders()#创建一个边框对象
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
borders.bottom_colour=0x3A
创建一个Pattern设置：
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green,
应用到样式中：
style.borders = borders
style.font = font
style.pattern = pattern # Add Pattern to Style
最后演示结果图：
aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAh8AAAB6CAIAAACQmaZGAAAVoklEQVR4nO2dv47cNrvGdR+uzjWcbaIm3bmEr9oAqVQbOReQ4yZFqmn3cwIYsQMDxszsrteWnSCFi/ikCJDCdqd+bkNfoZH0knwlUhLJlxo9PwiGV6M/DynyfURSErP/BxynYEin7HJAlgKQMlm4MAoAAGCzwF0AAAD4B+4CAADAP3AXAAAA/oG7AAAA8A/cBQAAgH/gLgAAAPwDdwEAAOAfuAsAAAD/wF0AAAD4x+4u79+//7Rm3r9/HyEfw/Hp0ydpCYuAflmgX5Yt67e7y7t37wS9YTnv3r2bnTsp8M8//0hLWAT0ywL9smxZv91dyrKUNohFlGU5O3dS4O+//5aWsAjolwX6Zdmyfru7vH37NmDsPzx+9M2/vv8Q8Axv376dnTuDfHxylWlcPfno/zyn0+mvv/4KctyTmorrF4FOEkS/kf+hcj+W/nDXIFT54ZIQIgEBy//pxXX4IhSs/KiZba7xxBL9vLt8/vy5+/+bN2+CRf7fv//uq29/ePzoh+fBTvHpzZs3s3NnEPNavrgOFB3+/PPPAEfVBH98chXKYILo1/P/xXWw6BBFf0BClZ9YSQipnyYgVBGKVH6CXY4l+hl3+fz5M43IAd3lw49ff/P4afNvqHPEcpfTi+sw8e3Dhw/+D2qa4ccnVyvSH9HdI+kPRpjyEy8JYfRzxT1MiiKVn2CXY4l+3V0aa6ER+eHhIVDcf/fvfzWtlqc/fPX1v38PdJaHh4fZuTMIG93C1LU//vjD/0GDxWKTIPojunss/aEIov8ULwnIf4aI7rJEv+IunbVQd3n9+nWYsP/8227E5fD40Xc/Bno07fXr1/Ozdgiu0zlQz8xvv/3m/6ChQjFDEP1MXQpVvYLpjzPsEkb/iUvCivRfQvmPVICW6O/dhVoLdZf7+/sgUV9xlOfffvPVt4cg57m/v1+Uuyz8vUOQAhvkqYSItSvYUxWR3CWW/lAE0X+Kl4QI5Z8M7vuvFZHKT7DLsUT/2V00a6Hucnd3FyDm//79d189+kZdwozt393dLc1gE75nJsjlDdL2itgzFqztGKlnLJb+UATRf4qXhIj5H6QIRdIf7HIs0Z+dOGuh7nJ7e+s/5Jsj+R9+/DrMo8m3t7ce8lgjYnQ7Ho/+D8rYSyjDCaI/4qh+JP3BCFN+4iUhjH62qyFIFY5UfoJdjiX6M9ZaQrvL0x+Ylkqgsf047hKsY+z06tWrAEdtem47xepfXgmiP+ITyVH0ByRQ+YmWhJD6aZlphjL8F6JI5SfY5ViiP2OthbrL8Xj0HfDJeD7l8PhRgEeTg907RBnTPJ1evnwZ5sDqy2TBAkUQ/Ub+h4tzcfSHS0Wo8hPLXQKWf+0yhElNsPITyV2W6Le/q384hBltj8XhcJidOynw/PlzaQmLgH5ZoF+WLeu3u8t+v5c2iEXs9/vZuZMCz549k5awCOiXBfpl2bJ+i7v81//+z2UsszNInJ9++klawiKgXxbol2XL+rP7+/u7u7u7u7vb29vj8Xg8Hg+Hw+Fw2O/3+/1e3BV8LTcAAAAiko13KzWhuV4zbRKylS5ZJq8B+te7XIT+FfPlyxdpCYtYot/dXeTL2awF7gL90L/iBe4iC9xlZIG7QD/0r3iBu8gi4C7VLsuyLN/p/2eXonmcvBApnYy7NIKLsl1TZXkjr8ryLNtV/KHKwpLMkLWLz9JGKv3/yNJsloj+PtuHlRdUbbtxGVf8mP46q8vzmxKDmd/IzrOqzmqj4BVRUtTrr7KcFnvrQsWXTBVuaoS14PnQv2LgLqHcZZdz74wRwgc7B3dpa05ZjAWLdbhLqSbNvBZtpJPVX9c2d6myHcnw7tLEL0Jj7mL17JTcpSr7LG2UDHG+IgPu0smGu7iwaXcZfzdec5eirRtWd9GKr/JrG1YiVK2auEsjaVcNust5myKrqv621MqEm8Fl0YEurLt0Ibgox8KxkQAB/bQYjISnJhVlyRSwaIuinxRdpyxNyV1qqqHgi65iGHAXH8BdnNzlbBhdFGbdpewrzC4/l7yuBVOUWR/0It1Ek7ZL2wM24i5DS5OEXOL2v4sO5v0mdZc4VrFEv744uAvdzBLH4+hX3WJ80RvuhXIFSyF3aZauChQZqaRFbxhaYduRug93mQTcxa3tQsLBkLucb5mNOq/dSYe+3yeL0jPWVPimqhS7wbCl3SafrYXYZ4SgMB4dzLYLk6sON9qhQ8OQ/jF5RZ/P5xCW94Muu1y9EMm6S7sl/bdU2w2x3aVUr3jXOs+zqs6q0jB7tF18AHdx7Rnr4izvLsbd6Hj/TITQVqvuwnYLdLdppp6hn7pkRfAYNjo7jeqPjoQ7PgsQSD+Vx49yqcG3aMJcu0u+S95dmgGw3Xn7wrytKWK7S1me629RKrlXkuqAnjHvwF1c3eUcbdu7MM1dul8r1VeY+kNHNcIGCPuoPh2u6LbpVpZ0jSr13PUhMW5x8e7SiezvWpowV2V5k6K03eUceUu+1bLLlQZZzPxvTk31l+rNFp9YuMtc4C6u7tJFhII8z9O5y8jzM9anloMturt0zRFlcLW5uxwJVeaTCKOPL/uuXfoyyV1GSNxdam60SSfwMJiiv3Q9Y7Ujja3ivGPeNh3Kou+YFRl3Ge9UONdTuIsP4C7O7qKOVVJ36ShpPbRGhrhPJNM6dXYX9XFkWtXpA0tdH0K+y6qK1E+Re+eWSU8ku0fGgPrpYnWXzr/JM2NFl+exHiTLTHdxvuj0QZjaaCWIjOoXavGg1Zkft4O7LAPuMsFdzjXEbLsU7W2aUT7OgViNa8pQecCld5f+dRbSM9bXkCrLVT0F2Tgr2icCKmODGLWrl1oapzbdhV6F7sk96i5mL18E/fpic5cuNNOBvVLtho3sLlPfeerHOUiKBN2F9l13f+bkeUhFTOsuu0Kp73CXScBdJrhLXQ4/M1Yyd81DA+NaxQu2kLZLqQjWxjY7SSWVXfTbVFW/cpdnRanfmUaIDt0y5C5azO3uTPuBCjoqFqUdM9tdOju3vLsTMf+n3U8YjxeKu4tS6dpmbtei0oZkaLbnZEe4yyTgLlPchdQT93f1zSoUa1R5eFTf7Pgmw+Ddq5dMZ0i3WTfCHCs6sLlHQwbte9EeOtC9MNa3VWa6i01e/LbLNEumj7epmsXchXaNkn5FWmaKTLn/oLcsmdpuhrs4sml3GTUXxV0mvP6dn785kXFdSR1R3noZdJfmoR29bnAd6+z3bKK9ssNGZ6Y3rFemhwyq3+z6iPYlFfv4PE3CwAiHUgijt73KYsp1L3WF4l+CyUnvVnfdmdEgmu1t6eq2h7tMAu4yue2yhoVJ7BoX1l1WtED/6vUbNtkscBcX4C6X6S7a9JTSepbUrhUv0A/9i/WvGLiLi7usD7hLCgv0Q/9i/Stm0+5yf39/d3d3d3d3e3t7PB6Px+PhcDgcDvv9fr/fR5v33vtSG+7i3OsPAEiIL2CdZKdRxE3Co7t4c/O4ZFmW/Z+ySCuaxgXol5awCOiX5cuW2y7j7nI6nW5ublYaGi7SXaTlTOYC9EtLWAT0ywJ3cXUXj6KjsfbSSaOztJY5XIB+aQmLgH5ZNu0u1y9c3aWu67rq350oyu4g3Vqyrv+Fruz3z3fV2O7kRGRT9xORFNLSGUS/voNbWmx7tb900Xmifm5l/7YI1TF8dh8o+nkBnYogAhYyJ7rxaSmLoVIzuIsHUozOI9XIyIcU9U9h0+6SZVdPPtrdpa7rJjacr3xZdCWjW0t+rruN6Kp+p2qXj+9eFmZdcz+RlkLjAD712xVyaZmSrmxG/jMrqWbtSPzZ/dDrHxBAUnop7sIDd2lQi6StGKSnfxqbdpcnV5bmy83NzXlbpXZ0tYGsJTWkuTvJiyJXoqxZu/jduU3dT6SmsCudQfTPS8uEdM3Szx+TO6l1y6UMRIf+vNYrKAvcxTNKSu3FIDn9E9m0u1xnzu6i0N6ADJWVsmzv8dtf2frD717t8vEm8+iJtBQORjcf+uelZUq65ugfOKZyzr4R5m6fc2D102aM9QrKoug3e/aqXZ4Vu7anh0RK2ops+h0Lkr1kyrq61ncJpz8J+DuvoWKQnv5pbNpdbB1jvLu4hietmuW7Uuvj53cviyzPc6YaO51IT+FodFuoXz+oW1rse/W/ztE/csyqnfrAJVd9oOvXBCjrE3cXkj/05sHso1QakbQXSL9mA9cxkP50aAdezOIGd0mMRe5iG9Rn3EXpLZ0UnZV6OBwHlVGNdtTCn7v41K8f1y0t1r3Ir3P0Wz2DiYMDWy6Gjw7mIM863MWQqLTC2iRw3ZaWq7Mhd1GHL23FID3909i0u9jMRXcXeq/W/j3h3l8fNrBHN7eI6ewufvX3D7+c/3BKy4S9DHdx0u8rV31gHXc5swJ3qWnXGNP4NtxFffiD5jllY+4ysSInp38icBdXd2HuNcaHhQfHLSyD6tzxnU+kpZCUzgD6nQ+lbeAsYJb+NY3qn1mHu7Swjb85bZcWuAvza10nqH8icBc3d1HvxDrGHmlVy0pflMafiKUHIoPi7idSUtiVzjD6LYcaSIu7gHn6/eSqD5Rn3kayIn13ofnv6C62cRdul2D6U0EdbELPWNosfN/F9ZmxvlNA6xwwH4Dp0MuK0ifUwuxOOg/o7u4nUlIYWD/FPS2Wvbqd5+r3kas+yPS21+QrKIsS3QaeGRt2FzqAveOeGQvbcNT1J0JfDuwPd6Sofwqbdpep4y6rY+2lE/plgX5Z1q4f7gJ3SRfolwX6ZVm7frgL3CVdoF8W6Jdl7fo37S4PDw/39/fH43G/37969erly5e//vrrL7/88uzZs59//vnp06c3Nzd6fz8AAMQi/DRXIAiT53dZ3XfUs5Xf+2TZ6mffWrt+aQmLgH5Zvmy57TLVXTzqjsPaS6cWnaXlTOYC9EtLWAT0ywJ3cXUXj6KjsfbSSaOztJY5XIB+aQmLgH5ZNu0ulm9YYvYwu359B7e02PZqf+mi80T93ErzdY3xs/tA0c9mYLfO/2doPNCXH/0LLl4VB3rfZfAb2wmgJXngZah09buxaXexvUyJ2cMs+u0KubRMSdelzB7GZ6C6Lr33KVV3UfXNyLKhRC5MvPN39pJBq1x82a7T1e/Kpt3l2mFuyvO27GcoBz5U1dyIYPYwh90sAmbpT/M7Y1xOhP+M5kLG3GWGYLgLW7n4sl3XSeqfxKbdZd78Lv29xkB0wOxhmD2sYTQ6r9xdaKvU7PNjCo/xAZiOZoP297HvJFW7XJ+vbOywCUZnW51VmoQJ6p/Ept3FZdzF3M01PNFfS8wexoVOm4A5+keOeQ6CbrnqA63tZWSg9p3H5PrGRsddRrsarZ8go9DEq1mi92tWu9H5yvTDphudBzRrvc7p6ndj0+7iMu6i7VPtMHsYZg9zRXEXNgPbqN1YT9ruoqrrLq71Kji5i2H51hLlcNh0ozOnWSnbdV3DXaSJNe5S17V659T+PeHeX+9atUc3t4jp7C5+9fe3swVmD+OxjRtxqlLCNu6SFaVD2XZyF3Zjo6U08bDpRmdDs16267qGu0gTz13MOwvLsPDguEX3h6fxZzd3CaDf+VDaBs4CZulPclTfmoFhBCzEaVTfT9vFbeMLdReubNc13EWaRe5y5T6qr3WItow90qoWoD6SjT8RSw9UYfawS5k9jM9Aa6YKM+Iu5IJwBVofUupswDb8po62nNeyRqKs5A+bbnTWE8I3W9PV78am3WV80OWE2cPs+inuabHs1e08V7+PXPWBGh24DBx+4ikFsrFRfZJj/a9qH9l51a6gRmPm9WAr2cidYR9iD5slG51JQobLNtxFmOBfgvGoNT5rL53QLwv0y7J2/XAXuEu6QL8s0C/L2vXDXeAu6QL9skC/LGvXv2l3wexhAICUCT/NFQgCZg9LnSxb/exba9cvLWER0C/Lly23Xaa6i0fdcVh76dSis7ScyVyAfmkJi4B+WeAuru7iUXQ01l46aXSW1jKHC9AvLWER0C/Lpt3F5X0XzB42ql/fwS0ttr3aX7roPFE/t7J/rYDqGD67D3j97PmHs1WQvvwYL2UoYs1Pz42/HzO0Vzj9IrAlduSKGxVZWP9i4C52d6nr2nghWX9N2XjXusTsYUNpmZKui5k9TM0/5/yRZSC6ablV7fKsKNSEGIHSsBJuL9+IRme+xI5c8bLQKzLcRZbg7nLelv8KIf+hqubuBLOHOexmETBLf/rfGVM+AJNei6WHjW66EzdJ0zLQ+jU8di/fSEZnvsQOX/Gyma0C7pIQsdxFoa1fAyEDs4dh9rAGm7vY8kcaJv+NVh5n9XZ34ffyTTLRucu1oSte7XLGa5PRPxO4y2R3cQ1P9NcSs4fZPl/ICZijf+SY5y5vt1z1Adsz2ajoQutY/khj5L/Zg0jyjWYh2zOm9LNye/kmkeis9oUzV/zsOXCXxIjtLtUOs4dh9jBXFP3tcG4/UZg1f6TR8p8ZnVKyjfxujOrrTwGwewXWL4JRYs0rXhbM3VJdp6F/CXCXCe5S7TB7WP9DH0AKzB7GM9yzNzoIlwyGOzIPOen0DTQaUvUhF36voPol0EuswvmK97cVcJfEWOQumW3qY8weNqrf+VDaBs4CZulPclSfMiF/hCH6GW8ZKDOmc6vt3ZG9AuoXgCuxlOaKl8YH+Pt94C6yxGq7DFSAcuSRVrUWKc8JteuZ3emBKswedjGzh3H555A/snT62exnM400UY1xl3xXje/lG8nobM0y84qj7ZIYkdzFvMFQRiTYtr1eVpQ+oRZmd9JxQHd3PxFJIWYPsygZPrsPlOjQncp8ziCYgIW0+tm+rIJ3hCaAMmWyLKx7hdIvwFCJHbvicJfECP4lGI9a47P20gn9skC/LGvXD3eBu6QL9MsC/bKsXT/cBe6SLtAvC/TLsnb9m3YXp9nD/jtb8ZJJC4B+cQ3Qv94Fs4etlstvu3zZ8L1DCkC/LNAvy5b1w11SB/plgX5ZoF8WuMsYW766KQD9skC/LFvWn2VXTz7CXRIG+mWBflmgXxa0XcbY8tVNAeiXBfpl2bJ+uEvqQL8s0C8L9MsCdxljy1c3BaBfFuiXZcv6M8uoC9xFGuiXBfplgX5ZFrnLteUD/HAXYaBfFuiXBfplWegulofG4C6yQL8s0C8L9MsCdxljy1c3BaBfFuiXZcv6syu875I20C8L9MsC/bIscpcJc1Ouky1f3RSAflmgX5Yt68cTyakD/bJAvyzQLwvcZYwtX90UgH5ZoF+WLeuHu6QO9MsC/bJAvyyL3MVl9rAvAAAAwBTQdkkd6JcF+mWBflmW6Ie7pA70ywL9skC/LHCXMbZ8dVMA+mWBflm2rN86eRjcRRjolwX6ZYF+WZa5C74EkzbQLwv0ywL9sqBnbIwtX90UgH5ZoF+WLev/D2tCZxrpeyYaAAAAAElFTkSuQmCC" alt="" />
最后是官方实例，比较详细，有用到的可以看一下：
Examples Generating Excel Documents Using Python’s xlwt Here are some simple examples using Python’s xlwt library to dynamically generate Excel documents. Please note a useful alternative may be ezodf, which allows you to generate ODS (Open Document Spreadsheet) files for LibreOffice / OpenOffice. You can check them out at:http://packages.python.org/ezodf/index.html The Simplest Example
import xlwt
workbook = xlwt.Workbook(encoding = 'ascii')
worksheet = workbook.add_sheet('My Worksheet')
worksheet.write(0, 0, label = 'Row 0, Column 0 Value')
workbook.save('Excel_Workbook.xls') Formatting the Contents of a Cell
import xlwt
workbook = xlwt.Workbook(encoding = 'ascii')
worksheet = workbook.add_sheet('My Worksheet')
font = xlwt.Font() # Create the Font
font.name = 'Times New Roman'
font.bold = True
font.underline = True
font.italic = True
style = xlwt.XFStyle() # Create the Style
style.font = font # Apply the Font to the Style
worksheet.write(0, 0, label = 'Unformatted value')
worksheet.write(1, 0, label = 'Formatted value', style) # Apply the Style to the Cell
workbook.save('Excel_Workbook.xls') Attributes of the Font Object
font.bold = True # May be: True, False
font.italic = True # May be: True, False
font.struck_out = True # May be: True, False
font.underline = xlwt.Font.UNDERLINE_SINGLE # May be: UNDERLINE_NONE, UNDERLINE_SINGLE, UNDERLINE_SINGLE_ACC, UNDERLINE_DOUBLE, UNDERLINE_DOUBLE_ACC
font.escapement = xlwt.Font.ESCAPEMENT_SUPERSCRIPT # May be: ESCAPEMENT_NONE, ESCAPEMENT_SUPERSCRIPT, ESCAPEMENT_SUBSCRIPT
font.family = xlwt.Font.FAMILY_ROMAN # May be: FAMILY_NONE, FAMILY_ROMAN, FAMILY_SWISS, FAMILY_MODERN, FAMILY_SCRIPT, FAMILY_DECORATIVE
font.charset = xlwt.Font.CHARSET_ANSI_LATIN # May be: CHARSET_ANSI_LATIN, CHARSET_SYS_DEFAULT, CHARSET_SYMBOL, CHARSET_APPLE_ROMAN, CHARSET_ANSI_JAP_SHIFT_JIS, CHARSET_ANSI_KOR_HANGUL, CHARSET_ANSI_KOR_JOHAB, CHARSET_ANSI_CHINESE_GBK, CHARSET_ANSI_CHINESE_BIG5, CHARSET_ANSI_GREEK, CHARSET_ANSI_TURKISH, CHARSET_ANSI_VIETNAMESE, CHARSET_ANSI_HEBREW, CHARSET_ANSI_ARABIC, CHARSET_ANSI_BALTIC, CHARSET_ANSI_CYRILLIC, CHARSET_ANSI_THAI, CHARSET_ANSI_LATIN_II, CHARSET_OEM_LATIN_I
font.colour_index = ?
font.get_biff_record = ?
font.height = 0x00C8 # C8 in Hex (in decimal) = 10 points in height.
font.name = ?
font.outline = ?
font.shadow = ? Setting the Width of a Cell
import xltw
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, 'My Cell Contents')
worksheet.col(0).width = 3333 # 3333 = 1" (one inch).
workbook.save('Excel_Workbook.xls') Entering a Date into a Cell
import xlwt
import datetime
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
style = xlwt.XFStyle()
style.num_format_str = 'M/D/YY' # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
worksheet.write(0, 0, datetime.datetime.now(), style)
workbook.save('Excel_Workbook.xls') Adding a Formula to a Cell
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, 5) # Outputs 5
worksheet.write(0, 1, 2) # Outputs 2
worksheet.write(1, 0, xlwt.Formula('A1*B1')) # Should output "10" (A1[5] * A2[2])
worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) # Should output "7" (A1[5] + A2[2])
workbook.save('Excel_Workbook.xls') Adding a Hyperlink to a Cell
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, xlwt.Formula('HYPERLINK("http://www.google.com";"Google")')) # Outputs the text "Google" linking to http://www.google.com
workbook.save('Excel_Workbook.xls') Merging Columns and Rows
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write_merge(0, 0, 0, 3, 'First Merge') # Merges row 0's columns 0 through 3.
font = xlwt.Font() # Create Font
font.bold = True # Set font to Bold
style = xlwt.XFStyle() # Create Style
style.font = font # Add Bold Font to Style
worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) # Merges row 1 through 2's columns 0 through 3.
workbook.save('Excel_Workbook.xls') Setting the Alignment for the Contents of a Cell
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
style = xlwt.XFStyle() # Create Style
style.alignment = alignment # Add Alignment to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls') Adding Borders to a Cell
# Please note: While I was able to find these constants within the source code, on my system (using LibreOffice,) I was only presented with a solid line, varying from thin to thick; no dotted or dashed lines.
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.DASHED # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
borders.right = xlwt.Borders.DASHED
borders.top = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DASHED
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
style = xlwt.XFStyle() # Create Style
style.borders = borders # Add Borders to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls') Setting the Background Color of a Cell
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
style = xlwt.XFStyle() # Create the Pattern
style.pattern = pattern # Add Pattern to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls') TODO: Things Left to Document
- Panes -- separate views which are always in view
- Border Colors (documented above, but not taking effect as it should)
- Border Widths (document above, but not working as expected)
- Protection
- Row Styles
- Zoom / Manification
- WS Props?
Source Code for reference available at: https://secure.simplistix.co.uk/svn/xlwt/trunk/xlwt/
官方实例
最后是一个小实例，遇到了一个比较坑的东西，我想投机取巧，事前先把样式手动设置好，直接往里面写数据。这时我用到了copy，但是往里面写数据没问题，但是在保存的时候，老是报字符集的问题，这也是python2的一大问题吧。一天我也没解决掉，最后只能先放弃了。
#!/usr/bin/env python
#-*- coding:utf-8 -*-
__author__ = 'wyf' import ConfigParser
import xlwt
import xlrd
import sys
from xlutils.copy import copy
import sys
import codecs
reload(sys)
sys.setdefaultencoding('utf-8') a=[['', '2016-05-01 10', '', '', 'snidel', 81], ['', '2016-05-01 10', '', '', 'Bout hent ique', 12], ['', '2016-05-01 10', '', '', 'ZARA', 254]] def set_style(name,height,bold=False):
style = xlwt.XFStyle() # 初始化样式 font = xlwt.Font() # 为样式创建字体
font.name = name # 'Times New Roman'
font.bold = bold
font.color_index = 4
font.height = height
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
borders.bottom_colour=0x3A
style.borders = borders
style.font = font
style.pattern = pattern # Add Pattern to Style
return style def writeXlwt(result,row0,book,sheelName):
worksheet = book.add_sheet(sheelName)#添加一个sheet页
print '*****=',row0
for i in range(len(result)+1):
if i ==0:
for f in range(0,len(row0)):
worksheet.write(0,f,row0[f],set_style('Arial',220,True))
else:
for a in range(len(result[i-1])):
worksheet.row(i).write(a,result[i-1][a]) if __name__ =='__main__':
#book = xlwt.Workbook(encoding = 'utf-8')
oldWb = xlrd.open_workbook('test123.xls',formatting_info=True)
book = copy(oldWb)
setattr(book,'encoding','utf-8')
#book = xlwt.Workbook()
cf = ConfigParser.ConfigParser()#生成一个实例
#with codecs.open('config_dayueche.conf', encoding="utf-8" ) as f:
cf.read("config_dayueche.conf") #读取配置文件
sheelList = cf.sections()
for sheel in sheelList:
row0 = cf.get(sheel,'row0').split(';')
writeXlwt(a,row0,book,sheel)
book.save('test1232.xls')#打开一个工作薄
实例代码