Python - 对excel内容操作

 xlrd模块

 1.1.1 读取excel内容，安装xlrd模块 pip install xlrd

#coding=utf-8

import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import xlrd


class module(object):

  @staticmethod
  def get_table_info():
    '''
      获取excel表格内容
    :return:
    '''
    data = xlrd.open_workbook('D:\\test.xls')
    sheet = data.sheets()[0]
    rows = sheet.nrows
    info = []

    for i in range(1,rows):
        row_data = sheet.row_values(i)
        info.append([row_data[0],row_data[1]])
    return info

#调用,不需要main方法，直接 类.方法名
result = module.get_table_info()
print result

 1.1.2 写入excel内容 ， 安装xlwt 模块 pip install xlwt

  封装excel写入方法：

import sys
reload(sys)
sys.setdefaultencoding("utf-8")
import MySQLdb as mdb
import ConfigParser
import re
import xlwt

class tools_module(object):

    """
      写入excel
    """
    @staticmethod
    def write_excel(xlsName,sheetName,head,data):
        workbook = xlwt.Workbook(encoding='utf-8')
        worksheet = workbook.add_sheet(sheetName,cell_overwrite_ok=True)
        """写入表头"""
        for h in range(len(head)):
            worksheet.write(0,h,label=head[h])
        """写入内容行,格式：[[row[0]],[],[]]"""
        for d in range(len(data)):
            for h in range(len(head)):
                worksheet.write(d+1,h,label=str(data[d][h]))

        workbook.save(xlsName)

if __name__ == '__main__':
head = ["一级类目", "三级类目", "skuId", "spuId",
                "商品名", "供应商ID", "供应商名", "限售模版ID", "限售模版名", "限售详情"]
data = []
for r in skuInfos:
     data.append([r[9], r[5], r[2], r[0], r[1], r[3], '供应商', '限售模版ID', '限售模版名', '限售详情'])
tools_module.write_excel('ccc.xls', '限售区域', head, data)

 data: 可以理解为从数据库中获取的集合，封装成二维数组