diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index 9e86a67..7a549f1 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -9,8 +9,8 @@ namespace ClosedXML.Excel { - public enum XLEventTracking { Enabled, Disabled } + public enum XLCalculateMode { Auto, @@ -40,7 +40,7 @@ Simple = 1, } - public partial class XLWorkbook: IDisposable + public partial class XLWorkbook : IDisposable { #region Static @@ -51,71 +51,59 @@ get { return _defaultStyle ?? (_defaultStyle = new XLStyle(null) - { - Font = new XLFont(null, null) - { - Bold = false, - Italic = false, - Underline = XLFontUnderlineValues.None, - Strikethrough = false, - VerticalAlignment = - XLFontVerticalTextAlignmentValues. - Baseline, - FontSize = 11, - FontColor = XLColor.FromArgb(0, 0, 0), - FontName = "Calibri", - FontFamilyNumbering = - XLFontFamilyNumberingValues.Swiss - }, - Fill = new XLFill(null) - { - BackgroundColor = XLColor.FromIndex(64), - PatternType = XLFillPatternValues.None, - PatternColor = XLColor.FromIndex(64) - }, - Border = new XLBorder(null, null) - { - BottomBorder = - XLBorderStyleValues.None, - DiagonalBorder = - XLBorderStyleValues.None, - DiagonalDown = false, - DiagonalUp = false, - LeftBorder = XLBorderStyleValues.None, - RightBorder = XLBorderStyleValues.None, - TopBorder = XLBorderStyleValues.None, - BottomBorderColor = XLColor.Black, - DiagonalBorderColor = XLColor.Black, - LeftBorderColor = XLColor.Black, - RightBorderColor = XLColor.Black, - TopBorderColor = XLColor.Black - }, - NumberFormat = - new XLNumberFormat(null, null) {NumberFormatId = 0}, - Alignment = new XLAlignment(null) - { - 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) - { - Locked = true, - Hidden = false - } - }); + { + Font = new XLFont(null, null) + { + Bold = false, + Italic = false, + Underline = XLFontUnderlineValues.None, + Strikethrough = false, + VerticalAlignment = XLFontVerticalTextAlignmentValues.Baseline, + FontSize = 11, + FontColor = XLColor.FromArgb(0, 0, 0), + FontName = "Calibri", + FontFamilyNumbering = XLFontFamilyNumberingValues.Swiss + }, + Fill = new XLFill(null) + { + BackgroundColor = XLColor.FromIndex(64), + PatternType = XLFillPatternValues.None, + PatternColor = XLColor.FromIndex(64) + }, + Border = new XLBorder(null, null) + { + BottomBorder = XLBorderStyleValues.None, + DiagonalBorder = XLBorderStyleValues.None, + DiagonalDown = false, + DiagonalUp = false, + LeftBorder = XLBorderStyleValues.None, + RightBorder = XLBorderStyleValues.None, + TopBorder = XLBorderStyleValues.None, + BottomBorderColor = XLColor.Black, + DiagonalBorderColor = XLColor.Black, + LeftBorderColor = XLColor.Black, + RightBorderColor = XLColor.Black, + TopBorderColor = XLColor.Black + }, + NumberFormat = new XLNumberFormat(null, null) { NumberFormatId = 0 }, + Alignment = new XLAlignment(null) + { + 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) + { + Locked = true, + Hidden = false + } + }); } } @@ -127,24 +115,24 @@ get { var defaultPageOptions = new XLPageSetup(null, null) - { - PageOrientation = XLPageOrientation.Default, - Scale = 100, - PaperSize = XLPaperSize.LetterPaper, - Margins = new XLMargins - { - Top = 0.75, - Bottom = 0.5, - Left = 0.75, - Right = 0.75, - Header = 0.5, - Footer = 0.75 - }, - ScaleHFWithDocument = true, - AlignHFWithMargins = true, - PrintErrorValue = XLPrintErrorValues.Displayed, - ShowComments = XLShowCommentsValues.None - }; + { + PageOrientation = XLPageOrientation.Default, + Scale = 100, + PaperSize = XLPaperSize.LetterPaper, + Margins = new XLMargins + { + Top = 0.75, + Bottom = 0.5, + Left = 0.75, + Right = 0.75, + Header = 0.5, + Footer = 0.75 + }, + ScaleHFWithDocument = true, + AlignHFWithMargins = true, + PrintErrorValue = XLPrintErrorValues.Displayed, + ShowComments = XLShowCommentsValues.None + }; return defaultPageOptions; } } @@ -154,10 +142,10 @@ get { return new XLOutline(null) - { - SummaryHLocation = XLOutlineSummaryHLocation.Right, - SummaryVLocation = XLOutlineSummaryVLocation.Bottom - }; + { + SummaryHLocation = XLOutlineSummaryHLocation.Right, + SummaryVLocation = XLOutlineSummaryVLocation.Bottom + }; } } @@ -166,7 +154,7 @@ /// public static XLCellSetValueBehavior CellSetValueBehavior { get; set; } - #endregion + #endregion Static internal readonly List UnsupportedSheets = new List(); @@ -194,7 +182,7 @@ return _stylesById[id]; } - #region Nested Type: XLLoadSource + #region Nested Type: XLLoadSource private enum XLLoadSource { @@ -203,7 +191,7 @@ Stream }; - #endregion + #endregion Nested Type: XLLoadSource internal XLWorksheets WorksheetsInternal { get; private set; } @@ -265,7 +253,6 @@ /// public XLCalculateMode CalculateMode { get; set; } - public Boolean CalculationOnSave { get; set; } public Boolean ForceFullCalculation { get; set; } public Boolean FullCalculationOnLoad { get; set; } @@ -330,20 +317,20 @@ private void InitializeTheme() { Theme = new XLTheme - { - Text1 = XLColor.FromHtml("#FF000000"), - Background1 = XLColor.FromHtml("#FFFFFFFF"), - Text2 = XLColor.FromHtml("#FF1F497D"), - Background2 = XLColor.FromHtml("#FFEEECE1"), - Accent1 = XLColor.FromHtml("#FF4F81BD"), - Accent2 = XLColor.FromHtml("#FFC0504D"), - Accent3 = XLColor.FromHtml("#FF9BBB59"), - Accent4 = XLColor.FromHtml("#FF8064A2"), - Accent5 = XLColor.FromHtml("#FF4BACC6"), - Accent6 = XLColor.FromHtml("#FFF79646"), - Hyperlink = XLColor.FromHtml("#FF0000FF"), - FollowedHyperlink = XLColor.FromHtml("#FF800080") - }; + { + Text1 = XLColor.FromHtml("#FF000000"), + Background1 = XLColor.FromHtml("#FFFFFFFF"), + Text2 = XLColor.FromHtml("#FF1F497D"), + Background2 = XLColor.FromHtml("#FFEEECE1"), + Accent1 = XLColor.FromHtml("#FF4F81BD"), + Accent2 = XLColor.FromHtml("#FFC0504D"), + Accent3 = XLColor.FromHtml("#FF9BBB59"), + Accent4 = XLColor.FromHtml("#FF8064A2"), + Accent5 = XLColor.FromHtml("#FF4BACC6"), + Accent6 = XLColor.FromHtml("#FFF79646"), + Hyperlink = XLColor.FromHtml("#FF0000FF"), + FollowedHyperlink = XLColor.FromHtml("#FF800080") + }; } internal XLColor GetXLColor(XLThemeColor themeColor) @@ -352,24 +339,34 @@ { case XLThemeColor.Text1: return Theme.Text1; + case XLThemeColor.Background1: return Theme.Background1; + case XLThemeColor.Text2: return Theme.Text2; + case XLThemeColor.Background2: return Theme.Background2; + case XLThemeColor.Accent1: return Theme.Accent1; + case XLThemeColor.Accent2: return Theme.Accent2; + case XLThemeColor.Accent3: return Theme.Accent3; + case XLThemeColor.Accent4: return Theme.Accent4; + case XLThemeColor.Accent5: return Theme.Accent5; + case XLThemeColor.Accent6: return Theme.Accent6; + default: throw new ArgumentException("Invalid theme color"); } @@ -422,7 +419,6 @@ return null; } - /// /// Saves the current workbook. /// @@ -510,12 +506,13 @@ case "xlsm": case "xltm": return SpreadsheetDocumentType.MacroEnabledWorkbook; + case "xlsx": case "xltx": return SpreadsheetDocumentType.Workbook; + default: throw new ArgumentException(String.Format("Extension '{0}' is not supported. Supported extensions are '.xlsx', '.xslm', '.xltx' and '.xltm'.", extension)); - } } @@ -596,7 +593,6 @@ output.Write(buffer, 0, len); // dm 20130422, and flushing the output after write output.Flush(); - } public IXLWorksheet Worksheet(String name) @@ -650,24 +646,22 @@ return columns; } -#region Fields + #region Fields private readonly XLLoadSource _loadSource = XLLoadSource.New; private readonly String _originalFile; private readonly Stream _originalStream; -#endregion + #endregion Fields -#region Constructor - + #region Constructor /// /// Creates a new Excel workbook. /// public XLWorkbook() - :this(XLEventTracking.Enabled) + : this(XLEventTracking.Enabled) { - } public XLWorkbook(XLEventTracking eventTracking) @@ -706,7 +700,6 @@ public XLWorkbook(String file) : this(file, XLEventTracking.Enabled) { - } public XLWorkbook(String file, XLEventTracking eventTracking) @@ -718,15 +711,12 @@ Load(file); } - - /// /// Opens an existing workbook from a stream. /// /// The stream to open. - public XLWorkbook(Stream stream):this(stream, XLEventTracking.Enabled) + public XLWorkbook(Stream stream) : this(stream, XLEventTracking.Enabled) { - } public XLWorkbook(Stream stream, XLEventTracking eventTracking) @@ -737,9 +727,9 @@ Load(stream); } -#endregion + #endregion Constructor -#region Nested type: UnsupportedSheet + #region Nested type: UnsupportedSheet internal sealed class UnsupportedSheet { @@ -748,7 +738,7 @@ public Int32 Position; } -#endregion + #endregion Nested type: UnsupportedSheet public IXLCell Cell(String namedCell) { @@ -790,13 +780,13 @@ internal XLIdManager ShapeIdManager { get; private set; } - public void Dispose() { Worksheets.ForEach(w => w.Dispose()); } public Boolean Use1904DateSystem { get; set; } + public XLWorkbook SetUse1904DateSystem() { return SetUse1904DateSystem(true); @@ -817,10 +807,12 @@ { return Worksheets.Add(sheetName, position); } + public IXLWorksheet AddWorksheet(DataTable dataTable) { return Worksheets.Add(dataTable); } + public void AddWorksheet(DataSet dataSet) { Worksheets.Add(dataSet); @@ -837,10 +829,12 @@ } private XLCalcEngine _calcEngine; + private XLCalcEngine CalcEngine { get { return _calcEngine ?? (_calcEngine = new XLCalcEngine(this)); } } + public Object Evaluate(String expression) { return CalcEngine.Evaluate(expression); @@ -853,6 +847,7 @@ { get { return _calcEngineExpr ?? (_calcEngineExpr = new XLCalcEngine()); } } + public static Object EvaluateExpr(String expression) { return CalcEngineExpr.Evaluate(expression); @@ -861,12 +856,16 @@ public String Author { get; set; } public Boolean LockStructure { get; set; } + public XLWorkbook SetLockStructure(Boolean value) { LockStructure = value; return this; } + public Boolean LockWindows { get; set; } + public XLWorkbook SetLockWindows(Boolean value) { LockWindows = value; return this; } + internal HexBinaryValue LockPassword { get; set; } public Boolean IsPasswordProtected { get { return LockPassword != null; } } - + public void Protect(Boolean lockStructure, Boolean lockWindows, String workbookPassword) { if (IsPasswordProtected && workbookPassword == null) @@ -886,7 +885,6 @@ LockPassword = null; } - if (!IsPasswordProtected && hashPassword != null && (lockStructure || lockWindows)) { //Protect workbook using password. @@ -896,7 +894,7 @@ LockStructure = lockStructure; LockWindows = lockWindows; } - + public void Protect() { Protect(true); @@ -927,4 +925,4 @@ Protect(false, false, workbookPassword); } } -} \ No newline at end of file +}