diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs index 888ba9f..ba6d2ee 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs @@ -1,178 +1,163 @@ -using System; +#region + +using System; using System.Text; +#endregion + namespace ClosedXML.Excel { internal class XLAlignment : IXLAlignment { - IXLStylized container; + private readonly IXLStylized _container; + private XLAlignmentHorizontalValues _horizontal; + private Int32 _indent; + private Boolean _justifyLastLine; + private XLAlignmentReadingOrderValues _readingOrder; + private Int32 _relativeIndent; + private Boolean _shrinkToFit; + private Int32 _textRotation; + private XLAlignmentVerticalValues _vertical; + private Boolean _wrapText; - public XLAlignment() : this(null, XLWorkbook.DefaultStyle.Alignment) { } + public XLAlignment() : this(null, XLWorkbook.DefaultStyle.Alignment) + { + } public XLAlignment(IXLStylized container, IXLAlignment d = null) { - this.container = container; + _container = container; if (d != null) { - horizontal = d.Horizontal; - vertical = d.Vertical; - indent = d.Indent; - justifyLastLine = d.JustifyLastLine; - readingOrder = d.ReadingOrder; - relativeIndent = d.RelativeIndent; - shrinkToFit = d.ShrinkToFit; - textRotation = d.TextRotation; - wrapText = d.WrapText; + _horizontal = d.Horizontal; + _vertical = d.Vertical; + _indent = d.Indent; + _justifyLastLine = d.JustifyLastLine; + _readingOrder = d.ReadingOrder; + _relativeIndent = d.RelativeIndent; + _shrinkToFit = d.ShrinkToFit; + _textRotation = d.TextRotation; + _wrapText = d.WrapText; } } - private XLAlignmentHorizontalValues horizontal; + #region IXLAlignment Members + public XLAlignmentHorizontalValues Horizontal { - get - { - return horizontal; - } + get { return _horizontal; } set { Boolean updateIndent = !( - value == XLAlignmentHorizontalValues.Left - || value == XLAlignmentHorizontalValues.Right - || value == XLAlignmentHorizontalValues.Distributed - ); + value == XLAlignmentHorizontalValues.Left + || value == XLAlignmentHorizontalValues.Right + || value == XLAlignmentHorizontalValues.Distributed + ); - if (container != null && !container.UpdatingStyle) + if (_container != null && !_container.UpdatingStyle) { - container.Styles.ForEach(s => { - s.Alignment.Horizontal = value; - if (updateIndent) - s.Alignment.Indent = 0; - }); + _container.Styles.ForEach(s => + { + s.Alignment.Horizontal = value; + if (updateIndent) + s.Alignment.Indent = 0; + }); } else { - horizontal = value; + _horizontal = value; if (updateIndent) - indent = 0; + _indent = 0; } } } - private XLAlignmentVerticalValues vertical; public XLAlignmentVerticalValues Vertical { - get - { - return vertical; - } + get { return _vertical; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Alignment.Vertical = value); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Alignment.Vertical = value); else - vertical = value; + _vertical = value; } } - private Int32 indent; public Int32 Indent { - get - { - return indent; - } + get { return _indent; } set { if (Horizontal == XLAlignmentHorizontalValues.General) Horizontal = XLAlignmentHorizontalValues.Left; if (value > 0 && !( - Horizontal == XLAlignmentHorizontalValues.Left - || Horizontal == XLAlignmentHorizontalValues.Right - || Horizontal == XLAlignmentHorizontalValues.Distributed - )) - { - throw new ArgumentException("For indents, only left, right, and distributed horizontal alignments are supported."); - } + Horizontal == XLAlignmentHorizontalValues.Left + || Horizontal == XLAlignmentHorizontalValues.Right + || Horizontal == XLAlignmentHorizontalValues.Distributed + )) + throw new ArgumentException( + "For indents, only left, right, and distributed horizontal alignments are supported."); - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Alignment.Indent = value); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Alignment.Indent = value); else - indent = value; + _indent = value; } } - private Boolean justifyLastLine; public Boolean JustifyLastLine { - get - { - return justifyLastLine; - } + get { return _justifyLastLine; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Alignment.JustifyLastLine = value); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Alignment.JustifyLastLine = value); else - justifyLastLine = value; + _justifyLastLine = value; } } - private XLAlignmentReadingOrderValues readingOrder; public XLAlignmentReadingOrderValues ReadingOrder { - get - { - return readingOrder; - } + get { return _readingOrder; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Alignment.ReadingOrder = value); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Alignment.ReadingOrder = value); else - readingOrder = value; + _readingOrder = value; } } - private Int32 relativeIndent; public Int32 RelativeIndent { - get - { - return relativeIndent; - } + get { return _relativeIndent; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Alignment.RelativeIndent = value); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Alignment.RelativeIndent = value); else - relativeIndent = value; + _relativeIndent = value; } } - private Boolean shrinkToFit; public Boolean ShrinkToFit { - get - { - return shrinkToFit; - } + get { return _shrinkToFit; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Alignment.ShrinkToFit = value); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Alignment.ShrinkToFit = value); else - shrinkToFit = value; + _shrinkToFit = value; } } - private Int32 textRotation; public Int32 TextRotation { - get - { - return textRotation; - } + get { return _textRotation; } set { Int32 rotation = value; @@ -181,47 +166,148 @@ throw new ArgumentException("TextRotation must be between -90 and 180 degrees, or 255."); if (rotation < 0) - rotation = 90 + (rotation * -1); + rotation = 90 + (rotation*-1); - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Alignment.TextRotation = rotation); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Alignment.TextRotation = rotation); else - textRotation = rotation; + _textRotation = rotation; } } - private Boolean wrapText; public Boolean WrapText { - get - { - return wrapText; - } + get { return _wrapText; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Alignment.WrapText = value); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Alignment.WrapText = value); else - wrapText = value; + _wrapText = value; } } public Boolean TopToBottom { - get - { - return textRotation == 255; - } + get { return _textRotation == 255; } set { - if (container != null && !container.UpdatingStyle) - container.Styles.ForEach(s => s.Alignment.TextRotation = value ? 255 : 0 ); + if (_container != null && !_container.UpdatingStyle) + _container.Styles.ForEach(s => s.Alignment.TextRotation = value ? 255 : 0); else - textRotation = value ? 255 : 0; + _textRotation = value ? 255 : 0; } } + public bool Equals(IXLAlignment other) + { + if (other == null) + return false; + + var otherA = other as XLAlignment; + if (otherA == null) + return false; + + return + _horizontal == otherA._horizontal + && _vertical == otherA._vertical + && _indent == otherA._indent + && _justifyLastLine == otherA._justifyLastLine + && _readingOrder == otherA._readingOrder + && _relativeIndent == otherA._relativeIndent + && _shrinkToFit == otherA._shrinkToFit + && _textRotation == otherA._textRotation + && _wrapText == otherA._wrapText + ; + } + + public IXLStyle SetHorizontal(XLAlignmentHorizontalValues value) + { + Horizontal = value; + return _container.Style; + } + + public IXLStyle SetVertical(XLAlignmentVerticalValues value) + { + Vertical = value; + return _container.Style; + } + + public IXLStyle SetIndent(Int32 value) + { + Indent = value; + return _container.Style; + } + + public IXLStyle SetJustifyLastLine() + { + JustifyLastLine = true; + return _container.Style; + } + + public IXLStyle SetJustifyLastLine(Boolean value) + { + JustifyLastLine = value; + return _container.Style; + } + + public IXLStyle SetReadingOrder(XLAlignmentReadingOrderValues value) + { + ReadingOrder = value; + return _container.Style; + } + + public IXLStyle SetRelativeIndent(Int32 value) + { + RelativeIndent = value; + return _container.Style; + } + + public IXLStyle SetShrinkToFit() + { + ShrinkToFit = true; + return _container.Style; + } + + public IXLStyle SetShrinkToFit(Boolean value) + { + ShrinkToFit = value; + return _container.Style; + } + + public IXLStyle SetTextRotation(Int32 value) + { + TextRotation = value; + return _container.Style; + } + + public IXLStyle SetWrapText() + { + WrapText = true; + return _container.Style; + } + + public IXLStyle SetWrapText(Boolean value) + { + WrapText = value; + return _container.Style; + } + + public IXLStyle SetTopToBottom() + { + TopToBottom = true; + return _container.Style; + } + + public IXLStyle SetTopToBottom(Boolean value) + { + TopToBottom = value; + return _container.Style; + } + + #endregion + public override string ToString() { var sb = new StringBuilder(); @@ -248,49 +334,20 @@ public override bool Equals(object obj) { - return this.Equals((XLAlignment)obj); + return Equals((XLAlignment) obj); } public override int GetHashCode() { - return (Int32)Horizontal - ^ (Int32)Vertical - ^ Indent - ^ JustifyLastLine.GetHashCode() - ^ (Int32)ReadingOrder - ^ RelativeIndent - ^ ShrinkToFit.GetHashCode() - ^ TextRotation - ^ WrapText.GetHashCode(); + return (Int32) Horizontal + ^ (Int32) Vertical + ^ Indent + ^ JustifyLastLine.GetHashCode() + ^ (Int32) ReadingOrder + ^ RelativeIndent + ^ ShrinkToFit.GetHashCode() + ^ TextRotation + ^ WrapText.GetHashCode(); } - - public bool Equals(IXLAlignment other) - { - var otherA = other as XLAlignment; - return - horizontal == otherA.horizontal - && vertical == otherA.vertical - && indent == otherA.indent - && justifyLastLine == otherA.justifyLastLine - && readingOrder == otherA.readingOrder - && relativeIndent == otherA.relativeIndent - && shrinkToFit == otherA.shrinkToFit - && textRotation == otherA.textRotation - && wrapText == otherA.wrapText - ; - } - - public IXLStyle SetHorizontal(XLAlignmentHorizontalValues value) { Horizontal = value; return container.Style; } - public IXLStyle SetVertical(XLAlignmentVerticalValues value) { Vertical = value; return container.Style; } - public IXLStyle SetIndent(Int32 value) { Indent = value; return container.Style; } - public IXLStyle SetJustifyLastLine() { JustifyLastLine = true; return container.Style; } public IXLStyle SetJustifyLastLine(Boolean value) { JustifyLastLine = value; return container.Style; } - public IXLStyle SetReadingOrder(XLAlignmentReadingOrderValues value) { ReadingOrder = value; return container.Style; } - public IXLStyle SetRelativeIndent(Int32 value) { RelativeIndent = value; return container.Style; } - public IXLStyle SetShrinkToFit() { ShrinkToFit = true; return container.Style; } public IXLStyle SetShrinkToFit(Boolean value) { ShrinkToFit = value; return container.Style; } - public IXLStyle SetTextRotation(Int32 value) { TextRotation = value; return container.Style; } - public IXLStyle SetWrapText() { WrapText = true; return container.Style; } public IXLStyle SetWrapText(Boolean value) { WrapText = value; return container.Style; } - public IXLStyle SetTopToBottom() { TopToBottom = true; return container.Style; } public IXLStyle SetTopToBottom(Boolean value) { TopToBottom = value; return container.Style; } - - } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index e750fef..78aa546 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -22,14 +22,14 @@ public partial class XLWorkbook { #region Static - private static IXLStyle ms_defaultStyle; + private static IXLStyle _defaultStyle; public static IXLStyle DefaultStyle { get { - if (ms_defaultStyle == null) + if (_defaultStyle == null) { - ms_defaultStyle = new XLStyle(null, null) + _defaultStyle = new XLStyle(null, null) { Font = new XLFont(null, null) { @@ -67,15 +67,15 @@ NumberFormat = new XLNumberFormat(null, null) {NumberFormatId = 0}, Alignment = new XLAlignment(null) { - Horizontal = XLAlignmentHorizontalValues.General, - Indent = 0, - JustifyLastLine = false, - ReadingOrder = XLAlignmentReadingOrderValues.ContextDependent, - RelativeIndent = 0, - ShrinkToFit = false, - TextRotation = 0, - Vertical = XLAlignmentVerticalValues.Bottom, - WrapText = false + Indent = 0, + Horizontal = XLAlignmentHorizontalValues.General, + JustifyLastLine = false, + ReadingOrder = XLAlignmentReadingOrderValues.ContextDependent, + RelativeIndent = 0, + ShrinkToFit = false, + TextRotation = 0, + Vertical = XLAlignmentVerticalValues.Bottom, + WrapText = false }, Protection = new XLProtection(null) { @@ -84,7 +84,7 @@ } }; } - return ms_defaultStyle; + return _defaultStyle; } } @@ -190,9 +190,6 @@ { DefaultRowHeight = 15; DefaultColumnWidth = 9.140625; - WorksheetsInternal = new XLWorksheets(this); - NamedRanges = new XLNamedRanges(this); - CustomProperties = new XLCustomProperties(this); Style = new XLStyle(null, DefaultStyle); RowHeight = DefaultRowHeight; ColumnWidth = DefaultColumnWidth; @@ -209,6 +206,9 @@ ShowRuler = DefaultShowRuler; ShowWhiteSpace = DefaultShowWhiteSpace; ShowZeros = DefaultShowZeros; + WorksheetsInternal = new XLWorksheets(this); + NamedRanges = new XLNamedRanges(this); + CustomProperties = new XLCustomProperties(this); } /// /// Opens an existing workbook from a file. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index ed1b163..957bfa2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1,4 +1,6 @@ -using System; +#region + +using System; using System.Collections.Generic; using System.Globalization; using System.IO; @@ -9,34 +11,44 @@ using Ap = DocumentFormat.OpenXml.ExtendedProperties; using Op = DocumentFormat.OpenXml.CustomProperties; - +#endregion namespace ClosedXML.Excel { + #region + + using System.Drawing; + using Ap; + using Op; + + #endregion + public partial class XLWorkbook { + private readonly Dictionary _colorList = new Dictionary(); + private void Load(String file) { LoadSheets(file); } + private void Load(Stream stream) { LoadSheets(stream); } + private void LoadSheets(String fileName) { - using (SpreadsheetDocument dSpreadsheet = SpreadsheetDocument.Open(fileName, false)) - { + using (var dSpreadsheet = SpreadsheetDocument.Open(fileName, false)) LoadSpreadsheetDocument(dSpreadsheet); - } } + private void LoadSheets(Stream stream) { - using (SpreadsheetDocument dSpreadsheet = SpreadsheetDocument.Open(stream, false)) - { + using (var dSpreadsheet = SpreadsheetDocument.Open(stream, false)) LoadSpreadsheetDocument(dSpreadsheet); - } } + private void LoadSpreadsheetDocument(SpreadsheetDocument dSpreadsheet) { SetProperties(dSpreadsheet); @@ -44,20 +56,26 @@ SharedStringItem[] sharedStrings = null; if (dSpreadsheet.WorkbookPart.GetPartsOfType().Count() > 0) { - SharedStringTablePart shareStringPart = dSpreadsheet.WorkbookPart.GetPartsOfType().First(); + var shareStringPart = dSpreadsheet.WorkbookPart.GetPartsOfType().First(); sharedStrings = shareStringPart.SharedStringTable.Elements().ToArray(); } if (dSpreadsheet.WorkbookPart.GetPartsOfType().Count() > 0) { - CustomFilePropertiesPart customFilePropertiesPart = dSpreadsheet.WorkbookPart.GetPartsOfType().First(); - foreach (Op.CustomDocumentProperty m in customFilePropertiesPart.Properties.Elements()) + var customFilePropertiesPart = + dSpreadsheet.WorkbookPart.GetPartsOfType().First(); + foreach ( + CustomDocumentProperty m in customFilePropertiesPart.Properties.Elements()) { String name = m.Name.Value; if (m.VTLPWSTR != null) CustomProperties.Add(name, m.VTLPWSTR.Text); else if (m.VTFileTime != null) - CustomProperties.Add(name, DateTime.ParseExact(m.VTFileTime.Text, "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'", CultureInfo.InvariantCulture)); + { + CustomProperties.Add(name, + DateTime.ParseExact(m.VTFileTime.Text, "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'", + CultureInfo.InvariantCulture)); + } else if (m.VTDouble != null) CustomProperties.Add(name, Double.Parse(m.VTDouble.Text, CultureInfo.InvariantCulture)); else if (m.VTBool != null) @@ -67,149 +85,121 @@ var referenceMode = dSpreadsheet.WorkbookPart.Workbook.CalculationProperties.ReferenceMode; if (referenceMode != null) - { ReferenceStyle = referenceMode.Value.ToClosedXml(); - } var calculateMode = dSpreadsheet.WorkbookPart.Workbook.CalculationProperties.CalculationMode; if (calculateMode != null) - { CalculateMode = calculateMode.Value.ToClosedXml(); - } - if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0) - Properties.Company = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text; + if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0) + Properties.Company = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text; - if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0) - Properties.Manager = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text; + if (dSpreadsheet.ExtendedFilePropertiesPart.Properties.Elements().Count() > 0) + Properties.Manager = dSpreadsheet.ExtendedFilePropertiesPart.Properties.GetFirstChild().Text; - var workbookStylesPart = (WorkbookStylesPart)dSpreadsheet.WorkbookPart.WorkbookStylesPart; - var s = (Stylesheet)workbookStylesPart.Stylesheet; - - NumberingFormats numberingFormats = s.NumberingFormats; + var workbookStylesPart = dSpreadsheet.WorkbookPart.WorkbookStylesPart; + var s = workbookStylesPart.Stylesheet; + + var numberingFormats = s.NumberingFormats; //Int32 fillCount = (Int32)s.Fills.Count.Value; var fills = s.Fills; - Borders borders = (Borders)s.Borders; - Fonts fonts = (Fonts)s.Fonts; + var borders = s.Borders; + var fonts = s.Fonts; var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets; - foreach (var sheet in sheets) + foreach (OpenXmlElement sheet in sheets) { var sharedFormulasR1C1 = new Dictionary(); - Sheet dSheet = ((Sheet)sheet); - WorksheetPart wsPart = (WorksheetPart)dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id); + var dSheet = ((Sheet) sheet); + var wsPart = (WorksheetPart) dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id); var sheetName = dSheet.Name; - var ws = (XLWorksheet)WorksheetsInternal.Add(sheetName); + var ws = (XLWorksheet) WorksheetsInternal.Add(sheetName); ws.RelId = dSheet.Id; - ws.SheetId = (Int32)dSheet.SheetId.Value; + ws.SheetId = (Int32) dSheet.SheetId.Value; - + if (dSheet.State != null) ws.Visibility = dSheet.State.Value.ToClosedXml(); - Dictionary styleList = new Dictionary(); - styleList.Add(0, DefaultStyle); + var styleList = new Dictionary(); - OpenXmlReader reader = OpenXmlReader.Create(wsPart); + var reader = OpenXmlReader.Create(wsPart); while (reader.Read()) { - if (reader.ElementType == typeof(SheetFormatProperties)) +#pragma warning disable 252,253 + if (reader.ElementType == typeof (SheetFormatProperties)) { - var sheetFormatProperties = (SheetFormatProperties)reader.LoadCurrentElement(); + var sheetFormatProperties = (SheetFormatProperties) reader.LoadCurrentElement(); if (sheetFormatProperties != null) { if (sheetFormatProperties.DefaultRowHeight != null) ws.RowHeight = sheetFormatProperties.DefaultRowHeight; - ws.RowHeightChanged = (sheetFormatProperties.CustomHeight != null && sheetFormatProperties.CustomHeight.Value); + ws.RowHeightChanged = (sheetFormatProperties.CustomHeight != null && + sheetFormatProperties.CustomHeight.Value); if (sheetFormatProperties.DefaultColumnWidth != null) ws.ColumnWidth = sheetFormatProperties.DefaultColumnWidth; } } - else if (reader.ElementType == typeof(SheetViews)) + else if (reader.ElementType == typeof (SheetViews)) + LoadSheetViews((SheetViews) reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof (MergeCells)) { - LoadSheetViews((SheetViews)reader.LoadCurrentElement(), ws); - } - else if (reader.ElementType == typeof(MergeCells)) - { - var mergedCells = (MergeCells)reader.LoadCurrentElement(); + var mergedCells = (MergeCells) reader.LoadCurrentElement(); if (mergedCells != null) { foreach (MergeCell mergeCell in mergedCells.Elements()) - { ws.Range(mergeCell.Reference).Merge(); - } } } - else if (reader.ElementType == typeof(Columns)) + else if (reader.ElementType == typeof (Columns)) + LoadColumns(s, numberingFormats, fills, borders, fonts, ws, + (Columns) reader.LoadCurrentElement()); + else if (reader.ElementType == typeof (Row)) { - LoadColumns(s, numberingFormats, fills, borders, fonts, ws, (Columns)reader.LoadCurrentElement()); + LoadRows(s, numberingFormats, fills, borders, fonts, ws, sharedStrings, sharedFormulasR1C1, + styleList, (Row) reader.LoadCurrentElement()); } - else if (reader.ElementType == typeof(Row)) - { - LoadRows(s, numberingFormats, fills, borders, fonts, ws, sharedStrings, sharedFormulasR1C1, styleList, (Row)reader.LoadCurrentElement()); - } - else if (reader.ElementType == typeof(AutoFilter)) - { - LoadAutoFilter((AutoFilter)reader.LoadCurrentElement(), ws); - } - else if (reader.ElementType == typeof(SheetProtection)) - { - LoadSheetProtection((SheetProtection)reader.LoadCurrentElement(), ws); - } - else if (reader.ElementType == typeof(DataValidations)) - { - LoadDataValidations((DataValidations)reader.LoadCurrentElement(), ws); - } - else if (reader.ElementType == typeof(Hyperlinks)) - { - LoadHyperlinks((Hyperlinks)reader.LoadCurrentElement(), wsPart, ws); - } - else if (reader.ElementType == typeof(PrintOptions)) - { - LoadPrintOptions((PrintOptions)reader.LoadCurrentElement(), ws); - } - else if (reader.ElementType == typeof(PageMargins)) - { - LoadPageMargins((PageMargins)reader.LoadCurrentElement(), ws); - } - else if (reader.ElementType == typeof(PageSetup)) - { - LoadPageSetup((PageSetup)reader.LoadCurrentElement(), ws); - } - else if (reader.ElementType == typeof(HeaderFooter)) - { - LoadHeaderFooter((HeaderFooter)reader.LoadCurrentElement(), ws); - } - else if (reader.ElementType == typeof(SheetProperties)) - { - LoadSheetProperties((SheetProperties)reader.LoadCurrentElement(), ws); - } - else if (reader.ElementType == typeof(RowBreaks)) - { - LoadRowBreaks((RowBreaks)reader.LoadCurrentElement(), ws); - } - else if (reader.ElementType == typeof(ColumnBreaks)) - { - LoadColumnBreaks((ColumnBreaks)reader.LoadCurrentElement(), ws); - } - } - + else if (reader.ElementType == typeof (AutoFilter)) + LoadAutoFilter((AutoFilter) reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof (SheetProtection)) + LoadSheetProtection((SheetProtection) reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof (DataValidations)) + LoadDataValidations((DataValidations) reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof (Hyperlinks)) + LoadHyperlinks((Hyperlinks) reader.LoadCurrentElement(), wsPart, ws); + else if (reader.ElementType == typeof (PrintOptions)) + LoadPrintOptions((PrintOptions) reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof (PageMargins)) + LoadPageMargins((PageMargins) reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof (PageSetup)) + LoadPageSetup((PageSetup) reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof (HeaderFooter)) + LoadHeaderFooter((HeaderFooter) reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof (SheetProperties)) + LoadSheetProperties((SheetProperties) reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof (RowBreaks)) + LoadRowBreaks((RowBreaks) reader.LoadCurrentElement(), ws); + else if (reader.ElementType == typeof (ColumnBreaks)) + LoadColumnBreaks((ColumnBreaks) reader.LoadCurrentElement(), ws); +#pragma warning restore 252,253 + } #region LoadTables - foreach (var tablePart in wsPart.TableDefinitionParts) + + foreach (TableDefinitionPart tablePart in wsPart.TableDefinitionParts) { - var dTable = (Table)tablePart.Table; - var reference = dTable.Reference.Value; + var dTable = tablePart.Table; + string reference = dTable.Reference.Value; var xlTable = ws.Range(reference).CreateTable(dTable.Name); if (dTable.TotalsRowCount != null && dTable.TotalsRowCount.Value > 0) - ((XLTable)xlTable).m_showTotalsRow = true; + ((XLTable) xlTable).m_showTotalsRow = true; if (dTable.TableStyleInfo != null) { @@ -222,16 +212,18 @@ if (dTable.TableStyleInfo.ShowColumnStripes != null) xlTable.ShowColumnStripes = dTable.TableStyleInfo.ShowColumnStripes.Value; if (dTable.TableStyleInfo.Name != null) - xlTable.Theme = (XLTableTheme)Enum.Parse(typeof(XLTableTheme), dTable.TableStyleInfo.Name.Value); + xlTable.Theme = + (XLTableTheme) Enum.Parse(typeof (XLTableTheme), dTable.TableStyleInfo.Name.Value); } xlTable.ShowAutoFilter = dTable.AutoFilter != null; - foreach (var column in dTable.TableColumns) + foreach (OpenXmlElement column in dTable.TableColumns) { - var tableColumn = (TableColumn)column; + var tableColumn = (TableColumn) column; if (tableColumn.TotalsRowFunction != null) - xlTable.Field(tableColumn.Name.Value).TotalsRowFunction = tableColumn.TotalsRowFunction.Value.ToClosedXml(); + xlTable.Field(tableColumn.Name.Value).TotalsRowFunction = + tableColumn.TotalsRowFunction.Value.ToClosedXml(); if (tableColumn.TotalsRowFormula != null) xlTable.Field(tableColumn.Name.Value).TotalsRowFormulaA1 = tableColumn.TotalsRowFormula.Text; @@ -240,15 +232,16 @@ xlTable.Field(tableColumn.Name.Value).TotalsRowLabel = tableColumn.TotalsRowLabel.Value; } } + #endregion } - var workbook = (Workbook)dSpreadsheet.WorkbookPart.Workbook; + var workbook = dSpreadsheet.WorkbookPart.Workbook; - var workbookView = (WorkbookView)workbook.BookViews.FirstOrDefault(); + var workbookView = (WorkbookView) workbook.BookViews.FirstOrDefault(); if (workbookView != null && workbookView.ActiveTab != null) - Worksheet((Int32)(workbookView.ActiveTab.Value + 1)).SetTabActive(); - + Worksheet((Int32) (workbookView.ActiveTab.Value + 1)).SetTabActive(); + if (workbook.DefinedNames != null) { foreach (DefinedName definedName in workbook.DefinedNames) @@ -256,11 +249,11 @@ var name = definedName.Name; if (name == "_xlnm.Print_Area") { - foreach (var area in definedName.Text.Split(',')) + foreach (string area in definedName.Text.Split(',')) { var sections = area.Trim().Split('!'); - var sheetName = sections[0].Replace("\'", ""); - var sheetArea = sections[1]; + string sheetName = sections[0].Replace("\'", ""); + string sheetArea = sections[1]; if (!sheetArea.Equals("#REF")) WorksheetsInternal.Worksheet(sheetName).PageSetup.PrintAreas.Add(sheetArea); } @@ -270,45 +263,41 @@ var areas = definedName.Text.Split(','); var colSections = areas[0].Trim().Split('!'); - var sheetNameCol = colSections[0].Replace("\'", ""); - var sheetAreaCol = colSections[1]; + string sheetNameCol = colSections[0].Replace("\'", ""); + string sheetAreaCol = colSections[1]; if (!sheetAreaCol.Equals("#REF")) WorksheetsInternal.Worksheet(sheetNameCol).PageSetup.SetColumnsToRepeatAtLeft(sheetAreaCol); var rowSections = areas[1].Split('!'); - var sheetNameRow = rowSections[0].Replace("\'", ""); - var sheetAreaRow = rowSections[1]; + string sheetNameRow = rowSections[0].Replace("\'", ""); + string sheetAreaRow = rowSections[1]; if (!sheetAreaRow.Equals("#REF")) WorksheetsInternal.Worksheet(sheetNameRow).PageSetup.SetRowsToRepeatAtTop(sheetAreaRow); } else { - - var text = definedName.Text; + string text = definedName.Text; if (!text.Equals("#REF")) { var localSheetId = definedName.LocalSheetId; var comment = definedName.Comment; if (localSheetId == null) - { NamedRanges.Add(name, text, comment); - } else - { Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.Add(name, text, comment); - } } } } } } - private void LoadCells(SharedStringItem[] sharedStrings, Stylesheet s, NumberingFormats numberingFormats, Fills fills, Borders borders, Fonts fonts, Dictionary sharedFormulasR1C1, XLWorksheet ws, Dictionary styleList, Cell cell) + private void LoadCells(SharedStringItem[] sharedStrings, Stylesheet s, NumberingFormats numberingFormats, + Fills fills, Borders borders, Fonts fonts, Dictionary sharedFormulasR1C1, + XLWorksheet ws, Dictionary styleList, Cell cell) { - var dCell = (Cell)cell; - Int32 styleIndex = dCell.StyleIndex != null ? Int32.Parse(dCell.StyleIndex.InnerText) : 0; - var xlCell = (XLCell)ws.Cell(dCell.CellReference); + Int32 styleIndex = cell.StyleIndex != null ? Int32.Parse(cell.StyleIndex.InnerText) : 0; + var xlCell = ws.Cell(cell.CellReference); if (styleList.ContainsKey(styleIndex)) xlCell.Style = styleList[styleIndex]; @@ -330,15 +319,13 @@ xlCell.FormulaA1 = formula; sharedFormulasR1C1.Add(cell.CellFormula.SharedIndex.Value, xlCell.FormulaR1C1); - if (dCell.CellValue != null) - xlCell.ValueCached = dCell.CellValue.Text; + if (cell.CellValue != null) + xlCell.ValueCached = cell.CellValue.Text; } - else if (dCell.CellFormula != null) + else if (cell.CellFormula != null) { - if (dCell.CellFormula.SharedIndex != null) - { - xlCell.FormulaR1C1 = sharedFormulasR1C1[dCell.CellFormula.SharedIndex.Value]; - } + if (cell.CellFormula.SharedIndex != null) + xlCell.FormulaR1C1 = sharedFormulasR1C1[cell.CellFormula.SharedIndex.Value]; else { String formula; @@ -350,54 +337,53 @@ xlCell.FormulaA1 = formula; } - if (dCell.CellValue != null) - xlCell.ValueCached = dCell.CellValue.Text; + if (cell.CellValue != null) + xlCell.ValueCached = cell.CellValue.Text; } - else if (dCell.DataType != null) + else if (cell.DataType != null) { - if (dCell.DataType == CellValues.InlineString) + if (cell.DataType == CellValues.InlineString) { - xlCell.m_cellValue = dCell.InlineString.Text.Text; + xlCell.m_cellValue = cell.InlineString.Text.Text; xlCell.m_dataType = XLCellValues.Text; xlCell.ShareString = false; } - else if (dCell.DataType == CellValues.SharedString) + else if (cell.DataType == CellValues.SharedString) { - if (dCell.CellValue != null) + if (cell.CellValue != null) { - if (!StringExtensions.IsNullOrWhiteSpace(dCell.CellValue.Text)) + if (!StringExtensions.IsNullOrWhiteSpace(cell.CellValue.Text)) { - SharedStringItem sharedString = sharedStrings[Int32.Parse(dCell.CellValue.Text)]; - + var sharedString = sharedStrings[Int32.Parse(cell.CellValue.Text)]; + var runs = sharedString.Elements(); var phoneticRuns = sharedString.Elements(); var phoneticProperties = sharedString.Elements(); if (runs.Any()) { #region Load Runs - foreach (var run in runs) + + foreach (Run run in runs) { var runProperties = run.RunProperties; String text = run.Text.InnerText.Replace("\n", Environment.NewLine); if (runProperties == null) - { xlCell.RichText.AddText(text, xlCell.Style.Font); - } else { var rt = xlCell.RichText.AddText(text); LoadFont(runProperties, rt); } } + #endregion } else - { xlCell.m_cellValue = sharedString.Text.InnerText; - } #region Load PhoneticProperties + if (phoneticProperties.Any()) { var pp = phoneticProperties.First(); @@ -408,77 +394,86 @@ LoadFont(pp, xlCell.RichText.Phonetics); } + #endregion + #region Load Phonetic Runs - foreach (var pr in phoneticRuns) + + foreach (PhoneticRun pr in phoneticRuns) { - xlCell.RichText.Phonetics.Add(pr.Text.InnerText, (Int32)pr.BaseTextStartIndex.Value, (Int32)pr.EndingBaseIndex.Value); + xlCell.RichText.Phonetics.Add(pr.Text.InnerText, (Int32) pr.BaseTextStartIndex.Value, + (Int32) pr.EndingBaseIndex.Value); } + #endregion } else - xlCell.m_cellValue = dCell.CellValue.Text; + xlCell.m_cellValue = cell.CellValue.Text; } else - { xlCell.m_cellValue = String.Empty; - } xlCell.m_dataType = XLCellValues.Text; } - else if (dCell.DataType == CellValues.Date) + else if (cell.DataType == CellValues.Date) { //xlCell.cellValue = DateTime.FromOADate(Double.Parse(dCell.CellValue.Text, CultureInfo.InvariantCulture)); - xlCell.m_cellValue = Double.Parse(dCell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); + xlCell.m_cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); xlCell.m_dataType = XLCellValues.DateTime; } - else if (dCell.DataType == CellValues.Boolean) + else if (cell.DataType == CellValues.Boolean) { - xlCell.m_cellValue = dCell.CellValue.Text; + xlCell.m_cellValue = cell.CellValue.Text; xlCell.m_dataType = XLCellValues.Boolean; } - else if (dCell.DataType == CellValues.Number) + else if (cell.DataType == CellValues.Number) { - xlCell.m_cellValue = Double.Parse(dCell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); - var numberFormatId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).NumberFormatId; + xlCell.m_cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); + var numberFormatId = ((CellFormat) (s.CellFormats).ElementAt(styleIndex)).NumberFormatId; if (numberFormatId == 46U) xlCell.DataType = XLCellValues.TimeSpan; else xlCell.m_dataType = XLCellValues.Number; } } - else if (dCell.CellValue != null) + else if (cell.CellValue != null) { - var numberFormatId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).NumberFormatId; - xlCell.m_cellValue = Double.Parse(dCell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); - if (s.NumberingFormats != null && s.NumberingFormats.Any(nf => ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId)) + var numberFormatId = ((CellFormat) (s.CellFormats).ElementAt(styleIndex)).NumberFormatId; + xlCell.m_cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToString(); + if (s.NumberingFormats != null && + s.NumberingFormats.Any(nf => ((NumberingFormat) nf).NumberFormatId.Value == numberFormatId)) + { xlCell.Style.NumberFormat.Format = - ((NumberingFormat)s.NumberingFormats.Where(nf => ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId).Single()).FormatCode.Value; + ((NumberingFormat) + s.NumberingFormats.Where(nf => ((NumberingFormat) nf).NumberFormatId.Value == numberFormatId). + Single()).FormatCode.Value; + } else xlCell.Style.NumberFormat.NumberFormatId = Int32.Parse(numberFormatId); if (!StringExtensions.IsNullOrWhiteSpace(xlCell.Style.NumberFormat.Format)) xlCell.m_dataType = GetDataTypeFromFormat(xlCell.Style.NumberFormat.Format); + else if ((numberFormatId >= 14 && numberFormatId <= 22) || (numberFormatId >= 45 && numberFormatId <= 47)) + xlCell.m_dataType = XLCellValues.DateTime; + else if (numberFormatId == 49) + xlCell.m_dataType = XLCellValues.Text; else - if ((numberFormatId >= 14 && numberFormatId <= 22) || (numberFormatId >= 45 && numberFormatId <= 47)) - xlCell.m_dataType = XLCellValues.DateTime; - else if (numberFormatId == 49) - xlCell.m_dataType = XLCellValues.Text; - else - xlCell.m_dataType = XLCellValues.Number; + xlCell.m_dataType = XLCellValues.Number; } } private void LoadFont(OpenXmlElement fontSource, IXLFontBase fontBase) { fontBase.Bold = GetBoolean(fontSource.Elements().FirstOrDefault()); - var fontColor = GetColor(fontSource.Elements().FirstOrDefault()); + var fontColor = GetColor(fontSource.Elements().FirstOrDefault()); if (fontColor.HasValue) fontBase.FontColor = fontColor; - var fontFamilyNumbering = fontSource.Elements().FirstOrDefault(); + var fontFamilyNumbering = + fontSource.Elements().FirstOrDefault(); if (fontFamilyNumbering != null && fontFamilyNumbering.Val != null) - fontBase.FontFamilyNumbering = (XLFontFamilyNumberingValues)Int32.Parse(fontFamilyNumbering.Val.ToString()); + fontBase.FontFamilyNumbering = + (XLFontFamilyNumberingValues) Int32.Parse(fontFamilyNumbering.Val.ToString()); var runFont = fontSource.Elements().FirstOrDefault(); if (runFont != null) { @@ -488,8 +483,8 @@ var fontSize = fontSource.Elements().FirstOrDefault(); if (fontSize != null) { - if (((FontSize)fontSize).Val != null) - fontBase.FontSize = ((FontSize)fontSize).Val; + if ((fontSize).Val != null) + fontBase.FontSize = (fontSize).Val; } fontBase.Italic = GetBoolean(fontSource.Elements().FirstOrDefault()); @@ -498,23 +493,29 @@ var underline = fontSource.Elements().FirstOrDefault(); if (underline != null) + { if (underline.Val != null) fontBase.Underline = underline.Val.Value.ToClosedXml(); else fontBase.Underline = XLFontUnderlineValues.Single; + } var verticalTextAlignment = fontSource.Elements().FirstOrDefault(); if (verticalTextAlignment != null) - + { if (verticalTextAlignment.Val != null) fontBase.VerticalAlignment = verticalTextAlignment.Val.Value.ToClosedXml(); else fontBase.VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline; + } } - private void LoadRows(Stylesheet s, NumberingFormats numberingFormats, Fills fills, Borders borders, Fonts fonts, XLWorksheet ws, SharedStringItem[] sharedStrings, Dictionary sharedFormulasR1C1, Dictionary styleList, Row row) + private void LoadRows(Stylesheet s, NumberingFormats numberingFormats, Fills fills, Borders borders, Fonts fonts, + XLWorksheet ws, SharedStringItem[] sharedStrings, + Dictionary sharedFormulasR1C1, Dictionary styleList, + Row row) { - var xlRow = (XLRow)ws.Row((Int32)row.RowIndex.Value, false); + var xlRow = (XLRow) ws.Row((Int32) row.RowIndex.Value, false); if (row.Height != null) xlRow.Height = row.Height; else @@ -533,9 +534,7 @@ { Int32 styleIndex = row.StyleIndex != null ? Int32.Parse(row.StyleIndex.InnerText) : -1; if (styleIndex > 0) - { ApplyStyle(xlRow, styleIndex, s, fills, borders, fonts, numberingFormats); - } else { //((XLRow)xlRow).style = ws.Style; @@ -545,42 +544,43 @@ } } - //OpenXmlReader reader = OpenXmlReader.Create(row); - //while (reader.Read()) - //{ - // if (reader.ElementType == typeof(Cell)) - // { - // LoadCells(sharedStrings, s, numberingFormats, fills, borders, fonts, sharedFormulasR1C1, ws, styleList, (Cell)reader.LoadCurrentElement()); - // } - //} + //OpenXmlReader reader = OpenXmlReader.Create(row); + //while (reader.Read()) + //{ + // if (reader.ElementType == typeof(Cell)) + // { + // LoadCells(sharedStrings, s, numberingFormats, fills, borders, fonts, sharedFormulasR1C1, ws, styleList, (Cell)reader.LoadCurrentElement()); + // } + //} foreach (Cell cell in row.Elements()) - { - LoadCells(sharedStrings, s, numberingFormats, fills, borders, fonts, sharedFormulasR1C1, ws, styleList, cell); - } + LoadCells(sharedStrings, s, numberingFormats, fills, borders, fonts, sharedFormulasR1C1, ws, styleList, + cell); } - private void LoadColumns(Stylesheet s, NumberingFormats numberingFormats, Fills fills, Borders borders, Fonts fonts, XLWorksheet ws, Columns columns) + private void LoadColumns(Stylesheet s, NumberingFormats numberingFormats, Fills fills, Borders borders, + Fonts fonts, XLWorksheet ws, Columns columns) { if (columns != null) { + var wsDefaultColumn = + columns.Elements().Where(c => c.Max == XLWorksheet.MaxNumberOfColumns).FirstOrDefault(); - var wsDefaultColumn = columns.Elements().Where(c => c.Max == XLWorksheet.MaxNumberOfColumns).FirstOrDefault(); + if (wsDefaultColumn != null && wsDefaultColumn.Width != null) + ws.ColumnWidth = wsDefaultColumn.Width - COLUMN_WIDTH_OFFSET; - if (wsDefaultColumn != null && wsDefaultColumn.Width != null) ws.ColumnWidth = wsDefaultColumn.Width - COLUMN_WIDTH_OFFSET; - - Int32 styleIndexDefault = wsDefaultColumn != null && wsDefaultColumn.Style != null ? Int32.Parse(wsDefaultColumn.Style.InnerText) : -1; + Int32 styleIndexDefault = wsDefaultColumn != null && wsDefaultColumn.Style != null + ? Int32.Parse(wsDefaultColumn.Style.InnerText) + : -1; if (styleIndexDefault >= 0) - { ApplyStyle(ws, styleIndexDefault, s, fills, borders, fonts, numberingFormats); - } - foreach (var col in columns.Elements()) + foreach (Column col in columns.Elements()) { //IXLStylized toApply; if (col.Max != XLWorksheet.MaxNumberOfColumns) { - var xlColumns = (XLColumns)ws.Columns(col.Min, col.Max); + var xlColumns = (XLColumns) ws.Columns(col.Min, col.Max); if (col.Width != null) xlColumns.Width = col.Width - COLUMN_WIDTH_OFFSET; else @@ -597,13 +597,9 @@ Int32 styleIndex = col.Style != null ? Int32.Parse(col.Style.InnerText) : -1; if (styleIndex > 0) - { ApplyStyle(xlColumns, styleIndex, s, fills, borders, fonts, numberingFormats); - } else - { xlColumns.Style = DefaultStyle; - } } } } @@ -611,7 +607,7 @@ private XLCellValues GetDataTypeFromFormat(String format) { - var length = format.Length; + int length = format.Length; String f = format.ToLower(); for (Int32 i = 0; i < length; i++) { @@ -658,7 +654,7 @@ { if (dataValidations != null) { - foreach (var dvs in dataValidations.Elements()) + foreach (DataValidation dvs in dataValidations.Elements()) { foreach (String rangeAddress in dvs.SequenceOfReferences.InnerText.Split(' ')) { @@ -689,11 +685,11 @@ if (hyperlinks != null) { - foreach (var hl in hyperlinks.Elements()) + foreach (Hyperlink hl in hyperlinks.Elements()) { if (!hl.Reference.Value.Equals("#REF")) { - String tooltip = hl.Tooltip != null ? tooltip = hl.Tooltip.Value : tooltip = String.Empty; + String tooltip = hl.Tooltip != null ? hl.Tooltip.Value : String.Empty; var xlRange = ws.Range(hl.Reference.Value); foreach (XLCell xlCell in xlRange.Cells()) { @@ -713,7 +709,7 @@ { if (columnBreaks != null) { - foreach (var columnBreak in columnBreaks.Elements()) + foreach (Break columnBreak in columnBreaks.Elements()) { if (columnBreak.Id != null) ws.PageSetup.ColumnBreaks.Add(Int32.Parse(columnBreak.Id.InnerText)); @@ -725,10 +721,8 @@ { if (rowBreaks != null) { - foreach (var rowBreak in rowBreaks.Elements()) - { + foreach (Break rowBreak in rowBreaks.Elements()) ws.PageSetup.RowBreaks.Add(Int32.Parse(rowBreak.Id.InnerText)); - } } } @@ -743,14 +737,16 @@ { if (sheetProperty.OutlineProperties.SummaryBelow != null) { - ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow ? - XLOutlineSummaryVLocation.Bottom : XLOutlineSummaryVLocation.Top; + ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow + ? XLOutlineSummaryVLocation.Bottom + : XLOutlineSummaryVLocation.Top; } if (sheetProperty.OutlineProperties.SummaryRight != null) { - ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight ? - XLOutlineSummaryHLocation.Right : XLOutlineSummaryHLocation.Left; + ws.Outline.SummaryHLocation = sheetProperty.OutlineProperties.SummaryRight + ? XLOutlineSummaryHLocation.Right + : XLOutlineSummaryHLocation.Left; } } } @@ -766,25 +762,25 @@ ws.PageSetup.ScaleHFWithDocument = headerFooter.ScaleWithDoc; // Footers - var xlFooter = (XLHeaderFooter)ws.PageSetup.Footer; - var evenFooter = (EvenFooter)headerFooter.EvenFooter; + var xlFooter = (XLHeaderFooter) ws.PageSetup.Footer; + var evenFooter = headerFooter.EvenFooter; if (evenFooter != null) xlFooter.SetInnerText(XLHFOccurrence.EvenPages, evenFooter.Text); - var oddFooter = (OddFooter)headerFooter.OddFooter; + var oddFooter = headerFooter.OddFooter; if (oddFooter != null) xlFooter.SetInnerText(XLHFOccurrence.OddPages, oddFooter.Text); - var firstFooter = (FirstFooter)headerFooter.FirstFooter; + var firstFooter = headerFooter.FirstFooter; if (firstFooter != null) xlFooter.SetInnerText(XLHFOccurrence.FirstPage, firstFooter.Text); // Headers - var xlHeader = (XLHeaderFooter)ws.PageSetup.Header; - var evenHeader = (EvenHeader)headerFooter.EvenHeader; + var xlHeader = (XLHeaderFooter) ws.PageSetup.Header; + var evenHeader = headerFooter.EvenHeader; if (evenHeader != null) xlHeader.SetInnerText(XLHFOccurrence.EvenPages, evenHeader.Text); - var oddHeader = (OddHeader)headerFooter.OddHeader; + var oddHeader = headerFooter.OddHeader; if (oddHeader != null) xlHeader.SetInnerText(XLHFOccurrence.OddPages, oddHeader.Text); - var firstHeader = (FirstHeader)headerFooter.FirstHeader; + var firstHeader = headerFooter.FirstHeader; if (firstHeader != null) xlHeader.SetInnerText(XLHFOccurrence.FirstPage, firstHeader.Text); } @@ -795,11 +791,9 @@ if (pageSetup != null) { if (pageSetup.PaperSize != null) - ws.PageSetup.PaperSize = (XLPaperSize)Int32.Parse(pageSetup.PaperSize.InnerText); + ws.PageSetup.PaperSize = (XLPaperSize) Int32.Parse(pageSetup.PaperSize.InnerText); if (pageSetup.Scale != null) - { ws.PageSetup.Scale = Int32.Parse(pageSetup.Scale.InnerText); - } else { if (pageSetup.FitToWidth != null) @@ -819,9 +813,10 @@ ws.PageSetup.ShowComments = pageSetup.CellComments.Value.ToClosedXml(); if (pageSetup.Errors != null) ws.PageSetup.PrintErrorValue = pageSetup.Errors.Value.ToClosedXml(); - if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = (Int32)pageSetup.HorizontalDpi.Value; - if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = (Int32)pageSetup.VerticalDpi.Value; - if (pageSetup.FirstPageNumber != null) ws.PageSetup.FirstPageNumber = Int32.Parse(pageSetup.FirstPageNumber.InnerText); + if (pageSetup.HorizontalDpi != null) ws.PageSetup.HorizontalDpi = (Int32) pageSetup.HorizontalDpi.Value; + if (pageSetup.VerticalDpi != null) ws.PageSetup.VerticalDpi = (Int32) pageSetup.VerticalDpi.Value; + if (pageSetup.FirstPageNumber != null) + ws.PageSetup.FirstPageNumber = Int32.Parse(pageSetup.FirstPageNumber.InnerText); } } @@ -863,27 +858,29 @@ { if (sheetViews != null) { - SheetView sheetView = sheetViews.Elements().FirstOrDefault(); + var sheetView = sheetViews.Elements().FirstOrDefault(); if (sheetView != null) { if (sheetView.ShowFormulas != null) ws.ShowFormulas = sheetView.ShowFormulas.Value; if (sheetView.ShowGridLines != null) ws.ShowGridLines = sheetView.ShowGridLines.Value; - if (sheetView.ShowOutlineSymbols != null) ws.ShowOutlineSymbols = sheetView.ShowOutlineSymbols.Value; + if (sheetView.ShowOutlineSymbols != null) + ws.ShowOutlineSymbols = sheetView.ShowOutlineSymbols.Value; if (sheetView.ShowRowColHeaders != null) ws.ShowRowColHeaders = sheetView.ShowRowColHeaders.Value; if (sheetView.ShowRuler != null) ws.ShowRuler = sheetView.ShowRuler.Value; if (sheetView.ShowWhiteSpace != null) ws.ShowWhiteSpace = sheetView.ShowWhiteSpace.Value; if (sheetView.ShowZeros != null) ws.ShowZeros = sheetView.ShowZeros.Value; if (sheetView.TabSelected != null) ws.TabSelected = sheetView.TabSelected.Value; - var pane = (Pane)sheetView.Elements().FirstOrDefault(); + var pane = sheetView.Elements().FirstOrDefault(); if (pane != null) { - if (pane.State != null && (pane.State == PaneStateValues.FrozenSplit || pane.State == PaneStateValues.Frozen)) + if (pane.State != null && + (pane.State == PaneStateValues.FrozenSplit || pane.State == PaneStateValues.Frozen)) { if (pane.HorizontalSplit != null) - ws.SheetView.SplitColumn = (Int32)pane.HorizontalSplit.Value; + ws.SheetView.SplitColumn = (Int32) pane.HorizontalSplit.Value; if (pane.VerticalSplit != null) - ws.SheetView.SplitRow = (Int32)pane.VerticalSplit.Value; + ws.SheetView.SplitRow = (Int32) pane.VerticalSplit.Value; } } } @@ -905,7 +902,6 @@ Properties.Title = p.Title; } - private Dictionary colorList = new Dictionary(); private IXLColor GetColor(ColorType color) { IXLColor retVal = null; @@ -914,28 +910,24 @@ if (color.Rgb != null) { String htmlColor = "#" + color.Rgb.Value; - System.Drawing.Color thisColor; - if (!colorList.ContainsKey(htmlColor)) + Color thisColor; + if (!_colorList.ContainsKey(htmlColor)) { - thisColor = System.Drawing.ColorTranslator.FromHtml(htmlColor); - colorList.Add(htmlColor, thisColor); + thisColor = ColorTranslator.FromHtml(htmlColor); + _colorList.Add(htmlColor, thisColor); } else - { - thisColor = colorList[htmlColor]; - } + thisColor = _colorList[htmlColor]; retVal = new XLColor(thisColor); } else if (color.Indexed != null && color.Indexed < 64) - { - retVal = new XLColor((Int32)color.Indexed.Value); - } + retVal = new XLColor((Int32) color.Indexed.Value); else if (color.Theme != null) { if (color.Tint != null) - retVal = XLColor.FromTheme((XLThemeColor)color.Theme.Value, color.Tint.Value); + retVal = XLColor.FromTheme((XLThemeColor) color.Theme.Value, color.Tint.Value); else - retVal = XLColor.FromTheme((XLThemeColor)color.Theme.Value); + retVal = XLColor.FromTheme((XLThemeColor) color.Theme.Value); } } if (retVal == null) @@ -944,27 +936,30 @@ return retVal; } - private void ApplyStyle(IXLStylized xlStylized, Int32 styleIndex, Stylesheet s, Fills fills, Borders borders, Fonts fonts, NumberingFormats numberingFormats) + private void ApplyStyle(IXLStylized xlStylized, Int32 styleIndex, Stylesheet s, Fills fills, Borders borders, + Fonts fonts, NumberingFormats numberingFormats) { - var cellFormat = (CellFormat)s.CellFormats.ElementAt(styleIndex); - + var cellFormat = (CellFormat) s.CellFormats.ElementAt(styleIndex); + if (cellFormat.ApplyProtection != null) { - Protection protection = cellFormat.Protection; + var protection = cellFormat.Protection; if (protection == null) xlStylized.InnerStyle.Protection = new XLProtection(null, DefaultStyle.Protection); else { - xlStylized.InnerStyle.Protection.Hidden = protection.Hidden != null && protection.Hidden.HasValue && protection.Hidden.Value; - xlStylized.InnerStyle.Protection.Locked = protection.Locked == null || (protection.Locked != null && protection.Locked.HasValue && protection.Locked.Value); + xlStylized.InnerStyle.Protection.Hidden = protection.Hidden != null && protection.Hidden.HasValue && + protection.Hidden.Value; + xlStylized.InnerStyle.Protection.Locked = protection.Locked == null || + (protection.Locked.HasValue && protection.Locked.Value); } } - var fillId = cellFormat.FillId.Value; + uint fillId = cellFormat.FillId.Value; if (fillId > 0) { - var fill = (Fill)fills.ElementAt((Int32)fillId); + var fill = (Fill) fills.ElementAt((Int32) fillId); if (fill.PatternFill != null) { if (fill.PatternFill.PatternType != null) @@ -974,7 +969,7 @@ if (fgColor.HasValue) xlStylized.InnerStyle.Fill.PatternColor = fgColor; var bgColor = GetColor(fill.PatternFill.BackgroundColor); - if (bgColor.HasValue) + if (bgColor.HasValue) xlStylized.InnerStyle.Fill.PatternBackgroundColor = bgColor; } } @@ -994,16 +989,19 @@ if (alignment.JustifyLastLine != null) xlStylized.InnerStyle.Alignment.JustifyLastLine = alignment.JustifyLastLine; if (alignment.ReadingOrder != null) - xlStylized.InnerStyle.Alignment.ReadingOrder = (XLAlignmentReadingOrderValues)Int32.Parse(alignment.ReadingOrder.ToString()); + { + xlStylized.InnerStyle.Alignment.ReadingOrder = + (XLAlignmentReadingOrderValues) Int32.Parse(alignment.ReadingOrder.ToString()); + } if (alignment.RelativeIndent != null) xlStylized.InnerStyle.Alignment.RelativeIndent = alignment.RelativeIndent; if (alignment.ShrinkToFit != null) xlStylized.InnerStyle.Alignment.ShrinkToFit = alignment.ShrinkToFit; if (alignment.TextRotation != null) - xlStylized.InnerStyle.Alignment.TextRotation = (Int32)alignment.TextRotation.Value; + xlStylized.InnerStyle.Alignment.TextRotation = (Int32) alignment.TextRotation.Value; if (alignment.Vertical != null) xlStylized.InnerStyle.Alignment.Vertical = alignment.Vertical.Value.ToClosedXml(); - if (alignment.WrapText !=null) + if (alignment.WrapText != null) xlStylized.InnerStyle.Alignment.WrapText = alignment.WrapText; } @@ -1011,11 +1009,11 @@ //if (borders.ContainsKey(styleIndex)) //{ // var border = borders[styleIndex]; - var borderId = cellFormat.BorderId.Value; - var border = (Border)borders.ElementAt((Int32)borderId); + uint borderId = cellFormat.BorderId.Value; + var border = (Border) borders.ElementAt((Int32) borderId); if (border != null) { - var bottomBorder = (BottomBorder)border.BottomBorder; + var bottomBorder = border.BottomBorder; if (bottomBorder != null) { if (bottomBorder.Style != null) @@ -1025,7 +1023,7 @@ if (bottomBorderColor.HasValue) xlStylized.InnerStyle.Border.BottomBorderColor = bottomBorderColor; } - var topBorder = (TopBorder)border.TopBorder; + var topBorder = border.TopBorder; if (topBorder != null) { if (topBorder.Style != null) @@ -1034,7 +1032,7 @@ if (topBorderColor.HasValue) xlStylized.InnerStyle.Border.TopBorderColor = topBorderColor; } - var leftBorder = (LeftBorder)border.LeftBorder; + var leftBorder = border.LeftBorder; if (leftBorder != null) { if (leftBorder.Style != null) @@ -1043,7 +1041,7 @@ if (leftBorderColor.HasValue) xlStylized.InnerStyle.Border.LeftBorderColor = leftBorderColor; } - var rightBorder = (RightBorder)border.RightBorder; + var rightBorder = border.RightBorder; if (rightBorder != null) { if (rightBorder.Style != null) @@ -1052,7 +1050,7 @@ if (rightBorderColor.HasValue) xlStylized.InnerStyle.Border.RightBorderColor = rightBorderColor; } - var diagonalBorder = (DiagonalBorder)border.DiagonalBorder; + var diagonalBorder = border.DiagonalBorder; if (diagonalBorder != null) { if (diagonalBorder.Style != null) @@ -1071,7 +1069,7 @@ //{ // var font = fonts[styleIndex]; var fontId = cellFormat.FontId; - var font = (Font)fonts.ElementAt((Int32)fontId.Value); + var font = (DocumentFormat.OpenXml.Spreadsheet.Font) fonts.ElementAt((Int32) fontId.Value); if (font != null) { xlStylized.InnerStyle.Font.Bold = GetBoolean(font.Bold); @@ -1080,58 +1078,68 @@ if (fontColor.HasValue) xlStylized.InnerStyle.Font.FontColor = fontColor; - if (font.FontFamilyNumbering != null && ((FontFamilyNumbering)font.FontFamilyNumbering).Val != null) - xlStylized.InnerStyle.Font.FontFamilyNumbering = (XLFontFamilyNumberingValues)Int32.Parse(((FontFamilyNumbering)font.FontFamilyNumbering).Val.ToString()); + if (font.FontFamilyNumbering != null && (font.FontFamilyNumbering).Val != null) + { + xlStylized.InnerStyle.Font.FontFamilyNumbering = + (XLFontFamilyNumberingValues) Int32.Parse((font.FontFamilyNumbering).Val.ToString()); + } if (font.FontName != null) { - if (((FontName)font.FontName).Val != null) - xlStylized.InnerStyle.Font.FontName = ((FontName)font.FontName).Val; + if ((font.FontName).Val != null) + xlStylized.InnerStyle.Font.FontName = (font.FontName).Val; } if (font.FontSize != null) { - if (((FontSize)font.FontSize).Val != null) - xlStylized.InnerStyle.Font.FontSize = ((FontSize)font.FontSize).Val; + if ((font.FontSize).Val != null) + xlStylized.InnerStyle.Font.FontSize = (font.FontSize).Val; } xlStylized.InnerStyle.Font.Italic = GetBoolean(font.Italic); xlStylized.InnerStyle.Font.Shadow = GetBoolean(font.Shadow); xlStylized.InnerStyle.Font.Strikethrough = GetBoolean(font.Strike); - + if (font.Underline != null) + { if (font.Underline.Val != null) - xlStylized.InnerStyle.Font.Underline = ((Underline)font.Underline).Val.Value.ToClosedXml(); + xlStylized.InnerStyle.Font.Underline = (font.Underline).Val.Value.ToClosedXml(); else xlStylized.InnerStyle.Font.Underline = XLFontUnderlineValues.Single; + } if (font.VerticalTextAlignment != null) - - if (font.VerticalTextAlignment.Val != null) - xlStylized.InnerStyle.Font.VerticalAlignment = ((VerticalTextAlignment)font.VerticalTextAlignment).Val.Value.ToClosedXml(); - else - xlStylized.InnerStyle.Font.VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline; + { + if (font.VerticalTextAlignment.Val != null) + xlStylized.InnerStyle.Font.VerticalAlignment = + (font.VerticalTextAlignment).Val.Value.ToClosedXml(); + else + xlStylized.InnerStyle.Font.VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline; + } } - var numberFormatId = cellFormat.NumberFormatId; - if (numberFormatId != null) + var numberFormatId = cellFormat.NumberFormatId; + if (numberFormatId != null) + { + string formatCode = String.Empty; + if (numberingFormats != null) { - var formatCode = String.Empty; - if (numberingFormats != null) - { - var numberFormatList = numberingFormats.Where(nf => ((NumberingFormat)nf).NumberFormatId != null && ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId); + var numberFormatList = + numberingFormats.Where( + nf => + ((NumberingFormat) nf).NumberFormatId != null && + ((NumberingFormat) nf).NumberFormatId.Value == numberFormatId); - if (numberFormatList.Count() > 0) - { - NumberingFormat numberingFormat = (NumberingFormat)numberFormatList.First(); - if (numberingFormat.FormatCode != null) - formatCode = numberingFormat.FormatCode.Value; - } + if (numberFormatList.Count() > 0) + { + var numberingFormat = (NumberingFormat) numberFormatList.First(); + if (numberingFormat.FormatCode != null) + formatCode = numberingFormat.FormatCode.Value; } - if (formatCode.Length > 0) - xlStylized.InnerStyle.NumberFormat.Format = formatCode; - else - xlStylized.InnerStyle.NumberFormat.NumberFormatId = (Int32)numberFormatId.Value; } - + if (formatCode.Length > 0) + xlStylized.InnerStyle.NumberFormat.Format = formatCode; + else + xlStylized.InnerStyle.NumberFormat.NumberFormatId = (Int32) numberFormatId.Value; + } } private Boolean GetBoolean(BooleanPropertyType property) @@ -1144,13 +1152,7 @@ return true; } else - { return false; - } } - - - - } } \ No newline at end of file