diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index 1c3e8aa..377f058 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -165,8 +165,8 @@ #endregion - internal readonly Dictionary UnsupportedSheets = - new Dictionary(); + internal readonly List UnsupportedSheets = + new List(); private readonly Dictionary _stylesById = new Dictionary(); private readonly Dictionary _stylesByStyle = new Dictionary(); @@ -600,6 +600,7 @@ { public Boolean IsActive; public UInt32 SheetId; + public Int32 Position; } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index b05c03c..553f52d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -121,7 +121,7 @@ if (wsPart == null) { - UnsupportedSheets.Add(position, new UnsupportedSheet {SheetId = dSheet.SheetId.Value}); + UnsupportedSheets.Add(new UnsupportedSheet {SheetId = dSheet.SheetId.Value, Position = position}); continue; } @@ -341,8 +341,9 @@ var workbookView = (WorkbookView) workbook.BookViews.FirstOrDefault(); if (workbookView != null && workbookView.ActiveTab != null) { - UnsupportedSheet unsupportedSheet; - if (UnsupportedSheets.TryGetValue((Int32)(workbookView.ActiveTab.Value + 1), out unsupportedSheet)) + UnsupportedSheet unsupportedSheet = + UnsupportedSheets.FirstOrDefault(us => us.Position == (Int32)(workbookView.ActiveTab.Value + 1)); + if (unsupportedSheet != null) unsupportedSheet.IsActive = true; else { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 053e381..b0ef824 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -451,18 +451,16 @@ if (!WorksheetsInternal.Any(w => w.SheetId == sheetId)) continue; - var wks = - WorksheetsInternal.Where(w => w.SheetId == sheetId).Single(); + var wks = WorksheetsInternal.Where(w => w.SheetId == sheetId).Single(); wks.RelId = sheet.Id; sheet.Name = wks.Name; } - foreach ( - XLWorksheet xlSheet in - WorksheetsInternal.Cast().Where(w => w.SheetId == 0).OrderBy(w => w.Position)) + foreach (XLWorksheet xlSheet in + WorksheetsInternal.Cast().Where(s => s.SheetId == 0).OrderBy(w => w.Position)) { String rId = context.RelIdGenerator.GetNext(RelType.Workbook); - //Int32 rIdSub = Int32.Parse(rId.Substring(3)); + while (WorksheetsInternal.Cast().Any(w => w.SheetId == Int32.Parse(rId.Substring(3)))) rId = context.RelIdGenerator.GetNext(RelType.Workbook); @@ -484,15 +482,17 @@ select sheet; UInt32 firstSheetVisible = 0; - UInt32 activeTab = (from us in UnsupportedSheets where us.Value.IsActive select (UInt32)us.Key - 1).FirstOrDefault(); + UInt32 activeTab = (from us in UnsupportedSheets where us.IsActive select (UInt32)us.Position - 1).FirstOrDefault(); Boolean foundVisible = false; - //Int32 position = 0; + Int32 totalSheets = sheetElements.Count() + UnsupportedSheets.Count; for (Int32 p = 1; p <= totalSheets; p++) { - if (!UnsupportedSheets.ContainsKey(p)) + if (!UnsupportedSheets.Any(us =>us.Position == p)) { - var sheet = sheetElements.ElementAt(p - UnsupportedSheets.Keys.Count(n => n <= p) - 1); + var sheet = sheetElements.ElementAt(p - UnsupportedSheets.Count(us => us.Position <= p) - 1); + workbook.Sheets.RemoveChild(sheet); + workbook.Sheets.AppendChild(sheet); var xlSheet = Worksheet(sheet.Name); if (xlSheet.Visibility != XLWorksheetVisibility.Visible) sheet.State = xlSheet.Visibility.ToOpenXml(); @@ -504,6 +504,13 @@ else firstSheetVisible++; } + else + { + var sheetId = UnsupportedSheets.First(us=>us.Position == p).SheetId; + var sheet = workbook.Sheets.Elements().First(s => s.SheetId == sheetId); + workbook.Sheets.RemoveChild(sheet); + workbook.Sheets.AppendChild(sheet); + } } var workbookView = workbook.BookViews.Elements().FirstOrDefault(); @@ -633,32 +640,6 @@ } workbook.DefinedNames = definedNames; - //if (workbook.DefinedNames == null) - // workbook.DefinedNames = new DefinedNames(); - - //foreach (DefinedName dn in definedNames) - //{ - // String dnName = dn.Name.Value; - // var dnLocalSheetId = dn.LocalSheetId; - // var existingDefinedName = workbook.DefinedNames - // .Elements() - // .FirstOrDefault(d => - // String.Compare(d.Name.Value, dnName, true) == 0 - // && ( - // (d.LocalSheetId != null && dnLocalSheetId != null && - // d.LocalSheetId.InnerText == dnLocalSheetId.InnerText) - // || d.LocalSheetId == null - // || dnLocalSheetId == null) - // ); - // if (existingDefinedName != null) - // { - // existingDefinedName.Text = dn.Text; - // existingDefinedName.LocalSheetId = dn.LocalSheetId; - // existingDefinedName.Comment = dn.Comment; - // } - // else - // workbook.DefinedNames.AppendChild(dn.CloneNode(true)); - //} if (workbook.CalculationProperties == null) workbook.CalculationProperties = new CalculationProperties {CalculationId = 125725U}; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs index 6b4da79..ebd3ab7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs @@ -11,7 +11,7 @@ { #region Constructor - private readonly XLWorkbook m_workbook; + private readonly XLWorkbook _workbook; private readonly Dictionary _worksheets = new Dictionary(); #endregion @@ -22,7 +22,7 @@ public XLWorksheets(XLWorkbook workbook) { - m_workbook = workbook; + _workbook = workbook; } #endregion @@ -87,16 +87,17 @@ public IXLWorksheet Add(String sheetName) { - var sheet = new XLWorksheet(sheetName, m_workbook); + var sheet = new XLWorksheet(sheetName, _workbook); _worksheets.Add(sheetName, sheet); - sheet._position = _worksheets.Count; + sheet._position = _worksheets.Count + _workbook.UnsupportedSheets.Count; return sheet; } public IXLWorksheet Add(String sheetName, Int32 position) { _worksheets.Values.Where(w => w._position >= position).ForEach(w => w._position += 1); - var sheet = new XLWorksheet(sheetName, m_workbook); + _workbook.UnsupportedSheets.Where(w => w.Position >= position).ForEach(w => w.Position += 1); + var sheet = new XLWorksheet(sheetName, _workbook); _worksheets.Add(sheetName, sheet); sheet._position = position; return sheet; @@ -122,7 +123,8 @@ Deleted.Add(ws.RelId); _worksheets.RemoveAll(w => w.Position == position); - _worksheets.Values.Where(w => w.Position > position).ForEach(w => (w)._position -= 1); + _worksheets.Values.Where(w => w.Position > position).ForEach(w => w._position -= 1); + _workbook.UnsupportedSheets.Where(w => w.Position > position).ForEach(w => w.Position -= 1); } IEnumerator IEnumerable.GetEnumerator() diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/PageSetup/HeaderFooters.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/PageSetup/HeaderFooters.xlsx index 6b5ca8b..a956ab6 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/PageSetup/HeaderFooters.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/PageSetup/HeaderFooters.xlsx Binary files differ