diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index d852a94..f8878fc 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -483,8 +483,8 @@ // cellsToInsert.Add(newKey, newCell); // cellsToDelete.Add(c.Address); //} - cellsToDelete.ForEach(c => (Worksheet).Internals.CellsCollection.Remove(c)); - cellsToInsert.ForEach(c => (Worksheet).Internals.CellsCollection.Add(c.Key, c.Value)); + cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); } private void TransposeMerged(Int32 squareSide) @@ -497,7 +497,7 @@ RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); var mranges = new List(); - foreach (var merge in (Worksheet).Internals.MergedRanges) + foreach (var merge in Worksheet.Internals.MergedRanges) { if (Contains(merge)) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 3e74458..83a57f4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -10,8 +10,6 @@ using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using DocumentFormat.OpenXml.VariantTypes; -using A = DocumentFormat.OpenXml.Drawing; -using Ap = DocumentFormat.OpenXml.ExtendedProperties; using BackgroundColor = DocumentFormat.OpenXml.Spreadsheet.BackgroundColor; using BottomBorder = DocumentFormat.OpenXml.Spreadsheet.BottomBorder; using Break = DocumentFormat.OpenXml.Spreadsheet.Break; @@ -23,7 +21,6 @@ using GradientStop = DocumentFormat.OpenXml.Drawing.GradientStop; using Hyperlink = DocumentFormat.OpenXml.Spreadsheet.Hyperlink; using LeftBorder = DocumentFormat.OpenXml.Spreadsheet.LeftBorder; -using Op = DocumentFormat.OpenXml.CustomProperties; using Outline = DocumentFormat.OpenXml.Drawing.Outline; using Path = System.IO.Path; using PatternFill = DocumentFormat.OpenXml.Spreadsheet.PatternFill; @@ -33,7 +30,6 @@ using Text = DocumentFormat.OpenXml.Spreadsheet.Text; using TopBorder = DocumentFormat.OpenXml.Spreadsheet.TopBorder; using Underline = DocumentFormat.OpenXml.Spreadsheet.Underline; -using Vt = DocumentFormat.OpenXml.VariantTypes; namespace ClosedXML.Excel @@ -56,9 +52,9 @@ switch (xlCell.DataType) { case XLCellValues.Text: - { - return xlCell.ShareString ? cvSharedString : cvInlineString; - } + { + return xlCell.ShareString ? cvSharedString : cvInlineString; + } case XLCellValues.Number: return cvNumber; case XLCellValues.DateTime: @@ -77,13 +73,9 @@ PathHelper.CreateDirectory(Path.GetDirectoryName(filePath)); SpreadsheetDocument package; if (File.Exists(filePath)) - { package = SpreadsheetDocument.Open(filePath, true); - } else - { package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook); - } using (package) { @@ -96,13 +88,9 @@ { SpreadsheetDocument package; if (newStream) - { package = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook); - } else - { package = SpreadsheetDocument.Open(stream, true); - } using (package) { @@ -116,7 +104,7 @@ { var context = new SaveContext(); - WorkbookPart workbookPart = document.WorkbookPart ?? document.AddWorkbookPart(); + var workbookPart = document.WorkbookPart ?? document.AddWorkbookPart(); var worksheets = WorksheetsInternal; var partsToRemove = workbookPart.Parts.Where(s => worksheets.Deleted.Contains(s.RelationshipId)).ToList(); @@ -127,48 +115,44 @@ List existingSheetNames; if (workbookPart.Workbook != null && workbookPart.Workbook.Sheets != null) - { - existingSheetNames = workbookPart.Workbook.Sheets.Elements().Select(s => s.Name.Value.ToLower()).ToList(); - } + existingSheetNames = + workbookPart.Workbook.Sheets.Elements().Select(s => s.Name.Value.ToLower()).ToList(); else - { existingSheetNames = new List(); - } var allSheetNames = existingSheetNames.Union(modifiedSheetNames); - ExtendedFilePropertiesPart extendedFilePropertiesPart = document.ExtendedFilePropertiesPart ?? - document.AddNewPart( - context.RelIdGenerator.GetNext(RelType.Workbook)); + var extendedFilePropertiesPart = document.ExtendedFilePropertiesPart ?? + document.AddNewPart( + context.RelIdGenerator.GetNext(RelType.Workbook)); GenerateExtendedFilePropertiesPartContent(extendedFilePropertiesPart, workbookPart); GenerateWorkbookPartContent(workbookPart, context); - SharedStringTablePart sharedStringTablePart = workbookPart.SharedStringTablePart ?? - workbookPart.AddNewPart( - context.RelIdGenerator.GetNext(RelType.Workbook)); + var sharedStringTablePart = workbookPart.SharedStringTablePart ?? + workbookPart.AddNewPart( + context.RelIdGenerator.GetNext(RelType.Workbook)); GenerateSharedStringTablePartContent(sharedStringTablePart, context); - WorkbookStylesPart workbookStylesPart = workbookPart.WorkbookStylesPart ?? - workbookPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); + var workbookStylesPart = workbookPart.WorkbookStylesPart ?? + workbookPart.AddNewPart( + context.RelIdGenerator.GetNext(RelType.Workbook)); GenerateWorkbookStylesPartContent(workbookStylesPart, context); - foreach (var worksheet in WorksheetsInternal.Cast().OrderBy(w => w.Position)) + foreach (XLWorksheet worksheet in WorksheetsInternal.Cast().OrderBy(w => w.Position)) { WorksheetPart worksheetPart; if (workbookPart.Parts.Any(p => p.RelationshipId == worksheet.RelId)) { - worksheetPart = (WorksheetPart) workbookPart.GetPartById(worksheet.RelId); + worksheetPart = (WorksheetPart)workbookPart.GetPartById(worksheet.RelId); var wsPartsToRemove = worksheetPart.TableDefinitionParts.ToList(); wsPartsToRemove.ForEach(tdp => worksheetPart.DeletePart(tdp)); } else - { worksheetPart = workbookPart.AddNewPart(worksheet.RelId); - } GenerateWorksheetPartContent(worksheetPart, worksheet, context); @@ -186,15 +170,15 @@ if (workbookPart.ThemePart == null) { - ThemePart themePart = workbookPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); + var themePart = workbookPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); GenerateThemePartContent(themePart); } if (CustomProperties.Any()) { document.GetPartsOfType().ToList().ForEach(p => document.DeletePart(p)); - CustomFilePropertiesPart customFilePropertiesPart = - document.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); + var customFilePropertiesPart = + document.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); GenerateCustomFilePropertiesPartContent(customFilePropertiesPart); } @@ -206,10 +190,10 @@ worksheetPart.Worksheet.RemoveAllChildren(); if (worksheet.Tables.Any()) { - foreach (var table in worksheet.Tables) + foreach (IXLTable table in worksheet.Tables) { - var tableRelId = context.RelIdGenerator.GetNext(RelType.Workbook); - var xlTable = (XLTable) table; + string tableRelId = context.RelIdGenerator.GetNext(RelType.Workbook); + var xlTable = (XLTable)table; xlTable.RelId = tableRelId; var tableDefinitionPart = worksheetPart.AddNewPart(tableRelId); GenerateTableDefinitionPartContent(tableDefinitionPart, xlTable, context); @@ -217,47 +201,35 @@ } } - private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart, WorkbookPart workbookPart) + private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart, + WorkbookPart workbookPart) { //if (extendedFilePropertiesPart.Properties.NamespaceDeclarations.Contains(new KeyValuePair( Properties properties; if (extendedFilePropertiesPart.Properties == null) - { extendedFilePropertiesPart.Properties = new Properties(); - } properties = extendedFilePropertiesPart.Properties; if ( - !properties.NamespaceDeclarations.Contains(new KeyValuePair("vt", - "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"))) - { - properties.AddNamespaceDeclaration("vt", "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"); - } + !properties.NamespaceDeclarations.Contains(new KeyValuePair("vt", + "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"))) + properties.AddNamespaceDeclaration("vt", + "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"); if (properties.Application == null) - { properties.AppendChild(new Application {Text = "Microsoft Excel"}); - } if (properties.DocumentSecurity == null) - { properties.AppendChild(new DocumentSecurity {Text = "0"}); - } if (properties.ScaleCrop == null) - { properties.AppendChild(new ScaleCrop {Text = "false"}); - } if (properties.HeadingPairs == null) - { properties.HeadingPairs = new HeadingPairs(); - } if (properties.TitlesOfParts == null) - { properties.TitlesOfParts = new TitlesOfParts(); - } properties.HeadingPairs.VTVector = new VTVector {BaseType = VectorBaseValues.Variant}; @@ -269,25 +241,26 @@ VTVector vTVector_Two; vTVector_Two = properties.TitlesOfParts.VTVector; - var modifiedWorksheets = ((IEnumerable) WorksheetsInternal).Select(w => new {w.Name, Order = w.Position}).ToList(); + var modifiedWorksheets = + ((IEnumerable)WorksheetsInternal).Select(w => new {w.Name, Order = w.Position}).ToList(); var modifiedNamedRanges = GetModifiedNamedRanges(); - var modifiedWorksheetsCount = modifiedWorksheets.Count(); - var modifiedNamedRangesCount = modifiedNamedRanges.Count(); + int modifiedWorksheetsCount = modifiedWorksheets.Count(); + int modifiedNamedRangesCount = modifiedNamedRanges.Count(); InsertOnVTVector(vTVector_One, "Worksheets", 0, modifiedWorksheetsCount.ToString()); InsertOnVTVector(vTVector_One, "Named Ranges", 2, modifiedNamedRangesCount.ToString()); - vTVector_Two.Size = (UInt32) (modifiedNamedRangesCount + modifiedWorksheetsCount); + vTVector_Two.Size = (UInt32)(modifiedNamedRangesCount + modifiedWorksheetsCount); foreach (var w in modifiedWorksheets.OrderBy(w => w.Order)) { - VTLPSTR vTLPSTR3 = new VTLPSTR {Text = w.Name}; + var vTLPSTR3 = new VTLPSTR {Text = w.Name}; vTVector_Two.AppendChild(vTLPSTR3); } - foreach (var nr in modifiedNamedRanges) + foreach (string nr in modifiedNamedRanges) { - VTLPSTR vTLPSTR7 = new VTLPSTR {Text = nr}; + var vTLPSTR7 = new VTLPSTR {Text = nr}; vTVector_Two.AppendChild(vTLPSTR7); } @@ -296,16 +269,12 @@ if (!StringExtensions.IsNullOrWhiteSpace(Properties.Manager)) { if (properties.Manager == null) - { properties.Manager = new Manager(); - } properties.Manager.Text = Properties.Manager; } else - { properties.Manager = null; - } } if (Properties.Company != null) @@ -313,16 +282,12 @@ if (!StringExtensions.IsNullOrWhiteSpace(Properties.Company)) { if (properties.Company == null) - { properties.Company = new Company(); - } properties.Company.Text = Properties.Company; } else - { properties = null; - } } } @@ -334,24 +299,22 @@ if (!m.Any()) { if (vTVector.Size == null) - { vTVector.Size = new UInt32Value(0U); - } vTVector.Size += 2U; - Variant variant1 = new Variant(); - VTLPSTR vTLPSTR1 = new VTLPSTR {Text = property}; + var variant1 = new Variant(); + var vTLPSTR1 = new VTLPSTR {Text = property}; variant1.AppendChild(vTLPSTR1); vTVector.InsertAt(variant1, index); - Variant variant2 = new Variant(); - VTInt32 vTInt321 = new VTInt32(); + var variant2 = new Variant(); + var vTInt321 = new VTInt32(); variant2.AppendChild(vTInt321); vTVector.InsertAt(variant2, index + 1); } Int32 targetIndex = 0; - foreach (var e in vTVector.Elements()) + foreach (Variant e in vTVector.Elements()) { if (e.Elements().Any(e2 => e2.Text == property)) { @@ -365,12 +328,10 @@ private List GetModifiedNamedRanges() { var namedRanges = new List(); - foreach (var w in WorksheetsInternal) + foreach (XLWorksheet w in WorksheetsInternal) { - foreach (var n in w.NamedRanges) - { + foreach (IXLNamedRange n in w.NamedRanges) namedRanges.Add(w.Name + "!" + n.Name); - } namedRanges.Add(w.Name + "!Print_Area"); namedRanges.Add(w.Name + "!Print_Titles"); } @@ -381,92 +342,83 @@ 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("r", - "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) - { - workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); - } + !workbook.NamespaceDeclarations.Contains(new KeyValuePair("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 (workbook.WorkbookProperties.DefaultThemeVersion == null) - { workbook.WorkbookProperties.DefaultThemeVersion = 124226U; - } + #endregion + if (workbook.BookViews == null) - { workbook.BookViews = new BookViews(); - } if (workbook.Sheets == null) - { workbook.Sheets = new Sheets(); - } var worksheets = WorksheetsInternal; workbook.Sheets.Elements().Where(s => worksheets.Deleted.Contains(s.Id)).ForEach(s => s.Remove()); - foreach (var sheet in workbook.Sheets.Elements()) + foreach (Sheet sheet in workbook.Sheets.Elements()) { - var sName = sheet.Name.Value; + string sName = sheet.Name.Value; //if (Worksheets.Where(w => w.Name.ToLower() == sName.ToLower())) - if (WorksheetsInternal.Any(w => (w).SheetId == (Int32) sheet.SheetId.Value)) + if (WorksheetsInternal.Any(w => (w).SheetId == (Int32)sheet.SheetId.Value)) { - var wks = WorksheetsInternal.Where(w => (w).SheetId == (Int32) sheet.SheetId.Value).Single(); + var wks = + WorksheetsInternal.Where(w => (w).SheetId == (Int32)sheet.SheetId.Value).Single(); //wks.SheetId = (Int32)sheet.SheetId.Value; wks.RelId = sheet.Id; sheet.Name = wks.Name; } } - foreach (var xlSheet in WorksheetsInternal.Cast().Where(w => w.SheetId == 0).OrderBy(w => w.Position)) + foreach ( + XLWorksheet xlSheet in + WorksheetsInternal.Cast().Where(w => w.SheetId == 0).OrderBy(w => w.Position)) { String rId = context.RelIdGenerator.GetNext(RelType.Workbook); while (WorksheetsInternal.Cast().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 + Name = xlSheet.Name, + Id = rId, + SheetId = (UInt32)xlSheet.SheetId }; if (xlSheet.Visibility != XLWorksheetVisibility.Visible) - { newSheet.State = xlSheet.Visibility.ToOpenXml(); - } workbook.Sheets.AppendChild(newSheet); } var sheetElements = from sheet in workbook.Sheets.Elements() - join worksheet in ((IEnumerable) WorksheetsInternal) on sheet.Id.Value equals worksheet.RelId + join worksheet in ((IEnumerable)WorksheetsInternal) on sheet.Id.Value + equals worksheet.RelId orderby worksheet.Position select sheet; UInt32 firstSheetVisible = 0; Boolean foundVisible = false; - foreach (var sheet in sheetElements) + foreach (Sheet sheet in sheetElements) { workbook.Sheets.RemoveChild(sheet); workbook.Sheets.AppendChild(sheet); @@ -474,24 +426,20 @@ if (!foundVisible) { if (sheet.State == null || sheet.State == SheetStateValues.Visible) - { foundVisible = true; - } else - { firstSheetVisible++; - } } } - WorkbookView workbookView = workbook.BookViews.Elements().FirstOrDefault(); + var workbookView = workbook.BookViews.Elements().FirstOrDefault(); UInt32 activeTab = firstSheetVisible; - foreach (var ws in worksheets) + foreach (XLWorksheet ws in worksheets) { if (ws.TabActive) { - activeTab = (UInt32) (ws.Position - 1); + activeTab = (UInt32)(ws.Position - 1); break; } } @@ -507,24 +455,22 @@ workbookView.FirstSheet = firstSheetVisible; } - DefinedNames definedNames = new DefinedNames(); - foreach (var worksheet in WorksheetsInternal.Cast()) + var definedNames = new DefinedNames(); + foreach (XLWorksheet worksheet in WorksheetsInternal.Cast()) { UInt32 sheetId = 0; - foreach (var s in workbook.Sheets.Elements()) + foreach (Sheet s in workbook.Sheets.Elements()) { - if (s.SheetId == (UInt32) worksheet.SheetId) - { + if (s.SheetId == (UInt32)worksheet.SheetId) break; - } sheetId++; } if (worksheet.PageSetup.PrintAreas.Any()) { - DefinedName definedName = new DefinedName {Name = "_xlnm.Print_Area", LocalSheetId = sheetId}; - var definedNameText = String.Empty; - foreach (var printArea in worksheet.PageSetup.PrintAreas) + var definedName = new DefinedName {Name = "_xlnm.Print_Area", LocalSheetId = sheetId}; + string definedNameText = String.Empty; + foreach (IXLRange printArea in worksheet.PageSetup.PrintAreas) { definedNameText += "'" + worksheet.Name + "'!" + printArea.RangeAddress.FirstAddress.ToStringFixed() @@ -534,34 +480,33 @@ definedNames.AppendChild(definedName); } - foreach (var nr in worksheet.NamedRanges) + foreach (IXLNamedRange nr in worksheet.NamedRanges) { - DefinedName definedName = new DefinedName - { - Name = nr.Name, - LocalSheetId = sheetId, - Text = nr.ToString() - }; + var definedName = new DefinedName + { + Name = nr.Name, + LocalSheetId = sheetId, + Text = nr.ToString() + }; if (!StringExtensions.IsNullOrWhiteSpace(nr.Comment)) - { definedName.Comment = nr.Comment; - } definedNames.AppendChild(definedName); } - var titles = String.Empty; - var definedNameTextRow = String.Empty; - var definedNameTextColumn = String.Empty; + string titles = String.Empty; + string definedNameTextRow = String.Empty; + string definedNameTextColumn = String.Empty; if (worksheet.PageSetup.FirstRowToRepeatAtTop > 0) { - definedNameTextRow = "'" + worksheet.Name + "'!" + worksheet.PageSetup.FirstRowToRepeatAtTop.ToString() - + ":" + worksheet.PageSetup.LastRowToRepeatAtTop.ToString(); + 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 + "'!" + ExcelHelper.GetColumnLetterFromNumber(minColumn) + int minColumn = worksheet.PageSetup.FirstColumnToRepeatAtLeft; + int maxColumn = worksheet.PageSetup.LastColumnToRepeatAtLeft; + definedNameTextColumn = "'" + worksheet.Name + "'!" + + ExcelHelper.GetColumnLetterFromNumber(minColumn) + ":" + ExcelHelper.GetColumnLetterFromNumber(maxColumn); } @@ -569,140 +514,130 @@ { titles = definedNameTextColumn; if (definedNameTextRow.Length > 0) - { titles += "," + definedNameTextRow; - } } else - { titles = definedNameTextRow; - } if (titles.Length > 0) { - DefinedName definedName = new DefinedName {Name = "_xlnm.Print_Titles", LocalSheetId = sheetId}; + var definedName = new DefinedName {Name = "_xlnm.Print_Titles", LocalSheetId = sheetId}; definedName.Text = titles; definedNames.AppendChild(definedName); } } - foreach (var nr in NamedRanges) + foreach (IXLNamedRange nr in NamedRanges) { - DefinedName definedName = new DefinedName - { - Name = nr.Name, - Text = nr.ToString() - }; + var definedName = new DefinedName + { + Name = nr.Name, + Text = nr.ToString() + }; if (!StringExtensions.IsNullOrWhiteSpace(nr.Comment)) - { definedName.Comment = nr.Comment; - } definedNames.AppendChild(definedName); } if (workbook.DefinedNames == null) - { workbook.DefinedNames = new DefinedNames(); - } foreach (DefinedName dn in definedNames) { if (workbook.DefinedNames.Elements().Any(d => d.Name.Value.ToLower() == dn.Name.Value.ToLower() && ( - (d.LocalSheetId != null && dn.LocalSheetId != null && - d.LocalSheetId.InnerText == dn.LocalSheetId.InnerText) - || d.LocalSheetId == null || dn.LocalSheetId == null) - )) + (d.LocalSheetId != null && + dn.LocalSheetId != null && + d.LocalSheetId.InnerText == + dn.LocalSheetId.InnerText) + || d.LocalSheetId == null || + dn.LocalSheetId == null) + )) { - DefinedName existingDefinedName = (DefinedName) workbook.DefinedNames.Where(d => - ((DefinedName) d).Name.Value.ToLower() == - dn.Name.Value.ToLower() - && ( - (((DefinedName) d).LocalSheetId != null && - dn.LocalSheetId != null && - ((DefinedName) d).LocalSheetId.InnerText == - dn.LocalSheetId.InnerText) - || ((DefinedName) d).LocalSheetId == null || - dn.LocalSheetId == null) - ).First(); + var existingDefinedName = (DefinedName)workbook.DefinedNames.Where(d => + ((DefinedName)d).Name.Value. + ToLower() == + dn.Name.Value.ToLower() + && ( + (((DefinedName)d). + LocalSheetId != null && + dn.LocalSheetId != null && + ((DefinedName)d). + LocalSheetId. + InnerText == + dn.LocalSheetId.InnerText) + || + ((DefinedName)d). + LocalSheetId == null || + dn.LocalSheetId == null) + ).First(); 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}; - } 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(); - } } - private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart, SaveContext context) + private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart, + SaveContext context) { - sharedStringTablePart.SharedStringTable = new SharedStringTable() { Count = 0, UniqueCount = 0 }; + sharedStringTablePart.SharedStringTable = new SharedStringTable {Count = 0, UniqueCount = 0}; Int32 stringId = 0; - Dictionary newStrings = new Dictionary(); - Dictionary newRichStrings = new Dictionary(); - foreach (var w in Worksheets.Cast()) + var newStrings = new Dictionary(); + var newRichStrings = new Dictionary(); + foreach (XLWorksheet w in Worksheets.Cast()) { - foreach (var c in w.Internals.CellsCollection.Values) + foreach (XLCell c in w.Internals.CellsCollection.Values) { if ( - c.DataType == XLCellValues.Text + c.DataType == XLCellValues.Text && c.ShareString && !StringExtensions.IsNullOrWhiteSpace(c.InnerText)) { if (c.HasRichText) { if (newRichStrings.ContainsKey(c.RichText)) - { c.SharedStringId = newRichStrings[c.RichText]; - } else { - - SharedStringItem sharedStringItem = new SharedStringItem(); - foreach (var rt in c.RichText) + var sharedStringItem = new SharedStringItem(); + foreach (IXLRichString rt in c.RichText) { var run = new DocumentFormat.OpenXml.Spreadsheet.Run(); var runProperties = new DocumentFormat.OpenXml.Spreadsheet.RunProperties(); - Bold bold = rt.Bold ? new Bold() : null; - Italic italic = rt.Italic ? new Italic() : null; - Underline underline = rt.Underline != XLFontUnderlineValues.None ? new Underline() { Val = rt.Underline.ToOpenXml() } : null; - Strike strike = rt.Strikethrough ? new Strike() : null; - VerticalTextAlignment verticalAlignment = new VerticalTextAlignment() { Val = rt.VerticalAlignment.ToOpenXml() }; - Shadow shadow = rt.Shadow ? new Shadow() : null; - FontSize fontSize = new FontSize() { Val = rt.FontSize }; - Color color = GetNewColor(rt.FontColor); - RunFont fontName = new RunFont() { Val = rt.FontName }; - FontFamily fontFamilyNumbering = new FontFamily() { Val = (Int32)rt.FontFamilyNumbering }; + var bold = rt.Bold ? new Bold() : null; + var italic = rt.Italic ? new Italic() : null; + var underline = rt.Underline != XLFontUnderlineValues.None + ? new Underline {Val = rt.Underline.ToOpenXml()} + : null; + var strike = rt.Strikethrough ? new Strike() : null; + var verticalAlignment = new VerticalTextAlignment + {Val = rt.VerticalAlignment.ToOpenXml()}; + var shadow = rt.Shadow ? new Shadow() : null; + var fontSize = new FontSize {Val = rt.FontSize}; + var color = GetNewColor(rt.FontColor); + var fontName = new RunFont {Val = rt.FontName}; + var fontFamilyNumbering = new FontFamily {Val = (Int32)rt.FontFamilyNumbering}; if (bold != null) runProperties.Append(bold); if (italic != null) runProperties.Append(italic); @@ -717,9 +652,10 @@ runProperties.Append(fontName); runProperties.Append(fontFamilyNumbering); - Text text = new Text(); + var text = new Text(); text.Text = rt.Text; - if (rt.Text.StartsWith(" ") || rt.Text.EndsWith(" ") || rt.Text.Contains(Environment.NewLine)) + if (rt.Text.StartsWith(" ") || rt.Text.EndsWith(" ") || + rt.Text.Contains(Environment.NewLine)) text.Space = SpaceProcessingModeValues.Preserve; run.Append(runProperties); @@ -730,26 +666,30 @@ if (c.RichText.HasPhonetics) { - foreach (var p in c.RichText.Phonetics) + foreach (IXLPhonetic p in c.RichText.Phonetics) { - PhoneticRun phoneticRun = new PhoneticRun() - { - BaseTextStartIndex = (UInt32)p.Start, - EndingBaseIndex = (UInt32)p.End - }; + var phoneticRun = new PhoneticRun + { + BaseTextStartIndex = (UInt32)p.Start, + EndingBaseIndex = (UInt32)p.End + }; - Text text = new Text(); + var text = new Text(); text.Text = p.Text; phoneticRun.Append(text); sharedStringItem.Append(phoneticRun); } var f = new XLFont(null, c.RichText.Phonetics); - context.SharedFonts.Add(f, new FontInfo() { Font = f }); + context.SharedFonts.Add(f, new FontInfo {Font = f}); - PhoneticProperties phoneticProperties = new PhoneticProperties() { - FontId = context.SharedFonts[new XLFont(null, c.RichText.Phonetics)].FontId - }; + var phoneticProperties = new PhoneticProperties + { + FontId = + context.SharedFonts[ + new XLFont(null, c.RichText.Phonetics)]. + FontId + }; if (c.RichText.Phonetics.Alignment != XLPhoneticAlignment.Left) phoneticProperties.Alignment = c.RichText.Phonetics.Alignment.ToOpenXml(); if (c.RichText.Phonetics.Type != XLPhoneticType.FullWidthKatakana) @@ -771,14 +711,12 @@ else { if (newStrings.ContainsKey(c.Value.ToString())) - { c.SharedStringId = newStrings[c.Value.ToString()]; - } else { String s = c.Value.ToString(); - SharedStringItem sharedStringItem = new SharedStringItem(); - Text text = new Text(); + var sharedStringItem = new SharedStringItem(); + var text = new Text(); text.Text = s; if (s.StartsWith(" ") || s.EndsWith(" ")) text.Space = SpaceProcessingModeValues.Preserve; @@ -797,2126 +735,17 @@ } } } - #region GenerateWorkbookStylesPartContent - private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart, SaveContext context) - { - var defaultStyle = new XLStyle(null, DefaultStyle); - if (!context.SharedFonts.ContainsKey(defaultStyle.Font)) - context.SharedFonts.Add(defaultStyle.Font, new FontInfo { FontId = 0, Font = defaultStyle.Font }); - - Dictionary sharedFills = new Dictionary(); - sharedFills.Add(defaultStyle.Fill, new FillInfo {FillId = 2, Fill = defaultStyle.Fill}); - - Dictionary sharedBorders = new Dictionary(); - sharedBorders.Add(defaultStyle.Border, new BorderInfo {BorderId = 0, Border = defaultStyle.Border}); - - Dictionary sharedNumberFormats = new Dictionary(); - sharedNumberFormats.Add(defaultStyle.NumberFormat, new NumberFormatInfo {NumberFormatId = 0, NumberFormat = defaultStyle.NumberFormat}); - - //Dictionary sharedAlignments = new Dictionary(); - //sharedAlignments.Add(defaultStyle.Alignment.ToString(), new AlignmentInfo() { AlignmentId = 0, Alignment = defaultStyle.Alignment }); - - if (workbookStylesPart.Stylesheet == null) - { - workbookStylesPart.Stylesheet = new Stylesheet(); - } - - // Cell styles = Named styles - if (workbookStylesPart.Stylesheet.CellStyles == null) - { - workbookStylesPart.Stylesheet.CellStyles = new CellStyles(); - } - - UInt32 defaultFormatId; - if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.Name == "Normal")) - { - defaultFormatId = - workbookStylesPart.Stylesheet.CellStyles.Elements().Where(c => c.Name == "Normal").Single().FormatId.Value; - } - else if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any()) - { - defaultFormatId = workbookStylesPart.Stylesheet.CellStyles.Elements().Max(c => c.FormatId.Value) + 1; - } - else - { - defaultFormatId = 0; - } - - context.SharedStyles.Add(defaultStyle, - new StyleInfo - { - StyleId = defaultFormatId, - Style = defaultStyle, - FontId = 0, - FillId = 0, - BorderId = 0, - NumberFormatId = 0 - //AlignmentId = 0 - }); - - UInt32 styleCount = 1; - UInt32 fontCount = 1; - UInt32 fillCount = 3; - UInt32 borderCount = 1; - Int32 numberFormatCount = 1; - var xlStyles = new HashSet(); - - foreach (var worksheet in WorksheetsInternal) - { - foreach (var s in worksheet.Styles) - { - if (!xlStyles.Contains(s)) - { - xlStyles.Add(s); - } - } - - foreach (var s in worksheet.Internals.ColumnsCollection.Select(kp => kp.Value.Style)) - { - if (!xlStyles.Contains(s)) - { - xlStyles.Add(s); - } - } - - foreach (var s in worksheet.Internals.RowsCollection.Select(kp => kp.Value.Style)) - { - if (!xlStyles.Contains(s)) - { - xlStyles.Add(s); - } - } - - //xlStyles.AddRange(worksheet.Styles); - //worksheet.Internals.ColumnsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); - //worksheet.Internals.RowsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); - } - - foreach (var xlStyle in xlStyles) - { - if (!context.SharedFonts.ContainsKey(xlStyle.Font)) - { - context.SharedFonts.Add(xlStyle.Font, new FontInfo { FontId = fontCount++, Font = xlStyle.Font }); - } - - if (!sharedFills.ContainsKey(xlStyle.Fill)) - { - sharedFills.Add(xlStyle.Fill, new FillInfo {FillId = fillCount++, Fill = xlStyle.Fill}); - } - - if (!sharedBorders.ContainsKey(xlStyle.Border)) - { - sharedBorders.Add(xlStyle.Border, new BorderInfo {BorderId = borderCount++, Border = xlStyle.Border}); - } - - if (xlStyle.NumberFormat.NumberFormatId == -1 && !sharedNumberFormats.ContainsKey(xlStyle.NumberFormat)) - { - sharedNumberFormats.Add(xlStyle.NumberFormat, - new NumberFormatInfo {NumberFormatId = numberFormatCount + 164, NumberFormat = xlStyle.NumberFormat}); - numberFormatCount++; - } - } - - var allSharedNumberFormats = ResolveNumberFormats(workbookStylesPart, sharedNumberFormats); - ResolveFonts(workbookStylesPart, context); - var allSharedFills = ResolveFills(workbookStylesPart, sharedFills); - var allSharedBorders = ResolveBorders(workbookStylesPart, sharedBorders); - - foreach (var xlStyle in xlStyles) - { - if (!context.SharedStyles.ContainsKey(xlStyle)) - { - Int32 numberFormatId; - if (xlStyle.NumberFormat.NumberFormatId >= 0) - { - numberFormatId = xlStyle.NumberFormat.NumberFormatId; - } - else - { - numberFormatId = allSharedNumberFormats[xlStyle.NumberFormat].NumberFormatId; - } - - context.SharedStyles.Add(xlStyle, - new StyleInfo - { - StyleId = styleCount++, - Style = xlStyle, - FontId = context.SharedFonts[xlStyle.Font].FontId, - FillId = allSharedFills[xlStyle.Fill].FillId, - BorderId = allSharedBorders[xlStyle.Border].BorderId, - NumberFormatId = numberFormatId - }); - } - } - - var allCellStyleFormats = ResolveCellStyleFormats(workbookStylesPart, context); - ResolveRest(workbookStylesPart, context); - - if (!workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.Name == "Normal")) - { - //var defaultFormatId = context.SharedStyles.Values.Where(s => s.Style.Equals(DefaultStyle)).Single().StyleId; - - CellStyle cellStyle1 = new CellStyle {Name = "Normal", FormatId = defaultFormatId, BuiltinId = 0U}; - workbookStylesPart.Stylesheet.CellStyles.AppendChild(cellStyle1); - } - workbookStylesPart.Stylesheet.CellStyles.Count = (UInt32) workbookStylesPart.Stylesheet.CellStyles.Count(); - - var newSharedStyles = new Dictionary(); - foreach (var ss in context.SharedStyles) - { - Int32 styleId = -1; - foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats) - { - styleId++; - if (CellFormatsAreEqual(f, ss.Value)) - { - break; - } - } - if (styleId == -1) - { - styleId = 0; - } - var si = ss.Value; - si.StyleId = (UInt32) styleId; - newSharedStyles.Add(ss.Key, si); - } - context.SharedStyles.Clear(); - newSharedStyles.ForEach(kp => context.SharedStyles.Add(kp.Key, kp.Value)); - - //TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" }; - //workbookStylesPart.Stylesheet.AppendChild(tableStyles1); - } - - private void ResolveRest(WorkbookStylesPart workbookStylesPart, SaveContext context) - { - if (workbookStylesPart.Stylesheet.CellFormats == null) - { - workbookStylesPart.Stylesheet.CellFormats = new CellFormats(); - } - - foreach (var styleInfo in context.SharedStyles.Values) - { - Int32 styleId = 0; - Boolean foundOne = false; - foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats) - { - if (CellFormatsAreEqual(f, styleInfo)) - { - foundOne = true; - break; - } - styleId++; - } - if (!foundOne) - { - Int32 formatId = 0; - foreach (CellFormat f in workbookStylesPart.Stylesheet.CellStyleFormats) - { - if (CellFormatsAreEqual(f, styleInfo)) - { - break; - } - styleId++; - } - - //CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false, FormatId = (UInt32)formatId }; - CellFormat cellFormat = GetCellFormat(styleInfo); - cellFormat.FormatId = (UInt32) formatId; - Alignment alignment = new Alignment - { - Horizontal = styleInfo.Style.Alignment.Horizontal.ToOpenXml(), - Vertical = styleInfo.Style.Alignment.Vertical.ToOpenXml(), - Indent = (UInt32) styleInfo.Style.Alignment.Indent, - ReadingOrder = (UInt32) styleInfo.Style.Alignment.ReadingOrder, - WrapText = styleInfo.Style.Alignment.WrapText, - TextRotation = (UInt32) styleInfo.Style.Alignment.TextRotation, - ShrinkToFit = styleInfo.Style.Alignment.ShrinkToFit, - RelativeIndent = styleInfo.Style.Alignment.RelativeIndent, - JustifyLastLine = styleInfo.Style.Alignment.JustifyLastLine - }; - cellFormat.AppendChild(alignment); - - if (cellFormat.ApplyProtection.Value) - { - cellFormat.AppendChild(GetProtection(styleInfo)); - } - - workbookStylesPart.Stylesheet.CellFormats.AppendChild(cellFormat); - } - } - workbookStylesPart.Stylesheet.CellFormats.Count = (UInt32) workbookStylesPart.Stylesheet.CellFormats.Count(); - } - - private Dictionary ResolveCellStyleFormats(WorkbookStylesPart workbookStylesPart, SaveContext context) - { - if (workbookStylesPart.Stylesheet.CellStyleFormats == null) - { - workbookStylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats(); - } - - var allSharedStyles = new Dictionary(); - foreach (var styleInfo in context.SharedStyles.Values) - { - Int32 styleId = 0; - Boolean foundOne = false; - foreach (CellFormat f in workbookStylesPart.Stylesheet.CellStyleFormats) - { - if (CellFormatsAreEqual(f, styleInfo)) - { - foundOne = true; - break; - } - styleId++; - } - if (!foundOne) - { - //CellFormat cellStyleFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false }; - CellFormat cellStyleFormat = GetCellFormat(styleInfo); - - if (cellStyleFormat.ApplyProtection.Value) - { - cellStyleFormat.AppendChild(GetProtection(styleInfo)); - } - - workbookStylesPart.Stylesheet.CellStyleFormats.AppendChild(cellStyleFormat); - } - allSharedStyles.Add(styleInfo.Style, new StyleInfo {Style = styleInfo.Style, StyleId = (UInt32) styleId}); - } - workbookStylesPart.Stylesheet.CellStyleFormats.Count = (UInt32) workbookStylesPart.Stylesheet.CellStyleFormats.Count(); - - return allSharedStyles; - } - - private static bool ApplyFill(StyleInfo styleInfo) - { - return styleInfo.Style.Fill.PatternType.ToOpenXml() == PatternValues.None; - } - - private static bool ApplyBorder(StyleInfo styleInfo) - { - IXLBorder opBorder = styleInfo.Style.Border; - return (opBorder.BottomBorder.ToOpenXml() != BorderStyleValues.None - || opBorder.DiagonalBorder.ToOpenXml() != BorderStyleValues.None - || opBorder.RightBorder.ToOpenXml() != BorderStyleValues.None - || opBorder.LeftBorder.ToOpenXml() != BorderStyleValues.None - || opBorder.TopBorder.ToOpenXml() != BorderStyleValues.None); - } - - private static bool ApplyProtection(StyleInfo styleInfo) - { - return styleInfo.Style.Protection != null; - } - - private CellFormat GetCellFormat(StyleInfo styleInfo) - { - var cellFormat = new CellFormat - { - NumberFormatId = (UInt32) styleInfo.NumberFormatId, - FontId = styleInfo.FontId, - FillId = styleInfo.FillId, - BorderId = styleInfo.BorderId, - ApplyNumberFormat = false, - ApplyFill = ApplyFill(styleInfo), - ApplyBorder = ApplyBorder(styleInfo), - ApplyAlignment = false, - ApplyProtection = ApplyProtection(styleInfo) - }; - return cellFormat; - } - - private static Protection GetProtection(StyleInfo styleInfo) - { - return new Protection - { - Locked = styleInfo.Style.Protection.Locked, - Hidden = styleInfo.Style.Protection.Hidden - }; - } - - private static bool CellFormatsAreEqual(CellFormat f, StyleInfo styleInfo) - { - return - styleInfo.BorderId == f.BorderId - && styleInfo.FillId == f.FillId - && styleInfo.FontId == f.FontId - && styleInfo.NumberFormatId == f.NumberFormatId - && f.ApplyNumberFormat != null && f.ApplyNumberFormat == false - && f.ApplyAlignment != null && f.ApplyAlignment == false - && f.ApplyFill != null && f.ApplyFill == ApplyFill(styleInfo) - && f.ApplyBorder != null && f.ApplyBorder == ApplyBorder(styleInfo) - && AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment) - && ProtectionsAreEqual(f.Protection, styleInfo.Style.Protection) - ; - } - - private static bool ProtectionsAreEqual(Protection protection, IXLProtection xlProtection) - { - var p = new XLProtection(); - if (protection != null) - { - if (protection.Locked != null) - { - p.Locked = protection.Locked.Value; - } - if (protection.Hidden != null) - { - p.Hidden = protection.Hidden.Value; - } - } - return p.Equals(xlProtection); - } - - private static bool AlignmentsAreEqual(Alignment alignment, IXLAlignment xlAlignment) - { - var a = new XLAlignment(); - if (alignment != null) - { - if (alignment.Indent != null) - { - a.Indent = (Int32)alignment.Indent.Value; - } - - if (alignment.Horizontal != null) - { - a.Horizontal = alignment.Horizontal.Value.ToClosedXml(); - } - if (alignment.Vertical != null) - { - a.Vertical = alignment.Vertical.Value.ToClosedXml(); - } - - if (alignment.ReadingOrder != null) - { - a.ReadingOrder = alignment.ReadingOrder.Value.ToClosedXml(); - } - if (alignment.WrapText != null) - { - a.WrapText = alignment.WrapText.Value; - } - if (alignment.TextRotation != null) - { - a.TextRotation = (Int32) alignment.TextRotation.Value; - } - if (alignment.ShrinkToFit != null) - { - a.ShrinkToFit = alignment.ShrinkToFit.Value; - } - if (alignment.RelativeIndent != null) - { - a.RelativeIndent = alignment.RelativeIndent.Value; - } - if (alignment.JustifyLastLine != null) - { - a.JustifyLastLine = alignment.JustifyLastLine.Value; - } - } - return a.Equals(xlAlignment); - } - - private Dictionary ResolveBorders(WorkbookStylesPart workbookStylesPart, - Dictionary sharedBorders) - { - if (workbookStylesPart.Stylesheet.Borders == null) - { - workbookStylesPart.Stylesheet.Borders = new Borders(); - } - - var allSharedBorders = new Dictionary(); - foreach (var borderInfo in sharedBorders.Values) - { - Int32 borderId = 0; - Boolean foundOne = false; - foreach (Border f in workbookStylesPart.Stylesheet.Borders) - { - if (BordersAreEqual(f, borderInfo.Border)) - { - foundOne = true; - break; - } - borderId++; - } - if (!foundOne) - { - Border border = GetNewBorder(borderInfo); - workbookStylesPart.Stylesheet.Borders.AppendChild(border); - } - allSharedBorders.Add(borderInfo.Border, new BorderInfo {Border = borderInfo.Border, BorderId = (UInt32) borderId}); - } - workbookStylesPart.Stylesheet.Borders.Count = (UInt32) workbookStylesPart.Stylesheet.Borders.Count(); - return allSharedBorders; - } - - private Border GetNewBorder(BorderInfo borderInfo) - { - Border border = new Border {DiagonalUp = borderInfo.Border.DiagonalUp, DiagonalDown = borderInfo.Border.DiagonalDown}; - - LeftBorder leftBorder = new LeftBorder {Style = borderInfo.Border.LeftBorder.ToOpenXml()}; - Color leftBorderColor = GetNewColor(borderInfo.Border.LeftBorderColor); - leftBorder.AppendChild(leftBorderColor); - border.AppendChild(leftBorder); - - RightBorder rightBorder = new RightBorder {Style = borderInfo.Border.RightBorder.ToOpenXml()}; - Color rightBorderColor = GetNewColor(borderInfo.Border.RightBorderColor); - rightBorder.AppendChild(rightBorderColor); - border.AppendChild(rightBorder); - - TopBorder topBorder = new TopBorder {Style = borderInfo.Border.TopBorder.ToOpenXml()}; - Color topBorderColor = GetNewColor(borderInfo.Border.TopBorderColor); - topBorder.AppendChild(topBorderColor); - border.AppendChild(topBorder); - - BottomBorder bottomBorder = new BottomBorder {Style = borderInfo.Border.BottomBorder.ToOpenXml()}; - Color bottomBorderColor = GetNewColor(borderInfo.Border.BottomBorderColor); - bottomBorder.AppendChild(bottomBorderColor); - border.AppendChild(bottomBorder); - - DiagonalBorder diagonalBorder = new DiagonalBorder {Style = borderInfo.Border.DiagonalBorder.ToOpenXml()}; - Color diagonalBorderColor = GetNewColor(borderInfo.Border.DiagonalBorderColor); - diagonalBorder.AppendChild(diagonalBorderColor); - border.AppendChild(diagonalBorder); - - return border; - } - - private bool BordersAreEqual(Border b, IXLBorder xlBorder) - { - var nb = new XLBorder(); - if (b.DiagonalUp != null) - { - nb.DiagonalUp = b.DiagonalUp.Value; - } - - if (b.DiagonalDown != null) - { - nb.DiagonalDown = b.DiagonalDown.Value; - } - - if (b.LeftBorder != null) - { - if (b.LeftBorder.Style != null) - { - nb.LeftBorder = b.LeftBorder.Style.Value.ToClosedXml(); - } - var bColor = GetColor(b.LeftBorder.Color); - if (bColor.HasValue) - { - nb.LeftBorderColor = bColor; - } - } - - if (b.RightBorder != null) - { - if (b.RightBorder.Style != null) - { - nb.RightBorder = b.RightBorder.Style.Value.ToClosedXml(); - } - var bColor = GetColor(b.RightBorder.Color); - if (bColor.HasValue) - { - nb.RightBorderColor = bColor; - } - } - - if (b.TopBorder != null) - { - if (b.TopBorder.Style != null) - { - nb.TopBorder = b.TopBorder.Style.Value.ToClosedXml(); - } - var bColor = GetColor(b.TopBorder.Color); - if (bColor.HasValue) - { - nb.TopBorderColor = bColor; - } - } - - if (b.BottomBorder != null) - { - if (b.BottomBorder.Style != null) - { - nb.BottomBorder = b.BottomBorder.Style.Value.ToClosedXml(); - } - var bColor = GetColor(b.BottomBorder.Color); - if (bColor.HasValue) - { - nb.BottomBorderColor = bColor; - } - } - - return nb.Equals(xlBorder); - } - - private Dictionary ResolveFills(WorkbookStylesPart workbookStylesPart, Dictionary sharedFills) - { - if (workbookStylesPart.Stylesheet.Fills == null) - { - workbookStylesPart.Stylesheet.Fills = new Fills(); - } - - ResolveFillWithPattern(workbookStylesPart.Stylesheet.Fills, PatternValues.None); - ResolveFillWithPattern(workbookStylesPart.Stylesheet.Fills, PatternValues.Gray125); - - var allSharedFills = new Dictionary(); - foreach (var fillInfo in sharedFills.Values) - { - Int32 fillId = 0; - Boolean foundOne = false; - foreach (Fill f in workbookStylesPart.Stylesheet.Fills) - { - if (FillsAreEqual(f, fillInfo.Fill)) - { - foundOne = true; - break; - } - fillId++; - } - if (!foundOne) - { - Fill fill = GetNewFill(fillInfo); - workbookStylesPart.Stylesheet.Fills.AppendChild(fill); - } - allSharedFills.Add(fillInfo.Fill, new FillInfo {Fill = fillInfo.Fill, FillId = (UInt32) fillId}); - } - - workbookStylesPart.Stylesheet.Fills.Count = (UInt32) workbookStylesPart.Stylesheet.Fills.Count(); - return allSharedFills; - } - - private static void ResolveFillWithPattern(Fills fills, PatternValues patternValues) - { - if (!fills.Elements().Any(f => - f.PatternFill.PatternType == patternValues - && f.PatternFill.ForegroundColor == null - && f.PatternFill.BackgroundColor == null - )) - { - Fill fill1 = new Fill(); - PatternFill patternFill1 = new PatternFill {PatternType = patternValues}; - fill1.AppendChild(patternFill1); - fills.AppendChild(fill1); - } - } - - private static Fill GetNewFill(FillInfo fillInfo) - { - Fill fill = new Fill(); - - PatternFill patternFill = new PatternFill {PatternType = fillInfo.Fill.PatternType.ToOpenXml()}; - ForegroundColor foregroundColor = new ForegroundColor(); - if (fillInfo.Fill.PatternColor.ColorType == XLColorType.Color) - { - foregroundColor.Rgb = fillInfo.Fill.PatternColor.Color.ToHex(); - } - else if (fillInfo.Fill.PatternColor.ColorType == XLColorType.Indexed) - { - foregroundColor.Indexed = (UInt32) fillInfo.Fill.PatternColor.Indexed; - } - else - { - foregroundColor.Theme = (UInt32) fillInfo.Fill.PatternColor.ThemeColor; - if (fillInfo.Fill.PatternColor.ThemeTint != 1) - { - foregroundColor.Tint = fillInfo.Fill.PatternColor.ThemeTint; - } - } - BackgroundColor backgroundColor = new BackgroundColor(); - if (fillInfo.Fill.PatternBackgroundColor.ColorType == XLColorType.Color) - { - backgroundColor.Rgb = fillInfo.Fill.PatternBackgroundColor.Color.ToHex(); - } - else if (fillInfo.Fill.PatternBackgroundColor.ColorType == XLColorType.Indexed) - { - backgroundColor.Indexed = (UInt32) fillInfo.Fill.PatternBackgroundColor.Indexed; - } - else - { - backgroundColor.Theme = (UInt32) fillInfo.Fill.PatternBackgroundColor.ThemeColor; - if (fillInfo.Fill.PatternBackgroundColor.ThemeTint != 1) - { - backgroundColor.Tint = fillInfo.Fill.PatternBackgroundColor.ThemeTint; - } - } - - patternFill.AppendChild(foregroundColor); - patternFill.AppendChild(backgroundColor); - - fill.AppendChild(patternFill); - - return fill; - } - - private bool FillsAreEqual(Fill f, IXLFill xlFill) - { - var nF = new XLFill(); - if (f.PatternFill != null) - { - if (f.PatternFill.PatternType != null) - { - nF.PatternType = f.PatternFill.PatternType.Value.ToClosedXml(); - } - - var fColor = GetColor(f.PatternFill.ForegroundColor); - if (fColor.HasValue) - { - nF.PatternColor = fColor; - } - - var bColor = GetColor(f.PatternFill.BackgroundColor); - if (bColor.HasValue) - { - nF.PatternBackgroundColor = bColor; - } - } - return nF.Equals(xlFill); - } - - private void ResolveFonts(WorkbookStylesPart workbookStylesPart, SaveContext context) - { - if (workbookStylesPart.Stylesheet.Fonts == null) - { - workbookStylesPart.Stylesheet.Fonts = new Fonts(); - } - - var newFonts = new Dictionary(); - foreach (var fontInfo in context.SharedFonts.Values) - { - Int32 fontId = 0; - Boolean foundOne = false; - foreach (Font f in workbookStylesPart.Stylesheet.Fonts) - { - if (FontsAreEqual(f, fontInfo.Font)) - { - foundOne = true; - break; - } - fontId++; - } - if (!foundOne) - { - Font font = GetNewFont(fontInfo); - workbookStylesPart.Stylesheet.Fonts.AppendChild(font); - } - newFonts.Add(fontInfo.Font, new FontInfo { Font = fontInfo.Font, FontId = (UInt32)fontId }); - } - context.SharedFonts.Clear(); - foreach (var kp in newFonts) - context.SharedFonts.Add(kp.Key, kp.Value); - - workbookStylesPart.Stylesheet.Fonts.Count = (UInt32) workbookStylesPart.Stylesheet.Fonts.Count(); - } - - private Font GetNewFont(FontInfo fontInfo) - { - Font font = new Font(); - Bold bold = fontInfo.Font.Bold ? new Bold() : null; - Italic italic = fontInfo.Font.Italic ? new Italic() : null; - Underline underline = fontInfo.Font.Underline != XLFontUnderlineValues.None - ? new Underline {Val = fontInfo.Font.Underline.ToOpenXml()} - : null; - Strike strike = fontInfo.Font.Strikethrough ? new Strike() : null; - VerticalTextAlignment verticalAlignment = new VerticalTextAlignment {Val = fontInfo.Font.VerticalAlignment.ToOpenXml()}; - Shadow shadow = fontInfo.Font.Shadow ? new Shadow() : null; - FontSize fontSize = new FontSize {Val = fontInfo.Font.FontSize}; - Color color = GetNewColor(fontInfo.Font.FontColor); - - FontName fontName = new FontName {Val = fontInfo.Font.FontName}; - FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering {Val = (Int32) fontInfo.Font.FontFamilyNumbering}; - - if (bold != null) - { - font.AppendChild(bold); - } - if (italic != null) - { - font.AppendChild(italic); - } - if (underline != null) - { - font.AppendChild(underline); - } - if (strike != null) - { - font.AppendChild(strike); - } - font.AppendChild(verticalAlignment); - if (shadow != null) - { - font.AppendChild(shadow); - } - font.AppendChild(fontSize); - font.AppendChild(color); - font.AppendChild(fontName); - font.AppendChild(fontFamilyNumbering); - - return font; - } - - private Color GetNewColor(IXLColor xlColor) - { - Color color = new Color(); - if (xlColor.ColorType == XLColorType.Color) - { - color.Rgb = xlColor.Color.ToHex(); - } - else if (xlColor.ColorType == XLColorType.Indexed) - { - color.Indexed = (UInt32) xlColor.Indexed; - } - else - { - color.Theme = (UInt32) xlColor.ThemeColor; - if (xlColor.ThemeTint != 1) - { - color.Tint = xlColor.ThemeTint; - } - } - return color; - } - - private TabColor GetTabColor(IXLColor xlColor) - { - TabColor color = new TabColor(); - if (xlColor.ColorType == XLColorType.Color) - { - color.Rgb = xlColor.Color.ToHex(); - } - else if (xlColor.ColorType == XLColorType.Indexed) - { - color.Indexed = (UInt32) xlColor.Indexed; - } - else - { - color.Theme = (UInt32) xlColor.ThemeColor; - if (xlColor.ThemeTint != 1) - { - color.Tint = xlColor.ThemeTint; - } - } - return color; - } - - private bool FontsAreEqual(Font f, IXLFont xlFont) - { - var nf = new XLFont(); - nf.Bold = f.Bold != null; - nf.Italic = f.Italic != null; - if (f.Underline != null) - { - if (f.Underline.Val != null) - { - nf.Underline = f.Underline.Val.Value.ToClosedXml(); - } - else - { - nf.Underline = XLFontUnderlineValues.Single; - } - } - nf.Strikethrough = f.Strike != null; - if (f.VerticalTextAlignment != null) - { - if (f.VerticalTextAlignment.Val != null) - { - nf.VerticalAlignment = f.VerticalTextAlignment.Val.Value.ToClosedXml(); - } - else - { - nf.VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline; - } - } - nf.Shadow = f.Shadow != null; - if (f.FontSize != null) - { - nf.FontSize = f.FontSize.Val; - } - var fColor = GetColor(f.Color); - if (fColor.HasValue) - { - nf.FontColor = fColor; - } - if (f.FontName != null) - { - nf.FontName = f.FontName.Val; - } - if (f.FontFamilyNumbering != null) - { - nf.FontFamilyNumbering = (XLFontFamilyNumberingValues) f.FontFamilyNumbering.Val.Value; - } - - return nf.Equals(xlFont); - } - - private static Dictionary ResolveNumberFormats(WorkbookStylesPart workbookStylesPart, - Dictionary sharedNumberFormats) - { - if (workbookStylesPart.Stylesheet.NumberingFormats == null) - { - workbookStylesPart.Stylesheet.NumberingFormats = new NumberingFormats(); - } - - var allSharedNumberFormats = new Dictionary(); - foreach (var numberFormatInfo in sharedNumberFormats.Values) - { - Int32 numberingFormatId = 0; - Boolean foundOne = false; - foreach (NumberingFormat nf in workbookStylesPart.Stylesheet.NumberingFormats) - { - if (NumberFormatsAreEqual(nf, numberFormatInfo.NumberFormat)) - { - foundOne = true; - numberingFormatId = (Int32) nf.NumberFormatId.Value; - break; - } - numberingFormatId++; - } - if (!foundOne) - { - NumberingFormat numberingFormat = new NumberingFormat - { - NumberFormatId = (UInt32) numberingFormatId, - FormatCode = numberFormatInfo.NumberFormat.Format - }; - workbookStylesPart.Stylesheet.NumberingFormats.AppendChild(numberingFormat); - } - allSharedNumberFormats.Add(numberFormatInfo.NumberFormat, - new NumberFormatInfo {NumberFormat = numberFormatInfo.NumberFormat, NumberFormatId = numberingFormatId}); - } - workbookStylesPart.Stylesheet.NumberingFormats.Count = (UInt32) workbookStylesPart.Stylesheet.NumberingFormats.Count(); - return allSharedNumberFormats; - } - - private static bool NumberFormatsAreEqual(NumberingFormat nf, IXLNumberFormat xlNumberFormat) - { - var newXLNumberFormat = new XLNumberFormat(); - - if (nf.FormatCode != null && !StringExtensions.IsNullOrWhiteSpace(nf.FormatCode.Value)) - { - newXLNumberFormat.Format = nf.FormatCode.Value; - } - else if (nf.NumberFormatId != null) - { - newXLNumberFormat.NumberFormatId = (Int32) nf.NumberFormatId.Value; - } - - return newXLNumberFormat.Equals(xlNumberFormat); - } - #endregion - #region GenerateWorksheetPartContent - private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet, SaveContext context) - { - #region Worksheet - if (worksheetPart.Worksheet == null) - { - worksheetPart.Worksheet = new Worksheet(); - } - - GenerateTables(xlWorksheet, worksheetPart, context); - - if ( - !worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair("r", - "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) - { - worksheetPart.Worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); - } - #endregion - var cm = new XLWSContentManager(worksheetPart.Worksheet); - #region SheetProperties - if (worksheetPart.Worksheet.SheetProperties == null) - { - worksheetPart.Worksheet.SheetProperties = new SheetProperties(); - } - - if (xlWorksheet.TabColor.HasValue) - { - worksheetPart.Worksheet.SheetProperties.TabColor = GetTabColor(xlWorksheet.TabColor); - } - else - { - worksheetPart.Worksheet.SheetProperties.TabColor = null; - } - - cm.SetElement(XLWSContentManager.XLWSContents.SheetProperties, worksheetPart.Worksheet.SheetProperties); - - if (worksheetPart.Worksheet.SheetProperties.OutlineProperties == null) - { - worksheetPart.Worksheet.SheetProperties.OutlineProperties = new OutlineProperties(); - } - - worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryBelow = (xlWorksheet.Outline.SummaryVLocation == - XLOutlineSummaryVLocation.Bottom); - worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryRight = (xlWorksheet.Outline.SummaryHLocation == - XLOutlineSummaryHLocation.Right); - - if (worksheetPart.Worksheet.SheetProperties.PageSetupProperties == null && - (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0)) - { - worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties(); - } - - if (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0) - { - worksheetPart.Worksheet.SheetProperties.PageSetupProperties.FitToPage = true; - } - #endregion - UInt32 maxColumn = 0; - UInt32 maxRow = 0; - - String sheetDimensionReference = "A1"; - if (xlWorksheet.Internals.CellsCollection.Count > 0) - { - maxColumn = (UInt32) xlWorksheet.Internals.CellsCollection.Select(c => c.Key.ColumnNumber).Max(); - maxRow = (UInt32) xlWorksheet.Internals.CellsCollection.Select(c => c.Key.RowNumber).Max(); - sheetDimensionReference = "A1:" + ExcelHelper.GetColumnLetterFromNumber((Int32) maxColumn) + ((Int32) maxRow).ToStringLookup(); - } - - if (xlWorksheet.Internals.ColumnsCollection.Count > 0) - { - UInt32 maxColCollection = (UInt32) xlWorksheet.Internals.ColumnsCollection.Keys.Max(); - if (maxColCollection > maxColumn) - { - maxColumn = maxColCollection; - } - } - - if (xlWorksheet.Internals.RowsCollection.Count > 0) - { - UInt32 maxRowCollection = (UInt32) xlWorksheet.Internals.RowsCollection.Keys.Max(); - if (maxRowCollection > maxRow) - { - maxRow = maxRowCollection; - } - } - #region SheetViews - if (worksheetPart.Worksheet.SheetDimension == null) - { - worksheetPart.Worksheet.SheetDimension = new SheetDimension() {Reference = sheetDimensionReference}; - } - - cm.SetElement(XLWSContentManager.XLWSContents.SheetDimension, worksheetPart.Worksheet.SheetDimension); - - if (worksheetPart.Worksheet.SheetViews == null) - { - worksheetPart.Worksheet.SheetViews = new SheetViews(); - } - - cm.SetElement(XLWSContentManager.XLWSContents.SheetViews, worksheetPart.Worksheet.SheetViews); - - SheetView sheetView = (SheetView) worksheetPart.Worksheet.SheetViews.FirstOrDefault(); - if (sheetView == null) - { - sheetView = new SheetView() {WorkbookViewId = (UInt32Value) 0U}; - worksheetPart.Worksheet.SheetViews.AppendChild(sheetView); - } - - sheetView.TabSelected = xlWorksheet.TabSelected; - - if (xlWorksheet.ShowFormulas) - { - sheetView.ShowFormulas = true; - } - else - { - sheetView.ShowFormulas = null; - } - - if (xlWorksheet.ShowGridLines) - { - sheetView.ShowGridLines = null; - } - else - { - sheetView.ShowGridLines = false; - } - - if (xlWorksheet.ShowOutlineSymbols) - { - sheetView.ShowOutlineSymbols = null; - } - else - { - sheetView.ShowOutlineSymbols = false; - } - - if (xlWorksheet.ShowRowColHeaders) - { - sheetView.ShowRowColHeaders = null; - } - else - { - sheetView.ShowRowColHeaders = false; - } - - if (xlWorksheet.ShowRuler) - { - sheetView.ShowRuler = null; - } - else - { - sheetView.ShowRuler = false; - } - - if (xlWorksheet.ShowWhiteSpace) - { - sheetView.ShowWhiteSpace = null; - } - else - { - sheetView.ShowWhiteSpace = false; - } - - if (xlWorksheet.ShowZeros) - { - sheetView.ShowZeros = null; - } - else - { - sheetView.ShowZeros = false; - } - - var pane = sheetView.Elements().FirstOrDefault(); - if (pane == null) - { - pane = new Pane(); - sheetView.AppendChild(pane); - } - - Double hSplit = 0; - Double ySplit = 0; - //if (xlWorksheet.SheetView.FreezePanes) - //{ - pane.State = PaneStateValues.FrozenSplit; - hSplit = xlWorksheet.SheetView.SplitColumn; - ySplit = xlWorksheet.SheetView.SplitRow; - //} - //else - //{ - // pane.State = null; - // foreach (var column in xlWorksheet.Columns(1, xlWorksheet.SheetView.SplitColumn)) - // { - // hSplit += (column.Width * 141.33); - // } - // foreach (var row in xlWorksheet.Rows(1, xlWorksheet.SheetView.SplitRow)) - // { - // ySplit += (row.Height * 37.0); - // } - //} - - pane.HorizontalSplit = hSplit; - pane.VerticalSplit = ySplit; - - pane.TopLeftCell = ExcelHelper.GetColumnLetterFromNumber(xlWorksheet.SheetView.SplitColumn + 1) - + (xlWorksheet.SheetView.SplitRow + 1).ToString(); - - if (hSplit == 0 && ySplit == 0) - { - sheetView.RemoveAllChildren(); - } - #endregion - var maxOutlineColumn = 0; - if (xlWorksheet.ColumnCount() > 0) - { - maxOutlineColumn = xlWorksheet.GetMaxColumnOutline(); - } - - var maxOutlineRow = 0; - if (xlWorksheet.RowCount() > 0) - { - maxOutlineRow = xlWorksheet.GetMaxRowOutline(); - } - #region SheetFormatProperties - if (worksheetPart.Worksheet.SheetFormatProperties == null) - { - worksheetPart.Worksheet.SheetFormatProperties = new SheetFormatProperties(); - } - - cm.SetElement(XLWSContentManager.XLWSContents.SheetFormatProperties, worksheetPart.Worksheet.SheetFormatProperties); - - worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight = xlWorksheet.RowHeight; - worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = xlWorksheet.ColumnWidth; - if (xlWorksheet.RowHeightChanged) - { - worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true; - } - - if (maxOutlineColumn > 0) - { - worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = (byte) maxOutlineColumn; - } - else - { - worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = null; - } - - if (maxOutlineRow > 0) - { - worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = (byte) maxOutlineRow; - } - else - { - worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = null; - } - #endregion - #region Columns - Columns columns = null; - if (xlWorksheet.Internals.CellsCollection.Count == 0 && - xlWorksheet.Internals.ColumnsCollection.Count == 0) - { - worksheetPart.Worksheet.RemoveAllChildren(); - } - else - { - var worksheetColumnWidth = GetColumnWidth(xlWorksheet.ColumnWidth); - - if (!worksheetPart.Worksheet.Elements().Any()) - { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Columns); - worksheetPart.Worksheet.InsertAfter(new Columns(), previousElement); - } - - columns = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.Columns, columns); - - Dictionary sheetColumnsByMin = columns.Elements().ToDictionary(c => c.Min.Value, c => c); - //Dictionary sheetColumnsByMax = columns.Elements().ToDictionary(c => c.Max.Value, c => c); - - Int32 minInColumnsCollection; - Int32 maxInColumnsCollection; - if (xlWorksheet.Internals.ColumnsCollection.Count > 0) - { - minInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Min(); - maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max(); - } - else - { - minInColumnsCollection = 1; - maxInColumnsCollection = 0; - } - - if (minInColumnsCollection > 1) - { - UInt32Value min = 1; - UInt32Value max = (UInt32) (minInColumnsCollection - 1); - var styleId = context.SharedStyles[xlWorksheet.Style].StyleId; - - for (var co = min; co <= max; co++) - { - Column column = new Column() - { - Min = co, - Max = co, - Style = styleId, - Width = worksheetColumnWidth, - CustomWidth = true - }; - - UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax); - } - } - - for (var co = minInColumnsCollection; co <= maxInColumnsCollection; co++) - { - UInt32 styleId; - Double columnWidth; - Boolean isHidden = false; - Boolean collapsed = false; - Int32 outlineLevel = 0; - if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co)) - { - styleId = context.SharedStyles[xlWorksheet.Internals.ColumnsCollection[co].Style].StyleId; - columnWidth = GetColumnWidth(xlWorksheet.Internals.ColumnsCollection[co].Width); - isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden; - collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed; - outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel; - } - else - { - styleId = context.SharedStyles[xlWorksheet.Style].StyleId; - columnWidth = worksheetColumnWidth; - } - - Column column = new Column() - { - Min = (UInt32) co, - Max = (UInt32) co, - Style = styleId, - Width = columnWidth, - CustomWidth = true - }; - if (isHidden) - { - column.Hidden = true; - } - if (collapsed) - { - column.Collapsed = true; - } - if (outlineLevel > 0) - { - column.OutlineLevel = (byte) outlineLevel; - } - - UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax); - } - - foreach (var col in columns.Elements().Where(c => c.Min > (UInt32) (maxInColumnsCollection)).OrderBy(c => c.Min.Value)) - { - col.Style = context.SharedStyles[xlWorksheet.Style].StyleId; - col.Width = worksheetColumnWidth; - col.CustomWidth = true; - if ((Int32) col.Max.Value > maxInColumnsCollection) - { - maxInColumnsCollection = (Int32) col.Max.Value; - } - } - - if (maxInColumnsCollection < ExcelHelper.MaxColumnNumber) - { - Column column = new Column() - { - Min = (UInt32) (maxInColumnsCollection + 1), - Max = (UInt32) (ExcelHelper.MaxColumnNumber), - Style = context.SharedStyles[xlWorksheet.Style].StyleId, - Width = worksheetColumnWidth, - CustomWidth = true - }; - columns.AppendChild(column); - } - - CollapseColumns(columns, sheetColumnsByMin); - } - #endregion - #region SheetData - SheetData sheetData; - if (!worksheetPart.Worksheet.Elements().Any()) - { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetData); - worksheetPart.Worksheet.InsertAfter(new SheetData(), previousElement); - } - - sheetData = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData); - - var cellsByRow = new Dictionary>(); - foreach (var c in xlWorksheet.Internals.CellsCollection.Values) - { - Int32 rowNum = c.Address.RowNumber; - if (!cellsByRow.ContainsKey(rowNum)) - { - cellsByRow.Add(rowNum, new List()); - } - - cellsByRow[rowNum].Add(c); - } - - var sheetDataRows = sheetData.Elements().ToDictionary(r => (Int32) r.RowIndex.Value, r => r); - foreach (var r in xlWorksheet.Internals.RowsCollection.Deleted) - { - if (sheetDataRows.ContainsKey(r.Key)) - { - sheetData.RemoveChild(sheetDataRows[r.Key]); - sheetDataRows.Remove(r.Key); - } - } - - var distinctRows = cellsByRow.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys); - Boolean noRows = (sheetData.Elements().FirstOrDefault() == null); - foreach (var distinctRow in distinctRows.OrderBy(r => r)) - { - Row row; // = sheetData.Elements().FirstOrDefault(r => r.RowIndex.Value == (UInt32)distinctRow); - if (sheetDataRows.ContainsKey(distinctRow)) - { - row = sheetDataRows[distinctRow]; - } - else - { - row = new Row() {RowIndex = (UInt32) distinctRow}; - if (noRows) - { - sheetData.AppendChild(row); - noRows = false; - } - else - { - if (sheetDataRows.Any(r => r.Key > row.RowIndex.Value)) - { - var minRow = sheetDataRows.Where(r => r.Key > (Int32) row.RowIndex.Value).Min(r => r.Key); - Row rowBeforeInsert = sheetDataRows[minRow]; - sheetData.InsertBefore(row, rowBeforeInsert); - } - else - { - sheetData.AppendChild(row); - } - } - } - - if (maxColumn > 0) - { - row.Spans = new ListValue() {InnerText = "1:" + maxColumn.ToString()}; - } - - row.Height = null; - row.CustomHeight = null; - row.Hidden = null; - row.StyleIndex = null; - row.CustomFormat = null; - row.Collapsed = null; - if (xlWorksheet.Internals.RowsCollection.ContainsKey(distinctRow)) - { - var thisRow = xlWorksheet.Internals.RowsCollection[distinctRow]; - if (thisRow.Height != xlWorksheet.RowHeight) - { - row.Height = thisRow.Height; - row.CustomHeight = true; - } - if (!thisRow.Style.Equals(xlWorksheet.Style)) - { - row.StyleIndex = context.SharedStyles[thisRow.Style].StyleId; - row.CustomFormat = true; - } - if (thisRow.IsHidden) - { - row.Hidden = true; - } - if (thisRow.Collapsed) - { - row.Collapsed = true; - } - if (thisRow.OutlineLevel > 0) - { - row.OutlineLevel = (byte) thisRow.OutlineLevel; - } - } - else - { - //row.Height = xlWorksheet.RowHeight; - //row.CustomHeight = true; - //row.Hidden = false; - } - - var cellsByReference = row.Elements().ToDictionary(c => c.CellReference.Value, c => c); - - foreach (var c in xlWorksheet.Internals.CellsCollection.Deleted) - { - if (cellsByReference.ContainsKey(c.Key.ToStringRelative())) - { - row.RemoveChild(cellsByReference[c.Key.ToStringRelative()]); - } - } - - //List cellsToRemove = new List(); - //foreach (var cell in row.Elements()) - //{ - // var cellReference = cell.CellReference; - // if (xlWorksheet.Internals.CellsCollection.Deleted.ContainsKey(XLAddress.Create(xlWorksheet, cellReference))) - // cellsToRemove.Add(cell); - //} - //cellsToRemove.ForEach(cell => row.RemoveChild(cell)); - - if (cellsByRow.ContainsKey(distinctRow)) - { - Boolean isNewRow = !row.Elements().Any(); - foreach (var opCell in cellsByRow[distinctRow] - .OrderBy(c => c.Address.ColumnNumber) - .Select(c => (XLCell) c)) - { - var styleId = context.SharedStyles[opCell.Style].StyleId; - - var dataType = opCell.DataType; - var cellReference = ((XLAddress) opCell.Address).GetTrimmedAddress(); - - //Boolean isNewCell = false; - - Cell cell; - if (cellsByReference.ContainsKey(cellReference)) - { - cell = cellsByReference[cellReference]; - } - else - { - //isNewCell = true; - cell = new Cell() {CellReference = new StringValue(cellReference)}; - if (isNewRow) - { - row.AppendChild(cell); - } - else - { - Int32 newColumn = ExcelHelper.GetColumnNumberFromAddress1(cellReference); - - Cell cellBeforeInsert = null; - Int32 lastCo = Int32.MaxValue; - foreach ( - var c in - row.Elements().Where( - c => ExcelHelper.GetColumnNumberFromAddress1(c.CellReference.Value) > newColumn)) - { - var thidCo = ExcelHelper.GetColumnNumberFromAddress1(c.CellReference.Value); - if (lastCo > thidCo) - { - cellBeforeInsert = c; - lastCo = thidCo; - } - } - if (cellBeforeInsert == null) - { - row.AppendChild(cell); - } - else - { - row.InsertBefore(cell, cellBeforeInsert); - } - } - } - - cell.StyleIndex = styleId; - if (!StringExtensions.IsNullOrWhiteSpace(opCell.FormulaA1)) - { - String formula = opCell.FormulaA1; - if (formula.StartsWith("{")) - { - formula = formula.Substring(1, formula.Length - 2); - cell.CellFormula = new CellFormula(formula); - cell.CellFormula.FormulaType = CellFormulaValues.Array; - cell.CellFormula.Reference = cellReference; - } - else - { - cell.CellFormula = new CellFormula(formula); - } - cell.CellValue = null; - } - else - { - cell.CellFormula = null; - - if (opCell.DataType == XLCellValues.DateTime) - { - cell.DataType = null; - } - else - { - cell.DataType = GetCellValue(opCell); - } - - CellValue cellValue = new CellValue(); - if (dataType == XLCellValues.Text) - { - if (StringExtensions.IsNullOrWhiteSpace(opCell.InnerText)) - { - cell.CellValue = null; - } - else - { - if (opCell.ShareString) - { - cellValue.Text = opCell.SharedStringId.ToString(); - cell.CellValue = cellValue; - } - else - { - cell.InlineString = new InlineString() {Text = new Text(opCell.GetString())}; - } - } - } - else if (dataType == XLCellValues.TimeSpan) - { - TimeSpan timeSpan = opCell.GetTimeSpan(); - cellValue.Text = XLCell.BaseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture); - cell.CellValue = cellValue; - } - else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) - { - cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture); - cell.CellValue = cellValue; - } - else - { - cellValue.Text = opCell.InnerText; - cell.CellValue = cellValue; - } - } - } - } - } - #endregion - #region SheetProtection - SheetProtection sheetProtection = null; - if (xlWorksheet.Protection.Protected) - { - if (!worksheetPart.Worksheet.Elements().Any()) - { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetProtection); - worksheetPart.Worksheet.InsertAfter(new SheetProtection(), previousElement); - } - - sheetProtection = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, sheetProtection); - - var protection = (XLSheetProtection) xlWorksheet.Protection; - sheetProtection.Sheet = protection.Protected; - if (!StringExtensions.IsNullOrWhiteSpace(protection.PasswordHash)) - { - sheetProtection.Password = protection.PasswordHash; - } - sheetProtection.FormatCells = GetBooleanValue(!protection.FormatCells, true); - sheetProtection.FormatColumns = GetBooleanValue(!protection.FormatColumns, true); - sheetProtection.FormatRows = GetBooleanValue(!protection.FormatRows, true); - sheetProtection.InsertColumns = GetBooleanValue(!protection.InsertColumns, true); - sheetProtection.InsertHyperlinks = GetBooleanValue(!protection.InsertHyperlinks, true); - sheetProtection.InsertRows = GetBooleanValue(!protection.InsertRows, true); - sheetProtection.DeleteColumns = GetBooleanValue(!protection.DeleteColumns, true); - sheetProtection.DeleteRows = GetBooleanValue(!protection.DeleteRows, true); - sheetProtection.AutoFilter = GetBooleanValue(!protection.AutoFilter, true); - sheetProtection.PivotTables = GetBooleanValue(!protection.PivotTables, true); - sheetProtection.Sort = GetBooleanValue(!protection.Sort, true); - sheetProtection.SelectLockedCells = GetBooleanValue(!protection.SelectLockedCells, false); - sheetProtection.SelectUnlockedCells = GetBooleanValue(!protection.SelectUnlockedCells, false); - } - else - { - worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, null); - } - #endregion - #region AutoFilter - if (xlWorksheet.AutoFilterRange != null) - { - if (!worksheetPart.Worksheet.Elements().Any()) - { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.AutoFilter); - worksheetPart.Worksheet.InsertAfter(new AutoFilter(), previousElement); - } - - var autoFilter = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, autoFilter); - - autoFilter.Reference = xlWorksheet.AutoFilterRange.RangeAddress.ToString(); - } - else - { - worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, null); - } - #endregion - #region MergeCells - MergeCells mergeCells = null; - if (xlWorksheet.Internals.MergedRanges.Any()) - { - if (!worksheetPart.Worksheet.Elements().Any()) - { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.MergeCells); - worksheetPart.Worksheet.InsertAfter(new MergeCells(), previousElement); - } - - mergeCells = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, mergeCells); - mergeCells.RemoveAllChildren(); - - foreach (SheetRange mergedRange in xlWorksheet.Internals.MergedRanges) - { - var mergeCell = new MergeCell() { Reference = mergedRange.ToStringA1() }; - mergeCells.AppendChild(mergeCell); - } - - mergeCells.Count = (UInt32) mergeCells.Count(); - } - else - { - worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, null); - } - #endregion - #region DataValidations - DataValidations dataValidations = null; - - if (!xlWorksheet.DataValidations.Any()) - { - worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, null); - } - else - { - worksheetPart.Worksheet.Elements().FirstOrDefault(); - if (!worksheetPart.Worksheet.Elements().Any()) - { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.DataValidations); - worksheetPart.Worksheet.InsertAfter(new DataValidations(), previousElement); - } - - dataValidations = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, dataValidations); - dataValidations.RemoveAllChildren(); - foreach (var dv in xlWorksheet.DataValidations) - { - String sequence = String.Empty; - foreach (var r in dv.Ranges) - { - sequence += r.RangeAddress.ToString() + " "; - } - - if (sequence.Length > 0) - { - sequence = sequence.Substring(0, sequence.Length - 1); - } - - DataValidation dataValidation = new DataValidation() - { - AllowBlank = dv.IgnoreBlanks, - Formula1 = new Formula1(dv.MinValue), - Formula2 = new Formula2(dv.MaxValue), - Type = dv.AllowedValues.ToOpenXml(), - ShowErrorMessage = dv.ShowErrorMessage, - Prompt = dv.InputMessage, - PromptTitle = dv.InputTitle, - ErrorTitle = dv.ErrorTitle, - Error = dv.ErrorMessage, - ShowDropDown = !dv.InCellDropdown, - ShowInputMessage = dv.ShowInputMessage, - ErrorStyle = dv.ErrorStyle.ToOpenXml(), - Operator = dv.Operator.ToOpenXml(), - SequenceOfReferences = new ListValue() {InnerText = sequence} - }; - - dataValidations.AppendChild(dataValidation); - } - dataValidations.Count = (UInt32) xlWorksheet.DataValidations.Count(); - } - #endregion - #region Hyperlinks - Hyperlinks hyperlinks = null; - var relToRemove = worksheetPart.HyperlinkRelationships.ToList(); - relToRemove.ForEach(h => worksheetPart.DeleteReferenceRelationship(h)); - if (!xlWorksheet.Hyperlinks.Any()) - { - worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, null); - } - else - { - worksheetPart.Worksheet.Elements().FirstOrDefault(); - if (!worksheetPart.Worksheet.Elements().Any()) - { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Hyperlinks); - worksheetPart.Worksheet.InsertAfter(new Hyperlinks(), previousElement); - } - - hyperlinks = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, hyperlinks); - hyperlinks.RemoveAllChildren(); - foreach (var hl in xlWorksheet.Hyperlinks) - { - Hyperlink hyperlink; - if (hl.IsExternal) - { - String rId = context.RelIdGenerator.GetNext(RelType.Workbook); - hyperlink = new Hyperlink() {Reference = hl.Cell.Address.ToString(), Id = rId}; - worksheetPart.AddHyperlinkRelationship(hl.ExternalAddress, true, rId); - } - else - { - hyperlink = new Hyperlink() - { - Reference = hl.Cell.Address.ToString(), - Location = hl.InternalAddress, - Display = hl.Cell.GetFormattedString() - }; - } - if (!StringExtensions.IsNullOrWhiteSpace(hl.Tooltip)) - { - hyperlink.Tooltip = hl.Tooltip; - } - hyperlinks.AppendChild(hyperlink); - } - } - #endregion - #region PrintOptions - PrintOptions printOptions = null; - if (!worksheetPart.Worksheet.Elements().Any()) - { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PrintOptions); - worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement); - } - - printOptions = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.PrintOptions, printOptions); - - printOptions.HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally; - printOptions.VerticalCentered = xlWorksheet.PageSetup.CenterVertically; - printOptions.Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings; - printOptions.GridLines = xlWorksheet.PageSetup.ShowGridlines; - #endregion - #region PageMargins - if (!worksheetPart.Worksheet.Elements().Any()) - { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageMargins); - worksheetPart.Worksheet.InsertAfter(new PageMargins(), previousElement); - } - - PageMargins pageMargins = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.PageMargins, pageMargins); - pageMargins.Left = xlWorksheet.PageSetup.Margins.Left; - pageMargins.Right = xlWorksheet.PageSetup.Margins.Right; - pageMargins.Top = xlWorksheet.PageSetup.Margins.Top; - pageMargins.Bottom = xlWorksheet.PageSetup.Margins.Bottom; - pageMargins.Header = xlWorksheet.PageSetup.Margins.Header; - pageMargins.Footer = xlWorksheet.PageSetup.Margins.Footer; - #endregion - #region PageSetup - if (!worksheetPart.Worksheet.Elements().Any()) - { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageSetup); - worksheetPart.Worksheet.InsertAfter(new PageSetup(), previousElement); - } - - PageSetup pageSetup = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.PageSetup, pageSetup); - - pageSetup.Orientation = xlWorksheet.PageSetup.PageOrientation.ToOpenXml(); - pageSetup.PaperSize = (UInt32) xlWorksheet.PageSetup.PaperSize; - pageSetup.BlackAndWhite = xlWorksheet.PageSetup.BlackAndWhite; - pageSetup.Draft = xlWorksheet.PageSetup.DraftQuality; - pageSetup.PageOrder = xlWorksheet.PageSetup.PageOrder.ToOpenXml(); - pageSetup.CellComments = xlWorksheet.PageSetup.ShowComments.ToOpenXml(); - pageSetup.Errors = xlWorksheet.PageSetup.PrintErrorValue.ToOpenXml(); - - if (xlWorksheet.PageSetup.FirstPageNumber > 0) - { - pageSetup.FirstPageNumber = (UInt32) xlWorksheet.PageSetup.FirstPageNumber; - pageSetup.UseFirstPageNumber = true; - } - else - { - pageSetup.FirstPageNumber = null; - pageSetup.UseFirstPageNumber = null; - } - - if (xlWorksheet.PageSetup.HorizontalDpi > 0) - { - pageSetup.HorizontalDpi = (UInt32) xlWorksheet.PageSetup.HorizontalDpi; - } - else - { - pageSetup.HorizontalDpi = null; - } - - if (xlWorksheet.PageSetup.VerticalDpi > 0) - { - pageSetup.VerticalDpi = (UInt32) xlWorksheet.PageSetup.VerticalDpi; - } - else - { - pageSetup.VerticalDpi = null; - } - - if (xlWorksheet.PageSetup.Scale > 0) - { - pageSetup.Scale = (UInt32) xlWorksheet.PageSetup.Scale; - pageSetup.FitToWidth = null; - pageSetup.FitToHeight = null; - } - else - { - pageSetup.Scale = null; - - if (xlWorksheet.PageSetup.PagesWide > 0) - { - pageSetup.FitToWidth = (UInt32) xlWorksheet.PageSetup.PagesWide; - } - else - { - pageSetup.FitToWidth = 0; - } - - if (xlWorksheet.PageSetup.PagesTall > 0) - { - pageSetup.FitToHeight = (UInt32) xlWorksheet.PageSetup.PagesTall; - } - else - { - pageSetup.FitToHeight = 0; - } - } - #endregion - #region HeaderFooter - if (!worksheetPart.Worksheet.Elements().Any()) - { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.HeaderFooter); - worksheetPart.Worksheet.InsertAfter(new HeaderFooter(), previousElement); - } - - HeaderFooter headerFooter = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.HeaderFooter, headerFooter); - headerFooter.RemoveAllChildren(); - - headerFooter.ScaleWithDoc = xlWorksheet.PageSetup.ScaleHFWithDocument; - headerFooter.AlignWithMargins = xlWorksheet.PageSetup.AlignHFWithMargins; - headerFooter.DifferentFirst = true; - headerFooter.DifferentOddEven = true; - - OddHeader oddHeader = new OddHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.OddPages)); - headerFooter.AppendChild(oddHeader); - OddFooter oddFooter = new OddFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.OddPages)); - headerFooter.AppendChild(oddFooter); - - EvenHeader evenHeader = new EvenHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.EvenPages)); - headerFooter.AppendChild(evenHeader); - EvenFooter evenFooter = new EvenFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.EvenPages)); - headerFooter.AppendChild(evenFooter); - - FirstHeader firstHeader = new FirstHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.FirstPage)); - headerFooter.AppendChild(firstHeader); - FirstFooter firstFooter = new FirstFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.FirstPage)); - headerFooter.AppendChild(firstFooter); - - //if (!headerFooter.Any(hf => hf.InnerText.Length > 0)) - // worksheetPart.Worksheet.RemoveAllChildren(); - #endregion - #region RowBreaks - if (!worksheetPart.Worksheet.Elements().Any()) - { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks); - worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement); - } - - RowBreaks rowBreaks = worksheetPart.Worksheet.Elements().First(); - - var rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count; - if (rowBreakCount > 0) - { - rowBreaks.Count = (UInt32) rowBreakCount; - rowBreaks.ManualBreakCount = (UInt32) rowBreakCount; - foreach (var rb in xlWorksheet.PageSetup.RowBreaks) - { - Break break1 = new Break() - {Id = (UInt32) rb, Max = (UInt32) xlWorksheet.RangeAddress.LastAddress.RowNumber, ManualPageBreak = true}; - rowBreaks.AppendChild(break1); - } - cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, rowBreaks); - } - else - { - worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, null); - } - #endregion - #region ColumnBreaks - if (!worksheetPart.Worksheet.Elements().Any()) - { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks); - worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement); - } - - ColumnBreaks columnBreaks = worksheetPart.Worksheet.Elements().First(); - - var columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count; - if (columnBreakCount > 0) - { - columnBreaks.Count = (UInt32) columnBreakCount; - columnBreaks.ManualBreakCount = (UInt32) columnBreakCount; - foreach (var cb in xlWorksheet.PageSetup.ColumnBreaks) - { - Break break1 = new Break() - {Id = (UInt32) cb, Max = (UInt32) xlWorksheet.RangeAddress.LastAddress.ColumnNumber, ManualPageBreak = true}; - columnBreaks.AppendChild(break1); - } - cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, columnBreaks); - } - else - { - worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, null); - } - #endregion - #region Drawings - //worksheetPart.Worksheet.RemoveAllChildren(); - //{ - // OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Drawing); - // worksheetPart.Worksheet.InsertAfter(new Drawing() { Id = String.Format("rId{0}", 1) }, previousElement); - //} - - //Drawing drawing = worksheetPart.Worksheet.Elements().First(); - //cm.SetElement(XLWSContentManager.XLWSContents.Drawing, drawing); - #endregion - #region Tables - worksheetPart.Worksheet.RemoveAllChildren(); - { - OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts); - worksheetPart.Worksheet.InsertAfter(new TableParts(), previousElement); - } - - TableParts tableParts = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts); - - tableParts.Count = (UInt32) xlWorksheet.Tables.Count(); - foreach (var table in xlWorksheet.Tables) - { - var xlTable = (XLTable) table; - var tablePart = new TablePart() {Id = xlTable.RelId}; - tableParts.AppendChild(tablePart); - } - #endregion - } - - private static BooleanValue GetBooleanValue(bool value, bool defaultValue) - { - return value == defaultValue ? null : new BooleanValue(value); - } - - private void CollapseColumns(Columns columns, Dictionary sheetColumns) - { - UInt32 lastMax = 1; - UInt32 lastMin = 1; - Int32 count = sheetColumns.Count; - foreach (var kp in sheetColumns.OrderBy(kp => kp.Key)) - { - if (kp.Key < count && ColumnsAreEqual(kp.Value, sheetColumns[kp.Key + 1])) - { - lastMax = kp.Key; - } - else - { - var newColumn = (Column) kp.Value.CloneNode(true); - newColumn.Min = lastMin; - var columnsToRemove = new List(); - foreach (var c in columns.Elements().Where(co => co.Min >= newColumn.Min && co.Max <= newColumn.Max).Select(co => co)) - { - columnsToRemove.Add(c); - } - columnsToRemove.ForEach(c => columns.RemoveChild(c)); - - columns.AppendChild(newColumn); - - lastMin = kp.Key + 1; - } - } - } - - private static double GetColumnWidth(double columnWidth) - { - if (columnWidth > 0) - { - return columnWidth + COLUMN_WIDTH_OFFSET; - } - return columnWidth; - } - - private static void UpdateColumn(Column column, Columns columns, Dictionary sheetColumnsByMin) - //, Dictionary sheetColumnsByMax) - { - UInt32 co = column.Min.Value; - Column newColumn; - Column existingColumn; // = columns.Elements().FirstOrDefault(c => c.Min.Value == column.Min.Value); - if (!sheetColumnsByMin.ContainsKey(co)) - { - //if (sheetColumnsByMin.ContainsKey(co + 1) && ColumnsAreEqual(column, sheetColumnsByMin[co + 1])) - //{ - // var thisColumn = sheetColumnsByMin[co + 1]; - // thisColumn.Min -= 1; - // sheetColumnsByMin.Remove(co + 1); - // sheetColumnsByMin.Add(co, thisColumn); - //} - //else if (sheetColumnsByMax.ContainsKey(co - 1) && ColumnsAreEqual(column, sheetColumnsByMin[co - 1])) - //{ - // var thisColumn = sheetColumnsByMin[co - 1]; - // thisColumn.Max += 1; - // sheetColumnsByMax.Remove(co - 1); - // sheetColumnsByMax.Add(co, thisColumn); - //} - //else - //{ - newColumn = (Column) column.CloneNode(true); - columns.AppendChild(newColumn); - sheetColumnsByMin.Add(co, newColumn); - // sheetColumnsByMax.Add(co, newColumn); - //} - } - else - { - existingColumn = sheetColumnsByMin[column.Min.Value]; - newColumn = (Column) existingColumn.CloneNode(true); - //newColumn = new Column() { InnerXml = existingColumn.InnerXml }; - newColumn.Min = column.Min; - newColumn.Max = column.Max; - newColumn.Style = column.Style; - newColumn.Width = column.Width; - newColumn.CustomWidth = column.CustomWidth; - - if (column.Hidden != null) - { - newColumn.Hidden = true; - } - else - { - newColumn.Hidden = null; - } - - if (column.Collapsed != null) - { - newColumn.Collapsed = true; - } - else - { - newColumn.Collapsed = null; - } - - if (column.OutlineLevel != null && column.OutlineLevel > 0) - { - newColumn.OutlineLevel = (byte) column.OutlineLevel; - } - else - { - newColumn.OutlineLevel = null; - } - - sheetColumnsByMin.Remove(column.Min.Value); - if (existingColumn.Min + 1 > existingColumn.Max) - { - //existingColumn.Min = existingColumn.Min + 1; - //columns.InsertBefore(existingColumn, newColumn); - //existingColumn.Remove(); - columns.RemoveChild(existingColumn); - columns.AppendChild(newColumn); - sheetColumnsByMin.Add(newColumn.Min.Value, newColumn); - } - else - { - //columns.InsertBefore(existingColumn, newColumn); - columns.AppendChild(newColumn); - sheetColumnsByMin.Add(newColumn.Min.Value, newColumn); - existingColumn.Min = existingColumn.Min + 1; - sheetColumnsByMin.Add(existingColumn.Min.Value, existingColumn); - } - } - } - private static bool ColumnsAreEqual(Column left, Column right) - { - return - left.Style.Value == right.Style.Value - && left.Width.Value == right.Width.Value - && ((left.Hidden == null && right.Hidden == null) - || (left.Hidden != null && right.Hidden != null && left.Hidden.Value == right.Hidden.Value)) - && ((left.Collapsed == null && right.Collapsed == null) - || (left.Collapsed != null && right.Collapsed != null && left.Collapsed.Value == right.Collapsed.Value)) - && ((left.OutlineLevel == null && right.OutlineLevel == null) - || (left.OutlineLevel != null && right.OutlineLevel != null && left.OutlineLevel.Value == right.OutlineLevel.Value)); - } - #endregion private void GenerateCalculationChainPartContent(WorkbookPart workbookPart, SaveContext context) { - var thisRelId = context.RelIdGenerator.GetNext(RelType.Workbook); + string thisRelId = context.RelIdGenerator.GetNext(RelType.Workbook); if (workbookPart.CalculationChainPart == null) - { workbookPart.AddNewPart(thisRelId); - } if (workbookPart.CalculationChainPart.CalculationChain == null) - { workbookPart.CalculationChainPart.CalculationChain = new CalculationChain(); - } - CalculationChain calculationChain = workbookPart.CalculationChainPart.CalculationChain; + var calculationChain = workbookPart.CalculationChainPart.CalculationChain; calculationChain.RemoveAllChildren(); //var calculationCells = new Dictionary>(); //foreach(var calculationCell in calculationChain.Elements().Where(cc => cc.CellReference != null)) @@ -2928,15 +757,13 @@ // calculationCells[cellReference].Add(calculationCell); //} - foreach (var worksheet in WorksheetsInternal) + foreach (XLWorksheet worksheet in WorksheetsInternal) { var cellsWithoutFormulas = new HashSet(); - foreach (var c in worksheet.Internals.CellsCollection.Values) + foreach (XLCell c in worksheet.Internals.CellsCollection.Values) { if (StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)) - { cellsWithoutFormulas.Add(c.Address.ToStringRelative()); - } else { //var calculationCells = calculationChain.Elements().Where( @@ -2947,13 +774,20 @@ if (c.FormulaA1.StartsWith("{")) { calculationChain.AppendChild(new CalculationCell - {CellReference = c.Address.ToString(), SheetId = worksheet.SheetId, Array = true}); - calculationChain.AppendChild(new CalculationCell {CellReference = c.Address.ToString(), InChildChain = true}); + { + CellReference = c.Address.ToString(), + SheetId = worksheet.SheetId, + Array = true + }); + calculationChain.AppendChild(new CalculationCell + {CellReference = c.Address.ToString(), InChildChain = true}); } else - { - calculationChain.AppendChild(new CalculationCell {CellReference = c.Address.ToString(), SheetId = worksheet.SheetId}); - } + calculationChain.AppendChild(new CalculationCell + { + CellReference = c.Address.ToString(), + SheetId = worksheet.SheetId + }); } } @@ -2961,9 +795,9 @@ var m = from cc in calculationChain.Elements() where !(cc.SheetId != null || cc.InChildChain != null) && calculationChain.Elements() - .Where(c1 => c1.SheetId != null) - .Select(c1 => c1.CellReference.Value) - .Contains(cc.CellReference.Value) + .Where(c1 => c1.SheetId != null) + .Select(c1 => c1.CellReference.Value) + .Contains(cc.CellReference.Value) || cellsWithoutFormulas.Contains(cc.CellReference.Value) select cc; //m.ToList().ForEach(cc => cCellsToRemove.Add(cc)); @@ -2971,9 +805,7 @@ } if (!calculationChain.Any()) - { workbookPart.DeletePart(workbookPart.CalculationChainPart); - } } private void GenerateThemePartContent(ThemePart themePart) @@ -2986,12 +818,20 @@ var colorScheme1 = new ColorScheme {Name = "Office"}; var dark1Color1 = new Dark1Color(); - var systemColor1 = new SystemColor {Val = SystemColorValues.WindowText, LastColor = Theme.Text1.Color.ToHex().Substring(2)}; + var systemColor1 = new SystemColor + { + Val = SystemColorValues.WindowText, + LastColor = Theme.Text1.Color.ToHex().Substring(2) + }; dark1Color1.AppendChild(systemColor1); var light1Color1 = new Light1Color(); - var systemColor2 = new SystemColor {Val = SystemColorValues.Window, LastColor = Theme.Background1.Color.ToHex().Substring(2)}; + var systemColor2 = new SystemColor + { + Val = SystemColorValues.Window, + LastColor = Theme.Background1.Color.ToHex().Substring(2) + }; light1Color1.AppendChild(systemColor2); @@ -3068,31 +908,31 @@ var supplementalFont2 = new SupplementalFont {Script = "Hang", Typeface = "맑은 고딕"}; var supplementalFont3 = new SupplementalFont {Script = "Hans", Typeface = "宋体"}; var supplementalFont4 = new SupplementalFont {Script = "Hant", Typeface = "新細明體"}; - var supplementalFont5 = new SupplementalFont {Script = "Arab", Typeface = "Times New Roman"}; - var supplementalFont6 = new SupplementalFont {Script = "Hebr", Typeface = "Times New Roman"}; - var supplementalFont7 = new SupplementalFont {Script = "Thai", Typeface = "Tahoma"}; - var supplementalFont8 = new SupplementalFont {Script = "Ethi", Typeface = "Nyala"}; - var supplementalFont9 = new SupplementalFont {Script = "Beng", Typeface = "Vrinda"}; - var supplementalFont10 = new SupplementalFont {Script = "Gujr", Typeface = "Shruti"}; - var supplementalFont11 = new SupplementalFont {Script = "Khmr", Typeface = "MoolBoran"}; - var supplementalFont12 = new SupplementalFont {Script = "Knda", Typeface = "Tunga"}; - var supplementalFont13 = new SupplementalFont {Script = "Guru", Typeface = "Raavi"}; - var supplementalFont14 = new SupplementalFont {Script = "Cans", Typeface = "Euphemia"}; - var supplementalFont15 = new SupplementalFont {Script = "Cher", Typeface = "Plantagenet Cherokee"}; - var supplementalFont16 = new SupplementalFont {Script = "Yiii", Typeface = "Microsoft Yi Baiti"}; - var supplementalFont17 = new SupplementalFont {Script = "Tibt", Typeface = "Microsoft Himalaya"}; - var supplementalFont18 = new SupplementalFont {Script = "Thaa", Typeface = "MV Boli"}; - var supplementalFont19 = new SupplementalFont {Script = "Deva", Typeface = "Mangal"}; - var supplementalFont20 = new SupplementalFont {Script = "Telu", Typeface = "Gautami"}; - var supplementalFont21 = new SupplementalFont {Script = "Taml", Typeface = "Latha"}; - var supplementalFont22 = new SupplementalFont {Script = "Syrc", Typeface = "Estrangelo Edessa"}; - var supplementalFont23 = new SupplementalFont {Script = "Orya", Typeface = "Kalinga"}; - var supplementalFont24 = new SupplementalFont {Script = "Mlym", Typeface = "Kartika"}; - var supplementalFont25 = new SupplementalFont {Script = "Laoo", Typeface = "DokChampa"}; - var supplementalFont26 = new SupplementalFont {Script = "Sinh", Typeface = "Iskoola Pota"}; - var supplementalFont27 = new SupplementalFont {Script = "Mong", Typeface = "Mongolian Baiti"}; - var supplementalFont28 = new SupplementalFont {Script = "Viet", Typeface = "Times New Roman"}; - var supplementalFont29 = new SupplementalFont {Script = "Uigh", Typeface = "Microsoft Uighur"}; + var supplementalFont5 = new SupplementalFont {Script = "Arab", Typeface = "Times New Roman"}; + var supplementalFont6 = new SupplementalFont {Script = "Hebr", Typeface = "Times New Roman"}; + var supplementalFont7 = new SupplementalFont {Script = "Thai", Typeface = "Tahoma"}; + var supplementalFont8 = new SupplementalFont {Script = "Ethi", Typeface = "Nyala"}; + var supplementalFont9 = new SupplementalFont {Script = "Beng", Typeface = "Vrinda"}; + var supplementalFont10 = new SupplementalFont {Script = "Gujr", Typeface = "Shruti"}; + var supplementalFont11 = new SupplementalFont {Script = "Khmr", Typeface = "MoolBoran"}; + var supplementalFont12 = new SupplementalFont {Script = "Knda", Typeface = "Tunga"}; + var supplementalFont13 = new SupplementalFont {Script = "Guru", Typeface = "Raavi"}; + var supplementalFont14 = new SupplementalFont {Script = "Cans", Typeface = "Euphemia"}; + var supplementalFont15 = new SupplementalFont {Script = "Cher", Typeface = "Plantagenet Cherokee"}; + var supplementalFont16 = new SupplementalFont {Script = "Yiii", Typeface = "Microsoft Yi Baiti"}; + var supplementalFont17 = new SupplementalFont {Script = "Tibt", Typeface = "Microsoft Himalaya"}; + var supplementalFont18 = new SupplementalFont {Script = "Thaa", Typeface = "MV Boli"}; + var supplementalFont19 = new SupplementalFont {Script = "Deva", Typeface = "Mangal"}; + var supplementalFont20 = new SupplementalFont {Script = "Telu", Typeface = "Gautami"}; + var supplementalFont21 = new SupplementalFont {Script = "Taml", Typeface = "Latha"}; + var supplementalFont22 = new SupplementalFont {Script = "Syrc", Typeface = "Estrangelo Edessa"}; + var supplementalFont23 = new SupplementalFont {Script = "Orya", Typeface = "Kalinga"}; + var supplementalFont24 = new SupplementalFont {Script = "Mlym", Typeface = "Kartika"}; + var supplementalFont25 = new SupplementalFont {Script = "Laoo", Typeface = "DokChampa"}; + var supplementalFont26 = new SupplementalFont {Script = "Sinh", Typeface = "Iskoola Pota"}; + var supplementalFont27 = new SupplementalFont {Script = "Mong", Typeface = "Mongolian Baiti"}; + var supplementalFont28 = new SupplementalFont {Script = "Viet", Typeface = "Times New Roman"}; + var supplementalFont29 = new SupplementalFont {Script = "Uigh", Typeface = "Microsoft Uighur"}; majorFont1.AppendChild(latinFont1); majorFont1.AppendChild(eastAsianFont1); @@ -3128,38 +968,38 @@ majorFont1.AppendChild(supplementalFont29); var minorFont1 = new MinorFont(); - var latinFont2 = new LatinFont { Typeface = "Calibri" }; - var eastAsianFont2 = new EastAsianFont { Typeface = "" }; - var complexScriptFont2 = new ComplexScriptFont { Typeface = "" }; - var supplementalFont30 = new SupplementalFont {Script = "Jpan", Typeface = "ＭＳ Ｐゴシック"}; - var supplementalFont31 = new SupplementalFont {Script = "Hang", Typeface = "맑은 고딕"}; - var supplementalFont32 = new SupplementalFont {Script = "Hans", Typeface = "宋体"}; - var supplementalFont33 = new SupplementalFont {Script = "Hant", Typeface = "新細明體"}; - var supplementalFont34 = new SupplementalFont {Script = "Arab", Typeface = "Arial"}; - var supplementalFont35 = new SupplementalFont {Script = "Hebr", Typeface = "Arial"}; - var supplementalFont36 = new SupplementalFont {Script = "Thai", Typeface = "Tahoma"}; - var supplementalFont37 = new SupplementalFont {Script = "Ethi", Typeface = "Nyala"}; - var supplementalFont38 = new SupplementalFont {Script = "Beng", Typeface = "Vrinda"}; - var supplementalFont39 = new SupplementalFont {Script = "Gujr", Typeface = "Shruti"}; - var supplementalFont40 = new SupplementalFont {Script = "Khmr", Typeface = "DaunPenh"}; - var supplementalFont41 = new SupplementalFont {Script = "Knda", Typeface = "Tunga"}; - var supplementalFont42 = new SupplementalFont {Script = "Guru", Typeface = "Raavi"}; - var supplementalFont43 = new SupplementalFont {Script = "Cans", Typeface = "Euphemia"}; - var supplementalFont44 = new SupplementalFont {Script = "Cher", Typeface = "Plantagenet Cherokee"}; - var supplementalFont45 = new SupplementalFont {Script = "Yiii", Typeface = "Microsoft Yi Baiti"}; - var supplementalFont46 = new SupplementalFont {Script = "Tibt", Typeface = "Microsoft Himalaya"}; - var supplementalFont47 = new SupplementalFont {Script = "Thaa", Typeface = "MV Boli"}; - var supplementalFont48 = new SupplementalFont {Script = "Deva", Typeface = "Mangal"}; - var supplementalFont49 = new SupplementalFont {Script = "Telu", Typeface = "Gautami"}; - var supplementalFont50 = new SupplementalFont {Script = "Taml", Typeface = "Latha"}; - var supplementalFont51 = new SupplementalFont {Script = "Syrc", Typeface = "Estrangelo Edessa"}; - var supplementalFont52 = new SupplementalFont {Script = "Orya", Typeface = "Kalinga"}; - var supplementalFont53 = new SupplementalFont {Script = "Mlym", Typeface = "Kartika"}; - var supplementalFont54 = new SupplementalFont {Script = "Laoo", Typeface = "DokChampa"}; - var supplementalFont55 = new SupplementalFont {Script = "Sinh", Typeface = "Iskoola Pota"}; - var supplementalFont56 = new SupplementalFont {Script = "Mong", Typeface = "Mongolian Baiti"}; - var supplementalFont57 = new SupplementalFont {Script = "Viet", Typeface = "Arial"}; - var supplementalFont58 = new SupplementalFont {Script = "Uigh", Typeface = "Microsoft Uighur"}; + var latinFont2 = new LatinFont {Typeface = "Calibri"}; + var eastAsianFont2 = new EastAsianFont {Typeface = ""}; + var complexScriptFont2 = new ComplexScriptFont {Typeface = ""}; + var supplementalFont30 = new SupplementalFont {Script = "Jpan", Typeface = "ＭＳ Ｐゴシック"}; + var supplementalFont31 = new SupplementalFont {Script = "Hang", Typeface = "맑은 고딕"}; + var supplementalFont32 = new SupplementalFont {Script = "Hans", Typeface = "宋体"}; + var supplementalFont33 = new SupplementalFont {Script = "Hant", Typeface = "新細明體"}; + var supplementalFont34 = new SupplementalFont {Script = "Arab", Typeface = "Arial"}; + var supplementalFont35 = new SupplementalFont {Script = "Hebr", Typeface = "Arial"}; + var supplementalFont36 = new SupplementalFont {Script = "Thai", Typeface = "Tahoma"}; + var supplementalFont37 = new SupplementalFont {Script = "Ethi", Typeface = "Nyala"}; + var supplementalFont38 = new SupplementalFont {Script = "Beng", Typeface = "Vrinda"}; + var supplementalFont39 = new SupplementalFont {Script = "Gujr", Typeface = "Shruti"}; + var supplementalFont40 = new SupplementalFont {Script = "Khmr", Typeface = "DaunPenh"}; + var supplementalFont41 = new SupplementalFont {Script = "Knda", Typeface = "Tunga"}; + var supplementalFont42 = new SupplementalFont {Script = "Guru", Typeface = "Raavi"}; + var supplementalFont43 = new SupplementalFont {Script = "Cans", Typeface = "Euphemia"}; + var supplementalFont44 = new SupplementalFont {Script = "Cher", Typeface = "Plantagenet Cherokee"}; + var supplementalFont45 = new SupplementalFont {Script = "Yiii", Typeface = "Microsoft Yi Baiti"}; + var supplementalFont46 = new SupplementalFont {Script = "Tibt", Typeface = "Microsoft Himalaya"}; + var supplementalFont47 = new SupplementalFont {Script = "Thaa", Typeface = "MV Boli"}; + var supplementalFont48 = new SupplementalFont {Script = "Deva", Typeface = "Mangal"}; + var supplementalFont49 = new SupplementalFont {Script = "Telu", Typeface = "Gautami"}; + var supplementalFont50 = new SupplementalFont {Script = "Taml", Typeface = "Latha"}; + var supplementalFont51 = new SupplementalFont {Script = "Syrc", Typeface = "Estrangelo Edessa"}; + var supplementalFont52 = new SupplementalFont {Script = "Orya", Typeface = "Kalinga"}; + var supplementalFont53 = new SupplementalFont {Script = "Mlym", Typeface = "Kartika"}; + var supplementalFont54 = new SupplementalFont {Script = "Laoo", Typeface = "DokChampa"}; + var supplementalFont55 = new SupplementalFont {Script = "Sinh", Typeface = "Iskoola Pota"}; + var supplementalFont56 = new SupplementalFont {Script = "Mong", Typeface = "Mongolian Baiti"}; + var supplementalFont57 = new SupplementalFont {Script = "Viet", Typeface = "Arial"}; + var supplementalFont58 = new SupplementalFont {Script = "Uigh", Typeface = "Microsoft Uighur"}; minorFont1.AppendChild(latinFont2); minorFont1.AppendChild(eastAsianFont2); @@ -3197,46 +1037,46 @@ fontScheme2.AppendChild(majorFont1); fontScheme2.AppendChild(minorFont1); - FormatScheme formatScheme1 = new FormatScheme {Name = "Office"}; + var formatScheme1 = new FormatScheme {Name = "Office"}; - FillStyleList fillStyleList1 = new FillStyleList(); + var fillStyleList1 = new FillStyleList(); - SolidFill solidFill1 = new SolidFill(); - SchemeColor schemeColor1 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var solidFill1 = new SolidFill(); + var schemeColor1 = new SchemeColor {Val = SchemeColorValues.PhColor}; solidFill1.AppendChild(schemeColor1); - GradientFill gradientFill1 = new GradientFill {RotateWithShape = true}; + var gradientFill1 = new GradientFill {RotateWithShape = true}; - GradientStopList gradientStopList1 = new GradientStopList(); + var gradientStopList1 = new GradientStopList(); - GradientStop gradientStop1 = new GradientStop {Position = 0}; + var gradientStop1 = new GradientStop {Position = 0}; - SchemeColor schemeColor2 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Tint tint1 = new Tint {Val = 50000}; - SaturationModulation saturationModulation1 = new SaturationModulation {Val = 300000}; + var schemeColor2 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var tint1 = new Tint {Val = 50000}; + var saturationModulation1 = new SaturationModulation {Val = 300000}; schemeColor2.AppendChild(tint1); schemeColor2.AppendChild(saturationModulation1); gradientStop1.AppendChild(schemeColor2); - GradientStop gradientStop2 = new GradientStop {Position = 35000}; + var gradientStop2 = new GradientStop {Position = 35000}; - SchemeColor schemeColor3 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Tint tint2 = new Tint {Val = 37000}; - SaturationModulation saturationModulation2 = new SaturationModulation {Val = 300000}; + var schemeColor3 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var tint2 = new Tint {Val = 37000}; + var saturationModulation2 = new SaturationModulation {Val = 300000}; schemeColor3.AppendChild(tint2); schemeColor3.AppendChild(saturationModulation2); gradientStop2.AppendChild(schemeColor3); - GradientStop gradientStop3 = new GradientStop {Position = 100000}; + var gradientStop3 = new GradientStop {Position = 100000}; - SchemeColor schemeColor4 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Tint tint3 = new Tint {Val = 15000}; - SaturationModulation saturationModulation3 = new SaturationModulation {Val = 350000}; + var schemeColor4 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var tint3 = new Tint {Val = 15000}; + var saturationModulation3 = new SaturationModulation {Val = 350000}; schemeColor4.AppendChild(tint3); schemeColor4.AppendChild(saturationModulation3); @@ -3246,42 +1086,42 @@ gradientStopList1.AppendChild(gradientStop1); gradientStopList1.AppendChild(gradientStop2); gradientStopList1.AppendChild(gradientStop3); - LinearGradientFill linearGradientFill1 = new LinearGradientFill {Angle = 16200000, Scaled = true}; + var linearGradientFill1 = new LinearGradientFill {Angle = 16200000, Scaled = true}; gradientFill1.AppendChild(gradientStopList1); gradientFill1.AppendChild(linearGradientFill1); - GradientFill gradientFill2 = new GradientFill {RotateWithShape = true}; + var gradientFill2 = new GradientFill {RotateWithShape = true}; - GradientStopList gradientStopList2 = new GradientStopList(); + var gradientStopList2 = new GradientStopList(); - GradientStop gradientStop4 = new GradientStop {Position = 0}; + var gradientStop4 = new GradientStop {Position = 0}; - SchemeColor schemeColor5 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Shade shade1 = new Shade {Val = 51000}; - SaturationModulation saturationModulation4 = new SaturationModulation {Val = 130000}; + var schemeColor5 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var shade1 = new Shade {Val = 51000}; + var saturationModulation4 = new SaturationModulation {Val = 130000}; schemeColor5.AppendChild(shade1); schemeColor5.AppendChild(saturationModulation4); gradientStop4.AppendChild(schemeColor5); - GradientStop gradientStop5 = new GradientStop {Position = 80000}; + var gradientStop5 = new GradientStop {Position = 80000}; - SchemeColor schemeColor6 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Shade shade2 = new Shade {Val = 93000}; - SaturationModulation saturationModulation5 = new SaturationModulation {Val = 130000}; + var schemeColor6 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var shade2 = new Shade {Val = 93000}; + var saturationModulation5 = new SaturationModulation {Val = 130000}; schemeColor6.AppendChild(shade2); schemeColor6.AppendChild(saturationModulation5); gradientStop5.AppendChild(schemeColor6); - GradientStop gradientStop6 = new GradientStop {Position = 100000}; + var gradientStop6 = new GradientStop {Position = 100000}; - SchemeColor schemeColor7 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Shade shade3 = new Shade {Val = 94000}; - SaturationModulation saturationModulation6 = new SaturationModulation {Val = 135000}; + var schemeColor7 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var shade3 = new Shade {Val = 94000}; + var saturationModulation6 = new SaturationModulation {Val = 135000}; schemeColor7.AppendChild(shade3); schemeColor7.AppendChild(saturationModulation6); @@ -3291,7 +1131,7 @@ gradientStopList2.AppendChild(gradientStop4); gradientStopList2.AppendChild(gradientStop5); gradientStopList2.AppendChild(gradientStop6); - LinearGradientFill linearGradientFill2 = new LinearGradientFill {Angle = 16200000, Scaled = false}; + var linearGradientFill2 = new LinearGradientFill {Angle = 16200000, Scaled = false}; gradientFill2.AppendChild(gradientStopList2); gradientFill2.AppendChild(linearGradientFill2); @@ -3300,61 +1140,61 @@ fillStyleList1.AppendChild(gradientFill1); fillStyleList1.AppendChild(gradientFill2); - LineStyleList lineStyleList1 = new LineStyleList(); + var lineStyleList1 = new LineStyleList(); - Outline outline1 = new Outline - { - Width = 9525, - CapType = LineCapValues.Flat, - CompoundLineType = CompoundLineValues.Single, - Alignment = PenAlignmentValues.Center - }; + var outline1 = new Outline + { + Width = 9525, + CapType = LineCapValues.Flat, + CompoundLineType = CompoundLineValues.Single, + Alignment = PenAlignmentValues.Center + }; - SolidFill solidFill2 = new SolidFill(); + var solidFill2 = new SolidFill(); - SchemeColor schemeColor8 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Shade shade4 = new Shade {Val = 95000}; - SaturationModulation saturationModulation7 = new SaturationModulation {Val = 105000}; + var schemeColor8 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var shade4 = new Shade {Val = 95000}; + var saturationModulation7 = new SaturationModulation {Val = 105000}; schemeColor8.AppendChild(shade4); schemeColor8.AppendChild(saturationModulation7); solidFill2.AppendChild(schemeColor8); - PresetDash presetDash1 = new PresetDash {Val = PresetLineDashValues.Solid}; + var presetDash1 = new PresetDash {Val = PresetLineDashValues.Solid}; outline1.AppendChild(solidFill2); outline1.AppendChild(presetDash1); - Outline outline2 = new Outline - { - Width = 25400, - CapType = LineCapValues.Flat, - CompoundLineType = CompoundLineValues.Single, - Alignment = PenAlignmentValues.Center - }; + var outline2 = new Outline + { + Width = 25400, + CapType = LineCapValues.Flat, + CompoundLineType = CompoundLineValues.Single, + Alignment = PenAlignmentValues.Center + }; - SolidFill solidFill3 = new SolidFill(); - SchemeColor schemeColor9 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var solidFill3 = new SolidFill(); + var schemeColor9 = new SchemeColor {Val = SchemeColorValues.PhColor}; solidFill3.AppendChild(schemeColor9); - PresetDash presetDash2 = new PresetDash {Val = PresetLineDashValues.Solid}; + var presetDash2 = new PresetDash {Val = PresetLineDashValues.Solid}; outline2.AppendChild(solidFill3); outline2.AppendChild(presetDash2); - Outline outline3 = new Outline - { - Width = 38100, - CapType = LineCapValues.Flat, - CompoundLineType = CompoundLineValues.Single, - Alignment = PenAlignmentValues.Center - }; + var outline3 = new Outline + { + Width = 38100, + CapType = LineCapValues.Flat, + CompoundLineType = CompoundLineValues.Single, + Alignment = PenAlignmentValues.Center + }; - SolidFill solidFill4 = new SolidFill(); - SchemeColor schemeColor10 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var solidFill4 = new SolidFill(); + var schemeColor10 = new SchemeColor {Val = SchemeColorValues.PhColor}; solidFill4.AppendChild(schemeColor10); - PresetDash presetDash3 = new PresetDash {Val = PresetLineDashValues.Solid}; + var presetDash3 = new PresetDash {Val = PresetLineDashValues.Solid}; outline3.AppendChild(solidFill4); outline3.AppendChild(presetDash3); @@ -3363,16 +1203,22 @@ lineStyleList1.AppendChild(outline2); lineStyleList1.AppendChild(outline3); - EffectStyleList effectStyleList1 = new EffectStyleList(); + var effectStyleList1 = new EffectStyleList(); - EffectStyle effectStyle1 = new EffectStyle(); + var effectStyle1 = new EffectStyle(); - EffectList effectList1 = new EffectList(); + var effectList1 = new EffectList(); - OuterShadow outerShadow1 = new OuterShadow {BlurRadius = 40000L, Distance = 20000L, Direction = 5400000, RotateWithShape = false}; + var outerShadow1 = new OuterShadow + { + BlurRadius = 40000L, + Distance = 20000L, + Direction = 5400000, + RotateWithShape = false + }; - RgbColorModelHex rgbColorModelHex11 = new RgbColorModelHex {Val = "000000"}; - Alpha alpha1 = new Alpha {Val = 38000}; + var rgbColorModelHex11 = new RgbColorModelHex {Val = "000000"}; + var alpha1 = new Alpha {Val = 38000}; rgbColorModelHex11.AppendChild(alpha1); @@ -3382,14 +1228,20 @@ effectStyle1.AppendChild(effectList1); - EffectStyle effectStyle2 = new EffectStyle(); + var effectStyle2 = new EffectStyle(); - EffectList effectList2 = new EffectList(); + var effectList2 = new EffectList(); - OuterShadow outerShadow2 = new OuterShadow {BlurRadius = 40000L, Distance = 23000L, Direction = 5400000, RotateWithShape = false}; + var outerShadow2 = new OuterShadow + { + BlurRadius = 40000L, + Distance = 23000L, + Direction = 5400000, + RotateWithShape = false + }; - RgbColorModelHex rgbColorModelHex12 = new RgbColorModelHex {Val = "000000"}; - Alpha alpha2 = new Alpha {Val = 35000}; + var rgbColorModelHex12 = new RgbColorModelHex {Val = "000000"}; + var alpha2 = new Alpha {Val = 35000}; rgbColorModelHex12.AppendChild(alpha2); @@ -3399,14 +1251,20 @@ effectStyle2.AppendChild(effectList2); - EffectStyle effectStyle3 = new EffectStyle(); + var effectStyle3 = new EffectStyle(); - EffectList effectList3 = new EffectList(); + var effectList3 = new EffectList(); - OuterShadow outerShadow3 = new OuterShadow {BlurRadius = 40000L, Distance = 23000L, Direction = 5400000, RotateWithShape = false}; + var outerShadow3 = new OuterShadow + { + BlurRadius = 40000L, + Distance = 23000L, + Direction = 5400000, + RotateWithShape = false + }; - RgbColorModelHex rgbColorModelHex13 = new RgbColorModelHex {Val = "000000"}; - Alpha alpha3 = new Alpha {Val = 35000}; + var rgbColorModelHex13 = new RgbColorModelHex {Val = "000000"}; + var alpha3 = new Alpha {Val = 35000}; rgbColorModelHex13.AppendChild(alpha3); @@ -3414,23 +1272,23 @@ effectList3.AppendChild(outerShadow3); - Scene3DType scene3DType1 = new Scene3DType(); + var scene3DType1 = new Scene3DType(); - Camera camera1 = new Camera {Preset = PresetCameraValues.OrthographicFront}; - Rotation rotation1 = new Rotation {Latitude = 0, Longitude = 0, Revolution = 0}; + var camera1 = new Camera {Preset = PresetCameraValues.OrthographicFront}; + var rotation1 = new Rotation {Latitude = 0, Longitude = 0, Revolution = 0}; camera1.AppendChild(rotation1); - LightRig lightRig1 = new LightRig {Rig = LightRigValues.ThreePoints, Direction = LightRigDirectionValues.Top}; - Rotation rotation2 = new Rotation {Latitude = 0, Longitude = 0, Revolution = 1200000}; + var lightRig1 = new LightRig {Rig = LightRigValues.ThreePoints, Direction = LightRigDirectionValues.Top}; + var rotation2 = new Rotation {Latitude = 0, Longitude = 0, Revolution = 1200000}; lightRig1.AppendChild(rotation2); scene3DType1.AppendChild(camera1); scene3DType1.AppendChild(lightRig1); - Shape3DType shape3DType1 = new Shape3DType(); - BevelTop bevelTop1 = new BevelTop {Width = 63500L, Height = 25400L}; + var shape3DType1 = new Shape3DType(); + var bevelTop1 = new BevelTop {Width = 63500L, Height = 25400L}; shape3DType1.AppendChild(bevelTop1); @@ -3442,34 +1300,34 @@ effectStyleList1.AppendChild(effectStyle2); effectStyleList1.AppendChild(effectStyle3); - BackgroundFillStyleList backgroundFillStyleList1 = new BackgroundFillStyleList(); + var backgroundFillStyleList1 = new BackgroundFillStyleList(); - SolidFill solidFill5 = new SolidFill(); - SchemeColor schemeColor11 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var solidFill5 = new SolidFill(); + var schemeColor11 = new SchemeColor {Val = SchemeColorValues.PhColor}; solidFill5.AppendChild(schemeColor11); - GradientFill gradientFill3 = new GradientFill {RotateWithShape = true}; + var gradientFill3 = new GradientFill {RotateWithShape = true}; - GradientStopList gradientStopList3 = new GradientStopList(); + var gradientStopList3 = new GradientStopList(); - GradientStop gradientStop7 = new GradientStop {Position = 0}; + var gradientStop7 = new GradientStop {Position = 0}; - SchemeColor schemeColor12 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Tint tint4 = new Tint {Val = 40000}; - SaturationModulation saturationModulation8 = new SaturationModulation {Val = 350000}; + var schemeColor12 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var tint4 = new Tint {Val = 40000}; + var saturationModulation8 = new SaturationModulation {Val = 350000}; schemeColor12.AppendChild(tint4); schemeColor12.AppendChild(saturationModulation8); gradientStop7.AppendChild(schemeColor12); - GradientStop gradientStop8 = new GradientStop {Position = 40000}; + var gradientStop8 = new GradientStop {Position = 40000}; - SchemeColor schemeColor13 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Tint tint5 = new Tint {Val = 45000}; - Shade shade5 = new Shade {Val = 99000}; - SaturationModulation saturationModulation9 = new SaturationModulation {Val = 350000}; + var schemeColor13 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var tint5 = new Tint {Val = 45000}; + var shade5 = new Shade {Val = 99000}; + var saturationModulation9 = new SaturationModulation {Val = 350000}; schemeColor13.AppendChild(tint5); schemeColor13.AppendChild(shade5); @@ -3477,11 +1335,11 @@ gradientStop8.AppendChild(schemeColor13); - GradientStop gradientStop9 = new GradientStop {Position = 100000}; + var gradientStop9 = new GradientStop {Position = 100000}; - SchemeColor schemeColor14 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Shade shade6 = new Shade {Val = 20000}; - SaturationModulation saturationModulation10 = new SaturationModulation {Val = 255000}; + var schemeColor14 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var shade6 = new Shade {Val = 20000}; + var saturationModulation10 = new SaturationModulation {Val = 255000}; schemeColor14.AppendChild(shade6); schemeColor14.AppendChild(saturationModulation10); @@ -3492,34 +1350,34 @@ gradientStopList3.AppendChild(gradientStop8); gradientStopList3.AppendChild(gradientStop9); - PathGradientFill pathGradientFill1 = new PathGradientFill {Path = PathShadeValues.Circle}; - FillToRectangle fillToRectangle1 = new FillToRectangle {Left = 50000, Top = -80000, Right = 50000, Bottom = 180000}; + var pathGradientFill1 = new PathGradientFill {Path = PathShadeValues.Circle}; + var fillToRectangle1 = new FillToRectangle {Left = 50000, Top = -80000, Right = 50000, Bottom = 180000}; pathGradientFill1.AppendChild(fillToRectangle1); gradientFill3.AppendChild(gradientStopList3); gradientFill3.AppendChild(pathGradientFill1); - GradientFill gradientFill4 = new GradientFill {RotateWithShape = true}; + var gradientFill4 = new GradientFill {RotateWithShape = true}; - GradientStopList gradientStopList4 = new GradientStopList(); + var gradientStopList4 = new GradientStopList(); - GradientStop gradientStop10 = new GradientStop {Position = 0}; + var gradientStop10 = new GradientStop {Position = 0}; - SchemeColor schemeColor15 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Tint tint6 = new Tint {Val = 80000}; - SaturationModulation saturationModulation11 = new SaturationModulation {Val = 300000}; + var schemeColor15 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var tint6 = new Tint {Val = 80000}; + var saturationModulation11 = new SaturationModulation {Val = 300000}; schemeColor15.AppendChild(tint6); schemeColor15.AppendChild(saturationModulation11); gradientStop10.AppendChild(schemeColor15); - GradientStop gradientStop11 = new GradientStop {Position = 100000}; + var gradientStop11 = new GradientStop {Position = 100000}; - SchemeColor schemeColor16 = new SchemeColor {Val = SchemeColorValues.PhColor}; - Shade shade7 = new Shade {Val = 30000}; - SaturationModulation saturationModulation12 = new SaturationModulation {Val = 200000}; + var schemeColor16 = new SchemeColor {Val = SchemeColorValues.PhColor}; + var shade7 = new Shade {Val = 30000}; + var saturationModulation12 = new SaturationModulation {Val = 200000}; schemeColor16.AppendChild(shade7); schemeColor16.AppendChild(saturationModulation12); @@ -3529,8 +1387,8 @@ gradientStopList4.AppendChild(gradientStop10); gradientStopList4.AppendChild(gradientStop11); - PathGradientFill pathGradientFill2 = new PathGradientFill {Path = PathShadeValues.Circle}; - FillToRectangle fillToRectangle2 = new FillToRectangle {Left = 50000, Top = 50000, Right = 50000, Bottom = 50000}; + var pathGradientFill2 = new PathGradientFill {Path = PathShadeValues.Circle}; + var fillToRectangle2 = new FillToRectangle {Left = 50000, Top = 50000, Right = 50000, Bottom = 50000}; pathGradientFill2.AppendChild(fillToRectangle2); @@ -3549,8 +1407,8 @@ themeElements1.AppendChild(colorScheme1); themeElements1.AppendChild(fontScheme2); themeElements1.AppendChild(formatScheme1); - ObjectDefaults objectDefaults1 = new ObjectDefaults(); - ExtraColorSchemeList extraColorSchemeList1 = new ExtraColorSchemeList(); + var objectDefaults1 = new ObjectDefaults(); + var extraColorSchemeList1 = new ExtraColorSchemeList(); theme1.AppendChild(themeElements1); theme1.AppendChild(objectDefaults1); @@ -3561,18 +1419,19 @@ private void GenerateCustomFilePropertiesPartContent(CustomFilePropertiesPart customFilePropertiesPart1) { - DocumentFormat.OpenXml.CustomProperties.Properties properties2 = new DocumentFormat.OpenXml.CustomProperties.Properties(); - properties2.AddNamespaceDeclaration("vt", "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"); + var properties2 = new DocumentFormat.OpenXml.CustomProperties.Properties(); + properties2.AddNamespaceDeclaration("vt", + "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"); Int32 propertyId = 1; - foreach (var p in CustomProperties) + foreach (IXLCustomProperty p in CustomProperties) { propertyId++; - CustomDocumentProperty customDocumentProperty = new CustomDocumentProperty - { - FormatId = "{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", - PropertyId = propertyId, - Name = p.Name - }; + var customDocumentProperty = new CustomDocumentProperty + { + FormatId = "{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", + PropertyId = propertyId, + Name = p.Name + }; if (p.Type == XLCustomPropertyType.Text) { var vTLPWSTR1 = new VTLPWSTR(); @@ -3581,19 +1440,20 @@ } else if (p.Type == XLCustomPropertyType.Date) { - VTFileTime vTFileTime1 = new VTFileTime(); - vTFileTime1.Text = p.GetValue().ToUniversalTime().ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"); + var vTFileTime1 = new VTFileTime(); + vTFileTime1.Text = + p.GetValue().ToUniversalTime().ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'"); customDocumentProperty.AppendChild(vTFileTime1); } else if (p.Type == XLCustomPropertyType.Number) { - VTDouble vTDouble1 = new VTDouble(); + var vTDouble1 = new VTDouble(); vTDouble1.Text = p.GetValue().ToString(CultureInfo.InvariantCulture); customDocumentProperty.AppendChild(vTDouble1); } else { - VTBool vTBool1 = new VTBool(); + var vTBool1 = new VTBool(); vTBool1.Text = p.GetValue().ToString().ToLower(); customDocumentProperty.AppendChild(vTBool1); } @@ -3639,41 +1499,38 @@ return name; } - private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable,SaveContext context) + private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable, + SaveContext context) { context.TableId++; string reference; reference = xlTable.RangeAddress.FirstAddress + ":" + xlTable.RangeAddress.LastAddress; String tableName = GetTableName(xlTable.Name, context); var table = new Table - { - Id = context.TableId, - Name = tableName, - DisplayName = tableName, - Reference = reference - }; + { + Id = context.TableId, + Name = tableName, + DisplayName = tableName, + Reference = reference + }; if (xlTable.ShowTotalsRow) - { table.TotalsRowCount = 1; - } else - { table.TotalsRowShown = false; - } - TableColumns tableColumns1 = new TableColumns {Count = (UInt32) xlTable.ColumnCount()}; + var tableColumns1 = new TableColumns {Count = (UInt32)xlTable.ColumnCount()}; UInt32 columnId = 0; - foreach (var cell in xlTable.HeadersRow().Cells()) + foreach (IXLCell cell in xlTable.HeadersRow().Cells()) { columnId++; String fieldName = cell.GetString(); var xlField = xlTable.Field(fieldName); - TableColumn tableColumn1 = new TableColumn - { - Id = columnId, - Name = fieldName - }; + var tableColumn1 = new TableColumn + { + Id = columnId, + Name = fieldName + }; if (xlTable.ShowTotalsRow) { if (xlField.TotalsRowFunction != XLTotalsRowFunction.None) @@ -3681,42 +1538,37 @@ tableColumn1.TotalsRowFunction = xlField.TotalsRowFunction.ToOpenXml(); if (xlField.TotalsRowFunction == XLTotalsRowFunction.Custom) - { tableColumn1.TotalsRowFormula = new TotalsRowFormula(xlField.TotalsRowFormulaA1); - } } if (!StringExtensions.IsNullOrWhiteSpace(xlField.TotalsRowLabel)) - { tableColumn1.TotalsRowLabel = xlField.TotalsRowLabel; - } } tableColumns1.AppendChild(tableColumn1); } - TableStyleInfo tableStyleInfo1 = new TableStyleInfo - { - Name = Enum.GetName(typeof (XLTableTheme), xlTable.Theme), - ShowFirstColumn = xlTable.EmphasizeFirstColumn, - ShowLastColumn = xlTable.EmphasizeLastColumn, - ShowRowStripes = xlTable.ShowRowStripes, - ShowColumnStripes = xlTable.ShowColumnStripes - }; + var tableStyleInfo1 = new TableStyleInfo + { + Name = Enum.GetName(typeof(XLTableTheme), xlTable.Theme), + ShowFirstColumn = xlTable.EmphasizeFirstColumn, + ShowLastColumn = xlTable.EmphasizeLastColumn, + ShowRowStripes = xlTable.ShowRowStripes, + ShowColumnStripes = xlTable.ShowColumnStripes + }; if (xlTable.ShowAutoFilter) { - AutoFilter autoFilter1 = new AutoFilter(); + var autoFilter1 = new AutoFilter(); if (xlTable.ShowTotalsRow) { autoFilter1.Reference = xlTable.RangeAddress.FirstAddress + ":" + - ExcelHelper.GetColumnLetterFromNumber(xlTable.RangeAddress.LastAddress.ColumnNumber) + + ExcelHelper.GetColumnLetterFromNumber( + xlTable.RangeAddress.LastAddress.ColumnNumber) + (xlTable.RangeAddress.LastAddress.RowNumber - 1).ToStringLookup(); } else - { autoFilter1.Reference = reference; - } table.AppendChild(autoFilter1); } @@ -3727,6 +1579,1861 @@ tableDefinitionPart.Table = table; } + #region GenerateWorkbookStylesPartContent + + private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart, SaveContext context) + { + var defaultStyle = new XLStyle(null, DefaultStyle); + if (!context.SharedFonts.ContainsKey(defaultStyle.Font)) + context.SharedFonts.Add(defaultStyle.Font, new FontInfo {FontId = 0, Font = defaultStyle.Font}); + + var sharedFills = new Dictionary(); + sharedFills.Add(defaultStyle.Fill, new FillInfo {FillId = 2, Fill = defaultStyle.Fill}); + + var sharedBorders = new Dictionary(); + sharedBorders.Add(defaultStyle.Border, new BorderInfo {BorderId = 0, Border = defaultStyle.Border}); + + var sharedNumberFormats = new Dictionary(); + sharedNumberFormats.Add(defaultStyle.NumberFormat, + new NumberFormatInfo {NumberFormatId = 0, NumberFormat = defaultStyle.NumberFormat}); + + //Dictionary sharedAlignments = new Dictionary(); + //sharedAlignments.Add(defaultStyle.Alignment.ToString(), new AlignmentInfo() { AlignmentId = 0, Alignment = defaultStyle.Alignment }); + + if (workbookStylesPart.Stylesheet == null) + workbookStylesPart.Stylesheet = new Stylesheet(); + + // Cell styles = Named styles + if (workbookStylesPart.Stylesheet.CellStyles == null) + workbookStylesPart.Stylesheet.CellStyles = new CellStyles(); + + UInt32 defaultFormatId; + if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.Name == "Normal")) + { + defaultFormatId = + workbookStylesPart.Stylesheet.CellStyles.Elements().Where(c => c.Name == "Normal").Single + ().FormatId.Value; + } + else if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any()) + defaultFormatId = + workbookStylesPart.Stylesheet.CellStyles.Elements().Max(c => c.FormatId.Value) + 1; + else + defaultFormatId = 0; + + context.SharedStyles.Add(defaultStyle, + new StyleInfo + { + StyleId = defaultFormatId, + Style = defaultStyle, + FontId = 0, + FillId = 0, + BorderId = 0, + NumberFormatId = 0 + //AlignmentId = 0 + }); + + UInt32 styleCount = 1; + UInt32 fontCount = 1; + UInt32 fillCount = 3; + UInt32 borderCount = 1; + Int32 numberFormatCount = 1; + var xlStyles = new HashSet(); + + foreach (XLWorksheet worksheet in WorksheetsInternal) + { + foreach (IXLStyle s in worksheet.Styles) + { + if (!xlStyles.Contains(s)) + xlStyles.Add(s); + } + + foreach (IXLStyle s in worksheet.Internals.ColumnsCollection.Select(kp => kp.Value.Style)) + { + if (!xlStyles.Contains(s)) + xlStyles.Add(s); + } + + foreach (IXLStyle s in worksheet.Internals.RowsCollection.Select(kp => kp.Value.Style)) + { + if (!xlStyles.Contains(s)) + xlStyles.Add(s); + } + + //xlStyles.AddRange(worksheet.Styles); + //worksheet.Internals.ColumnsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); + //worksheet.Internals.RowsCollection.Values.ForEach(c => xlStyles.Add(c.Style)); + } + + foreach (IXLStyle xlStyle in xlStyles) + { + if (!context.SharedFonts.ContainsKey(xlStyle.Font)) + context.SharedFonts.Add(xlStyle.Font, new FontInfo {FontId = fontCount++, Font = xlStyle.Font}); + + if (!sharedFills.ContainsKey(xlStyle.Fill)) + sharedFills.Add(xlStyle.Fill, new FillInfo {FillId = fillCount++, Fill = xlStyle.Fill}); + + if (!sharedBorders.ContainsKey(xlStyle.Border)) + sharedBorders.Add(xlStyle.Border, new BorderInfo {BorderId = borderCount++, Border = xlStyle.Border}); + + if (xlStyle.NumberFormat.NumberFormatId == -1 && !sharedNumberFormats.ContainsKey(xlStyle.NumberFormat)) + { + sharedNumberFormats.Add(xlStyle.NumberFormat, + new NumberFormatInfo + { + NumberFormatId = numberFormatCount + 164, + NumberFormat = xlStyle.NumberFormat + }); + numberFormatCount++; + } + } + + var allSharedNumberFormats = ResolveNumberFormats(workbookStylesPart, sharedNumberFormats); + ResolveFonts(workbookStylesPart, context); + var allSharedFills = ResolveFills(workbookStylesPart, sharedFills); + var allSharedBorders = ResolveBorders(workbookStylesPart, sharedBorders); + + foreach (IXLStyle xlStyle in xlStyles) + { + if (!context.SharedStyles.ContainsKey(xlStyle)) + { + Int32 numberFormatId; + if (xlStyle.NumberFormat.NumberFormatId >= 0) + numberFormatId = xlStyle.NumberFormat.NumberFormatId; + else + numberFormatId = allSharedNumberFormats[xlStyle.NumberFormat].NumberFormatId; + + context.SharedStyles.Add(xlStyle, + new StyleInfo + { + StyleId = styleCount++, + Style = xlStyle, + FontId = context.SharedFonts[xlStyle.Font].FontId, + FillId = allSharedFills[xlStyle.Fill].FillId, + BorderId = allSharedBorders[xlStyle.Border].BorderId, + NumberFormatId = numberFormatId + }); + } + } + + var allCellStyleFormats = ResolveCellStyleFormats(workbookStylesPart, context); + ResolveRest(workbookStylesPart, context); + + if (!workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.Name == "Normal")) + { + //var defaultFormatId = context.SharedStyles.Values.Where(s => s.Style.Equals(DefaultStyle)).Single().StyleId; + + var cellStyle1 = new CellStyle {Name = "Normal", FormatId = defaultFormatId, BuiltinId = 0U}; + workbookStylesPart.Stylesheet.CellStyles.AppendChild(cellStyle1); + } + workbookStylesPart.Stylesheet.CellStyles.Count = (UInt32)workbookStylesPart.Stylesheet.CellStyles.Count(); + + var newSharedStyles = new Dictionary(); + foreach (KeyValuePair ss in context.SharedStyles) + { + Int32 styleId = -1; + foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats) + { + styleId++; + if (CellFormatsAreEqual(f, ss.Value)) + break; + } + if (styleId == -1) + styleId = 0; + var si = ss.Value; + si.StyleId = (UInt32)styleId; + newSharedStyles.Add(ss.Key, si); + } + context.SharedStyles.Clear(); + newSharedStyles.ForEach(kp => context.SharedStyles.Add(kp.Key, kp.Value)); + + //TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9", DefaultPivotStyle = "PivotStyleLight16" }; + //workbookStylesPart.Stylesheet.AppendChild(tableStyles1); + } + + private void ResolveRest(WorkbookStylesPart workbookStylesPart, SaveContext context) + { + if (workbookStylesPart.Stylesheet.CellFormats == null) + workbookStylesPart.Stylesheet.CellFormats = new CellFormats(); + + foreach (StyleInfo styleInfo in context.SharedStyles.Values) + { + Int32 styleId = 0; + Boolean foundOne = false; + foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats) + { + if (CellFormatsAreEqual(f, styleInfo)) + { + foundOne = true; + break; + } + styleId++; + } + if (!foundOne) + { + Int32 formatId = 0; + foreach (CellFormat f in workbookStylesPart.Stylesheet.CellStyleFormats) + { + if (CellFormatsAreEqual(f, styleInfo)) + break; + styleId++; + } + + //CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false, FormatId = (UInt32)formatId }; + var cellFormat = GetCellFormat(styleInfo); + cellFormat.FormatId = (UInt32)formatId; + var alignment = new Alignment + { + Horizontal = styleInfo.Style.Alignment.Horizontal.ToOpenXml(), + Vertical = styleInfo.Style.Alignment.Vertical.ToOpenXml(), + Indent = (UInt32)styleInfo.Style.Alignment.Indent, + ReadingOrder = (UInt32)styleInfo.Style.Alignment.ReadingOrder, + WrapText = styleInfo.Style.Alignment.WrapText, + TextRotation = (UInt32)styleInfo.Style.Alignment.TextRotation, + ShrinkToFit = styleInfo.Style.Alignment.ShrinkToFit, + RelativeIndent = styleInfo.Style.Alignment.RelativeIndent, + JustifyLastLine = styleInfo.Style.Alignment.JustifyLastLine + }; + cellFormat.AppendChild(alignment); + + if (cellFormat.ApplyProtection.Value) + cellFormat.AppendChild(GetProtection(styleInfo)); + + workbookStylesPart.Stylesheet.CellFormats.AppendChild(cellFormat); + } + } + workbookStylesPart.Stylesheet.CellFormats.Count = (UInt32)workbookStylesPart.Stylesheet.CellFormats.Count(); + } + + private Dictionary ResolveCellStyleFormats(WorkbookStylesPart workbookStylesPart, + SaveContext context) + { + if (workbookStylesPart.Stylesheet.CellStyleFormats == null) + workbookStylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats(); + + var allSharedStyles = new Dictionary(); + foreach (StyleInfo styleInfo in context.SharedStyles.Values) + { + Int32 styleId = 0; + Boolean foundOne = false; + foreach (CellFormat f in workbookStylesPart.Stylesheet.CellStyleFormats) + { + if (CellFormatsAreEqual(f, styleInfo)) + { + foundOne = true; + break; + } + styleId++; + } + if (!foundOne) + { + //CellFormat cellStyleFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false }; + var cellStyleFormat = GetCellFormat(styleInfo); + + if (cellStyleFormat.ApplyProtection.Value) + cellStyleFormat.AppendChild(GetProtection(styleInfo)); + + workbookStylesPart.Stylesheet.CellStyleFormats.AppendChild(cellStyleFormat); + } + allSharedStyles.Add(styleInfo.Style, new StyleInfo {Style = styleInfo.Style, StyleId = (UInt32)styleId}); + } + workbookStylesPart.Stylesheet.CellStyleFormats.Count = + (UInt32)workbookStylesPart.Stylesheet.CellStyleFormats.Count(); + + return allSharedStyles; + } + + private static bool ApplyFill(StyleInfo styleInfo) + { + return styleInfo.Style.Fill.PatternType.ToOpenXml() == PatternValues.None; + } + + private static bool ApplyBorder(StyleInfo styleInfo) + { + var opBorder = styleInfo.Style.Border; + return (opBorder.BottomBorder.ToOpenXml() != BorderStyleValues.None + || opBorder.DiagonalBorder.ToOpenXml() != BorderStyleValues.None + || opBorder.RightBorder.ToOpenXml() != BorderStyleValues.None + || opBorder.LeftBorder.ToOpenXml() != BorderStyleValues.None + || opBorder.TopBorder.ToOpenXml() != BorderStyleValues.None); + } + + private static bool ApplyProtection(StyleInfo styleInfo) + { + return styleInfo.Style.Protection != null; + } + + private CellFormat GetCellFormat(StyleInfo styleInfo) + { + var cellFormat = new CellFormat + { + NumberFormatId = (UInt32)styleInfo.NumberFormatId, + FontId = styleInfo.FontId, + FillId = styleInfo.FillId, + BorderId = styleInfo.BorderId, + ApplyNumberFormat = false, + ApplyFill = ApplyFill(styleInfo), + ApplyBorder = ApplyBorder(styleInfo), + ApplyAlignment = false, + ApplyProtection = ApplyProtection(styleInfo) + }; + return cellFormat; + } + + private static Protection GetProtection(StyleInfo styleInfo) + { + return new Protection + { + Locked = styleInfo.Style.Protection.Locked, + Hidden = styleInfo.Style.Protection.Hidden + }; + } + + private static bool CellFormatsAreEqual(CellFormat f, StyleInfo styleInfo) + { + return + styleInfo.BorderId == f.BorderId + && styleInfo.FillId == f.FillId + && styleInfo.FontId == f.FontId + && styleInfo.NumberFormatId == f.NumberFormatId + && f.ApplyNumberFormat != null && f.ApplyNumberFormat == false + && f.ApplyAlignment != null && f.ApplyAlignment == false + && f.ApplyFill != null && f.ApplyFill == ApplyFill(styleInfo) + && f.ApplyBorder != null && f.ApplyBorder == ApplyBorder(styleInfo) + && AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment) + && ProtectionsAreEqual(f.Protection, styleInfo.Style.Protection) + ; + } + + private static bool ProtectionsAreEqual(Protection protection, IXLProtection xlProtection) + { + var p = new XLProtection(); + if (protection != null) + { + if (protection.Locked != null) + p.Locked = protection.Locked.Value; + if (protection.Hidden != null) + p.Hidden = protection.Hidden.Value; + } + return p.Equals(xlProtection); + } + + private static bool AlignmentsAreEqual(Alignment alignment, IXLAlignment xlAlignment) + { + var a = new XLAlignment(); + if (alignment != null) + { + if (alignment.Indent != null) + a.Indent = (Int32)alignment.Indent.Value; + + if (alignment.Horizontal != null) + a.Horizontal = alignment.Horizontal.Value.ToClosedXml(); + if (alignment.Vertical != null) + a.Vertical = alignment.Vertical.Value.ToClosedXml(); + + if (alignment.ReadingOrder != null) + a.ReadingOrder = alignment.ReadingOrder.Value.ToClosedXml(); + if (alignment.WrapText != null) + a.WrapText = alignment.WrapText.Value; + if (alignment.TextRotation != null) + a.TextRotation = (Int32)alignment.TextRotation.Value; + if (alignment.ShrinkToFit != null) + a.ShrinkToFit = alignment.ShrinkToFit.Value; + if (alignment.RelativeIndent != null) + a.RelativeIndent = alignment.RelativeIndent.Value; + if (alignment.JustifyLastLine != null) + a.JustifyLastLine = alignment.JustifyLastLine.Value; + } + return a.Equals(xlAlignment); + } + + private Dictionary ResolveBorders(WorkbookStylesPart workbookStylesPart, + Dictionary sharedBorders) + { + if (workbookStylesPart.Stylesheet.Borders == null) + workbookStylesPart.Stylesheet.Borders = new Borders(); + + var allSharedBorders = new Dictionary(); + foreach (BorderInfo borderInfo in sharedBorders.Values) + { + Int32 borderId = 0; + Boolean foundOne = false; + foreach (Border f in workbookStylesPart.Stylesheet.Borders) + { + if (BordersAreEqual(f, borderInfo.Border)) + { + foundOne = true; + break; + } + borderId++; + } + if (!foundOne) + { + var border = GetNewBorder(borderInfo); + workbookStylesPart.Stylesheet.Borders.AppendChild(border); + } + allSharedBorders.Add(borderInfo.Border, + new BorderInfo {Border = borderInfo.Border, BorderId = (UInt32)borderId}); + } + workbookStylesPart.Stylesheet.Borders.Count = (UInt32)workbookStylesPart.Stylesheet.Borders.Count(); + return allSharedBorders; + } + + private Border GetNewBorder(BorderInfo borderInfo) + { + var border = new Border + {DiagonalUp = borderInfo.Border.DiagonalUp, DiagonalDown = borderInfo.Border.DiagonalDown}; + + var leftBorder = new LeftBorder {Style = borderInfo.Border.LeftBorder.ToOpenXml()}; + var leftBorderColor = GetNewColor(borderInfo.Border.LeftBorderColor); + leftBorder.AppendChild(leftBorderColor); + border.AppendChild(leftBorder); + + var rightBorder = new RightBorder {Style = borderInfo.Border.RightBorder.ToOpenXml()}; + var rightBorderColor = GetNewColor(borderInfo.Border.RightBorderColor); + rightBorder.AppendChild(rightBorderColor); + border.AppendChild(rightBorder); + + var topBorder = new TopBorder {Style = borderInfo.Border.TopBorder.ToOpenXml()}; + var topBorderColor = GetNewColor(borderInfo.Border.TopBorderColor); + topBorder.AppendChild(topBorderColor); + border.AppendChild(topBorder); + + var bottomBorder = new BottomBorder {Style = borderInfo.Border.BottomBorder.ToOpenXml()}; + var bottomBorderColor = GetNewColor(borderInfo.Border.BottomBorderColor); + bottomBorder.AppendChild(bottomBorderColor); + border.AppendChild(bottomBorder); + + var diagonalBorder = new DiagonalBorder {Style = borderInfo.Border.DiagonalBorder.ToOpenXml()}; + var diagonalBorderColor = GetNewColor(borderInfo.Border.DiagonalBorderColor); + diagonalBorder.AppendChild(diagonalBorderColor); + border.AppendChild(diagonalBorder); + + return border; + } + + private bool BordersAreEqual(Border b, IXLBorder xlBorder) + { + var nb = new XLBorder(); + if (b.DiagonalUp != null) + nb.DiagonalUp = b.DiagonalUp.Value; + + if (b.DiagonalDown != null) + nb.DiagonalDown = b.DiagonalDown.Value; + + if (b.LeftBorder != null) + { + if (b.LeftBorder.Style != null) + nb.LeftBorder = b.LeftBorder.Style.Value.ToClosedXml(); + var bColor = GetColor(b.LeftBorder.Color); + if (bColor.HasValue) + nb.LeftBorderColor = bColor; + } + + if (b.RightBorder != null) + { + if (b.RightBorder.Style != null) + nb.RightBorder = b.RightBorder.Style.Value.ToClosedXml(); + var bColor = GetColor(b.RightBorder.Color); + if (bColor.HasValue) + nb.RightBorderColor = bColor; + } + + if (b.TopBorder != null) + { + if (b.TopBorder.Style != null) + nb.TopBorder = b.TopBorder.Style.Value.ToClosedXml(); + var bColor = GetColor(b.TopBorder.Color); + if (bColor.HasValue) + nb.TopBorderColor = bColor; + } + + if (b.BottomBorder != null) + { + if (b.BottomBorder.Style != null) + nb.BottomBorder = b.BottomBorder.Style.Value.ToClosedXml(); + var bColor = GetColor(b.BottomBorder.Color); + if (bColor.HasValue) + nb.BottomBorderColor = bColor; + } + + return nb.Equals(xlBorder); + } + + private Dictionary ResolveFills(WorkbookStylesPart workbookStylesPart, + Dictionary sharedFills) + { + if (workbookStylesPart.Stylesheet.Fills == null) + workbookStylesPart.Stylesheet.Fills = new Fills(); + + ResolveFillWithPattern(workbookStylesPart.Stylesheet.Fills, PatternValues.None); + ResolveFillWithPattern(workbookStylesPart.Stylesheet.Fills, PatternValues.Gray125); + + var allSharedFills = new Dictionary(); + foreach (FillInfo fillInfo in sharedFills.Values) + { + Int32 fillId = 0; + Boolean foundOne = false; + foreach (Fill f in workbookStylesPart.Stylesheet.Fills) + { + if (FillsAreEqual(f, fillInfo.Fill)) + { + foundOne = true; + break; + } + fillId++; + } + if (!foundOne) + { + var fill = GetNewFill(fillInfo); + workbookStylesPart.Stylesheet.Fills.AppendChild(fill); + } + allSharedFills.Add(fillInfo.Fill, new FillInfo {Fill = fillInfo.Fill, FillId = (UInt32)fillId}); + } + + workbookStylesPart.Stylesheet.Fills.Count = (UInt32)workbookStylesPart.Stylesheet.Fills.Count(); + return allSharedFills; + } + + private static void ResolveFillWithPattern(Fills fills, PatternValues patternValues) + { + if (!fills.Elements().Any(f => + f.PatternFill.PatternType == patternValues + && f.PatternFill.ForegroundColor == null + && f.PatternFill.BackgroundColor == null + )) + { + var fill1 = new Fill(); + var patternFill1 = new PatternFill {PatternType = patternValues}; + fill1.AppendChild(patternFill1); + fills.AppendChild(fill1); + } + } + + private static Fill GetNewFill(FillInfo fillInfo) + { + var fill = new Fill(); + + var patternFill = new PatternFill {PatternType = fillInfo.Fill.PatternType.ToOpenXml()}; + var foregroundColor = new ForegroundColor(); + if (fillInfo.Fill.PatternColor.ColorType == XLColorType.Color) + foregroundColor.Rgb = fillInfo.Fill.PatternColor.Color.ToHex(); + else if (fillInfo.Fill.PatternColor.ColorType == XLColorType.Indexed) + foregroundColor.Indexed = (UInt32)fillInfo.Fill.PatternColor.Indexed; + else + { + foregroundColor.Theme = (UInt32)fillInfo.Fill.PatternColor.ThemeColor; + if (fillInfo.Fill.PatternColor.ThemeTint != 1) + foregroundColor.Tint = fillInfo.Fill.PatternColor.ThemeTint; + } + var backgroundColor = new BackgroundColor(); + if (fillInfo.Fill.PatternBackgroundColor.ColorType == XLColorType.Color) + backgroundColor.Rgb = fillInfo.Fill.PatternBackgroundColor.Color.ToHex(); + else if (fillInfo.Fill.PatternBackgroundColor.ColorType == XLColorType.Indexed) + backgroundColor.Indexed = (UInt32)fillInfo.Fill.PatternBackgroundColor.Indexed; + else + { + backgroundColor.Theme = (UInt32)fillInfo.Fill.PatternBackgroundColor.ThemeColor; + if (fillInfo.Fill.PatternBackgroundColor.ThemeTint != 1) + backgroundColor.Tint = fillInfo.Fill.PatternBackgroundColor.ThemeTint; + } + + patternFill.AppendChild(foregroundColor); + patternFill.AppendChild(backgroundColor); + + fill.AppendChild(patternFill); + + return fill; + } + + private bool FillsAreEqual(Fill f, IXLFill xlFill) + { + var nF = new XLFill(); + if (f.PatternFill != null) + { + if (f.PatternFill.PatternType != null) + nF.PatternType = f.PatternFill.PatternType.Value.ToClosedXml(); + + var fColor = GetColor(f.PatternFill.ForegroundColor); + if (fColor.HasValue) + nF.PatternColor = fColor; + + var bColor = GetColor(f.PatternFill.BackgroundColor); + if (bColor.HasValue) + nF.PatternBackgroundColor = bColor; + } + return nF.Equals(xlFill); + } + + private void ResolveFonts(WorkbookStylesPart workbookStylesPart, SaveContext context) + { + if (workbookStylesPart.Stylesheet.Fonts == null) + workbookStylesPart.Stylesheet.Fonts = new Fonts(); + + var newFonts = new Dictionary(); + foreach (FontInfo fontInfo in context.SharedFonts.Values) + { + Int32 fontId = 0; + Boolean foundOne = false; + foreach (Font f in workbookStylesPart.Stylesheet.Fonts) + { + if (FontsAreEqual(f, fontInfo.Font)) + { + foundOne = true; + break; + } + fontId++; + } + if (!foundOne) + { + var font = GetNewFont(fontInfo); + workbookStylesPart.Stylesheet.Fonts.AppendChild(font); + } + newFonts.Add(fontInfo.Font, new FontInfo {Font = fontInfo.Font, FontId = (UInt32)fontId}); + } + context.SharedFonts.Clear(); + foreach (KeyValuePair kp in newFonts) + context.SharedFonts.Add(kp.Key, kp.Value); + + workbookStylesPart.Stylesheet.Fonts.Count = (UInt32)workbookStylesPart.Stylesheet.Fonts.Count(); + } + + private Font GetNewFont(FontInfo fontInfo) + { + var font = new Font(); + var bold = fontInfo.Font.Bold ? new Bold() : null; + var italic = fontInfo.Font.Italic ? new Italic() : null; + var underline = fontInfo.Font.Underline != XLFontUnderlineValues.None + ? new Underline {Val = fontInfo.Font.Underline.ToOpenXml()} + : null; + var strike = fontInfo.Font.Strikethrough ? new Strike() : null; + var verticalAlignment = new VerticalTextAlignment {Val = fontInfo.Font.VerticalAlignment.ToOpenXml()}; + var shadow = fontInfo.Font.Shadow ? new Shadow() : null; + var fontSize = new FontSize {Val = fontInfo.Font.FontSize}; + var color = GetNewColor(fontInfo.Font.FontColor); + + var fontName = new FontName {Val = fontInfo.Font.FontName}; + var fontFamilyNumbering = new FontFamilyNumbering {Val = (Int32)fontInfo.Font.FontFamilyNumbering}; + + if (bold != null) + font.AppendChild(bold); + if (italic != null) + font.AppendChild(italic); + if (underline != null) + font.AppendChild(underline); + if (strike != null) + font.AppendChild(strike); + font.AppendChild(verticalAlignment); + if (shadow != null) + font.AppendChild(shadow); + font.AppendChild(fontSize); + font.AppendChild(color); + font.AppendChild(fontName); + font.AppendChild(fontFamilyNumbering); + + return font; + } + + private Color GetNewColor(IXLColor xlColor) + { + var color = new Color(); + if (xlColor.ColorType == XLColorType.Color) + color.Rgb = xlColor.Color.ToHex(); + else if (xlColor.ColorType == XLColorType.Indexed) + color.Indexed = (UInt32)xlColor.Indexed; + else + { + color.Theme = (UInt32)xlColor.ThemeColor; + if (xlColor.ThemeTint != 1) + color.Tint = xlColor.ThemeTint; + } + return color; + } + + private TabColor GetTabColor(IXLColor xlColor) + { + var color = new TabColor(); + if (xlColor.ColorType == XLColorType.Color) + color.Rgb = xlColor.Color.ToHex(); + else if (xlColor.ColorType == XLColorType.Indexed) + color.Indexed = (UInt32)xlColor.Indexed; + else + { + color.Theme = (UInt32)xlColor.ThemeColor; + if (xlColor.ThemeTint != 1) + color.Tint = xlColor.ThemeTint; + } + return color; + } + + private bool FontsAreEqual(Font f, IXLFont xlFont) + { + var nf = new XLFont(); + nf.Bold = f.Bold != null; + nf.Italic = f.Italic != null; + if (f.Underline != null) + { + if (f.Underline.Val != null) + nf.Underline = f.Underline.Val.Value.ToClosedXml(); + else + nf.Underline = XLFontUnderlineValues.Single; + } + nf.Strikethrough = f.Strike != null; + if (f.VerticalTextAlignment != null) + { + if (f.VerticalTextAlignment.Val != null) + nf.VerticalAlignment = f.VerticalTextAlignment.Val.Value.ToClosedXml(); + else + nf.VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline; + } + nf.Shadow = f.Shadow != null; + if (f.FontSize != null) + nf.FontSize = f.FontSize.Val; + var fColor = GetColor(f.Color); + if (fColor.HasValue) + nf.FontColor = fColor; + if (f.FontName != null) + nf.FontName = f.FontName.Val; + if (f.FontFamilyNumbering != null) + nf.FontFamilyNumbering = (XLFontFamilyNumberingValues)f.FontFamilyNumbering.Val.Value; + + return nf.Equals(xlFont); + } + + private static Dictionary ResolveNumberFormats( + WorkbookStylesPart workbookStylesPart, + Dictionary sharedNumberFormats) + { + if (workbookStylesPart.Stylesheet.NumberingFormats == null) + workbookStylesPart.Stylesheet.NumberingFormats = new NumberingFormats(); + + var allSharedNumberFormats = new Dictionary(); + foreach (NumberFormatInfo numberFormatInfo in sharedNumberFormats.Values) + { + Int32 numberingFormatId = 0; + Boolean foundOne = false; + foreach (NumberingFormat nf in workbookStylesPart.Stylesheet.NumberingFormats) + { + if (NumberFormatsAreEqual(nf, numberFormatInfo.NumberFormat)) + { + foundOne = true; + numberingFormatId = (Int32)nf.NumberFormatId.Value; + break; + } + numberingFormatId++; + } + if (!foundOne) + { + var numberingFormat = new NumberingFormat + { + NumberFormatId = (UInt32)numberingFormatId, + FormatCode = numberFormatInfo.NumberFormat.Format + }; + workbookStylesPart.Stylesheet.NumberingFormats.AppendChild(numberingFormat); + } + allSharedNumberFormats.Add(numberFormatInfo.NumberFormat, + new NumberFormatInfo + { + NumberFormat = numberFormatInfo.NumberFormat, + NumberFormatId = numberingFormatId + }); + } + workbookStylesPart.Stylesheet.NumberingFormats.Count = + (UInt32)workbookStylesPart.Stylesheet.NumberingFormats.Count(); + return allSharedNumberFormats; + } + + private static bool NumberFormatsAreEqual(NumberingFormat nf, IXLNumberFormat xlNumberFormat) + { + var newXLNumberFormat = new XLNumberFormat(); + + if (nf.FormatCode != null && !StringExtensions.IsNullOrWhiteSpace(nf.FormatCode.Value)) + newXLNumberFormat.Format = nf.FormatCode.Value; + else if (nf.NumberFormatId != null) + newXLNumberFormat.NumberFormatId = (Int32)nf.NumberFormatId.Value; + + return newXLNumberFormat.Equals(xlNumberFormat); + } + + #endregion + + #region GenerateWorksheetPartContent + + private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet, + SaveContext context) + { + #region Worksheet + + if (worksheetPart.Worksheet == null) + worksheetPart.Worksheet = new Worksheet(); + + GenerateTables(xlWorksheet, worksheetPart, context); + + if ( + !worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair("r", + "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) + { + worksheetPart.Worksheet.AddNamespaceDeclaration("r", + "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); + } + + #endregion + + var cm = new XLWSContentManager(worksheetPart.Worksheet); + + #region SheetProperties + + if (worksheetPart.Worksheet.SheetProperties == null) + worksheetPart.Worksheet.SheetProperties = new SheetProperties(); + + if (xlWorksheet.TabColor.HasValue) + worksheetPart.Worksheet.SheetProperties.TabColor = GetTabColor(xlWorksheet.TabColor); + else + worksheetPart.Worksheet.SheetProperties.TabColor = null; + + cm.SetElement(XLWSContentManager.XLWSContents.SheetProperties, worksheetPart.Worksheet.SheetProperties); + + if (worksheetPart.Worksheet.SheetProperties.OutlineProperties == null) + worksheetPart.Worksheet.SheetProperties.OutlineProperties = new OutlineProperties(); + + worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryBelow = + (xlWorksheet.Outline.SummaryVLocation == + XLOutlineSummaryVLocation.Bottom); + worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryRight = + (xlWorksheet.Outline.SummaryHLocation == + XLOutlineSummaryHLocation.Right); + + if (worksheetPart.Worksheet.SheetProperties.PageSetupProperties == null && + (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0)) + worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties(); + + if (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0) + worksheetPart.Worksheet.SheetProperties.PageSetupProperties.FitToPage = true; + + #endregion + + UInt32 maxColumn = 0; + UInt32 maxRow = 0; + + String sheetDimensionReference = "A1"; + if (xlWorksheet.Internals.CellsCollection.Count > 0) + { + maxColumn = (UInt32)xlWorksheet.Internals.CellsCollection.Select(c => c.Key.ColumnNumber).Max(); + maxRow = (UInt32)xlWorksheet.Internals.CellsCollection.Select(c => c.Key.RowNumber).Max(); + sheetDimensionReference = "A1:" + ExcelHelper.GetColumnLetterFromNumber((Int32)maxColumn) + + ((Int32)maxRow).ToStringLookup(); + } + + if (xlWorksheet.Internals.ColumnsCollection.Count > 0) + { + UInt32 maxColCollection = (UInt32)xlWorksheet.Internals.ColumnsCollection.Keys.Max(); + if (maxColCollection > maxColumn) + maxColumn = maxColCollection; + } + + #region SheetViews + + if (worksheetPart.Worksheet.SheetDimension == null) + worksheetPart.Worksheet.SheetDimension = new SheetDimension {Reference = sheetDimensionReference}; + + cm.SetElement(XLWSContentManager.XLWSContents.SheetDimension, worksheetPart.Worksheet.SheetDimension); + + if (worksheetPart.Worksheet.SheetViews == null) + worksheetPart.Worksheet.SheetViews = new SheetViews(); + + cm.SetElement(XLWSContentManager.XLWSContents.SheetViews, worksheetPart.Worksheet.SheetViews); + + var sheetView = (SheetView)worksheetPart.Worksheet.SheetViews.FirstOrDefault(); + if (sheetView == null) + { + sheetView = new SheetView {WorkbookViewId = 0U}; + worksheetPart.Worksheet.SheetViews.AppendChild(sheetView); + } + + sheetView.TabSelected = xlWorksheet.TabSelected; + + if (xlWorksheet.ShowFormulas) + sheetView.ShowFormulas = true; + else + sheetView.ShowFormulas = null; + + if (xlWorksheet.ShowGridLines) + sheetView.ShowGridLines = null; + else + sheetView.ShowGridLines = false; + + if (xlWorksheet.ShowOutlineSymbols) + sheetView.ShowOutlineSymbols = null; + else + sheetView.ShowOutlineSymbols = false; + + if (xlWorksheet.ShowRowColHeaders) + sheetView.ShowRowColHeaders = null; + else + sheetView.ShowRowColHeaders = false; + + if (xlWorksheet.ShowRuler) + sheetView.ShowRuler = null; + else + sheetView.ShowRuler = false; + + if (xlWorksheet.ShowWhiteSpace) + sheetView.ShowWhiteSpace = null; + else + sheetView.ShowWhiteSpace = false; + + if (xlWorksheet.ShowZeros) + sheetView.ShowZeros = null; + else + sheetView.ShowZeros = false; + + var pane = sheetView.Elements().FirstOrDefault(); + if (pane == null) + { + pane = new Pane(); + sheetView.AppendChild(pane); + } + + pane.State = PaneStateValues.FrozenSplit; + Double hSplit = xlWorksheet.SheetView.SplitColumn; + Double ySplit = xlWorksheet.SheetView.SplitRow; + + + pane.HorizontalSplit = hSplit; + pane.VerticalSplit = ySplit; + + pane.TopLeftCell = ExcelHelper.GetColumnLetterFromNumber(xlWorksheet.SheetView.SplitColumn + 1) + + (xlWorksheet.SheetView.SplitRow + 1); + + if (hSplit == 0 && ySplit == 0) + sheetView.RemoveAllChildren(); + + #endregion + + int maxOutlineColumn = 0; + if (xlWorksheet.ColumnCount() > 0) + maxOutlineColumn = xlWorksheet.GetMaxColumnOutline(); + + int maxOutlineRow = 0; + if (xlWorksheet.RowCount() > 0) + maxOutlineRow = xlWorksheet.GetMaxRowOutline(); + + #region SheetFormatProperties + + if (worksheetPart.Worksheet.SheetFormatProperties == null) + worksheetPart.Worksheet.SheetFormatProperties = new SheetFormatProperties(); + + cm.SetElement(XLWSContentManager.XLWSContents.SheetFormatProperties, + worksheetPart.Worksheet.SheetFormatProperties); + + worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight = xlWorksheet.RowHeight; + worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = xlWorksheet.ColumnWidth; + if (xlWorksheet.RowHeightChanged) + worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true; + + if (maxOutlineColumn > 0) + worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = (byte)maxOutlineColumn; + else + worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = null; + + if (maxOutlineRow > 0) + worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = (byte)maxOutlineRow; + else + worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = null; + + #endregion + + #region Columns + + if (xlWorksheet.Internals.CellsCollection.Count == 0 && + xlWorksheet.Internals.ColumnsCollection.Count == 0) + worksheetPart.Worksheet.RemoveAllChildren(); + else + { + double worksheetColumnWidth = GetColumnWidth(xlWorksheet.ColumnWidth); + + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Columns); + worksheetPart.Worksheet.InsertAfter(new Columns(), previousElement); + } + + Columns columns = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.Columns, columns); + + var sheetColumnsByMin = columns.Elements().ToDictionary(c => c.Min.Value, c => c); + //Dictionary sheetColumnsByMax = columns.Elements().ToDictionary(c => c.Max.Value, c => c); + + Int32 minInColumnsCollection; + Int32 maxInColumnsCollection; + if (xlWorksheet.Internals.ColumnsCollection.Count > 0) + { + minInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Min(); + maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max(); + } + else + { + minInColumnsCollection = 1; + maxInColumnsCollection = 0; + } + + if (minInColumnsCollection > 1) + { + UInt32Value min = 1; + UInt32Value max = (UInt32)(minInColumnsCollection - 1); + uint styleId = context.SharedStyles[xlWorksheet.Style].StyleId; + + for (var co = min; co <= max; co++) + { + var column = new Column + { + Min = co, + Max = co, + Style = styleId, + Width = worksheetColumnWidth, + CustomWidth = true + }; + + UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax); + } + } + + for (int co = minInColumnsCollection; co <= maxInColumnsCollection; co++) + { + UInt32 styleId; + Double columnWidth; + Boolean isHidden = false; + Boolean collapsed = false; + Int32 outlineLevel = 0; + if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co)) + { + styleId = context.SharedStyles[xlWorksheet.Internals.ColumnsCollection[co].Style].StyleId; + columnWidth = GetColumnWidth(xlWorksheet.Internals.ColumnsCollection[co].Width); + isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden; + collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed; + outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel; + } + else + { + styleId = context.SharedStyles[xlWorksheet.Style].StyleId; + columnWidth = worksheetColumnWidth; + } + + var column = new Column + { + Min = (UInt32)co, + Max = (UInt32)co, + Style = styleId, + Width = columnWidth, + CustomWidth = true + }; + if (isHidden) + column.Hidden = true; + if (collapsed) + column.Collapsed = true; + if (outlineLevel > 0) + column.OutlineLevel = (byte)outlineLevel; + + UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax); + } + + int collection = maxInColumnsCollection; + foreach ( + Column col in + columns.Elements().Where(c => c.Min > (UInt32)(collection)).OrderBy( + c => c.Min.Value)) + { + col.Style = context.SharedStyles[xlWorksheet.Style].StyleId; + col.Width = worksheetColumnWidth; + col.CustomWidth = true; + if ((Int32)col.Max.Value > maxInColumnsCollection) + maxInColumnsCollection = (Int32)col.Max.Value; + } + + if (maxInColumnsCollection < ExcelHelper.MaxColumnNumber) + { + var column = new Column + { + Min = (UInt32)(maxInColumnsCollection + 1), + Max = (UInt32)(ExcelHelper.MaxColumnNumber), + Style = context.SharedStyles[xlWorksheet.Style].StyleId, + Width = worksheetColumnWidth, + CustomWidth = true + }; + columns.AppendChild(column); + } + + CollapseColumns(columns, sheetColumnsByMin); + } + + #endregion + + #region SheetData + + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetData); + worksheetPart.Worksheet.InsertAfter(new SheetData(), previousElement); + } + + SheetData sheetData = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData); + + var cellsByRow = new Dictionary>(); + foreach (XLCell c in xlWorksheet.Internals.CellsCollection.Values) + { + Int32 rowNum = c.Address.RowNumber; + if (!cellsByRow.ContainsKey(rowNum)) + cellsByRow.Add(rowNum, new List()); + + cellsByRow[rowNum].Add(c); + } + + var sheetDataRows = sheetData.Elements().ToDictionary(r => (Int32)r.RowIndex.Value, r => r); + foreach (KeyValuePair r in xlWorksheet.Internals.RowsCollection.Deleted) + { + if (sheetDataRows.ContainsKey(r.Key)) + { + sheetData.RemoveChild(sheetDataRows[r.Key]); + sheetDataRows.Remove(r.Key); + } + } + + var distinctRows = cellsByRow.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys); + Boolean noRows = (sheetData.Elements().FirstOrDefault() == null); + foreach (int distinctRow in distinctRows.OrderBy(r => r)) + { + Row row; // = sheetData.Elements().FirstOrDefault(r => r.RowIndex.Value == (UInt32)distinctRow); + if (sheetDataRows.ContainsKey(distinctRow)) + row = sheetDataRows[distinctRow]; + else + { + row = new Row {RowIndex = (UInt32)distinctRow}; + if (noRows) + { + sheetData.AppendChild(row); + noRows = false; + } + else + { + if (sheetDataRows.Any(r => r.Key > row.RowIndex.Value)) + { + int minRow = sheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r => r.Key); + var rowBeforeInsert = sheetDataRows[minRow]; + sheetData.InsertBefore(row, rowBeforeInsert); + } + else + sheetData.AppendChild(row); + } + } + + if (maxColumn > 0) + row.Spans = new ListValue {InnerText = "1:" + maxColumn}; + + row.Height = null; + row.CustomHeight = null; + row.Hidden = null; + row.StyleIndex = null; + row.CustomFormat = null; + row.Collapsed = null; + if (xlWorksheet.Internals.RowsCollection.ContainsKey(distinctRow)) + { + var thisRow = xlWorksheet.Internals.RowsCollection[distinctRow]; + if (thisRow.Height != xlWorksheet.RowHeight) + { + row.Height = thisRow.Height; + row.CustomHeight = true; + } + if (!thisRow.Style.Equals(xlWorksheet.Style)) + { + row.StyleIndex = context.SharedStyles[thisRow.Style].StyleId; + row.CustomFormat = true; + } + if (thisRow.IsHidden) + row.Hidden = true; + if (thisRow.Collapsed) + row.Collapsed = true; + if (thisRow.OutlineLevel > 0) + row.OutlineLevel = (byte)thisRow.OutlineLevel; + } + + + var cellsByReference = row.Elements().ToDictionary(c => c.CellReference.Value, c => c); + + foreach (KeyValuePair c in xlWorksheet.Internals.CellsCollection.Deleted) + { + if (cellsByReference.ContainsKey(c.Key.ToStringRelative())) + row.RemoveChild(cellsByReference[c.Key.ToStringRelative()]); + } + + if (cellsByRow.ContainsKey(distinctRow)) + { + Boolean isNewRow = !row.Elements().Any(); + foreach (XLCell opCell in cellsByRow[distinctRow] + .OrderBy(c => c.Address.ColumnNumber) + .Select(c => (XLCell)c)) + { + uint styleId = context.SharedStyles[opCell.Style].StyleId; + + var dataType = opCell.DataType; + string cellReference = (opCell.Address).GetTrimmedAddress(); + + Cell cell; + if (cellsByReference.ContainsKey(cellReference)) + cell = cellsByReference[cellReference]; + else + { + cell = new Cell {CellReference = new StringValue(cellReference)}; + if (isNewRow) + row.AppendChild(cell); + else + { + Int32 newColumn = ExcelHelper.GetColumnNumberFromAddress1(cellReference); + + Cell cellBeforeInsert = null; + Int32 lastCo = Int32.MaxValue; + foreach ( + Cell c in + row.Elements().Where( + c => + ExcelHelper.GetColumnNumberFromAddress1(c.CellReference.Value) > newColumn)) + { + int thidCo = ExcelHelper.GetColumnNumberFromAddress1(c.CellReference.Value); + if (lastCo > thidCo) + { + cellBeforeInsert = c; + lastCo = thidCo; + } + } + if (cellBeforeInsert == null) + row.AppendChild(cell); + else + row.InsertBefore(cell, cellBeforeInsert); + } + } + + cell.StyleIndex = styleId; + if (!StringExtensions.IsNullOrWhiteSpace(opCell.FormulaA1)) + { + String formula = opCell.FormulaA1; + if (formula.StartsWith("{")) + { + formula = formula.Substring(1, formula.Length - 2); + cell.CellFormula = new CellFormula(formula) + { + FormulaType = CellFormulaValues.Array, + Reference = cellReference + }; + } + else + cell.CellFormula = new CellFormula(formula); + cell.CellValue = null; + } + else + { + cell.CellFormula = null; + + cell.DataType = opCell.DataType == XLCellValues.DateTime ? null : GetCellValue(opCell); + + var cellValue = new CellValue(); + if (dataType == XLCellValues.Text) + { + if (StringExtensions.IsNullOrWhiteSpace(opCell.InnerText)) + cell.CellValue = null; + else + { + if (opCell.ShareString) + { + cellValue.Text = opCell.SharedStringId.ToString(); + cell.CellValue = cellValue; + } + else + cell.InlineString = new InlineString {Text = new Text(opCell.GetString())}; + } + } + else if (dataType == XLCellValues.TimeSpan) + { + var timeSpan = opCell.GetTimeSpan(); + cellValue.Text = + XLCell.BaseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture); + cell.CellValue = cellValue; + } + else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) + { + cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture); + cell.CellValue = cellValue; + } + else + { + cellValue.Text = opCell.InnerText; + cell.CellValue = cellValue; + } + } + } + } + } + + #endregion + + #region SheetProtection + + if (xlWorksheet.Protection.Protected) + { + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetProtection); + worksheetPart.Worksheet.InsertAfter(new SheetProtection(), previousElement); + } + + SheetProtection sheetProtection = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, sheetProtection); + + var protection = (XLSheetProtection)xlWorksheet.Protection; + sheetProtection.Sheet = protection.Protected; + if (!StringExtensions.IsNullOrWhiteSpace(protection.PasswordHash)) + sheetProtection.Password = protection.PasswordHash; + sheetProtection.FormatCells = GetBooleanValue(!protection.FormatCells, true); + sheetProtection.FormatColumns = GetBooleanValue(!protection.FormatColumns, true); + sheetProtection.FormatRows = GetBooleanValue(!protection.FormatRows, true); + sheetProtection.InsertColumns = GetBooleanValue(!protection.InsertColumns, true); + sheetProtection.InsertHyperlinks = GetBooleanValue(!protection.InsertHyperlinks, true); + sheetProtection.InsertRows = GetBooleanValue(!protection.InsertRows, true); + sheetProtection.DeleteColumns = GetBooleanValue(!protection.DeleteColumns, true); + sheetProtection.DeleteRows = GetBooleanValue(!protection.DeleteRows, true); + sheetProtection.AutoFilter = GetBooleanValue(!protection.AutoFilter, true); + sheetProtection.PivotTables = GetBooleanValue(!protection.PivotTables, true); + sheetProtection.Sort = GetBooleanValue(!protection.Sort, true); + sheetProtection.SelectLockedCells = GetBooleanValue(!protection.SelectLockedCells, false); + sheetProtection.SelectUnlockedCells = GetBooleanValue(!protection.SelectUnlockedCells, false); + } + else + { + worksheetPart.Worksheet.RemoveAllChildren(); + cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, null); + } + + #endregion + + #region AutoFilter + + if (xlWorksheet.AutoFilterRange != null) + { + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.AutoFilter); + worksheetPart.Worksheet.InsertAfter(new AutoFilter(), previousElement); + } + + var autoFilter = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, autoFilter); + + autoFilter.Reference = xlWorksheet.AutoFilterRange.RangeAddress.ToString(); + } + else + { + worksheetPart.Worksheet.RemoveAllChildren(); + cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, null); + } + + #endregion + + #region MergeCells + + if (xlWorksheet.Internals.MergedRanges.Any()) + { + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.MergeCells); + worksheetPart.Worksheet.InsertAfter(new MergeCells(), previousElement); + } + + MergeCells mergeCells = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, mergeCells); + mergeCells.RemoveAllChildren(); + + foreach (SheetRange mergedRange in xlWorksheet.Internals.MergedRanges) + { + var mergeCell = new MergeCell {Reference = mergedRange.ToStringA1()}; + mergeCells.AppendChild(mergeCell); + } + + mergeCells.Count = (UInt32)mergeCells.Count(); + } + else + { + worksheetPart.Worksheet.RemoveAllChildren(); + cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, null); + } + + #endregion + + #region DataValidations + + if (!xlWorksheet.DataValidations.Any()) + { + worksheetPart.Worksheet.RemoveAllChildren(); + cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, null); + } + else + { + worksheetPart.Worksheet.Elements().FirstOrDefault(); + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.DataValidations); + worksheetPart.Worksheet.InsertAfter(new DataValidations(), previousElement); + } + + DataValidations dataValidations = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, dataValidations); + dataValidations.RemoveAllChildren(); + foreach (IXLDataValidation dv in xlWorksheet.DataValidations) + { + String sequence = dv.Ranges.Aggregate(String.Empty, (current, r) => current + (r.RangeAddress + " ")); + + if (sequence.Length > 0) + sequence = sequence.Substring(0, sequence.Length - 1); + + var dataValidation = new DataValidation + { + AllowBlank = dv.IgnoreBlanks, + Formula1 = new Formula1(dv.MinValue), + Formula2 = new Formula2(dv.MaxValue), + Type = dv.AllowedValues.ToOpenXml(), + ShowErrorMessage = dv.ShowErrorMessage, + Prompt = dv.InputMessage, + PromptTitle = dv.InputTitle, + ErrorTitle = dv.ErrorTitle, + Error = dv.ErrorMessage, + ShowDropDown = !dv.InCellDropdown, + ShowInputMessage = dv.ShowInputMessage, + ErrorStyle = dv.ErrorStyle.ToOpenXml(), + Operator = dv.Operator.ToOpenXml(), + SequenceOfReferences = + new ListValue {InnerText = sequence} + }; + + dataValidations.AppendChild(dataValidation); + } + dataValidations.Count = (UInt32)xlWorksheet.DataValidations.Count(); + } + + #endregion + + #region Hyperlinks + + var relToRemove = worksheetPart.HyperlinkRelationships.ToList(); + relToRemove.ForEach(worksheetPart.DeleteReferenceRelationship); + if (!xlWorksheet.Hyperlinks.Any()) + { + worksheetPart.Worksheet.RemoveAllChildren(); + cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, null); + } + else + { + worksheetPart.Worksheet.Elements().FirstOrDefault(); + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Hyperlinks); + worksheetPart.Worksheet.InsertAfter(new Hyperlinks(), previousElement); + } + + Hyperlinks hyperlinks = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, hyperlinks); + hyperlinks.RemoveAllChildren(); + foreach (XLHyperlink hl in xlWorksheet.Hyperlinks) + { + Hyperlink hyperlink; + if (hl.IsExternal) + { + String rId = context.RelIdGenerator.GetNext(RelType.Workbook); + hyperlink = new Hyperlink {Reference = hl.Cell.Address.ToString(), Id = rId}; + worksheetPart.AddHyperlinkRelationship(hl.ExternalAddress, true, rId); + } + else + { + hyperlink = new Hyperlink + { + Reference = hl.Cell.Address.ToString(), + Location = hl.InternalAddress, + Display = hl.Cell.GetFormattedString() + }; + } + if (!StringExtensions.IsNullOrWhiteSpace(hl.Tooltip)) + hyperlink.Tooltip = hl.Tooltip; + hyperlinks.AppendChild(hyperlink); + } + } + + #endregion + + #region PrintOptions + + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PrintOptions); + worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement); + } + + PrintOptions printOptions = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.PrintOptions, printOptions); + + printOptions.HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally; + printOptions.VerticalCentered = xlWorksheet.PageSetup.CenterVertically; + printOptions.Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings; + printOptions.GridLines = xlWorksheet.PageSetup.ShowGridlines; + + #endregion + + #region PageMargins + + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageMargins); + worksheetPart.Worksheet.InsertAfter(new PageMargins(), previousElement); + } + + var pageMargins = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.PageMargins, pageMargins); + pageMargins.Left = xlWorksheet.PageSetup.Margins.Left; + pageMargins.Right = xlWorksheet.PageSetup.Margins.Right; + pageMargins.Top = xlWorksheet.PageSetup.Margins.Top; + pageMargins.Bottom = xlWorksheet.PageSetup.Margins.Bottom; + pageMargins.Header = xlWorksheet.PageSetup.Margins.Header; + pageMargins.Footer = xlWorksheet.PageSetup.Margins.Footer; + + #endregion + + #region PageSetup + + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageSetup); + worksheetPart.Worksheet.InsertAfter(new PageSetup(), previousElement); + } + + var pageSetup = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.PageSetup, pageSetup); + + pageSetup.Orientation = xlWorksheet.PageSetup.PageOrientation.ToOpenXml(); + pageSetup.PaperSize = (UInt32)xlWorksheet.PageSetup.PaperSize; + pageSetup.BlackAndWhite = xlWorksheet.PageSetup.BlackAndWhite; + pageSetup.Draft = xlWorksheet.PageSetup.DraftQuality; + pageSetup.PageOrder = xlWorksheet.PageSetup.PageOrder.ToOpenXml(); + pageSetup.CellComments = xlWorksheet.PageSetup.ShowComments.ToOpenXml(); + pageSetup.Errors = xlWorksheet.PageSetup.PrintErrorValue.ToOpenXml(); + + if (xlWorksheet.PageSetup.FirstPageNumber > 0) + { + pageSetup.FirstPageNumber = (UInt32)xlWorksheet.PageSetup.FirstPageNumber; + pageSetup.UseFirstPageNumber = true; + } + else + { + pageSetup.FirstPageNumber = null; + pageSetup.UseFirstPageNumber = null; + } + + if (xlWorksheet.PageSetup.HorizontalDpi > 0) + pageSetup.HorizontalDpi = (UInt32)xlWorksheet.PageSetup.HorizontalDpi; + else + pageSetup.HorizontalDpi = null; + + if (xlWorksheet.PageSetup.VerticalDpi > 0) + pageSetup.VerticalDpi = (UInt32)xlWorksheet.PageSetup.VerticalDpi; + else + pageSetup.VerticalDpi = null; + + if (xlWorksheet.PageSetup.Scale > 0) + { + pageSetup.Scale = (UInt32)xlWorksheet.PageSetup.Scale; + pageSetup.FitToWidth = null; + pageSetup.FitToHeight = null; + } + else + { + pageSetup.Scale = null; + + if (xlWorksheet.PageSetup.PagesWide > 0) + pageSetup.FitToWidth = (UInt32)xlWorksheet.PageSetup.PagesWide; + else + pageSetup.FitToWidth = 0; + + if (xlWorksheet.PageSetup.PagesTall > 0) + pageSetup.FitToHeight = (UInt32)xlWorksheet.PageSetup.PagesTall; + else + pageSetup.FitToHeight = 0; + } + + #endregion + + #region HeaderFooter + + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.HeaderFooter); + worksheetPart.Worksheet.InsertAfter(new HeaderFooter(), previousElement); + } + + var headerFooter = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.HeaderFooter, headerFooter); + headerFooter.RemoveAllChildren(); + + headerFooter.ScaleWithDoc = xlWorksheet.PageSetup.ScaleHFWithDocument; + headerFooter.AlignWithMargins = xlWorksheet.PageSetup.AlignHFWithMargins; + headerFooter.DifferentFirst = true; + headerFooter.DifferentOddEven = true; + + var oddHeader = new OddHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.OddPages)); + headerFooter.AppendChild(oddHeader); + var oddFooter = new OddFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.OddPages)); + headerFooter.AppendChild(oddFooter); + + var evenHeader = new EvenHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.EvenPages)); + headerFooter.AppendChild(evenHeader); + var evenFooter = new EvenFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.EvenPages)); + headerFooter.AppendChild(evenFooter); + + var firstHeader = new FirstHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.FirstPage)); + headerFooter.AppendChild(firstHeader); + var firstFooter = new FirstFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.FirstPage)); + headerFooter.AppendChild(firstFooter); + + //if (!headerFooter.Any(hf => hf.InnerText.Length > 0)) + // worksheetPart.Worksheet.RemoveAllChildren(); + + #endregion + + #region RowBreaks + + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks); + worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement); + } + + var rowBreaks = worksheetPart.Worksheet.Elements().First(); + + int rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count; + if (rowBreakCount > 0) + { + rowBreaks.Count = (UInt32)rowBreakCount; + rowBreaks.ManualBreakCount = (UInt32)rowBreakCount; + foreach (int rb in xlWorksheet.PageSetup.RowBreaks) + { + var break1 = new Break + { + Id = (UInt32)rb, + Max = (UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber, + ManualPageBreak = true + }; + rowBreaks.AppendChild(break1); + } + cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, rowBreaks); + } + else + { + worksheetPart.Worksheet.RemoveAllChildren(); + cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, null); + } + + #endregion + + #region ColumnBreaks + + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks); + worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement); + } + + var columnBreaks = worksheetPart.Worksheet.Elements().First(); + + int columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count; + if (columnBreakCount > 0) + { + columnBreaks.Count = (UInt32)columnBreakCount; + columnBreaks.ManualBreakCount = (UInt32)columnBreakCount; + foreach (int cb in xlWorksheet.PageSetup.ColumnBreaks) + { + var break1 = new Break + { + Id = (UInt32)cb, + Max = (UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber, + ManualPageBreak = true + }; + columnBreaks.AppendChild(break1); + } + cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, columnBreaks); + } + else + { + worksheetPart.Worksheet.RemoveAllChildren(); + cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, null); + } + + #endregion + + #region Drawings + + //worksheetPart.Worksheet.RemoveAllChildren(); + //{ + // OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Drawing); + // worksheetPart.Worksheet.InsertAfter(new Drawing() { Id = String.Format("rId{0}", 1) }, previousElement); + //} + + //Drawing drawing = worksheetPart.Worksheet.Elements().First(); + //cm.SetElement(XLWSContentManager.XLWSContents.Drawing, drawing); + + #endregion + + #region Tables + + worksheetPart.Worksheet.RemoveAllChildren(); + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts); + worksheetPart.Worksheet.InsertAfter(new TableParts(), previousElement); + } + + var tableParts = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts); + + tableParts.Count = (UInt32)xlWorksheet.Tables.Count(); + foreach (IXLTable table in xlWorksheet.Tables) + { + var xlTable = (XLTable)table; + var tablePart = new TablePart {Id = xlTable.RelId}; + tableParts.AppendChild(tablePart); + } + + #endregion + } + + private static BooleanValue GetBooleanValue(bool value, bool defaultValue) + { + return value == defaultValue ? null : new BooleanValue(value); + } + + private static void CollapseColumns(Columns columns, Dictionary sheetColumns) + { + UInt32 lastMin = 1; + Int32 count = sheetColumns.Count; + foreach (KeyValuePair kp in sheetColumns.OrderBy(kp => kp.Key)) + { + if (!(kp.Key < count && ColumnsAreEqual(kp.Value, sheetColumns[kp.Key + 1]))) + { + var newColumn = (Column)kp.Value.CloneNode(true); + newColumn.Min = lastMin; + var columnsToRemove = columns.Elements().Where(co => co.Min >= newColumn.Min && co.Max <= newColumn.Max).Select(co => co).ToList(); + columnsToRemove.ForEach(c => columns.RemoveChild(c)); + + columns.AppendChild(newColumn); + + lastMin = kp.Key + 1; + } + } + } + + private static double GetColumnWidth(double columnWidth) + { + if (columnWidth > 0) + return columnWidth + COLUMN_WIDTH_OFFSET; + return columnWidth; + } + + private static void UpdateColumn(Column column, Columns columns, Dictionary sheetColumnsByMin) + //, Dictionary sheetColumnsByMax) + { + UInt32 co = column.Min.Value; + Column newColumn; + Column existingColumn; // = columns.Elements().FirstOrDefault(c => c.Min.Value == column.Min.Value); + if (!sheetColumnsByMin.ContainsKey(co)) + { + //if (sheetColumnsByMin.ContainsKey(co + 1) && ColumnsAreEqual(column, sheetColumnsByMin[co + 1])) + //{ + // var thisColumn = sheetColumnsByMin[co + 1]; + // thisColumn.Min -= 1; + // sheetColumnsByMin.Remove(co + 1); + // sheetColumnsByMin.Add(co, thisColumn); + //} + //else if (sheetColumnsByMax.ContainsKey(co - 1) && ColumnsAreEqual(column, sheetColumnsByMin[co - 1])) + //{ + // var thisColumn = sheetColumnsByMin[co - 1]; + // thisColumn.Max += 1; + // sheetColumnsByMax.Remove(co - 1); + // sheetColumnsByMax.Add(co, thisColumn); + //} + //else + //{ + newColumn = (Column)column.CloneNode(true); + columns.AppendChild(newColumn); + sheetColumnsByMin.Add(co, newColumn); + // sheetColumnsByMax.Add(co, newColumn); + //} + } + else + { + existingColumn = sheetColumnsByMin[column.Min.Value]; + newColumn = (Column)existingColumn.CloneNode(true); + //newColumn = new Column() { InnerXml = existingColumn.InnerXml }; + newColumn.Min = column.Min; + newColumn.Max = column.Max; + newColumn.Style = column.Style; + newColumn.Width = column.Width; + newColumn.CustomWidth = column.CustomWidth; + + if (column.Hidden != null) + newColumn.Hidden = true; + else + newColumn.Hidden = null; + + if (column.Collapsed != null) + newColumn.Collapsed = true; + else + newColumn.Collapsed = null; + + if (column.OutlineLevel != null && column.OutlineLevel > 0) + newColumn.OutlineLevel = (byte)column.OutlineLevel; + else + newColumn.OutlineLevel = null; + + sheetColumnsByMin.Remove(column.Min.Value); + if (existingColumn.Min + 1 > existingColumn.Max) + { + //existingColumn.Min = existingColumn.Min + 1; + //columns.InsertBefore(existingColumn, newColumn); + //existingColumn.Remove(); + columns.RemoveChild(existingColumn); + columns.AppendChild(newColumn); + sheetColumnsByMin.Add(newColumn.Min.Value, newColumn); + } + else + { + //columns.InsertBefore(existingColumn, newColumn); + columns.AppendChild(newColumn); + sheetColumnsByMin.Add(newColumn.Min.Value, newColumn); + existingColumn.Min = existingColumn.Min + 1; + sheetColumnsByMin.Add(existingColumn.Min.Value, existingColumn); + } + } + } + + private static bool ColumnsAreEqual(Column left, Column right) + { + return + left.Style.Value == right.Style.Value + && left.Width.Value == right.Width.Value + && ((left.Hidden == null && right.Hidden == null) + || (left.Hidden != null && right.Hidden != null && left.Hidden.Value == right.Hidden.Value)) + && ((left.Collapsed == null && right.Collapsed == null) + || + (left.Collapsed != null && right.Collapsed != null && left.Collapsed.Value == right.Collapsed.Value)) + && ((left.OutlineLevel == null && right.OutlineLevel == null) + || + (left.OutlineLevel != null && right.OutlineLevel != null && + left.OutlineLevel.Value == right.OutlineLevel.Value)); + } + + #endregion + //private void GenerateDrawingsPartContent(DrawingsPart drawingsPart, XLWorksheet worksheet) //{ // if (drawingsPart.WorksheetDrawing == null) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 9c2fa04..137e26e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -645,38 +645,38 @@ public IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName, Int32 position) { - var ws = (XLWorksheet) workbook.WorksheetsInternal.Add(newSheetName, position); + var targetSheet = (XLWorksheet) workbook.WorksheetsInternal.Add(newSheetName, position); - Internals.CellsCollection.ForEach(kp => ws.Cell(kp.Value.Address.RowNumber, kp.Value.Address.ColumnNumber).CopyFrom(kp.Value)); - DataValidations.ForEach(dv => ws.DataValidations.Add(new XLDataValidation(dv, ws))); - Internals.ColumnsCollection.ForEach(kp => ws.Internals.ColumnsCollection.Add(kp.Key, new XLColumn(kp.Value))); - Internals.RowsCollection.ForEach(kp => ws.Internals.RowsCollection.Add(kp.Key, new XLRow(kp.Value))); - ws.Visibility = Visibility; - ws.ColumnWidth = ColumnWidth; - ws.RowHeight = RowHeight; - ws.style = new XLStyle(ws, style); - ws.PageSetup = new XLPageSetup(PageSetup, ws); - ws.Outline = new XLOutline(Outline); - ws.SheetView = new XLSheetView(SheetView); - Internals.MergedRanges = ws.Internals.MergedRanges.Clone(); + Internals.CellsCollection.ForEach(kp => targetSheet.Cell(kp.Value.Address.RowNumber, kp.Value.Address.ColumnNumber).CopyFrom(kp.Value)); + DataValidations.ForEach(dv => targetSheet.DataValidations.Add(new XLDataValidation(dv, targetSheet))); + Internals.ColumnsCollection.ForEach(kp => targetSheet.Internals.ColumnsCollection.Add(kp.Key, new XLColumn(kp.Value))); + Internals.RowsCollection.ForEach(kp => targetSheet.Internals.RowsCollection.Add(kp.Key, new XLRow(kp.Value))); + targetSheet.Visibility = Visibility; + targetSheet.ColumnWidth = ColumnWidth; + targetSheet.RowHeight = RowHeight; + targetSheet.style = new XLStyle(targetSheet, style); + targetSheet.PageSetup = new XLPageSetup(PageSetup, targetSheet); + targetSheet.Outline = new XLOutline(Outline); + targetSheet.SheetView = new XLSheetView(SheetView); + targetSheet.Internals.MergedRanges = Internals.MergedRanges.Clone(); foreach (var r in NamedRanges) { var ranges = new XLRanges(); r.Ranges.ForEach(ranges.Add); - ws.NamedRanges.Add(r.Name, ranges); + targetSheet.NamedRanges.Add(r.Name, ranges); } foreach (var t in Tables.Cast()) { XLTable table; - if (ws.Tables.Any(tt => tt.Name == t.Name)) + if (targetSheet.Tables.Any(tt => tt.Name == t.Name)) { - table = new XLTable(ws.Range(t.RangeAddress.ToString()), true); + table = new XLTable(targetSheet.Range(t.RangeAddress.ToString()), true); } else { - table = new XLTable(ws.Range(t.RangeAddress.ToString()), t.Name, true); + table = new XLTable(targetSheet.Range(t.RangeAddress.ToString()), t.Name, true); } table.RelId = t.RelId; @@ -702,10 +702,10 @@ if (AutoFilterRange != null) { - ws.Range(AutoFilterRange.RangeAddress).SetAutoFilter(); + targetSheet.Range(AutoFilterRange.RangeAddress).SetAutoFilter(); } - return ws; + return targetSheet; } #region Outlines public void IncrementColumnOutline(Int32 level)