python 操作表格

1.常见表格的读操作
#coding=utf-8
import xlrd
import os
import re
import sys
import time
import datetime
import requests
import chardet
import traceback
import csv
import warnings
warnings.filterwarnings("ignore")


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

def print_xls(path):
    data=xlrd.open_workbook(path)   #打开excel
    table=data.sheets()[0] #打开excel的第几个sheet
    nrows=table.nrows   #捕获到有效数据的行数
    print nrows
    books=[]
    for i in range(nrows):
        ss=table.row_values(i)   #获取一行的所有值，每一列的值以列表项存在
        url = ss[0]
        # for i in range(len(ss)):
        #     print ss[i]            #输出一行中各个列的值
        #     print '+++++++++++++++++++'
        print url
        pass

def read_csv(path):
    file = open(path)
    reader = csv.reader(file)
    for line in reader:
        site = ''
        for row in line[0:1]:
            site = site + row.decode('utf-8')
            pass
        for row in line[3:]:
            site = site + row.decode('utf-8')
            pass
        print site    
    pass

if __name__ == '__main__':
    db_client = MongoClient('192.168.86.136',27017)
    read_csv(u'黄山市学校数据.csv')
    print_xls(u'黄山市学校数据.xls')

db_client.close()

2.xls表格写操作

#coding=utf-8
import os
import re
import time
import requests
import json
from pymongo import MongoClient
import traceback
import urlparse
import urllib
import urllib2
import hashlib
import chardet
import random
import xlwt

import sys
reload(sys)
sys.setdefaultencoding("utf-8")

#导出数据
def export_mongo_data():
    file = xlwt.Workbook(encoding = 'utf-8')
    sheet_name = u'name'
    round = 1
    while True:
        result = db_client.xxxxxx.xxx.find({'xxx':{'$exists':False}}).limit(2000)
        if round > 1:
            sheet_name_temp = sheet_name + str(round)
        else:
            sheet_name_temp = sheet_name
        if result.count():
            table = file.add_sheet(sheet_name_temp)
            table.write(0,0,u'id')
            table.write(0,1,u'url')
            table.write(0,2,u'name')
            row_index = 1
            for curr_res in result:
                url = curr_res['url']
                if url:
                    try:
                        _id = curr_res['_id']
                        zzz = curr_res['zzz']
                        paper_name = zzz + _id + ".html"
                        #print paper_name
                        table.write(row_index,0,curr_res['_id'])
                        table.write(row_index,1,curr_res['url'])
                        table.write(row_index,2,paper_name)                
                        db_client.xxx.xxx.update({'_id':curr_res['_id']},{'$set':{'xxx':True}})
                        print curr_res['_id']
                    except Exception as e:
                        db_client.crawler_zuowen.gaosanW_byzhinengyuejuan.update({'_id':curr_res['_id']},{'$set':{'xxx':False}})
                        continue
                    row_index += 1
                else:
                    db_client.crawler_zuowen.gaosanW_byzhinengyuejuan.update({'_id':curr_res['_id']},{'$set':{'zzz':False}})
            round += 1
        else:
            break
    file.save(u'name.xls')

if __name__ == '__main__':
    db_client = MongoClient('xxx.xxx.xx.xxxx',27017)
    export_mongo_data()
    db_client.close()