AI专业者开发社区 1 号成员
  •  0 回帖  •  9 浏览  •  5 个月前
   

python 读写 Excel文件

 

　一、用 xlrd 和 xlwt 读写 excel

　　　　首先下载安装 xlrd 和 xlwt 这两个库。

　　1、打开 excel

　　　　readbook = xlrd.open_workbook(r'\test\canying.xlsx')

　　2、获取读入的文件的 sheet

　　　　sheet = readbook.sheet_by_index(1)#索引的方式，从 0 开始
　　　　sheet = readbook.sheet_by_name('sheet2')#名字的方式

　　3、获取 sheet 的最大行数和列数

　　　　nrows = sheet.nrows#行
　　　　ncols = sheet.ncols#列

　　4、获取某个单元格的值

　　　　lng = table.cell(i,3).value#获取 i 行 3 列的表格值
　　　　lat = table.cell(i,4).value#获取 i 行 4 列的表格值

　　5、打开将写的表并添加 sheet

　　　　writebook = xlwt.Workbook()#打开一个 excel
　　　　sheet = writebook.add_sheet('test')#在打开的 excel 中添加一个 sheet

　　6、将数据写入 excel

 　　　　sheet.write(i,0,result[0])#写入excel，i行0列
 　　　　sheet.write(i,1,result[1])

　　7、保存

　　　　 writebook.save('answer.xls')#一定要记得保存

　　相关代码

# coding=utf-8
import xlrd
import xlwt
import datetime

import os


class excelProcess:
def init(self,keywordExcelFile,mainExcelFile):
self.keywordExcelFile = keywordExcelFile
self.mainExcelFile = mainExcelFile


