python-xls实用操作.py

?#将文件名重命名为xls.py
# Write an XLS file with a single worksheet, containing
# a heading row and some rows of data.
import xlwt
import pdb
from xlutils.copy import copy
from xlrd import open_workbook
import sys,logging
from datetime import timedelta,date
from utils import in_tst_output_dir

#测试XLS颜色值
filename = 'TestData2.xls' #检测当前目录下是否有TestData2.xls文件，如果有则清除以前保存文件
if os.path.exists(filename):
os.remove(filename)
wbk = Workbook(encoding='utf-8')
sheet = wbk.add_sheet('new sheet 1', cell_overwrite_ok=True)
style = XFStyle() #赋值style为XFStyle()，初始化样式
for i in range(0x00,0xff): # 设置单元格内字体样式
fnt = Font() # 创建一个文本格式，包括字体、字号和颜色样式特性
= u'微软雅黑' # 设置其字体为微软雅黑
fnt.colour_index = i # 设置其字体颜色
fnt.bold = True
style.font = fnt #将赋值好的模式参数导入Style
sheet.write_merge(i,i,3,5,Line_data,style) #以合并单元格形式写入数据，即将数据写入以第4/5/6列合并德单元格内
wbk.save('TestData2.xls') #保存TestData2.xls文件，保存到脚本或exe文件运行的目录下
class DoXls():
def __init__(self,filename,t,c):
self.ezxf = xlwt.easyxf
self.filename=filename
self.title=t
self.content=c
self.heading_xf=self.ezxf()
self.create_xls()

def create_xls(self):
#(self.content)
data=[list(i) for i in self.content]
#data.insert(0,title)
kinds = ['text', 'text','text']
#内容格式
kind_to_xf_map = {
'date': self.ezxf(num_format_str='yyyy-mm-dd'),
'int': self.ezxf(num_format_str='#,##0'),
'money': self.ezxf(num_format_str='$#,##0.00'),
'price': self.ezxf(num_format_str='#0.000000'),
'text': self.ezxf()
}
data_xfs = [kind_to_xf_map[k] for k in kinds]
self.write_xls(in_tst_output_dir('%s.xls'%self.filename),self.filename, self.title, data, self.heading_xf, data_xfs)


def write_xls(self,file_name, sheet_name, headings, data, heading_xf, data_xfs):
try:
workbook = open_workbook(file_name)
book = copy(workbook)
sheet = book.get_sheet(0)
rowx=len(sheet.rows)-1
except:
book = xlwt.Workbook()
sheet = book.add_sheet(sheet_name)
rowx = 0
self._create_workbook(sheet,hea

dings,heading_xf)
#sheet = book.add_sheet("2016-06-05")
#通过get_sheet()获取的sheet有write()方法
#for colx, value in enumerate(headings):
#sheet.write(rowx, colx, value, heading_xf)
sheet.set_panes_frozen(True) # frozen headings instead of split panes
sheet.set_horz_split_pos(1) # in general, freeze after last heading row
sheet.set_remove_splits(True) # if user does unfreeze, don't leave a split there
for row in data:
rowx += 1
for colx, value in enumerate(row):
sheet.write(rowx, colx, value, data_xfs[colx])
book.save(file_name)
def _create_workbook(self,sheet,headings,heading_xf):
for colx, value in enumerate(headings):
sheet.write(0, colx, value, heading_xf)
return
if __name__ == '__main__':
temp=DoXls('文件名','标题','内容')