private static void CopyChart(string fromPresentation1, string toPresentation2)
{
using (PresentationDocument ppt1 = PresentationDocument.Open(fromPresentation1, false))
using (PresentationDocument ppt2 = PresentationDocument.Open(toPresentation2, true))
{
SlideId fromSlideId = ppt1.PresentationPart.Presentation.SlideIdList.GetFirstChild <SlideId>();
string fromRelId = fromSlideId.RelationshipId;
SlideId toSlideId = ppt2.PresentationPart.Presentation.SlideIdList.GetFirstChild <SlideId>();
string toRelId = fromSlideId.RelationshipId;
SlidePart fromSlidePart = (SlidePart)ppt1.PresentationPart.GetPartById(fromRelId);
SlidePart toSlidePart = (SlidePart)ppt2.PresentationPart.GetPartById(fromRelId);
var graphFrame = fromSlidePart.Slide.CommonSlideData.ShapeTree.GetFirstChild <GraphicFrame>().CloneNode(true);
GroupShapeProperties groupShapeProperties = toSlidePart.Slide.CommonSlideData.ShapeTree.GetFirstChild <GroupShapeProperties>();
toSlidePart.Slide.CommonSlideData.ShapeTree.InsertAfter(graphFrame, groupShapeProperties);
ChartPart fromChartPart = fromSlidePart.ChartParts.First();
ChartPart toChartPart = toSlidePart.AddNewPart <ChartPart>("rId2");
using (StreamReader streamReader = new StreamReader(fromChartPart.GetStream()))
using (StreamWriter streamWriter = new StreamWriter(toChartPart.GetStream(FileMode.Create)))
{
streamWriter.Write(streamReader.ReadToEnd());
}
EmbeddedPackagePart fromEmbeddedPackagePart1 = fromChartPart.EmbeddedPackagePart;
EmbeddedPackagePart toEmbeddedPackagePart1 = toChartPart.AddNewPart <EmbeddedPackagePart>("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "rId3");
using (StreamReader streamReader = new StreamReader(fromEmbeddedPackagePart1.GetStream()))
toEmbeddedPackagePart1.FeedData(streamReader.BaseStream);
}
}
/// <summary>
/// Copies a chart from the source worksheet part to the report worksheet part
/// The placeholder id in the drawing mapping information is used to
/// </summary>
/// <param name="part">The part.</param>
/// <param name="placeholder">The placeholder.</param>
/// <param name="mapping">The mapping.</param>
/// <param name="worksheetPart">The worksheet part.</param>
/// <param name="reportWSPart">The report ws part.</param>
/// <returns></returns>
private static ChartPart ProcessDrawingPart(ExportPart part, MappingPlaceholder placeholder, DrawingMapping mapping, WorksheetPart worksheetPart, WorksheetPart reportWSPart)
{
// if no placeholder is specified then return now
if (placeholder == null)
{
return(null);
}
DrawingSpreadsheet.Shape matchShape = reportWSPart.GetShapeByName(placeholder.Id);
if (matchShape == null)
{
return(null);
}
// save the location of this shape,
// this information will be used to position the incoming chart
Extents extents = matchShape.ShapeProperties.Transform2D.Extents;
Offset offset = matchShape.ShapeProperties.Transform2D.Offset;
// get the source chart.... if a source drawing id is specified use it
// otherwise get the 1st one
ChartPart sourceChartPart = null;
if (string.IsNullOrEmpty(mapping.SourceDrawingId))
{
// the 1st chart if there is one
sourceChartPart = worksheetPart.DrawingsPart.GetPartsOfType <ChartPart>().FirstOrDefault();
}
else
{
DrawingSpreadsheet.GraphicFrame sourceFrame = null;
// we need to pull out the graphic frame that matches the supplied name
foreach (var gf in worksheetPart.DrawingsPart.WorksheetDrawing.Descendants <DrawingSpreadsheet.GraphicFrame>())
{
// need to check it has the various properties
if (gf.NonVisualGraphicFrameProperties != null &&
gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties != null &&
gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Name.HasValue)
{
// and then try and match
if (mapping.SourceDrawingId.CompareTo(gf.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Name.Value) == 0)
{
sourceFrame = gf;
break;
}
}
}
// we have the graphics frame with data, so no pull out the chart part
if (sourceFrame != null && sourceFrame.Graphic != null && sourceFrame.Graphic.GraphicData != null)
{
var sourceChartRef = sourceFrame.Graphic.GraphicData.Descendants <DrawingCharts.ChartReference>().FirstOrDefault();
if (sourceChartRef != null && sourceChartRef.Id.HasValue)
{
sourceChartPart = (ChartPart)worksheetPart.DrawingsPart.GetPartById(sourceChartRef.Id.Value);
}
}
}
// create a new one
var targetChartPart = reportWSPart.DrawingsPart.AddNewPart <ChartPart>();
// and feed data from old to new
targetChartPart.FeedData(sourceChartPart.GetStream());
// chart in now
// just need the drawing to host it
// get the graphic frame from the source anchor
var sourceAnchor = worksheetPart.DrawingsPart.WorksheetDrawing.GetFirstChild <DrawingSpreadsheet.TwoCellAnchor>();
var sourceGraphicFrame = sourceAnchor.Descendants <DrawingSpreadsheet.GraphicFrame>().FirstOrDefault();
// add it to the target anchor (ie. the one with the shape removed)
var targetGraphicFrame = sourceGraphicFrame.CloneNode(true);
// positon the new graphic frame after the shape its going to replace
matchShape.Parent.InsertAfter <OpenXmlElement>(targetGraphicFrame, matchShape);
// and remove the shape, not needed anymore
matchShape.Remove();
// update the extents and offsets that were saved above
var transform = targetGraphicFrame.Descendants <DrawingSpreadsheet.Transform>().FirstOrDefault();
if (transform != null)
{
transform.Extents.Cx = extents.Cx;
transform.Extents.Cy = extents.Cy;
transform.Offset.X = offset.X;
transform.Offset.Y = offset.Y;
}
// ensure that the id of the chart reference in the cloned graphic frame matches that of the new cloned chart
// if this isnt done then no chart will appeat
var chartReference = targetGraphicFrame.Descendants <DocumentFormat.OpenXml.Drawing.Charts.ChartReference>().FirstOrDefault();
chartReference.Id = reportWSPart.DrawingsPart.GetIdOfPart(targetChartPart);
return(targetChartPart);
}
public string AddSheetWithChart(string TemplateSheetName, List <List <object> > ChartData, string[] SeriesLabels, Dictionary <String, String> ReplacementDict)
{
//Assume error
string result = "Error Creating Chart";
WSheetName = TemplateSheetName;
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(resultfilepath + "CloudReport.xlsx", true))
{
//create clone of template chart sheet as TemplateSheetName
CloneSheet(document, "TemplateChartSheet", WSheetName);
result = "\nTemplate Chart Sheet CLONED as " + WSheetName;
}
using (SpreadsheetDocument document = SpreadsheetDocument.Open(resultfilepath + "CloudReport.xlsx", true))
{
IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == WSheetName);
if (sheets.Count() == 0)
{
return(result = result + "\nNo worksheet found named as " + WSheetName);
}
else
{
WorkbookPart workbookPart = document.WorkbookPart;
int numOfRowsToAdd = ChartData.Count();
Int32 currentRowNum = 3;
Char currentColumn;
// inserting Chart data in excel sheet rows
foreach (List <Object> rowitem in ChartData)
{
//this is a line
currentColumn = 'B';
foreach (var obj in rowitem)
{
//inserted values are NOT of type string
UpdateValue(workbookPart, WSheetName, currentColumn + currentRowNum.ToString(), obj.ToString(), 3, false);
currentColumn++;
}
currentRowNum++;
}
result = result + "\nInserted Chart data in excel sheet rows";
// update chart part to reflect the new data
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where((s) => s.Name == WSheetName).FirstOrDefault();
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
ChartPart part = worksheetPart.DrawingsPart.ChartParts.First();
XElement XmlChart;
using (XmlReader xmlr = XmlReader.Create(part.GetStream()))
{
XmlChart = XElement.Load(xmlr);
}
XElement barChartData = XmlChart.Descendants(ns_c + "barChart").Single();
List <XElement> elements = new List <XElement>();
elements.AddRange(barChartData.Elements().Take(ChartData.Count()));
// these 2 values are hard coded for now
Char startColChar = 'B';
currentRowNum = 3;
// Char endColChar = 'B';
int colCount = 0;
int m = 0;
foreach (List <object> rowitem in ChartData)
{
Char endColChar = 'B';
colCount = rowitem.Count();
while (colCount > 1)
{
endColChar++;
colCount--;
}
XElement ser = new XElement(ns_c + "ser");
ser.Add(new XElement(ns_c + "idx", new XAttribute("val", m)));
ser.Add(new XElement(ns_c + "order", new XAttribute("val", m)));
ser.Add(new XElement(ns_c + "tx", new XElement(ns_c + "v", new XText(SeriesLabels[m].ToString()))));
m++;
XElement cat = new XElement(ns_c + "cat");
XElement strRef = new XElement(ns_c + "strRef");
cat.Add(strRef);
strRef.Add(new XElement(ns_c + "f", new XText("'" + WSheetName + "'!$" + "B" + "$2:$" + endColChar + "$2")));
//XElement strCache = new XElement(ns_c + "strCache");
//strRef.Add(strCache);
//strCache.Add(new XElement(ns_c + "ptCount", new XAttribute("val", rowitem.Count())));
//int j = 0;
//foreach (var obj in rowitem)
//{
// strCache.Add(new XElement(ns_c + "pt",
// new XAttribute("idx", j),
// new XElement(ns_s + "v", new XText(obj.ToString()))));
// j++;
//}
XElement val = new XElement(ns_c + "val");
XElement numRef = new XElement(ns_c + "numRef");
val.Add(numRef);
numRef.Add(new XElement(ns_c + "f", new XText("'" + WSheetName + "'!$" + startColChar + "$" + currentRowNum + ":$" + endColChar + "$" + currentRowNum)));
XElement numCache = new XElement(ns_c + "numCache");
numRef.Add(numCache);
//numCache.Add(new XElement(ns_c + "formatCode", new XText(""$"#,##0_);\("$"#,##0\)");
numCache.Add(new XElement(ns_c + "ptCount", new XAttribute("val", rowitem.Count())));
int k = 0;
foreach (var obj in rowitem)
{
numCache.Add(new XElement(ns_c + "pt",
new XAttribute("idx", k),
new XElement(ns_s + "v", new XText(obj.ToString()))));
k++;
}
ser.Add(cat);
ser.Add(val);
elements.Add(ser);
currentRowNum++;
}
//Now we have all elements
barChartData.Elements().Remove();
barChartData.Add(elements);
using (Stream s = part.GetStream(FileMode.Create, FileAccess.Write))
{
using (XmlWriter xmlw = XmlWriter.Create(s))
{
XmlChart.WriteTo(xmlw);
}
}
result = result + "\nChart updated.";
}
// Replace [Tag Name] by “Tag Value” in a worksheet
foreach (KeyValuePair <string, string> item in ReplacementDict)
{
string tagname = item.Key;
string tagvalue = item.Value;
WorkbookPart workbookPart = document.WorkbookPart;
SharedStringTablePart sharedStringsPart = workbookPart.SharedStringTablePart;
IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Text> sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants <DocumentFormat.OpenXml.Spreadsheet.Text>();
DoReplace(sharedStringTextElements, tagname, tagvalue);
IEnumerable <WorksheetPart> worksheetParts = workbookPart.GetPartsOfType <WorksheetPart>();
foreach (var worksheet in worksheetParts)
{
var allTextElements = worksheet.Worksheet.Descendants <DocumentFormat.OpenXml.Spreadsheet.Text>();
DoReplace(allTextElements, tagname, tagvalue);
}
}
//result = result + "\n Replaced [Tag Name] by “Tag Value” in a worksheet";
}
}
catch (Exception ex)
{
result = ex.Message;
}
return(result);
}
public static ChartPart cloneChart(WorksheetPart worksheetPart, string chartTitle, int posLi, int posCol, int largeLi, int largeCol)
{
//Obtention de tous les ChartParts dans une enumeration
DrawingsPart dp = worksheetPart.DrawingsPart;
IEnumerable <ChartPart> cps = dp.ChartParts;
//Recherche la ChartPart qui porte le titre correspondant
ChartPart b = null;
foreach (ChartPart cp in cps)
{
if (cp.ChartSpace.Descendants <Chart.Chart>().First().Descendants <Chart.Title>().First().Descendants <DocumentFormat.OpenXml.Drawing.Run>().First().Text.Text == chartTitle)
{
b = cp;
}
}
DrawingsPart a = worksheetPart.DrawingsPart;
//Ajout de la nouvelle partie et copie
ChartPart x = a.AddNewPart <ChartPart>();
Stream stream = b.GetStream();
x.FeedData(stream);
string id = a.GetIdOfPart(b);
//Copie de l'ancre associée au graph original
Draw.TwoCellAnchor newAnchor = null;
DocumentFormat.OpenXml.Drawing.Spreadsheet.WorksheetDrawing wsd = a.WorksheetDrawing;
foreach (Draw.TwoCellAnchor tca in wsd.Elements <Draw.TwoCellAnchor>())
{
string tmp = tca.Descendants <Chart.ChartReference>().First().Id;
if (tmp == id)
{
newAnchor = (Draw.TwoCellAnchor)tca.CloneNode(true);
}
}
//positionnement de la nouvelle ancre
int r = Convert.ToInt32(newAnchor.ToMarker.RowId.Text) - Convert.ToInt32(newAnchor.FromMarker.RowId.Text);
int c = Convert.ToInt32(newAnchor.ToMarker.ColumnId.Text) - Convert.ToInt32(newAnchor.FromMarker.ColumnId.Text);
newAnchor.FromMarker.ColumnId = new Draw.ColumnId()
{
Text = "" + largeCol * posCol
};
newAnchor.ToMarker.ColumnId = new Draw.ColumnId()
{
Text = "" + (c + largeCol * posCol)
};
newAnchor.FromMarker.RowId = new Draw.RowId()
{
Text = "" + largeLi * posLi
};
newAnchor.ToMarker.RowId = new Draw.RowId()
{
Text = "" + (r + largeLi * posLi)
};
newAnchor.Descendants <Chart.ChartReference>().First().Id = a.GetIdOfPart(x);
wsd.Append(newAnchor);
return(x);
}