python操作excel

今天练习了2个excel相关的脚本，一个是excel拆分，另外一个是执行动态sql查询出结果后，输出到excel，在此总结下本次练习一、excel拆分：将一个大的excel表格拆分成若干个小表，根据某一列作为拆分关键列进行拆分。联系中主要遇到以下个问题：1、刚开始时，使用xlwt模块的write方法，在写了若干数据后，总是有部分单元格数据丢失，在打开生成的excel的时候报错“发现不可读取内容”。一直无法解决。2、后来使用了xlsxwriter模块，输出 2007版本的excel则为遇到此问题3、xlsxwriter模块只能输出高版本的excel，这是个遗憾的地方。代码记录如下，以备以后查看：

#!/usr/bin/env python#coding=utf-8#pip3 install xlsxwriter，自动下载安装 xlsxwriter 模块import xlrdimport xlwtfrom openpyxl import Workbookfrom openpyxl import load_workbookimport sysimport xlsxwriter data = xlrd.open_workbook('年内分配量问题-全省20161217排序.xlsx') # 打开xls文件table = data.sheets()[0] # 打开第一张表#workbook = xlwt.Workbook() #注意Workbook的开头W要大写#sheet1 = workbook.add_sheet('sheet1') numRow = table.nrows # 获取表的行数 F_NAME=''start_row=0print(numRow)for i in range(1,1000):  #跳过表头    #print(i)    row_value=table.row_values(i)    #print('数据：')    #print(row_value)    numCol=len(row_value)    if F_NAME!=row_value[8] and row_value[8]!='':   #对拆分关键列先排序，关键列发生变化，则新建一个表格        F_NAME=row_value[8]        print(F_NAME)        workbook2007 = xlsxwriter.Workbook(F_NAME+'.xlsx')        worksheet2007 = workbook2007.add_worksheet()        #写表头        head=table.row_values(0)        #print('表头：')        #print(head)        numCol=len(head)        for j in range(numCol):          worksheet2007.write(0,j,head[j])        start_row=1         #重新从第2行开始写    else:        start_row=start_row+1        #print(start_row,END=',')    for j in range(numCol):        pass        worksheet2007.write(start_row,j,row_value[j])workbook2007.close()print ('excel创建完成') 

二、执行动态sql并输出结果到excel
1、在从存储过程中将动态sql拼接转化成python字符串时，需要做一些修改，其实在python中思路更加简单些，直接将需要行转列的关键值做一个循环即可得到动态的列名。
2、将原来的sql语句粘贴到python中时，使用三个双引号作为字符串的封闭字符，省去很多转意和换行的麻烦。
3、依然使用xlsxwriter模块输出2007版本excel
4、出现中文乱码，在写入前，将内容转换成字符串并进行编码转换，将问题解决。str(value).encode('latin-1').decode('gbk')
5、从数据库中查询出来的null值，在python中会被转换成None，需要进行判断，并置为''。

代码记录如下，以备以后查看：

