public void InsertVerticalPageBreak(int columnIndex) { columnIndex++; ColumnBreaks cb = WorksheetPart.Worksheet.GetFirstChild <ColumnBreaks>(); if (cb == null) { cb = new ColumnBreaks(); cb.ManualBreakCount = (UInt32Value)0; cb.Count = (UInt32Value)0; WorksheetPart.Worksheet.Append(cb); } Break br = new Break() { Id = (UInt32Value)(uint)columnIndex, Max = (UInt32Value)1048575U, ManualPageBreak = true }; cb.Append(br); cb.ManualBreakCount++; cb.Count++; }
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 } }