</span><span style="color: rgba(0, 0, 255, 1)">def</span><span style="color: rgba(0, 0, 0, 1)"> WriteLog(self, message,date):
    fileName </span>= os.path.join(os.getcwd(),  date  +   <span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">.txt</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
    with open(fileName, </span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">a</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">) as f:
        f.write(message)

</span><span style="color: rgba(0, 0, 255, 1)">def</span><span style="color: rgba(0, 0, 0, 1)"> WriteSheetRow(self,sheet, rowValueList, rowIndex, isBold):
    i </span>=<span style="color: rgba(0, 0, 0, 1)"> 0
    style </span>= xlwt.easyxf(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">font: bold 1</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
    </span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> style = xlwt.easyxf('font: bold 0, color red;')#红色字体</span>
    <span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> style2 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow; font: bold on;') # 设置Excel单元格的背景色为黄色，字体为粗体</span>
    <span style="color: rgba(0, 0, 255, 1)">for</span> svalue <span style="color: rgba(0, 0, 255, 1)">in</span><span style="color: rgba(0, 0, 0, 1)"> rowValueList:
        </span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> isBold:
            sheet.write(rowIndex, i, svalue, style)
        </span><span style="color: rgba(0, 0, 255, 1)">else</span><span style="color: rgba(0, 0, 0, 1)">:
            sheet.write(rowIndex, i, svalue)
        i </span>= i + 1

<span style="color: rgba(0, 0, 255, 1)">def</span><span style="color: rgba(0, 0, 0, 1)"> save_Excel(self):
    wbk </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Workbook()
    sheet </span>= wbk.add_sheet(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">sheet1</span><span style="color: rgba(128, 0, 0, 1)">'</span>, cell_overwrite_ok=<span style="color: rgba(0, 0, 0, 1)">True)
    headList </span>= [<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">IR_SITENAME</span><span style="color: rgba(128, 0, 0, 1)">'</span>, <span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">IR_AUTHORS</span><span style="color: rgba(128, 0, 0, 1)">'</span>, <span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">SY_INFOTYPE</span><span style="color: rgba(128, 0, 0, 1)">'</span>, <span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">RID</span><span style="color: rgba(128, 0, 0, 1)">'</span>, <span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">IR_URLTITLE</span><span style="color: rgba(128, 0, 0, 1)">'</span>,<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">SY_KEYWORDS</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">,
                </span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">IR_URLNAME</span><span style="color: rgba(128, 0, 0, 1)">'</span>, <span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">IR_URLTIME</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">,
                </span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">IR_GROUPNAME</span><span style="color: rgba(128, 0, 0, 1)">'</span>, <span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">IR_CHANNEL</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">,
                </span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">SY_BB_COMMON</span><span style="color: rgba(128, 0, 0, 1)">'</span>, <span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">summary</span><span style="color: rgba(128, 0, 0, 1)">'</span>, <span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">keyword</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">
                ]

    rowIndex </span>=<span style="color: rgba(0, 0, 0, 1)"> 0
    self.WriteSheetRow(sheet, headList, rowIndex, True)
    </span><span style="color: rgba(0, 0, 255, 1)">for</span> i <span style="color: rgba(0, 0, 255, 1)">in</span> range(1, 11<span style="color: rgba(0, 0, 0, 1)">):
        rowIndex </span>= rowIndex + 1<span style="color: rgba(0, 0, 0, 1)">
        valueList </span>=<span style="color: rgba(0, 0, 0, 1)"> []
        </span><span style="color: rgba(0, 0, 255, 1)">for</span> j <span style="color: rgba(0, 0, 255, 1)">in</span> range(1, 14<span style="color: rgba(0, 0, 0, 1)">):
            valueList.append(j </span>*<span style="color: rgba(0, 0, 0, 1)"> i)
        self.WriteSheetRow(sheet, valueList, rowIndex, False)
    fileName </span>= os.path.join(os.getcwd(),<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">test.xlsx</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
    wbk.save(fileName)</span></pre>
View Code

 　　8、

 

 　二、使用 openpyxl 库读写 excel

　　　　参考：https://www.cnblogs.com/hls-code/p/15674197.html

　　　　xlrd 和 xlwt 处理的是 xls 文件，单个 sheet 最大行数是 65535，如果有更大需要的，建议使用 openpyxl 函数，最大行数达到 1048576。 
　　　　如果数据量超过 65535 就会遇到：ValueError: row index was 65536, not allowed by .xls format

　　　　首先需要导入 import openpyxl

　　　　1、打开 excel

　　　　　　

　　　　2、获取打开的 excel 的 sheet 内容

 

 　　　　　　

　　　　3、获取 sheet 的最大行数和列数

　　　　　　

　　　　4、获取某个单元格的值

　　　　　　print(ws.cell(1,1).value)

　　　　5、打开将写的表并添加 sheet

　　　　　　

　　　　6、保存

　　　　　　

 

 　　　　完整代码

    def readExel(self):
        filename = r'D:\work\Excel_txtProcesss\new- 微博 - 合并 \58.xlsx'
        inwb = openpyxl.load_workbook(filename)  # 读文件

        sheetnames = inwb.get_sheet_names()  # 获取读文件中所有的 sheet，通过名字的方式
        ws = inwb.get_sheet_by_name(sheetnames[0])  # 获取第一个 sheet 内容

    <span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 获取sheet的最大行数和列数</span>
    rows =<span style="color: rgba(0, 0, 0, 1)"> ws.max_row
    cols </span>=<span style="color: rgba(0, 0, 0, 1)"> ws.max_column
    </span><span style="color: rgba(0, 0, 255, 1)">for</span> r <span style="color: rgba(0, 0, 255, 1)">in</span> range(1<span style="color: rgba(0, 0, 0, 1)">,rows):
        </span><span style="color: rgba(0, 0, 255, 1)">for</span> c <span style="color: rgba(0, 0, 255, 1)">in</span> range(1<span style="color: rgba(0, 0, 0, 1)">,cols):
            </span><span style="color: rgba(0, 0, 255, 1)">print</span><span style="color: rgba(0, 0, 0, 1)">(ws.cell(r,c).value)
        </span><span style="color: rgba(0, 0, 255, 1)">if</span> r==10<span style="color: rgba(0, 0, 0, 1)">:
            </span><span style="color: rgba(0, 0, 255, 1)">break</span>

<span style="color: rgba(0, 0, 255, 1)">def</span><span style="color: rgba(0, 0, 0, 1)"> writeExcel(self):
    outwb </span>= openpyxl.Workbook()  <span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 打开一个将写的文件</span>
    outws = outwb.create_sheet(index=0)  <span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 在将写的文件创建sheet</span>
    <span style="color: rgba(0, 0, 255, 1)">for</span> row <span style="color: rgba(0, 0, 255, 1)">in</span> range(1,70000<span style="color: rgba(0, 0, 0, 1)">):
        </span><span style="color: rgba(0, 0, 255, 1)">for</span> col <span style="color: rgba(0, 0, 255, 1)">in</span> range(1,4<span style="color: rgba(0, 0, 0, 1)">):
            outws.cell(row, col).value </span>= row*2  <span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 写文件</span>
        <span style="color: rgba(0, 0, 255, 1)">print</span><span style="color: rgba(0, 0, 0, 1)">(row)
    saveExcel </span>= <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">D:\\work\\Excel_txtProcesss\\test.xlsx</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">
    outwb.save(saveExcel)  </span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 一定要记得保存</span></pre>
View Code

 　　三、用 pandas 库导出 excel 文件

import pandas as pd
# 单纯导出一个数据集
def exportData_pandas(excelFile='output.xlsx'):
    try:
        # data = {'Name': ['Alice', 'Bob', 'Charlie'],
        #         'Age': [25, 28, 30]}
        name_list = []
        age_list =[]
        for i in range(10):
            name_list.append('name'+str(i))
            age_list.append('age' + str(i))
        data = {'Name':name_list,'Age':age_list}
        df = pd.DataFrame(data)

    </span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 导出 DataFrame 到 Excel 文件</span>
    df.to_excel(excelFile, index=<span style="color: rgba(0, 0, 0, 1)">False)
</span><span style="color: rgba(0, 0, 255, 1)">except</span><span style="color: rgba(0, 0, 0, 1)"> Exception as e:
    </span><span style="color: rgba(0, 0, 255, 1)">print</span><span style="color: rgba(0, 0, 0, 1)">(e)



# 以增加方式导出数据集合，在导出之前，output.xlsx 已经有数据
def exportData_pandas_append(excelFile='output.xlsx'):
try:
original_data = pd.read_excel(excelFile)
name_list = []
age_list =[]
for i in range(10):
name_list.append('name'+str(i))
age_list.append('age' + str(i))
data = {'Name':name_list,'Age':age_list}
data2 = pd.DataFrame(data)
# 将新数据与旧数据合并起来
save_data = original_data.append(data2)
save_data.to_excel(excelFile, index=False)
except Exception as e:
print(e)

View Code

 

font = xlwt.Font()
# 字体颜色
font.colour_index = 0x30
# 初始化样式
style2 = xlwt.XFStyle()
style2.font = font
#超链接相对路径
sheet1.write(0, 0, xlwt.Formula('HYPERLINK("{}", "{}"\r)'.format('./first/test.txt', '点击查看'),style2)
#超链接绝对路径
sheet1.write(0, 0, xlwt.Formula('HYPERLINK("{}", "{}"\r)'.format('D:/future/first/test.txt', '点击查看'),style2)

#超链接 url
sheet1.write(0, 0, xlwt.Formula('HYPERLINK("{}", "{}"\r)'.format('https://www.baidu.com/', '点击查看'),style2)

 

  # 1、# 给表格内容设置样式，即设置字体等
    import xlwt
    workbook = xlwt.Workbook(encoding='utf-8')
    worksheet = workbook.add_sheet('sheet1')
    # 创建字体样式对象
    font = xlwt.Font()
    # 设置字体（常用字体都支持）
    font.name = '微软雅黑'
    # 设置下划线
    font.underline = True
    # 设置加粗
    font.bold = True
    # 设置斜体
    font.italic = True
    # 设置字体为红色, 这里不能使用 16 进制表示颜色的方法去设置
    font.colour_index = xlwt.Style.colour_map['red']
    # 创建样式对象
    font_style = xlwt.XFStyle()
    # 设置字体样式
    font_style.font = font
    # 往表中写内容, 第四个参数：字体样式
    worksheet.write(0, 0, '小红', font_style)
    workbook.save('students.xls')

</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 2、单元格设置（对齐方式、大小）</span>


  
    workbook = xlwt.Workbook(encoding='utf-8')
    worksheet = workbook.add_sheet('sheet1')
    # 创建一个单元格设置格式对象
    alignment = xlwt.Alignment()
    # 设置水平居中
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    # 设置垂直居中
    alignment.vert = xlwt.Alignment.VERT_CENTER
    # 创建样式对象
    style = xlwt.XFStyle()
    # 把水平 \ 垂直居中放入样式对象
    style.alignment = alignment
    # 设置单元格宽度
    worksheet.col(0).width = 5000
    # 设置单元格高度
    worksheet.row(0).height_mismatch = True
    worksheet.row(0).height = 800
    worksheet.write(0, 0, 'name', style)
    workbook.save('stu_info.xls')

</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 3、设置单元格边框（边框颜色、边框样式）</span>
<span style="color: rgba(0, 0, 255, 1)">import</span><span style="color: rgba(0, 0, 0, 1)"> xlwt
workbook </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Workbook()
worksheet </span>= workbook.add_sheet(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">sheet1</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 创建边框样式对象</span>
borders =<span style="color: rgba(0, 0, 0, 1)"> xlwt.Borders()
</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 设置上下左右边框的线条样式：DASHED 表示虚线</span>
borders.top =<span style="color: rgba(0, 0, 0, 1)"> xlwt.Borders.DASHED
borders.bottom </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Borders.DASHED
borders.left </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Borders.DASHED
borders.right </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Borders.DASHED
</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 设置上下左右边框的颜色 0x0A 表示红色</span>
borders.top_colour = 0x0A<span style="color: rgba(0, 0, 0, 1)">
borders.bottom_colour </span>= 0x0A<span style="color: rgba(0, 0, 0, 1)">
borders.left_colour </span>= 0x0A<span style="color: rgba(0, 0, 0, 1)">
borders.right_colour </span>= 0x0A
<span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 创建样式对象</span>
style =<span style="color: rgba(0, 0, 0, 1)"> xlwt.XFStyle()
</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 把边框样式对象放入样式对象</span>
style.borders =<span style="color: rgba(0, 0, 0, 1)"> borders
worksheet.write(</span>1, 1, <span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">小明</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">, style)
workbook.save(</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">stu_info.xls</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)

</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 4、设置单元格背景色</span>
<span style="color: rgba(0, 0, 255, 1)">import</span><span style="color: rgba(0, 0, 0, 1)"> xlwt
workbook </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Workbook()
worksheet </span>= workbook.add_sheet(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">My Sheet</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 创建样式对象</span>
pattern =<span style="color: rgba(0, 0, 0, 1)"> xlwt.Pattern()
</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 设置固定样式</span>
pattern.pattern = xlwt.Pattern.SOLID_PATTERN  <span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12</span>
<span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 设置背景颜色 0 黑色 1 白色 2 红色 3 绿色 4 蓝色 5 黄色 6 紫红色等等</span>
pattern.pattern_fore_colour = 5</pre>

 

字体颜色表

 aqua 0x31
 black 0x08
 blue 0x0C
 blue_gray 0x36
 bright_green 0x0B
 brown 0x3C
 coral 0x1D
 cyan_ega 0x0F
 dark_blue 0x12
 dark_blue_ega 0x12
 dark_green 0x3A
 dark_green_ega 0x11
 dark_purple 0x1C
 dark_red 0x10
 dark_red_ega 0x10
 dark_teal 0x38
 dark_yellow 0x13
 gold 0x33
 gray_ega 0x17
 gray25 0x16
 gray40 0x37
 gray50 0x17
 gray80 0x3F
 green 0x11
 ice_blue 0x1F
 indigo 0x3E
 ivory 0x1A
 lavender 0x2E
 light_blue 0x30
 light_green 0x2A
 light_orange 0x34
 light_turquoise 0x29
 light_yellow 0x2B
 lime 0x32
 magenta_ega 0x0E
 ocean_blue 0x1E
 olive_ega 0x13
 olive_green 0x3B
 orange 0x35
 pale_blue 0x2C
 periwinkle 0x18
 pink 0x0E
 plum 0x3D
 purple_ega 0x14
 red 0x0A
 rose 0x2D
 sea_green 0x39
 silver_ega 0x16
 sky_blue 0x28
 tan 0x2F
 teal 0x15
 teal_ega 0x15
 turquoise 0x0F
 violet 0x14
 white 0x09
 yellow 0x0D

 

　三、

　　style2 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow; font: bold on;')

　　在设置上 Excel 单元格的背景色时，fore_colour 支持的颜色是有限的，仅支持一下颜色

　　aqua 0x31
　　black 0x08
　　blue 0x0C
　　blue_gray 0x36
　　bright_green 0x0B
　　brown 0x3C
　　coral 0x1D
　　cyan_ega 0x0F
　　dark_blue 0x12
　　dark_blue_ega 0x12
　　dark_green 0x3A
　　dark_green_ega 0x11
　　dark_purple 0x1C
　　dark_red 0x10
　　dark_red_ega 0x10
　　dark_teal 0x38
　　dark_yellow 0x13
　　gold 0x33
　　gray_ega 0x17
　　gray25 0x16
　　gray40 0x37
　　gray50 0x17
　　gray80 0x3F
　　green 0x11
　　ice_blue 0x1F
　　indigo 0x3E
　　ivory 0x1A
　　lavender 0x2E
　　light_blue 0x30
　　light_green 0x2A
　　light_orange 0x34
　　light_turquoise 0x29
　　light_yellow 0x2B
　　lime 0x32
　　magenta_ega 0x0E
　　ocean_blue 0x1E
　　olive_ega 0x13
　　olive_green 0x3B
　　orange 0x35
　　pale_blue 0x2C
　　periwinkle 0x18
　　pink 0x0E
　　plum 0x3D
　　purple_ega 0x14
　　red 0x0A
　　rose 0x2D
　　sea_green 0x39
　　silver_ega 0x16
　　sky_blue 0x28
　　tan 0x2F
　　teal 0x15
　　teal_ega 0x15
　　turquoise 0x0F
　　violet 0x14
　　white 0x09
　　yellow 0x0D"""
另外一种方式是 用pyExcelerator 






from pyExcelerator import *
# excel 第一行数据
excel_headDatas = [u'发布时间', u'文章标题', u'文章链接', u'文章简介']
articles =[
    {u'发布时间':u'2017 年 5 月 9 日',
     u'文章标题':u'Python 项目实战教程：国内就能访问的 google 搜索引擎',
     u'文章链接':'http://mp.weixin.qq.com/s?timestamp=1494557315',
     u'文章简介':u'大家可以留言、想了解 python 那个方向的知识、不然我也不知道'},

{u</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">发布时间</span><span style="color: rgba(128, 0, 0, 1)">'</span>:u<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">2017年5月4日</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">,
 u</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">文章标题</span><span style="color: rgba(128, 0, 0, 1)">'</span>:u<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">对于学习Django的建议、你知道的有那些</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">,
 u</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">文章链接</span><span style="color: rgba(128, 0, 0, 1)">'</span>:<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">http://mp.weixin.qq.com/s?timestamp=1494557323</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">,
 u</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">文章简介</span><span style="color: rgba(128, 0, 0, 1)">'</span>:u<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">随着Django1.4第二个候选版的发布，虽然还不支持Python3，但Django团队已经在着手计划中，据官方博客所说，Django1.5将会试验性的支持python3</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">}



]
# 定义 excel 操作句柄
excle_Workbook = Workbook()
excel_sheet_name = time.strftime('%Y-%m-%d')
excel_sheet = excle_Workbook.add_sheet(excel_sheet_name)
index = 0
#标题
for data in excel_headDatas:
excel_sheet.write(0, index, data)
index += 1


index = 1


#内容
for article in articles:
colIndex = 0
for item in excel_headDatas:
excel_sheet.write(index, colIndex, article[item])
colIndex += 1
index += 1
#保存 test.xlsx 到当前程序目录
excle_Workbook.save('test.xlsx')


# db = mongoDB.mongoDbBase()
# db.Get_information_stat()



View Code