/// <summary> /// Translate an address from A1 to R1C1 /// </summary> /// <param name="address">The address</param> /// <param name="row">The row of the cell to calculate from</param> /// <param name="col">The column of the cell to calculate from</param> /// <returns>The address in R1C1 notation</returns> public static string ToR1C1(ExcelAddressBase address, int row, int col) { string returnAddress; if (address.IsFullRow) //Full Row { if (address._fromRow == address._toRow && address._fromRowFixed == address._toRowFixed) { returnAddress = GetCellAddress("R", address._fromRow, row, address._fromRowFixed); } else { returnAddress = GetCellAddress("R", address._fromRow, row, address._fromRowFixed) + ":" + GetCellAddress("R", address._toRow, row, address._toRowFixed); } } else if (address.IsFullColumn) //Full Column { if (address._fromCol == address._toCol && address._fromColFixed == address._toColFixed) { returnAddress = GetCellAddress("C", address._fromCol, col, address._fromColFixed); } else { returnAddress = GetCellAddress("C", address._fromCol, col, address._fromColFixed) + ":" + GetCellAddress("C", address._toCol, col, address._toColFixed); } } else if (address.Table != null) { return(address.Address); } else { if (address.IsSingleCell) { returnAddress = GetCellAddress("R", address._fromRow, row, address._fromRowFixed) + GetCellAddress("C", address._fromCol, col, address._fromColFixed); } else { returnAddress = GetCellAddress("R", address._fromRow, row, address._fromRowFixed) + GetCellAddress("C", address._fromCol, col, address._fromColFixed) + ":" + GetCellAddress("R", address._toRow, row, address._toRowFixed) + GetCellAddress("C", address._toCol, col, address._toColFixed); } } return(ExcelAddressBase.GetFullAddress(address._wb, address._ws, returnAddress)); }
private static void CopyDrawing(ExcelPackage pck, XmlNamespaceManager nsm, ExcelWorksheet Copy, ExcelWorksheet added) { //First copy the drawing XML string xml = Copy.Drawings.DrawingXml.OuterXml; var uriDraw = new Uri(string.Format("/xl/drawings/drawing{0}.xml", added.SheetId), UriKind.Relative); var part = pck.ZipPackage.CreatePart(uriDraw, "application/vnd.openxmlformats-officedocument.drawing+xml", pck.Compression); StreamWriter streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write)); streamDrawing.Write(xml); streamDrawing.Flush(); XmlDocument drawXml = new XmlDocument(); drawXml.LoadXml(xml); //Add the relationship ID to the worksheet xml. var drawRelation = added.Part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, uriDraw), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/drawing"); XmlElement e = added.WorksheetXml.SelectSingleNode("//d:drawing", nsm) as XmlElement; e.SetAttribute("id", ExcelPackage.schemaRelationships, drawRelation.Id); for (int i = 0; i < Copy.Drawings.Count; i++) { ExcelDrawing draw = Copy.Drawings[i]; //draw.AdjustPositionAndSize(); //Adjust position for any change in normal style font/row size etc. if (draw is ExcelChart chart) { xml = chart.ChartXml.InnerXml; var UriChart = XmlHelper.GetNewUri(pck.ZipPackage, "/xl/charts/chart{0}.xml"); var chartPart = pck.ZipPackage.CreatePart(UriChart, ContentTypes.contentTypeChart, pck.Compression); StreamWriter streamChart = new StreamWriter(chartPart.GetStream(FileMode.Create, FileAccess.Write)); streamChart.Write(xml); streamChart.Flush(); //Now create the new relationship to the copied chart xml var prevRelID = draw.TopNode.SelectSingleNode("xdr:graphicFrame/a:graphic/a:graphicData/c:chart/@r:id", Copy.Drawings.NameSpaceManager).Value; var rel = part.CreateRelationship(UriHelper.GetRelativeUri(uriDraw, UriChart), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/chart"); XmlAttribute relAtt = drawXml.SelectSingleNode(string.Format("//c:chart/@r:id[.='{0}']", prevRelID), Copy.Drawings.NameSpaceManager) as XmlAttribute; relAtt.Value = rel.Id; } else if (draw is ExcelPicture pic) { IPictureContainer container = pic; var uri = container.UriPic; var img = PictureStore.ImageToByteArray(pic.Image); var ii = added.Workbook._package.PictureStore.AddImage(img, null, pic.ContentType); var rel = part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, ii.Uri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/image"); //Fixes problem with invalid image when the same image is used more than once. XmlNode relAtt = drawXml.SelectSingleNode( string.Format( "//xdr:pic/xdr:nvPicPr/xdr:cNvPr/@name[.='{0}']/../../../xdr:blipFill/a:blip/@r:embed", pic.Name), Copy.Drawings.NameSpaceManager); if (relAtt != null) { relAtt.Value = rel.Id; } } else if (draw is ExcelTableSlicer slicer) { var name = pck.Workbook.GetSlicerName(slicer.Name); var newSlicer = added.Drawings[i] as ExcelTableSlicer; newSlicer.Name = name; newSlicer.SlicerName = name; //The slicer still reference the copied slicers cache. We need to create a new cache for the copied slicer. newSlicer.CreateNewCache(); } else if (draw is ExcelPivotTableSlicer ptSlicer) { var name = pck.Workbook.GetSlicerName(ptSlicer.Name); var newSlicer = added.Drawings[i] as ExcelPivotTableSlicer; newSlicer.Name = name; newSlicer.SlicerName = name; //The slicer still reference the copied slicers cache. We need to create a new cache for the copied slicer. newSlicer.CreateNewCache(ptSlicer.Cache._field); } else if (draw is ExcelControl ctrl) { var UriCtrl = XmlHelper.GetNewUri(pck.ZipPackage, "/xl/ctrlProps/ctrlProp{0}.xml"); var ctrlPart = pck.ZipPackage.CreatePart(UriCtrl, ContentTypes.contentTypeControlProperties, pck.Compression); StreamWriter streamChart = new StreamWriter(ctrlPart.GetStream(FileMode.Create, FileAccess.Write)); streamChart.Write(ctrl.ControlPropertiesXml.OuterXml); streamChart.Flush(); var prevRelID = ctrl._control.RelationshipId; var rel = added.Part.CreateRelationship(UriHelper.GetRelativeUri(added.WorksheetUri, UriCtrl), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/ctrlProp"); XmlAttribute relAtt = added.WorksheetXml.SelectSingleNode(string.Format("//d:control/@r:id[.='{0}']", prevRelID), added.NameSpaceManager) as XmlAttribute; relAtt.Value = rel.Id; } } //rewrite the drawing xml with the new relID's streamDrawing = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write)); streamDrawing.Write(drawXml.OuterXml); streamDrawing.Flush(); //Copy the size variables to the copy. for (int i = 0; i < Copy.Drawings.Count; i++) { var draw = Copy.Drawings[i]; var c = added.Drawings[i]; if (c != null) { c._left = draw._left; c._top = draw._top; c._height = draw._height; c._width = draw._width; } if (c is ExcelChart chart) { for (int j = 0; i < chart.Series.Count; i++) { var s = chart.Series[j]; var a = new ExcelAddressBase(s.Series); if (a.WorkSheetName.Equals(Copy.Name)) { s.Series = ExcelAddressBase.GetFullAddress(added.Name, a.LocalAddress); } a = new ExcelAddressBase(s.XSeries); if (a.WorkSheetName.Equals(Copy.Name)) { s.XSeries = ExcelAddressBase.GetFullAddress(added.Name, a.LocalAddress); } } } } }