Pythontoexcel

luguozheng

# -*- coding: utf-8 -*-
#导入xlwt模块
import xlwt
import xlrd
import numpy as np
def def_style():
    style = xlwt.XFStyle()

##########这部分设置字体#########
    font = xlwt.Font()
    font.name = 'Times New Roman' #或者换成外面传进来的参数，这样可以使一个函数定义所有style
    font.bold = 'True'
    font.height = 220
    font.colour_index=0
    style.font = font

########这部分设置居中格式#######
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER    #水平居中
    alignment.vert = xlwt.Alignment.VERT_CENTER    #垂直居中
    style.alignment = alignment

#########还可以添加几个设置颜色，边框的部分##########

    return style
#
#
# # 创建一个Workbook对象，这就相当于创建了一个Excel文件
# book = xlwt.Workbook(encoding='utf-8', style_compression=0)
#
# sheet1 = book.add_sheet(u'sheet1',cell_overwrite_ok=True) #创建sheet
#
# first_col=sheet1.col(0)
# sec_col=sheet1.col(1)
# first_col.width=256*20
#
# row0 = [u'序号',u'市（县）名',u'个数',u'位置']
#
# #生成第一行  
# for i in range(0,len(row0)):  
#   sheet1.write(0,i,row0[i])
#   
#
# sheet1.write(1,2,3)
#   
# sheet1.write_merge(1,3,1,1,"中国",def_style()) #第一列
#     
#   
# # 最后，将以上操作保存到指定的Excel文件中
# book.save(r'e:\lgz13.xls')  # 在字符串前加r，声明为raw字符串，这样就不会处理其中的转义了。否则，可能会报错


# 创建excel工作表
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet('sheet1')

first_col=worksheet.col(3)
sec_col=worksheet.col(1)
first_col.width=256*20

row0 = [u'序号',u'市（县）名',u'个数',u'位置']

#生成第一行  
for i in range(0,len(row0)):  
  worksheet.write(0,i,row0[i],def_style())


# 设置表头
# worksheet.write(0, 0, label='序号')
# worksheet.write(0, 1, label='市县名')
# worksheet.write(0, 2, label='个数')
# worksheet.write(0, 3, label='位置')


# 读取json文件
data= [{"序号": 1,"市县名": "青藏县","个数": 3,"位置": [[10.2,10.3],[10.5,10,6],[10.7,10,8]]},{"序号": 2,"市县名": "和田县","个数": 2,"位置": [[8.2,8.3],[9.5,9,6]]},{"序号": 3,"市县名": "国家县","个数": 3,"位置": [[10.3,10.4],[8.5,8,6],[5.7,5,8]]}]

# 将json字典写入excel
# 变量用来循环时控制写入单元格，感觉有更好的表达方式
val1 = 1
val2 = 1
val3 = 1
val4 = 1
k=0
m=0
intarr=np.zeros(3)
for list_item in data:
for key, value in list_item.items():
  if key == "个数":
    intarr[k]=value
    k += 1
  else:
   pass


for list_item in data:
for key, value in list_item.items():  
  if key == "序号":
   worksheet.write_merge(val1,val1+int(intarr[m])-1,0,0,value,def_style())
   val1 = val1+int(intarr[m])
#    worksheet.write(val1, 0, value)
#    val1 += 1
  elif key == "市县名":
   worksheet.write_merge(val2,val2+int(intarr[m])-1,1,1,value,def_style())
   val2 = val2+int(intarr[m])
#    worksheet.write(val2, 1, value)
#    val2 += 1
  elif key == "个数":
   worksheet.write_merge(val3,val3+int(intarr[m])-1,2,2,value,def_style())
   val3 = val3+int(intarr[m])
#    worksheet.write(val3, 2, value)
#    val3 += 1
  elif key == "位置":
   for i in range(0,len(value)):   
       print str(value[i])
       worksheet.write(val4, 3, str(value[i]),def_style())
       val4 += 1
  else:
   pass
m += 1

# 保存
workbook.save(r'e:\lgz11.xls')