Récemment on m’a demandé d’envoyer toutes les semaines par mail un rapport qui existait déjà dans notre report center MOSS.
Afin de ne pas avoir à tout refaire et d’utiliser le rapport existant, j’ai développé un petit programme bash qui effectue l’extraction d’un snapshost du rapport, cache les feuilles excel qui ne doivent pas figurer, effectue un export en PDF et l’envoie par mail. Il n’y plus qu’ensuite à programmer l’exe via le gestionnaire de taches windows.
Prérequis pour faire fonctionner le programme : avoir Excel 2007 installé et sans le SP2, il faut aussi le complément pour exporter en PDF installé.
La conversion d’un fichier Excel en PDF est bien documenté sur msdn, la récupération du fichier excel utilise un des nombreux webservices présents dans MOSS (http://serveur-moss07/_vti_bin/excelservice.asmx) que j’ai ajouté comme référence web à mon projet.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Services.Protocols;
using sendMossExcel.ExcelWebService;
using System.IO;
using System.Net.Mail;
using Microsoft.Office.Interop.Excel;
namespace sendMossExcel
{
class Program
{
static void Main(string[] args)
{
try
{
if (args.Length < 1)
{
Console.Error.WriteLine("Command line arguments should be: [workbook_path]");
return;
}
String tempXlsxName = System.IO.Path.GetTempFileName();
String tempPdfName = System.IO.Path.GetTempFileName();
//recuperation via webservice
byte [] workbook = retrieveWorkBook(args[0]);
//enregistrement
writeTempory(workbook, tempXlsxName);
customAndExport(tempXlsxName, tempPdfName);
MailMessage mail = new MailMessage(sendMossExcel.Properties.Settings.Default.mailFrom, sendMossExcel.Properties.Settings.Default.mailTo);
mail.Subject = sendMossExcel.Properties.Settings.Default.mailSubject;
mail.IsBodyHtml = true;
mail.Body = sendMossExcel.Properties.Settings.Default.mailBody;
mail.Attachments.Add(new Attachment(File.Open(tempXlsxName,FileMode.Open), "report.xlsx","Microsoft/Excel"));
mail.Attachments.Add(new Attachment(File.Open(tempPdfName+".pdf", FileMode.Open), "report.pdf", "Adobe/PDF"));
SmtpClient smtpClient = new SmtpClient();
smtpClient.Host = sendMossExcel.Properties.Settings.Default.mailSmtpServer;
smtpClient.Send(mail);
mail.Dispose();
File.Delete(tempPdfName + ".pdf");
File.Delete(tempPdfName);
File.Delete(tempXlsxName);
Console.WriteLine("Traitement Termine Correctement");
}
catch (SoapException e)
{
Console.WriteLine("SOAP Exception Message: {0}", e.Message);
}
catch (Exception e)
{
Console.WriteLine("Exception Message: {0}", e.Message);
Console.WriteLine(e.Data);
Console.WriteLine(e.StackTrace);
}
}
static byte[] retrieveWorkBook(string Uri)
{
// Instantiate the Web service and
// create a status array object.
ExcelServiceSoapClient xlService = new ExcelServiceSoapClient();
Status[] status;
xlService.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
// Open the workbook, then call GetWorkbook
// and close the session.
string sessionId = xlService.OpenWorkbook(Uri, "en-US", "en-US", out status);
xlService.Refresh(sessionId, "");
byte[] workbook = xlService.GetWorkbook(sessionId, WorkbookType.PublishedItemsSnapshot, out status);
// byte[] workbook = xlService.GetWorkbook(sessionId, WorkbookType.FullWorkbook, out status);
// byte[] workbook = xlService.GetWorkbook(sessionId, WorkbookType.FullSnapshot, out status);
// Close the workbook. This also closes the session.
status = xlService.CloseWorkbook(sessionId);
return workbook;
}
static void writeTempory(byte[] fileBytes, string fileName)
{
BinaryWriter binaryWriter = new BinaryWriter(File.Open(fileName, FileMode.OpenOrCreate));
binaryWriter.Write(fileBytes);
binaryWriter.Close();
}
static void customAndExport(string xlsxFileName, string pdfFileName)
{
object paramMissing = System.Type.Missing;
//ouverture excel et fichier
ApplicationClass excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
Workbook excelWorkBook = excelApplication.Workbooks.Open(xlsxFileName, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
//custo
for (int i = 2; i < 5; i++)
{
((Worksheet)excelWorkBook.Worksheets.get_Item(i)).Visible = XlSheetVisibility.xlSheetVeryHidden;
}
for (int i = 6; i < 10; i++)
{
((Worksheet)excelWorkBook.Worksheets.get_Item(i)).Visible = XlSheetVisibility.xlSheetVeryHidden;
}
((Worksheet)excelWorkBook.Worksheets.get_Item(1)).PageSetup.Orientation = XlPageOrientation.xlLandscape;
((Worksheet)excelWorkBook.Worksheets.get_Item(5)).PageSetup.Orientation = XlPageOrientation.xlLandscape;
//export
excelWorkBook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, pdfFileName, XlFixedFormatQuality.xlQualityStandard, true, false, paramMissing, paramMissing, false, paramMissing);
//fermeture
excelWorkBook.Save();
excelWorkBook.Close(false, paramMissing, paramMissing);
excelApplication.Quit();
}
}
}
