// Adds child parts and generates content of the specified part.
public static void CreateTable(WorkbookPart workbookPart, WorksheetPart worksheetPart, string[] columns, int topLeftColumn, int topLeftRow, int width, int height)
{
List<WorksheetPart> worksheets = workbookPart.GetPartsOfType<WorksheetPart>().ToList();
uint maxTableId = worksheets.Select(ws => ws.TableDefinitionParts.ToList()).SelectMany(tableDefinitions => tableDefinitions).Aggregate<TableDefinitionPart, uint>(0, (current, tableDef) => Math.Max(tableDef.Table.Id, current));
uint tableId = maxTableId + 1;
var tables = new TableParts { Count = 1U };
worksheetPart.Worksheet.Append((IEnumerable<OpenXmlElement>)tables);
var newTableDefnPart = worksheetPart.AddNewPart<TableDefinitionPart>();
string relationshipId = worksheetPart.GetIdOfPart(newTableDefnPart);
string cellReference = string.Format("{0}{1}:{2}{3}", GetColumnIdentifier(topLeftColumn), topLeftRow, GetColumnIdentifier(topLeftColumn + width - 1), topLeftRow + height);
var table1 = new Table { Id = tableId, Name = "Table" + relationshipId, DisplayName = "Table" + relationshipId, Reference = cellReference, TotalsRowShown = false };
var autoFilter1 = new AutoFilter { Reference = cellReference };
var tableColumns1 = new TableColumns { Count = (uint)columns.Length };
for (int iColumn = 0; iColumn < columns.Length; iColumn++)
{
var tableColumn = new TableColumn { Id = (UInt32Value)(uint)iColumn + 1, Name = columns[iColumn] };
tableColumns1.Append((IEnumerable<OpenXmlElement>)tableColumn);
}
var tableStyleInfo1 = new TableStyleInfo { Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table1.Append((IEnumerable<OpenXmlElement>)autoFilter1);
table1.Append((IEnumerable<OpenXmlElement>)tableColumns1);
table1.Append((IEnumerable<OpenXmlElement>)tableStyleInfo1);
newTableDefnPart.Table = table1;
var table = new TablePart { Id = relationshipId };
tables.Append((IEnumerable<OpenXmlElement>)table);
//TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
//worksheetPart.Worksheet.Append(tableStyles1);
}
private ReadTableConfig()
{
table = new Hashtable();
FileStream fd = new FileStream("ConfigTable.json", FileMode.Open);
byte[] buff = new byte[fd.Length];
fd.Read(buff, 0, (int)fd.Length);
fd.Close();
fd.Dispose();
string str = Encoding.Default.GetString(buff);
JObject jsonObject = JObject.Parse(str);
ParseLine(ref lineGlobal, jsonObject["LineStyle"]);
ParseCell(ref cellGlobal, jsonObject["CellStyle"]);
ParseTable(ref tableGlobal, jsonObject["TableStyle"]);
tableRegion = new Region();
JArray list = (JArray)jsonObject["list"];
for (int i = 0; i < list.Count; ++i)
{
TableParts part = ParseParts(list[i]);
table.Add(part.id, part);
tableRegion.Union(part.tableSytle.rect);
}
}
TableParts ParseParts(JToken json)
{
TableParts part = new TableParts();
part.tableSytle = tableGlobal;
part.lineStyle = lineGlobal;
ParseTable(ref part.tableSytle, json["TableStyle"]);
part.id = (string)json["id"];
part.linePoints = ParsePoints(json["Points"]);
return(part);
}
public void creerTableau(WorksheetPart worksheetPart, DataTable dt, int indice)
{
obtentionBornesColonnes(dt);
//Obtention des titres pour la creation des colonnes de la tables
List <string> titres = new List <string>();
foreach (DataColumn t in dt.Columns)
{
if (t.ColumnName != "style" && t.ColumnName != "graph")
{
if (t.ColumnName == " ")
{
titres.Add("%");
}
else
{
titres.Add(t.ColumnName);
}
}
}
//ecriture des titres
SheetData sd = worksheetPart.Worksheet.GetFirstChild <SheetData>();
Row rr = XcelWin.creerTitres(dt, 1);
sd.AppendChild(rr);
//ecriture des données
int index = 2;
foreach (DataRow r in dt.Rows)
{
rr = XcelWin.creerLigne(r, index, 0, nbColonneConfig);
sd.AppendChild(rr);
index++;
}
XcelWin.AddTableDefinitionPart(worksheetPart, titres, dt.Rows.Count + 1, dt.Columns.Count - nbColonneConfig, indice);
TableParts tableParts1 = new TableParts()
{
Count = (UInt32Value)1U
};
TablePart tablePart1 = new TablePart()
{
Id = "vId1"
};
tableParts1.Append(tablePart1);
worksheetPart.Worksheet.Append(tableParts1);
}
public void Load()
{
TableAchievement.GetInstance().Read();
TableEnemy.GetInstance().Read();
TableLanguage.GetInstance().Read();
TableParts.GetInstance().Read();
xjlFont = Resources.Load <UIFont>("Font/xjlFont");
jpFont = Resources.Load <UIFont>("Font/jpFont");
BackgroundAtlas = Resources.Load <UIAtlas>("Background/Background");
EnemyAtlas = Resources.Load <UIAtlas>("Enemy/Enemy");
PartsAtlas = Resources.Load <UIAtlas>("Parts/Parts");
UiAtlas = Resources.Load <UIAtlas>("UI/UI");
Debug.Log("ResourceLoadDone!");
}
private void DefineTable(WorksheetPart worksheetPart, LineItem[] lineItems)
{
TableDefinitionPart tableDefinitionPart = worksheetPart.AddNewPart <TableDefinitionPart>("rId" + (worksheetPart.TableDefinitionParts.Count() + 1));
int tableNo = worksheetPart.TableDefinitionParts.Count();
string reference = $"A1:{((char)(64 + _fields.Length))}{lineItems.Length}";
Table table = new Table()
{
Id = (UInt32)tableNo, Name = "Table" + tableNo, DisplayName = "Table" + tableNo, Reference = reference, TotalsRowShown = false
};
AutoFilter autoFilter = new AutoFilter()
{
Reference = reference
};
TableColumns tableColumns = new TableColumns()
{
Count = (UInt32)_fields.Length
};
for (int i = 0; i < _fields.Length; i++)
{
tableColumns.Append(new TableColumn()
{
Id = (UInt32)(i + 1), Name = _fields[i]
});
}
table.Append(autoFilter);
table.Append(tableColumns);
tableDefinitionPart.Table = table;
TableParts tableParts = new TableParts()
{
Count = (UInt32)1
};
TablePart tablePart = new TablePart()
{
Id = "rId" + tableNo
};
tableParts.Append(tablePart);
worksheetPart.Worksheet.Append(tableParts);
}
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 worksheetPart1.
private static void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1, DataTable dtSource, bool encloseInDataTable)
{
Worksheet worksheet1 = new Worksheet();
worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
SheetData sheetData1 = new SheetData();
// todo: refactor to use bulk xml insert if performance is slow due to large data sets
Row row1 = new Row()
{
RowIndex = 1U
};
Cell cell1;
InlineString inlineString1;
Text text1;
CellValue cv;
// add header row
char charCol = 'A';
foreach (DataColumn dc in dtSource.Columns)
{
cell1 = new Cell()
{
CellReference = charCol.ToString() + "1", DataType = CellValues.InlineString
};
inlineString1 = new InlineString();
text1 = new Text();
text1.Text = dc.ColumnName;
inlineString1.Append(text1);
cell1.Append(inlineString1);
row1.Append(cell1);
charCol = (char)((int)charCol + 1);
}
sheetData1.Append(row1);
// add rows
int rowIx = 2;
foreach (DataRow dr in dtSource.Rows)
{
row1 = new Row()
{
RowIndex = (uint)rowIx
};
charCol = 'A';
foreach (DataColumn dc in dtSource.Columns)
{
if (dc.DataType == typeof(int) || dc.DataType == typeof(decimal))
{
cell1 = new Cell()
{
CellReference = charCol.ToString() + rowIx.ToString()
};
}
else
{
cell1 = new Cell()
{
CellReference = charCol.ToString() + rowIx.ToString(), DataType = CellValues.String
};
}
cv = new CellValue();
cv.Text = dr[dc.ColumnName].ToString();
cell1.Append(cv);
row1.Append(cell1);
charCol = (char)((int)charCol + 1);
}
sheetData1.Append(row1);
rowIx++;
}
worksheet1.Append(sheetData1);
string tableReferenceId = "rId2";
if (encloseInDataTable)
{
TableParts tableParts1 = new TableParts()
{
Count = (UInt32Value)1U
};
TablePart tablePart1 = new TablePart()
{
Id = tableReferenceId
};
tableParts1.Append(tablePart1);
worksheet1.Append(tableParts1);
}
worksheetPart1.Worksheet = worksheet1;
if (encloseInDataTable)
{
TableDefinitionPart tableDefinitionPart1 = worksheetPart1.AddNewPart <TableDefinitionPart>(tableReferenceId);
GenerateTableDefinitionPart1Content(tableDefinitionPart1, dtSource);
}
} // generate content
private void generateTaskDataWorksheet(WorksheetPart worksheetPart4)
{
foreach (KeyValuePair <string, userDetailsObject> person in reportData.people)
{
string displayName = person.Value.displayName;
string userid = person.Key;
Console.WriteLine(displayName);
Console.WriteLine(userid);
}
Worksheet worksheet4 = new Worksheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
worksheet4.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet4.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet4.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
//worksheet4.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
//worksheet4.AddNamespaceDeclaration("xr2", "http://schemas.microsoft.com/office/spreadsheetml/2015/revision2");
//worksheet4.AddNamespaceDeclaration("xr3", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3");
//worksheet4.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", genStrGuid()));
SheetDimension sheetDimension4 = new SheetDimension()
{
Reference = "A1:J" + (reportData.tasks.value.Count + 1).ToString()
};
SheetViews sheetViews4 = new SheetViews();
SheetView sheetView4 = new SheetView()
{
WorkbookViewId = (UInt32Value)0U
};
Selection selection2 = new Selection()
{
ActiveCell = "G23", SequenceOfReferences = new ListValue <StringValue>()
{
InnerText = "G23"
}
};
sheetView4.Append(selection2);
sheetViews4.Append(sheetView4);
SheetFormatProperties sheetFormatProperties4 = new SheetFormatProperties()
{
DefaultRowHeight = 15D, DyDescent = 0.25D
};
Columns columns4 = new Columns();
Column column18 = new Column()
{
Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 26.85546875D, BestFit = true, CustomWidth = true
};
Column column19 = new Column()
{
Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 12.85546875D, BestFit = true, CustomWidth = true
};
Column column20 = new Column()
{
Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 34.42578125D, BestFit = true, CustomWidth = true
};
Column column21 = new Column()
{
Min = (UInt32Value)4U, Max = (UInt32Value)4U, Width = 21.7109375D, BestFit = true, CustomWidth = true
};
Column column22 = new Column()
{
Min = (UInt32Value)5U, Max = (UInt32Value)5U, Width = 11.140625D, BestFit = true, CustomWidth = true
};
Column column23 = new Column()
{
Min = (UInt32Value)6U, Max = (UInt32Value)6U, Width = 15.7109375D, BestFit = true, CustomWidth = true
};
Column column24 = new Column()
{
Min = (UInt32Value)7U, Max = (UInt32Value)7U, Width = 18D, BestFit = true, CustomWidth = true
};
Column column25 = new Column()
{
Min = (UInt32Value)8U, Max = (UInt32Value)8U, Width = 16D, BestFit = true, CustomWidth = true
};
Column column26 = new Column()
{
Min = (UInt32Value)9U, Max = (UInt32Value)9U, Width = 15.5703125D, BestFit = true, CustomWidth = true
};
Column column27 = new Column()
{
Min = (UInt32Value)10U, Max = (UInt32Value)10U, Width = 35.85546875D, BestFit = true, CustomWidth = true
};
columns4.Append(column18);
columns4.Append(column19);
columns4.Append(column20);
columns4.Append(column21);
columns4.Append(column22);
columns4.Append(column23);
columns4.Append(column24);
columns4.Append(column25);
columns4.Append(column26);
columns4.Append(column27);
SheetData sheetData4 = new SheetData();
Row headerRow = new Row()
{
RowIndex = (UInt32Value)1U, Spans = new ListValue <StringValue>()
{
InnerText = "1:10"
}, DyDescent = 0.25D
};
for (int i = 1; i <= headers.Count; i++)
{
Console.WriteLine("Header value: {0} ", headers[i - 1]);
Console.WriteLine("Header cell ref: {0} ", rowArry[i - 1].ToString() + "1");
Cell cellHeader = new Cell()
{
CellReference = rowArry[i - 1].ToString() + (1).ToString(), DataType = CellValues.InlineString
};
InlineString cellHeaderValue = new InlineString();
cellHeaderValue.Text = new Text(headers[i - 1]);
cellHeader.Append(cellHeaderValue);
headerRow.Append(cellHeader);
}
sheetData4.Append(headerRow);
for (int tIndex = 1; tIndex <= reportData.tasks.value.Count; tIndex++)
{
int iCol = 0;
uint iRow = (uint)tIndex + 1;
Row dataRow = new Row()
{
RowIndex = new UInt32Value((uint)iRow), Spans = new ListValue <StringValue>()
{
InnerText = "1:10"
}, DyDescent = 0.25D
};
taskObject task = reportData.tasks.value[tIndex - 1];
Cell cellData = new Cell()
{
CellReference = rowArry[iCol++].ToString() + (iRow).ToString(), DataType = CellValues.InlineString
};
//CellValue cellValue = new CellValue
InlineString cellValue = new InlineString(new Text(task.title));
cellData.Append(cellValue);
dataRow.Append(cellData);
cellData = new Cell()
{
CellReference = rowArry[iCol++].ToString() + (iRow).ToString(), DataType = CellValues.InlineString
};
cellValue = new InlineString();
string userid = "";
foreach (KeyValuePair <string, assignmentObject> pair in task.assignments)
{
userid = pair.Key;
cellValue.Text = new Text(reportData.people[pair.Key].displayName);
break;
}
cellData.Append(cellValue);
dataRow.Append(cellData);
string email = " ";
if (userid != "")
{
email = reportData.people[userid].mail;
}
cellData = new Cell()
{
CellReference = rowArry[iCol++].ToString() + (iRow).ToString(), DataType = CellValues.InlineString
};
cellValue = new InlineString(new Text(email));
cellData.Append(cellValue);
dataRow.Append(cellData);
string bucketName = "";
foreach (bucketObject b in reportData.buckets)
{
if (b.id == task.bucketId)
{
bucketName = b.name;
}
}
cellData = new Cell()
{
CellReference = rowArry[iCol++].ToString() + (iRow).ToString(), DataType = CellValues.InlineString
};
cellValue = new InlineString(new Text(bucketName));
cellData.Append(cellValue);
dataRow.Append(cellData);
cellData = new Cell()
{
CellReference = rowArry[iCol++].ToString() + (iRow).ToString(), DataType = CellValues.InlineString
};
cellValue = new InlineString();
if (task.percentComplete == 0)
{
cellValue.Text = new Text("Not started");
}
else
{
cellValue.Text = new Text(progress[100 / task.percentComplete]);
}
cellData.Append(cellValue);
dataRow.Append(cellData);
cellData = new Cell()
{
CellReference = rowArry[iCol++].ToString() + (iRow).ToString(), DataType = CellValues.InlineString
};
cellValue = new InlineString(new Text(getStringFromDate(task.dueDateTime)));
cellData.Append(cellValue);
dataRow.Append(cellData);
cellData = new Cell()
{
CellReference = rowArry[iCol++].ToString() + (iRow).ToString(), DataType = CellValues.InlineString
};
cellValue = new InlineString(new Text(getStringFromDate(task.completedDateTime)));
cellData.Append(cellValue);
dataRow.Append(cellData);
string completedByUser = "";
cellData = new Cell()
{
CellReference = rowArry[iCol++].ToString() + (iRow).ToString(), DataType = CellValues.InlineString
};
if (task.completedBy != null)
{
completedByUser = reportData.people[task.completedBy].displayName;
}
cellValue = new InlineString(new Text(completedByUser));
cellData.Append(cellValue);
dataRow.Append(cellData);
cellData = new Cell()
{
CellReference = rowArry[iCol++].ToString() + (iRow).ToString(), DataType = CellValues.InlineString
};
cellValue = new InlineString(new Text(getStringFromDate(task.createdDateTime)));
cellData.Append(cellValue);
dataRow.Append(cellData);
cellData = new Cell()
{
CellReference = rowArry[iCol++].ToString() + (iRow).ToString(), DataType = CellValues.InlineString
};
cellValue = new InlineString(new Text(task.id));
cellData.Append(cellValue);
dataRow.Append(cellData);
sheetData4.Append(dataRow);
//Debugger.Break();
}
PageMargins pageMargins6 = new PageMargins()
{
Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D
};
PageSetup pageSetup6 = new PageSetup()
{
Orientation = OrientationValues.Portrait, Id = "rId1"
};
TableParts tableParts1 = new TableParts()
{
Count = (UInt32Value)1U
};
TablePart tablePart1 = new TablePart()
{
Id = "rId1"
};
tableParts1.Append(tablePart1);
worksheet4.Append(sheetDimension4);
worksheet4.Append(sheetViews4);
worksheet4.Append(sheetFormatProperties4);
worksheet4.Append(columns4);
worksheet4.Append(sheetData4);
worksheet4.Append(pageMargins6);
worksheet4.Append(pageSetup6);
worksheet4.Append(tableParts1);
worksheetPart4.Worksheet = worksheet4;
}
/// <summary>
/// Generate Excel Document.
/// </summary>
/// <param name="reportResult">QueryResult</param>
/// <returns>MemoryStream</returns>
public static MemoryStream CreateExcelDocument(ReportResult reportResult, List <DataRow> rows)
{
sharedStringIndex = 0;
sharedStrings = new ConcurrentDictionary <int, string>();
var ms = new MemoryStream();
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true))
{
// Create the Workbook
WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();
spreadSheet.WorkbookPart.Workbook = new Workbook();
// A Workbook must only have exactly one <Sheets> section
spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>("rId1");
newWorksheetPart.Worksheet = new Worksheet();
//Add columns of constant width
int columnIndx = 1;
Columns cols = new Columns();
foreach (ReportColumn column in reportResult.Metadata.ReportColumns.Values)
{
if (!column.IsHidden && column.Type != "Image")
{
cols.Append(CreateColumnData(columnIndx));
columnIndx++;
}
}
newWorksheetPart.Worksheet.Append(cols);
newWorksheetPart.Worksheet.Save();
// Create a new Excel worksheet
SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData());
// Create Styles and Insert into Workbook
WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId3");
Stylesheet styles = new ExportDataStylesheet();
styles.Save(stylesPart);
// Insert Datatable data into the worksheet.
InsertTableData(reportResult, sheetData, rows);
//Create table part.
TableDefinitionPart tableDefinitionPart = newWorksheetPart.AddNewPart <TableDefinitionPart>("rId1");
GenerateTablePartContent(tableDefinitionPart, reportResult, rows, columnIndx - 2);
TableParts tableParts1 = new TableParts()
{
Count = (UInt32Value)1U
};
TablePart tablePart1 = new TablePart()
{
Id = "rId1"
};
tableParts1.Append(tablePart1);
newWorksheetPart.Worksheet.Append(tableParts1);
newWorksheetPart.Worksheet.Save();
//add shared stringd
SharedStringTablePart sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart <SharedStringTablePart>("rId2");
SharedStringTable sharedStringTable = new SharedStringTable();
foreach (string sharedString in sharedStrings.Values)
{
CreateSharedStringItem(sharedStringTable, sharedString);
}
sharedStringTablePart.SharedStringTable = sharedStringTable;
// Save the worksheet.
newWorksheetPart.Worksheet.Save();
// Link this worksheet to our workbook
spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>().AppendChild(new Sheet()
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = 1,
Name = "Table"
});
// Save the workbook.
spreadSheet.WorkbookPart.Workbook.Save();
}
return(ms);
}
// Generates content of worksheetPart5.
private void GenerateWorksheetPart5Content(WorksheetPart worksheetPart5)
{
Worksheet worksheet5 = new Worksheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
worksheet5.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet5.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet5.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetDimension sheetDimension5 = new SheetDimension(){ Reference = "A1:K11" };
SheetViews sheetViews5 = new SheetViews();
SheetView sheetView5 = new SheetView(){ TabSelected = true, WorkbookViewId = (UInt32Value)0U };
sheetViews5.Append(sheetView5);
SheetFormatProperties sheetFormatProperties5 = new SheetFormatProperties(){ DefaultRowHeight = 15D };
Columns columns5 = new Columns();
Column column25 = new Column(){ Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 10.7109375D, BestFit = true, CustomWidth = true };
Column column26 = new Column(){ Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 10.140625D, BestFit = true, CustomWidth = true };
Column column27 = new Column(){ Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 8.140625D, BestFit = true, CustomWidth = true };
Column column28 = new Column(){ Min = (UInt32Value)4U, Max = (UInt32Value)4U, Width = 10.42578125D, BestFit = true, CustomWidth = true };
Column column29 = new Column(){ Min = (UInt32Value)5U, Max = (UInt32Value)5U, Width = 12.85546875D, BestFit = true, CustomWidth = true };
Column column30 = new Column(){ Min = (UInt32Value)6U, Max = (UInt32Value)6U, Width = 11.5703125D, BestFit = true, CustomWidth = true };
Column column31 = new Column(){ Min = (UInt32Value)8U, Max = (UInt32Value)8U, Width = 12.85546875D, BestFit = true, CustomWidth = true };
Column column32 = new Column(){ Min = (UInt32Value)9U, Max = (UInt32Value)9U, Width = 10.42578125D, BestFit = true, CustomWidth = true };
Column column33 = new Column(){ Min = (UInt32Value)11U, Max = (UInt32Value)11U, Width = 12.85546875D, BestFit = true, CustomWidth = true };
columns5.Append(column25);
columns5.Append(column26);
columns5.Append(column27);
columns5.Append(column28);
columns5.Append(column29);
columns5.Append(column30);
columns5.Append(column31);
columns5.Append(column32);
columns5.Append(column33);
SheetData sheetData5 = new SheetData();
Row row27 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell100 = new Cell(){ CellReference = "A1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue100 = new CellValue();
cellValue100.Text = "0";
cell100.Append(cellValue100);
Cell cell101 = new Cell(){ CellReference = "B1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue101 = new CellValue();
cellValue101.Text = "1";
cell101.Append(cellValue101);
Cell cell102 = new Cell(){ CellReference = "C1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue102 = new CellValue();
cellValue102.Text = "2";
cell102.Append(cellValue102);
Cell cell103 = new Cell(){ CellReference = "D1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue103 = new CellValue();
cellValue103.Text = "3";
cell103.Append(cellValue103);
Cell cell104 = new Cell(){ CellReference = "E1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue104 = new CellValue();
cellValue104.Text = "4";
cell104.Append(cellValue104);
Cell cell105 = new Cell(){ CellReference = "F1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue105 = new CellValue();
cellValue105.Text = "5";
cell105.Append(cellValue105);
Cell cell106 = new Cell(){ CellReference = "H1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue106 = new CellValue();
cellValue106.Text = "26";
cell106.Append(cellValue106);
Cell cell107 = new Cell(){ CellReference = "I1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue107 = new CellValue();
cellValue107.Text = "3";
cell107.Append(cellValue107);
Cell cell108 = new Cell(){ CellReference = "K1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue108 = new CellValue();
cellValue108.Text = "27";
cell108.Append(cellValue108);
row27.Append(cell100);
row27.Append(cell101);
row27.Append(cell102);
row27.Append(cell103);
row27.Append(cell104);
row27.Append(cell105);
row27.Append(cell106);
row27.Append(cell107);
row27.Append(cell108);
Row row28 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell109 = new Cell(){ CellReference = "A2", StyleIndex = (UInt32Value)3U };
CellValue cellValue109 = new CellValue();
cellValue109.Text = "36526.653255014644";
cell109.Append(cellValue109);
Cell cell110 = new Cell(){ CellReference = "B2", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue110 = new CellValue();
cellValue110.Text = "6";
cell110.Append(cellValue110);
Cell cell111 = new Cell(){ CellReference = "C2", StyleIndex = (UInt32Value)1U };
CellValue cellValue111 = new CellValue();
cellValue111.Text = "19";
cell111.Append(cellValue111);
Cell cell112 = new Cell(){ CellReference = "D2", StyleIndex = (UInt32Value)1U };
CellValue cellValue112 = new CellValue();
cellValue112.Text = "2375";
cell112.Append(cellValue112);
Cell cell113 = new Cell(){ CellReference = "E2", StyleIndex = (UInt32Value)3U };
CellValue cellValue113 = new CellValue();
cellValue113.Text = "36531.653255014644";
cell113.Append(cellValue113);
Cell cell114 = new Cell(){ CellReference = "F2", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue114 = new CellValue();
cellValue114.Text = "7";
cell114.Append(cellValue114);
Cell cell115 = new Cell(){ CellReference = "H2", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue115 = new CellValue();
cellValue115.Text = "6";
cell115.Append(cellValue115);
Cell cell116 = new Cell(){ CellReference = "I2", StyleIndex = (UInt32Value)4U };
CellValue cellValue116 = new CellValue();
cellValue116.Text = "125";
cell116.Append(cellValue116);
Cell cell117 = new Cell(){ CellReference = "K2", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue117 = new CellValue();
cellValue117.Text = "20";
cell117.Append(cellValue117);
row28.Append(cell109);
row28.Append(cell110);
row28.Append(cell111);
row28.Append(cell112);
row28.Append(cell113);
row28.Append(cell114);
row28.Append(cell115);
row28.Append(cell116);
row28.Append(cell117);
Row row29 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell118 = new Cell(){ CellReference = "A3", StyleIndex = (UInt32Value)3U };
CellValue cellValue118 = new CellValue();
cellValue118.Text = "36527.149391461739";
cell118.Append(cellValue118);
Cell cell119 = new Cell(){ CellReference = "B3", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue119 = new CellValue();
cellValue119.Text = "8";
cell119.Append(cellValue119);
Cell cell120 = new Cell(){ CellReference = "C3", StyleIndex = (UInt32Value)1U };
CellValue cellValue120 = new CellValue();
cellValue120.Text = "13";
cell120.Append(cellValue120);
Cell cell121 = new Cell(){ CellReference = "D3", StyleIndex = (UInt32Value)1U };
CellValue cellValue121 = new CellValue();
cellValue121.Text = "15990";
cell121.Append(cellValue121);
Cell cell122 = new Cell(){ CellReference = "E3", StyleIndex = (UInt32Value)3U };
CellValue cellValue122 = new CellValue();
cellValue122.Text = "36532.149391461739";
cell122.Append(cellValue122);
Cell cell123 = new Cell(){ CellReference = "F3", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue123 = new CellValue();
cellValue123.Text = "9";
cell123.Append(cellValue123);
Cell cell124 = new Cell(){ CellReference = "H3", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue124 = new CellValue();
cellValue124.Text = "14";
cell124.Append(cellValue124);
Cell cell125 = new Cell(){ CellReference = "I3", StyleIndex = (UInt32Value)4U };
CellValue cellValue125 = new CellValue();
cellValue125.Text = "250";
cell125.Append(cellValue125);
Cell cell126 = new Cell(){ CellReference = "K3", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue126 = new CellValue();
cellValue126.Text = "9";
cell126.Append(cellValue126);
row29.Append(cell118);
row29.Append(cell119);
row29.Append(cell120);
row29.Append(cell121);
row29.Append(cell122);
row29.Append(cell123);
row29.Append(cell124);
row29.Append(cell125);
row29.Append(cell126);
Row row30 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell127 = new Cell(){ CellReference = "A4", StyleIndex = (UInt32Value)3U };
CellValue cellValue127 = new CellValue();
cellValue127.Text = "37620.280037325079";
cell127.Append(cellValue127);
Cell cell128 = new Cell(){ CellReference = "B4", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue128 = new CellValue();
cellValue128.Text = "11";
cell128.Append(cellValue128);
Cell cell129 = new Cell(){ CellReference = "C4", StyleIndex = (UInt32Value)1U };
CellValue cellValue129 = new CellValue();
cellValue129.Text = "4";
cell129.Append(cellValue129);
Cell cell130 = new Cell(){ CellReference = "D4", StyleIndex = (UInt32Value)1U };
CellValue cellValue130 = new CellValue();
cellValue130.Text = "312";
cell130.Append(cellValue130);
Cell cell131 = new Cell(){ CellReference = "E4", StyleIndex = (UInt32Value)3U };
CellValue cellValue131 = new CellValue();
cellValue131.Text = "37624.280037325079";
cell131.Append(cellValue131);
Cell cell132 = new Cell(){ CellReference = "F4", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue132 = new CellValue();
cellValue132.Text = "12";
cell132.Append(cellValue132);
Cell cell133 = new Cell(){ CellReference = "H4", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue133 = new CellValue();
cellValue133.Text = "16";
cell133.Append(cellValue133);
Cell cell134 = new Cell(){ CellReference = "I4", StyleIndex = (UInt32Value)4U };
CellValue cellValue134 = new CellValue();
cellValue134.Text = "50";
cell134.Append(cellValue134);
Cell cell135 = new Cell(){ CellReference = "K4", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue135 = new CellValue();
cellValue135.Text = "10";
cell135.Append(cellValue135);
row30.Append(cell127);
row30.Append(cell128);
row30.Append(cell129);
row30.Append(cell130);
row30.Append(cell131);
row30.Append(cell132);
row30.Append(cell133);
row30.Append(cell134);
row30.Append(cell135);
Row row31 = new Row(){ RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell136 = new Cell(){ CellReference = "A5", StyleIndex = (UInt32Value)3U };
CellValue cellValue136 = new CellValue();
cellValue136.Text = "37620.86855653645";
cell136.Append(cellValue136);
Cell cell137 = new Cell(){ CellReference = "B5", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue137 = new CellValue();
cellValue137.Text = "11";
cell137.Append(cellValue137);
Cell cell138 = new Cell(){ CellReference = "C5", StyleIndex = (UInt32Value)1U };
CellValue cellValue138 = new CellValue();
cellValue138.Text = "29";
cell138.Append(cellValue138);
Cell cell139 = new Cell(){ CellReference = "D5", StyleIndex = (UInt32Value)1U };
CellValue cellValue139 = new CellValue();
cellValue139.Text = "2262";
cell139.Append(cellValue139);
Cell cell140 = new Cell(){ CellReference = "E5", StyleIndex = (UInt32Value)3U };
CellValue cellValue140 = new CellValue();
cellValue140.Text = "37621.86855653645";
cell140.Append(cellValue140);
Cell cell141 = new Cell(){ CellReference = "F5", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue141 = new CellValue();
cellValue141.Text = "12";
cell141.Append(cellValue141);
Cell cell142 = new Cell(){ CellReference = "H5", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue142 = new CellValue();
cellValue142.Text = "8";
cell142.Append(cellValue142);
Cell cell143 = new Cell(){ CellReference = "I5", StyleIndex = (UInt32Value)4U };
CellValue cellValue143 = new CellValue();
cellValue143.Text = "1230";
cell143.Append(cellValue143);
Cell cell144 = new Cell(){ CellReference = "K5", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue144 = new CellValue();
cellValue144.Text = "24";
cell144.Append(cellValue144);
row31.Append(cell136);
row31.Append(cell137);
row31.Append(cell138);
row31.Append(cell139);
row31.Append(cell140);
row31.Append(cell141);
row31.Append(cell142);
row31.Append(cell143);
row31.Append(cell144);
Row row32 = new Row(){ RowIndex = (UInt32Value)6U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell145 = new Cell(){ CellReference = "H6", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue145 = new CellValue();
cellValue145.Text = "11";
cell145.Append(cellValue145);
Cell cell146 = new Cell(){ CellReference = "I6", StyleIndex = (UInt32Value)4U };
CellValue cellValue146 = new CellValue();
cellValue146.Text = "78";
cell146.Append(cellValue146);
Cell cell147 = new Cell(){ CellReference = "K6", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue147 = new CellValue();
cellValue147.Text = "12";
cell147.Append(cellValue147);
row32.Append(cell145);
row32.Append(cell146);
row32.Append(cell147);
Row row33 = new Row(){ RowIndex = (UInt32Value)7U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell148 = new Cell(){ CellReference = "H7", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue148 = new CellValue();
cellValue148.Text = "22";
cell148.Append(cellValue148);
Cell cell149 = new Cell(){ CellReference = "I7", StyleIndex = (UInt32Value)4U };
CellValue cellValue149 = new CellValue();
cellValue149.Text = "99";
cell149.Append(cellValue149);
Cell cell150 = new Cell(){ CellReference = "K7", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue150 = new CellValue();
cellValue150.Text = "21";
cell150.Append(cellValue150);
row33.Append(cell148);
row33.Append(cell149);
row33.Append(cell150);
Row row34 = new Row(){ RowIndex = (UInt32Value)8U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell151 = new Cell(){ CellReference = "H8", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue151 = new CellValue();
cellValue151.Text = "23";
cell151.Append(cellValue151);
Cell cell152 = new Cell(){ CellReference = "I8", StyleIndex = (UInt32Value)4U };
CellValue cellValue152 = new CellValue();
cellValue152.Text = "427";
cell152.Append(cellValue152);
Cell cell153 = new Cell(){ CellReference = "K8", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue153 = new CellValue();
cellValue153.Text = "7";
cell153.Append(cellValue153);
row34.Append(cell151);
row34.Append(cell152);
row34.Append(cell153);
Row row35 = new Row(){ RowIndex = (UInt32Value)9U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell154 = new Cell(){ CellReference = "H9", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue154 = new CellValue();
cellValue154.Text = "17";
cell154.Append(cellValue154);
Cell cell155 = new Cell(){ CellReference = "I9", StyleIndex = (UInt32Value)4U };
CellValue cellValue155 = new CellValue();
cellValue155.Text = "8127";
cell155.Append(cellValue155);
Cell cell156 = new Cell(){ CellReference = "K9", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue156 = new CellValue();
cellValue156.Text = "19";
cell156.Append(cellValue156);
row35.Append(cell154);
row35.Append(cell155);
row35.Append(cell156);
Row row36 = new Row(){ RowIndex = (UInt32Value)10U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell157 = new Cell(){ CellReference = "H10", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue157 = new CellValue();
cellValue157.Text = "18";
cell157.Append(cellValue157);
Cell cell158 = new Cell(){ CellReference = "I10", StyleIndex = (UInt32Value)4U };
CellValue cellValue158 = new CellValue();
cellValue158.Text = "777";
cell158.Append(cellValue158);
Cell cell159 = new Cell(){ CellReference = "K10", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue159 = new CellValue();
cellValue159.Text = "15";
cell159.Append(cellValue159);
row36.Append(cell157);
row36.Append(cell158);
row36.Append(cell159);
Row row37 = new Row(){ RowIndex = (UInt32Value)11U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell160 = new Cell(){ CellReference = "H11", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue160 = new CellValue();
cellValue160.Text = "25";
cell160.Append(cellValue160);
Cell cell161 = new Cell(){ CellReference = "I11", StyleIndex = (UInt32Value)4U };
CellValue cellValue161 = new CellValue();
cellValue161.Text = "262";
cell161.Append(cellValue161);
Cell cell162 = new Cell(){ CellReference = "K11", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue162 = new CellValue();
cellValue162.Text = "13";
cell162.Append(cellValue162);
row37.Append(cell160);
row37.Append(cell161);
row37.Append(cell162);
sheetData5.Append(row27);
sheetData5.Append(row28);
sheetData5.Append(row29);
sheetData5.Append(row30);
sheetData5.Append(row31);
sheetData5.Append(row32);
sheetData5.Append(row33);
sheetData5.Append(row34);
sheetData5.Append(row35);
sheetData5.Append(row36);
sheetData5.Append(row37);
PhoneticProperties phoneticProperties11 = new PhoneticProperties(){ FontId = (UInt32Value)1U };
PageMargins pageMargins11 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
TableParts tableParts2 = new TableParts(){ Count = (UInt32Value)3U };
TablePart tablePart4 = new TablePart(){ Id = "rId1" };
TablePart tablePart5 = new TablePart(){ Id = "rId2" };
TablePart tablePart6 = new TablePart(){ Id = "rId3" };
tableParts2.Append(tablePart4);
tableParts2.Append(tablePart5);
tableParts2.Append(tablePart6);
worksheet5.Append(sheetDimension5);
worksheet5.Append(sheetViews5);
worksheet5.Append(sheetFormatProperties5);
worksheet5.Append(columns5);
worksheet5.Append(sheetData5);
worksheet5.Append(phoneticProperties11);
worksheet5.Append(pageMargins11);
worksheet5.Append(tableParts2);
worksheetPart5.Worksheet = worksheet5;
}
// Generates content of worksheetPart4.
private void GenerateWorksheetPart4Content(WorksheetPart worksheetPart4)
{
Worksheet worksheet4 = new Worksheet(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x14ac" } };
worksheet4.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet4.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet4.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetDimension sheetDimension4 = new SheetDimension(){ Reference = "A1:K11" };
SheetViews sheetViews4 = new SheetViews();
SheetView sheetView4 = new SheetView(){ WorkbookViewId = (UInt32Value)0U };
sheetViews4.Append(sheetView4);
SheetFormatProperties sheetFormatProperties4 = new SheetFormatProperties(){ DefaultRowHeight = 15D };
Columns columns4 = new Columns();
Column column16 = new Column(){ Min = (UInt32Value)1U, Max = (UInt32Value)1U, Width = 10.7109375D, BestFit = true, CustomWidth = true };
Column column17 = new Column(){ Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 10.140625D, BestFit = true, CustomWidth = true };
Column column18 = new Column(){ Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 8.140625D, BestFit = true, CustomWidth = true };
Column column19 = new Column(){ Min = (UInt32Value)4U, Max = (UInt32Value)4U, Width = 10.42578125D, BestFit = true, CustomWidth = true };
Column column20 = new Column(){ Min = (UInt32Value)5U, Max = (UInt32Value)5U, Width = 12.85546875D, BestFit = true, CustomWidth = true };
Column column21 = new Column(){ Min = (UInt32Value)6U, Max = (UInt32Value)6U, Width = 11.5703125D, BestFit = true, CustomWidth = true };
Column column22 = new Column(){ Min = (UInt32Value)8U, Max = (UInt32Value)8U, Width = 12.85546875D, BestFit = true, CustomWidth = true };
Column column23 = new Column(){ Min = (UInt32Value)9U, Max = (UInt32Value)9U, Width = 10.42578125D, BestFit = true, CustomWidth = true };
Column column24 = new Column(){ Min = (UInt32Value)11U, Max = (UInt32Value)11U, Width = 12.85546875D, BestFit = true, CustomWidth = true };
columns4.Append(column16);
columns4.Append(column17);
columns4.Append(column18);
columns4.Append(column19);
columns4.Append(column20);
columns4.Append(column21);
columns4.Append(column22);
columns4.Append(column23);
columns4.Append(column24);
SheetData sheetData4 = new SheetData();
Row row16 = new Row(){ RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell31 = new Cell(){ CellReference = "A1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue31 = new CellValue();
cellValue31.Text = "0";
cell31.Append(cellValue31);
Cell cell32 = new Cell(){ CellReference = "B1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue32 = new CellValue();
cellValue32.Text = "1";
cell32.Append(cellValue32);
Cell cell33 = new Cell(){ CellReference = "C1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue33 = new CellValue();
cellValue33.Text = "2";
cell33.Append(cellValue33);
Cell cell34 = new Cell(){ CellReference = "D1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue34 = new CellValue();
cellValue34.Text = "3";
cell34.Append(cellValue34);
Cell cell35 = new Cell(){ CellReference = "E1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue35 = new CellValue();
cellValue35.Text = "4";
cell35.Append(cellValue35);
Cell cell36 = new Cell(){ CellReference = "F1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue36 = new CellValue();
cellValue36.Text = "5";
cell36.Append(cellValue36);
Cell cell37 = new Cell(){ CellReference = "H1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue37 = new CellValue();
cellValue37.Text = "26";
cell37.Append(cellValue37);
Cell cell38 = new Cell(){ CellReference = "I1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue38 = new CellValue();
cellValue38.Text = "3";
cell38.Append(cellValue38);
Cell cell39 = new Cell(){ CellReference = "K1", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue39 = new CellValue();
cellValue39.Text = "27";
cell39.Append(cellValue39);
row16.Append(cell31);
row16.Append(cell32);
row16.Append(cell33);
row16.Append(cell34);
row16.Append(cell35);
row16.Append(cell36);
row16.Append(cell37);
row16.Append(cell38);
row16.Append(cell39);
Row row17 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell40 = new Cell(){ CellReference = "A2", StyleIndex = (UInt32Value)3U };
CellValue cellValue40 = new CellValue();
cellValue40.Text = "36526.579536951103";
cell40.Append(cellValue40);
Cell cell41 = new Cell(){ CellReference = "B2", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue41 = new CellValue();
cellValue41.Text = "22";
cell41.Append(cellValue41);
Cell cell42 = new Cell(){ CellReference = "C2", StyleIndex = (UInt32Value)1U };
CellValue cellValue42 = new CellValue();
cellValue42.Text = "13";
cell42.Append(cellValue42);
Cell cell43 = new Cell(){ CellReference = "D2", StyleIndex = (UInt32Value)1U };
CellValue cellValue43 = new CellValue();
cellValue43.Text = "1287";
cell43.Append(cellValue43);
Cell cell44 = new Cell(){ CellReference = "E2", StyleIndex = (UInt32Value)3U };
CellValue cellValue44 = new CellValue();
cellValue44.Text = "36527.188771834619";
cell44.Append(cellValue44);
Cell cell45 = new Cell(){ CellReference = "F2", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue45 = new CellValue();
cellValue45.Text = "19";
cell45.Append(cellValue45);
Cell cell46 = new Cell(){ CellReference = "H2", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue46 = new CellValue();
cellValue46.Text = "6";
cell46.Append(cellValue46);
Cell cell47 = new Cell(){ CellReference = "I2", StyleIndex = (UInt32Value)4U };
CellValue cellValue47 = new CellValue();
cellValue47.Text = "125";
cell47.Append(cellValue47);
Cell cell48 = new Cell(){ CellReference = "K2", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue48 = new CellValue();
cellValue48.Text = "20";
cell48.Append(cellValue48);
row17.Append(cell40);
row17.Append(cell41);
row17.Append(cell42);
row17.Append(cell43);
row17.Append(cell44);
row17.Append(cell45);
row17.Append(cell46);
row17.Append(cell47);
row17.Append(cell48);
Row row18 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell49 = new Cell(){ CellReference = "A3", StyleIndex = (UInt32Value)3U };
CellValue cellValue49 = new CellValue();
cellValue49.Text = "36526.64725983989";
cell49.Append(cellValue49);
Cell cell50 = new Cell(){ CellReference = "B3", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue50 = new CellValue();
cellValue50.Text = "23";
cell50.Append(cellValue50);
Cell cell51 = new Cell(){ CellReference = "C3", StyleIndex = (UInt32Value)1U };
CellValue cellValue51 = new CellValue();
cellValue51.Text = "27";
cell51.Append(cellValue51);
Cell cell52 = new Cell(){ CellReference = "D3", StyleIndex = (UInt32Value)1U };
CellValue cellValue52 = new CellValue();
cellValue52.Text = "11529";
cell52.Append(cellValue52);
Cell cell53 = new Cell(){ CellReference = "E3", StyleIndex = (UInt32Value)3U };
CellValue cellValue53 = new CellValue();
cellValue53.Text = "36529.135002922048";
cell53.Append(cellValue53);
Cell cell54 = new Cell(){ CellReference = "F3", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue54 = new CellValue();
cellValue54.Text = "15";
cell54.Append(cellValue54);
Cell cell55 = new Cell(){ CellReference = "H3", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue55 = new CellValue();
cellValue55.Text = "14";
cell55.Append(cellValue55);
Cell cell56 = new Cell(){ CellReference = "I3", StyleIndex = (UInt32Value)4U };
CellValue cellValue56 = new CellValue();
cellValue56.Text = "250";
cell56.Append(cellValue56);
Cell cell57 = new Cell(){ CellReference = "K3", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue57 = new CellValue();
cellValue57.Text = "9";
cell57.Append(cellValue57);
row18.Append(cell49);
row18.Append(cell50);
row18.Append(cell51);
row18.Append(cell52);
row18.Append(cell53);
row18.Append(cell54);
row18.Append(cell55);
row18.Append(cell56);
row18.Append(cell57);
Row row19 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell58 = new Cell(){ CellReference = "A4", StyleIndex = (UInt32Value)3U };
CellValue cellValue58 = new CellValue();
cellValue58.Text = "36526.705772237176";
cell58.Append(cellValue58);
Cell cell59 = new Cell(){ CellReference = "B4", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue59 = new CellValue();
cellValue59.Text = "22";
cell59.Append(cellValue59);
Cell cell60 = new Cell(){ CellReference = "C4", StyleIndex = (UInt32Value)1U };
CellValue cellValue60 = new CellValue();
cellValue60.Text = "19";
cell60.Append(cellValue60);
Cell cell61 = new Cell(){ CellReference = "D4", StyleIndex = (UInt32Value)1U };
CellValue cellValue61 = new CellValue();
cellValue61.Text = "1881";
cell61.Append(cellValue61);
Cell cell62 = new Cell(){ CellReference = "E4", StyleIndex = (UInt32Value)3U };
CellValue cellValue62 = new CellValue();
cellValue62.Text = "36528.465495973578";
cell62.Append(cellValue62);
Cell cell63 = new Cell(){ CellReference = "F4", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue63 = new CellValue();
cellValue63.Text = "13";
cell63.Append(cellValue63);
Cell cell64 = new Cell(){ CellReference = "H4", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue64 = new CellValue();
cellValue64.Text = "16";
cell64.Append(cellValue64);
Cell cell65 = new Cell(){ CellReference = "I4", StyleIndex = (UInt32Value)4U };
CellValue cellValue65 = new CellValue();
cellValue65.Text = "50";
cell65.Append(cellValue65);
Cell cell66 = new Cell(){ CellReference = "K4", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue66 = new CellValue();
cellValue66.Text = "10";
cell66.Append(cellValue66);
row19.Append(cell58);
row19.Append(cell59);
row19.Append(cell60);
row19.Append(cell61);
row19.Append(cell62);
row19.Append(cell63);
row19.Append(cell64);
row19.Append(cell65);
row19.Append(cell66);
Row row20 = new Row(){ RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell67 = new Cell(){ CellReference = "A5", StyleIndex = (UInt32Value)3U };
CellValue cellValue67 = new CellValue();
cellValue67.Text = "36526.753330643623";
cell67.Append(cellValue67);
Cell cell68 = new Cell(){ CellReference = "B5", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue68 = new CellValue();
cellValue68.Text = "16";
cell68.Append(cellValue68);
Cell cell69 = new Cell(){ CellReference = "C5", StyleIndex = (UInt32Value)1U };
CellValue cellValue69 = new CellValue();
cellValue69.Text = "25";
cell69.Append(cellValue69);
Cell cell70 = new Cell(){ CellReference = "D5", StyleIndex = (UInt32Value)1U };
CellValue cellValue70 = new CellValue();
cellValue70.Text = "1250";
cell70.Append(cellValue70);
Cell cell71 = new Cell(){ CellReference = "E5", StyleIndex = (UInt32Value)3U };
CellValue cellValue71 = new CellValue();
cellValue71.Text = "36527.287605233112";
cell71.Append(cellValue71);
Cell cell72 = new Cell(){ CellReference = "F5", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue72 = new CellValue();
cellValue72.Text = "9";
cell72.Append(cellValue72);
Cell cell73 = new Cell(){ CellReference = "H5", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue73 = new CellValue();
cellValue73.Text = "8";
cell73.Append(cellValue73);
Cell cell74 = new Cell(){ CellReference = "I5", StyleIndex = (UInt32Value)4U };
CellValue cellValue74 = new CellValue();
cellValue74.Text = "1230";
cell74.Append(cellValue74);
Cell cell75 = new Cell(){ CellReference = "K5", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue75 = new CellValue();
cellValue75.Text = "24";
cell75.Append(cellValue75);
row20.Append(cell67);
row20.Append(cell68);
row20.Append(cell69);
row20.Append(cell70);
row20.Append(cell71);
row20.Append(cell72);
row20.Append(cell73);
row20.Append(cell74);
row20.Append(cell75);
Row row21 = new Row(){ RowIndex = (UInt32Value)6U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell76 = new Cell(){ CellReference = "A6", StyleIndex = (UInt32Value)3U };
CellValue cellValue76 = new CellValue();
cellValue76.Text = "37620.934998271434";
cell76.Append(cellValue76);
Cell cell77 = new Cell(){ CellReference = "B6", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue77 = new CellValue();
cellValue77.Text = "16";
cell77.Append(cellValue77);
Cell cell78 = new Cell(){ CellReference = "C6", StyleIndex = (UInt32Value)1U };
CellValue cellValue78 = new CellValue();
cellValue78.Text = "16";
cell78.Append(cellValue78);
Cell cell79 = new Cell(){ CellReference = "D6", StyleIndex = (UInt32Value)1U };
CellValue cellValue79 = new CellValue();
cellValue79.Text = "800";
cell79.Append(cellValue79);
Cell cell80 = new Cell(){ CellReference = "E6", StyleIndex = (UInt32Value)3U };
CellValue cellValue80 = new CellValue();
cellValue80.Text = "37622.167683550928";
cell80.Append(cellValue80);
Cell cell81 = new Cell(){ CellReference = "F6", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue81 = new CellValue();
cellValue81.Text = "21";
cell81.Append(cellValue81);
Cell cell82 = new Cell(){ CellReference = "H6", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue82 = new CellValue();
cellValue82.Text = "11";
cell82.Append(cellValue82);
Cell cell83 = new Cell(){ CellReference = "I6", StyleIndex = (UInt32Value)4U };
CellValue cellValue83 = new CellValue();
cellValue83.Text = "78";
cell83.Append(cellValue83);
Cell cell84 = new Cell(){ CellReference = "K6", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue84 = new CellValue();
cellValue84.Text = "12";
cell84.Append(cellValue84);
row21.Append(cell76);
row21.Append(cell77);
row21.Append(cell78);
row21.Append(cell79);
row21.Append(cell80);
row21.Append(cell81);
row21.Append(cell82);
row21.Append(cell83);
row21.Append(cell84);
Row row22 = new Row(){ RowIndex = (UInt32Value)7U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell85 = new Cell(){ CellReference = "H7", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue85 = new CellValue();
cellValue85.Text = "22";
cell85.Append(cellValue85);
Cell cell86 = new Cell(){ CellReference = "I7", StyleIndex = (UInt32Value)4U };
CellValue cellValue86 = new CellValue();
cellValue86.Text = "99";
cell86.Append(cellValue86);
Cell cell87 = new Cell(){ CellReference = "K7", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue87 = new CellValue();
cellValue87.Text = "21";
cell87.Append(cellValue87);
row22.Append(cell85);
row22.Append(cell86);
row22.Append(cell87);
Row row23 = new Row(){ RowIndex = (UInt32Value)8U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell88 = new Cell(){ CellReference = "H8", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue88 = new CellValue();
cellValue88.Text = "23";
cell88.Append(cellValue88);
Cell cell89 = new Cell(){ CellReference = "I8", StyleIndex = (UInt32Value)4U };
CellValue cellValue89 = new CellValue();
cellValue89.Text = "427";
cell89.Append(cellValue89);
Cell cell90 = new Cell(){ CellReference = "K8", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue90 = new CellValue();
cellValue90.Text = "7";
cell90.Append(cellValue90);
row23.Append(cell88);
row23.Append(cell89);
row23.Append(cell90);
Row row24 = new Row(){ RowIndex = (UInt32Value)9U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell91 = new Cell(){ CellReference = "H9", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue91 = new CellValue();
cellValue91.Text = "17";
cell91.Append(cellValue91);
Cell cell92 = new Cell(){ CellReference = "I9", StyleIndex = (UInt32Value)4U };
CellValue cellValue92 = new CellValue();
cellValue92.Text = "8127";
cell92.Append(cellValue92);
Cell cell93 = new Cell(){ CellReference = "K9", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue93 = new CellValue();
cellValue93.Text = "19";
cell93.Append(cellValue93);
row24.Append(cell91);
row24.Append(cell92);
row24.Append(cell93);
Row row25 = new Row(){ RowIndex = (UInt32Value)10U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell94 = new Cell(){ CellReference = "H10", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue94 = new CellValue();
cellValue94.Text = "18";
cell94.Append(cellValue94);
Cell cell95 = new Cell(){ CellReference = "I10", StyleIndex = (UInt32Value)4U };
CellValue cellValue95 = new CellValue();
cellValue95.Text = "777";
cell95.Append(cellValue95);
Cell cell96 = new Cell(){ CellReference = "K10", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue96 = new CellValue();
cellValue96.Text = "15";
cell96.Append(cellValue96);
row25.Append(cell94);
row25.Append(cell95);
row25.Append(cell96);
Row row26 = new Row(){ RowIndex = (UInt32Value)11U, Spans = new ListValue<StringValue>() { InnerText = "1:11" } };
Cell cell97 = new Cell(){ CellReference = "H11", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue97 = new CellValue();
cellValue97.Text = "25";
cell97.Append(cellValue97);
Cell cell98 = new Cell(){ CellReference = "I11", StyleIndex = (UInt32Value)4U };
CellValue cellValue98 = new CellValue();
cellValue98.Text = "262";
cell98.Append(cellValue98);
Cell cell99 = new Cell(){ CellReference = "K11", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue99 = new CellValue();
cellValue99.Text = "13";
cell99.Append(cellValue99);
row26.Append(cell97);
row26.Append(cell98);
row26.Append(cell99);
sheetData4.Append(row16);
sheetData4.Append(row17);
sheetData4.Append(row18);
sheetData4.Append(row19);
sheetData4.Append(row20);
sheetData4.Append(row21);
sheetData4.Append(row22);
sheetData4.Append(row23);
sheetData4.Append(row24);
sheetData4.Append(row25);
sheetData4.Append(row26);
PhoneticProperties phoneticProperties10 = new PhoneticProperties(){ FontId = (UInt32Value)1U };
PageMargins pageMargins10 = new PageMargins(){ Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
TableParts tableParts1 = new TableParts(){ Count = (UInt32Value)3U };
TablePart tablePart1 = new TablePart(){ Id = "rId1" };
TablePart tablePart2 = new TablePart(){ Id = "rId2" };
TablePart tablePart3 = new TablePart(){ Id = "rId3" };
tableParts1.Append(tablePart1);
tableParts1.Append(tablePart2);
tableParts1.Append(tablePart3);
worksheet4.Append(sheetDimension4);
worksheet4.Append(sheetViews4);
worksheet4.Append(sheetFormatProperties4);
worksheet4.Append(columns4);
worksheet4.Append(sheetData4);
worksheet4.Append(phoneticProperties10);
worksheet4.Append(pageMargins10);
worksheet4.Append(tableParts1);
worksheetPart4.Worksheet = worksheet4;
}
// 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");
SheetProperties sheetProperties1 = new SheetProperties(){ CodeName = "Sheet1" };
SheetDimension sheetDimension1 = new SheetDimension(){ Reference = "B2:I41" };
SheetViews sheetViews1 = new SheetViews();
SheetView sheetView1 = new SheetView(){ TabSelected = true, WorkbookViewId = (UInt32Value)0U };
Selection selection1 = new Selection(){ ActiveCell = "G18", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "G18" } };
sheetView1.Append(selection1);
sheetViews1.Append(sheetView1);
SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties(){ DefaultRowHeight = 15D };
Columns columns1 = new Columns();
Column column1 = new Column(){ Min = (UInt32Value)2U, Max = (UInt32Value)2U, Width = 12.85546875D, BestFit = true, CustomWidth = true };
Column column2 = new Column(){ Min = (UInt32Value)3U, Max = (UInt32Value)3U, Width = 17.140625D, BestFit = true, CustomWidth = true };
Column column3 = new Column(){ Min = (UInt32Value)4U, Max = (UInt32Value)4U, Width = 24D, BestFit = true, CustomWidth = true };
Column column4 = new Column(){ Min = (UInt32Value)5U, Max = (UInt32Value)5U, Width = 15.140625D, BestFit = true, CustomWidth = true };
Column column5 = new Column(){ Min = (UInt32Value)6U, Max = (UInt32Value)6U, Width = 16.5703125D, BestFit = true, CustomWidth = true };
Column column6 = new Column(){ Min = (UInt32Value)7U, Max = (UInt32Value)7U, Width = 81.140625D, BestFit = true, CustomWidth = true };
Column column7 = new Column(){ Min = (UInt32Value)8U, Max = (UInt32Value)8U, Width = 18.7109375D, BestFit = true, CustomWidth = true };
Column column8 = new Column(){ Min = (UInt32Value)9U, Max = (UInt32Value)9U, Width = 14.5703125D, 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);
columns1.Append(column8);
SheetData sheetData1 = new SheetData();
Row row1 = new Row(){ RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell1 = new Cell(){ CellReference = "B2", DataType = CellValues.SharedString };
CellValue cellValue1 = new CellValue();
cellValue1.Text = "0";
cell1.Append(cellValue1);
Cell cell2 = new Cell(){ CellReference = "C2", DataType = CellValues.SharedString };
CellValue cellValue2 = new CellValue();
cellValue2.Text = "1";
cell2.Append(cellValue2);
Cell cell3 = new Cell(){ CellReference = "D2", DataType = CellValues.SharedString };
CellValue cellValue3 = new CellValue();
cellValue3.Text = "2";
cell3.Append(cellValue3);
Cell cell4 = new Cell(){ CellReference = "E2", DataType = CellValues.SharedString };
CellValue cellValue4 = new CellValue();
cellValue4.Text = "3";
cell4.Append(cellValue4);
Cell cell5 = new Cell(){ CellReference = "F2", DataType = CellValues.SharedString };
CellValue cellValue5 = new CellValue();
cellValue5.Text = "4";
cell5.Append(cellValue5);
Cell cell6 = new Cell(){ CellReference = "G2", DataType = CellValues.SharedString };
CellValue cellValue6 = new CellValue();
cellValue6.Text = "5";
cell6.Append(cellValue6);
Cell cell7 = new Cell(){ CellReference = "H2", DataType = CellValues.SharedString };
CellValue cellValue7 = new CellValue();
cellValue7.Text = "6";
cell7.Append(cellValue7);
Cell cell8 = new Cell(){ CellReference = "I2", DataType = CellValues.SharedString };
CellValue cellValue8 = new CellValue();
cellValue8.Text = "7";
cell8.Append(cellValue8);
row1.Append(cell1);
row1.Append(cell2);
row1.Append(cell3);
row1.Append(cell4);
row1.Append(cell5);
row1.Append(cell6);
row1.Append(cell7);
row1.Append(cell8);
Row row2 = new Row(){ RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell9 = new Cell(){ CellReference = "B3" };
CellValue cellValue9 = new CellValue();
cellValue9.Text = "4000";
cell9.Append(cellValue9);
Cell cell10 = new Cell(){ CellReference = "C3" };
CellValue cellValue10 = new CellValue();
cellValue10.Text = "5000";
cell10.Append(cellValue10);
Cell cell11 = new Cell(){ CellReference = "D3", DataType = CellValues.SharedString };
CellValue cellValue11 = new CellValue();
cellValue11.Text = "8";
cell11.Append(cellValue11);
Cell cell12 = new Cell(){ CellReference = "E3", DataType = CellValues.SharedString };
CellValue cellValue12 = new CellValue();
cellValue12.Text = "9";
cell12.Append(cellValue12);
Cell cell13 = new Cell(){ CellReference = "F3", DataType = CellValues.SharedString };
CellValue cellValue13 = new CellValue();
cellValue13.Text = "10";
cell13.Append(cellValue13);
Cell cell14 = new Cell(){ CellReference = "H3", DataType = CellValues.SharedString };
CellValue cellValue14 = new CellValue();
cellValue14.Text = "11";
cell14.Append(cellValue14);
Cell cell15 = new Cell(){ CellReference = "I3", StyleIndex = (UInt32Value)1U };
CellValue cellValue15 = new CellValue();
cellValue15.Text = "40855.491680594139";
cell15.Append(cellValue15);
row2.Append(cell9);
row2.Append(cell10);
row2.Append(cell11);
row2.Append(cell12);
row2.Append(cell13);
row2.Append(cell14);
row2.Append(cell15);
Row row3 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell16 = new Cell(){ CellReference = "B4" };
CellValue cellValue16 = new CellValue();
cellValue16.Text = "2000";
cell16.Append(cellValue16);
Cell cell17 = new Cell(){ CellReference = "D4", DataType = CellValues.SharedString };
CellValue cellValue17 = new CellValue();
cellValue17.Text = "12";
cell17.Append(cellValue17);
Cell cell18 = new Cell(){ CellReference = "E4", DataType = CellValues.SharedString };
CellValue cellValue18 = new CellValue();
cellValue18.Text = "13";
cell18.Append(cellValue18);
Cell cell19 = new Cell(){ CellReference = "F4", DataType = CellValues.SharedString };
CellValue cellValue19 = new CellValue();
cellValue19.Text = "14";
cell19.Append(cellValue19);
Cell cell20 = new Cell(){ CellReference = "H4", DataType = CellValues.SharedString };
CellValue cellValue20 = new CellValue();
cellValue20.Text = "11";
cell20.Append(cellValue20);
Cell cell21 = new Cell(){ CellReference = "I4", StyleIndex = (UInt32Value)1U };
CellValue cellValue21 = new CellValue();
cellValue21.Text = "40855.491680594139";
cell21.Append(cellValue21);
row3.Append(cell16);
row3.Append(cell17);
row3.Append(cell18);
row3.Append(cell19);
row3.Append(cell20);
row3.Append(cell21);
Row row4 = new Row(){ RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell22 = new Cell(){ CellReference = "B5" };
CellValue cellValue22 = new CellValue();
cellValue22.Text = "4300";
cell22.Append(cellValue22);
Cell cell23 = new Cell(){ CellReference = "C5" };
CellValue cellValue23 = new CellValue();
cellValue23.Text = "4000";
cell23.Append(cellValue23);
Cell cell24 = new Cell(){ CellReference = "D5", DataType = CellValues.SharedString };
CellValue cellValue24 = new CellValue();
cellValue24.Text = "15";
cell24.Append(cellValue24);
Cell cell25 = new Cell(){ CellReference = "E5", DataType = CellValues.SharedString };
CellValue cellValue25 = new CellValue();
cellValue25.Text = "9";
cell25.Append(cellValue25);
Cell cell26 = new Cell(){ CellReference = "F5", DataType = CellValues.SharedString };
CellValue cellValue26 = new CellValue();
cellValue26.Text = "16";
cell26.Append(cellValue26);
Cell cell27 = new Cell(){ CellReference = "H5", DataType = CellValues.SharedString };
CellValue cellValue27 = new CellValue();
cellValue27.Text = "11";
cell27.Append(cellValue27);
Cell cell28 = new Cell(){ CellReference = "I5", StyleIndex = (UInt32Value)1U };
CellValue cellValue28 = new CellValue();
cellValue28.Text = "40855.491680594139";
cell28.Append(cellValue28);
row4.Append(cell22);
row4.Append(cell23);
row4.Append(cell24);
row4.Append(cell25);
row4.Append(cell26);
row4.Append(cell27);
row4.Append(cell28);
Row row5 = new Row(){ RowIndex = (UInt32Value)6U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell29 = new Cell(){ CellReference = "B6" };
CellValue cellValue29 = new CellValue();
cellValue29.Text = "3200";
cell29.Append(cellValue29);
Cell cell30 = new Cell(){ CellReference = "C6" };
CellValue cellValue30 = new CellValue();
cellValue30.Text = "3000";
cell30.Append(cellValue30);
Cell cell31 = new Cell(){ CellReference = "D6", DataType = CellValues.SharedString };
CellValue cellValue31 = new CellValue();
cellValue31.Text = "17";
cell31.Append(cellValue31);
Cell cell32 = new Cell(){ CellReference = "E6", DataType = CellValues.SharedString };
CellValue cellValue32 = new CellValue();
cellValue32.Text = "18";
cell32.Append(cellValue32);
Cell cell33 = new Cell(){ CellReference = "F6", DataType = CellValues.SharedString };
CellValue cellValue33 = new CellValue();
cellValue33.Text = "10";
cell33.Append(cellValue33);
Cell cell34 = new Cell(){ CellReference = "H6", DataType = CellValues.SharedString };
CellValue cellValue34 = new CellValue();
cellValue34.Text = "11";
cell34.Append(cellValue34);
Cell cell35 = new Cell(){ CellReference = "I6", StyleIndex = (UInt32Value)1U };
CellValue cellValue35 = new CellValue();
cellValue35.Text = "40855.491680594139";
cell35.Append(cellValue35);
row5.Append(cell29);
row5.Append(cell30);
row5.Append(cell31);
row5.Append(cell32);
row5.Append(cell33);
row5.Append(cell34);
row5.Append(cell35);
Row row6 = new Row(){ RowIndex = (UInt32Value)7U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell36 = new Cell(){ CellReference = "B7" };
CellValue cellValue36 = new CellValue();
cellValue36.Text = "4100";
cell36.Append(cellValue36);
Cell cell37 = new Cell(){ CellReference = "C7" };
CellValue cellValue37 = new CellValue();
cellValue37.Text = "4000";
cell37.Append(cellValue37);
Cell cell38 = new Cell(){ CellReference = "D7", DataType = CellValues.SharedString };
CellValue cellValue38 = new CellValue();
cellValue38.Text = "19";
cell38.Append(cellValue38);
Cell cell39 = new Cell(){ CellReference = "E7", DataType = CellValues.SharedString };
CellValue cellValue39 = new CellValue();
cellValue39.Text = "9";
cell39.Append(cellValue39);
Cell cell40 = new Cell(){ CellReference = "F7", DataType = CellValues.SharedString };
CellValue cellValue40 = new CellValue();
cellValue40.Text = "16";
cell40.Append(cellValue40);
Cell cell41 = new Cell(){ CellReference = "H7", DataType = CellValues.SharedString };
CellValue cellValue41 = new CellValue();
cellValue41.Text = "11";
cell41.Append(cellValue41);
Cell cell42 = new Cell(){ CellReference = "I7", StyleIndex = (UInt32Value)1U };
CellValue cellValue42 = new CellValue();
cellValue42.Text = "40855.491680594139";
cell42.Append(cellValue42);
row6.Append(cell36);
row6.Append(cell37);
row6.Append(cell38);
row6.Append(cell39);
row6.Append(cell40);
row6.Append(cell41);
row6.Append(cell42);
Row row7 = new Row(){ RowIndex = (UInt32Value)8U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell43 = new Cell(){ CellReference = "B8" };
CellValue cellValue43 = new CellValue();
cellValue43.Text = "4400";
cell43.Append(cellValue43);
Cell cell44 = new Cell(){ CellReference = "C8" };
CellValue cellValue44 = new CellValue();
cellValue44.Text = "4000";
cell44.Append(cellValue44);
Cell cell45 = new Cell(){ CellReference = "D8", DataType = CellValues.SharedString };
CellValue cellValue45 = new CellValue();
cellValue45.Text = "20";
cell45.Append(cellValue45);
Cell cell46 = new Cell(){ CellReference = "E8", DataType = CellValues.SharedString };
CellValue cellValue46 = new CellValue();
cellValue46.Text = "9";
cell46.Append(cellValue46);
Cell cell47 = new Cell(){ CellReference = "F8", DataType = CellValues.SharedString };
CellValue cellValue47 = new CellValue();
cellValue47.Text = "16";
cell47.Append(cellValue47);
Cell cell48 = new Cell(){ CellReference = "H8", DataType = CellValues.SharedString };
CellValue cellValue48 = new CellValue();
cellValue48.Text = "11";
cell48.Append(cellValue48);
Cell cell49 = new Cell(){ CellReference = "I8", StyleIndex = (UInt32Value)1U };
CellValue cellValue49 = new CellValue();
cellValue49.Text = "40855.491680594139";
cell49.Append(cellValue49);
row7.Append(cell43);
row7.Append(cell44);
row7.Append(cell45);
row7.Append(cell46);
row7.Append(cell47);
row7.Append(cell48);
row7.Append(cell49);
Row row8 = new Row(){ RowIndex = (UInt32Value)9U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell50 = new Cell(){ CellReference = "B9" };
CellValue cellValue50 = new CellValue();
cellValue50.Text = "3100";
cell50.Append(cellValue50);
Cell cell51 = new Cell(){ CellReference = "C9" };
CellValue cellValue51 = new CellValue();
cellValue51.Text = "3000";
cell51.Append(cellValue51);
Cell cell52 = new Cell(){ CellReference = "D9", DataType = CellValues.SharedString };
CellValue cellValue52 = new CellValue();
cellValue52.Text = "21";
cell52.Append(cellValue52);
Cell cell53 = new Cell(){ CellReference = "E9", DataType = CellValues.SharedString };
CellValue cellValue53 = new CellValue();
cellValue53.Text = "18";
cell53.Append(cellValue53);
Cell cell54 = new Cell(){ CellReference = "F9", DataType = CellValues.SharedString };
CellValue cellValue54 = new CellValue();
cellValue54.Text = "16";
cell54.Append(cellValue54);
Cell cell55 = new Cell(){ CellReference = "G9", DataType = CellValues.SharedString };
CellValue cellValue55 = new CellValue();
cellValue55.Text = "22";
cell55.Append(cellValue55);
Cell cell56 = new Cell(){ CellReference = "H9", DataType = CellValues.SharedString };
CellValue cellValue56 = new CellValue();
cellValue56.Text = "11";
cell56.Append(cellValue56);
Cell cell57 = new Cell(){ CellReference = "I9", StyleIndex = (UInt32Value)1U };
CellValue cellValue57 = new CellValue();
cellValue57.Text = "40855.491680594139";
cell57.Append(cellValue57);
row8.Append(cell50);
row8.Append(cell51);
row8.Append(cell52);
row8.Append(cell53);
row8.Append(cell54);
row8.Append(cell55);
row8.Append(cell56);
row8.Append(cell57);
Row row9 = new Row(){ RowIndex = (UInt32Value)10U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell58 = new Cell(){ CellReference = "B10" };
CellValue cellValue58 = new CellValue();
cellValue58.Text = "3000";
cell58.Append(cellValue58);
Cell cell59 = new Cell(){ CellReference = "C10" };
CellValue cellValue59 = new CellValue();
cellValue59.Text = "5000";
cell59.Append(cellValue59);
Cell cell60 = new Cell(){ CellReference = "D10", DataType = CellValues.SharedString };
CellValue cellValue60 = new CellValue();
cellValue60.Text = "23";
cell60.Append(cellValue60);
Cell cell61 = new Cell(){ CellReference = "E10", DataType = CellValues.SharedString };
CellValue cellValue61 = new CellValue();
cellValue61.Text = "18";
cell61.Append(cellValue61);
Cell cell62 = new Cell(){ CellReference = "F10", DataType = CellValues.SharedString };
CellValue cellValue62 = new CellValue();
cellValue62.Text = "16";
cell62.Append(cellValue62);
Cell cell63 = new Cell(){ CellReference = "H10", DataType = CellValues.SharedString };
CellValue cellValue63 = new CellValue();
cellValue63.Text = "11";
cell63.Append(cellValue63);
Cell cell64 = new Cell(){ CellReference = "I10", StyleIndex = (UInt32Value)1U };
CellValue cellValue64 = new CellValue();
cellValue64.Text = "40855.491680594139";
cell64.Append(cellValue64);
row9.Append(cell58);
row9.Append(cell59);
row9.Append(cell60);
row9.Append(cell61);
row9.Append(cell62);
row9.Append(cell63);
row9.Append(cell64);
Row row10 = new Row(){ RowIndex = (UInt32Value)11U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell65 = new Cell(){ CellReference = "B11" };
CellValue cellValue65 = new CellValue();
cellValue65.Text = "5000";
cell65.Append(cellValue65);
Cell cell66 = new Cell(){ CellReference = "D11", DataType = CellValues.SharedString };
CellValue cellValue66 = new CellValue();
cellValue66.Text = "24";
cell66.Append(cellValue66);
Cell cell67 = new Cell(){ CellReference = "E11", DataType = CellValues.SharedString };
CellValue cellValue67 = new CellValue();
cellValue67.Text = "18";
cell67.Append(cellValue67);
Cell cell68 = new Cell(){ CellReference = "F11", DataType = CellValues.SharedString };
CellValue cellValue68 = new CellValue();
cellValue68.Text = "16";
cell68.Append(cellValue68);
Cell cell69 = new Cell(){ CellReference = "H11", DataType = CellValues.SharedString };
CellValue cellValue69 = new CellValue();
cellValue69.Text = "11";
cell69.Append(cellValue69);
Cell cell70 = new Cell(){ CellReference = "I11", StyleIndex = (UInt32Value)1U };
CellValue cellValue70 = new CellValue();
cellValue70.Text = "40855.491680594139";
cell70.Append(cellValue70);
row10.Append(cell65);
row10.Append(cell66);
row10.Append(cell67);
row10.Append(cell68);
row10.Append(cell69);
row10.Append(cell70);
Row row11 = new Row(){ RowIndex = (UInt32Value)12U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell71 = new Cell(){ CellReference = "B12" };
CellValue cellValue71 = new CellValue();
cellValue71.Text = "4200";
cell71.Append(cellValue71);
Cell cell72 = new Cell(){ CellReference = "C12" };
CellValue cellValue72 = new CellValue();
cellValue72.Text = "4000";
cell72.Append(cellValue72);
Cell cell73 = new Cell(){ CellReference = "D12", DataType = CellValues.SharedString };
CellValue cellValue73 = new CellValue();
cellValue73.Text = "25";
cell73.Append(cellValue73);
Cell cell74 = new Cell(){ CellReference = "E12", DataType = CellValues.SharedString };
CellValue cellValue74 = new CellValue();
cellValue74.Text = "9";
cell74.Append(cellValue74);
Cell cell75 = new Cell(){ CellReference = "F12", DataType = CellValues.SharedString };
CellValue cellValue75 = new CellValue();
cellValue75.Text = "16";
cell75.Append(cellValue75);
Cell cell76 = new Cell(){ CellReference = "H12", DataType = CellValues.SharedString };
CellValue cellValue76 = new CellValue();
cellValue76.Text = "11";
cell76.Append(cellValue76);
Cell cell77 = new Cell(){ CellReference = "I12", StyleIndex = (UInt32Value)1U };
CellValue cellValue77 = new CellValue();
cellValue77.Text = "40855.491680594139";
cell77.Append(cellValue77);
row11.Append(cell71);
row11.Append(cell72);
row11.Append(cell73);
row11.Append(cell74);
row11.Append(cell75);
row11.Append(cell76);
row11.Append(cell77);
Row row12 = new Row(){ RowIndex = (UInt32Value)13U, Spans = new ListValue<StringValue>() { InnerText = "2:9" } };
Cell cell78 = new Cell(){ CellReference = "B13" };
CellValue cellValue78 = new CellValue();
cellValue78.Text = "1000";
cell78.Append(cellValue78);
Cell cell79 = new Cell(){ CellReference = "D13", DataType = CellValues.SharedString };
CellValue cellValue79 = new CellValue();
cellValue79.Text = "26";
cell79.Append(cellValue79);
Cell cell80 = new Cell(){ CellReference = "E13", DataType = CellValues.SharedString };
CellValue cellValue80 = new CellValue();
cellValue80.Text = "27";
cell80.Append(cellValue80);
Cell cell81 = new Cell(){ CellReference = "F13", DataType = CellValues.SharedString };
CellValue cellValue81 = new CellValue();
cellValue81.Text = "14";
cell81.Append(cellValue81);
Cell cell82 = new Cell(){ CellReference = "H13", DataType = CellValues.SharedString };
CellValue cellValue82 = new CellValue();
cellValue82.Text = "11";
cell82.Append(cellValue82);
Cell cell83 = new Cell(){ CellReference = "I13", StyleIndex = (UInt32Value)1U };
CellValue cellValue83 = new CellValue();
cellValue83.Text = "40855.491680594139";
cell83.Append(cellValue83);
row12.Append(cell78);
row12.Append(cell79);
row12.Append(cell80);
row12.Append(cell81);
row12.Append(cell82);
row12.Append(cell83);
Row row13 = new Row(){ RowIndex = (UInt32Value)24U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell84 = new Cell(){ CellReference = "C24", StyleIndex = (UInt32Value)2U };
Cell cell85 = new Cell(){ CellReference = "D24", StyleIndex = (UInt32Value)3U };
Cell cell86 = new Cell(){ CellReference = "E24", StyleIndex = (UInt32Value)4U };
row13.Append(cell84);
row13.Append(cell85);
row13.Append(cell86);
Row row14 = new Row(){ RowIndex = (UInt32Value)25U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell87 = new Cell(){ CellReference = "C25", StyleIndex = (UInt32Value)5U };
Cell cell88 = new Cell(){ CellReference = "D25", StyleIndex = (UInt32Value)6U };
Cell cell89 = new Cell(){ CellReference = "E25", StyleIndex = (UInt32Value)7U };
row14.Append(cell87);
row14.Append(cell88);
row14.Append(cell89);
Row row15 = new Row(){ RowIndex = (UInt32Value)26U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell90 = new Cell(){ CellReference = "C26", StyleIndex = (UInt32Value)5U };
Cell cell91 = new Cell(){ CellReference = "D26", StyleIndex = (UInt32Value)6U };
Cell cell92 = new Cell(){ CellReference = "E26", StyleIndex = (UInt32Value)7U };
row15.Append(cell90);
row15.Append(cell91);
row15.Append(cell92);
Row row16 = new Row(){ RowIndex = (UInt32Value)27U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell93 = new Cell(){ CellReference = "C27", StyleIndex = (UInt32Value)5U };
Cell cell94 = new Cell(){ CellReference = "D27", StyleIndex = (UInt32Value)6U };
Cell cell95 = new Cell(){ CellReference = "E27", StyleIndex = (UInt32Value)7U };
row16.Append(cell93);
row16.Append(cell94);
row16.Append(cell95);
Row row17 = new Row(){ RowIndex = (UInt32Value)28U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell96 = new Cell(){ CellReference = "C28", StyleIndex = (UInt32Value)5U };
Cell cell97 = new Cell(){ CellReference = "D28", StyleIndex = (UInt32Value)6U };
Cell cell98 = new Cell(){ CellReference = "E28", StyleIndex = (UInt32Value)7U };
row17.Append(cell96);
row17.Append(cell97);
row17.Append(cell98);
Row row18 = new Row(){ RowIndex = (UInt32Value)29U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell99 = new Cell(){ CellReference = "C29", StyleIndex = (UInt32Value)5U };
Cell cell100 = new Cell(){ CellReference = "D29", StyleIndex = (UInt32Value)6U };
Cell cell101 = new Cell(){ CellReference = "E29", StyleIndex = (UInt32Value)7U };
row18.Append(cell99);
row18.Append(cell100);
row18.Append(cell101);
Row row19 = new Row(){ RowIndex = (UInt32Value)30U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell102 = new Cell(){ CellReference = "C30", StyleIndex = (UInt32Value)5U };
Cell cell103 = new Cell(){ CellReference = "D30", StyleIndex = (UInt32Value)6U };
Cell cell104 = new Cell(){ CellReference = "E30", StyleIndex = (UInt32Value)7U };
row19.Append(cell102);
row19.Append(cell103);
row19.Append(cell104);
Row row20 = new Row(){ RowIndex = (UInt32Value)31U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell105 = new Cell(){ CellReference = "C31", StyleIndex = (UInt32Value)5U };
Cell cell106 = new Cell(){ CellReference = "D31", StyleIndex = (UInt32Value)6U };
Cell cell107 = new Cell(){ CellReference = "E31", StyleIndex = (UInt32Value)7U };
row20.Append(cell105);
row20.Append(cell106);
row20.Append(cell107);
Row row21 = new Row(){ RowIndex = (UInt32Value)32U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell108 = new Cell(){ CellReference = "C32", StyleIndex = (UInt32Value)5U };
Cell cell109 = new Cell(){ CellReference = "D32", StyleIndex = (UInt32Value)6U };
Cell cell110 = new Cell(){ CellReference = "E32", StyleIndex = (UInt32Value)7U };
row21.Append(cell108);
row21.Append(cell109);
row21.Append(cell110);
Row row22 = new Row(){ RowIndex = (UInt32Value)33U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell111 = new Cell(){ CellReference = "C33", StyleIndex = (UInt32Value)5U };
Cell cell112 = new Cell(){ CellReference = "D33", StyleIndex = (UInt32Value)6U };
Cell cell113 = new Cell(){ CellReference = "E33", StyleIndex = (UInt32Value)7U };
row22.Append(cell111);
row22.Append(cell112);
row22.Append(cell113);
Row row23 = new Row(){ RowIndex = (UInt32Value)34U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell114 = new Cell(){ CellReference = "C34", StyleIndex = (UInt32Value)5U };
Cell cell115 = new Cell(){ CellReference = "D34", StyleIndex = (UInt32Value)6U };
Cell cell116 = new Cell(){ CellReference = "E34", StyleIndex = (UInt32Value)7U };
row23.Append(cell114);
row23.Append(cell115);
row23.Append(cell116);
Row row24 = new Row(){ RowIndex = (UInt32Value)35U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell117 = new Cell(){ CellReference = "C35", StyleIndex = (UInt32Value)5U };
Cell cell118 = new Cell(){ CellReference = "D35", StyleIndex = (UInt32Value)6U };
Cell cell119 = new Cell(){ CellReference = "E35", StyleIndex = (UInt32Value)7U };
row24.Append(cell117);
row24.Append(cell118);
row24.Append(cell119);
Row row25 = new Row(){ RowIndex = (UInt32Value)36U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell120 = new Cell(){ CellReference = "C36", StyleIndex = (UInt32Value)5U };
Cell cell121 = new Cell(){ CellReference = "D36", StyleIndex = (UInt32Value)6U };
Cell cell122 = new Cell(){ CellReference = "E36", StyleIndex = (UInt32Value)7U };
row25.Append(cell120);
row25.Append(cell121);
row25.Append(cell122);
Row row26 = new Row(){ RowIndex = (UInt32Value)37U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell123 = new Cell(){ CellReference = "C37", StyleIndex = (UInt32Value)5U };
Cell cell124 = new Cell(){ CellReference = "D37", StyleIndex = (UInt32Value)6U };
Cell cell125 = new Cell(){ CellReference = "E37", StyleIndex = (UInt32Value)7U };
row26.Append(cell123);
row26.Append(cell124);
row26.Append(cell125);
Row row27 = new Row(){ RowIndex = (UInt32Value)38U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell126 = new Cell(){ CellReference = "C38", StyleIndex = (UInt32Value)5U };
Cell cell127 = new Cell(){ CellReference = "D38", StyleIndex = (UInt32Value)6U };
Cell cell128 = new Cell(){ CellReference = "E38", StyleIndex = (UInt32Value)7U };
row27.Append(cell126);
row27.Append(cell127);
row27.Append(cell128);
Row row28 = new Row(){ RowIndex = (UInt32Value)39U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell129 = new Cell(){ CellReference = "C39", StyleIndex = (UInt32Value)5U };
Cell cell130 = new Cell(){ CellReference = "D39", StyleIndex = (UInt32Value)6U };
Cell cell131 = new Cell(){ CellReference = "E39", StyleIndex = (UInt32Value)7U };
row28.Append(cell129);
row28.Append(cell130);
row28.Append(cell131);
Row row29 = new Row(){ RowIndex = (UInt32Value)40U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell132 = new Cell(){ CellReference = "C40", StyleIndex = (UInt32Value)5U };
Cell cell133 = new Cell(){ CellReference = "D40", StyleIndex = (UInt32Value)6U };
Cell cell134 = new Cell(){ CellReference = "E40", StyleIndex = (UInt32Value)7U };
row29.Append(cell132);
row29.Append(cell133);
row29.Append(cell134);
Row row30 = new Row(){ RowIndex = (UInt32Value)41U, Spans = new ListValue<StringValue>() { InnerText = "3:5" } };
Cell cell135 = new Cell(){ CellReference = "C41", StyleIndex = (UInt32Value)8U };
Cell cell136 = new Cell(){ CellReference = "D41", StyleIndex = (UInt32Value)9U };
Cell cell137 = new Cell(){ CellReference = "E41", StyleIndex = (UInt32Value)10U };
row30.Append(cell135);
row30.Append(cell136);
row30.Append(cell137);
sheetData1.Append(row1);
sheetData1.Append(row2);
sheetData1.Append(row3);
sheetData1.Append(row4);
sheetData1.Append(row5);
sheetData1.Append(row6);
sheetData1.Append(row7);
sheetData1.Append(row8);
sheetData1.Append(row9);
sheetData1.Append(row10);
sheetData1.Append(row11);
sheetData1.Append(row12);
sheetData1.Append(row13);
sheetData1.Append(row14);
sheetData1.Append(row15);
sheetData1.Append(row16);
sheetData1.Append(row17);
sheetData1.Append(row18);
sheetData1.Append(row19);
sheetData1.Append(row20);
sheetData1.Append(row21);
sheetData1.Append(row22);
sheetData1.Append(row23);
sheetData1.Append(row24);
sheetData1.Append(row25);
sheetData1.Append(row26);
sheetData1.Append(row27);
sheetData1.Append(row28);
sheetData1.Append(row29);
sheetData1.Append(row30);
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 };
PageSetup pageSetup1 = new PageSetup(){ PaperSize = (UInt32Value)9U, Orientation = OrientationValues.Portrait, Id = "rId2" };
TableParts tableParts1 = new TableParts(){ Count = (UInt32Value)1U };
TablePart tablePart1 = new TablePart(){ Id = "rId3" };
tableParts1.Append(tablePart1);
worksheet1.Append(sheetProperties1);
worksheet1.Append(sheetDimension1);
worksheet1.Append(sheetViews1);
worksheet1.Append(sheetFormatProperties1);
worksheet1.Append(columns1);
worksheet1.Append(sheetData1);
worksheet1.Append(phoneticProperties1);
worksheet1.Append(pageMargins1);
worksheet1.Append(pageSetup1);
worksheet1.Append(tableParts1);
worksheetPart1.Worksheet = worksheet1;
}
/// <summary>
/// Generates the content of the WorkSheet part.
/// </summary>
/// <param name="part">The part.</param>
private static void GeneratePartContent(WorksheetPart part, string[] partIds)
{
char sheetDimensionColumn = 'A';
if (!string.IsNullOrEmpty(partIds[0]))
{
// If file level metadata is present, sheet dimension will be from column A to column B.
sheetDimensionColumn = (char)(sheetDimensionColumn + 1);
}
if (!string.IsNullOrEmpty(partIds[1]))
{
// If column level metadata is present, sheet dimension will be from column D to column I if there is file level metadata. Otherwise from column A to column F.
sheetDimensionColumn = !string.IsNullOrEmpty(partIds[0]) ? (char)(sheetDimensionColumn + 7) : (char)(sheetDimensionColumn + 5);
}
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:" + sheetDimensionColumn + "30" };
SheetViews sheetViews1 = new SheetViews();
SheetView sheetView1 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
// Add 2 extra columns for selection.
char activeColumn = (char)(sheetDimensionColumn + 2);
Selection selection1 = new Selection() { ActiveCell = activeColumn + "9", SequenceOfReferences = new ListValue<StringValue>() { InnerText = activeColumn + "9" } };
sheetView1.Append(selection1);
sheetViews1.Append(sheetView1);
SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };
Columns columns1 = new Columns();
Column column1 = new Column() { Min = (UInt32Value)1U, Max = (UInt32Value)2U, Width = 11D, CustomWidth = true };
columns1.Append(column1);
SheetData sheetData1 = new SheetData();
Row row1 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = (string.IsNullOrEmpty(partIds[1])) ? "1:2" : "1:9" }, DyDescent = 0.25D };
char column = 'A';
if (!string.IsNullOrEmpty(partIds[0]))
{
// Add file level metadata name to column A.
Cell cell1 = new Cell() { CellReference = "A1", DataType = CellValues.String };
CellValue cellValue1 = new CellValue() { Text = "Name" };
cell1.Append(cellValue1);
row1.Append(cell1);
// Add file level metadata value to column B.
Cell cell2 = new Cell() { CellReference = "B1", DataType = CellValues.String };
CellValue cellValue2 = new CellValue() { Text = "Value" };
cell2.Append(cellValue2);
row1.Append(cell2);
// Add column level metadata from column D if file level metadata is present.
column = 'D';
}
if (!string.IsNullOrEmpty(partIds[1]))
{
Cell cell3 = new Cell() { CellReference = (column++) + "1", DataType = CellValues.String };
CellValue cellValue3 = new CellValue() { Text = Statics.TableName };
cell3.Append(cellValue3);
row1.Append(cell3);
Cell cell4 = new Cell() { CellReference = (column++) + "1", DataType = CellValues.String };
CellValue cellValue4 = new CellValue() { Text = Statics.TableDescription };
cell4.Append(cellValue4);
row1.Append(cell4);
Cell cell5 = new Cell() { CellReference = (column++) + "1", DataType = CellValues.String };
CellValue cellValue5 = new CellValue() { Text = Statics.FieldName };
cell5.Append(cellValue5);
row1.Append(cell5);
Cell cell6 = new Cell() { CellReference = (column++) + "1", DataType = CellValues.String };
CellValue cellValue6 = new CellValue() { Text = Statics.FieldDescription };
cell6.Append(cellValue6);
row1.Append(cell6);
Cell cell7 = new Cell() { CellReference = (column++) + "1", DataType = CellValues.String };
CellValue cellValue7 = new CellValue() { Text = Statics.DataType };
cell7.Append(cellValue7);
row1.Append(cell7);
Cell cell8 = new Cell() { CellReference = (column++) + "1", DataType = CellValues.String };
CellValue cellValue8 = new CellValue() { Text = Statics.Units };
cell8.Append(cellValue8);
row1.Append(cell8);
}
sheetData1.Append(row1);
PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
TableParts tableParts1 = new TableParts() { Count = new UInt32Value((uint)partIds.Count(id => !string.IsNullOrEmpty(id))) };
if (!string.IsNullOrEmpty(partIds[0]))
{
tableParts1.Append(new TablePart() { Id = partIds[0] });
}
if (!string.IsNullOrEmpty(partIds[1]))
{
tableParts1.Append(new TablePart() { Id = partIds[1] });
}
worksheet1.Append(sheetDimension1);
worksheet1.Append(sheetViews1);
worksheet1.Append(sheetFormatProperties1);
worksheet1.Append(columns1);
worksheet1.Append(sheetData1);
worksheet1.Append(pageMargins1);
worksheet1.Append(tableParts1);
part.Worksheet = worksheet1;
}
/// <summary>
/// Generates the Content of the table in the worksheet
/// </summary>
/// <param name="worksheetPart">Worksheet to add the content onto</param>
private void GenerateWorksheetContent(WorksheetPart worksheetPart)
{
var worksheet = new Worksheet {
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x14ac xr xr2 xr3"
}
};
worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
worksheet.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
worksheet.AddNamespaceDeclaration("xr2", "http://schemas.microsoft.com/office/spreadsheetml/2015/revision2");
worksheet.AddNamespaceDeclaration("xr3", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3");
worksheet.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{AB762A7B-1BC4-4BEC-86BE-67D5F0445939}"));
SheetData sheetData = new SheetData();
Row header = new Row {
RowIndex = 1U, Spans = new ListValue <StringValue> {
InnerText = "1:2"
}, DyDescent = 0.4D
};
for (var i = 0; i < _tableColumns.Count; i++)
{
string column = ConvertToAlphabetBasedNumber(i + 1);
Cell cell = new Cell {
CellReference = $"{column}1",
StyleIndex = 1U, DataType = CellValues.String, CellValue = new CellValue(_tableColumns[i])
};
header.Append(cell);
}
sheetData.Append(header);
for (var y = 0; y < _rows.Count; y++)
{
uint rowIndex = (uint)(y + 2); //1 based index plus the header
Row row = new Row {
RowIndex = rowIndex, Spans = new ListValue <StringValue> {
InnerText = "1:2"
}, DyDescent = 0.4D
};
for (var x = 0; x < _rows[y].Count; x++)
{
TableCell tableCell = _rows[y][x];
string column = ConvertToAlphabetBasedNumber(x + 1);
Cell cell = new Cell {
CellReference = $"{column}{rowIndex}",
StyleIndex = 1U, DataType = tableCell.ValueType, CellValue = tableCell.GetValue()
};
row.Append(cell);
}
sheetData.Append(row);
}
TableParts tableParts = new TableParts {
Count = 1U
};
TablePart tablePart = new TablePart {
Id = "rId2"
};
tableParts.Append(tablePart);
worksheet.Append(sheetData);
worksheet.Append(tableParts);
worksheetPart.Worksheet = worksheet;
}
//This is work in progress. Apply SOLID and DRY because it is too long and complex
//Future modifications:
//1. Done (Send the buttons as modiffiers, so they can be attached only when needed)
//2. Done (Image modifier to wrap a specific column, just as NationalFlag one)
//3. Done (String formatter to separate camel-case in different words)
//4. Icons on buttons
//5. Exception handling here and in the related classes
public static IHtmlContent AutomaticTableTypedWithActions <TModel, TResult>(this IHtmlHelper htmlHelper,
Expression <Func <TModel, TResult> > idProperty, IEnumerable <TModel> data,
object tableHtmlAttributes,
string detailLinkTargetAction, string detailLinkTargetController,
string editLinkText, string editTargetAction, string editTargetController,
object editHtmlAttributes,
string deleteLinkText, string deleteTargetAction, string deleteTargetController,
object deleteHtmlAttributes, Expression <Func <TModel, TResult> > imageProperty)
where TModel : class, new()
{
//Starting the table and adding attributes to it
var table = new TagBuilder("table");
table.MergeAttributes(HtmlHelper.AnonymousObjectToHtmlAttributes(tableHtmlAttributes));
//Create a new row for the header
var headerRow = TableParts.CreateTableHeaderRowWithActions <TModel>(new string[] { editLinkText, deleteLinkText });
table.InnerHtml.AppendHtml(headerRow);
//Getting the name and value of the expression received to identify which one is the ID
var bodyId = idProperty.Body as MemberExpression;
var idPropertyName = bodyId.Member.Name;
//Getting the name and value of the expression received to identify which one is the ID
var bodyImg = imageProperty.Body as MemberExpression;
var imgPropertyName = bodyImg.Member.Name;
bool firstTime = true;
Type dType = null;
PropertyInfo modelIdPropety = null;
PropertyInfo modelImgPropety = null;
foreach (var d in data)
{
//Getting the value of model's ID property that belongs to the object "d"
if (firstTime)
{
//avoiding the use of reflection all the time
dType = d.GetType();
modelIdPropety = dType.GetProperty(idPropertyName);
modelImgPropety = dType.GetProperty(imgPropertyName);
firstTime = false;
}
//Getting the values of the desired properties
object modelIdPropertyValue = modelIdPropety.GetValue(d);
object modelImgPropertyValue = modelImgPropety.GetValue(d);
//Making a new row for the table
var dataRow = TableParts.CreateTableRowWithDetailAndImage(idPropertyName, modelIdPropertyValue, d, dType,
detailLinkTargetAction, detailLinkTargetController,
imgPropertyName, modelImgPropertyValue);
table.InnerHtml.AppendHtml(dataRow);
//Creating a link button for the Edit action
LinkButtonCreation buttons = new LinkButtonCreation();
var editButton = buttons.LinkButtonCreate(editTargetAction, editTargetController, editLinkText,
idPropertyName.ToLower(), modelIdPropertyValue.ToString(),
editHtmlAttributes);
//Creating a link button for the Delete action
var deleteButton = buttons.LinkButtonCreate(deleteTargetAction, deleteTargetController, deleteLinkText,
idPropertyName.ToLower(), modelIdPropertyValue.ToString(),
deleteHtmlAttributes);
//Appending buttons to row
RowModificator.AppendCustomCellToRow(ref dataRow, editButton);
RowModificator.AppendCustomCellToRow(ref dataRow, deleteButton);
}
return(table);
}
private static void AddTablePart(WorksheetPart sheetPart, TableColumn[] columns, int rowCount, WorkbookPart workBookPart)
{
if (sheetPart == null)
{
throw new ArgumentNullException(nameof(sheetPart));
}
if (columns == null)
{
throw new ArgumentNullException(nameof(columns));
}
if (rowCount < 0)
{
throw new ArgumentOutOfRangeException(nameof(rowCount));
}
if (workBookPart == null)
{
throw new ArgumentNullException(nameof(workBookPart));
}
var rangeReference = GetXlsTableRangeReference(columns.Length, rowCount);
var ignoredErrors = new IgnoredErrors(
new IgnoredError
{
NumberStoredAsText = true,
SequenceOfReferences = new ListValue <StringValue>
{
InnerText = rangeReference
}
}
);
// Ignored errors must be added before table parts.
sheetPart.Worksheet.Append(ignoredErrors);
var tableDefinitionPart = sheetPart.AddNewPart <TableDefinitionPart>();
var autoFilter = new AutoFilter {
Reference = rangeReference
};
var tableColumns = new TableColumns {
Count = (uint)columns.Length
};
var styleInfo = new TableStyleInfo
{
Name = "TableStyleMedium2",
ShowFirstColumn = false,
ShowLastColumn = false,
ShowRowStripes = true,
ShowColumnStripes = false
};
var tableId = workBookPart.WorksheetParts
.Select(x => x.TableDefinitionParts.Where(y => y.Table != null)
.Select(y => (uint)y.Table.Id).DefaultIfEmpty(0U).Max()).DefaultIfEmpty(0U).Max() + 1;
var table =
new DocumentFormat.OpenXml.Spreadsheet.Table(autoFilter, tableColumns, styleInfo)
{
Id = tableId,
Name = "Table" + tableId,
DisplayName = "Table" + tableId,
Reference = rangeReference,
TotalsRowShown = false
};
for (var i = 0; i < columns.Length; i++)
{
table.TableColumns.Append(
new DocumentFormat.OpenXml.Spreadsheet.TableColumn
{
Id = (uint)(i + 1),
Name = columns[i].ColumnName
});
}
tableDefinitionPart.Table = table;
var tableParts = new TableParts(
new TablePart
{
Id = sheetPart.GetIdOfPart(tableDefinitionPart)
}
)
{
Count = 1U
};
sheetPart.Worksheet.Append(tableParts);
}
// Generates content of worksheetPart2.
private void GenerateWorksheetPart2Content(WorksheetPart worksheetPart2)
{
Worksheet worksheet2 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet2.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet2.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
worksheet2.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
worksheet2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet2.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetDimension sheetDimension2 = new SheetDimension() { Reference = "A1:V19" };
SheetViews sheetViews2 = new SheetViews();
SheetView sheetView2 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U };
Selection selection2 = new Selection() { ActiveCell = "X5", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "X5" } };
sheetView2.Append(selection2);
sheetViews2.Append(sheetView2);
SheetFormatProperties sheetFormatProperties2 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D };
SheetData sheetData2 = new SheetData();
Row row2 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell2 = new Cell() { CellReference = "A1" };
CellValue cellValue2 = new CellValue();
cellValue2.Text = "1";
cell2.Append(cellValue2);
Cell cell3 = new Cell() { CellReference = "C1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };
CellValue cellValue3 = new CellValue();
cellValue3.Text = "0";
cell3.Append(cellValue3);
row2.Append(cell2);
row2.Append(cell3);
Row row3 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell4 = new Cell() { CellReference = "A2" };
CellValue cellValue4 = new CellValue();
cellValue4.Text = "2";
cell4.Append(cellValue4);
row3.Append(cell4);
Row row4 = new Row() { RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell5 = new Cell() { CellReference = "A3" };
CellValue cellValue5 = new CellValue();
cellValue5.Text = "3";
cell5.Append(cellValue5);
row4.Append(cell5);
Row row5 = new Row() { RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, Height = 135D, DyDescent = 0.25D };
Cell cell6 = new Cell() { CellReference = "A4" };
CellValue cellValue6 = new CellValue();
cellValue6.Text = "4";
cell6.Append(cellValue6);
Cell cell7 = new Cell() { CellReference = "C4", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString };
CellValue cellValue7 = new CellValue();
cellValue7.Text = "1";
cell7.Append(cellValue7);
row5.Append(cell6);
row5.Append(cell7);
Row row6 = new Row() { RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell8 = new Cell() { CellReference = "A5" };
CellValue cellValue8 = new CellValue();
cellValue8.Text = "5";
cell8.Append(cellValue8);
Cell cell9 = new Cell() { CellReference = "Q5", StyleIndex = (UInt32Value)3U, DataType = CellValues.SharedString };
CellValue cellValue9 = new CellValue();
cellValue9.Text = "6";
cell9.Append(cellValue9);
Cell cell10 = new Cell() { CellReference = "T5" };
CellValue cellValue10 = new CellValue();
cellValue10.Text = "4";
cell10.Append(cellValue10);
row6.Append(cell8);
row6.Append(cell9);
row6.Append(cell10);
Row row7 = new Row() { RowIndex = (UInt32Value)6U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell11 = new Cell() { CellReference = "A6" };
CellFormula cellFormula1 = new CellFormula();
cellFormula1.Text = "SUM(A1:A5)";
CellValue cellValue11 = new CellValue();
cellValue11.Text = "15";
cell11.Append(cellFormula1);
cell11.Append(cellValue11);
Cell cell12 = new Cell() { CellReference = "T6" };
CellValue cellValue12 = new CellValue();
cellValue12.Text = "7";
cell12.Append(cellValue12);
row7.Append(cell11);
row7.Append(cell12);
Row row8 = new Row() { RowIndex = (UInt32Value)7U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell13 = new Cell() { CellReference = "T7" };
CellValue cellValue13 = new CellValue();
cellValue13.Text = "6";
cell13.Append(cellValue13);
row8.Append(cell13);
Row row9 = new Row() { RowIndex = (UInt32Value)8U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell14 = new Cell() { CellReference = "T8" };
CellValue cellValue14 = new CellValue();
cellValue14.Text = "5";
cell14.Append(cellValue14);
row9.Append(cell14);
Row row10 = new Row() { RowIndex = (UInt32Value)9U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell15 = new Cell() { CellReference = "T9" };
CellValue cellValue15 = new CellValue();
cellValue15.Text = "4";
cell15.Append(cellValue15);
row10.Append(cell15);
Row row11 = new Row() { RowIndex = (UInt32Value)10U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D };
Cell cell16 = new Cell() { CellReference = "T10" };
CellValue cellValue16 = new CellValue();
cellValue16.Text = "7";
cell16.Append(cellValue16);
row11.Append(cell16);
Row row12 = new Row() { RowIndex = (UInt32Value)17U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
Cell cell17 = new Cell() { CellReference = "A17", DataType = CellValues.SharedString };
CellValue cellValue17 = new CellValue();
cellValue17.Text = "3";
cell17.Append(cellValue17);
Cell cell18 = new Cell() { CellReference = "B17", DataType = CellValues.SharedString };
CellValue cellValue18 = new CellValue();
cellValue18.Text = "4";
cell18.Append(cellValue18);
Cell cell19 = new Cell() { CellReference = "C17", DataType = CellValues.SharedString };
CellValue cellValue19 = new CellValue();
cellValue19.Text = "5";
cell19.Append(cellValue19);
row12.Append(cell17);
row12.Append(cell18);
row12.Append(cell19);
Row row13 = new Row() { RowIndex = (UInt32Value)18U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
Cell cell20 = new Cell() { CellReference = "A18" };
CellValue cellValue20 = new CellValue();
cellValue20.Text = "1";
cell20.Append(cellValue20);
Cell cell21 = new Cell() { CellReference = "B18" };
CellValue cellValue21 = new CellValue();
cellValue21.Text = "2";
cell21.Append(cellValue21);
Cell cell22 = new Cell() { CellReference = "C18" };
CellValue cellValue22 = new CellValue();
cellValue22.Text = "3";
cell22.Append(cellValue22);
row13.Append(cell20);
row13.Append(cell21);
row13.Append(cell22);
Row row14 = new Row() { RowIndex = (UInt32Value)19U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D };
Cell cell23 = new Cell() { CellReference = "A19" };
CellValue cellValue23 = new CellValue();
cellValue23.Text = "4";
cell23.Append(cellValue23);
Cell cell24 = new Cell() { CellReference = "B19" };
CellValue cellValue24 = new CellValue();
cellValue24.Text = "5";
cell24.Append(cellValue24);
Cell cell25 = new Cell() { CellReference = "C19" };
CellValue cellValue25 = new CellValue();
cellValue25.Text = "6";
cell25.Append(cellValue25);
row14.Append(cell23);
row14.Append(cell24);
row14.Append(cell25);
sheetData2.Append(row2);
sheetData2.Append(row3);
sheetData2.Append(row4);
sheetData2.Append(row5);
sheetData2.Append(row6);
sheetData2.Append(row7);
sheetData2.Append(row8);
sheetData2.Append(row9);
sheetData2.Append(row10);
sheetData2.Append(row11);
sheetData2.Append(row12);
sheetData2.Append(row13);
sheetData2.Append(row14);
Hyperlinks hyperlinks1 = new Hyperlinks();
Hyperlink hyperlink2 = new Hyperlink() { Reference = "Q5", Id = "rId1" };
hyperlinks1.Append(hyperlink2);
PageMargins pageMargins2 = new PageMargins() { Left = 0.25D, Right = 0.25D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D };
PageSetup pageSetup1 = new PageSetup() { Orientation = OrientationValues.Landscape, HorizontalDpi = (UInt32Value)4294967293U, VerticalDpi = (UInt32Value)0U, Id = "rId2" };
S.Drawing drawing3 = new S.Drawing() { Id = "rId3" };
LegacyDrawing legacyDrawing1 = new LegacyDrawing() { Id = "rId4" };
TableParts tableParts1 = new TableParts() { Count = (UInt32Value)1U };
TablePart tablePart1 = new TablePart() { Id = "rId5" };
tableParts1.Append(tablePart1);
worksheet2.Append(sheetDimension2);
worksheet2.Append(sheetViews2);
worksheet2.Append(sheetFormatProperties2);
worksheet2.Append(sheetData2);
worksheet2.Append(hyperlinks1);
worksheet2.Append(pageMargins2);
worksheet2.Append(pageSetup1);
worksheet2.Append(drawing3);
worksheet2.Append(legacyDrawing1);
worksheet2.Append(tableParts1);
worksheetPart2.Worksheet = worksheet2;
}
/// <summary>
/// Generate Excel Document.
/// </summary>
/// <param name="queryResult">QueryResult</param>
/// <returns>MemoryStream</returns>
public static MemoryStream CreateExcelDocument(QueryResult queryResult)
{
var ms = new MemoryStream( );
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true))
{
// Create the Workbook
WorkbookPart workbookPart = spreadSheet.AddWorkbookPart( );
spreadSheet.WorkbookPart.Workbook = new Workbook( );
// A Workbook must only have exactly one <Sheets> section
spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets( ));
var newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>( );
newWorksheetPart.Worksheet = new Worksheet( );
// Create a new Excel worksheet
SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData( ));
var tdp = newWorksheetPart.AddNewPart <TableDefinitionPart>( );
string rId = newWorksheetPart.GetIdOfPart(tdp);
var T = new Table
{
Id = 1U,
Name = "MyTable",
DisplayName = "MyTable",
Reference = "A1:B10",
TotalsRowShown = false
};
var columns = new TableColumns
{
Count = 2U
};
var column1 = new TableColumn
{
Id = 1U,
Name = "Column1"
};
var column2 = new TableColumn
{
Id = 2U,
Name = "Column2"
};
var styleInfo = new TableStyleInfo
{
Name = "TableStyleMedium2",
ShowFirstColumn = false,
ShowLastColumn = false,
ShowRowStripes = true,
ShowColumnStripes = false
};
var autoFilter = new AutoFilter
{
Reference = "A1:B10"
};
columns.Append(column1);
columns.Append(column2);
T.Append(autoFilter);
T.Append(columns);
T.Append(styleInfo);
tdp.Table = T;
T.Save( );
var tableParts = new TableParts
{
Count = 1U
};
var tablePart = new TablePart
{
Id = rId
};
tableParts.Append(tablePart);
newWorksheetPart.Worksheet.Append(tableParts);
newWorksheetPart.Worksheet.Save( );
// Create Styles and Insert into Workbook
var stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>( );
Stylesheet styles = new ExportDataStylesheet( );
styles.Save(stylesPart);
// Insert Datatable data into the worksheet.
InsertTableData(queryResult, sheetData, stylesPart);
// Save the worksheet.
newWorksheetPart.Worksheet.Save( );
// Link this worksheet to our workbook
spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>( ).AppendChild(new Sheet
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = 1,
Name = "Table"
});
// Save the workbook.
spreadSheet.WorkbookPart.Workbook.Save( );
}
return(ms);
}
// 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;
}
