Python Excel ************ Jusqu'à peu, les utilisateurs de Windows étaient favorisés car le module Pywin (qui utilise com) pouvait être utilisé pour cette tâche (voir « Python for Windows, Ressources and examples », par exemple). Rien pour les autres... Heureusement, deux modules « universels » sont sortis , xlrd pour lire les données et xlwt pour écrire des données (classeur, feuilles etc.). Pour l'installation rien de plus simple avec easy_install et pipi :: easy_install xlrd easy_install xlwt lecture d'un fichier Excel .. code-block:: python import xlrd # ouverture du fichier Excel wb = xlrd.open_workbook('testxy.xls') # feuilles dans le classeur print wb.sheet_names() [u'Feuil1', u'Feuil2', u'Feuil3'] # lecture des données dans la première feuille sh = wb.sheet_by_name(u'Feuil1') for rownum in range(sh.nrows): print sh.row_values(rownum) [u'id', u'x', u'y', u'test'] [1.0, 235.0, 424.0, u'a'] [2.0, 245.0, 444.0, u'b'] [3.0, 255.0, 464.0, u'c'] [4.0, 265.0, 484.0, u'd'] [5.0, 275.0, 504.0, u'e'] [6.0, 285.0, 524.0, u'f'] [7.0, 295.0, 544.0, u'g'] # lecture par colonne colonne1 = sh.col_values(0) print colonne1 [u'id', 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0] colonne2=sh.col_values(1) print colonne2 [u'x', 235.0, 245.0, 255.0, 265.0, 275.0, 285.0, 295.0] # extraction d'un élément particulier print colonne1[1],colonne2[1] 1.0 235.0 création d'un fichier Excel .. code-block:: python from xlwt import Workbook # création test = Workbook() # création de la feuille 1 feuil1 = book.add_sheet('feuille 1') # ajout des en-têtes feuil1.write(0,0,'id') feuil1.write(0,1,'x') feuil1.write(0,2,'y') feuil1.write(0,3,'test') # ajout des valeurs dans la ligne suivante ligne1 = feuil1.row(1) ligne1.write(0,'1') ligne1.write(1,'235.0') ligne1.write(2,'424.0') ligne1.write(3,'a') etc... # ajustement éventuel de la largeur d'une colonne feuil1.col(0).width = 10000 # éventuellement ajout d'une autre feuille 2 feuil2 = book.add_sheet('feuille 2') etc... # création matérielle du fichier résultant test.save('monsimple.xls') .. attention:: pour avoir un fichier excel en latin il faut faire book = Workbook(encoding='latin-1') un très bonne doc sur http://www.simplistix.co.uk/presentations/python-excel.pdf un exemple complet de génération d'un fichier excel multi-feuilles à partir d'extraction excel. le fichier excel est zippé puis envoyé par mail a noté que les feuilles possèdent une entête coloré .. code-block:: python import sys import os, os.path import datetime import time import zipfile import smtplib from email.mime.multipart import MIMEMultipart from email.mime.text import MIMEText from email.MIMEBase import MIMEBase from email.Utils import COMMASPACE, formatdate from email import Encoders import pyodbc from xlwt import Workbook, easyxf MAIL_FROM='MYSOCIETY' MAIL_FOR='c.barre@myprop-group.com' # PZ.SupplyChain.Achat@myprop-group.com' MAIL_SERVER='srvxxxx1' MAIL_SUBJECT='Analyse Des Manquants' MAIL_HTML="""
Vous pouvez trouver en piece jointe l'analyse de l'evolution du stock """ SQL_CONNEXION_PRECIX = 'DSN=NEW_MECA_J-1;UID=informix;PWD=*******' QUERY1 = "select * from tutu" QUERY2 = "select * from tutu1" QUERY3 = "select * from tutu2" format_cell = easyxf( 'font: name Arial, color black;' 'align: horizontal center, vertical center;' 'pattern: pattern solid, fore_colour white;' 'border: left thin, top thin, right thin, bottom thin') format_title_col = easyxf( 'font: name Arial, color black;' 'align: horizontal center, vertical center;' 'pattern: pattern solid, fore_colour light_blue;' 'border: left thin, top thin, right thin, bottom thin') def optimizetable(table = None, optimize = False): if optimize == True: k,l = 0,0 for k in range(0, len(table)): for l in range(0,len(table[k])): try: table[k][l] = str(table[k][l]).strip() except Exception as e: #manage unicode on sqlserver table[k][l] = "'".join(repr(table[k][l]).split("'")[1:-1]).strip() return table def add_sheet(book, name, results): sheet = book.add_sheet(name) results = optimizetable(results, True) ln = [max(len(str(x)) for x in line) for line in zip(*results)] for i in range(0,len(ln)): sheet.col(i).width = 350 * ln[i] #size 350 by caractere format = format_title_col col, lig = 0, 0 for i in results: for j in i: sheet.write(lig, col, str(j).strip(), format) col = col +1 col = 0 lig = lig + 1 format = format_cell def send_mail(type='html', send_from="", send_to="", subject="", text="", server="srvxxxx1", password="", fil=[]): msg = MIMEMultipart() msg['Subject'] = subject msg['From'] = send_from msg['To'] = send_to if type == 'html': part = MIMEText(text, 'html') else: part = MIMEText(text, 'plain') msg.attach(part) for f in fil: print os.path.basename(f) part = MIMEBase('application', "octet-stream") part.set_payload( open(f,"rb").read() ) Encoders.encode_base64(part) part.add_header('Content-Disposition', 'attachment; filename="%s"' % os.path.basename(f)) msg.attach(part) smtp = smtplib.SMTP(server) if password != "": smtp.login(send_from, password) smtp.sendmail(send_from, send_to, msg.as_string()) smtp.close() def send_mail_html(send_from="", send_to="", subject="", text="", server="srvxxxx1", password="", fil=[]): send_mail('html', send_from, send_to, subject, text, server, password, fil) book = Workbook(encoding='latin-1') _cursor_precix.execute(QUERY1) _rows = _cursor_precix.fetchall() _rows.insert(0,[i[0] for i in _cursor_precix.description]) add_sheet(book,'PBM DU JOUR', _rows) _cursor_precix.execute(QUERY2) _rows = _cursor_precix.fetchall() _rows.insert(0,[i[0] for i in _cursor_precix.description]) add_sheet(book,'PBM DU JOUR2', _rows) _cursor_precix.execute(QUERY3) _rows = _cursor_precix.fetchall() _rows.insert(0,[i[0] for i in _cursor_precix.description]) add_sheet(book,'PBM DU JOUR3', _rows) now = datetime.datetime.now() xls_file = now.strftime("%Y%m%d")+".xls" zip_file = now.strftime("%Y%m%d")+".zip" book.save(xls_file) myzip = zipfile.ZipFile(zip_file, 'w', zipfile.ZIP_DEFLATED) myzip.write(xls_file) myzip.close() send_mail_html(send_from = MAIL_FROM, send_to = MAIL_FOR, subject = MAIL_SUBJECT , text = MAIL_HTML, server = MAIL_SERVER, fil=[zip_file] ) os.remove(zip_file) os.remove(xls_file) programme batch qui réalise en plus * une coloration une ligne sur 2 * gestion de l'encoding des lignes .. code-block:: python #!/usr/bin/env python import glob from optparse import OptionParser import sys import os, os.path import traceback, sys import datetime import time import pyodbc from xlwt import Workbook, easyxf import mysociety VERSION="0.1" PROG="precixmailartdot" DESCRIPTION="generation mxls pour les articles DOT" AUTHOR="Service Informatique Mysociety" MAIL_FROM='MYSOCIETY' MAIL_FOR='f.aoustin@myprop-group.com' MAIL_SERVER='srvxxxx1' MAIL_SUBJECT='Analyse Article en Dotation' MAIL_HTML="""
Vous pouvez trouver en piece jointe l'analyse des articles en dotation """ SQL_CONNEXION_PRECIX = 'DSN=NEW_MECA;UID=informix;PWD=yalnodb1u' SQL_SELECT_ART_DOT=""" SELECT BAS_ART.NO_ART AS NO_ART, BAS_ART.DESIGN1 AS DESIGNATION, STK.QTE AS STOCK, BAS_ART.STK_ALERTE AS STOCK_ALERTE, ZZ_ARTCNS.CNS9 AS QTE_BY_BAC, BAS_ART.QTE_ECO AS QTE_ECO, BAS_ART.DELAI_APPR AS DELAI_APPRO, BAS_ART.NO_FRNPRIN AS NO_FOURNISSEUR, BAS_FRN.RAIS_SOC AS FOURNISSEUR, BAS_ART.CONSOM_MOY AS CONSOMMATION_MOYENNE FROM BAS_ART LEFT OUTER JOIN (SELECT bas_stocklig.no_art, SUM(bas_stocklig.condit * bas_stocklig.qte_stk) AS QTE FROM bas_stocklig, bas_stockent WHERE bas_stocklig.no_lieu=bas_stockent.no_lieu AND bas_stocklig.no_art=bas_stockent.no_art AND bas_stockent.dispo = 1 GROUP BY 1) AS STK ON STK.NO_ART = BAS_ART.NO_ART, BAS_FRN, ZZ_ARTCNS WHERE BAS_FRN.NO_FRN = BAS_ART.NO_FRNPRIN AND ZZ_ARTCNS.NO_ART = BAS_ART.NO_ART AND BAS_ART.CNS3=1 """ format_cell_noeven = easyxf( 'font: name Consolas, color black;' 'align: horizontal center, vertical center, shrink_to_fit true;' 'pattern: pattern solid, fore_colour gray25;' 'border: left no_line, top no_line, right no_line, bottom no_line') format_cell_noeven_red = easyxf( 'font: name Consolas, color red;' 'align: horizontal center, vertical center, shrink_to_fit true;' 'pattern: pattern solid, fore_colour gray25;' 'border: left no_line, top no_line, right no_line, bottom no_line') format_cell_even = easyxf( 'font: name Consolas, color black;' 'align: horizontal center, vertical center, shrink_to_fit true;' 'pattern: pattern solid, fore_colour white;' 'border: left no_line, top no_line, right no_line, bottom no_line') format_cell_even_red = easyxf( 'font: name Consolas, color red;' 'align: horizontal center, vertical center, shrink_to_fit true;' 'pattern: pattern solid, fore_colour white;' 'border: left no_line, top no_line, right no_line, bottom no_line') format_title_col = easyxf( 'font: name Consolas, color black;' 'align: horizontal center, vertical center, shrink_to_fit true;' 'pattern: pattern solid, fore_colour light_blue;' 'border: left no_line, top no_line, right no_line, bottom no_line') def decodeRows(rows, coding='latin-1'): new_rows = [] for row in rows: new_row = [] for col in row: try: new_row.append(col.decode(coding)) except: new_row.append(col) new_rows.append(new_row) return new_rows def isEven(number): if number%2==0: return True else: return False def optimizetable(table = None, optimize = False): if optimize == True: k,l = 0,0 for k in range(0, len(table)): for l in range(0,len(table[k])): try: table[k][l] = str(table[k][l]).strip() except Exception as e: #manage unicode on sqlserver table[k][l] = "'".join(repr(table[k][l]).split("'")[1:-1]).strip() return table def add_sheet(book= None, name='', results=[], color= False): # si color = true on utilise le format red si derniere colonne = 1 sheet = book.add_sheet(name) results = optimizetable(results, True) ln = [max(len(str(x)) for x in line) for line in zip(*results)] for i in range(0,len(ln)): sheet.col(i).width = 285 * ln[i] #size 350 by caractere #treat header format = format_title_col col = 0 for j in results[0]: sheet.write(0, col, str(j).strip(), format) col = col +1 #treat body col, lig = 0, 1 for i in results[1:]: if color == True and int(i[-1]) == 1: if isEven(lig): format = format_cell_even_red else: format = format_cell_noeven_red else: if isEven(lig): format = format_cell_even else: format = format_cell_noeven for j in i: sheet.write(lig, col, str(j).strip(), format) col = col +1 col = 0 lig = lig + 1 if __name__ == '__main__': parser = OptionParser(version="%s %s" % (PROG,VERSION)) parser.description= DESCRIPTION parser.epilog = AUTHOR parser.add_option("-l", "--level-log", dest="loglevel", help ="niveau de log (INFO, DEBUG, CRITICAL, WARNING, ERROR default: INFO)", default="INFO", type="string") parser.add_option("-p", "--path-log", dest="logpath", help ="fichier de log(defaut: log.txt)", default="log.txt", type="string") parser.add_option("-s", "--size-log", dest="logsize", help ="taille des fichiers log (Bytes, default: 1048576 )", default=1048576 , type="int") parser.add_option("-c", "--count-log", dest="logcnt", help ="nombre de fichier log (default: 5)", default=5, type="int") (options, args) = parser.parse_args() try: if options.loglevel not in mysociety.LEVEL.keys(): parser.print_help() sys.exit(1) my_logger = mysociety.Logger(options.logpath, options.logsize, options.logcnt, options.loglevel) my_logger.info("START PROGRAM %s VERSION %s" % (PROG, VERSION)) last_sql = '' try: my_logger.debug("connexion with PRECIX") _connect_precix = pyodbc.connect(SQL_CONNEXION_PRECIX) col = 0 row = 0 format_title_col = easyxf( 'font: name Arial, color black;' 'align: horizontal center, vertical center;' 'pattern: pattern solid, fore_colour light_blue;' 'border: left thin, top thin, right thin, bottom thin') format_cell = easyxf( 'font: name Arial, color black;' 'align: horizontal center, vertical center;' 'pattern: pattern solid, fore_colour white;' 'border: left thin, top thin, right thin, bottom thin') book = Workbook() my_logger.info("load sql SQL_SELECT_ART_DOT") _cursor = _connect_precix.cursor() _cursor.execute(SQL_SELECT_ART_DOT) _rows = _cursor.fetchall() my_logger.info("generate xsl file") _rows.insert(0,[i[0] for i in _cursor.description]) add_sheet(book,'Articles Dotation', decodeRows(_rows), False) now = datetime.datetime.now() name_file = now.strftime("%Y%m%d")+"_ArtDot.xls" book.save(name_file) _connect_precix.close() my_logger.info("send mail") mysociety.send_mail_html(send_from = MAIL_FROM, send_to = MAIL_FOR, subject = MAIL_SUBJECT , text = MAIL_HTML, server = MAIL_SERVER, fil=[name_file] ) os.remove(name_file) except Exception as e: my_logger.error(str(e)) exc_type, exc_value, exc_traceback = sys.exc_info() formatted_lines = traceback.format_exc().splitlines() print ('\n'.join(formatted_lines)) my_logger.info("END PROGRAM %s VERSION %s" % (PROG, VERSION)) except Exception, e: print parser.error(e) parser.print_help() sys.exit(1)