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();
}
