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
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
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é
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="""
<body style="font: 12px Trebuchet MS, helvetica, sans-serif ;">
<br> 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
#!/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="""
<body style="font: 12px Trebuchet MS, helvetica, sans-serif ;">
<br> 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)