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