private void GenerateWorkbookPartContent(WorkbookPart workbookPart, SaveContext context) { if (workbookPart.Workbook == null) workbookPart.Workbook = new Workbook(); var workbook = workbookPart.Workbook; if ( !workbook.NamespaceDeclarations.Contains(new KeyValuePair<string, string>("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) { workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); } #region WorkbookProperties if (workbook.WorkbookProperties == null) workbook.WorkbookProperties = new WorkbookProperties(); if (workbook.WorkbookProperties.CodeName == null) workbook.WorkbookProperties.CodeName = "ThisWorkbook"; if (Use1904DateSystem) workbook.WorkbookProperties.Date1904 = true; #endregion if (workbook.BookViews == null) workbook.BookViews = new BookViews(); if (workbook.Sheets == null) workbook.Sheets = new Sheets(); var worksheets = WorksheetsInternal; workbook.Sheets.Elements<Sheet>().Where(s => worksheets.Deleted.Contains(s.Id)).ToList().ForEach( s => s.Remove()); foreach (var sheet in workbook.Sheets.Elements<Sheet>()) { var sheetId = (Int32)sheet.SheetId.Value; if (WorksheetsInternal.All<XLWorksheet>(w => w.SheetId != sheetId)) continue; var wks = WorksheetsInternal.Single<XLWorksheet>(w => w.SheetId == sheetId); wks.RelId = sheet.Id; sheet.Name = wks.Name; } foreach (var xlSheet in WorksheetsInternal.Cast<XLWorksheet>().Where(s => s.SheetId == 0).OrderBy(w => w.Position)) { var rId = context.RelIdGenerator.GetNext(RelType.Workbook); while (WorksheetsInternal.Cast<XLWorksheet>().Any(w => w.SheetId == Int32.Parse(rId.Substring(3)))) rId = context.RelIdGenerator.GetNext(RelType.Workbook); xlSheet.SheetId = Int32.Parse(rId.Substring(3)); xlSheet.RelId = rId; var newSheet = new Sheet { Name = xlSheet.Name, Id = rId, SheetId = (UInt32)xlSheet.SheetId }; workbook.Sheets.AppendChild(newSheet); } var sheetElements = from sheet in workbook.Sheets.Elements<Sheet>() join worksheet in ((IEnumerable<XLWorksheet>)WorksheetsInternal) on sheet.Id.Value equals worksheet.RelId orderby worksheet.Position select sheet; UInt32 firstSheetVisible = 0; var activeTab = (from us in UnsupportedSheets where us.IsActive select (UInt32)us.Position - 1).FirstOrDefault(); var foundVisible = false; var totalSheets = sheetElements.Count() + UnsupportedSheets.Count; for (var p = 1; p <= totalSheets; p++) { if (UnsupportedSheets.All(us => us.Position != p)) { 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(); if (foundVisible) continue; if (sheet.State == null || sheet.State == SheetStateValues.Visible) foundVisible = true; else firstSheetVisible++; } else { var sheetId = UnsupportedSheets.First(us => us.Position == p).SheetId; var sheet = workbook.Sheets.Elements<Sheet>().First(s => s.SheetId == sheetId); workbook.Sheets.RemoveChild(sheet); workbook.Sheets.AppendChild(sheet); } } var workbookView = workbook.BookViews.Elements<WorkbookView>().FirstOrDefault(); if (activeTab == 0) { activeTab = firstSheetVisible; foreach (var ws in worksheets) { if (!ws.TabActive) continue; activeTab = (UInt32)(ws.Position - 1); break; } } if (workbookView == null) { workbookView = new WorkbookView {ActiveTab = activeTab, FirstSheet = firstSheetVisible}; workbook.BookViews.AppendChild(workbookView); } else { workbookView.ActiveTab = activeTab; workbookView.FirstSheet = firstSheetVisible; } var definedNames = new DefinedNames(); foreach (var worksheet in WorksheetsInternal) { var wsSheetId = (UInt32)worksheet.SheetId; UInt32 sheetId = 0; foreach (var s in workbook.Sheets.Elements<Sheet>().TakeWhile(s => s.SheetId != wsSheetId)) { sheetId++; } if (worksheet.PageSetup.PrintAreas.Any()) { var definedName = new DefinedName {Name = "_xlnm.Print_Area", LocalSheetId = sheetId}; var worksheetName = worksheet.Name; var definedNameText = worksheet.PageSetup.PrintAreas.Aggregate(String.Empty, (current, printArea) => current + ("'" + worksheetName + "'!" + printArea.RangeAddress. FirstAddress.ToStringFixed( XLReferenceStyle.A1) + ":" + printArea.RangeAddress. LastAddress.ToStringFixed( XLReferenceStyle.A1) + ",")); definedName.Text = definedNameText.Substring(0, definedNameText.Length - 1); definedNames.AppendChild(definedName); } if (worksheet.AutoFilter.Enabled) { var definedName = new DefinedName { Name = "_xlnm._FilterDatabase", LocalSheetId = sheetId, Text = "'" + worksheet.Name + "'!" + worksheet.AutoFilter.Range.RangeAddress.FirstAddress.ToStringFixed( XLReferenceStyle.A1) + ":" + worksheet.AutoFilter.Range.RangeAddress.LastAddress.ToStringFixed( XLReferenceStyle.A1), Hidden = BooleanValue.FromBoolean(true) }; definedNames.AppendChild(definedName); } foreach (var nr in worksheet.NamedRanges.Where(n => n.Name != "_xlnm._FilterDatabase")) { var definedName = new DefinedName { Name = nr.Name, LocalSheetId = sheetId, Text = nr.ToString() }; if (!XLHelper.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; definedNames.AppendChild(definedName); } var definedNameTextRow = String.Empty; var definedNameTextColumn = String.Empty; if (worksheet.PageSetup.FirstRowToRepeatAtTop > 0) { definedNameTextRow = "'" + worksheet.Name + "'!" + worksheet.PageSetup.FirstRowToRepeatAtTop + ":" + worksheet.PageSetup.LastRowToRepeatAtTop; } if (worksheet.PageSetup.FirstColumnToRepeatAtLeft > 0) { var minColumn = worksheet.PageSetup.FirstColumnToRepeatAtLeft; var maxColumn = worksheet.PageSetup.LastColumnToRepeatAtLeft; definedNameTextColumn = "'" + worksheet.Name + "'!" + XLHelper.GetColumnLetterFromNumber(minColumn) + ":" + XLHelper.GetColumnLetterFromNumber(maxColumn); } string titles; if (definedNameTextColumn.Length > 0) { titles = definedNameTextColumn; if (definedNameTextRow.Length > 0) titles += "," + definedNameTextRow; } else titles = definedNameTextRow; if (titles.Length <= 0) continue; var definedName2 = new DefinedName { Name = "_xlnm.Print_Titles", LocalSheetId = sheetId, Text = titles }; definedNames.AppendChild(definedName2); } foreach (var nr in NamedRanges) { var definedName = new DefinedName { Name = nr.Name, Text = nr.ToString() }; if (!XLHelper.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; definedNames.AppendChild(definedName); } workbook.DefinedNames = definedNames; if (workbook.CalculationProperties == null) workbook.CalculationProperties = new CalculationProperties {CalculationId = 125725U}; if (CalculateMode == XLCalculateMode.Default) workbook.CalculationProperties.CalculationMode = null; else workbook.CalculationProperties.CalculationMode = CalculateMode.ToOpenXml(); if (ReferenceStyle == XLReferenceStyle.Default) workbook.CalculationProperties.ReferenceMode = null; else workbook.CalculationProperties.ReferenceMode = ReferenceStyle.ToOpenXml(); }