static void GenerateWorksheetPartContent(WorksheetPart worksheetPart, DataTable dataTable) { var worksheet = new Worksheet(); var sheetData = new SheetData(); ExportDataTable(sheetData, dataTable); worksheet.Append(sheetData); if (dataTable.TableName != "Дата") { var worksheetExtensionList = new WorksheetExtensionList(); var worksheetExtension = new WorksheetExtension { Uri = WorksheetExtensionUri }; var dataValidations = new X14.DataValidations { Count = (UInt32Value)1U }; var dataValidation = new X14.DataValidation { Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true }; var dataValidationForumla1 = new X14.DataValidationForumla1(); var formula = new Excel.Formula { Text = "Дата!$B$2:$B$4" }; dataValidationForumla1.Append(formula); var referenceSequence = new Excel.ReferenceSequence { Text = "F2:F4" }; dataValidation.Append(dataValidationForumla1); dataValidation.Append(referenceSequence); dataValidations.Append(dataValidation); worksheetExtension.Append(dataValidations); worksheetExtensionList.Append(worksheetExtension); worksheet.Append(worksheetExtensionList); } worksheetPart.Worksheet = worksheet; }
private void WriteSelectedWorksheet() { // split into writing for existing worksheet and for new worksheet this.CleanUpReallyEmptyCells(); int i = 0; bool bFound = false; OpenXmlElement oxe; SLColumnProperties cp; SLRowProperties rp; byte byMaxOutline; List<int> listintkeys; // remove empty rows/columns plus getting the maximum outline levels at the same time. byMaxOutline = 0; listintkeys = slws.RowProperties.Keys.ToList<int>(); foreach (int key in listintkeys) { rp = slws.RowProperties[key]; if (rp.IsEmpty) slws.RowProperties.Remove(key); else if (rp.OutlineLevel > byMaxOutline) byMaxOutline = rp.OutlineLevel; } if (byMaxOutline > 0) slws.SheetFormatProperties.OutlineLevelRow = byMaxOutline; byMaxOutline = 0; listintkeys = slws.ColumnProperties.Keys.ToList<int>(); foreach (int key in listintkeys) { cp = slws.ColumnProperties[key]; if (cp.IsEmpty) slws.ColumnProperties.Remove(key); else if (cp.OutlineLevel > byMaxOutline) byMaxOutline = cp.OutlineLevel; } if (byMaxOutline > 0) slws.SheetFormatProperties.OutlineLevelColumn = byMaxOutline; List<SLCellPoint> listCellRefKeys = slws.Cells.Keys.ToList<SLCellPoint>(); listCellRefKeys.Sort(new SLCellReferencePointComparer()); HashSet<int> hsRows = new HashSet<int>(listCellRefKeys.GroupBy(g => g.RowIndex).Select(s => s.Key).ToList<int>()); hsRows.UnionWith(slws.RowProperties.Keys.ToList<int>()); // this now contains every row index that's either in the list of row properties // or in the list of cells. List<int> listRowIndex = hsRows.ToList<int>(); listRowIndex.Sort(); List<int> listColumnIndex = slws.ColumnProperties.Keys.ToList<int>(); listColumnIndex.Sort(); int iDimensionStartRowIndex = SLConstants.RowLimit + 1; int iDimensionStartColumnIndex = SLConstants.ColumnLimit + 1; int iDimensionEndRowIndex = -1; int iDimensionEndColumnIndex = -1; if (listCellRefKeys.Count > 0 || listRowIndex.Count > 0 || listColumnIndex.Count > 0 || slws.MergeCells.Count > 0) { foreach (SLCellPoint refpt in slws.Cells.Keys) { // just check for columns because row checking is already done with RowProperties // this cuts down on checking, and speed things up. if (refpt.ColumnIndex < iDimensionStartColumnIndex) iDimensionStartColumnIndex = refpt.ColumnIndex; if (refpt.ColumnIndex > iDimensionEndColumnIndex) iDimensionEndColumnIndex = refpt.ColumnIndex; } if (listRowIndex.Count > 0) { if (listRowIndex[0] < iDimensionStartRowIndex) iDimensionStartRowIndex = listRowIndex[0]; if (listRowIndex[listRowIndex.Count - 1] > iDimensionEndRowIndex) iDimensionEndRowIndex = listRowIndex[listRowIndex.Count - 1]; } if (listColumnIndex.Count > 0) { if (listColumnIndex[0] < iDimensionStartColumnIndex) iDimensionStartColumnIndex = listColumnIndex[0]; if (listColumnIndex[listColumnIndex.Count - 1] > iDimensionEndColumnIndex) iDimensionEndColumnIndex = listColumnIndex[listColumnIndex.Count - 1]; } foreach (SLMergeCell mc in slws.MergeCells) { if (mc.StartRowIndex < iDimensionStartRowIndex) iDimensionStartRowIndex = mc.StartRowIndex; if (mc.StartColumnIndex < iDimensionStartColumnIndex) iDimensionStartColumnIndex = mc.StartColumnIndex; if (mc.EndRowIndex > iDimensionEndRowIndex) iDimensionEndRowIndex = mc.EndRowIndex; if (mc.EndColumnIndex > iDimensionEndColumnIndex) iDimensionEndColumnIndex = mc.EndColumnIndex; } // need to do for hyperlinks? //foreach (SLHyperlink hl in slws.Hyperlinks) //{ // if (hl.Reference.StartRowIndex < iDimensionStartRowIndex) iDimensionStartRowIndex = hl.Reference.StartRowIndex; // if (hl.Reference.StartColumnIndex < iDimensionStartColumnIndex) iDimensionStartColumnIndex = hl.Reference.StartColumnIndex; // if (hl.Reference.EndRowIndex > iDimensionEndRowIndex) iDimensionEndRowIndex = hl.Reference.EndRowIndex; // if (hl.Reference.EndColumnIndex > iDimensionEndColumnIndex) iDimensionEndColumnIndex = hl.Reference.EndColumnIndex; //} } string sDimensionCellRange = string.Empty; if (iDimensionStartRowIndex > SLConstants.RowLimit) iDimensionStartRowIndex = 1; if (iDimensionStartColumnIndex > SLConstants.ColumnLimit) iDimensionStartColumnIndex = 1; if (iDimensionEndRowIndex < 1) iDimensionEndRowIndex = 1; if (iDimensionEndColumnIndex < 1) iDimensionEndColumnIndex = 1; if (iDimensionStartRowIndex == iDimensionEndRowIndex && iDimensionStartColumnIndex == iDimensionEndColumnIndex) { sDimensionCellRange = SLTool.ToCellReference(iDimensionStartRowIndex, iDimensionStartColumnIndex); } else { sDimensionCellRange = string.Format("{0}:{1}", SLTool.ToCellReference(iDimensionStartRowIndex, iDimensionStartColumnIndex), SLTool.ToCellReference(iDimensionEndRowIndex, iDimensionEndColumnIndex)); } Row r; SLCell c; int iRowIndex = 0; int iCellDataKey = 0; int iRowKey = 0; SLCellPoint pt; if (!IsNewWorksheet) { // Need to check? //if (string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID)) return; WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID); if (slws.ForceCustomRowColumnDimensionsSplitting) { slws.ToggleCustomRowColumnDimension(true); } if (slws.PageSettings.HasSheetProperties) { wsp.Worksheet.SheetProperties = slws.PageSettings.SheetProperties.ToSheetProperties(); } else { wsp.Worksheet.SheetProperties = null; } wsp.Worksheet.SheetDimension = new SheetDimension() { Reference = sDimensionCellRange }; if (slws.SheetViews.Count > 0) { wsp.Worksheet.SheetViews = new SheetViews(); foreach (SLSheetView sv in slws.SheetViews) { wsp.Worksheet.SheetViews.Append(sv.ToSheetView()); } } else { wsp.Worksheet.SheetViews = null; } wsp.Worksheet.SheetFormatProperties = slws.SheetFormatProperties.ToSheetFormatProperties(); #region Filling Columns if (wsp.Worksheet.Elements<Columns>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<Columns>(); } if (slws.ColumnProperties.Count > 0) { Columns cols = new Columns(); Column col; int iPreviousColumnIndex = listColumnIndex[0]; int iCurrentColumnIndex = iPreviousColumnIndex; string sCollectiveColumnData = string.Empty; string sCurrentColumnData = string.Empty; int colmin, colmax; colmin = colmax = iCurrentColumnIndex; cp = slws.ColumnProperties[iCurrentColumnIndex]; sCollectiveColumnData = cp.ToHash(); col = new Column(); col.Min = (uint)colmin; col.Max = (uint)colmax; if (cp.HasWidth) { col.Width = cp.Width; col.CustomWidth = true; } else { col.Width = slws.SheetFormatProperties.DefaultColumnWidth; } if (cp.StyleIndex > 0) col.Style = cp.StyleIndex; if (cp.Hidden) col.Hidden = cp.Hidden; if (cp.BestFit) col.BestFit = cp.BestFit; if (cp.Phonetic) col.Phonetic = cp.Phonetic; if (cp.OutlineLevel > 0) col.OutlineLevel = cp.OutlineLevel; if (cp.Collapsed) col.Collapsed = cp.Collapsed; for (i = 1; i < listColumnIndex.Count; ++i) { iPreviousColumnIndex = iCurrentColumnIndex; iCurrentColumnIndex = listColumnIndex[i]; cp = slws.ColumnProperties[iCurrentColumnIndex]; sCurrentColumnData = cp.ToHash(); if ((iCurrentColumnIndex != (iPreviousColumnIndex + 1)) || (sCollectiveColumnData != sCurrentColumnData)) { col.Max = (uint)colmax; cols.Append(col); colmin = iCurrentColumnIndex; colmax = iCurrentColumnIndex; sCollectiveColumnData = sCurrentColumnData; col = new Column(); col.Min = (uint)colmin; col.Max = (uint)colmax; if (cp.HasWidth) { col.Width = cp.Width; col.CustomWidth = true; } else { col.Width = slws.SheetFormatProperties.DefaultColumnWidth; } if (cp.StyleIndex > 0) col.Style = cp.StyleIndex; if (cp.Hidden) col.Hidden = cp.Hidden; if (cp.BestFit) col.BestFit = cp.BestFit; if (cp.Phonetic) col.Phonetic = cp.Phonetic; if (cp.OutlineLevel > 0) col.OutlineLevel = cp.OutlineLevel; if (cp.Collapsed) col.Collapsed = cp.Collapsed; } else { colmax = iCurrentColumnIndex; } } // there's always a "leftover" column col.Max = (uint)colmax; cols.Append(col); bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { if (child is SheetProperties || child is SheetDimension || child is SheetViews || child is SheetFormatProperties) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(cols, oxe); } else { wsp.Worksheet.PrependChild(cols); } } #endregion SheetData sd = new SheetData(); iCellDataKey = 0; for (iRowKey = 0; iRowKey < listRowIndex.Count; ++iRowKey) { iRowIndex = listRowIndex[iRowKey]; if (slws.RowProperties.ContainsKey(iRowIndex)) { r = slws.RowProperties[iRowIndex].ToRow(); r.RowIndex = (uint)iRowIndex; } else { r = new Row(); r.RowIndex = (uint)iRowIndex; } while (iCellDataKey < listCellRefKeys.Count) { pt = listCellRefKeys[iCellDataKey]; if (pt.RowIndex == iRowIndex) { c = slws.Cells[pt]; r.Append(c.ToCell(SLTool.ToCellReference(pt.RowIndex, pt.ColumnIndex))); ++iCellDataKey; } else { break; } } sd.Append(r); } wsp.Worksheet.RemoveAllChildren<SheetData>(); bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { if (child is SheetProperties || child is SheetDimension || child is SheetViews || child is SheetFormatProperties || child is Columns) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(sd, oxe); } else { wsp.Worksheet.PrependChild(sd); } #region Sheet protection if (wsp.Worksheet.Elements<SheetProtection>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<SheetProtection>(); } if (slws.HasSheetProtection) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.SheetProtection.ToSheetProtection(), oxe); } else { wsp.Worksheet.PrependChild(slws.SheetProtection.ToSheetProtection()); } } #endregion #region AutoFilter if (wsp.Worksheet.Elements<AutoFilter>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<AutoFilter>(); } if (slws.HasAutoFilter) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.AutoFilter.ToAutoFilter(), oxe); } else { wsp.Worksheet.PrependChild(slws.AutoFilter.ToAutoFilter()); } } #endregion #region Filling merge cells if (wsp.Worksheet.Elements<MergeCells>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<MergeCells>(); } if (slws.MergeCells.Count > 0) { MergeCells mcs = new MergeCells() { Count = (uint)slws.MergeCells.Count }; for (i = 0; i < slws.MergeCells.Count; ++i) { mcs.Append(slws.MergeCells[i].ToMergeCell()); } bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(mcs, oxe); } else { wsp.Worksheet.PrependChild(mcs); } } #endregion #region Conditional Formatting if (wsp.Worksheet.Elements<ConditionalFormatting>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<ConditionalFormatting>(); } if (slws.ConditionalFormattings.Count > 0) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties) { oxe = child; bFound = true; } } if (bFound) { for (i = slws.ConditionalFormattings.Count - 1; i >= 0; --i) { wsp.Worksheet.InsertAfter(slws.ConditionalFormattings[i].ToConditionalFormatting(), oxe); } } else { for (i = slws.ConditionalFormattings.Count - 1; i >= 0; --i) { wsp.Worksheet.PrependChild(slws.ConditionalFormattings[i].ToConditionalFormatting()); } } } #endregion #region DataValidations if (wsp.Worksheet.Elements<DataValidations>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<DataValidations>(); } if (slws.DataValidations.Count > 0) { DataValidations dvs = new DataValidations(); if (slws.DataValidationDisablePrompts) dvs.DisablePrompts = slws.DataValidationDisablePrompts; if (slws.DataValidationXWindow != null) dvs.XWindow = slws.DataValidationXWindow.Value; if (slws.DataValidationYWindow != null) dvs.YWindow = slws.DataValidationYWindow.Value; dvs.Count = (uint)slws.DataValidations.Count; foreach (SLDataValidation dv in slws.DataValidations) { dvs.Append(dv.ToDataValidation()); } bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(dvs, oxe); } else { wsp.Worksheet.PrependChild(dvs); } } #endregion #region Hyperlinks if (wsp.Worksheet.Elements<Hyperlinks>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<Hyperlinks>(); } if (slws.Hyperlinks.Count > 0) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations) { oxe = child; bFound = true; } } Hyperlinks hls = new Hyperlinks(); HyperlinkRelationship hlrel; foreach (SLHyperlink hl in slws.Hyperlinks) { if (hl.IsExternal && hl.IsNew) { hlrel = wsp.AddHyperlinkRelationship(new Uri(hl.HyperlinkUri, hl.HyperlinkUriKind), true); hl.Id = hlrel.Id; } hls.Append(hl.ToHyperlink()); } if (bFound) { wsp.Worksheet.InsertAfter(hls, oxe); } else { wsp.Worksheet.PrependChild(hls); } } #endregion #region PrintOptions if (wsp.Worksheet.Elements<PrintOptions>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<PrintOptions>(); } if (slws.PageSettings.HasPrintOptions) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.PageSettings.ExportPrintOptions(), oxe); } else { wsp.Worksheet.PrependChild(slws.PageSettings.ExportPrintOptions()); } } #endregion #region PageMargins if (wsp.Worksheet.Elements<PageMargins>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<PageMargins>(); } if (slws.PageSettings.HasPageMargins) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.PageSettings.ExportPageMargins(), oxe); } else { wsp.Worksheet.PrependChild(slws.PageSettings.ExportPageMargins()); } } #endregion #region PageSetup if (wsp.Worksheet.Elements<PageSetup>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<PageSetup>(); } if (slws.PageSettings.HasPageSetup) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.PageSettings.ExportPageSetup(), oxe); } else { wsp.Worksheet.PrependChild(slws.PageSettings.ExportPageSetup()); } } #endregion #region HeaderFooter if (wsp.Worksheet.Elements<HeaderFooter>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<HeaderFooter>(); } if (slws.PageSettings.HasHeaderFooter) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.PageSettings.ExportHeaderFooter(), oxe); } else { wsp.Worksheet.PrependChild(slws.PageSettings.ExportHeaderFooter()); } } #endregion #region RowBreaks if (wsp.Worksheet.Elements<RowBreaks>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<RowBreaks>(); } if (slws.RowBreaks.Count > 0) { List<int> bkkeys = slws.RowBreaks.Keys.ToList<int>(); bkkeys.Sort(); RowBreaks rowbk = new RowBreaks(); int bkmancount = 0; foreach (int bkindex in bkkeys) { if (slws.RowBreaks[bkindex].ManualPageBreak) ++bkmancount; rowbk.Append(slws.RowBreaks[bkindex].ToBreak()); } rowbk.Count = (uint)slws.RowBreaks.Count; rowbk.ManualBreakCount = (uint)bkmancount; bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(rowbk, oxe); } else { wsp.Worksheet.PrependChild(rowbk); } } #endregion #region ColumnBreaks if (wsp.Worksheet.Elements<ColumnBreaks>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<ColumnBreaks>(); } if (slws.ColumnBreaks.Count > 0) { List<int> bkkeys = slws.ColumnBreaks.Keys.ToList<int>(); bkkeys.Sort(); ColumnBreaks colbk = new ColumnBreaks(); int bkmancount = 0; foreach (int bkindex in bkkeys) { if (slws.ColumnBreaks[bkindex].ManualPageBreak) ++bkmancount; colbk.Append(slws.ColumnBreaks[bkindex].ToBreak()); } colbk.Count = (uint)slws.ColumnBreaks.Count; colbk.ManualBreakCount = (uint)bkmancount; bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter || child is RowBreaks) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(colbk, oxe); } else { wsp.Worksheet.PrependChild(colbk); } } #endregion #region Drawing // these are "new" charts and pictures added if (slws.Charts.Count > 0 || slws.Pictures.Count > 0) { // if the length > 0, then we assume there's already an existing DrawingsPart if (slws.DrawingId.Length > 0) { WriteImageParts(wsp.DrawingsPart); } else { DrawingsPart dp = wsp.AddNewPart<DrawingsPart>(); dp.WorksheetDrawing = new Xdr.WorksheetDrawing(); dp.WorksheetDrawing.AddNamespaceDeclaration("xdr", SLConstants.NamespaceXdr); dp.WorksheetDrawing.AddNamespaceDeclaration("a", SLConstants.NamespaceA); DocumentFormat.OpenXml.Spreadsheet.Drawing drawing = new DocumentFormat.OpenXml.Spreadsheet.Drawing(); drawing.Id = wsp.GetIdOfPart(dp); WriteImageParts(dp); // NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove // from check below? bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter || child is RowBreaks || child is ColumnBreaks || child is CustomProperties || child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(drawing, oxe); } else { wsp.Worksheet.PrependChild(drawing); } } } #endregion #region LegacyDrawing if (slws.Comments.Count > 0) { // we're going to do this only if there are no comments and VML already if (wsp.WorksheetCommentsPart == null && wsp.Worksheet.Elements<LegacyDrawing>().Count() == 0) { WorksheetCommentsPart wcp = wsp.AddNewPart<WorksheetCommentsPart>(); VmlDrawingPart vdp = wsp.AddNewPart<VmlDrawingPart>(); WriteCommentPart(wcp, vdp); LegacyDrawing ldrawing = new LegacyDrawing(); ldrawing.Id = wsp.GetIdOfPart(vdp); // NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove // from check below? bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter || child is RowBreaks || child is ColumnBreaks || child is CustomProperties || child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/ || child is DocumentFormat.OpenXml.Spreadsheet.Drawing) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(ldrawing, oxe); } else { wsp.Worksheet.PrependChild(ldrawing); } } } #endregion #region Picture if (wsp.Worksheet.Elements<Picture>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<Picture>(); } if (slws.BackgroundPictureId.Length > 0 || slws.BackgroundPictureDataIsInFile != null) { Picture pic = new Picture(); if (slws.BackgroundPictureId.Length > 0) { pic.Id = slws.BackgroundPictureId; } else if (slws.BackgroundPictureDataIsInFile != null) { ImagePart imgp = wsp.AddImagePart(slws.BackgroundPictureImagePartType); if (slws.BackgroundPictureDataIsInFile.Value) { using (FileStream fs = new FileStream(slws.BackgroundPictureFileName, FileMode.Open)) { imgp.FeedData(fs); } } else { using (MemoryStream ms = new MemoryStream(slws.BackgroundPictureByteData)) { imgp.FeedData(ms); } } pic.Id = wsp.GetIdOfPart(imgp); } // NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove // from check below? bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter || child is RowBreaks || child is ColumnBreaks || child is CustomProperties || child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/ || child is DocumentFormat.OpenXml.Spreadsheet.Drawing || child is LegacyDrawing || child is LegacyDrawingHeaderFooter) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(pic, oxe); } else { wsp.Worksheet.PrependChild(pic); } } #endregion #region Tables if (wsp.Worksheet.Elements<TableParts>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<TableParts>(); } if (slws.Tables.Count > 0) { TableParts tps = new TableParts() { Count = (uint)slws.Tables.Count }; TableDefinitionPart tdp; string sRelID = string.Empty; foreach (SLTable t in slws.Tables) { if (t.IsNewTable) { if (t.RelationshipID.Length > 0) { // is a modified existing table tdp = (TableDefinitionPart)wsp.GetPartById(t.RelationshipID); tdp.Table = t.ToTable(); sRelID = t.RelationshipID; } else { // is a completely new table tdp = wsp.AddNewPart<TableDefinitionPart>(); tdp.Table = t.ToTable(); sRelID = wsp.GetIdOfPart(tdp); } } else { // if it's an existing table with no modifications, // don't need to do anything to the XML content. tdp = (TableDefinitionPart)wsp.GetPartById(t.RelationshipID); sRelID = t.RelationshipID; } tps.Append(new TablePart() { Id = sRelID }); } // NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove // from check below? bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter || child is RowBreaks || child is ColumnBreaks || child is CustomProperties || child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/ || child is DocumentFormat.OpenXml.Spreadsheet.Drawing || child is LegacyDrawing || child is LegacyDrawingHeaderFooter || child is Picture || child is OleObjects || child is Controls || child is WebPublishItems) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(tps, oxe); } else { wsp.Worksheet.PrependChild(tps); } } #endregion #region 2010 Conditional formatting, Sparklines and possibly other extensions WorksheetExtensionList wsextlist; WorksheetExtension wsext; List<WorksheetExtension> listExtensions = new List<WorksheetExtension>(); slws.RefreshSparklineGroups(); if (wsp.Worksheet.Elements<WorksheetExtensionList>().Count() > 0) { wsextlist = wsp.Worksheet.Elements<WorksheetExtensionList>().First(); foreach (var wsextchild in wsextlist.ChildElements) { if (wsextchild is WorksheetExtension) { wsext = (WorksheetExtension)wsextchild; wsext.RemoveAllChildren<X14.ConditionalFormattings>(); wsext.RemoveAllChildren<X14.SparklineGroups>(); // there might be other extension types, like slicers (erhmahgerd...). if (wsext.ChildElements.Count > 0) { listExtensions.Add((WorksheetExtension)wsext.CloneNode(true)); } } } wsp.Worksheet.RemoveAllChildren<WorksheetExtensionList>(); } if (slws.ConditionalFormattings2010.Count > 0 || slws.SparklineGroups.Count > 0 || listExtensions.Count > 0) { wsextlist = new WorksheetExtensionList(); foreach (WorksheetExtension ext in listExtensions) { // be extra safe by cloning again to avoid pass-by-reference. Deeply. wsextlist.Append((WorksheetExtension)ext.CloneNode(true)); } if (slws.ConditionalFormattings2010.Count > 0) { // this is important! Apparently extensions are tied to a URI that Microsoft uses. wsext = new WorksheetExtension() { Uri = SLConstants.ConditionalFormattingExtensionUri }; wsext.AddNamespaceDeclaration("x14", SLConstants.NamespaceX14); X14.ConditionalFormattings cfs = new X14.ConditionalFormattings(); foreach (SLConditionalFormatting2010 cfr2010 in slws.ConditionalFormattings2010) { cfs.Append(cfr2010.ToConditionalFormatting()); } wsext.Append(cfs); wsextlist.Append(wsext); } if (slws.SparklineGroups.Count > 0) { // this is important! Apparently extensions are tied to a URI that Microsoft uses. wsext = new WorksheetExtension() { Uri = SLConstants.SparklineExtensionUri }; wsext.AddNamespaceDeclaration("x14", SLConstants.NamespaceX14); X14.SparklineGroups spkgrps = new X14.SparklineGroups(); spkgrps.AddNamespaceDeclaration("xm", SLConstants.NamespaceXm); foreach (SLSparklineGroup spkgrp in slws.SparklineGroups) { spkgrps.Append(spkgrp.ToSparklineGroup()); } wsext.Append(spkgrps); wsextlist.Append(wsext); } // WorksheetExtensionList is the very last element possible. // So we can just append it because everything else is in front. wsp.Worksheet.Append(wsextlist); } #endregion wsp.Worksheet.Save(); // end of writing for existing worksheet } else { // start of writing for new worksheet WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>(); gsSelectedWorksheetRelationshipID = wbp.GetIdOfPart(wsp); foreach (SLSheet s in slwb.Sheets) { if (s.Name.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase)) { s.Id = gsSelectedWorksheetRelationshipID; break; } } if (slws.ForceCustomRowColumnDimensionsSplitting) { slws.ToggleCustomRowColumnDimension(true); } List<OpenXmlAttribute> oxa; OpenXmlWriter oxw = OpenXmlWriter.Create(wsp); oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("xmlns:r", null, SLConstants.NamespaceRelationships)); if (slws.ConditionalFormattings2010.Count > 0 || slws.SparklineGroups.Count > 0) { oxa.Add(new OpenXmlAttribute("xmlns:x14", null, SLConstants.NamespaceX14)); oxa.Add(new OpenXmlAttribute("xmlns:xm", null, SLConstants.NamespaceXm)); oxa.Add(new OpenXmlAttribute("xmlns:mc", null, SLConstants.NamespaceMc)); oxa.Add(new OpenXmlAttribute("xmlns:x14ac", null, SLConstants.NamespaceX14ac)); oxa.Add(new OpenXmlAttribute("mc", "Ignorable", SLConstants.NamespaceMc, "x14ac")); oxw.WriteStartElement(new Worksheet(), oxa); } else { oxw.WriteStartElement(new Worksheet(), oxa); } if (slws.PageSettings.HasSheetProperties) { oxw.WriteElement(slws.PageSettings.SheetProperties.ToSheetProperties()); } oxw.WriteElement(new SheetDimension() { Reference = sDimensionCellRange }); if (slws.SheetViews.Count > 0) { oxw.WriteStartElement(new SheetViews()); foreach (SLSheetView sv in slws.SheetViews) { oxw.WriteElement(sv.ToSheetView()); } oxw.WriteEndElement(); } oxa = new List<OpenXmlAttribute>(); if (slws.SheetFormatProperties.BaseColumnWidth != null && slws.SheetFormatProperties.BaseColumnWidth.Value != 8) { oxa.Add(new OpenXmlAttribute("baseColWidth", null, slws.SheetFormatProperties.BaseColumnWidth.Value.ToString(CultureInfo.InvariantCulture))); } if (slws.SheetFormatProperties.HasDefaultColumnWidth) { oxa.Add(new OpenXmlAttribute("defaultColWidth", null, slws.SheetFormatProperties.DefaultColumnWidth.ToString(CultureInfo.InvariantCulture))); } oxa.Add(new OpenXmlAttribute("defaultRowHeight", null, slws.SheetFormatProperties.DefaultRowHeight.ToString("0.####", CultureInfo.InvariantCulture))); if (slws.SheetFormatProperties.CustomHeight != null && slws.SheetFormatProperties.CustomHeight.Value) { oxa.Add(new OpenXmlAttribute("customHeight", null, "1")); } if (slws.SheetFormatProperties.ZeroHeight != null && slws.SheetFormatProperties.ZeroHeight.Value) { oxa.Add(new OpenXmlAttribute("zeroHeight", null, "1")); } if (slws.SheetFormatProperties.ThickTop != null && slws.SheetFormatProperties.ThickTop.Value) { oxa.Add(new OpenXmlAttribute("thickTop", null, "1")); } if (slws.SheetFormatProperties.ThickBottom != null && slws.SheetFormatProperties.ThickBottom.Value) { oxa.Add(new OpenXmlAttribute("thickBottom", null, "1")); } if (slws.SheetFormatProperties.OutlineLevelRow != null && slws.SheetFormatProperties.OutlineLevelRow.Value > 0) { oxa.Add(new OpenXmlAttribute("outlineLevelRow", null, slws.SheetFormatProperties.OutlineLevelRow.Value.ToString(CultureInfo.InvariantCulture))); } if (slws.SheetFormatProperties.OutlineLevelColumn != null && slws.SheetFormatProperties.OutlineLevelColumn.Value > 0) { oxa.Add(new OpenXmlAttribute("outlineLevelCol", null, slws.SheetFormatProperties.OutlineLevelColumn.Value.ToString(CultureInfo.InvariantCulture))); } oxw.WriteStartElement(new SheetFormatProperties(), oxa); oxw.WriteEndElement(); #region Filling Columns if (slws.ColumnProperties.Count > 0) { oxw.WriteStartElement(new Columns()); int iPreviousColumnIndex = listColumnIndex[0]; int iCurrentColumnIndex = iPreviousColumnIndex; string sCollectiveColumnData = string.Empty; string sCurrentColumnData = string.Empty; int colmin, colmax; colmin = colmax = iCurrentColumnIndex; cp = slws.ColumnProperties[iCurrentColumnIndex]; sCollectiveColumnData = cp.ToHash(); oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("min", null, colmin.ToString(CultureInfo.InvariantCulture))); // max is left to the end because we're calculating it //oxa.Add(new OpenXmlAttribute("max", null, colmax.ToString(CultureInfo.InvariantCulture))); if (cp.HasWidth) { oxa.Add(new OpenXmlAttribute("width", null, cp.Width.ToString(CultureInfo.InvariantCulture))); oxa.Add(new OpenXmlAttribute("customWidth", null, "1")); } else { oxa.Add(new OpenXmlAttribute("width", null, slws.SheetFormatProperties.DefaultColumnWidth.ToString(CultureInfo.InvariantCulture))); } if (cp.StyleIndex > 0) oxa.Add(new OpenXmlAttribute("style", null, cp.StyleIndex.ToString(CultureInfo.InvariantCulture))); if (cp.Hidden != false) oxa.Add(new OpenXmlAttribute("hidden", null, "1")); if (cp.BestFit != false) oxa.Add(new OpenXmlAttribute("bestFit", null, "1")); if (cp.Phonetic != false) oxa.Add(new OpenXmlAttribute("phonetic", null, "1")); if (cp.OutlineLevel > 0) oxa.Add(new OpenXmlAttribute("outlineLevel", null, cp.OutlineLevel.ToString(CultureInfo.InvariantCulture))); if (cp.Collapsed != false) oxa.Add(new OpenXmlAttribute("collapsed", null, "1")); for (i = 1; i < listColumnIndex.Count; ++i) { iPreviousColumnIndex = iCurrentColumnIndex; iCurrentColumnIndex = listColumnIndex[i]; cp = slws.ColumnProperties[iCurrentColumnIndex]; sCurrentColumnData = cp.ToHash(); if ((iCurrentColumnIndex != (iPreviousColumnIndex + 1)) || (sCollectiveColumnData != sCurrentColumnData)) { oxa.Add(new OpenXmlAttribute("max", null, colmax.ToString(CultureInfo.InvariantCulture))); oxw.WriteStartElement(new Column(), oxa); oxw.WriteEndElement(); colmin = iCurrentColumnIndex; colmax = iCurrentColumnIndex; sCollectiveColumnData = sCurrentColumnData; oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("min", null, colmin.ToString(CultureInfo.InvariantCulture))); if (cp.HasWidth) { oxa.Add(new OpenXmlAttribute("width", null, cp.Width.ToString(CultureInfo.InvariantCulture))); oxa.Add(new OpenXmlAttribute("customWidth", null, "1")); } else { oxa.Add(new OpenXmlAttribute("width", null, slws.SheetFormatProperties.DefaultColumnWidth.ToString(CultureInfo.InvariantCulture))); } if (cp.StyleIndex > 0) oxa.Add(new OpenXmlAttribute("style", null, cp.StyleIndex.ToString(CultureInfo.InvariantCulture))); if (cp.Hidden != false) oxa.Add(new OpenXmlAttribute("hidden", null, "1")); if (cp.BestFit != false) oxa.Add(new OpenXmlAttribute("bestFit", null, "1")); if (cp.Phonetic != false) oxa.Add(new OpenXmlAttribute("phonetic", null, "1")); if (cp.OutlineLevel > 0) oxa.Add(new OpenXmlAttribute("outlineLevel", null, cp.OutlineLevel.ToString(CultureInfo.InvariantCulture))); if (cp.Collapsed != false) oxa.Add(new OpenXmlAttribute("collapsed", null, "1")); } else { colmax = iCurrentColumnIndex; } } // there's always a "leftover" column oxa.Add(new OpenXmlAttribute("max", null, colmax.ToString(CultureInfo.InvariantCulture))); oxw.WriteStartElement(new Column(), oxa); oxw.WriteEndElement(); oxw.WriteEndElement(); } #endregion oxw.WriteStartElement(new SheetData()); iCellDataKey = 0; for (iRowKey = 0; iRowKey < listRowIndex.Count; ++iRowKey) { iRowIndex = listRowIndex[iRowKey]; oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("r", null, iRowIndex.ToString(CultureInfo.InvariantCulture))); if (slws.RowProperties.ContainsKey(iRowIndex)) { rp = slws.RowProperties[iRowIndex]; if (rp.StyleIndex > 0) { oxa.Add(new OpenXmlAttribute("s", null, rp.StyleIndex.ToString(CultureInfo.InvariantCulture))); oxa.Add(new OpenXmlAttribute("customFormat", null, "1")); } if (rp.HasHeight) { oxa.Add(new OpenXmlAttribute("ht", null, rp.Height.ToString(CultureInfo.InvariantCulture))); } if (rp.Hidden != false) { oxa.Add(new OpenXmlAttribute("hidden", null, "1")); } if (rp.CustomHeight) { oxa.Add(new OpenXmlAttribute("customHeight", null, "1")); } if (rp.OutlineLevel > 0) { oxa.Add(new OpenXmlAttribute("outlineLevel", null, rp.OutlineLevel.ToString(CultureInfo.InvariantCulture))); } if (rp.Collapsed != false) { oxa.Add(new OpenXmlAttribute("collapsed", null, "1")); } if (rp.ThickTop != false) { oxa.Add(new OpenXmlAttribute("thickTop", null, "1")); } if (rp.ThickBottom != false) { oxa.Add(new OpenXmlAttribute("thickBot", null, "1")); } if (rp.ShowPhonetic != false) { oxa.Add(new OpenXmlAttribute("ph", null, "1")); } } oxw.WriteStartElement(new Row(), oxa); while (iCellDataKey < listCellRefKeys.Count) { pt = listCellRefKeys[iCellDataKey]; if (pt.RowIndex == iRowIndex) { c = slws.Cells[pt]; oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("r", null, SLTool.ToCellReference(pt.RowIndex, pt.ColumnIndex))); if (c.StyleIndex > 0) { oxa.Add(new OpenXmlAttribute("s", null, c.StyleIndex.ToString(CultureInfo.InvariantCulture))); } // number type is default switch (c.DataType) { case CellValues.Boolean: oxa.Add(new OpenXmlAttribute("t", null, "b")); break; case CellValues.Date: oxa.Add(new OpenXmlAttribute("t", null, "d")); break; case CellValues.Error: oxa.Add(new OpenXmlAttribute("t", null, "e")); break; case CellValues.InlineString: oxa.Add(new OpenXmlAttribute("t", null, "inlineStr")); break; case CellValues.SharedString: oxa.Add(new OpenXmlAttribute("t", null, "s")); break; case CellValues.String: oxa.Add(new OpenXmlAttribute("t", null, "str")); break; } if (c.CellMetaIndex > 0) { oxa.Add(new OpenXmlAttribute("cm", null, c.CellMetaIndex.ToString(CultureInfo.InvariantCulture))); } if (c.ValueMetaIndex > 0) { oxa.Add(new OpenXmlAttribute("vm", null, c.ValueMetaIndex.ToString(CultureInfo.InvariantCulture))); } if (c.ShowPhonetic != false) { oxa.Add(new OpenXmlAttribute("ph", null, "1")); } oxw.WriteStartElement(new Cell(), oxa); if (c.CellFormula != null) { oxw.WriteElement(c.CellFormula.ToCellFormula()); } if (c.CellText != null) { if (c.CellText.Length > 0) { if (c.ToPreserveSpace) { oxw.WriteElement(new CellValue(c.CellText) { Space = SpaceProcessingModeValues.Preserve }); } else { oxw.WriteElement(new CellValue(c.CellText)); } } } else { if (c.DataType == CellValues.Number) { oxw.WriteElement(new CellValue(c.NumericValue.ToString(CultureInfo.InvariantCulture))); } else if (c.DataType == CellValues.SharedString) { oxw.WriteElement(new CellValue(c.NumericValue.ToString("f0", CultureInfo.InvariantCulture))); } else if (c.DataType == CellValues.Boolean) { if (c.NumericValue > 0.5) oxw.WriteElement(new CellValue("1")); else oxw.WriteElement(new CellValue("0")); } } oxw.WriteEndElement(); ++iCellDataKey; } else { break; } } oxw.WriteEndElement(); } oxw.WriteEndElement(); #region Sheet protection if (slws.HasSheetProtection) { oxw.WriteElement(slws.SheetProtection.ToSheetProtection()); } #endregion #region AutoFilter if (slws.HasAutoFilter) { oxw.WriteElement(slws.AutoFilter.ToAutoFilter()); } #endregion #region Filling merge cells if (slws.MergeCells.Count > 0) { oxw.WriteStartElement(new MergeCells() { Count = (uint)slws.MergeCells.Count }); for (i = 0; i < slws.MergeCells.Count; ++i) { oxw.WriteElement(slws.MergeCells[i].ToMergeCell()); } oxw.WriteEndElement(); } #endregion #region Conditional Formatting if (slws.ConditionalFormattings.Count > 0) { for (i = 0; i < slws.ConditionalFormattings.Count; ++i) { oxw.WriteElement(slws.ConditionalFormattings[i].ToConditionalFormatting()); } } #endregion #region DataValidations if (slws.DataValidations.Count > 0) { DataValidations dvs = new DataValidations(); if (slws.DataValidationDisablePrompts) dvs.DisablePrompts = slws.DataValidationDisablePrompts; if (slws.DataValidationXWindow != null) dvs.XWindow = slws.DataValidationXWindow.Value; if (slws.DataValidationYWindow != null) dvs.YWindow = slws.DataValidationYWindow.Value; dvs.Count = (uint)slws.DataValidations.Count; foreach (SLDataValidation dv in slws.DataValidations) { dvs.Append(dv.ToDataValidation()); } oxw.WriteElement(dvs); } #endregion #region Hyperlinks if (slws.Hyperlinks.Count > 0) { Hyperlinks hls = new Hyperlinks(); HyperlinkRelationship hlrel; foreach (SLHyperlink hl in slws.Hyperlinks) { if (hl.IsExternal && hl.IsNew) { hlrel = wsp.AddHyperlinkRelationship(new Uri(hl.HyperlinkUri, hl.HyperlinkUriKind), true); hl.Id = hlrel.Id; } hls.Append(hl.ToHyperlink()); } oxw.WriteElement(hls); } #endregion #region PrintOptions if (slws.PageSettings.HasPrintOptions) { oxw.WriteElement(slws.PageSettings.ExportPrintOptions()); } #endregion #region PageMargins if (slws.PageSettings.HasPageMargins) { oxw.WriteElement(slws.PageSettings.ExportPageMargins()); } #endregion #region PageSetup if (slws.PageSettings.HasPageSetup) { oxw.WriteElement(slws.PageSettings.ExportPageSetup()); } #endregion #region HeaderFooter if (slws.PageSettings.HasHeaderFooter) { oxw.WriteElement(slws.PageSettings.ExportHeaderFooter()); } #endregion #region RowBreaks if (slws.RowBreaks.Count > 0) { List<int> bkkeys = slws.RowBreaks.Keys.ToList<int>(); bkkeys.Sort(); // if it's a new worksheet, then all breaks are considered manual oxw.WriteStartElement(new RowBreaks() { Count = (uint)slws.RowBreaks.Count, ManualBreakCount = (uint)slws.RowBreaks.Count }); foreach (int bkindex in bkkeys) { oxw.WriteElement(slws.RowBreaks[bkindex].ToBreak()); } oxw.WriteEndElement(); } #endregion #region ColumnBreaks if (slws.ColumnBreaks.Count > 0) { List<int> bkkeys = slws.ColumnBreaks.Keys.ToList<int>(); bkkeys.Sort(); // if it's a new worksheet, then all breaks are considered manual oxw.WriteStartElement(new ColumnBreaks() { Count = (uint)slws.ColumnBreaks.Count, ManualBreakCount = (uint)slws.ColumnBreaks.Count }); foreach (int bkindex in bkkeys) { oxw.WriteElement(slws.ColumnBreaks[bkindex].ToBreak()); } oxw.WriteEndElement(); } #endregion #region Drawing // these are "new" charts and pictures added if (slws.Charts.Count > 0 || slws.Pictures.Count > 0) { DrawingsPart dp = wsp.AddNewPart<DrawingsPart>(); dp.WorksheetDrawing = new Xdr.WorksheetDrawing(); dp.WorksheetDrawing.AddNamespaceDeclaration("xdr", SLConstants.NamespaceXdr); dp.WorksheetDrawing.AddNamespaceDeclaration("a", SLConstants.NamespaceA); oxw.WriteElement(new DocumentFormat.OpenXml.Spreadsheet.Drawing() { Id = wsp.GetIdOfPart(dp) }); WriteImageParts(dp); } #endregion #region LegacyDrawing // these are "new" comments added if (slws.Comments.Count > 0) { WorksheetCommentsPart wcp = wsp.AddNewPart<WorksheetCommentsPart>(); VmlDrawingPart vdp = wsp.AddNewPart<VmlDrawingPart>(); WriteCommentPart(wcp, vdp); oxw.WriteElement(new LegacyDrawing() { Id = wsp.GetIdOfPart(vdp) }); } #endregion #region Picture if (slws.BackgroundPictureDataIsInFile != null) { ImagePart imgp = wsp.AddImagePart(slws.BackgroundPictureImagePartType); if (slws.BackgroundPictureDataIsInFile.Value) { using (FileStream fs = new FileStream(slws.BackgroundPictureFileName, FileMode.Open)) { imgp.FeedData(fs); } } else { using (MemoryStream ms = new MemoryStream(slws.BackgroundPictureByteData)) { imgp.FeedData(ms); } } oxw.WriteElement(new Picture() { Id = wsp.GetIdOfPart(imgp) }); } #endregion if (slws.Tables.Count > 0) { // If it's a new worksheet, ALL tables are new tables... oxw.WriteStartElement(new TableParts() { Count = (uint)slws.Tables.Count }); TableDefinitionPart tdp; foreach (SLTable t in slws.Tables) { tdp = wsp.AddNewPart<TableDefinitionPart>(); tdp.Table = t.ToTable(); oxw.WriteElement(new TablePart() { Id = wsp.GetIdOfPart(tdp) }); } oxw.WriteEndElement(); } #region 2010 Conditional formatting, Sparklines and possibly other extensions slws.RefreshSparklineGroups(); if (slws.ConditionalFormattings2010.Count > 0 || slws.SparklineGroups.Count > 0) { oxw.WriteStartElement(new WorksheetExtensionList()); if (slws.ConditionalFormattings2010.Count > 0) { oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("xmlns:x14", null, SLConstants.NamespaceX14)); // this is important! Apparently extensions are tied to a URI that Microsoft uses. oxa.Add(new OpenXmlAttribute("uri", null, SLConstants.ConditionalFormattingExtensionUri)); oxw.WriteStartElement(new WorksheetExtension(), oxa); oxw.WriteStartElement(new X14.ConditionalFormattings()); foreach (SLConditionalFormatting2010 cf2010 in slws.ConditionalFormattings2010) { oxw.WriteElement(cf2010.ToConditionalFormatting()); } oxw.WriteEndElement(); oxw.WriteEndElement(); } if (slws.SparklineGroups.Count > 0) { oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("xmlns:x14", null, SLConstants.NamespaceX14)); // this is important! Apparently extensions are tied to a URI that Microsoft uses. oxa.Add(new OpenXmlAttribute("uri", null, SLConstants.SparklineExtensionUri)); oxw.WriteStartElement(new WorksheetExtension(), oxa); oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("xmlns:xm", null, SLConstants.NamespaceXm)); oxw.WriteStartElement(new X14.SparklineGroups(), oxa); foreach (SLSparklineGroup spkgrp in slws.SparklineGroups) { oxw.WriteElement(spkgrp.ToSparklineGroup()); } oxw.WriteEndElement(); oxw.WriteEndElement(); } oxw.WriteEndElement(); } #endregion oxw.WriteEndElement(); oxw.Dispose(); // end of writing for new worksheet } }
private void CreateResourcesWorksheet(Dictionary <string, object[]> fieldDictionary, DataTable dataTable, WorkbookPart workbookPart, Dictionary <string, int> sharedStringIndices) { var worksheetPart = workbookPart.AddNewPart <WorksheetPart>("rId1"); var worksheet = new Worksheet(new SheetProperties { CodeName = "Sheet1" }, new SheetDimension { Reference = string.Format("A1:{0}{1}", GetColId(fieldDictionary.Count - 1), dataTable.Rows.Count + 2) }, new SheetViews( new SheetView( new Pane { VerticalSplit = 1D, TopLeftCell = "A3", ActivePane = PaneValues.BottomLeft, State = PaneStateValues.Frozen }, new Selection { ActiveCell = "A2", SequenceOfReferences = new ListValue <StringValue> { InnerText = "A2" } }, new Selection { Pane = PaneValues.BottomLeft }) { TabSelected = true, TopLeftCell = "A2", WorkbookViewId = (UInt32Value)0U })) { MCAttributes = new MarkupCompatibilityAttributes { Ignorable = "x14ac" } }; worksheet.AddNamespaceDeclaration("r", R_SCHEMA); worksheet.AddNamespaceDeclaration("mc", MC_SCHEMA); worksheet.AddNamespaceDeclaration("x14ac", X14_AC_SCHEMA); var columns = new Columns(); UInt32Value colIndex = 1U; foreach (var pair in fieldDictionary) { var column = new Column { Min = colIndex, Max = colIndex, Width = 9.140625D, Style = 1U, BestFit = true, CustomWidth = true }; if (pair.Key.Equals("ID") || pair.Key.Equals("Title")) { column.Style = 5U; } if (pair.Key.Equals("SharePointAccount") && new Act(_spWeb).IsOnline) { column.Hidden = true; } columns.AppendChild(column); colIndex++; } columns.AppendChild(new Column { Min = colIndex, Max = 16384U, Width = 9.140625D, Style = 1U }); var sheetData = new SheetData(); for (UInt32Value i = 1; i <= dataTable.Rows.Count + 2; i++) { var row = new Row { RowIndex = i }; if (i == 1) { row.Hidden = true; } else if (i == 2) { row.StyleIndex = 4U; row.CustomFormat = true; } var rowId = (int)(i - 1); for (int j = 0; j < fieldDictionary.Count; j++) { var cellValue = new CellValue(); KeyValuePair <string, object[]> pair = fieldDictionary.ElementAt(j); object oValue = null; string value = string.Empty; if (rowId > 1) { oValue = dataTable.Rows[rowId - 2][pair.Key]; if (pair.Key.Equals("ResourceLevel")) { foreach (var l in ((IList <string>)pair.Value[2]) .Select(level => level.Split('|')) .Where(l => l[0].Equals(oValue.ToString()))) { oValue = l[1]; break; } } value = oValue.ToString(); } switch (rowId) { case 0: cellValue.Text = sharedStringIndices[pair.Key].ToString(CultureInfo.InvariantCulture); break; case 1: cellValue.Text = sharedStringIndices[pair.Value[0].ToString()].ToString(CultureInfo.InvariantCulture); break; default: cellValue.Text = sharedStringIndices[value].ToString(CultureInfo.InvariantCulture); break; } var cell = new Cell { CellReference = GetColId(j) + i, StyleIndex = 1U }; if (rowId > 1) { switch ((SPFieldType)pair.Value[1]) { case SPFieldType.Counter: case SPFieldType.Integer: case SPFieldType.Number: cellValue.Text = value; break; case SPFieldType.DateTime: cell.StyleIndex = 3U; cellValue.Text = oValue != null && oValue != DBNull.Value ? ((DateTime)oValue).ToOADate() .ToString(CultureInfo.InvariantCulture) : null; break; case SPFieldType.Currency: cell.StyleIndex = 2U; cellValue.Text = value; break; case SPFieldType.Boolean: cell.DataType = CellValues.Boolean; cellValue.Text = value; break; default: cell.DataType = CellValues.SharedString; break; } switch (pair.Key) { case "ID": case "Title": case "Generic": case "SharePointAccount": case "Email": cell.StyleIndex = 5U; break; } } else { cell.DataType = CellValues.SharedString; } if (i == 2) { cell.StyleIndex = 4U; } cell.AppendChild(cellValue); row.AppendChild(cell); } sheetData.AppendChild(row); } var worksheetExtensionList = new WorksheetExtensionList(); var worksheetExtension = new WorksheetExtension { Uri = "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" }; worksheetExtension.AddNamespaceDeclaration("x14", X14_SCHEMA); var dataValidations = new X14.DataValidations { Count = 0U }; dataValidations.AddNamespaceDeclaration("xm", XM_SCHEMA); for (int i = 0; i < fieldDictionary.Count; i++) { string colId = GetColId(i); KeyValuePair <string, object[]> pair = fieldDictionary.ElementAt(i); var spFieldType = (SPFieldType)pair.Value[1]; if (spFieldType != SPFieldType.Boolean && spFieldType != SPFieldType.Lookup && spFieldType != SPFieldType.User && spFieldType != SPFieldType.Choice) { continue; } if (pair.Key.Equals("SharePointAccount")) { continue; } if ((bool)pair.Value[3]) { continue; } dataValidations.Count++; var dataValidation = new X14.DataValidation { Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true }; string formulaValue = string.Empty; switch (spFieldType) { case SPFieldType.Boolean: formulaValue = "BValues!$A$1:$A$2"; break; case SPFieldType.Lookup: case SPFieldType.Choice: int count = ((List <string>)pair.Value[2]).Count(); formulaValue = pair.Key.Replace("_x0020_", "__") + "Values!$A$1" + (count > 1 ? ":$A$" + count : string.Empty); break; case SPFieldType.User: formulaValue = "DNValues!$D:$D"; break; } var dataValidationForumla = new X14.DataValidationForumla1(); var formula = new Formula { Text = formulaValue }; dataValidationForumla.AppendChild(formula); var refSeq = new ReferenceSequence { Text = string.Format("{0}3:{0}1048576", colId) }; dataValidation.Append(dataValidationForumla, refSeq); dataValidations.AppendChild(dataValidation); } worksheetExtension.AppendChild(dataValidations); worksheetExtensionList.AppendChild(worksheetExtension); worksheet.Append(columns, sheetData, worksheetExtensionList); worksheetPart.Worksheet = worksheet; }