#!E:\Python34#coding=utf-8#coding=utf8 import smtplib,sysimport os,sys,xdrlib import xlrdimport pymssqlimport xlwtimport xlsxwriterfrom email.mime.text import MIMEText  from email.header import Headerconn = pymssql.connect(server="10.44.31.68",port="1433",user="wtgd_yw",password="wtgdyw@2015", database="WATER_GD",charset="UTF-8")cursor = conn.cursor()head_str=['ID_','分析结果','数据来源2','数据来源','类型','mp_cd','mp_nm','最新数据日期']print(head_str)sql_str="""select   distinct  '['+CONVERT(varchar(10),tm,120) +']' as TM from DSE_WR_W_HOUR  where TM >=GETDATE()-10  and TM <convert(varchar(10),GETDATE(),120)"""#print(sql_str)str_col=''#sql_str='select GETDATE()'cursor.execute(sql_str)days=cursor.fetchall()n=len(days)print(days)print(n)for i in range(len(days)):    if str_col=='':        head_str.append(str(days[i][0]))        str_col=str(days[i][0])            else:        head_str.append(str(days[i][0]))        str_col=str_col+','+str(days[i][0])print(str_col)print(head_str)print('-----')sql_str="""select row_number() over(order by case 分析结果 when '长期掉线' then 0 when '掉线——' then 1 when '正常——' then 2 else 999 end,CASE [数据来源] WHEN '新建' THEN '做做' WHEN '省管' THEN '做' ELSE [数据来源] END,case when  最新数据日期 is null then '0' else 最新数据日期 end,mp_cd) AS ID_,w.* from (select -- row_number() over(order by case when t.mx_tm is  null then 0 case else 1 end ,case when t.mp_cd2 is  null then 0 else 1 end,data_source_type , mx_tm,t.mp_cd) as ID_, LEFT(case when    mx_tm < GETDATE()-7 or mx_tm is null then '长期掉线' when  mx_tm >=getdate()-1 then '正常' WHEN   mx_tm <getdate()-1 THEN '掉线' WHEN  MP_CD2 IS not NULL THEN '未上报' else NULL end +'————',4) 分析结果  ,case data_source_type when '0' then '新建' when '1'  then '省管' when '2' THEN '接入' ELSE data_source_type END AS 数据来源2,REPLACE(BUILD_TYPE_DESC,'接','') as 数据来源, case mon_g when '1' then '国控' else '非国控' end as 类型,t.mp_cd,mp_nm,mx_tm as 最新数据日期,"""+str_col+"""from (select a.mp_cd ,a.mp_nm,C.mx_tm,a.flag ,a.data_source_type,a.BUILD_TYPE_DESC,a.mon_g,B.*  from   DSE_MP_REPORT_FILTER a   LEFT JOIN (select MP_CD AS MP_CD_MX, convert(varchar(16),max(tm),120 ) mx_tm from DSE_WR_W_HOUR group by MP_CD) C   ON A.MP_CD =C.MP_CD_MX   LEFT JOIN   (select * from (select MP_CD AS MP_CD2,CONVERT(varchar(10),tm,120)  as   TM,SUM(HOUR_W) AS DAY_W from DSE_WR_W_HOUR  where TM >=GETDATE()-10  GROUP BY MP_CD,CONVERT(varchar(10),tm,120)) tb pivot (MAX(DAY_W) for tm in ("""+str_col+""") ) W) b on a.mp_cd=b.MP_CD2   where a.GW_BD_FLG='1') t where  1=1 --and mx_tm is not null --and flag<>0 --or MP_CD2  is not null ) w"""print(sql_str)cursor.execute(sql_str)sql_result=cursor.fetchall()#print(str(sql_result).encode('latin-1').decode('gbk'))workbook2007 = xlsxwriter.Workbook('000_1.xlsx')worksheet2007 = workbook2007.add_worksheet()top = workbook2007.add_format({'border':1,'bold':True})other = workbook2007.add_format({'border':1})format = top#写表头num_cols=len(head_str)for j in range(num_cols):    value=head_str[j]    if value is not None:   #将None转换成空白        pass        #if isinstance(value, str): #判断是否为字符串，如果是则进行中文解码        #    value=str(value).encode('latin-1').decode('gbk')    else:        value=''        #print('转换value')        #print(value)    worksheet2007.write(0,j,value,top)#写内容num_rows=len(sql_result)print(num_rows)for i in range(num_rows):    value_row=sql_result[i]    num_cols=len(value_row)    for j in range(num_cols):        value=value_row[j]                if value is not None:   #将None转换成空白            if isinstance(value, str): #判断是否为字符串，如果是则进行中文解码                value=str(value_row[j]).encode('latin-1').decode('gbk')        else:            value=''            #print('转换value')        #print(value)        worksheet2007.write(i+1,j,value,other)  ##i+1，从第二行开始写workbook2007.close()"""row=1j=1wbk =xlwt.Workbook(encoding = 'utf-8')sheet=wbk.add_sheet('sheet 1')sheet.write(0,0,'company')sheet.write(0,1,'city')#conn=MySQLdb.connect(host='XXX',user='XXX',passwd='XXX',db='gh')#cursor=conn.cursor()#cursor.execute('select mp_cd,convert(nvarchar(200), mp_nm)  from DSE_MP_REPORT_FILTER')cursor.execute("select mp_cd,   aa   from DSE_MP_REPORT_FILTER  order by mp_cd ")sql_col=['mp_cd','mp_nm']print (sql_col)for  mp_cd,mp_nm  in cursor.fetchall():    sheet.write(row,0,mp_cd)    sheet.write(row,1,mp_nm)    row+=1    #if row%100==99:            #   object_nm.save('D:\\WORK\PYScript\\data1\\aa'+'%d' %j+'.xls')     #   object_nm='wbk'+'%d' %j     #   object_nm =xlwt.Workbook(encoding = 'utf-8')     #   sheet=object_nm.add_sheet('sheet 1')      #  sheet.write(0,0,'company')     #   sheet.write(0,1,'city')     #   j+=1    #print(row%100)wbk.save('D:\\WORK\PYScript\\aa.xls') print(sql_col[1])cursor.close()"""