diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index baeaf73..c4848c2 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -10,8 +10,8 @@ namespace ClosedXML.Excel { - public enum XLEventTracking { Enabled, Disabled } + public enum XLCalculateMode { Auto, @@ -41,7 +41,7 @@ Simple = 1, } - public partial class XLWorkbook: IDisposable + public partial class XLWorkbook : IDisposable { #region Static @@ -52,71 +52,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 + } + }); } } @@ -128,24 +116,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; } } @@ -155,10 +143,10 @@ get { return new XLOutline(null) - { - SummaryHLocation = XLOutlineSummaryHLocation.Right, - SummaryVLocation = XLOutlineSummaryVLocation.Bottom - }; + { + SummaryHLocation = XLOutlineSummaryHLocation.Right, + SummaryVLocation = XLOutlineSummaryVLocation.Bottom + }; } } @@ -167,7 +155,12 @@ /// public static XLCellSetValueBehavior CellSetValueBehavior { get; set; } - #endregion + public static XLWorkbook OpenFromTemplate(String path) + { + return new XLWorkbook(path, true); + } + + #endregion Static internal readonly List UnsupportedSheets = new List(); @@ -195,7 +188,7 @@ return _stylesById[id]; } - #region Nested Type: XLLoadSource + #region Nested Type: XLLoadSource private enum XLLoadSource { @@ -204,7 +197,7 @@ Stream }; - #endregion + #endregion Nested Type: XLLoadSource internal XLWorksheets WorksheetsInternal { get; private set; } @@ -266,7 +259,6 @@ /// public XLCalculateMode CalculateMode { get; set; } - public Boolean CalculationOnSave { get; set; } public Boolean ForceFullCalculation { get; set; } public Boolean FullCalculationOnLoad { get; set; } @@ -331,20 +323,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) @@ -353,24 +345,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"); } @@ -423,7 +425,6 @@ return null; } - /// /// Saves the current workbook. /// @@ -512,12 +513,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)); - } } @@ -598,7 +600,6 @@ output.Write(buffer, 0, len); // dm 20130422, and flushing the output after write output.Flush(); - } public IXLWorksheet Worksheet(String name) @@ -668,25 +669,28 @@ } } - #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) { + } + internal XLWorkbook(String file, Boolean asTemplate) + : this(XLEventTracking.Enabled) + { + LoadSheetsFromTemplate(file); } public XLWorkbook(XLEventTracking eventTracking) @@ -725,7 +729,6 @@ public XLWorkbook(String file) : this(file, XLEventTracking.Enabled) { - } public XLWorkbook(String file, XLEventTracking eventTracking) @@ -737,15 +740,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) @@ -756,9 +756,9 @@ Load(stream); } -#endregion + #endregion Constructor -#region Nested type: UnsupportedSheet + #region Nested type: UnsupportedSheet internal sealed class UnsupportedSheet { @@ -767,7 +767,7 @@ public Int32 Position; } -#endregion + #endregion Nested type: UnsupportedSheet public IXLCell Cell(String namedCell) { @@ -809,13 +809,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); @@ -836,10 +836,12 @@ { return Worksheets.Add(sheetName, position); } + public IXLWorksheet AddWorksheet(DataTable dataTable) { return Worksheets.Add(dataTable); } + public void AddWorksheet(DataSet dataSet) { Worksheets.Add(dataSet); @@ -856,10 +858,12 @@ } private XLCalcEngine _calcEngine; + private XLCalcEngine CalcEngine { get { return _calcEngine ?? (_calcEngine = new XLCalcEngine(this)); } } + public Object Evaluate(String expression) { return CalcEngine.Evaluate(expression); @@ -872,6 +876,7 @@ { get { return _calcEngineExpr ?? (_calcEngineExpr = new XLCalcEngine()); } } + public static Object EvaluateExpr(String expression) { return CalcEngineExpr.Evaluate(expression); @@ -880,9 +885,13 @@ 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; } } @@ -905,7 +914,6 @@ LockPassword = null; } - if (!IsPasswordProtected && hashPassword != null && (lockStructure || lockWindows)) { //Protect workbook using password. diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 4d879ae..4490d5a 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -55,11 +55,17 @@ LoadSpreadsheetDocument(dSpreadsheet); } + private void LoadSheetsFromTemplate(String fileName) + { + using (var dSpreadsheet = SpreadsheetDocument.CreateFromTemplate(fileName)) + LoadSpreadsheetDocument(dSpreadsheet); + } + private void LoadSpreadsheetDocument(SpreadsheetDocument dSpreadsheet) { ShapeIdManager = new XLIdManager(); SetProperties(dSpreadsheet); - //var sharedStrings = dSpreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable.Elements(); + SharedStringItem[] sharedStrings = null; if (dSpreadsheet.WorkbookPart.GetPartsOfType().Count() > 0) { diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 6c67db2..da45762 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -87,7 +87,7 @@ } } - private bool Validate(SpreadsheetDocument package) + private Boolean Validate(SpreadsheetDocument package) { var backupCulture = Thread.CurrentThread.CurrentCulture; diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index b3da509..c9b60d4 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -93,6 +93,7 @@ + diff --git a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs index 7d39689..f416784 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs @@ -122,11 +122,11 @@ [Test] public void Vlookup_Exceptions() { - Assert.That(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$B$2:$I$71,3,FALSE)"), Throws.TypeOf()); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$B$2:$I$71,3,FALSE)"), Throws.TypeOf()); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)"), Throws.TypeOf()); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$B$2:$I$71,3,FALSE)"), Throws.TypeOf()); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$B$2:$I$71,3,FALSE)"), Throws.TypeOf()); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)"), Throws.TypeOf()); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$B$2:$I$71,9,TRUE)"), Throws.TypeOf()); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$B$2:$I$71,9,TRUE)"), Throws.TypeOf()); } } } diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 9ecccc6..a20cf0f 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -1,6 +1,8 @@ using ClosedXML.Excel; using ClosedXML.Excel.Drawings; +using ClosedXML_Tests.Utils; using NUnit.Framework; +using System; using System.Collections.Generic; using System.IO; using System.Linq; @@ -180,5 +182,26 @@ wb.SaveAs(ms, true); } } + + [Test] + public void CanLoadFromTemplate() + { + using (var tf1 = new TemporaryFile()) + using (var tf2 = new TemporaryFile()) + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\AllShapes.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + // Save as temporary file + wb.SaveAs(tf1.Path); + } + + var workbook = XLWorkbook.OpenFromTemplate(tf1.Path); + Assert.True(workbook.Worksheets.Any()); + Assert.Throws(() => workbook.Save()); + + workbook.SaveAs(tf2.Path); + } + } } } diff --git a/ClosedXML_Tests/OleDb/OleDbTests.cs b/ClosedXML_Tests/OleDb/OleDbTests.cs index e502614..2aa897c 100644 --- a/ClosedXML_Tests/OleDb/OleDbTests.cs +++ b/ClosedXML_Tests/OleDb/OleDbTests.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel; +using ClosedXML_Tests.Utils; using NUnit.Framework; using System; using System.Collections.Generic; @@ -16,7 +17,7 @@ [Test] public void TestOleDbValues() { - using (var tf = new TestFile(CreateTestFile())) + using (var tf = new TemporaryFile(CreateTestFile())) { Console.Write("Using temporary file\t{0}", tf.Path); var connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';", tf.Path); @@ -115,32 +116,5 @@ return path; } } - - internal class TestFile : IDisposable - { - internal TestFile(string path) - : this(path, false) - { } - - internal TestFile(string path, bool preserve) - { - this.Path = path; - this.Preserve = preserve; - } - - public string Path { get; private set; } - public bool Preserve { get; private set; } - - public void Dispose() - { - if (!Preserve) - File.Delete(Path); - } - - public override string ToString() - { - return this.Path; - } - } } } diff --git a/ClosedXML_Tests/Utils/TemporaryFile.cs b/ClosedXML_Tests/Utils/TemporaryFile.cs new file mode 100644 index 0000000..6d95b1f --- /dev/null +++ b/ClosedXML_Tests/Utils/TemporaryFile.cs @@ -0,0 +1,37 @@ +using System; +using System.IO; + +namespace ClosedXML_Tests.Utils +{ + internal class TemporaryFile : IDisposable + { + internal TemporaryFile() + : this(System.IO.Path.ChangeExtension(System.IO.Path.GetTempFileName(), "xlsx")) + { } + + internal TemporaryFile(string path) + : this(path, false) + { } + + internal TemporaryFile(String path, bool preserve) + { + this.Path = path; + this.Preserve = preserve; + } + + + public string Path { get; private set; } + public bool Preserve { get; private set; } + + public void Dispose() + { + if (!Preserve) + File.Delete(Path); + } + + public override string ToString() + { + return this.Path; + } + } +}