static void GenerateWorksheetPartContent(WorksheetPart worksheetPart, DataTable dataTable)
{
var worksheet = new Worksheet();
var sheetData = new SheetData();
ExportDataTable(sheetData, dataTable);
worksheet.Append(sheetData);
if (dataTable.TableName != "Дата")
{
var worksheetExtensionList = new WorksheetExtensionList();
var worksheetExtension = new WorksheetExtension {
Uri = WorksheetExtensionUri
};
var dataValidations = new X14.DataValidations {
Count = (UInt32Value)1U
};
var dataValidation = new X14.DataValidation {
Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true
};
var dataValidationForumla1 = new X14.DataValidationForumla1();
var formula = new Excel.Formula {
Text = "Дата!$B$2:$B$4"
};
dataValidationForumla1.Append(formula);
var referenceSequence = new Excel.ReferenceSequence {
Text = "F2:F4"
};
dataValidation.Append(dataValidationForumla1);
dataValidation.Append(referenceSequence);
dataValidations.Append(dataValidation);
worksheetExtension.Append(dataValidations);
worksheetExtensionList.Append(worksheetExtension);
worksheet.Append(worksheetExtensionList);
}
worksheetPart.Worksheet = worksheet;
}
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 worksheetPart6.
private void GenerateWorksheetPart6Content(WorksheetPart worksheetPart6)
{
Worksheet worksheet6 = new Worksheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
worksheet6.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet6.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet6.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetDimension sheetDimension6 = new SheetDimension(){ Reference = "A1:B5" };
SheetViews sheetViews6 = new SheetViews();
SheetView sheetView6 = new SheetView(){ WorkbookViewId = (UInt32Value)0U };
sheetViews6.Append(sheetView6);
SheetFormatProperties sheetFormatProperties6 = new SheetFormatProperties(){ DefaultRowHeight = 15D };
Columns columns6 = new Columns();
Column column34 = new Column(){ Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 14.140625D, BestFit = true, CustomWidth = true };
Column column35 = new Column(){ Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 17.42578125D, CustomWidth = true };
Column column36 = new Column(){ Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 12.42578125D, CustomWidth = true };
Column column37 = new Column(){ Min = (UInt32Value)4U, Max = (UInt32Value)11U, Width = 13D, BestFit = true, CustomWidth = true };
Column column38 = new Column(){ Min = (UInt32Value)12U, Max = (UInt32Value)12U, Width = 13D, CustomWidth = true };
Column column39 = new Column(){ Min = (UInt32Value)13U, Max = (UInt32Value)20U, Width = 13D, BestFit = true, CustomWidth = true };
Column column40 = new Column(){ Min = (UInt32Value)21U, Max = (UInt32Value)23U, Width = 20.42578125D, BestFit = true, CustomWidth = true };
columns6.Append(column34);
columns6.Append(column35);
columns6.Append(column36);
columns6.Append(column37);
columns6.Append(column38);
columns6.Append(column39);
columns6.Append(column40);
SheetData sheetData6 = new SheetData();
Row row38 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell163 = new Cell(){ CellReference = "A1", StyleIndex = (UInt32Value)5U, DataType = CellValues.SharedString };
CellValue cellValue163 = new CellValue();
cellValue163.Text = "29";
cell163.Append(cellValue163);
Cell cell164 = new Cell(){ CellReference = "B1", DataType = CellValues.SharedString };
CellValue cellValue164 = new CellValue();
cellValue164.Text = "28";
cell164.Append(cellValue164);
row38.Append(cell163);
row38.Append(cell164);
Row row39 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell165 = new Cell(){ CellReference = "A2", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue165 = new CellValue();
cellValue165.Text = "6";
cell165.Append(cellValue165);
Cell cell166 = new Cell(){ CellReference = "B2", StyleIndex = (UInt32Value)7U };
CellValue cellValue166 = new CellValue();
cellValue166.Text = "19";
cell166.Append(cellValue166);
row39.Append(cell165);
row39.Append(cell166);
Row row40 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell167 = new Cell(){ CellReference = "A3", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue167 = new CellValue();
cellValue167.Text = "8";
cell167.Append(cellValue167);
Cell cell168 = new Cell(){ CellReference = "B3", StyleIndex = (UInt32Value)7U };
CellValue cellValue168 = new CellValue();
cellValue168.Text = "13";
cell168.Append(cellValue168);
row40.Append(cell167);
row40.Append(cell168);
Row row41 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell169 = new Cell(){ CellReference = "A4", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue169 = new CellValue();
cellValue169.Text = "11";
cell169.Append(cellValue169);
Cell cell170 = new Cell(){ CellReference = "B4", StyleIndex = (UInt32Value)7U };
CellValue cellValue170 = new CellValue();
cellValue170.Text = "33";
cell170.Append(cellValue170);
row41.Append(cell169);
row41.Append(cell170);
Row row42 = new Row(){ RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell171 = new Cell(){ CellReference = "A5", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue171 = new CellValue();
cellValue171.Text = "30";
cell171.Append(cellValue171);
Cell cell172 = new Cell(){ CellReference = "B5", StyleIndex = (UInt32Value)7U };
CellValue cellValue172 = new CellValue();
cellValue172.Text = "65";
cell172.Append(cellValue172);
row42.Append(cell171);
row42.Append(cell172);
sheetData6.Append(row38);
sheetData6.Append(row39);
sheetData6.Append(row40);
sheetData6.Append(row41);
sheetData6.Append(row42);
PhoneticProperties phoneticProperties12 = new PhoneticProperties(){ FontId = (UInt32Value)1U };
PageMargins pageMargins12 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
S.Drawing drawing4 = new S.Drawing(){ Id = "rId2" };
WorksheetExtensionList worksheetExtensionList4 = new WorksheetExtensionList();
WorksheetExtension worksheetExtension4 = new WorksheetExtension(){ Uri = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}" };
worksheetExtension4.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineReferences timelineReferences4 = new X15.TimelineReferences();
X15.TimelineReference timelineReference4 = new X15.TimelineReference(){ Id = "rId3" };
timelineReferences4.Append(timelineReference4);
worksheetExtension4.Append(timelineReferences4);
worksheetExtensionList4.Append(worksheetExtension4);
worksheet6.Append(sheetDimension6);
worksheet6.Append(sheetViews6);
worksheet6.Append(sheetFormatProperties6);
worksheet6.Append(columns6);
worksheet6.Append(sheetData6);
worksheet6.Append(phoneticProperties12);
worksheet6.Append(pageMargins12);
worksheet6.Append(drawing4);
worksheet6.Append(worksheetExtensionList4);
worksheetPart6.Worksheet = worksheet6;
}
// Generates content of worksheetPart1.
private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
{
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");
SheetDimension sheetDimension1 = new SheetDimension(){ Reference = "A1:G4" };
SheetViews sheetViews1 = new SheetViews();
SheetView sheetView1 = new SheetView(){ TabSelected = true, WorkbookViewId = (UInt32Value)0U };
sheetViews1.Append(sheetView1);
SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties(){ DefaultRowHeight = 15D };
Columns columns1 = new Columns();
Column column1 = new Column(){ Min = (UInt32Value)1U, Max = (UInt32Value)3U, Width = 10.5703125D, CustomWidth = true };
Column column2 = new Column(){ Min = (UInt32Value)5U, Max = (UInt32Value)7U, Width = 10.5703125D, CustomWidth = true };
columns1.Append(column1);
columns1.Append(column2);
SheetData sheetData1 = new SheetData();
Row row1 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:7" } };
Cell cell1 = new Cell(){ CellReference = "A1", DataType = CellValues.SharedString };
CellValue cellValue1 = new CellValue();
cellValue1.Text = "12";
cell1.Append(cellValue1);
Cell cell2 = new Cell(){ CellReference = "B1", DataType = CellValues.SharedString };
CellValue cellValue2 = new CellValue();
cellValue2.Text = "13";
cell2.Append(cellValue2);
Cell cell3 = new Cell(){ CellReference = "C1", DataType = CellValues.SharedString };
CellValue cellValue3 = new CellValue();
cellValue3.Text = "14";
cell3.Append(cellValue3);
Cell cell4 = new Cell(){ CellReference = "E1", DataType = CellValues.SharedString };
CellValue cellValue4 = new CellValue();
cellValue4.Text = "12";
cell4.Append(cellValue4);
Cell cell5 = new Cell(){ CellReference = "F1", DataType = CellValues.SharedString };
CellValue cellValue5 = new CellValue();
cellValue5.Text = "13";
cell5.Append(cellValue5);
Cell cell6 = new Cell(){ CellReference = "G1", DataType = CellValues.SharedString };
CellValue cellValue6 = new CellValue();
cellValue6.Text = "14";
cell6.Append(cellValue6);
row1.Append(cell1);
row1.Append(cell2);
row1.Append(cell3);
row1.Append(cell4);
row1.Append(cell5);
row1.Append(cell6);
Row row2 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:7" } };
Cell cell7 = new Cell(){ CellReference = "A2", DataType = CellValues.SharedString };
CellValue cellValue7 = new CellValue();
cellValue7.Text = "0";
cell7.Append(cellValue7);
Cell cell8 = new Cell(){ CellReference = "B2", DataType = CellValues.SharedString };
CellValue cellValue8 = new CellValue();
cellValue8.Text = "2";
cell8.Append(cellValue8);
Cell cell9 = new Cell(){ CellReference = "C2" };
CellValue cellValue9 = new CellValue();
cellValue9.Text = "1";
cell9.Append(cellValue9);
Cell cell10 = new Cell(){ CellReference = "E2", DataType = CellValues.SharedString };
CellValue cellValue10 = new CellValue();
cellValue10.Text = "4";
cell10.Append(cellValue10);
Cell cell11 = new Cell(){ CellReference = "F2", DataType = CellValues.SharedString };
CellValue cellValue11 = new CellValue();
cellValue11.Text = "9";
cell11.Append(cellValue11);
Cell cell12 = new Cell(){ CellReference = "G2" };
CellValue cellValue12 = new CellValue();
cellValue12.Text = "3";
cell12.Append(cellValue12);
row2.Append(cell7);
row2.Append(cell8);
row2.Append(cell9);
row2.Append(cell10);
row2.Append(cell11);
row2.Append(cell12);
Row row3 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:7" } };
Cell cell13 = new Cell(){ CellReference = "A3", DataType = CellValues.SharedString };
CellValue cellValue13 = new CellValue();
cellValue13.Text = "1";
cell13.Append(cellValue13);
Cell cell14 = new Cell(){ CellReference = "B3", DataType = CellValues.SharedString };
CellValue cellValue14 = new CellValue();
cellValue14.Text = "3";
cell14.Append(cellValue14);
Cell cell15 = new Cell(){ CellReference = "C3" };
CellValue cellValue15 = new CellValue();
cellValue15.Text = "2";
cell15.Append(cellValue15);
Cell cell16 = new Cell(){ CellReference = "E3", DataType = CellValues.SharedString };
CellValue cellValue16 = new CellValue();
cellValue16.Text = "5";
cell16.Append(cellValue16);
Cell cell17 = new Cell(){ CellReference = "F3", DataType = CellValues.SharedString };
CellValue cellValue17 = new CellValue();
cellValue17.Text = "10";
cell17.Append(cellValue17);
Cell cell18 = new Cell(){ CellReference = "G3" };
CellValue cellValue18 = new CellValue();
cellValue18.Text = "4";
cell18.Append(cellValue18);
row3.Append(cell13);
row3.Append(cell14);
row3.Append(cell15);
row3.Append(cell16);
row3.Append(cell17);
row3.Append(cell18);
Row row4 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:7" } };
Cell cell19 = new Cell(){ CellReference = "A4", DataType = CellValues.SharedString };
CellValue cellValue19 = new CellValue();
cellValue19.Text = "6";
cell19.Append(cellValue19);
Cell cell20 = new Cell(){ CellReference = "B4", DataType = CellValues.SharedString };
CellValue cellValue20 = new CellValue();
cellValue20.Text = "7";
cell20.Append(cellValue20);
Cell cell21 = new Cell(){ CellReference = "C4" };
CellValue cellValue21 = new CellValue();
cellValue21.Text = "3";
cell21.Append(cellValue21);
Cell cell22 = new Cell(){ CellReference = "E4", DataType = CellValues.SharedString };
CellValue cellValue22 = new CellValue();
cellValue22.Text = "8";
cell22.Append(cellValue22);
Cell cell23 = new Cell(){ CellReference = "F4", DataType = CellValues.SharedString };
CellValue cellValue23 = new CellValue();
cellValue23.Text = "11";
cell23.Append(cellValue23);
Cell cell24 = new Cell(){ CellReference = "G4" };
CellValue cellValue24 = new CellValue();
cellValue24.Text = "5";
cell24.Append(cellValue24);
row4.Append(cell19);
row4.Append(cell20);
row4.Append(cell21);
row4.Append(cell22);
row4.Append(cell23);
row4.Append(cell24);
sheetData1.Append(row1);
sheetData1.Append(row2);
sheetData1.Append(row3);
sheetData1.Append(row4);
PhoneticProperties phoneticProperties13 = new PhoneticProperties(){ FontId = (UInt32Value)1U };
PageMargins pageMargins1 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
S.Drawing drawing1 = new S.Drawing(){ Id = "rId1" };
TableParts tableParts1 = new TableParts(){ Count = (UInt32Value)2U };
TablePart tablePart1 = new TablePart(){ Id = "rId2" };
TablePart tablePart2 = new TablePart(){ Id = "rId3" };
tableParts1.Append(tablePart1);
tableParts1.Append(tablePart2);
WorksheetExtensionList worksheetExtensionList1 = new WorksheetExtensionList();
WorksheetExtension worksheetExtension1 = new WorksheetExtension(){ Uri = "{3A4CF648-6AED-40f4-86FF-DC5316D8AED3}" };
worksheetExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X14.SlicerList slicerList1 = new X14.SlicerList();
slicerList1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerRef slicerRef1 = new X14.SlicerRef(){ Id = "rId4" };
slicerList1.Append(slicerRef1);
worksheetExtension1.Append(slicerList1);
worksheetExtensionList1.Append(worksheetExtension1);
worksheet1.Append(sheetDimension1);
worksheet1.Append(sheetViews1);
worksheet1.Append(sheetFormatProperties1);
worksheet1.Append(columns1);
worksheet1.Append(sheetData1);
worksheet1.Append(phoneticProperties13);
worksheet1.Append(pageMargins1);
worksheet1.Append(drawing1);
worksheet1.Append(tableParts1);
worksheet1.Append(worksheetExtensionList1);
worksheetPart1.Worksheet = worksheet1;
}
// Generates content of worksheetPart3.
private void GenerateWorksheetPart3Content(WorksheetPart worksheetPart3)
{
Worksheet worksheet3 = new Worksheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
worksheet3.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet3.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet3.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetDimension sheetDimension3 = new SheetDimension(){ Reference = "A1:B5" };
SheetViews sheetViews3 = new SheetViews();
SheetView sheetView3 = new SheetView(){ WorkbookViewId = (UInt32Value)0U };
sheetViews3.Append(sheetView3);
SheetFormatProperties sheetFormatProperties3 = new SheetFormatProperties(){ DefaultRowHeight = 15D };
Columns columns3 = new Columns();
Column column9 = new Column(){ Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 14.140625D, BestFit = true, CustomWidth = true };
Column column10 = new Column(){ Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 17.42578125D, CustomWidth = true };
Column column11 = new Column(){ Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 12.42578125D, CustomWidth = true };
Column column12 = new Column(){ Min = (UInt32Value)4U, Max = (UInt32Value)11U, Width = 13D, BestFit = true, CustomWidth = true };
Column column13 = new Column(){ Min = (UInt32Value)12U, Max = (UInt32Value)12U, Width = 13D, CustomWidth = true };
Column column14 = new Column(){ Min = (UInt32Value)13U, Max = (UInt32Value)20U, Width = 13D, BestFit = true, CustomWidth = true };
Column column15 = new Column(){ Min = (UInt32Value)21U, Max = (UInt32Value)23U, Width = 20.42578125D, BestFit = true, CustomWidth = true };
columns3.Append(column9);
columns3.Append(column10);
columns3.Append(column11);
columns3.Append(column12);
columns3.Append(column13);
columns3.Append(column14);
columns3.Append(column15);
SheetData sheetData3 = new SheetData();
Row row11 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell21 = new Cell(){ CellReference = "A1", StyleIndex = (UInt32Value)5U, DataType = CellValues.SharedString };
CellValue cellValue21 = new CellValue();
cellValue21.Text = "29";
cell21.Append(cellValue21);
Cell cell22 = new Cell(){ CellReference = "B1", DataType = CellValues.SharedString };
CellValue cellValue22 = new CellValue();
cellValue22.Text = "28";
cell22.Append(cellValue22);
row11.Append(cell21);
row11.Append(cell22);
Row row12 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell23 = new Cell(){ CellReference = "A2", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue23 = new CellValue();
cellValue23.Text = "6";
cell23.Append(cellValue23);
Cell cell24 = new Cell(){ CellReference = "B2", StyleIndex = (UInt32Value)7U };
CellValue cellValue24 = new CellValue();
cellValue24.Text = "19";
cell24.Append(cellValue24);
row12.Append(cell23);
row12.Append(cell24);
Row row13 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell25 = new Cell(){ CellReference = "A3", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue25 = new CellValue();
cellValue25.Text = "8";
cell25.Append(cellValue25);
Cell cell26 = new Cell(){ CellReference = "B3", StyleIndex = (UInt32Value)7U };
CellValue cellValue26 = new CellValue();
cellValue26.Text = "13";
cell26.Append(cellValue26);
row13.Append(cell25);
row13.Append(cell26);
Row row14 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell27 = new Cell(){ CellReference = "A4", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue27 = new CellValue();
cellValue27.Text = "11";
cell27.Append(cellValue27);
Cell cell28 = new Cell(){ CellReference = "B4", StyleIndex = (UInt32Value)7U };
CellValue cellValue28 = new CellValue();
cellValue28.Text = "33";
cell28.Append(cellValue28);
row14.Append(cell27);
row14.Append(cell28);
Row row15 = new Row(){ RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell29 = new Cell(){ CellReference = "A5", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue29 = new CellValue();
cellValue29.Text = "30";
cell29.Append(cellValue29);
Cell cell30 = new Cell(){ CellReference = "B5", StyleIndex = (UInt32Value)7U };
CellValue cellValue30 = new CellValue();
cellValue30.Text = "65";
cell30.Append(cellValue30);
row15.Append(cell29);
row15.Append(cell30);
sheetData3.Append(row11);
sheetData3.Append(row12);
sheetData3.Append(row13);
sheetData3.Append(row14);
sheetData3.Append(row15);
PhoneticProperties phoneticProperties3 = new PhoneticProperties(){ FontId = (UInt32Value)1U };
PageMargins pageMargins7 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
S.Drawing drawing3 = new S.Drawing(){ Id = "rId2" };
WorksheetExtensionList worksheetExtensionList3 = new WorksheetExtensionList();
WorksheetExtension worksheetExtension3 = new WorksheetExtension(){ Uri = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}" };
worksheetExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineReferences timelineReferences3 = new X15.TimelineReferences();
X15.TimelineReference timelineReference3 = new X15.TimelineReference(){ Id = "rId3" };
timelineReferences3.Append(timelineReference3);
worksheetExtension3.Append(timelineReferences3);
worksheetExtensionList3.Append(worksheetExtension3);
worksheet3.Append(sheetDimension3);
worksheet3.Append(sheetViews3);
worksheet3.Append(sheetFormatProperties3);
worksheet3.Append(columns3);
worksheet3.Append(sheetData3);
worksheet3.Append(phoneticProperties3);
worksheet3.Append(pageMargins7);
worksheet3.Append(drawing3);
worksheet3.Append(worksheetExtensionList3);
worksheetPart3.Worksheet = worksheet3;
}
// Generates content of worksheetPart1.
private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
{
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");
SheetDimension sheetDimension1 = new SheetDimension(){ Reference = "A1:B5" };
SheetViews sheetViews1 = new SheetViews();
SheetView sheetView1 = new SheetView(){ WorkbookViewId = (UInt32Value)0U };
sheetViews1.Append(sheetView1);
SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties(){ DefaultRowHeight = 15D };
Columns columns1 = new Columns();
Column column1 = new Column(){ Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 14.140625D, BestFit = true, CustomWidth = true };
Column column2 = new Column(){ Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 17.42578125D, CustomWidth = true };
Column column3 = new Column(){ Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 12.42578125D, CustomWidth = true };
Column column4 = new Column(){ Min = (UInt32Value)4U, Max = (UInt32Value)11U, Width = 13D, BestFit = true, CustomWidth = true };
Column column5 = new Column(){ Min = (UInt32Value)12U, Max = (UInt32Value)12U, Width = 13D, CustomWidth = true };
Column column6 = new Column(){ Min = (UInt32Value)13U, Max = (UInt32Value)20U, Width = 13D, BestFit = true, CustomWidth = true };
Column column7 = new Column(){ Min = (UInt32Value)21U, Max = (UInt32Value)23U, Width = 20.42578125D, BestFit = true, CustomWidth = true };
columns1.Append(column1);
columns1.Append(column2);
columns1.Append(column3);
columns1.Append(column4);
columns1.Append(column5);
columns1.Append(column6);
columns1.Append(column7);
SheetData sheetData1 = new SheetData();
Row row1 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell1 = new Cell(){ CellReference = "A1", StyleIndex = (UInt32Value)5U, DataType = CellValues.SharedString };
CellValue cellValue1 = new CellValue();
cellValue1.Text = "29";
cell1.Append(cellValue1);
Cell cell2 = new Cell(){ CellReference = "B1", DataType = CellValues.SharedString };
CellValue cellValue2 = new CellValue();
cellValue2.Text = "28";
cell2.Append(cellValue2);
row1.Append(cell1);
row1.Append(cell2);
Row row2 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell3 = new Cell(){ CellReference = "A2", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue3 = new CellValue();
cellValue3.Text = "6";
cell3.Append(cellValue3);
Cell cell4 = new Cell(){ CellReference = "B2", StyleIndex = (UInt32Value)7U };
CellValue cellValue4 = new CellValue();
cellValue4.Text = "19";
cell4.Append(cellValue4);
row2.Append(cell3);
row2.Append(cell4);
Row row3 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell5 = new Cell(){ CellReference = "A3", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue5 = new CellValue();
cellValue5.Text = "8";
cell5.Append(cellValue5);
Cell cell6 = new Cell(){ CellReference = "B3", StyleIndex = (UInt32Value)7U };
CellValue cellValue6 = new CellValue();
cellValue6.Text = "13";
cell6.Append(cellValue6);
row3.Append(cell5);
row3.Append(cell6);
Row row4 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell7 = new Cell(){ CellReference = "A4", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue7 = new CellValue();
cellValue7.Text = "11";
cell7.Append(cellValue7);
Cell cell8 = new Cell(){ CellReference = "B4", StyleIndex = (UInt32Value)7U };
CellValue cellValue8 = new CellValue();
cellValue8.Text = "33";
cell8.Append(cellValue8);
row4.Append(cell7);
row4.Append(cell8);
Row row5 = new Row(){ RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell9 = new Cell(){ CellReference = "A5", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue9 = new CellValue();
cellValue9.Text = "30";
cell9.Append(cellValue9);
Cell cell10 = new Cell(){ CellReference = "B5", StyleIndex = (UInt32Value)7U };
CellValue cellValue10 = new CellValue();
cellValue10.Text = "65";
cell10.Append(cellValue10);
row5.Append(cell9);
row5.Append(cell10);
sheetData1.Append(row1);
sheetData1.Append(row2);
sheetData1.Append(row3);
sheetData1.Append(row4);
sheetData1.Append(row5);
PhoneticProperties phoneticProperties1 = new PhoneticProperties(){ FontId = (UInt32Value)1U };
PageMargins pageMargins1 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
S.Drawing drawing1 = new S.Drawing(){ Id = "rId2" };
WorksheetExtensionList worksheetExtensionList1 = new WorksheetExtensionList();
WorksheetExtension worksheetExtension1 = new WorksheetExtension(){ Uri = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}" };
worksheetExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineReferences timelineReferences1 = new X15.TimelineReferences();
X15.TimelineReference timelineReference1 = new X15.TimelineReference(){ Id = "rId3" };
timelineReferences1.Append(timelineReference1);
worksheetExtension1.Append(timelineReferences1);
worksheetExtensionList1.Append(worksheetExtension1);
worksheet1.Append(sheetDimension1);
worksheet1.Append(sheetViews1);
worksheet1.Append(sheetFormatProperties1);
worksheet1.Append(columns1);
worksheet1.Append(sheetData1);
worksheet1.Append(phoneticProperties1);
worksheet1.Append(pageMargins1);
worksheet1.Append(drawing1);
worksheet1.Append(worksheetExtensionList1);
worksheetPart1.Worksheet = worksheet1;
}
// 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("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:B5" };
SheetViews sheetViews2 = new SheetViews();
SheetView sheetView2 = new SheetView(){ WorkbookViewId = (UInt32Value)0U };
sheetViews2.Append(sheetView2);
SheetFormatProperties sheetFormatProperties2 = new SheetFormatProperties(){ DefaultRowHeight = 15D };
Columns columns2 = new Columns();
Column column8 = new Column(){ Min = (UInt32Value)1U, Max = (UInt32Value)2U, Width = 14.140625D, BestFit = true, CustomWidth = true };
columns2.Append(column8);
SheetData sheetData2 = new SheetData();
Row row6 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell11 = new Cell(){ CellReference = "A1", StyleIndex = (UInt32Value)5U, DataType = CellValues.SharedString };
CellValue cellValue11 = new CellValue();
cellValue11.Text = "29";
cell11.Append(cellValue11);
Cell cell12 = new Cell(){ CellReference = "B1", DataType = CellValues.SharedString };
CellValue cellValue12 = new CellValue();
cellValue12.Text = "31";
cell12.Append(cellValue12);
row6.Append(cell11);
row6.Append(cell12);
Row row7 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell13 = new Cell(){ CellReference = "A2", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue13 = new CellValue();
cellValue13.Text = "16";
cell13.Append(cellValue13);
Cell cell14 = new Cell(){ CellReference = "B2", StyleIndex = (UInt32Value)7U };
CellValue cellValue14 = new CellValue();
cellValue14.Text = "2050";
cell14.Append(cellValue14);
row7.Append(cell13);
row7.Append(cell14);
Row row8 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell15 = new Cell(){ CellReference = "A3", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue15 = new CellValue();
cellValue15.Text = "22";
cell15.Append(cellValue15);
Cell cell16 = new Cell(){ CellReference = "B3", StyleIndex = (UInt32Value)7U };
CellValue cellValue16 = new CellValue();
cellValue16.Text = "3168";
cell16.Append(cellValue16);
row8.Append(cell15);
row8.Append(cell16);
Row row9 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell17 = new Cell(){ CellReference = "A4", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue17 = new CellValue();
cellValue17.Text = "23";
cell17.Append(cellValue17);
Cell cell18 = new Cell(){ CellReference = "B4", StyleIndex = (UInt32Value)7U };
CellValue cellValue18 = new CellValue();
cellValue18.Text = "11529";
cell18.Append(cellValue18);
row9.Append(cell17);
row9.Append(cell18);
Row row10 = new Row(){ RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell19 = new Cell(){ CellReference = "A5", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue19 = new CellValue();
cellValue19.Text = "30";
cell19.Append(cellValue19);
Cell cell20 = new Cell(){ CellReference = "B5", StyleIndex = (UInt32Value)7U };
CellValue cellValue20 = new CellValue();
cellValue20.Text = "16747";
cell20.Append(cellValue20);
row10.Append(cell19);
row10.Append(cell20);
sheetData2.Append(row6);
sheetData2.Append(row7);
sheetData2.Append(row8);
sheetData2.Append(row9);
sheetData2.Append(row10);
PhoneticProperties phoneticProperties2 = new PhoneticProperties(){ FontId = (UInt32Value)1U };
PageMargins pageMargins4 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
S.Drawing drawing2 = new S.Drawing(){ Id = "rId2" };
WorksheetExtensionList worksheetExtensionList2 = new WorksheetExtensionList();
WorksheetExtension worksheetExtension2 = new WorksheetExtension(){ Uri = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}" };
worksheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineReferences timelineReferences2 = new X15.TimelineReferences();
X15.TimelineReference timelineReference2 = new X15.TimelineReference(){ Id = "rId3" };
timelineReferences2.Append(timelineReference2);
worksheetExtension2.Append(timelineReferences2);
worksheetExtensionList2.Append(worksheetExtension2);
worksheet2.Append(sheetDimension2);
worksheet2.Append(sheetViews2);
worksheet2.Append(sheetFormatProperties2);
worksheet2.Append(columns2);
worksheet2.Append(sheetData2);
worksheet2.Append(phoneticProperties2);
worksheet2.Append(pageMargins4);
worksheet2.Append(drawing2);
worksheet2.Append(worksheetExtensionList2);
worksheetPart2.Worksheet = worksheet2;
}
// Generates content of worksheetPart9.
private void GenerateWorksheetPart9Content(WorksheetPart worksheetPart9)
{
Worksheet worksheet9 = new Worksheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
worksheet9.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet9.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet9.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetDimension sheetDimension9 = new SheetDimension(){ Reference = "A1:B5" };
SheetViews sheetViews9 = new SheetViews();
SheetView sheetView9 = new SheetView(){ WorkbookViewId = (UInt32Value)0U };
sheetViews9.Append(sheetView9);
SheetFormatProperties sheetFormatProperties9 = new SheetFormatProperties(){ DefaultRowHeight = 15D };
Columns columns9 = new Columns();
Column column55 = new Column(){ Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 14.140625D, BestFit = true, CustomWidth = true };
Column column56 = new Column(){ Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 17.42578125D, CustomWidth = true };
Column column57 = new Column(){ Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 12.42578125D, CustomWidth = true };
Column column58 = new Column(){ Min = (UInt32Value)4U, Max = (UInt32Value)11U, Width = 13D, BestFit = true, CustomWidth = true };
Column column59 = new Column(){ Min = (UInt32Value)12U, Max = (UInt32Value)12U, Width = 13D, CustomWidth = true };
Column column60 = new Column(){ Min = (UInt32Value)13U, Max = (UInt32Value)20U, Width = 13D, BestFit = true, CustomWidth = true };
Column column61 = new Column(){ Min = (UInt32Value)21U, Max = (UInt32Value)23U, Width = 20.42578125D, BestFit = true, CustomWidth = true };
columns9.Append(column55);
columns9.Append(column56);
columns9.Append(column57);
columns9.Append(column58);
columns9.Append(column59);
columns9.Append(column60);
columns9.Append(column61);
SheetData sheetData9 = new SheetData();
Row row53 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell193 = new Cell(){ CellReference = "A1", StyleIndex = (UInt32Value)5U, DataType = CellValues.SharedString };
CellValue cellValue193 = new CellValue();
cellValue193.Text = "29";
cell193.Append(cellValue193);
Cell cell194 = new Cell(){ CellReference = "B1", DataType = CellValues.SharedString };
CellValue cellValue194 = new CellValue();
cellValue194.Text = "28";
cell194.Append(cellValue194);
row53.Append(cell193);
row53.Append(cell194);
Row row54 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell195 = new Cell(){ CellReference = "A2", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue195 = new CellValue();
cellValue195.Text = "6";
cell195.Append(cellValue195);
Cell cell196 = new Cell(){ CellReference = "B2", StyleIndex = (UInt32Value)7U };
CellValue cellValue196 = new CellValue();
cellValue196.Text = "19";
cell196.Append(cellValue196);
row54.Append(cell195);
row54.Append(cell196);
Row row55 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell197 = new Cell(){ CellReference = "A3", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue197 = new CellValue();
cellValue197.Text = "8";
cell197.Append(cellValue197);
Cell cell198 = new Cell(){ CellReference = "B3", StyleIndex = (UInt32Value)7U };
CellValue cellValue198 = new CellValue();
cellValue198.Text = "13";
cell198.Append(cellValue198);
row55.Append(cell197);
row55.Append(cell198);
Row row56 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell199 = new Cell(){ CellReference = "A4", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue199 = new CellValue();
cellValue199.Text = "11";
cell199.Append(cellValue199);
Cell cell200 = new Cell(){ CellReference = "B4", StyleIndex = (UInt32Value)7U };
CellValue cellValue200 = new CellValue();
cellValue200.Text = "33";
cell200.Append(cellValue200);
row56.Append(cell199);
row56.Append(cell200);
Row row57 = new Row(){ RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell201 = new Cell(){ CellReference = "A5", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue201 = new CellValue();
cellValue201.Text = "30";
cell201.Append(cellValue201);
Cell cell202 = new Cell(){ CellReference = "B5", StyleIndex = (UInt32Value)7U };
CellValue cellValue202 = new CellValue();
cellValue202.Text = "65";
cell202.Append(cellValue202);
row57.Append(cell201);
row57.Append(cell202);
sheetData9.Append(row53);
sheetData9.Append(row54);
sheetData9.Append(row55);
sheetData9.Append(row56);
sheetData9.Append(row57);
PhoneticProperties phoneticProperties15 = new PhoneticProperties(){ FontId = (UInt32Value)1U };
PageMargins pageMargins21 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
S.Drawing drawing7 = new S.Drawing(){ Id = "rId2" };
WorksheetExtensionList worksheetExtensionList7 = new WorksheetExtensionList();
WorksheetExtension worksheetExtension7 = new WorksheetExtension(){ Uri = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}" };
worksheetExtension7.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineReferences timelineReferences7 = new X15.TimelineReferences();
X15.TimelineReference timelineReference7 = new X15.TimelineReference(){ Id = "rId3" };
timelineReferences7.Append(timelineReference7);
worksheetExtension7.Append(timelineReferences7);
worksheetExtensionList7.Append(worksheetExtension7);
worksheet9.Append(sheetDimension9);
worksheet9.Append(sheetViews9);
worksheet9.Append(sheetFormatProperties9);
worksheet9.Append(columns9);
worksheet9.Append(sheetData9);
worksheet9.Append(phoneticProperties15);
worksheet9.Append(pageMargins21);
worksheet9.Append(drawing7);
worksheet9.Append(worksheetExtensionList7);
worksheetPart9.Worksheet = worksheet9;
}
// Generates content of worksheetPart8.
private void GenerateWorksheetPart8Content(WorksheetPart worksheetPart8)
{
Worksheet worksheet8 = new Worksheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
worksheet8.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet8.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet8.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetDimension sheetDimension8 = new SheetDimension(){ Reference = "A1:B5" };
SheetViews sheetViews8 = new SheetViews();
SheetView sheetView8 = new SheetView(){ WorkbookViewId = (UInt32Value)0U };
sheetViews8.Append(sheetView8);
SheetFormatProperties sheetFormatProperties8 = new SheetFormatProperties(){ DefaultRowHeight = 15D };
Columns columns8 = new Columns();
Column column48 = new Column(){ Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 14.140625D, BestFit = true, CustomWidth = true };
Column column49 = new Column(){ Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 17.42578125D, CustomWidth = true };
Column column50 = new Column(){ Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 12.42578125D, CustomWidth = true };
Column column51 = new Column(){ Min = (UInt32Value)4U, Max = (UInt32Value)11U, Width = 13D, BestFit = true, CustomWidth = true };
Column column52 = new Column(){ Min = (UInt32Value)12U, Max = (UInt32Value)12U, Width = 13D, CustomWidth = true };
Column column53 = new Column(){ Min = (UInt32Value)13U, Max = (UInt32Value)20U, Width = 13D, BestFit = true, CustomWidth = true };
Column column54 = new Column(){ Min = (UInt32Value)21U, Max = (UInt32Value)23U, Width = 20.42578125D, BestFit = true, CustomWidth = true };
columns8.Append(column48);
columns8.Append(column49);
columns8.Append(column50);
columns8.Append(column51);
columns8.Append(column52);
columns8.Append(column53);
columns8.Append(column54);
SheetData sheetData8 = new SheetData();
Row row48 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell183 = new Cell(){ CellReference = "A1", StyleIndex = (UInt32Value)5U, DataType = CellValues.SharedString };
CellValue cellValue183 = new CellValue();
cellValue183.Text = "29";
cell183.Append(cellValue183);
Cell cell184 = new Cell(){ CellReference = "B1", DataType = CellValues.SharedString };
CellValue cellValue184 = new CellValue();
cellValue184.Text = "28";
cell184.Append(cellValue184);
row48.Append(cell183);
row48.Append(cell184);
Row row49 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell185 = new Cell(){ CellReference = "A2", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue185 = new CellValue();
cellValue185.Text = "6";
cell185.Append(cellValue185);
Cell cell186 = new Cell(){ CellReference = "B2", StyleIndex = (UInt32Value)7U };
CellValue cellValue186 = new CellValue();
cellValue186.Text = "19";
cell186.Append(cellValue186);
row49.Append(cell185);
row49.Append(cell186);
Row row50 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell187 = new Cell(){ CellReference = "A3", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue187 = new CellValue();
cellValue187.Text = "8";
cell187.Append(cellValue187);
Cell cell188 = new Cell(){ CellReference = "B3", StyleIndex = (UInt32Value)7U };
CellValue cellValue188 = new CellValue();
cellValue188.Text = "13";
cell188.Append(cellValue188);
row50.Append(cell187);
row50.Append(cell188);
Row row51 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell189 = new Cell(){ CellReference = "A4", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue189 = new CellValue();
cellValue189.Text = "11";
cell189.Append(cellValue189);
Cell cell190 = new Cell(){ CellReference = "B4", StyleIndex = (UInt32Value)7U };
CellValue cellValue190 = new CellValue();
cellValue190.Text = "33";
cell190.Append(cellValue190);
row51.Append(cell189);
row51.Append(cell190);
Row row52 = new Row(){ RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell191 = new Cell(){ CellReference = "A5", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue191 = new CellValue();
cellValue191.Text = "30";
cell191.Append(cellValue191);
Cell cell192 = new Cell(){ CellReference = "B5", StyleIndex = (UInt32Value)7U };
CellValue cellValue192 = new CellValue();
cellValue192.Text = "65";
cell192.Append(cellValue192);
row52.Append(cell191);
row52.Append(cell192);
sheetData8.Append(row48);
sheetData8.Append(row49);
sheetData8.Append(row50);
sheetData8.Append(row51);
sheetData8.Append(row52);
PhoneticProperties phoneticProperties14 = new PhoneticProperties(){ FontId = (UInt32Value)1U };
PageMargins pageMargins18 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
S.Drawing drawing6 = new S.Drawing(){ Id = "rId2" };
WorksheetExtensionList worksheetExtensionList6 = new WorksheetExtensionList();
WorksheetExtension worksheetExtension6 = new WorksheetExtension(){ Uri = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}" };
worksheetExtension6.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineReferences timelineReferences6 = new X15.TimelineReferences();
X15.TimelineReference timelineReference6 = new X15.TimelineReference(){ Id = "rId3" };
timelineReferences6.Append(timelineReference6);
worksheetExtension6.Append(timelineReferences6);
worksheetExtensionList6.Append(worksheetExtension6);
worksheet8.Append(sheetDimension8);
worksheet8.Append(sheetViews8);
worksheet8.Append(sheetFormatProperties8);
worksheet8.Append(columns8);
worksheet8.Append(sheetData8);
worksheet8.Append(phoneticProperties14);
worksheet8.Append(pageMargins18);
worksheet8.Append(drawing6);
worksheet8.Append(worksheetExtensionList6);
worksheetPart8.Worksheet = worksheet8;
}
// Generates content of worksheetPart7.
private void GenerateWorksheetPart7Content(WorksheetPart worksheetPart7)
{
Worksheet worksheet7 = new Worksheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
worksheet7.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet7.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet7.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetDimension sheetDimension7 = new SheetDimension(){ Reference = "A1:B5" };
SheetViews sheetViews7 = new SheetViews();
SheetView sheetView7 = new SheetView(){ WorkbookViewId = (UInt32Value)0U };
sheetViews7.Append(sheetView7);
SheetFormatProperties sheetFormatProperties7 = new SheetFormatProperties(){ DefaultRowHeight = 15D };
Columns columns7 = new Columns();
Column column41 = new Column(){ Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 14.140625D, BestFit = true, CustomWidth = true };
Column column42 = new Column(){ Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 17.42578125D, CustomWidth = true };
Column column43 = new Column(){ Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 12.42578125D, CustomWidth = true };
Column column44 = new Column(){ Min = (UInt32Value)4U, Max = (UInt32Value)11U, Width = 13D, BestFit = true, CustomWidth = true };
Column column45 = new Column(){ Min = (UInt32Value)12U, Max = (UInt32Value)12U, Width = 13D, CustomWidth = true };
Column column46 = new Column(){ Min = (UInt32Value)13U, Max = (UInt32Value)20U, Width = 13D, BestFit = true, CustomWidth = true };
Column column47 = new Column(){ Min = (UInt32Value)21U, Max = (UInt32Value)23U, Width = 20.42578125D, BestFit = true, CustomWidth = true };
columns7.Append(column41);
columns7.Append(column42);
columns7.Append(column43);
columns7.Append(column44);
columns7.Append(column45);
columns7.Append(column46);
columns7.Append(column47);
SheetData sheetData7 = new SheetData();
Row row43 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell173 = new Cell(){ CellReference = "A1", StyleIndex = (UInt32Value)5U, DataType = CellValues.SharedString };
CellValue cellValue173 = new CellValue();
cellValue173.Text = "29";
cell173.Append(cellValue173);
Cell cell174 = new Cell(){ CellReference = "B1", DataType = CellValues.SharedString };
CellValue cellValue174 = new CellValue();
cellValue174.Text = "28";
cell174.Append(cellValue174);
row43.Append(cell173);
row43.Append(cell174);
Row row44 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell175 = new Cell(){ CellReference = "A2", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue175 = new CellValue();
cellValue175.Text = "6";
cell175.Append(cellValue175);
Cell cell176 = new Cell(){ CellReference = "B2", StyleIndex = (UInt32Value)7U };
CellValue cellValue176 = new CellValue();
cellValue176.Text = "19";
cell176.Append(cellValue176);
row44.Append(cell175);
row44.Append(cell176);
Row row45 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell177 = new Cell(){ CellReference = "A3", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue177 = new CellValue();
cellValue177.Text = "8";
cell177.Append(cellValue177);
Cell cell178 = new Cell(){ CellReference = "B3", StyleIndex = (UInt32Value)7U };
CellValue cellValue178 = new CellValue();
cellValue178.Text = "13";
cell178.Append(cellValue178);
row45.Append(cell177);
row45.Append(cell178);
Row row46 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell179 = new Cell(){ CellReference = "A4", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue179 = new CellValue();
cellValue179.Text = "11";
cell179.Append(cellValue179);
Cell cell180 = new Cell(){ CellReference = "B4", StyleIndex = (UInt32Value)7U };
CellValue cellValue180 = new CellValue();
cellValue180.Text = "33";
cell180.Append(cellValue180);
row46.Append(cell179);
row46.Append(cell180);
Row row47 = new Row(){ RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:2" } };
Cell cell181 = new Cell(){ CellReference = "A5", StyleIndex = (UInt32Value)6U, DataType = CellValues.SharedString };
CellValue cellValue181 = new CellValue();
cellValue181.Text = "30";
cell181.Append(cellValue181);
Cell cell182 = new Cell(){ CellReference = "B5", StyleIndex = (UInt32Value)7U };
CellValue cellValue182 = new CellValue();
cellValue182.Text = "65";
cell182.Append(cellValue182);
row47.Append(cell181);
row47.Append(cell182);
sheetData7.Append(row43);
sheetData7.Append(row44);
sheetData7.Append(row45);
sheetData7.Append(row46);
sheetData7.Append(row47);
PhoneticProperties phoneticProperties13 = new PhoneticProperties(){ FontId = (UInt32Value)1U };
PageMargins pageMargins15 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
S.Drawing drawing5 = new S.Drawing(){ Id = "rId2" };
WorksheetExtensionList worksheetExtensionList5 = new WorksheetExtensionList();
WorksheetExtension worksheetExtension5 = new WorksheetExtension(){ Uri = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}" };
worksheetExtension5.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineReferences timelineReferences5 = new X15.TimelineReferences();
X15.TimelineReference timelineReference5 = new X15.TimelineReference(){ Id = "rId3" };
timelineReferences5.Append(timelineReference5);
worksheetExtension5.Append(timelineReferences5);
worksheetExtensionList5.Append(worksheetExtension5);
worksheet7.Append(sheetDimension7);
worksheet7.Append(sheetViews7);
worksheet7.Append(sheetFormatProperties7);
worksheet7.Append(columns7);
worksheet7.Append(sheetData7);
worksheet7.Append(phoneticProperties13);
worksheet7.Append(pageMargins15);
worksheet7.Append(drawing5);
worksheet7.Append(worksheetExtensionList5);
worksheetPart7.Worksheet = worksheet7;
}
private void CreateResourcesWorksheet(Dictionary <string, object[]> fieldDictionary, DataTable dataTable,
WorkbookPart workbookPart, Dictionary <string, int> sharedStringIndices)
{
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>("rId1");
var worksheet = new Worksheet(new SheetProperties {
CodeName = "Sheet1"
},
new SheetDimension
{
Reference =
string.Format("A1:{0}{1}",
GetColId(fieldDictionary.Count - 1),
dataTable.Rows.Count + 2)
}, new SheetViews(
new SheetView(
new Pane
{
VerticalSplit = 1D,
TopLeftCell = "A3",
ActivePane = PaneValues.BottomLeft,
State = PaneStateValues.Frozen
},
new Selection
{
ActiveCell = "A2",
SequenceOfReferences =
new ListValue <StringValue> {
InnerText = "A2"
}
},
new Selection {
Pane = PaneValues.BottomLeft
})
{
TabSelected = true,
TopLeftCell = "A2",
WorkbookViewId = (UInt32Value)0U
}))
{
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac"
}
};
worksheet.AddNamespaceDeclaration("r", R_SCHEMA);
worksheet.AddNamespaceDeclaration("mc", MC_SCHEMA);
worksheet.AddNamespaceDeclaration("x14ac", X14_AC_SCHEMA);
var columns = new Columns();
UInt32Value colIndex = 1U;
foreach (var pair in fieldDictionary)
{
var column = new Column
{
Min = colIndex,
Max = colIndex,
Width = 9.140625D,
Style = 1U,
BestFit = true,
CustomWidth = true
};
if (pair.Key.Equals("ID") || pair.Key.Equals("Title"))
{
column.Style = 5U;
}
if (pair.Key.Equals("SharePointAccount") && new Act(_spWeb).IsOnline)
{
column.Hidden = true;
}
columns.AppendChild(column);
colIndex++;
}
columns.AppendChild(new Column {
Min = colIndex, Max = 16384U, Width = 9.140625D, Style = 1U
});
var sheetData = new SheetData();
for (UInt32Value i = 1; i <= dataTable.Rows.Count + 2; i++)
{
var row = new Row {
RowIndex = i
};
if (i == 1)
{
row.Hidden = true;
}
else if (i == 2)
{
row.StyleIndex = 4U;
row.CustomFormat = true;
}
var rowId = (int)(i - 1);
for (int j = 0; j < fieldDictionary.Count; j++)
{
var cellValue = new CellValue();
KeyValuePair <string, object[]> pair = fieldDictionary.ElementAt(j);
object oValue = null;
string value = string.Empty;
if (rowId > 1)
{
oValue = dataTable.Rows[rowId - 2][pair.Key];
if (pair.Key.Equals("ResourceLevel"))
{
foreach (var l in ((IList <string>)pair.Value[2])
.Select(level => level.Split('|'))
.Where(l => l[0].Equals(oValue.ToString())))
{
oValue = l[1];
break;
}
}
value = oValue.ToString();
}
switch (rowId)
{
case 0:
cellValue.Text = sharedStringIndices[pair.Key].ToString(CultureInfo.InvariantCulture);
break;
case 1:
cellValue.Text =
sharedStringIndices[pair.Value[0].ToString()].ToString(CultureInfo.InvariantCulture);
break;
default:
cellValue.Text = sharedStringIndices[value].ToString(CultureInfo.InvariantCulture);
break;
}
var cell = new Cell {
CellReference = GetColId(j) + i, StyleIndex = 1U
};
if (rowId > 1)
{
switch ((SPFieldType)pair.Value[1])
{
case SPFieldType.Counter:
case SPFieldType.Integer:
case SPFieldType.Number:
cellValue.Text = value;
break;
case SPFieldType.DateTime:
cell.StyleIndex = 3U;
cellValue.Text = oValue != null && oValue != DBNull.Value
? ((DateTime)oValue).ToOADate()
.ToString(CultureInfo.InvariantCulture)
: null;
break;
case SPFieldType.Currency:
cell.StyleIndex = 2U;
cellValue.Text = value;
break;
case SPFieldType.Boolean:
cell.DataType = CellValues.Boolean;
cellValue.Text = value;
break;
default:
cell.DataType = CellValues.SharedString;
break;
}
switch (pair.Key)
{
case "ID":
case "Title":
case "Generic":
case "SharePointAccount":
case "Email":
cell.StyleIndex = 5U;
break;
}
}
else
{
cell.DataType = CellValues.SharedString;
}
if (i == 2)
{
cell.StyleIndex = 4U;
}
cell.AppendChild(cellValue);
row.AppendChild(cell);
}
sheetData.AppendChild(row);
}
var worksheetExtensionList = new WorksheetExtensionList();
var worksheetExtension = new WorksheetExtension {
Uri = "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}"
};
worksheetExtension.AddNamespaceDeclaration("x14", X14_SCHEMA);
var dataValidations = new X14.DataValidations {
Count = 0U
};
dataValidations.AddNamespaceDeclaration("xm", XM_SCHEMA);
for (int i = 0; i < fieldDictionary.Count; i++)
{
string colId = GetColId(i);
KeyValuePair <string, object[]> pair = fieldDictionary.ElementAt(i);
var spFieldType = (SPFieldType)pair.Value[1];
if (spFieldType != SPFieldType.Boolean && spFieldType != SPFieldType.Lookup &&
spFieldType != SPFieldType.User && spFieldType != SPFieldType.Choice)
{
continue;
}
if (pair.Key.Equals("SharePointAccount"))
{
continue;
}
if ((bool)pair.Value[3])
{
continue;
}
dataValidations.Count++;
var dataValidation = new X14.DataValidation
{
Type = DataValidationValues.List,
AllowBlank = true,
ShowInputMessage = true,
ShowErrorMessage = true
};
string formulaValue = string.Empty;
switch (spFieldType)
{
case SPFieldType.Boolean:
formulaValue = "BValues!$A$1:$A$2";
break;
case SPFieldType.Lookup:
case SPFieldType.Choice:
int count = ((List <string>)pair.Value[2]).Count();
formulaValue = pair.Key.Replace("_x0020_", "__") + "Values!$A$1" +
(count > 1 ? ":$A$" + count : string.Empty);
break;
case SPFieldType.User:
formulaValue = "DNValues!$D:$D";
break;
}
var dataValidationForumla = new X14.DataValidationForumla1();
var formula = new Formula {
Text = formulaValue
};
dataValidationForumla.AppendChild(formula);
var refSeq = new ReferenceSequence {
Text = string.Format("{0}3:{0}1048576", colId)
};
dataValidation.Append(dataValidationForumla, refSeq);
dataValidations.AppendChild(dataValidation);
}
worksheetExtension.AppendChild(dataValidations);
worksheetExtensionList.AppendChild(worksheetExtension);
worksheet.Append(columns, sheetData, worksheetExtensionList);
worksheetPart.Worksheet = worksheet;
}