// Generates content of worksheetPart2.
private void GenerateWorksheetPart2Content(WorksheetPart worksheetPart2)
{
Worksheet worksheet2 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet2.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet2.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
worksheet2.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
worksheet2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet2.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetDimension sheetDimension2 = new SheetDimension() { Reference = "A1:V19" };
SheetViews sheetViews2 = new SheetViews();
SheetView sheetView2 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
Selection selection2 = new Selection() { ActiveCell = "X5", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "X5" } };
sheetView2.Append(selection2);
sheetViews2.Append(sheetView2);
SheetFormatProperties sheetFormatProperties2 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };
SheetData sheetData2 = new SheetData();
Row row2 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell2 = new Cell() { CellReference = "A1" };
CellValue cellValue2 = new CellValue();
cellValue2.Text = "1";
cell2.Append(cellValue2);
Cell cell3 = new Cell() { CellReference = "C1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue3 = new CellValue();
cellValue3.Text = "0";
cell3.Append(cellValue3);
row2.Append(cell2);
row2.Append(cell3);
Row row3 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell4 = new Cell() { CellReference = "A2" };
CellValue cellValue4 = new CellValue();
cellValue4.Text = "2";
cell4.Append(cellValue4);
row3.Append(cell4);
Row row4 = new Row() { RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell5 = new Cell() { CellReference = "A3" };
CellValue cellValue5 = new CellValue();
cellValue5.Text = "3";
cell5.Append(cellValue5);
row4.Append(cell5);
Row row5 = new Row() { RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, Height = 135D, DyDescent = 0.25D };
Cell cell6 = new Cell() { CellReference = "A4" };
CellValue cellValue6 = new CellValue();
cellValue6.Text = "4";
cell6.Append(cellValue6);
Cell cell7 = new Cell() { CellReference = "C4", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue7 = new CellValue();
cellValue7.Text = "1";
cell7.Append(cellValue7);
row5.Append(cell6);
row5.Append(cell7);
Row row6 = new Row() { RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell8 = new Cell() { CellReference = "A5" };
CellValue cellValue8 = new CellValue();
cellValue8.Text = "5";
cell8.Append(cellValue8);
Cell cell9 = new Cell() { CellReference = "Q5", StyleIndex = (UInt32Value)3U, DataType = CellValues.SharedString };
CellValue cellValue9 = new CellValue();
cellValue9.Text = "6";
cell9.Append(cellValue9);
Cell cell10 = new Cell() { CellReference = "T5" };
CellValue cellValue10 = new CellValue();
cellValue10.Text = "4";
cell10.Append(cellValue10);
row6.Append(cell8);
row6.Append(cell9);
row6.Append(cell10);
Row row7 = new Row() { RowIndex = (UInt32Value)6U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell11 = new Cell() { CellReference = "A6" };
CellFormula cellFormula1 = new CellFormula();
cellFormula1.Text = "SUM(A1:A5)";
CellValue cellValue11 = new CellValue();
cellValue11.Text = "15";
cell11.Append(cellFormula1);
cell11.Append(cellValue11);
Cell cell12 = new Cell() { CellReference = "T6" };
CellValue cellValue12 = new CellValue();
cellValue12.Text = "7";
cell12.Append(cellValue12);
row7.Append(cell11);
row7.Append(cell12);
Row row8 = new Row() { RowIndex = (UInt32Value)7U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell13 = new Cell() { CellReference = "T7" };
CellValue cellValue13 = new CellValue();
cellValue13.Text = "6";
cell13.Append(cellValue13);
row8.Append(cell13);
Row row9 = new Row() { RowIndex = (UInt32Value)8U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell14 = new Cell() { CellReference = "T8" };
CellValue cellValue14 = new CellValue();
cellValue14.Text = "5";
cell14.Append(cellValue14);
row9.Append(cell14);
Row row10 = new Row() { RowIndex = (UInt32Value)9U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell15 = new Cell() { CellReference = "T9" };
CellValue cellValue15 = new CellValue();
cellValue15.Text = "4";
cell15.Append(cellValue15);
row10.Append(cell15);
Row row11 = new Row() { RowIndex = (UInt32Value)10U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell16 = new Cell() { CellReference = "T10" };
CellValue cellValue16 = new CellValue();
cellValue16.Text = "7";
cell16.Append(cellValue16);
row11.Append(cell16);
Row row12 = new Row() { RowIndex = (UInt32Value)17U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
Cell cell17 = new Cell() { CellReference = "A17", DataType = CellValues.SharedString };
CellValue cellValue17 = new CellValue();
cellValue17.Text = "3";
cell17.Append(cellValue17);
Cell cell18 = new Cell() { CellReference = "B17", DataType = CellValues.SharedString };
CellValue cellValue18 = new CellValue();
cellValue18.Text = "4";
cell18.Append(cellValue18);
Cell cell19 = new Cell() { CellReference = "C17", DataType = CellValues.SharedString };
CellValue cellValue19 = new CellValue();
cellValue19.Text = "5";
cell19.Append(cellValue19);
row12.Append(cell17);
row12.Append(cell18);
row12.Append(cell19);
Row row13 = new Row() { RowIndex = (UInt32Value)18U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
Cell cell20 = new Cell() { CellReference = "A18" };
CellValue cellValue20 = new CellValue();
cellValue20.Text = "1";
cell20.Append(cellValue20);
Cell cell21 = new Cell() { CellReference = "B18" };
CellValue cellValue21 = new CellValue();
cellValue21.Text = "2";
cell21.Append(cellValue21);
Cell cell22 = new Cell() { CellReference = "C18" };
CellValue cellValue22 = new CellValue();
cellValue22.Text = "3";
cell22.Append(cellValue22);
row13.Append(cell20);
row13.Append(cell21);
row13.Append(cell22);
Row row14 = new Row() { RowIndex = (UInt32Value)19U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
Cell cell23 = new Cell() { CellReference = "A19" };
CellValue cellValue23 = new CellValue();
cellValue23.Text = "4";
cell23.Append(cellValue23);
Cell cell24 = new Cell() { CellReference = "B19" };
CellValue cellValue24 = new CellValue();
cellValue24.Text = "5";
cell24.Append(cellValue24);
Cell cell25 = new Cell() { CellReference = "C19" };
CellValue cellValue25 = new CellValue();
cellValue25.Text = "6";
cell25.Append(cellValue25);
row14.Append(cell23);
row14.Append(cell24);
row14.Append(cell25);
sheetData2.Append(row2);
sheetData2.Append(row3);
sheetData2.Append(row4);
sheetData2.Append(row5);
sheetData2.Append(row6);
sheetData2.Append(row7);
sheetData2.Append(row8);
sheetData2.Append(row9);
sheetData2.Append(row10);
sheetData2.Append(row11);
sheetData2.Append(row12);
sheetData2.Append(row13);
sheetData2.Append(row14);
Hyperlinks hyperlinks1 = new Hyperlinks();
Hyperlink hyperlink2 = new Hyperlink() { Reference = "Q5", Id = "rId1" };
hyperlinks1.Append(hyperlink2);
PageMargins pageMargins2 = new PageMargins() { Left = 0.25D, Right = 0.25D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
PageSetup pageSetup1 = new PageSetup() { Orientation = OrientationValues.Landscape, HorizontalDpi = (UInt32Value)4294967293U, VerticalDpi = (UInt32Value)0U, Id = "rId2" };
S.Drawing drawing3 = new S.Drawing() { Id = "rId3" };
LegacyDrawing legacyDrawing1 = new LegacyDrawing() { Id = "rId4" };
TableParts tableParts1 = new TableParts() { Count = (UInt32Value)1U };
TablePart tablePart1 = new TablePart() { Id = "rId5" };
tableParts1.Append(tablePart1);
worksheet2.Append(sheetDimension2);
worksheet2.Append(sheetViews2);
worksheet2.Append(sheetFormatProperties2);
worksheet2.Append(sheetData2);
worksheet2.Append(hyperlinks1);
worksheet2.Append(pageMargins2);
worksheet2.Append(pageSetup1);
worksheet2.Append(drawing3);
worksheet2.Append(legacyDrawing1);
worksheet2.Append(tableParts1);
worksheetPart2.Worksheet = worksheet2;
}
private void WriteSelectedWorksheet()
{
// split into writing for existing worksheet and for new worksheet
this.CleanUpReallyEmptyCells();
int i = 0;
bool bFound = false;
OpenXmlElement oxe;
SLColumnProperties cp;
SLRowProperties rp;
byte byMaxOutline;
List<int> listintkeys;
// remove empty rows/columns plus getting the maximum outline levels at the same time.
byMaxOutline = 0;
listintkeys = slws.RowProperties.Keys.ToList<int>();
foreach (int key in listintkeys)
{
rp = slws.RowProperties[key];
if (rp.IsEmpty) slws.RowProperties.Remove(key);
else if (rp.OutlineLevel > byMaxOutline) byMaxOutline = rp.OutlineLevel;
}
if (byMaxOutline > 0) slws.SheetFormatProperties.OutlineLevelRow = byMaxOutline;
byMaxOutline = 0;
listintkeys = slws.ColumnProperties.Keys.ToList<int>();
foreach (int key in listintkeys)
{
cp = slws.ColumnProperties[key];
if (cp.IsEmpty) slws.ColumnProperties.Remove(key);
else if (cp.OutlineLevel > byMaxOutline) byMaxOutline = cp.OutlineLevel;
}
if (byMaxOutline > 0) slws.SheetFormatProperties.OutlineLevelColumn = byMaxOutline;
List<SLCellPoint> listCellRefKeys = slws.Cells.Keys.ToList<SLCellPoint>();
listCellRefKeys.Sort(new SLCellReferencePointComparer());
HashSet<int> hsRows = new HashSet<int>(listCellRefKeys.GroupBy(g => g.RowIndex).Select(s => s.Key).ToList<int>());
hsRows.UnionWith(slws.RowProperties.Keys.ToList<int>());
// this now contains every row index that's either in the list of row properties
// or in the list of cells.
List<int> listRowIndex = hsRows.ToList<int>();
listRowIndex.Sort();
List<int> listColumnIndex = slws.ColumnProperties.Keys.ToList<int>();
listColumnIndex.Sort();
int iDimensionStartRowIndex = SLConstants.RowLimit + 1;
int iDimensionStartColumnIndex = SLConstants.ColumnLimit + 1;
int iDimensionEndRowIndex = -1;
int iDimensionEndColumnIndex = -1;
if (listCellRefKeys.Count > 0 || listRowIndex.Count > 0 || listColumnIndex.Count > 0 || slws.MergeCells.Count > 0)
{
foreach (SLCellPoint refpt in slws.Cells.Keys)
{
// just check for columns because row checking is already done with RowProperties
// this cuts down on checking, and speed things up.
if (refpt.ColumnIndex < iDimensionStartColumnIndex) iDimensionStartColumnIndex = refpt.ColumnIndex;
if (refpt.ColumnIndex > iDimensionEndColumnIndex) iDimensionEndColumnIndex = refpt.ColumnIndex;
}
if (listRowIndex.Count > 0)
{
if (listRowIndex[0] < iDimensionStartRowIndex) iDimensionStartRowIndex = listRowIndex[0];
if (listRowIndex[listRowIndex.Count - 1] > iDimensionEndRowIndex) iDimensionEndRowIndex = listRowIndex[listRowIndex.Count - 1];
}
if (listColumnIndex.Count > 0)
{
if (listColumnIndex[0] < iDimensionStartColumnIndex) iDimensionStartColumnIndex = listColumnIndex[0];
if (listColumnIndex[listColumnIndex.Count - 1] > iDimensionEndColumnIndex) iDimensionEndColumnIndex = listColumnIndex[listColumnIndex.Count - 1];
}
foreach (SLMergeCell mc in slws.MergeCells)
{
if (mc.StartRowIndex < iDimensionStartRowIndex) iDimensionStartRowIndex = mc.StartRowIndex;
if (mc.StartColumnIndex < iDimensionStartColumnIndex) iDimensionStartColumnIndex = mc.StartColumnIndex;
if (mc.EndRowIndex > iDimensionEndRowIndex) iDimensionEndRowIndex = mc.EndRowIndex;
if (mc.EndColumnIndex > iDimensionEndColumnIndex) iDimensionEndColumnIndex = mc.EndColumnIndex;
}
// need to do for hyperlinks?
//foreach (SLHyperlink hl in slws.Hyperlinks)
//{
// if (hl.Reference.StartRowIndex < iDimensionStartRowIndex) iDimensionStartRowIndex = hl.Reference.StartRowIndex;
// if (hl.Reference.StartColumnIndex < iDimensionStartColumnIndex) iDimensionStartColumnIndex = hl.Reference.StartColumnIndex;
// if (hl.Reference.EndRowIndex > iDimensionEndRowIndex) iDimensionEndRowIndex = hl.Reference.EndRowIndex;
// if (hl.Reference.EndColumnIndex > iDimensionEndColumnIndex) iDimensionEndColumnIndex = hl.Reference.EndColumnIndex;
//}
}
string sDimensionCellRange = string.Empty;
if (iDimensionStartRowIndex > SLConstants.RowLimit) iDimensionStartRowIndex = 1;
if (iDimensionStartColumnIndex > SLConstants.ColumnLimit) iDimensionStartColumnIndex = 1;
if (iDimensionEndRowIndex < 1) iDimensionEndRowIndex = 1;
if (iDimensionEndColumnIndex < 1) iDimensionEndColumnIndex = 1;
if (iDimensionStartRowIndex == iDimensionEndRowIndex && iDimensionStartColumnIndex == iDimensionEndColumnIndex)
{
sDimensionCellRange = SLTool.ToCellReference(iDimensionStartRowIndex, iDimensionStartColumnIndex);
}
else
{
sDimensionCellRange = string.Format("{0}:{1}", SLTool.ToCellReference(iDimensionStartRowIndex, iDimensionStartColumnIndex), SLTool.ToCellReference(iDimensionEndRowIndex, iDimensionEndColumnIndex));
}
Row r;
SLCell c;
int iRowIndex = 0;
int iCellDataKey = 0;
int iRowKey = 0;
SLCellPoint pt;
if (!IsNewWorksheet)
{
// Need to check?
//if (string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID)) return;
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID);
if (slws.ForceCustomRowColumnDimensionsSplitting)
{
slws.ToggleCustomRowColumnDimension(true);
}
if (slws.PageSettings.HasSheetProperties)
{
wsp.Worksheet.SheetProperties = slws.PageSettings.SheetProperties.ToSheetProperties();
}
else
{
wsp.Worksheet.SheetProperties = null;
}
wsp.Worksheet.SheetDimension = new SheetDimension() { Reference = sDimensionCellRange };
if (slws.SheetViews.Count > 0)
{
wsp.Worksheet.SheetViews = new SheetViews();
foreach (SLSheetView sv in slws.SheetViews)
{
wsp.Worksheet.SheetViews.Append(sv.ToSheetView());
}
}
else
{
wsp.Worksheet.SheetViews = null;
}
wsp.Worksheet.SheetFormatProperties = slws.SheetFormatProperties.ToSheetFormatProperties();
#region Filling Columns
if (wsp.Worksheet.Elements<Columns>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<Columns>();
}
if (slws.ColumnProperties.Count > 0)
{
Columns cols = new Columns();
Column col;
int iPreviousColumnIndex = listColumnIndex[0];
int iCurrentColumnIndex = iPreviousColumnIndex;
string sCollectiveColumnData = string.Empty;
string sCurrentColumnData = string.Empty;
int colmin, colmax;
colmin = colmax = iCurrentColumnIndex;
cp = slws.ColumnProperties[iCurrentColumnIndex];
sCollectiveColumnData = cp.ToHash();
col = new Column();
col.Min = (uint)colmin;
col.Max = (uint)colmax;
if (cp.HasWidth)
{
col.Width = cp.Width;
col.CustomWidth = true;
}
else
{
col.Width = slws.SheetFormatProperties.DefaultColumnWidth;
}
if (cp.StyleIndex > 0) col.Style = cp.StyleIndex;
if (cp.Hidden) col.Hidden = cp.Hidden;
if (cp.BestFit) col.BestFit = cp.BestFit;
if (cp.Phonetic) col.Phonetic = cp.Phonetic;
if (cp.OutlineLevel > 0) col.OutlineLevel = cp.OutlineLevel;
if (cp.Collapsed) col.Collapsed = cp.Collapsed;
for (i = 1; i < listColumnIndex.Count; ++i)
{
iPreviousColumnIndex = iCurrentColumnIndex;
iCurrentColumnIndex = listColumnIndex[i];
cp = slws.ColumnProperties[iCurrentColumnIndex];
sCurrentColumnData = cp.ToHash();
if ((iCurrentColumnIndex != (iPreviousColumnIndex + 1)) || (sCollectiveColumnData != sCurrentColumnData))
{
col.Max = (uint)colmax;
cols.Append(col);
colmin = iCurrentColumnIndex;
colmax = iCurrentColumnIndex;
sCollectiveColumnData = sCurrentColumnData;
col = new Column();
col.Min = (uint)colmin;
col.Max = (uint)colmax;
if (cp.HasWidth)
{
col.Width = cp.Width;
col.CustomWidth = true;
}
else
{
col.Width = slws.SheetFormatProperties.DefaultColumnWidth;
}
if (cp.StyleIndex > 0) col.Style = cp.StyleIndex;
if (cp.Hidden) col.Hidden = cp.Hidden;
if (cp.BestFit) col.BestFit = cp.BestFit;
if (cp.Phonetic) col.Phonetic = cp.Phonetic;
if (cp.OutlineLevel > 0) col.OutlineLevel = cp.OutlineLevel;
if (cp.Collapsed) col.Collapsed = cp.Collapsed;
}
else
{
colmax = iCurrentColumnIndex;
}
}
// there's always a "leftover" column
col.Max = (uint)colmax;
cols.Append(col);
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
if (child is SheetProperties || child is SheetDimension || child is SheetViews || child is SheetFormatProperties)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(cols, oxe);
}
else
{
wsp.Worksheet.PrependChild(cols);
}
}
#endregion
SheetData sd = new SheetData();
iCellDataKey = 0;
for (iRowKey = 0; iRowKey < listRowIndex.Count; ++iRowKey)
{
iRowIndex = listRowIndex[iRowKey];
if (slws.RowProperties.ContainsKey(iRowIndex))
{
r = slws.RowProperties[iRowIndex].ToRow();
r.RowIndex = (uint)iRowIndex;
}
else
{
r = new Row();
r.RowIndex = (uint)iRowIndex;
}
while (iCellDataKey < listCellRefKeys.Count)
{
pt = listCellRefKeys[iCellDataKey];
if (pt.RowIndex == iRowIndex)
{
c = slws.Cells[pt];
r.Append(c.ToCell(SLTool.ToCellReference(pt.RowIndex, pt.ColumnIndex)));
++iCellDataKey;
}
else
{
break;
}
}
sd.Append(r);
}
wsp.Worksheet.RemoveAllChildren<SheetData>();
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
if (child is SheetProperties || child is SheetDimension || child is SheetViews || child is SheetFormatProperties || child is Columns)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(sd, oxe);
}
else
{
wsp.Worksheet.PrependChild(sd);
}
#region Sheet protection
if (wsp.Worksheet.Elements<SheetProtection>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<SheetProtection>();
}
if (slws.HasSheetProtection)
{
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(slws.SheetProtection.ToSheetProtection(), oxe);
}
else
{
wsp.Worksheet.PrependChild(slws.SheetProtection.ToSheetProtection());
}
}
#endregion
#region AutoFilter
if (wsp.Worksheet.Elements<AutoFilter>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<AutoFilter>();
}
if (slws.HasAutoFilter)
{
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(slws.AutoFilter.ToAutoFilter(), oxe);
}
else
{
wsp.Worksheet.PrependChild(slws.AutoFilter.ToAutoFilter());
}
}
#endregion
#region Filling merge cells
if (wsp.Worksheet.Elements<MergeCells>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<MergeCells>();
}
if (slws.MergeCells.Count > 0)
{
MergeCells mcs = new MergeCells() { Count = (uint)slws.MergeCells.Count };
for (i = 0; i < slws.MergeCells.Count; ++i)
{
mcs.Append(slws.MergeCells[i].ToMergeCell());
}
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(mcs, oxe);
}
else
{
wsp.Worksheet.PrependChild(mcs);
}
}
#endregion
#region Conditional Formatting
if (wsp.Worksheet.Elements<ConditionalFormatting>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<ConditionalFormatting>();
}
if (slws.ConditionalFormattings.Count > 0)
{
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
for (i = slws.ConditionalFormattings.Count - 1; i >= 0; --i)
{
wsp.Worksheet.InsertAfter(slws.ConditionalFormattings[i].ToConditionalFormatting(), oxe);
}
}
else
{
for (i = slws.ConditionalFormattings.Count - 1; i >= 0; --i)
{
wsp.Worksheet.PrependChild(slws.ConditionalFormattings[i].ToConditionalFormatting());
}
}
}
#endregion
#region DataValidations
if (wsp.Worksheet.Elements<DataValidations>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<DataValidations>();
}
if (slws.DataValidations.Count > 0)
{
DataValidations dvs = new DataValidations();
if (slws.DataValidationDisablePrompts) dvs.DisablePrompts = slws.DataValidationDisablePrompts;
if (slws.DataValidationXWindow != null) dvs.XWindow = slws.DataValidationXWindow.Value;
if (slws.DataValidationYWindow != null) dvs.YWindow = slws.DataValidationYWindow.Value;
dvs.Count = (uint)slws.DataValidations.Count;
foreach (SLDataValidation dv in slws.DataValidations)
{
dvs.Append(dv.ToDataValidation());
}
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(dvs, oxe);
}
else
{
wsp.Worksheet.PrependChild(dvs);
}
}
#endregion
#region Hyperlinks
if (wsp.Worksheet.Elements<Hyperlinks>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<Hyperlinks>();
}
if (slws.Hyperlinks.Count > 0)
{
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting
|| child is DataValidations)
{
oxe = child;
bFound = true;
}
}
Hyperlinks hls = new Hyperlinks();
HyperlinkRelationship hlrel;
foreach (SLHyperlink hl in slws.Hyperlinks)
{
if (hl.IsExternal && hl.IsNew)
{
hlrel = wsp.AddHyperlinkRelationship(new Uri(hl.HyperlinkUri, hl.HyperlinkUriKind), true);
hl.Id = hlrel.Id;
}
hls.Append(hl.ToHyperlink());
}
if (bFound)
{
wsp.Worksheet.InsertAfter(hls, oxe);
}
else
{
wsp.Worksheet.PrependChild(hls);
}
}
#endregion
#region PrintOptions
if (wsp.Worksheet.Elements<PrintOptions>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<PrintOptions>();
}
if (slws.PageSettings.HasPrintOptions)
{
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting
|| child is DataValidations || child is Hyperlinks)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(slws.PageSettings.ExportPrintOptions(), oxe);
}
else
{
wsp.Worksheet.PrependChild(slws.PageSettings.ExportPrintOptions());
}
}
#endregion
#region PageMargins
if (wsp.Worksheet.Elements<PageMargins>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<PageMargins>();
}
if (slws.PageSettings.HasPageMargins)
{
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting
|| child is DataValidations || child is Hyperlinks || child is PrintOptions)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(slws.PageSettings.ExportPageMargins(), oxe);
}
else
{
wsp.Worksheet.PrependChild(slws.PageSettings.ExportPageMargins());
}
}
#endregion
#region PageSetup
if (wsp.Worksheet.Elements<PageSetup>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<PageSetup>();
}
if (slws.PageSettings.HasPageSetup)
{
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting
|| child is DataValidations || child is Hyperlinks || child is PrintOptions
|| child is PageMargins)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(slws.PageSettings.ExportPageSetup(), oxe);
}
else
{
wsp.Worksheet.PrependChild(slws.PageSettings.ExportPageSetup());
}
}
#endregion
#region HeaderFooter
if (wsp.Worksheet.Elements<HeaderFooter>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<HeaderFooter>();
}
if (slws.PageSettings.HasHeaderFooter)
{
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting
|| child is DataValidations || child is Hyperlinks || child is PrintOptions
|| child is PageMargins || child is PageSetup)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(slws.PageSettings.ExportHeaderFooter(), oxe);
}
else
{
wsp.Worksheet.PrependChild(slws.PageSettings.ExportHeaderFooter());
}
}
#endregion
#region RowBreaks
if (wsp.Worksheet.Elements<RowBreaks>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<RowBreaks>();
}
if (slws.RowBreaks.Count > 0)
{
List<int> bkkeys = slws.RowBreaks.Keys.ToList<int>();
bkkeys.Sort();
RowBreaks rowbk = new RowBreaks();
int bkmancount = 0;
foreach (int bkindex in bkkeys)
{
if (slws.RowBreaks[bkindex].ManualPageBreak) ++bkmancount;
rowbk.Append(slws.RowBreaks[bkindex].ToBreak());
}
rowbk.Count = (uint)slws.RowBreaks.Count;
rowbk.ManualBreakCount = (uint)bkmancount;
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting
|| child is DataValidations || child is Hyperlinks || child is PrintOptions
|| child is PageMargins || child is PageSetup || child is HeaderFooter)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(rowbk, oxe);
}
else
{
wsp.Worksheet.PrependChild(rowbk);
}
}
#endregion
#region ColumnBreaks
if (wsp.Worksheet.Elements<ColumnBreaks>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<ColumnBreaks>();
}
if (slws.ColumnBreaks.Count > 0)
{
List<int> bkkeys = slws.ColumnBreaks.Keys.ToList<int>();
bkkeys.Sort();
ColumnBreaks colbk = new ColumnBreaks();
int bkmancount = 0;
foreach (int bkindex in bkkeys)
{
if (slws.ColumnBreaks[bkindex].ManualPageBreak) ++bkmancount;
colbk.Append(slws.ColumnBreaks[bkindex].ToBreak());
}
colbk.Count = (uint)slws.ColumnBreaks.Count;
colbk.ManualBreakCount = (uint)bkmancount;
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting
|| child is DataValidations || child is Hyperlinks || child is PrintOptions
|| child is PageMargins || child is PageSetup || child is HeaderFooter
|| child is RowBreaks)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(colbk, oxe);
}
else
{
wsp.Worksheet.PrependChild(colbk);
}
}
#endregion
#region Drawing
// these are "new" charts and pictures added
if (slws.Charts.Count > 0 || slws.Pictures.Count > 0)
{
// if the length > 0, then we assume there's already an existing DrawingsPart
if (slws.DrawingId.Length > 0)
{
WriteImageParts(wsp.DrawingsPart);
}
else
{
DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
dp.WorksheetDrawing = new Xdr.WorksheetDrawing();
dp.WorksheetDrawing.AddNamespaceDeclaration("xdr", SLConstants.NamespaceXdr);
dp.WorksheetDrawing.AddNamespaceDeclaration("a", SLConstants.NamespaceA);
DocumentFormat.OpenXml.Spreadsheet.Drawing drawing = new DocumentFormat.OpenXml.Spreadsheet.Drawing();
drawing.Id = wsp.GetIdOfPart(dp);
WriteImageParts(dp);
// NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove
// from check below?
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting
|| child is DataValidations || child is Hyperlinks || child is PrintOptions
|| child is PageMargins || child is PageSetup || child is HeaderFooter
|| child is RowBreaks || child is ColumnBreaks || child is CustomProperties
|| child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(drawing, oxe);
}
else
{
wsp.Worksheet.PrependChild(drawing);
}
}
}
#endregion
#region LegacyDrawing
if (slws.Comments.Count > 0)
{
// we're going to do this only if there are no comments and VML already
if (wsp.WorksheetCommentsPart == null
&& wsp.Worksheet.Elements<LegacyDrawing>().Count() == 0)
{
WorksheetCommentsPart wcp = wsp.AddNewPart<WorksheetCommentsPart>();
VmlDrawingPart vdp = wsp.AddNewPart<VmlDrawingPart>();
WriteCommentPart(wcp, vdp);
LegacyDrawing ldrawing = new LegacyDrawing();
ldrawing.Id = wsp.GetIdOfPart(vdp);
// NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove
// from check below?
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting
|| child is DataValidations || child is Hyperlinks || child is PrintOptions
|| child is PageMargins || child is PageSetup || child is HeaderFooter
|| child is RowBreaks || child is ColumnBreaks || child is CustomProperties
|| child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/
|| child is DocumentFormat.OpenXml.Spreadsheet.Drawing)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(ldrawing, oxe);
}
else
{
wsp.Worksheet.PrependChild(ldrawing);
}
}
}
#endregion
#region Picture
if (wsp.Worksheet.Elements<Picture>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<Picture>();
}
if (slws.BackgroundPictureId.Length > 0 || slws.BackgroundPictureDataIsInFile != null)
{
Picture pic = new Picture();
if (slws.BackgroundPictureId.Length > 0)
{
pic.Id = slws.BackgroundPictureId;
}
else if (slws.BackgroundPictureDataIsInFile != null)
{
ImagePart imgp = wsp.AddImagePart(slws.BackgroundPictureImagePartType);
if (slws.BackgroundPictureDataIsInFile.Value)
{
using (FileStream fs = new FileStream(slws.BackgroundPictureFileName, FileMode.Open))
{
imgp.FeedData(fs);
}
}
else
{
using (MemoryStream ms = new MemoryStream(slws.BackgroundPictureByteData))
{
imgp.FeedData(ms);
}
}
pic.Id = wsp.GetIdOfPart(imgp);
}
// NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove
// from check below?
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting
|| child is DataValidations || child is Hyperlinks || child is PrintOptions
|| child is PageMargins || child is PageSetup || child is HeaderFooter
|| child is RowBreaks || child is ColumnBreaks || child is CustomProperties
|| child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/
|| child is DocumentFormat.OpenXml.Spreadsheet.Drawing
|| child is LegacyDrawing || child is LegacyDrawingHeaderFooter)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(pic, oxe);
}
else
{
wsp.Worksheet.PrependChild(pic);
}
}
#endregion
#region Tables
if (wsp.Worksheet.Elements<TableParts>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<TableParts>();
}
if (slws.Tables.Count > 0)
{
TableParts tps = new TableParts() { Count = (uint)slws.Tables.Count };
TableDefinitionPart tdp;
string sRelID = string.Empty;
foreach (SLTable t in slws.Tables)
{
if (t.IsNewTable)
{
if (t.RelationshipID.Length > 0)
{
// is a modified existing table
tdp = (TableDefinitionPart)wsp.GetPartById(t.RelationshipID);
tdp.Table = t.ToTable();
sRelID = t.RelationshipID;
}
else
{
// is a completely new table
tdp = wsp.AddNewPart<TableDefinitionPart>();
tdp.Table = t.ToTable();
sRelID = wsp.GetIdOfPart(tdp);
}
}
else
{
// if it's an existing table with no modifications,
// don't need to do anything to the XML content.
tdp = (TableDefinitionPart)wsp.GetPartById(t.RelationshipID);
sRelID = t.RelationshipID;
}
tps.Append(new TablePart() { Id = sRelID });
}
// NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove
// from check below?
bFound = false;
oxe = wsp.Worksheet.FirstChild;
foreach (var child in wsp.Worksheet.ChildElements)
{
// start with SheetData because it's a required child element
if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection
|| child is ProtectedRanges || child is Scenarios || child is AutoFilter
|| child is SortState || child is DataConsolidate || child is CustomSheetViews
|| child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting
|| child is DataValidations || child is Hyperlinks || child is PrintOptions
|| child is PageMargins || child is PageSetup || child is HeaderFooter
|| child is RowBreaks || child is ColumnBreaks || child is CustomProperties
|| child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/
|| child is DocumentFormat.OpenXml.Spreadsheet.Drawing
|| child is LegacyDrawing || child is LegacyDrawingHeaderFooter
|| child is Picture || child is OleObjects || child is Controls
|| child is WebPublishItems)
{
oxe = child;
bFound = true;
}
}
if (bFound)
{
wsp.Worksheet.InsertAfter(tps, oxe);
}
else
{
wsp.Worksheet.PrependChild(tps);
}
}
#endregion
#region 2010 Conditional formatting, Sparklines and possibly other extensions
WorksheetExtensionList wsextlist;
WorksheetExtension wsext;
List<WorksheetExtension> listExtensions = new List<WorksheetExtension>();
slws.RefreshSparklineGroups();
if (wsp.Worksheet.Elements<WorksheetExtensionList>().Count() > 0)
{
wsextlist = wsp.Worksheet.Elements<WorksheetExtensionList>().First();
foreach (var wsextchild in wsextlist.ChildElements)
{
if (wsextchild is WorksheetExtension)
{
wsext = (WorksheetExtension)wsextchild;
wsext.RemoveAllChildren<X14.ConditionalFormattings>();
wsext.RemoveAllChildren<X14.SparklineGroups>();
// there might be other extension types, like slicers (erhmahgerd...).
if (wsext.ChildElements.Count > 0)
{
listExtensions.Add((WorksheetExtension)wsext.CloneNode(true));
}
}
}
wsp.Worksheet.RemoveAllChildren<WorksheetExtensionList>();
}
if (slws.ConditionalFormattings2010.Count > 0
|| slws.SparklineGroups.Count > 0
|| listExtensions.Count > 0)
{
wsextlist = new WorksheetExtensionList();
foreach (WorksheetExtension ext in listExtensions)
{
// be extra safe by cloning again to avoid pass-by-reference. Deeply.
wsextlist.Append((WorksheetExtension)ext.CloneNode(true));
}
if (slws.ConditionalFormattings2010.Count > 0)
{
// this is important! Apparently extensions are tied to a URI that Microsoft uses.
wsext = new WorksheetExtension() { Uri = SLConstants.ConditionalFormattingExtensionUri };
wsext.AddNamespaceDeclaration("x14", SLConstants.NamespaceX14);
X14.ConditionalFormattings cfs = new X14.ConditionalFormattings();
foreach (SLConditionalFormatting2010 cfr2010 in slws.ConditionalFormattings2010)
{
cfs.Append(cfr2010.ToConditionalFormatting());
}
wsext.Append(cfs);
wsextlist.Append(wsext);
}
if (slws.SparklineGroups.Count > 0)
{
// this is important! Apparently extensions are tied to a URI that Microsoft uses.
wsext = new WorksheetExtension() { Uri = SLConstants.SparklineExtensionUri };
wsext.AddNamespaceDeclaration("x14", SLConstants.NamespaceX14);
X14.SparklineGroups spkgrps = new X14.SparklineGroups();
spkgrps.AddNamespaceDeclaration("xm", SLConstants.NamespaceXm);
foreach (SLSparklineGroup spkgrp in slws.SparklineGroups)
{
spkgrps.Append(spkgrp.ToSparklineGroup());
}
wsext.Append(spkgrps);
wsextlist.Append(wsext);
}
// WorksheetExtensionList is the very last element possible.
// So we can just append it because everything else is in front.
wsp.Worksheet.Append(wsextlist);
}
#endregion
wsp.Worksheet.Save();
// end of writing for existing worksheet
}
else
{
// start of writing for new worksheet
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
gsSelectedWorksheetRelationshipID = wbp.GetIdOfPart(wsp);
foreach (SLSheet s in slwb.Sheets)
{
if (s.Name.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
{
s.Id = gsSelectedWorksheetRelationshipID;
break;
}
}
if (slws.ForceCustomRowColumnDimensionsSplitting)
{
slws.ToggleCustomRowColumnDimension(true);
}
List<OpenXmlAttribute> oxa;
OpenXmlWriter oxw = OpenXmlWriter.Create(wsp);
oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("xmlns:r", null, SLConstants.NamespaceRelationships));
if (slws.ConditionalFormattings2010.Count > 0
|| slws.SparklineGroups.Count > 0)
{
oxa.Add(new OpenXmlAttribute("xmlns:x14", null, SLConstants.NamespaceX14));
oxa.Add(new OpenXmlAttribute("xmlns:xm", null, SLConstants.NamespaceXm));
oxa.Add(new OpenXmlAttribute("xmlns:mc", null, SLConstants.NamespaceMc));
oxa.Add(new OpenXmlAttribute("xmlns:x14ac", null, SLConstants.NamespaceX14ac));
oxa.Add(new OpenXmlAttribute("mc", "Ignorable", SLConstants.NamespaceMc, "x14ac"));
oxw.WriteStartElement(new Worksheet(), oxa);
}
else
{
oxw.WriteStartElement(new Worksheet(), oxa);
}
if (slws.PageSettings.HasSheetProperties)
{
oxw.WriteElement(slws.PageSettings.SheetProperties.ToSheetProperties());
}
oxw.WriteElement(new SheetDimension() { Reference = sDimensionCellRange });
if (slws.SheetViews.Count > 0)
{
oxw.WriteStartElement(new SheetViews());
foreach (SLSheetView sv in slws.SheetViews)
{
oxw.WriteElement(sv.ToSheetView());
}
oxw.WriteEndElement();
}
oxa = new List<OpenXmlAttribute>();
if (slws.SheetFormatProperties.BaseColumnWidth != null && slws.SheetFormatProperties.BaseColumnWidth.Value != 8)
{
oxa.Add(new OpenXmlAttribute("baseColWidth", null, slws.SheetFormatProperties.BaseColumnWidth.Value.ToString(CultureInfo.InvariantCulture)));
}
if (slws.SheetFormatProperties.HasDefaultColumnWidth)
{
oxa.Add(new OpenXmlAttribute("defaultColWidth", null, slws.SheetFormatProperties.DefaultColumnWidth.ToString(CultureInfo.InvariantCulture)));
}
oxa.Add(new OpenXmlAttribute("defaultRowHeight", null, slws.SheetFormatProperties.DefaultRowHeight.ToString("0.####", CultureInfo.InvariantCulture)));
if (slws.SheetFormatProperties.CustomHeight != null && slws.SheetFormatProperties.CustomHeight.Value)
{
oxa.Add(new OpenXmlAttribute("customHeight", null, "1"));
}
if (slws.SheetFormatProperties.ZeroHeight != null && slws.SheetFormatProperties.ZeroHeight.Value)
{
oxa.Add(new OpenXmlAttribute("zeroHeight", null, "1"));
}
if (slws.SheetFormatProperties.ThickTop != null && slws.SheetFormatProperties.ThickTop.Value)
{
oxa.Add(new OpenXmlAttribute("thickTop", null, "1"));
}
if (slws.SheetFormatProperties.ThickBottom != null && slws.SheetFormatProperties.ThickBottom.Value)
{
oxa.Add(new OpenXmlAttribute("thickBottom", null, "1"));
}
if (slws.SheetFormatProperties.OutlineLevelRow != null && slws.SheetFormatProperties.OutlineLevelRow.Value > 0)
{
oxa.Add(new OpenXmlAttribute("outlineLevelRow", null, slws.SheetFormatProperties.OutlineLevelRow.Value.ToString(CultureInfo.InvariantCulture)));
}
if (slws.SheetFormatProperties.OutlineLevelColumn != null && slws.SheetFormatProperties.OutlineLevelColumn.Value > 0)
{
oxa.Add(new OpenXmlAttribute("outlineLevelCol", null, slws.SheetFormatProperties.OutlineLevelColumn.Value.ToString(CultureInfo.InvariantCulture)));
}
oxw.WriteStartElement(new SheetFormatProperties(), oxa);
oxw.WriteEndElement();
#region Filling Columns
if (slws.ColumnProperties.Count > 0)
{
oxw.WriteStartElement(new Columns());
int iPreviousColumnIndex = listColumnIndex[0];
int iCurrentColumnIndex = iPreviousColumnIndex;
string sCollectiveColumnData = string.Empty;
string sCurrentColumnData = string.Empty;
int colmin, colmax;
colmin = colmax = iCurrentColumnIndex;
cp = slws.ColumnProperties[iCurrentColumnIndex];
sCollectiveColumnData = cp.ToHash();
oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("min", null, colmin.ToString(CultureInfo.InvariantCulture)));
// max is left to the end because we're calculating it
//oxa.Add(new OpenXmlAttribute("max", null, colmax.ToString(CultureInfo.InvariantCulture)));
if (cp.HasWidth)
{
oxa.Add(new OpenXmlAttribute("width", null, cp.Width.ToString(CultureInfo.InvariantCulture)));
oxa.Add(new OpenXmlAttribute("customWidth", null, "1"));
}
else
{
oxa.Add(new OpenXmlAttribute("width", null, slws.SheetFormatProperties.DefaultColumnWidth.ToString(CultureInfo.InvariantCulture)));
}
if (cp.StyleIndex > 0) oxa.Add(new OpenXmlAttribute("style", null, cp.StyleIndex.ToString(CultureInfo.InvariantCulture)));
if (cp.Hidden != false) oxa.Add(new OpenXmlAttribute("hidden", null, "1"));
if (cp.BestFit != false) oxa.Add(new OpenXmlAttribute("bestFit", null, "1"));
if (cp.Phonetic != false) oxa.Add(new OpenXmlAttribute("phonetic", null, "1"));
if (cp.OutlineLevel > 0) oxa.Add(new OpenXmlAttribute("outlineLevel", null, cp.OutlineLevel.ToString(CultureInfo.InvariantCulture)));
if (cp.Collapsed != false) oxa.Add(new OpenXmlAttribute("collapsed", null, "1"));
for (i = 1; i < listColumnIndex.Count; ++i)
{
iPreviousColumnIndex = iCurrentColumnIndex;
iCurrentColumnIndex = listColumnIndex[i];
cp = slws.ColumnProperties[iCurrentColumnIndex];
sCurrentColumnData = cp.ToHash();
if ((iCurrentColumnIndex != (iPreviousColumnIndex + 1)) || (sCollectiveColumnData != sCurrentColumnData))
{
oxa.Add(new OpenXmlAttribute("max", null, colmax.ToString(CultureInfo.InvariantCulture)));
oxw.WriteStartElement(new Column(), oxa);
oxw.WriteEndElement();
colmin = iCurrentColumnIndex;
colmax = iCurrentColumnIndex;
sCollectiveColumnData = sCurrentColumnData;
oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("min", null, colmin.ToString(CultureInfo.InvariantCulture)));
if (cp.HasWidth)
{
oxa.Add(new OpenXmlAttribute("width", null, cp.Width.ToString(CultureInfo.InvariantCulture)));
oxa.Add(new OpenXmlAttribute("customWidth", null, "1"));
}
else
{
oxa.Add(new OpenXmlAttribute("width", null, slws.SheetFormatProperties.DefaultColumnWidth.ToString(CultureInfo.InvariantCulture)));
}
if (cp.StyleIndex > 0) oxa.Add(new OpenXmlAttribute("style", null, cp.StyleIndex.ToString(CultureInfo.InvariantCulture)));
if (cp.Hidden != false) oxa.Add(new OpenXmlAttribute("hidden", null, "1"));
if (cp.BestFit != false) oxa.Add(new OpenXmlAttribute("bestFit", null, "1"));
if (cp.Phonetic != false) oxa.Add(new OpenXmlAttribute("phonetic", null, "1"));
if (cp.OutlineLevel > 0) oxa.Add(new OpenXmlAttribute("outlineLevel", null, cp.OutlineLevel.ToString(CultureInfo.InvariantCulture)));
if (cp.Collapsed != false) oxa.Add(new OpenXmlAttribute("collapsed", null, "1"));
}
else
{
colmax = iCurrentColumnIndex;
}
}
// there's always a "leftover" column
oxa.Add(new OpenXmlAttribute("max", null, colmax.ToString(CultureInfo.InvariantCulture)));
oxw.WriteStartElement(new Column(), oxa);
oxw.WriteEndElement();
oxw.WriteEndElement();
}
#endregion
oxw.WriteStartElement(new SheetData());
iCellDataKey = 0;
for (iRowKey = 0; iRowKey < listRowIndex.Count; ++iRowKey)
{
iRowIndex = listRowIndex[iRowKey];
oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("r", null, iRowIndex.ToString(CultureInfo.InvariantCulture)));
if (slws.RowProperties.ContainsKey(iRowIndex))
{
rp = slws.RowProperties[iRowIndex];
if (rp.StyleIndex > 0)
{
oxa.Add(new OpenXmlAttribute("s", null, rp.StyleIndex.ToString(CultureInfo.InvariantCulture)));
oxa.Add(new OpenXmlAttribute("customFormat", null, "1"));
}
if (rp.HasHeight)
{
oxa.Add(new OpenXmlAttribute("ht", null, rp.Height.ToString(CultureInfo.InvariantCulture)));
}
if (rp.Hidden != false)
{
oxa.Add(new OpenXmlAttribute("hidden", null, "1"));
}
if (rp.CustomHeight)
{
oxa.Add(new OpenXmlAttribute("customHeight", null, "1"));
}
if (rp.OutlineLevel > 0)
{
oxa.Add(new OpenXmlAttribute("outlineLevel", null, rp.OutlineLevel.ToString(CultureInfo.InvariantCulture)));
}
if (rp.Collapsed != false)
{
oxa.Add(new OpenXmlAttribute("collapsed", null, "1"));
}
if (rp.ThickTop != false)
{
oxa.Add(new OpenXmlAttribute("thickTop", null, "1"));
}
if (rp.ThickBottom != false)
{
oxa.Add(new OpenXmlAttribute("thickBot", null, "1"));
}
if (rp.ShowPhonetic != false)
{
oxa.Add(new OpenXmlAttribute("ph", null, "1"));
}
}
oxw.WriteStartElement(new Row(), oxa);
while (iCellDataKey < listCellRefKeys.Count)
{
pt = listCellRefKeys[iCellDataKey];
if (pt.RowIndex == iRowIndex)
{
c = slws.Cells[pt];
oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("r", null, SLTool.ToCellReference(pt.RowIndex, pt.ColumnIndex)));
if (c.StyleIndex > 0)
{
oxa.Add(new OpenXmlAttribute("s", null, c.StyleIndex.ToString(CultureInfo.InvariantCulture)));
}
// number type is default
switch (c.DataType)
{
case CellValues.Boolean:
oxa.Add(new OpenXmlAttribute("t", null, "b"));
break;
case CellValues.Date:
oxa.Add(new OpenXmlAttribute("t", null, "d"));
break;
case CellValues.Error:
oxa.Add(new OpenXmlAttribute("t", null, "e"));
break;
case CellValues.InlineString:
oxa.Add(new OpenXmlAttribute("t", null, "inlineStr"));
break;
case CellValues.SharedString:
oxa.Add(new OpenXmlAttribute("t", null, "s"));
break;
case CellValues.String:
oxa.Add(new OpenXmlAttribute("t", null, "str"));
break;
}
if (c.CellMetaIndex > 0)
{
oxa.Add(new OpenXmlAttribute("cm", null, c.CellMetaIndex.ToString(CultureInfo.InvariantCulture)));
}
if (c.ValueMetaIndex > 0)
{
oxa.Add(new OpenXmlAttribute("vm", null, c.ValueMetaIndex.ToString(CultureInfo.InvariantCulture)));
}
if (c.ShowPhonetic != false)
{
oxa.Add(new OpenXmlAttribute("ph", null, "1"));
}
oxw.WriteStartElement(new Cell(), oxa);
if (c.CellFormula != null)
{
oxw.WriteElement(c.CellFormula.ToCellFormula());
}
if (c.CellText != null)
{
if (c.CellText.Length > 0)
{
if (c.ToPreserveSpace)
{
oxw.WriteElement(new CellValue(c.CellText)
{
Space = SpaceProcessingModeValues.Preserve
});
}
else
{
oxw.WriteElement(new CellValue(c.CellText));
}
}
}
else
{
if (c.DataType == CellValues.Number)
{
oxw.WriteElement(new CellValue(c.NumericValue.ToString(CultureInfo.InvariantCulture)));
}
else if (c.DataType == CellValues.SharedString)
{
oxw.WriteElement(new CellValue(c.NumericValue.ToString("f0", CultureInfo.InvariantCulture)));
}
else if (c.DataType == CellValues.Boolean)
{
if (c.NumericValue > 0.5) oxw.WriteElement(new CellValue("1"));
else oxw.WriteElement(new CellValue("0"));
}
}
oxw.WriteEndElement();
++iCellDataKey;
}
else
{
break;
}
}
oxw.WriteEndElement();
}
oxw.WriteEndElement();
#region Sheet protection
if (slws.HasSheetProtection)
{
oxw.WriteElement(slws.SheetProtection.ToSheetProtection());
}
#endregion
#region AutoFilter
if (slws.HasAutoFilter)
{
oxw.WriteElement(slws.AutoFilter.ToAutoFilter());
}
#endregion
#region Filling merge cells
if (slws.MergeCells.Count > 0)
{
oxw.WriteStartElement(new MergeCells() { Count = (uint)slws.MergeCells.Count });
for (i = 0; i < slws.MergeCells.Count; ++i)
{
oxw.WriteElement(slws.MergeCells[i].ToMergeCell());
}
oxw.WriteEndElement();
}
#endregion
#region Conditional Formatting
if (slws.ConditionalFormattings.Count > 0)
{
for (i = 0; i < slws.ConditionalFormattings.Count; ++i)
{
oxw.WriteElement(slws.ConditionalFormattings[i].ToConditionalFormatting());
}
}
#endregion
#region DataValidations
if (slws.DataValidations.Count > 0)
{
DataValidations dvs = new DataValidations();
if (slws.DataValidationDisablePrompts) dvs.DisablePrompts = slws.DataValidationDisablePrompts;
if (slws.DataValidationXWindow != null) dvs.XWindow = slws.DataValidationXWindow.Value;
if (slws.DataValidationYWindow != null) dvs.YWindow = slws.DataValidationYWindow.Value;
dvs.Count = (uint)slws.DataValidations.Count;
foreach (SLDataValidation dv in slws.DataValidations)
{
dvs.Append(dv.ToDataValidation());
}
oxw.WriteElement(dvs);
}
#endregion
#region Hyperlinks
if (slws.Hyperlinks.Count > 0)
{
Hyperlinks hls = new Hyperlinks();
HyperlinkRelationship hlrel;
foreach (SLHyperlink hl in slws.Hyperlinks)
{
if (hl.IsExternal && hl.IsNew)
{
hlrel = wsp.AddHyperlinkRelationship(new Uri(hl.HyperlinkUri, hl.HyperlinkUriKind), true);
hl.Id = hlrel.Id;
}
hls.Append(hl.ToHyperlink());
}
oxw.WriteElement(hls);
}
#endregion
#region PrintOptions
if (slws.PageSettings.HasPrintOptions)
{
oxw.WriteElement(slws.PageSettings.ExportPrintOptions());
}
#endregion
#region PageMargins
if (slws.PageSettings.HasPageMargins)
{
oxw.WriteElement(slws.PageSettings.ExportPageMargins());
}
#endregion
#region PageSetup
if (slws.PageSettings.HasPageSetup)
{
oxw.WriteElement(slws.PageSettings.ExportPageSetup());
}
#endregion
#region HeaderFooter
if (slws.PageSettings.HasHeaderFooter)
{
oxw.WriteElement(slws.PageSettings.ExportHeaderFooter());
}
#endregion
#region RowBreaks
if (slws.RowBreaks.Count > 0)
{
List<int> bkkeys = slws.RowBreaks.Keys.ToList<int>();
bkkeys.Sort();
// if it's a new worksheet, then all breaks are considered manual
oxw.WriteStartElement(new RowBreaks()
{
Count = (uint)slws.RowBreaks.Count,
ManualBreakCount = (uint)slws.RowBreaks.Count
});
foreach (int bkindex in bkkeys)
{
oxw.WriteElement(slws.RowBreaks[bkindex].ToBreak());
}
oxw.WriteEndElement();
}
#endregion
#region ColumnBreaks
if (slws.ColumnBreaks.Count > 0)
{
List<int> bkkeys = slws.ColumnBreaks.Keys.ToList<int>();
bkkeys.Sort();
// if it's a new worksheet, then all breaks are considered manual
oxw.WriteStartElement(new ColumnBreaks()
{
Count = (uint)slws.ColumnBreaks.Count,
ManualBreakCount = (uint)slws.ColumnBreaks.Count
});
foreach (int bkindex in bkkeys)
{
oxw.WriteElement(slws.ColumnBreaks[bkindex].ToBreak());
}
oxw.WriteEndElement();
}
#endregion
#region Drawing
// these are "new" charts and pictures added
if (slws.Charts.Count > 0 || slws.Pictures.Count > 0)
{
DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
dp.WorksheetDrawing = new Xdr.WorksheetDrawing();
dp.WorksheetDrawing.AddNamespaceDeclaration("xdr", SLConstants.NamespaceXdr);
dp.WorksheetDrawing.AddNamespaceDeclaration("a", SLConstants.NamespaceA);
oxw.WriteElement(new DocumentFormat.OpenXml.Spreadsheet.Drawing() { Id = wsp.GetIdOfPart(dp) });
WriteImageParts(dp);
}
#endregion
#region LegacyDrawing
// these are "new" comments added
if (slws.Comments.Count > 0)
{
WorksheetCommentsPart wcp = wsp.AddNewPart<WorksheetCommentsPart>();
VmlDrawingPart vdp = wsp.AddNewPart<VmlDrawingPart>();
WriteCommentPart(wcp, vdp);
oxw.WriteElement(new LegacyDrawing() { Id = wsp.GetIdOfPart(vdp) });
}
#endregion
#region Picture
if (slws.BackgroundPictureDataIsInFile != null)
{
ImagePart imgp = wsp.AddImagePart(slws.BackgroundPictureImagePartType);
if (slws.BackgroundPictureDataIsInFile.Value)
{
using (FileStream fs = new FileStream(slws.BackgroundPictureFileName, FileMode.Open))
{
imgp.FeedData(fs);
}
}
else
{
using (MemoryStream ms = new MemoryStream(slws.BackgroundPictureByteData))
{
imgp.FeedData(ms);
}
}
oxw.WriteElement(new Picture() { Id = wsp.GetIdOfPart(imgp) });
}
#endregion
if (slws.Tables.Count > 0)
{
// If it's a new worksheet, ALL tables are new tables...
oxw.WriteStartElement(new TableParts() { Count = (uint)slws.Tables.Count });
TableDefinitionPart tdp;
foreach (SLTable t in slws.Tables)
{
tdp = wsp.AddNewPart<TableDefinitionPart>();
tdp.Table = t.ToTable();
oxw.WriteElement(new TablePart() { Id = wsp.GetIdOfPart(tdp) });
}
oxw.WriteEndElement();
}
#region 2010 Conditional formatting, Sparklines and possibly other extensions
slws.RefreshSparklineGroups();
if (slws.ConditionalFormattings2010.Count > 0
|| slws.SparklineGroups.Count > 0)
{
oxw.WriteStartElement(new WorksheetExtensionList());
if (slws.ConditionalFormattings2010.Count > 0)
{
oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("xmlns:x14", null, SLConstants.NamespaceX14));
// this is important! Apparently extensions are tied to a URI that Microsoft uses.
oxa.Add(new OpenXmlAttribute("uri", null, SLConstants.ConditionalFormattingExtensionUri));
oxw.WriteStartElement(new WorksheetExtension(), oxa);
oxw.WriteStartElement(new X14.ConditionalFormattings());
foreach (SLConditionalFormatting2010 cf2010 in slws.ConditionalFormattings2010)
{
oxw.WriteElement(cf2010.ToConditionalFormatting());
}
oxw.WriteEndElement();
oxw.WriteEndElement();
}
if (slws.SparklineGroups.Count > 0)
{
oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("xmlns:x14", null, SLConstants.NamespaceX14));
// this is important! Apparently extensions are tied to a URI that Microsoft uses.
oxa.Add(new OpenXmlAttribute("uri", null, SLConstants.SparklineExtensionUri));
oxw.WriteStartElement(new WorksheetExtension(), oxa);
oxa = new List<OpenXmlAttribute>();
oxa.Add(new OpenXmlAttribute("xmlns:xm", null, SLConstants.NamespaceXm));
oxw.WriteStartElement(new X14.SparklineGroups(), oxa);
foreach (SLSparklineGroup spkgrp in slws.SparklineGroups)
{
oxw.WriteElement(spkgrp.ToSparklineGroup());
}
oxw.WriteEndElement();
oxw.WriteEndElement();
}
oxw.WriteEndElement();
}
#endregion
oxw.WriteEndElement();
oxw.Dispose();
// end of writing for new worksheet
}
}
/// <summary>
/// Adds all the comments defined in the List to the current worksheet.
/// </summary>
/// <param name="worksheetPart">Worksheet Part of file.</param>
/// <param name="commentsToAddList">List of CellComment which contain cell coordinates and the text value to set as comment.</param>
public static void InsertComments(WorksheetPart worksheetPart, List <CellComment> commentsToAddList)
{
if (commentsToAddList.Any())
{
string commentsVmlXml = string.Empty;
// Create all the comment VML Shape XML
foreach (var commentToAdd in commentsToAddList)
{
commentsVmlXml += GetCommentVMLShapeXML(ConvertColumnNumberToName(commentToAdd.col), commentToAdd.row.ToString());
}
// The VMLDrawingPart should contain all the definitions for how to draw every comment shape for the worksheet
VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart <VmlDrawingPart>();
using (XmlTextWriter writer = new XmlTextWriter(vmlDrawingPart.GetStream(FileMode.Create), Encoding.UTF8))
{
writer.WriteRaw("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\"\r\n xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n <o:shapelayout v:ext=\"edit\">\r\n <o:idmap v:ext=\"edit\" data=\"1\"/>\r\n" +
"</o:shapelayout><v:shapetype id=\"_x0000_t202\" coordsize=\"21600,21600\" o:spt=\"202\"\r\n path=\"m,l,21600r21600,l21600,xe\">\r\n <v:stroke joinstyle=\"miter\"/>\r\n <v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>\r\n </v:shapetype>"
+ commentsVmlXml + "</xml>");
}
// Create the comment elements
foreach (var commentToAdd in commentsToAddList)
{
WorksheetCommentsPart worksheetCommentsPart = worksheetPart.WorksheetCommentsPart ?? worksheetPart.AddNewPart <WorksheetCommentsPart>();
// We only want one legacy drawing element per worksheet for comments
if (worksheetPart.Worksheet.Descendants <LegacyDrawing>().SingleOrDefault() == null)
{
string vmlPartId = worksheetPart.GetIdOfPart(vmlDrawingPart);
LegacyDrawing legacyDrawing = new LegacyDrawing()
{
Id = vmlPartId
};
worksheetPart.Worksheet.Append(legacyDrawing);
}
Comments comments;
bool appendComments = false;
if (worksheetPart.WorksheetCommentsPart.Comments != null)
{
comments = worksheetPart.WorksheetCommentsPart.Comments;
}
else
{
comments = new Comments();
appendComments = true;
}
// We only want one Author element per Comments element
if (worksheetPart.WorksheetCommentsPart.Comments == null)
{
Authors authors = new Authors();
Author author = new Author
{
Text = "Author Name"
};
authors.Append(author);
comments.Append(authors);
}
CommentList commentList;
bool appendCommentList = false;
if (worksheetPart.WorksheetCommentsPart.Comments != null &&
worksheetPart.WorksheetCommentsPart.Comments.Descendants <CommentList>().SingleOrDefault() != null)
{
commentList = worksheetPart.WorksheetCommentsPart.Comments.Descendants <CommentList>().Single();
}
else
{
commentList = new CommentList();
appendCommentList = true;
}
Comment comment = new Comment()
{
Reference = string.Concat(ConvertColumnNumberToName(commentToAdd.col), commentToAdd.row), AuthorId = (UInt32Value)0U
};
CommentText commentTextElement = new CommentText();
Run run = new Run();
RunProperties runProperties = new RunProperties();
Bold bold = new Bold();
FontSize fontSize = new FontSize()
{
Val = 8D
};
Color color = new Color()
{
Indexed = (UInt32Value)81U
};
RunFont runFont = new RunFont()
{
Val = "Tahoma"
};
RunPropertyCharSet runPropertyCharSet = new RunPropertyCharSet()
{
Val = 1
};
runProperties.Append(bold);
runProperties.Append(fontSize);
runProperties.Append(color);
runProperties.Append(runFont);
runProperties.Append(runPropertyCharSet);
Text text = new Text
{
Text = commentToAdd.text
};
run.Append(runProperties);
run.Append(text);
commentTextElement.Append(run);
comment.Append(commentTextElement);
commentList.Append(comment);
// Only append the Comment List if this is the first time adding a comment
if (appendCommentList)
{
comments.Append(commentList);
}
// Only append the Comments if this is the first time adding Comments
if (appendComments)
{
worksheetCommentsPart.Comments = comments;
}
}
}
}