# coding=utf-8
'''
Created on 2017年3月2日
@author: chenkai
'''
import xlrd #读取excel
data =xlrd.open_workbook("F:\\test.xlsx")
table=data.sheets()[0]
nrows=table.nrows#行数
ncols=table.ncols#列数
print "行数: ",nrows,"列数: " ,ncols
for i in range(0,nrows):
print "--------------------第",i,"行"
rowValues=table.row_values(i)#某一行数据
for item in rowValues:
print item
'''
编写excel
'''
import xlwt
'''
往EXCEl单元格写内容,每次写一行sheet:页签名称;rowValueList:行内容列表;rowIndex:行索引;
isBold:true:粗字段,false:普通字体
'''
def WriteSheetRow(sheet, rowValueList, rowIndex, isBold):
i = 0
#style = xlwt.easyxf('font: bold 1')
style = xlwt.easyxf('font: bold 0, color red; pattern: pattern solid, fore_colour yellow;')#红色字体
'''
# style2 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow; font: bold on;')
# 设置Excel单元格的背景色为黄色,字体为粗体
'''
for svalue in rowValueList:
strValue = unicode(str(svalue), 'utf-8')
if isBold:
sheet.write(rowIndex, i, strValue, style)
else:
sheet.write(rowIndex, i, strValue,style)
i = i + 1
'''写excel文件'''
def save_Excel(strFile):
excelFile = unicode(strFile, "utf8")
wbk = xlwt.Workbook()
sheet = wbk.add_sheet('sheet1', cell_overwrite_ok=True)
headList = ['标题1', '标题2', '标题3', '标题4', '总计']
rowIndex = 0
WriteSheetRow(sheet, headList, rowIndex, True)
for i in xrange(1, 11):
rowIndex = rowIndex + 1
valueList = []
for j in xrange(1, 5):
valueList.append(j * i)
WriteSheetRow(sheet, valueList, rowIndex, False)
wbk.save(excelFile)
save_Excel("F:\\test2.xls")
'''
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
'''
'''
Created on 2017年3月2日
@author: chenkai
'''
import xlrd #读取excel
data =xlrd.open_workbook("F:\\test.xlsx")
table=data.sheets()[0]
nrows=table.nrows#行数
ncols=table.ncols#列数
print "行数: ",nrows,"列数: " ,ncols
for i in range(0,nrows):
print "--------------------第",i,"行"
rowValues=table.row_values(i)#某一行数据
for item in rowValues:
print item
'''
编写excel
'''
import xlwt
'''
往EXCEl单元格写内容,每次写一行sheet:页签名称;rowValueList:行内容列表;rowIndex:行索引;
isBold:true:粗字段,false:普通字体
'''
def WriteSheetRow(sheet, rowValueList, rowIndex, isBold):
i = 0
#style = xlwt.easyxf('font: bold 1')
style = xlwt.easyxf('font: bold 0, color red; pattern: pattern solid, fore_colour yellow;')#红色字体
'''
# style2 = xlwt.easyxf('pattern: pattern solid, fore_colour yellow; font: bold on;')
# 设置Excel单元格的背景色为黄色,字体为粗体
'''
for svalue in rowValueList:
strValue = unicode(str(svalue), 'utf-8')
if isBold:
sheet.write(rowIndex, i, strValue, style)
else:
sheet.write(rowIndex, i, strValue,style)
i = i + 1
'''写excel文件'''
def save_Excel(strFile):
excelFile = unicode(strFile, "utf8")
wbk = xlwt.Workbook()
sheet = wbk.add_sheet('sheet1', cell_overwrite_ok=True)
headList = ['标题1', '标题2', '标题3', '标题4', '总计']
rowIndex = 0
WriteSheetRow(sheet, headList, rowIndex, True)
for i in xrange(1, 11):
rowIndex = rowIndex + 1
valueList = []
for j in xrange(1, 5):
valueList.append(j * i)
WriteSheetRow(sheet, valueList, rowIndex, False)
wbk.save(excelFile)
save_Excel("F:\\test2.xls")
'''
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
'''