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(); } }