#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 讀取excel數(shù)據(jù)
# 小羅的需求，取第二行以下的數(shù)據(jù)，然后取每行前13列的數(shù)據(jù)
import xlrd
data = xlrd.open_workbook('test.xls') # 打開xls文件
table = data.sheets()[0] # 打開第一張表
nrows = table.nrows # 獲取表的行數(shù)
for i in range(nrows): # 循環(huán)逐行打印
if i == 0: # 跳過第一行
continue
print table.row_values(i)[:13] # 取前十三列 

#coding=utf-8
#######################################################
#filename:test_xlrd.py
#author:defias
#date:xxxx-xx-xx
#function：讀excel文件中的數(shù)據(jù)
#######################################################
import xlrd
#打開一個(gè)workbook
workbook = xlrd.open_workbook('E:\\Code\\Python\\testdata.xls')
#抓取所有sheet頁的名稱
worksheets = workbook.sheet_names()
print('worksheets is %s' %worksheets)
#定位到sheet1
worksheet1 = workbook.sheet_by_name(u'Sheet1')
"""
#通過索引順序獲取
worksheet1 = workbook.sheets()[0]
#或
worksheet1 = workbook.sheet_by_index(0)
"""
"""
#遍歷所有sheet對(duì)象
for worksheet_name in worksheets:
worksheet = workbook.sheet_by_name(worksheet_name)
"""
#遍歷sheet1中所有行row
num_rows = worksheet1.nrows
for curr_row in range(num_rows):
row = worksheet1.row_values(curr_row)
print('row%s is %s' %(curr_row,row))
#遍歷sheet1中所有列col
num_cols = worksheet1.ncols
for curr_col in range(num_cols):
col = worksheet1.col_values(curr_col)
print('col%s is %s' %(curr_col,col))
#遍歷sheet1中所有單元格cell
for rown in range(num_rows):
for coln in range(num_cols):
cell = worksheet1.cell_value(rown,coln)
print cell
"""
#其他寫法：
cell = worksheet1.cell(rown,coln).value
print cell
#或
cell = worksheet1.row(rown)[coln].value
print cell
#或
cell = worksheet1.col(coln)[rown].value
print cell
#獲取單元格中值的類型，類型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
cell_type = worksheet1.cell_type(rown,coln)
print cell_type
"""

#coding=utf-8
#######################################################
#filename:test_xlwt.py
#author:defias
#date:xxxx-xx-xx
#function：新建excel文件并寫入數(shù)據(jù)
#######################################################
import xlwt
#創(chuàng)建workbook和sheet對(duì)象
workbook = xlwt.Workbook() #注意Workbook的開頭W要大寫
sheet1 = workbook.add_sheet('sheet1',cell_overwrite_ok=True)
sheet2 = workbook.add_sheet('sheet2',cell_overwrite_ok=True)
#向sheet頁中寫入數(shù)據(jù)
sheet1.write(0,0,'this should overwrite1')
sheet1.write(0,1,'aaaaaaaaaaaa')
sheet2.write(0,0,'this should overwrite2')
sheet2.write(1,2,'bbbbbbbbbbbbb')
"""
#-----------使用樣式-----------------------------------
#初始化樣式
style = xlwt.XFStyle() 
#為樣式創(chuàng)建字體
font = xlwt.Font()
font.name = 'Times New Roman'
font.bold = True
#設(shè)置樣式的字體
style.font = font
#使用樣式
sheet.write(0,1,'some bold Times text',style)
"""
#保存該excel文件,有同名文件時(shí)直接覆蓋
workbook.save('E:\\Code\\Python\\test2.xls')
print '創(chuàng)建excel文件完成！'

#coding=utf-8
#######################################################
#filename:test_xlutils.py
#author:defias
#date:xxxx-xx-xx
#function：向excel文件中寫入數(shù)據(jù)
#######################################################
import xlrd
import xlutils.copy
#打開一個(gè)workbook
rb = xlrd.open_workbook('E:\\Code\\Python\\test1.xls') 
wb = xlutils.copy.copy(rb)
#獲取sheet對(duì)象，通過sheet_by_index()獲取的sheet對(duì)象沒有write()方法
ws = wb.get_sheet(0)
#寫入數(shù)據(jù)
ws.write(1, 1, 'changed!')
#添加sheet頁
wb.add_sheet('sheetnnn2',cell_overwrite_ok=True)
#利用保存時(shí)同名覆蓋達(dá)到修改excel文件的目的,注意未被修改的內(nèi)容保持不變
wb.save('E:\\Code\\Python\\test1.xls')

#coding=utf-8
#######################################################
#filename:test_pyExcelerator_read.py
#author:defias
#date:xxxx-xx-xx
#function：讀excel文件中的數(shù)據(jù)
#######################################################
import pyExcelerator
#parse_xls返回一個(gè)列表，每項(xiàng)都是一個(gè)sheet頁的數(shù)據(jù)。
#每項(xiàng)是一個(gè)二元組(表名,單元格數(shù)據(jù))。其中單元格數(shù)據(jù)為一個(gè)字典，鍵值就是單元格的索引(i,j)。如果某個(gè)單元格無數(shù)據(jù)，那么就不存在這個(gè)值
sheets = pyExcelerator.parse_xls('E:\\Code\\Python\\testdata.xls')
print sheets

#coding=utf-8
#######################################################
#filename:test_pyExcelerator.py
#author:defias
#date:xxxx-xx-xx
#function：新建excel文件并寫入數(shù)據(jù)
#######################################################
import pyExcelerator
#創(chuàng)建workbook和sheet對(duì)象
wb = pyExcelerator.Workbook()
ws = wb.add_sheet(u'第一頁')
#設(shè)置樣式
myfont = pyExcelerator.Font()
myfont.name = u'Times New Roman'
myfont.bold = True
mystyle = pyExcelerator.XFStyle()
mystyle.font = myfont
#寫入數(shù)據(jù)，使用樣式
ws.write(0,0,u'ni hao 帕索！',mystyle)
#保存該excel文件,有同名文件時(shí)直接覆蓋
wb.save('E:\\Code\\Python\\mini.xls')
print '創(chuàng)建excel文件完成！'