private void MoveSheetXmlNode(ExcelWorksheet sourceSheet, ExcelWorksheet targetSheet, bool placeAfter)
{
var sourceNode = TopNode.SelectSingleNode(string.Format("d:sheet[@sheetId = '{0}']", sourceSheet.SheetID), _namespaceManager);
var targetNode = TopNode.SelectSingleNode(string.Format("d:sheet[@sheetId = '{0}']", targetSheet.SheetID), _namespaceManager);
if (sourceNode == null || targetNode == null)
{
throw new Exception("Source SheetId and Target SheetId must be valid");
}
if (placeAfter)
{
TopNode.InsertAfter(sourceNode, targetNode);
}
else
{
TopNode.InsertBefore(sourceNode, targetNode);
}
}
/// <summary>
/// Freeze the columns/rows to left and above the cell
/// </summary>
/// <param name="Row"></param>
/// <param name="Column"></param>
public void FreezePanes(int Row, int Column)
{
//TODO:fix this method to handle splits as well.
if (Row == 1 && Column == 1)
{
UnFreezePanes();
}
string sqRef = SelectedRange, activeCell = ActiveCell;
XmlElement paneNode = TopNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement;
if (paneNode == null)
{
CreateNode(_paneNodePath);
paneNode = TopNode.SelectSingleNode(_paneNodePath, NameSpaceManager) as XmlElement;
}
paneNode.RemoveAll(); //Clear all attributes
if (Column > 1)
{
paneNode.SetAttribute("xSplit", (Column - 1).ToString());
}
if (Row > 1)
{
paneNode.SetAttribute("ySplit", (Row - 1).ToString());
}
paneNode.SetAttribute("topLeftCell", ExcelCellBase.GetAddress(Row, Column));
paneNode.SetAttribute("state", "frozen");
RemoveSelection();
if (Row > 1 && Column == 1)
{
paneNode.SetAttribute("activePane", "bottomLeft");
XmlElement sel = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain);
sel.SetAttribute("pane", "bottomLeft");
if (activeCell != "")
{
sel.SetAttribute("activeCell", activeCell);
}
if (sqRef != "")
{
sel.SetAttribute("sqref", sqRef);
}
sel.SetAttribute("sqref", sqRef);
TopNode.InsertAfter(sel, paneNode);
}
else if (Column > 1 && Row == 1)
{
paneNode.SetAttribute("activePane", "topRight");
XmlElement sel = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain);
sel.SetAttribute("pane", "topRight");
if (activeCell != "")
{
sel.SetAttribute("activeCell", activeCell);
}
if (sqRef != "")
{
sel.SetAttribute("sqref", sqRef);
}
TopNode.InsertAfter(sel, paneNode);
}
else
{
paneNode.SetAttribute("activePane", "bottomRight");
XmlElement sel1 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain);
sel1.SetAttribute("pane", "topRight");
string cell = ExcelCellBase.GetAddress(1, Column);
sel1.SetAttribute("activeCell", cell);
sel1.SetAttribute("sqref", cell);
paneNode.ParentNode.InsertAfter(sel1, paneNode);
XmlElement sel2 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain);
cell = ExcelCellBase.GetAddress(Row, 1);
sel2.SetAttribute("pane", "bottomLeft");
sel2.SetAttribute("activeCell", cell);
sel2.SetAttribute("sqref", cell);
sel1.ParentNode.InsertAfter(sel2, sel1);
XmlElement sel3 = TopNode.OwnerDocument.CreateElement("selection", ExcelPackage.schemaMain);
sel3.SetAttribute("pane", "bottomRight");
if (activeCell != "")
{
sel3.SetAttribute("activeCell", activeCell);
}
if (sqRef != "")
{
sel3.SetAttribute("sqref", sqRef);
}
sel2.ParentNode.InsertAfter(sel3, sel2);
}
Panes = LoadPanes();
}