diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index b841566..5b1e845 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -294,11 +294,11 @@ cAddress = fA1; } - if (_worksheet.Internals.Workbook.WorksheetsInternal.Any( + if (_worksheet.Workbook.WorksheetsInternal.Any( w => w.Name.ToLower().Equals(sName.ToLower())) && ExcelHelper.IsValidA1Address(cAddress) ) - return _worksheet.Internals.Workbook.Worksheet(sName).Cell(cAddress).Value; + return _worksheet.Workbook.Worksheet(sName).Cell(cAddress).Value; return fA1; } @@ -1264,7 +1264,7 @@ var address = XLAddress.Create(_worksheet, a1Address); string rowPart = GetR1C1Row(address.RowNumber, address.FixedRow, rowsToShift); - string columnPart = GetR1C1Column(address.ColumnNumber, address.FixedRow, columnsToShift); + string columnPart = GetR1C1Column(address.ColumnNumber, address.FixedColumn, columnsToShift); return rowPart + columnPart; } @@ -1368,7 +1368,7 @@ string rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); if (!a1ColumnRegex.IsMatch(rangeAddress)) { - var matchRange = _worksheet.Internals.Workbook.Worksheet(sheetName).Range(rangeAddress); + var matchRange = _worksheet.Workbook.Worksheet(sheetName).Range(rangeAddress); if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.LastAddress.RowNumber && @@ -1586,7 +1586,7 @@ string rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); if (!a1RowRegex.IsMatch(rangeAddress)) { - var matchRange = _worksheet.Internals.Workbook.Worksheet(sheetName).Range(rangeAddress); + var matchRange = _worksheet.Workbook.Worksheet(sheetName).Range(rangeAddress); if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.LastAddress.ColumnNumber && diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLAddress.cs index ace531d..643642a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Coordinates/XLAddress.cs @@ -6,6 +6,16 @@ internal class XLAddress : IXLAddress { #region Static + /// + /// Create address without worksheet. For calculation only! + /// + /// + /// + public static XLAddress Create(string cellAddressString) + { + return Create(null, cellAddressString); + } + public static XLAddress Create(XLWorksheet worksheet, string cellAddressString) { var fixedColumn = cellAddressString[0] == '$'; @@ -59,22 +69,31 @@ #endregion #region Private fields [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private bool m_fixedRow; + private bool _fixedRow; [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private bool m_fixedColumn; + private bool _fixedColumn; [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private string m_columnLetter; + private string _columnLetter; [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly int m_rowNumber; + private readonly int _rowNumber; [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly int m_columnNumber; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly int m_hashCode; - private string m_trimmedAddress; + private readonly int _columnNumber; + private string _trimmedAddress; #endregion #region Constructors /// + /// Initializes a new struct using a mixed notation. Attention: without worksheet for calculation only! + /// + /// The row number of the cell address. + /// The column letter of the cell address. + /// + /// + public XLAddress(int rowNumber, string columnLetter, bool fixedRow, bool fixedColumn) + : this(null, rowNumber, columnLetter, fixedRow, fixedColumn) + { + } + /// /// Initializes a new struct using a mixed notation. /// /// @@ -85,10 +104,21 @@ public XLAddress(XLWorksheet worksheet, int rowNumber, string columnLetter, bool fixedRow, bool fixedColumn) : this(worksheet, rowNumber, ExcelHelper.GetColumnNumberFromLetter(columnLetter), fixedRow, fixedColumn) { - m_columnLetter = columnLetter; + _columnLetter = columnLetter; } /// + /// Initializes a new struct using R1C1 notation. Attention: without worksheet for calculation only! + /// + /// The row number of the cell address. + /// The column number of the cell address. + /// + /// + public XLAddress(int rowNumber, int columnNumber, bool fixedRow, bool fixedColumn) + : this(null, rowNumber, columnNumber, fixedRow, fixedColumn) + { + } + /// /// Initializes a new struct using R1C1 notation. /// /// @@ -101,13 +131,13 @@ { Worksheet = worksheet; - m_rowNumber = rowNumber; - m_columnNumber = columnNumber; - m_columnLetter = null; - m_fixedColumn = fixedColumn; - m_fixedRow = fixedRow; + _rowNumber = rowNumber; + _columnNumber = columnNumber; + _columnLetter = null; + _fixedColumn = fixedColumn; + _fixedRow = fixedRow; - m_hashCode = m_rowNumber ^ m_columnNumber; + } #endregion #region Properties @@ -118,16 +148,22 @@ get { return Worksheet; } } + public bool HasWorksheet + { + [DebuggerStepThrough] + get { return Worksheet != null; } + } + public bool FixedRow { - get { return m_fixedRow; } - set { m_fixedRow = value; } + get { return _fixedRow; } + set { _fixedRow = value; } } public bool FixedColumn { - get { return m_fixedColumn; } - set { m_fixedColumn = value; } + get { return _fixedColumn; } + set { _fixedColumn = value; } } /// @@ -135,7 +171,7 @@ /// public Int32 RowNumber { - get { return m_rowNumber; } + get { return _rowNumber; } } /// @@ -143,7 +179,7 @@ /// public Int32 ColumnNumber { - get { return m_columnNumber; } + get { return _columnNumber; } } /// @@ -151,46 +187,46 @@ /// public String ColumnLetter { - get { return m_columnLetter ?? (m_columnLetter = ExcelHelper.GetColumnLetterFromNumber(m_columnNumber)); } + get { return _columnLetter ?? (_columnLetter = ExcelHelper.GetColumnLetterFromNumber(_columnNumber)); } } #endregion #region Overrides public override string ToString() { String retVal = ColumnLetter; - if (m_fixedColumn) + if (_fixedColumn) { retVal = "$" + retVal; } - if (m_fixedRow) + if (_fixedRow) { retVal += "$"; } - retVal += m_rowNumber.ToStringLookup(); + retVal += _rowNumber.ToStringLookup(); return retVal; } - public String ToString(XLReferenceStyle referenceStyle) + public string ToString(XLReferenceStyle referenceStyle) { if (referenceStyle == XLReferenceStyle.A1) { - return ColumnLetter + m_rowNumber.ToStringLookup(); + return ColumnLetter + _rowNumber.ToStringLookup(); } if (referenceStyle == XLReferenceStyle.R1C1) { - return m_rowNumber.ToStringLookup() + "," + ColumnNumber; + return _rowNumber.ToStringLookup() + "," + ColumnNumber; } - if ((Worksheet).Internals.Workbook.ReferenceStyle == XLReferenceStyle.R1C1) + if (HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1) { - return m_rowNumber.ToStringLookup() + "," + ColumnNumber; + return _rowNumber.ToStringLookup() + "," + ColumnNumber; } - return ColumnLetter + m_rowNumber.ToStringLookup(); + return ColumnLetter + _rowNumber.ToStringLookup(); } #endregion #region Methods public string GetTrimmedAddress() { - return m_trimmedAddress ?? (m_trimmedAddress = ColumnLetter + m_rowNumber.ToStringLookup()); + return _trimmedAddress ?? (_trimmedAddress = ColumnLetter + _rowNumber.ToStringLookup()); } public string ToStringRelative() @@ -200,7 +236,7 @@ public string ToStringFixed() { - return "$" + ColumnLetter + "$" + m_rowNumber.ToStringLookup(); + return "$" + ColumnLetter + "$" + _rowNumber.ToStringLookup(); } #endregion @@ -210,8 +246,8 @@ return new XLAddress(left.Worksheet, left.RowNumber + right.RowNumber, left.ColumnNumber + right.ColumnNumber, - left.m_fixedRow, - left.m_fixedColumn); + left._fixedRow, + left._fixedColumn); } public static XLAddress operator -(XLAddress left, XLAddress right) @@ -219,8 +255,8 @@ return new XLAddress(left.Worksheet, left.RowNumber - right.RowNumber, left.ColumnNumber - right.ColumnNumber, - left.m_fixedRow, - left.m_fixedColumn); + left._fixedRow, + left._fixedColumn); } public static XLAddress operator +(XLAddress left, Int32 right) @@ -228,8 +264,8 @@ return new XLAddress(left.Worksheet, left.RowNumber + right, left.ColumnNumber + right, - left.m_fixedRow, - left.m_fixedColumn); + left._fixedRow, + left._fixedColumn); } public static XLAddress operator -(XLAddress left, Int32 right) @@ -237,8 +273,8 @@ return new XLAddress(left.Worksheet, left.RowNumber - right, left.ColumnNumber - right, - left.m_fixedRow, - left.m_fixedColumn); + left._fixedRow, + left._fixedColumn); } public static Boolean operator ==(XLAddress left, XLAddress right) @@ -271,19 +307,19 @@ return obj.GetHashCode(); } - public new Boolean Equals(Object x, Object y) + public new Boolean Equals(object x, object y) { return x == y; } - public Int32 GetHashCode(Object obj) + public Int32 GetHashCode(object obj) { return (obj).GetHashCode(); } - public override Int32 GetHashCode() + public override int GetHashCode() { - return m_hashCode; + return _rowNumber ^ _columnNumber; } #endregion #region IEquatable Members @@ -294,12 +330,7 @@ { return false; } - if (m_hashCode != right.m_hashCode) - { - return false; - } - - return m_rowNumber == right.m_rowNumber && m_columnNumber == right.m_columnNumber; + return _rowNumber == right._rowNumber && _columnNumber == right._columnNumber; } public override Boolean Equals(Object other) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index b9c071d..e06693b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -330,13 +330,13 @@ public IXLRange AddToNamed(String rangeName, XLScope scope, String comment) { var namedRanges = scope == XLScope.Workbook - ? Worksheet.Internals.Workbook.NamedRanges + ? Worksheet.Workbook.NamedRanges : Worksheet.NamedRanges; if (namedRanges.Any(nr => nr.Name.ToLower() == rangeName.ToLower())) { var namedRange = namedRanges.Where(nr => nr.Name.ToLower() == rangeName.ToLower()).Single(); - namedRange.Add(Worksheet.Internals.Workbook, ToStringFixed()); + namedRange.Add(Worksheet.Workbook, ToStringFixed()); } else namedRanges.Add(rangeName, ToStringFixed(), comment); @@ -668,7 +668,7 @@ public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns) { - foreach (XLWorksheet ws in Worksheet.Internals.Workbook.WorksheetsInternal) + foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { foreach ( XLCell cell in @@ -816,7 +816,7 @@ public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows) { - foreach (XLWorksheet ws in Worksheet.Internals.Workbook.WorksheetsInternal) + foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { foreach ( XLCell cell in @@ -942,7 +942,7 @@ // Worksheet.Internals.CellsCollection.Remove(lastCell.Address.RowNumber, lastCell.Address.ColumnNumber); //} - foreach (IXLWorksheet ws in Worksheet.Internals.Workbook.Worksheets) + foreach (IXLWorksheet ws in Worksheet.Workbook.Worksheets) { var xlWorksheet = (XLWorksheet)ws; foreach ( diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index 200f8e4..4627ebc 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -19,6 +19,15 @@ Default }; + + public enum XLCellSetValueBehavior + { + /// Analyze input string and convert value. For avoid analyzing use escape symbol ' + Smart = 0, + /// Direct set value. If value has unsupported type - value will be stored as string returned by + Simple = 1, + } + public partial class XLWorkbook { #region Static @@ -129,6 +138,10 @@ }; } } + /// + /// Behavior for + /// + public static XLCellSetValueBehavior CellSetValueBehavior { get; set; } #endregion private void InitializeTheme() { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 33af857..9a79fe2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -56,7 +56,7 @@ Workbook = workbook; _style = new XLStyle(this, workbook.Style); Internals = new XLWorksheetInternals(new XLCellsCollection(), new XLColumnsCollection(), - new XLRowsCollection(), new XLRanges(), workbook); + new XLRowsCollection(), new XLRanges()); PageSetup = new XLPageSetup(workbook.PageOptions, this); Outline = new XLOutline(workbook.Outline); ColumnWidth = workbook.ColumnWidth; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs index a55c68d..24f61ec 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs @@ -6,21 +6,18 @@ XLCellsCollection cellsCollection, XLColumnsCollection columnsCollection, XLRowsCollection rowsCollection, - XLRanges mergedRanges, - XLWorkbook workbook + XLRanges mergedRanges ) { CellsCollection = cellsCollection; ColumnsCollection = columnsCollection; RowsCollection = rowsCollection; MergedRanges = mergedRanges; - Workbook = workbook; } public XLCellsCollection CellsCollection { get; private set; } public XLColumnsCollection ColumnsCollection { get; private set; } public XLRowsCollection RowsCollection { get; private set; } public XLRanges MergedRanges { get; internal set; } - public XLWorkbook Workbook { get; internal set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs index 72d6c84..1d16762 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs @@ -1,4 +1,5 @@ using System; +using System.IO; using ClosedXML.Excel; @@ -11,70 +12,82 @@ // Public public void Create(String filePath) { - var wb = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = wb.Worksheet(1); - ws.Name = "Contacts Table"; - var firstCell = ws.FirstCellUsed(); - var lastCell = ws.LastCellUsed(); - var range = ws.Range(firstCell.Address, lastCell.Address); - range.Row(1).Delete(); // Deleting the "Contacts" header (we don't need it for our purposes) - range.ClearStyles(); // We want to use a theme for table, not the hard coded format of the BasicTable - - var table = range.CreateTable(); // You can also use range.AsTable() if you want to - // manipulate the range as a table but don't want - // to create the table in the worksheet. - - // Let's activate the Totals row and add the sum of Income - table.ShowTotalsRow = true; - table.Field("Income").TotalsRowFunction = XLTotalsRowFunction.Sum; - // Just for fun let's add the text "Sum Of Income" to the totals row - table.Field(0).TotalsRowLabel = "Sum Of Income"; - - // Copy all the headers - Int32 columnWithHeaders = lastCell.Address.ColumnNumber + 2; - Int32 currentRow = table.RangeAddress.FirstAddress.RowNumber; - ws.Cell(currentRow, columnWithHeaders).Value = "Table Headers"; - foreach (var cell in table.HeadersRow().Cells()) + string tempFile = ExampleHelper.GetTempFilePath(); + try { - currentRow++; - ws.Cell(currentRow, columnWithHeaders).Value = cell.Value; + new BasicTable().Create(tempFile); + var wb = new XLWorkbook(tempFile); + var ws = wb.Worksheet(1); + ws.Name = "Contacts Table"; + var firstCell = ws.FirstCellUsed(); + var lastCell = ws.LastCellUsed(); + var range = ws.Range(firstCell.Address, lastCell.Address); + range.Row(1).Delete(); // Deleting the "Contacts" header (we don't need it for our purposes) + range.ClearStyles(); // We want to use a theme for table, not the hard coded format of the BasicTable + + var table = range.CreateTable(); // You can also use range.AsTable() if you want to + // manipulate the range as a table but don't want + // to create the table in the worksheet. + + // Let's activate the Totals row and add the sum of Income + table.ShowTotalsRow = true; + table.Field("Income").TotalsRowFunction = XLTotalsRowFunction.Sum; + // Just for fun let's add the text "Sum Of Income" to the totals row + table.Field(0).TotalsRowLabel = "Sum Of Income"; + + // Copy all the headers + Int32 columnWithHeaders = lastCell.Address.ColumnNumber + 2; + Int32 currentRow = table.RangeAddress.FirstAddress.RowNumber; + ws.Cell(currentRow, columnWithHeaders).Value = "Table Headers"; + foreach (var cell in table.HeadersRow().Cells()) + { + currentRow++; + ws.Cell(currentRow, columnWithHeaders).Value = cell.Value; + } + + // Format the headers as a table with a different style and no autofilters + var htFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithHeaders); + var htLastCell = ws.Cell(currentRow, columnWithHeaders); + var headersTable = ws.Range(htFirstCell, htLastCell).CreateTable("Headers"); + headersTable.Theme = XLTableTheme.TableStyleLight10; + headersTable.ShowAutoFilter = false; + + // Add a custom formula to the headersTable + headersTable.ShowTotalsRow = true; + headersTable.Field(0).TotalsRowFormulaA1 = "CONCATENATE(\"Count: \", CountA(Headers[Table Headers]))"; + + // Copy the names + Int32 columnWithNames = columnWithHeaders + 2; + currentRow = table.RangeAddress.FirstAddress.RowNumber; // reset the currentRow + ws.Cell(currentRow, columnWithNames).Value = "Names"; + foreach (var row in table.Rows()) + { + currentRow++; + var fName = row.Field("FName").GetString(); // Notice how we're calling the cell by field name + var lName = row.Field("LName").GetString(); // Notice how we're calling the cell by field name + var name = String.Format("{0} {1}", fName, lName); + ws.Cell(currentRow, columnWithNames).Value = name; + } + + // Format the names as a table with a different style and no autofilters + var ntFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithNames); + var ntLastCell = ws.Cell(currentRow, columnWithNames); + var namesTable = ws.Range(ntFirstCell, ntLastCell).CreateTable(); + namesTable.Theme = XLTableTheme.TableStyleLight12; + namesTable.ShowAutoFilter = false; + + ws.Columns().AdjustToContents(); + ws.Columns("A,G,I").Width = 3; + + wb.SaveAs(filePath); } - - // Format the headers as a table with a different style and no autofilters - var htFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithHeaders); - var htLastCell = ws.Cell(currentRow, columnWithHeaders); - var headersTable = ws.Range(htFirstCell, htLastCell).CreateTable("Headers"); - headersTable.Theme = XLTableTheme.TableStyleLight10; - headersTable.ShowAutoFilter = false; - - // Add a custom formula to the headersTable - headersTable.ShowTotalsRow = true; - headersTable.Field(0).TotalsRowFormulaA1 = "CONCATENATE(\"Count: \", CountA(Headers[Table Headers]))"; - - // Copy the names - Int32 columnWithNames = columnWithHeaders + 2; - currentRow = table.RangeAddress.FirstAddress.RowNumber; // reset the currentRow - ws.Cell(currentRow, columnWithNames).Value = "Names"; - foreach (var row in table.Rows()) + finally { - currentRow++; - var fName = row.Field("FName").GetString(); // Notice how we're calling the cell by field name - var lName = row.Field("LName").GetString(); // Notice how we're calling the cell by field name - var name = String.Format("{0} {1}", fName, lName); - ws.Cell(currentRow, columnWithNames).Value = name; + if (File.Exists(tempFile)) + { + File.Delete(tempFile); + } } - - // Format the names as a table with a different style and no autofilters - var ntFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithNames); - var ntLastCell = ws.Cell(currentRow, columnWithNames); - var namesTable = ws.Range(ntFirstCell, ntLastCell).CreateTable(); - namesTable.Theme = XLTableTheme.TableStyleLight12; - namesTable.ShowAutoFilter = false; - - ws.Columns().AdjustToContents(); - ws.Columns("A,G,I").Width = 3; - - wb.SaveAs(filePath); } // Private diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/TestHelper.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/TestHelper.cs index 2e599de..23d7056 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/TestHelper.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/TestHelper.cs @@ -9,6 +9,7 @@ { //Note: Run example tests parameters public const string TestsOutputDirectory = @"C:\Excel Files\Tests\"; + public const string ActualTestResultPostFix = "(Actual)"; public static readonly string TestsExampleOutputDirectory = Path.Combine(TestsOutputDirectory, "Examples"); private const bool RemoveSuccessExampleFiles = false; private const bool CompareWithResources = true; @@ -26,6 +27,14 @@ var example = new T(); string filePath = Path.Combine(TestsExampleOutputDirectory, filePartName); + var extension = Path.GetExtension(filePath); + var directory = Path.GetDirectoryName(filePath); + + var fileName= Path.GetFileNameWithoutExtension(filePath); + fileName += ActualTestResultPostFix; + fileName = Path.ChangeExtension(fileName, extension); + + filePath = Path.Combine(directory, fileName); //Run test example.Create(filePath); bool success = true;