public static void AddLink(WorksheetPart worksheetPart, string url, Cell cell)
{
var hyperlinks1 = new Hyperlinks();
var rel = worksheetPart.AddHyperlinkRelationship(new Uri(url, UriKind.Absolute), true);
var hyperlink1 = new Hyperlink()
{
Reference = cell.CellReference, Id = rel.Id
};
hyperlinks1.Append(hyperlink1);
var pageMargins = worksheetPart.Worksheet.Descendants <PageMargins>().First();
worksheetPart.Worksheet.InsertBefore(hyperlinks1, pageMargins);
/*var hlRelationship =
* worksheetPart.AddHyperlinkRelationship(new Uri(url, UriKind.Absolute), true);
*
* var hyperlinks1 = new Hyperlinks();
* var hyperlink1 = new Hyperlink() {Reference = cell.CellReference, Id = hlRelationship.Id};
* hyperlinks1.Append(hyperlink1);
*
* var pageMargins1 = worksheetPart.Worksheet.Descendants<PageMargins>().First();
* pageMargins1.Remove();
* worksheetPart.Worksheet.Append(hyperlinks1);
* worksheetPart.Worksheet.Append(pageMargins1);*/
}
protected InlineString FormatMessage(string value, string cellReference)
{
var returnObject = new InlineString();
var anchors = Anchor.Find(value);
if (!anchors.Any())
{
returnObject.Append(new Run(new Text(value.TrimEnd(Environment.NewLine.ToCharArray()))
{
Space = SpaceProcessingModeValues.Preserve
}));
}
else
{
// Use the first instance because only one hyperlink per cell is allowed
var anchor = anchors[0];
var hyperLinkId = string.Concat(cellReference, "HyperLink");
WorksheetPart.AddHyperlinkRelationship(new System.Uri(anchor.Href, System.UriKind.Absolute), true, hyperLinkId);
Hyperlinks.Append(new Hyperlink()
{
Reference = cellReference, Id = hyperLinkId
});
// The entire cell will be a hyperlink but only the anchor text will look like a link
var valueWithoutAnchor = value.Replace(anchor.Value, string.Empty).TrimEnd('.').TrimEnd(Environment.NewLine.ToCharArray());
Run run1 = new Run();
RunProperties runProperties1 = new RunProperties();
FontSize fontSize3 = new FontSize()
{
Val = 11D
};
Color color3 = new Color()
{
Theme = (UInt32Value)1U
};
RunFont runFont1 = new RunFont()
{
Val = "Calibri"
};
FontFamily fontFamily1 = new FontFamily()
{
Val = 2
};
FontScheme fontScheme4 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
runProperties1.Append(fontSize3);
runProperties1.Append(color3);
runProperties1.Append(runFont1);
runProperties1.Append(fontFamily1);
runProperties1.Append(fontScheme4);
Text text1 = new Text();
text1.Text = valueWithoutAnchor;
text1.Space = SpaceProcessingModeValues.Preserve;
run1.Append(runProperties1);
run1.Append(text1);
Run run2 = new Run();
RunProperties runProperties2 = new RunProperties();
Underline underline2 = new Underline();
FontSize fontSize4 = new FontSize()
{
Val = 11D
};
Color color4 = new Color()
{
Theme = (UInt32Value)10U
};
RunFont runFont2 = new RunFont()
{
Val = "Calibri"
};
FontFamily fontFamily2 = new FontFamily()
{
Val = 2
};
FontScheme fontScheme5 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
runProperties2.Append(underline2);
runProperties2.Append(fontSize4);
runProperties2.Append(color4);
runProperties2.Append(runFont2);
runProperties2.Append(fontFamily2);
runProperties2.Append(fontScheme5);
Text text2 = new Text()
{
Space = SpaceProcessingModeValues.Preserve
};
text2.Text = string.Concat(anchor.Text, ".");
run2.Append(runProperties2);
run2.Append(text2);
returnObject.Append(run1);
returnObject.Append(run2);
}
return(returnObject);
}
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
}
}
// 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 Button_Click_1(object sender, RoutedEventArgs e)
{
string mainGoAction = Properties.Settings.Default.mainGoAction;
if (mainGoAction == "email")
{
selectedOption.IsEnabled = true;
notSelectedOption.IsEnabled = true;
if (dataGrid.SelectedItems.Count == 0)
{
selectedOption.IsEnabled = false;
}
if (dataGrid.SelectedItems.Count == dataGrid.Items.Count)
{
notSelectedOption.IsEnabled = false;
}
System.Windows.Controls.Button senderButton = sender as System.Windows.Controls.Button;
System.Windows.Controls.ContextMenu contextMenu = senderButton.ContextMenu;
contextMenu.IsEnabled = true;
contextMenu.PlacementTarget = senderButton;
contextMenu.Placement = System.Windows.Controls.Primitives.PlacementMode.Left;
contextMenu.IsOpen = true;
}
else if (mainGoAction == "excel")
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel Workbook (*.xlsx)|*.xlsx";
if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(saveFileDialog.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorkbookStylesPart workbookStylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
workbookStylePart.Stylesheet = new Stylesheet(
new DocumentFormat.OpenXml.Spreadsheet.Fonts(
new DocumentFormat.OpenXml.Spreadsheet.Font(),
new DocumentFormat.OpenXml.Spreadsheet.Font()
{
Color = new DocumentFormat.OpenXml.Spreadsheet.Color()
{
Theme = 10
}, Underline = new DocumentFormat.OpenXml.Spreadsheet.Underline()
},
new DocumentFormat.OpenXml.Spreadsheet.Font()
{
Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold()
}),
new Fills(
new Fill()),
new Borders(
new DocumentFormat.OpenXml.Spreadsheet.Border()),
new CellFormats(
new CellFormat()
{
NumberFormatId = 0, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0
},
new CellFormat()
{
NumberFormatId = 14, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
},
new CellFormat()
{
NumberFormatId = 10, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
},
new CellFormat()
{
NumberFormatId = 0, FormatId = 0, FontId = 1, BorderId = 0, FillId = 0
},
new CellFormat()
{
NumberFormatId = 0, FormatId = 0, FontId = 2, BorderId = 0, FillId = 0
}));
workbookStylePart.Stylesheet.Save();
Hyperlinks hyperlinks = new Hyperlinks();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
worksheetPart.Worksheet.Append(hyperlinks);
worksheetPart.Worksheet.Save();
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Table"
};
sheets.Append(sheet);
SharedStringTablePart sharedStringTablePart = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
sharedStringTablePart.SharedStringTable = new SharedStringTable();
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
sheetData.Append(new Row(
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Requestor E-mail")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Feedback Rate")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Number Of Feedbacks")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Number Of No Feedbacks")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Reference")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Publish Date")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Title")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
}));
int rowCounter = 1;
foreach (UserData userData in dataGridSource)
{
foreach (SimRequestData simRequestData in userData.simRequests)
{
rowCounter++;
sheetData.Append(new Row(
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, userData.email.ToString())),
DataType = new EnumValue <CellValues>(CellValues.SharedString)
},
new Cell()
{
CellValue = new CellValue((Convert.ToDouble(userData.rate) / 100d).ToString()),
StyleIndex = 2
},
new Cell()
{
CellValue = new CellValue(userData.feedbacks.ToString())
},
new Cell()
{
CellValue = new CellValue(userData.noFeedbacks.ToString())
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, simRequestData.reference.ToString())),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 3
},
new Cell()
{
CellValue = new CellValue(Convert.ToDateTime(simRequestData.publishDate).ToOADate().ToString()),
StyleIndex = 1
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, simRequestData.title.ToString())),
DataType = new EnumValue <CellValues>(CellValues.SharedString)
}
));
string hyperlinkID = "id" + rowCounter.ToString();
hyperlinks.Append(new DocumentFormat.OpenXml.Spreadsheet.Hyperlink()
{
Reference = "E" + rowCounter.ToString(), Id = hyperlinkID
});
worksheetPart.AddHyperlinkRelationship(simRequestData.requestLink, true, hyperlinkID);
}
}
workbookPart.Workbook.Save();
}
}
catch (Exception exception)
{
System.Windows.MessageBox.Show(exception.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK);
}
}
}
}
// Generates content of Sheet.
private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, DataTable data, bool isPrimaryTab = false)
{
Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetViews sheetViews = new SheetViews();
SheetView sheetView = new SheetView() { TabSelected = isPrimaryTab, WorkbookViewId = (UInt32Value)0U };
sheetViews.Append(sheetView);
SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };
//If you want to default select a cell
//Selection selection1 = new Selection() { ActiveCell = "A3", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A3" } };
//sheetView1.Append(selection1);
//Column Creation
Column tempCol;
Columns columns = new Columns();
for (int ii = 0; ii < data.Columns.Count; ii++)
{
tempCol = new Column() {
Min = (UInt32Value)Convert.ToUInt32(ii + 1),
Max = (UInt32Value)Convert.ToUInt32(ii + 1),
Width = 10.28515625D,
CustomWidth = true,
BestFit = true
};
columns.Append(tempCol);
}
SheetData sheetData = new SheetData();
//Creating cells and adding them to rows
Row row;
Cell cell;
CellValue cellVal;
int rowIndex = 1;
string cellName = "";
List<string> cellsWithHyperLinks = new List<string>();
//Insert Header Row
row = new Row() { RowIndex = (UInt32Value)Convert.ToUInt32(rowIndex), Spans = new ListValue<StringValue>() { InnerText = "1:" + data.Columns.Count } };//, DyDescent = 0.25D };
for (int jj = 0; jj < data.Columns.Count; jj++)
{
cellName = GetExcelCellName(jj + 1, rowIndex);
cell = new Cell() { CellReference = cellName, StyleIndex = (UInt32Value)1U, DataType = CellValues.String };
cellVal = new CellValue() { Text = data.Columns[jj].ColumnName };
cell.Append(cellVal);
row.Append(cell);
}
sheetData.Append(row);
rowIndex++;
//Insert Data
foreach (DataRow datarow in data.Rows)
{
row = new Row() { RowIndex = (UInt32Value)Convert.ToUInt32(rowIndex), Spans = new ListValue<StringValue>() { InnerText = "1:" + data.Columns.Count } };//, DyDescent = 0.25D };
for (int jj = 0; jj < data.Columns.Count; jj++)
{
cellName = GetExcelCellName(jj + 1, rowIndex);
cell = new Cell() { CellReference = cellName, StyleIndex = (UInt32Value)0U };
if (data.Columns[jj].DataType == typeof(int) || data.Columns[jj].DataType == typeof(double))
{
cell.DataType = CellValues.Number;
}
else if (data.Columns[jj].DataType == typeof(DateTime))
{
cell.DataType = CellValues.Date;
}
else
{
cell.DataType = CellValues.String;
}
cellVal = new CellValue() { Text = datarow[jj].ToString() };
if (cellVal.Text.ToLower().Contains("http"))
{
cell.StyleIndex = (UInt32Value)2U;
cellsWithHyperLinks.Add(cellName);
}
cell.Append(cellVal);
row.Append(cell);
}
//Add the row to the sheetdata object
sheetData.Append(row);
rowIndex++;
}
//Adding hyperlinks
Hyperlink hyperlink;
Hyperlinks hyperlinks = null;
if (cellsWithHyperLinks.Count > 0)
{
hyperlinks = new Hyperlinks();
for (int ii = 0; ii < cellsWithHyperLinks.Count; ii++)
{
hyperlink = new Hyperlink() { Reference = cellsWithHyperLinks[ii], Id = "rId" + (ii + 1) };
hyperlinks.Append(hyperlink);
}
}
//PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
//PageSetup pageSetup1 = new PageSetup() { Orientation = OrientationValues.Portrait, VerticalDpi = (UInt32Value)0U, Id = "rId2" };
worksheet1.Append(sheetViews);
worksheet1.Append(sheetFormatProperties1);
worksheet1.Append(columns);
worksheet1.Append(sheetData);
if (hyperlinks != null)
{
worksheet1.Append(hyperlinks);
}
//worksheet1.Append(pageMargins1);
//worksheet1.Append(pageSetup1);
worksheetPart.Worksheet = worksheet1;
}