public void exec(System.Data.DataTable dt)
{
System.IO.File.Copy(template, copie, true);
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(copie, true);
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
WorksheetPart wsPart = XcelWin.getWorksheetPartByName(myWorkbook, "Feuil1");
//DocumentFormat.OpenXml.Spreadsheet.Columns columns = new Columns();
//for(int i=0;i<size.Length;i++)
//{
// DocumentFormat.OpenXml.Spreadsheet.Column c = new Column();
// c.CustomWidth = true;
// c.Min = (uint) i+1;
// c.Max = (uint) i+1;
// c.Width = size[i];
// columns.Append(c);
//}
//DocumentFormat.OpenXml.Spreadsheet.Columns cc = new Columns();
//cc.Append(new DocumentFormat.OpenXml.Spreadsheet.Column() { Min = 1, Max = 3, CustomWidth = true, Width = 5 });
////wsPart.Worksheet.Append(cc);
//Worksheet ws = wsPart.Worksheet;
//ws.Append(cc);
DrawingsPart drawingsPart1 = wsPart.AddNewPart <DrawingsPart>("rId1");
XcelWin.GenerateDrawingsPart1Content(drawingsPart1);
ChartPart chartPart1 = drawingsPart1.AddNewPart <ChartPart>("rId1");
XcelWin.GenerateChartPart1Content(chartPart1);
Drawing drawing1 = new Drawing()
{
Id = "rId1"
};
wsPart.Worksheet.Append(drawing1);
workbookPart.Workbook.Save();
myWorkbook.Close();
}
//-----------------------------------------------------------------------------------
//Tests sur les charts
public void exec(string formuleVal, string formuleLegende)
{
string label = "Feuil1!D5:J5";
string serie = "Feuil1!C12";
string val = "Feuil1!D12:J12";
//euro mmp mut2m cmav sapem quatrem auxia
System.IO.File.Copy(template, copie, true);
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(copie, true);
ChartPart cc = XcelWin.cloneChart(XcelWin.getWorksheetPartByName(myWorkbook, "Feuil1"), "Tests", 0, 0, 10, 10);
BarChart bc = cc.ChartSpace.Descendants <BarChart>().First();
for (int i = 0; i < 6; i++)
{
BarChartSeries newSerie = (BarChartSeries)bc.Elements <BarChartSeries>().First().CloneNode(true);
string form = val.Replace("12", (6 + i).ToString());
newSerie.SeriesText.StringReference.Formula.Text = serie;
//newSerie.Descendants<CategoryAxisData>().First().Remove();
newSerie.Descendants <CategoryAxisData>().First().NumberReference.Formula.Text = label;
newSerie.Descendants <Charts.Values>().First().NumberReference.Formula.Text = form;
newSerie.Index.Val = (uint)i;
newSerie.Order.Val = (uint)i;
bc.Append(newSerie);
}
bc.Elements <BarChartSeries>().First().Remove();
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
public void exec()
{
System.IO.File.Copy(template, copie, true);
SpreadsheetDocument myWorkbookCopie = SpreadsheetDocument.Open(copie, true);
SpreadsheetDocument myWorkbookSource = SpreadsheetDocument.Open(source, true);
////Obtention du graph
////WorkbookPart workbookPart = myWorkbookSource.WorkbookPart;
////WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt<WorksheetPart>(2);
//WorksheetPart worksheetPart = XcelWin.GetWorksheetPartByName(myWorkbookSource, "REPORTING");
//DrawingsPart a = worksheetPart.DrawingsPart;
//ChartPart b = a.ChartParts.ElementAt(0);
////Creation drawingpart et chartpart
//WorkbookPart workbookPart2 = myWorkbookCopie.WorkbookPart;
//WorksheetPart worksheetPart2 = workbookPart2.WorksheetParts.ElementAt<WorksheetPart>(0);
//DrawingsPart dp = worksheetPart2.AddNewPart<DrawingsPart>();
//ChartPart x = dp.AddNewPart<ChartPart>();
//x.FeedData(b.GetStream());
WorkbookPart workbookPart2 = myWorkbookCopie.WorkbookPart;
WorksheetPart worksheetPart = XcelWin.getWorksheetPartByName(myWorkbookCopie, "REPORTING");
string[] labels = { "a", "b", "c" };
double[] data = { 2.2, 3.3, 5.5 };
//Permet de mettre un chart avec des données
PieChartSeries pcs = GeneratePieChartSeries(labels, data);
Chart x = getChartByTitle(worksheetPart, "Ressource");
PieChart px = x.Descendants <PieChart>().First();
px.RemoveAllChildren <PieChartSeries>();
px.Append(pcs);
workbookPart2.Workbook.Save();
myWorkbookCopie.Close();
}
/*
* public void execution(DataTable[] dt)
* {
*
* //Copie du template et ouverture du fichier
* System.IO.File.Copy(template, copie, true);
* myWorkbook = SpreadsheetDocument.Open(copie, true);
*
*
* //Access the main Workbook part, which contains all references.
* WorkbookPart workbookPart = myWorkbook.WorkbookPart;
*
* //0 : feuille 1
* //4 : feuille 2
* //3 : feuille 3
*
*
* //WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt<WorksheetPart>(2);
* //SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
* ////Ajout de la date sur la page de garde
* //Cell cell = XcelWin.CreateTextCell("A", 1, "IRCEM", 0);
* //Row r = new Row();
* //r.RowIndex = 1;
* //r.AppendChild(cell);
* //sheetData.AppendChild(r);
*
*
* WorksheetPart wp = workbookPart.WorksheetParts.ElementAt<WorksheetPart>(0);
* creerFeuille(wp, dt[0]);
*
* wp = workbookPart.WorksheetParts.ElementAt<WorksheetPart>(5);
* creerFeuille(wp, dt[1]);
*
* wp = workbookPart.WorksheetParts.ElementAt<WorksheetPart>(4);
* creerFeuille(wp, dt[2]);
*
* wp = workbookPart.WorksheetParts.ElementAt<WorksheetPart>(3);
* creerFeuille(wp, dt[3]);
*
* myWorkbook.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
* myWorkbook.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
*
* //Sauvegarde du workbook et fermeture de l'objet fichier
* workbookPart.Workbook.Save();
* myWorkbook.Close();
* }
*/
public void execution2(DataSet ds)
{
//Copie du template et ouverture du fichier
System.IO.File.Copy(template, copie, true);
myWorkbook = SpreadsheetDocument.Open(copie, true);
//Access the main Workbook part, which contains all references.
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
WorksheetPart wp = XcelWin.getWorksheetPartByName(myWorkbook, "Feuil1");
creerFeuille2(wp, ds);
myWorkbook.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
myWorkbook.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
//Sauvegarde du workbook et fermeture de l'objet fichier
workbookPart.Workbook.Save();
myWorkbook.Close();
}
public void execution(DataSet dt, string date, string[] sheetNames, bool monoFeuille = false)
{
if (monoFeuille)
{
//Copie du template et ouverture du fichier
System.IO.File.Copy(template, copie, true);
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(copie, true);
//Access the main Workbook part, which contains all references.
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
WorksheetPart wp = XcelWin.getWorksheetPartByName(myWorkbook, "Feuil1");
creerFeuille2(wp, dt);
myWorkbook.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
myWorkbook.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
//Sauvegarde du workbook et fermeture de l'objet fichier
workbookPart.Workbook.Save();
myWorkbook.Close();
}
else
{
//Copie du template et ouverture du fichier
System.IO.File.Copy(template, copie, true);
SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(copie, true);
//Access the main Workbook part, which contains all references.
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
if (stylePath != null)
{//Importation des styles contenus dans le fichier de style
//Copie du StyleSheet existant pour ne pas perdre les elements du template
var wsp = workbookPart.WorkbookStylesPart;
Stylesheet ss = wsp.Stylesheet;
// Comme on a fait une copie, on doit supprimer le WorkbookStylePart oiginal
workbookPart.DeletePart(wsp);
// Et on y ajoute le notre
WorkbookStylesPart wbsp = workbookPart.AddNewPart <WorkbookStylesPart>();
//Importation des styles contenus dans le fichier de style
Interface itf = new Interface();
itf.lectureFichier(ss, stylePath);
wbsp.Stylesheet = ss;
wbsp.Stylesheet.Save();
lineStyles = itf.lineStyles;
}
//WorksheetPart que l'on va copier
WorksheetPart worksheetPart = XcelWin.getWorksheetPartByName(myWorkbook, "Feuil1");
//Obtention des conf de graph : modeles, titres, colonnes ...
Dictionary <string, Object> d = new Dictionary <string, Object>();
if (graphPath != null)
{
d = Interface.lectureConfGraph(graphPath);
}
WorksheetPart graphs = XcelWin.getWorksheetPartByName(myWorkbook, "Graphs");
//Dimension du plus grand des graphs (pour les placer sans superposition par la suite)
//maxJ pour savoir ou placer les metagraphs par la suite
int maxLi = -1, maxCol = -1;
int maxJ = 0;
try
{ XcelWin.maxDimChart(graphs, out maxLi, out maxCol); }
catch (NullReferenceException)
{ Console.WriteLine("Aucun graph présent dans la feuille 'graph' \nIgnore toutes les operations relatives aux graphs"); }
//Creation de chacunes des feuilles
for (int i = 0; i < dt.Tables.Count; i++)
{
//obtention du nom pour la feuille, si absent : Sheet 1, 2, 3...
string nom = "";
try { nom = sheetNames[i]; }
catch (Exception) { nom = "Sheet" + (i + 1); }
WorksheetPart wp = CopySheet(workbookPart, worksheetPart, nom);
int tab = -1;
foreach (string clef in d.Keys.Where(s => s.Contains("tableau")))
{
if (((TableauData)d[clef]).indice == i)
{
tab = i;
}
}
if (tab != -1)
{
creerTableau(wp, dt.Tables[i], i + 1);
}
else
{
creerFeuille(wp, dt.Tables[i]);
}
//Obtention des lignes de données des graphs
d = Interface.razConfGraph(d);
if (indiceGraph != -1)
{
obtInfosGraphs(dt.Tables[i], d, nom);
}
int j = 0;
//Si on a trouvé des graphs lors de l'obtention de leurs tailles
if (maxLi != -1)
{
//Creation des graphs qui ont les clefs de type graph
foreach (string clef in d.Keys.Where(s => s.Contains("graph") && ((ChartData)d[s]).data.Count != 0))
{
ChartData tmp = (ChartData)d[clef];
ChartPart y = XcelWin.cloneChart(graphs, tmp.nomModele, i, j, maxLi, maxCol);
if (tmp.colValeurs == "last")
{
XcelWin.fixChartData(y, nom, tmp.data, headerColumns[dt.Tables[i].Columns.Count - nbColonneConfig - 1], getHeaderCol(tmp.colTitres, dt.Tables[i]));
}
else
{
try { XcelWin.fixChartData(y, nom, tmp.data, getHeaderCol(tmp.colValeurs, dt.Tables[i]), getHeaderCol(tmp.colTitres, dt.Tables[i])); }
catch (Exception) { Console.WriteLine("Echec de mise à jour des données du graph, verifier le fichier de configuration " + clef); }
}
XcelWin.fixChartTitle(y, tmp.titre + " " + nom);
j++;
}
if (j > maxJ)
{
maxJ = j;
}
}
}
//Si on a trouvé des graphs lors de l'obtention de leurs tailles
if (maxLi != -1)
{
//Creation des metagraphs
WorksheetPart wp2 = XcelWin.addWorksheetPart(myWorkbook, "data");
ecrireMetaChartData(wp2, d);
int k = 0;
foreach (string key in d.Keys.Where(s => s.Contains("meta")))
{
MetaChartData tmp = (MetaChartData)d[key];
try
{
ChartPart cp = XcelWin.cloneChart(graphs, tmp.nomModele, k, maxJ, maxLi, maxCol);
List <string> formules;
if (tmp.transpose)
{
formules = creaFormuleTranspose(((ChartData)d[tmp.series.First()]).data.Count, tmp.series.Count, tmp.indice);
}
else
{
formules = creaFormule(((ChartData)d[tmp.series.First()]).data.Count, tmp.series.Count, tmp.indice);
}
XcelWin.majMetaChart(cp, formules);
XcelWin.fixChartTitle(cp, tmp.titre);
k++;
}
catch (Exception) { Console.WriteLine("Impossible de copier un graph (verifiez que le graph est présent dans le template avec le bon titre) : " + tmp.nomModele); }
}
//Cache la feuille de donnée des metagraph
Sheet sData = XcelWin.getSheetByName(workbookPart, "data");
sData.State = new SheetStateValues();
sData.State.Value = SheetStateValues.Hidden;
//suppression des modèles de graphs
List <string> listeModele = new List <string>();
foreach (string key in d.Keys.Where(s => s.Contains("graph") || s.Contains("meta")))
{
string nomModele = "";
try { nomModele = ((ChartData)d[key]).nomModele; }
catch (Exception) { nomModele = ((MetaChartData)d[key]).nomModele; }
if (!listeModele.Contains(nomModele))
{
listeModele.Add(nomModele);
try { XcelWin.supprChart(graphs, nomModele); }
catch (Exception) { Console.WriteLine("Impossible de supprimer le modèle car introuvable : " + nomModele); }
}
}
}
try
{//Ajout de la date sur la page de garde
Cell cell = XcelWin.createTextCell("H", 27, date, 0);
Row r = new Row();
r.RowIndex = 27;
r.AppendChild(cell);
worksheetPart = XcelWin.getWorksheetPartByName(myWorkbook, "REPORTING");;
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
sheetData.AppendChild(r);
}
catch (Exception)
{
Console.WriteLine("Erreur lors de l'ajout de la date sur la page de garde 'REPORTING'");
}
//Suppression du worksheetPart qui a servis de modele
workbookPart.DeletePart(XcelWin.getWorksheetPartByName(myWorkbook, "Feuil1"));
XcelWin.getSheetByName(workbookPart, "Feuil1").Remove();
//Sauvegarde du workbook et fermeture de l'objet fichier
workbookPart.Workbook.Save();
myWorkbook.Close();
}
}
