diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index c7cf602..71815b3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -140,5 +140,11 @@ IXLWorksheet Worksheet { get; } IXLDataValidation DataValidation { get; } + + + IXLCells InsertCellsAbove(int numberOfRows); + IXLCells InsertCellsBelow(int numberOfRows); + IXLCells InsertCellsAfter(int numberOfColumns); + IXLCells InsertCellsBefore(int numberOfColumns); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 11a3bfb..78e6d55 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -210,11 +210,17 @@ var fA1 = FormulaA1; if (!StringExtensions.IsNullOrWhiteSpace(fA1)) { + if (fA1[0] == '{') + fA1 = fA1.Substring(1, fA1.Length - 2); + String sName; String cAddress; if (fA1.Contains('!')) { sName = fA1.Substring(0, fA1.IndexOf('!')); + if (sName[0] == '\'') + sName = sName.Substring(1, sName.Length - 2); + cAddress = fA1.Substring(fA1.IndexOf('!') + 1); } else @@ -224,7 +230,7 @@ } - if (worksheet.Internals.Workbook.Worksheets.Where(w => w.Name == sName).Any() + if (worksheet.Internals.Workbook.Worksheets.Where(w => w.Name.ToLower().Equals(sName.ToLower())).Any() && XLAddress.IsValidA1Address(cAddress) ) { @@ -234,8 +240,6 @@ { return fA1; } - - } else { @@ -830,7 +834,15 @@ get { if (StringExtensions.IsNullOrWhiteSpace(formulaA1)) - return String.Empty; + { + if (!StringExtensions.IsNullOrWhiteSpace(formulaR1C1)) + { + formulaA1 = GetFormulaA1(formulaR1C1); + return FormulaA1; + } + else + return String.Empty; + } else if (formulaA1.Trim()[0] == '=') return formulaA1.Substring(1); else if (formulaA1.Trim().StartsWith("{=")) @@ -858,7 +870,7 @@ set { formulaR1C1 = value; - FormulaA1 = GetFormulaA1(value); + //FormulaA1 = GetFormulaA1(value); } } @@ -877,6 +889,35 @@ @"(?<=\W)(\$?[a-zA-Z]{1,3}\$?\d{1,7})(?=\W)" // A1 + @"|(?<=\W)(\d{1,7}:\d{1,7})(?=\W)" // 1:1 + @"|(?<=\W)([a-zA-Z]{1,3}:[a-zA-Z]{1,3})(?=\W)"); // A:A + + private static Regex a1SimpleRegex = new Regex( + @"(?<=\W)" // Start with non word + + @"(" // Start Group to pick + + @"(" // Start Sheet Name, optional + + @"(" + + @"\'[^\[\]\*/\\\?:]+\'" // Sheet name with special characters, surrounding apostrophes are required + + @"|" + + @"\'?\w+\'?" // Sheet name with letters and numbers, surrounding apostrophes are optional + + @")" + + @"!)?" // End Sheet Name, optional + + @"(" // Start range + + @"\$?[a-zA-Z]{1,3}\$?\d{1,7}" // A1 Address 1 + + @"(:\$?[a-zA-Z]{1,3}\$?\d{1,7})?" // A1 Address 2, optional + + @"|" + + @"(\d{1,7}:\d{1,7})" // 1:1 + + @"|" + + @"([a-zA-Z]{1,3}:[a-zA-Z]{1,3})" // A:A + + @")" // End Range + + @")" // End Group to pick + + @"(?=\W)" // End with non word + ); + + private static Regex a1RowRegex = new Regex( + @"(\d{1,7}:\d{1,7})" // 1:1 + ); + private static Regex a1ColumnRegex = new Regex( + @"([a-zA-Z]{1,3}:[a-zA-Z]{1,3})" // A:A + ); private static Regex r1c1Regex = new Regex( @"(?<=\W)([Rr]\[?-?\d{0,7}\]?[Cc]\[?-?\d{0,7}\]?)(?=\W)" // R1C1 @@ -1078,12 +1119,298 @@ this.formulaR1C1 = source.formulaR1C1; } - internal void ShiftFormula(Int32 rowsToShift, Int32 columnsToShift) + //internal void ShiftFormula(Int32 rowsToShift, Int32 columnsToShift) + //{ + // if (!StringExtensions.IsNullOrWhiteSpace(formulaA1)) + // FormulaR1C1 = GetFormula(formulaA1, FormulaConversionType.A1toR1C1, rowsToShift, columnsToShift); + // else if (!StringExtensions.IsNullOrWhiteSpace(formulaR1C1)) + // FormulaA1 = GetFormula(formulaR1C1, FormulaConversionType.R1C1toA1, rowsToShift, columnsToShift); + //} + + internal void ShiftFormulaRows(XLRange shiftedRange, int rowsShifted) { - if (!StringExtensions.IsNullOrWhiteSpace(formulaA1)) - FormulaR1C1 = GetFormula(formulaA1, FormulaConversionType.A1toR1C1, rowsToShift, columnsToShift); - else if (!StringExtensions.IsNullOrWhiteSpace(formulaA1)) - FormulaA1 = GetFormula(formulaA1, FormulaConversionType.R1C1toA1, rowsToShift, columnsToShift); + if (!StringExtensions.IsNullOrWhiteSpace(FormulaA1)) + { + var value = ">" + formulaA1 + "<"; + + Regex regex = a1SimpleRegex; + + var sb = new StringBuilder(); + var lastIndex = 0; + + foreach (var match in regex.Matches(value).Cast()) + { + var matchString = match.Value; + var matchIndex = match.Index; + if (value.Substring(0, matchIndex).CharCount('"') % 2 == 0) // Check that the match is not between quotes + { + sb.Append(value.Substring(lastIndex, matchIndex - lastIndex)); + String sheetName; + Boolean useSheetName = false; + if (matchString.Contains('!')) + { + sheetName = matchString.Substring(0, matchString.IndexOf('!')); + if (sheetName[0] == '\'') + sheetName = sheetName.Substring(1, sheetName.Length - 2); + useSheetName = true; + } + else + sheetName = worksheet.Name; + + if (sheetName.ToLower().Equals(shiftedRange.Worksheet.Name.ToLower())) + { + String rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); + if (!a1ColumnRegex.IsMatch(rangeAddress)) + { + IXLRange matchRange = worksheet.Internals.Workbook.Worksheet(sheetName).Range(rangeAddress); + if ( shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.LastAddress.RowNumber + && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber + && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= matchRange.RangeAddress.LastAddress.ColumnNumber) + { + if (a1RowRegex.IsMatch(rangeAddress)) + { + var rows = rangeAddress.Split(':'); + String row1String = rows[0]; + String row2String = rows[1]; + String row1; + if (row1String[0] == '$') + row1 = "$" + (Int32.Parse(row1String.Substring(1)) + rowsShifted).ToStringLookup(); + else + row1 = (Int32.Parse(row1String) + rowsShifted).ToStringLookup(); + + String row2; + if (row2String[0] == '$') + row2 = "$" + (Int32.Parse(row2String.Substring(1)) + rowsShifted).ToStringLookup(); + else + row2 = (Int32.Parse(row2String) + rowsShifted).ToStringLookup(); + + if (useSheetName) + sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, row1, row2)); + else + sb.Append(String.Format("{1}:{2}", row1, row2)); + } + else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.FirstAddress.RowNumber) + { + if (rangeAddress.Contains(':')) + { + if (useSheetName) + sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, + new XLAddress( + matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted, + matchRange.RangeAddress.FirstAddress.ColumnLetter, + matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn), + new XLAddress( + matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted, + matchRange.RangeAddress.LastAddress.ColumnLetter, + matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); + else + sb.Append(String.Format("{0}:{1}", + new XLAddress( + matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted, + matchRange.RangeAddress.FirstAddress.ColumnLetter, + matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn), + new XLAddress( + matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted, + matchRange.RangeAddress.LastAddress.ColumnLetter, + matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); + } + else + { + if (useSheetName) + sb.Append(String.Format("'{0}'!{1}", sheetName, + new XLAddress( + matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted, + matchRange.RangeAddress.FirstAddress.ColumnLetter, + matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn))); + else + sb.Append(String.Format("{0}", + new XLAddress( + matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted, + matchRange.RangeAddress.FirstAddress.ColumnLetter, + matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn))); + } + } + else + { + if (useSheetName) + sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, + matchRange.RangeAddress.FirstAddress.ToString(), + new XLAddress( + matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted, + matchRange.RangeAddress.LastAddress.ColumnLetter, + matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); + else + sb.Append(String.Format("{0}:{1}", + matchRange.RangeAddress.FirstAddress.ToString(), + new XLAddress( + matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted, + matchRange.RangeAddress.LastAddress.ColumnLetter, + matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); + } + } + else + { + sb.Append(matchString); + } + } + } + } + else + { + sb.Append(value.Substring(lastIndex, matchIndex - lastIndex + matchString.Length)); + } + lastIndex = matchIndex + matchString.Length; + } + if (lastIndex < value.Length) + sb.Append(value.Substring(lastIndex)); + + var retVal = sb.ToString(); + + formulaA1 = retVal.Substring(1, retVal.Length - 2); + } + } + + internal void ShiftFormulaColumns(XLRange shiftedRange, int columnsShifted) + { + if (!StringExtensions.IsNullOrWhiteSpace(FormulaA1)) + { + var value = ">" + formulaA1 + "<"; + + Regex regex = a1SimpleRegex; + + var sb = new StringBuilder(); + var lastIndex = 0; + + foreach (var match in regex.Matches(value).Cast()) + { + var matchString = match.Value; + var matchIndex = match.Index; + if (value.Substring(0, matchIndex).CharCount('"') % 2 == 0) // Check that the match is not between quotes + { + sb.Append(value.Substring(lastIndex, matchIndex - lastIndex)); + String sheetName; + Boolean useSheetName = false; + if (matchString.Contains('!')) + { + sheetName = matchString.Substring(0, matchString.IndexOf('!')); + if (sheetName[0] == '\'') + sheetName = sheetName.Substring(1, sheetName.Length - 2); + useSheetName = true; + } + else + sheetName = worksheet.Name; + + if (sheetName.ToLower().Equals(shiftedRange.Worksheet.Name.ToLower())) + { + String rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); + if (!a1RowRegex.IsMatch(rangeAddress)) + { + IXLRange matchRange = worksheet.Internals.Workbook.Worksheet(sheetName).Range(rangeAddress); + if ( shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.LastAddress.ColumnNumber + && shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.FirstAddress.RowNumber + && shiftedRange.RangeAddress.LastAddress.RowNumber >= matchRange.RangeAddress.LastAddress.RowNumber) + { + if (a1ColumnRegex.IsMatch(rangeAddress)) + { + var columns = rangeAddress.Split(':'); + String column1String = columns[0]; + String column2String = columns[1]; + String column1; + if (column1String[0] == '$') + column1 = "$" + XLAddress.GetColumnLetterFromNumber(XLAddress.GetColumnNumberFromLetter(column1String.Substring(1)) + columnsShifted); + else + column1 = XLAddress.GetColumnLetterFromNumber(XLAddress.GetColumnNumberFromLetter(column1String) + columnsShifted); + + String column2; + if (column2String[0] == '$') + column2 = "$" + XLAddress.GetColumnLetterFromNumber(XLAddress.GetColumnNumberFromLetter(column2String.Substring(1)) + columnsShifted); + else + column2 = XLAddress.GetColumnLetterFromNumber(XLAddress.GetColumnNumberFromLetter(column2String) + columnsShifted); + + if (useSheetName) + sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, column1, column2)); + else + sb.Append(String.Format("{1}:{2}", column1, column2)); + } + else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber) + { + if (rangeAddress.Contains(':')) + { + if (useSheetName) + sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, + new XLAddress( + matchRange.RangeAddress.FirstAddress.RowNumber, + matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, + matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn), + new XLAddress( + matchRange.RangeAddress.LastAddress.RowNumber, + matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted, + matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); + else + sb.Append(String.Format("{0}:{1}", + new XLAddress( + matchRange.RangeAddress.FirstAddress.RowNumber, + matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, + matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn), + new XLAddress( + matchRange.RangeAddress.LastAddress.RowNumber, + matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted, + matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); + } + else + { + if (useSheetName) + sb.Append(String.Format("'{0}'!{1}", sheetName, + new XLAddress( + matchRange.RangeAddress.FirstAddress.RowNumber, + matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, + matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn))); + else + sb.Append(String.Format("{0}", + new XLAddress( + matchRange.RangeAddress.FirstAddress.RowNumber, + matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted, + matchRange.RangeAddress.FirstAddress.FixedRow, matchRange.RangeAddress.FirstAddress.FixedColumn))); + } + } + else + { + if (useSheetName) + sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, + matchRange.RangeAddress.FirstAddress.ToString(), + new XLAddress( + matchRange.RangeAddress.LastAddress.RowNumber, + matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted, + matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); + else + sb.Append(String.Format("{0}:{1}", + matchRange.RangeAddress.FirstAddress.ToString(), + new XLAddress( + matchRange.RangeAddress.LastAddress.RowNumber, + matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted, + matchRange.RangeAddress.LastAddress.FixedRow, matchRange.RangeAddress.LastAddress.FixedColumn))); + } + } + else + { + sb.Append(matchString); + } + } + } + } + else + { + sb.Append(value.Substring(lastIndex, matchIndex - lastIndex + matchString.Length)); + } + lastIndex = matchIndex + matchString.Length; + } + if (lastIndex < value.Length) + sb.Append(value.Substring(lastIndex)); + + var retVal = sb.ToString(); + + formulaA1 = retVal.Substring(1, retVal.Length - 2); + } } public Boolean ShareString { get; set; } @@ -1127,5 +1454,22 @@ return this.AsRange().DataValidation; } } + + public IXLCells InsertCellsAbove(int numberOfRows) + { + return this.AsRange().InsertRowsAbove(numberOfRows).Cells(); + } + public IXLCells InsertCellsBelow(int numberOfRows) + { + return this.AsRange().InsertRowsBelow(numberOfRows).Cells(); + } + public IXLCells InsertCellsAfter(int numberOfColumns) + { + return this.AsRange().InsertColumnsAfter(numberOfColumns).Cells(); + } + public IXLCells InsertCellsBefore(int numberOfColumns) + { + return this.AsRange().InsertColumnsBefore(numberOfColumns).Cells(); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs index 3b9e784..81fabc8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs @@ -35,14 +35,14 @@ /// All columns at the right will be shifted accordingly. /// /// The number of columns to insert. - void InsertColumnsAfter(Int32 numberOfColumns); + IXLColumns InsertColumnsAfter(Int32 numberOfColumns); /// /// Inserts X number of columns at the left of this one. /// This column and all at the right will be shifted accordingly. /// /// The number of columns to insert. - void InsertColumnsBefore(Int32 numberOfColumns); + IXLColumns InsertColumnsBefore(Int32 numberOfColumns); /// /// Gets the cell in the specified row. @@ -67,18 +67,18 @@ /// /// Adjusts the width of the column based on its contents. /// - void AdjustToContents(); + IXLColumn AdjustToContents(); /// /// Adjusts the width of the column based on its contents, starting from the startRow. /// /// The row to start calculating the column width. - void AdjustToContents(Int32 startRow); + IXLColumn AdjustToContents(Int32 startRow); /// /// Adjusts the width of the column based on its contents, starting from the startRow and ending at endRow. /// /// The row to start calculating the column width. /// The row to end calculating the column width. - void AdjustToContents(Int32 startRow, Int32 endRow); + IXLColumn AdjustToContents(Int32 startRow, Int32 endRow); /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs index 6b77717..1dd77a9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs @@ -23,18 +23,18 @@ /// /// Adjusts the width of all columns based on its contents. /// - void AdjustToContents(); + IXLColumns AdjustToContents(); /// /// Adjusts the width of all columns based on its contents, starting from the startRow. /// /// The row to start calculating the column width. - void AdjustToContents(Int32 startRow); + IXLColumns AdjustToContents(Int32 startRow); /// /// Adjusts the width of all columns based on its contents, starting from the startRow and ending at endRow. /// /// The row to start calculating the column width. /// The row to end calculating the column width. - void AdjustToContents(Int32 startRow, Int32 endRow); + IXLColumns AdjustToContents(Int32 startRow, Int32 endRow); /// /// Hides all columns. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index f843fd5..2dabeb6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -9,7 +9,7 @@ internal class XLColumn: XLRangeBase, IXLColumn { public XLColumn(Int32 column, XLColumnParameters xlColumnParameters) - : base(new XLRangeAddress(1, column, XLWorksheet.MaxNumberOfRows, column)) + : base(new XLRangeAddress(new XLAddress(1, column, false, false), new XLAddress(XLWorksheet.MaxNumberOfRows, column, false, false))) { SetColumnNumber(column); Worksheet = xlColumnParameters.Worksheet; @@ -41,8 +41,8 @@ } else { - RangeAddress.FirstAddress = new XLAddress(1, column); - RangeAddress.LastAddress = new XLAddress(XLWorksheet.MaxNumberOfRows, column); + RangeAddress.FirstAddress = new XLAddress(1, column, RangeAddress.FirstAddress.FixedRow, RangeAddress.FirstAddress.FixedColumn); + RangeAddress.LastAddress = new XLAddress(XLWorksheet.MaxNumberOfRows, column, RangeAddress.LastAddress.FixedRow, RangeAddress.LastAddress.FixedColumn); } } @@ -218,14 +218,15 @@ #endregion - public new void InsertColumnsAfter(Int32 numberOfColumns) + public new IXLColumns InsertColumnsAfter(Int32 numberOfColumns) { var columnNum = this.ColumnNumber(); this.Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum + 1, numberOfColumns); XLRange range = (XLRange)this.Worksheet.Column(columnNum).AsRange(); range.InsertColumnsAfter(true, numberOfColumns); + return Worksheet.Columns(columnNum + 1, columnNum + numberOfColumns); } - public new void InsertColumnsBefore(Int32 numberOfColumns) + public new IXLColumns InsertColumnsBefore(Int32 numberOfColumns) { var columnNum = this.ColumnNumber(); this.Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum, numberOfColumns); @@ -233,6 +234,7 @@ // and we want to use the old columnNum. XLRange range = (XLRange)this.Worksheet.Column(columnNum).AsRange(); range.InsertColumnsBefore(true, numberOfColumns); + return Worksheet.Columns(columnNum, columnNum + numberOfColumns - 1); } public override IXLRange AsRange() @@ -265,15 +267,15 @@ return Range(firstRow, 1, lastRow, 1).Column(1); } - public void AdjustToContents() + public IXLColumn AdjustToContents() { - AdjustToContents(1); + return AdjustToContents(1); } - public void AdjustToContents(Int32 startRow) + public IXLColumn AdjustToContents(Int32 startRow) { - AdjustToContents(startRow, XLWorksheet.MaxNumberOfRows); + return AdjustToContents(startRow, XLWorksheet.MaxNumberOfRows); } - public void AdjustToContents(Int32 startRow, Int32 endRow) + public IXLColumn AdjustToContents(Int32 startRow, Int32 endRow) { Double maxWidth = 0; foreach (var c in CellsUsed().Where(cell=>cell.Address.RowNumber >= startRow && cell.Address.RowNumber <= endRow)) @@ -300,6 +302,8 @@ maxWidth = Worksheet.ColumnWidth; Width = maxWidth; + + return this; } public void Hide() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index 08b45cc..e67448d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -149,17 +149,20 @@ columns.Add(column); } - public void AdjustToContents() + public IXLColumns AdjustToContents() { columns.ForEach(c => c.AdjustToContents()); + return this; } - public void AdjustToContents(Int32 startRow) + public IXLColumns AdjustToContents(Int32 startRow) { columns.ForEach(c => c.AdjustToContents(startRow)); + return this; } - public void AdjustToContents(Int32 startRow, Int32 endRow) + public IXLColumns AdjustToContents(Int32 startRow, Int32 endRow) { columns.ForEach(c => c.AdjustToContents(startRow, endRow)); + return this; } public void Hide() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs index 44525d5..0f4f606 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs @@ -10,5 +10,7 @@ Int32 RowNumber { get; } Int32 ColumnNumber { get; } String ColumnLetter { get; } + Boolean FixedRow { get; } + Boolean FixedColumn { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index a8371fc..7fbb3c7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -235,6 +235,11 @@ /// Gets an object to manage this worksheet's named ranges. /// IXLNamedRanges NamedRanges { get; } + /// + /// Gets the specified named range. + /// + /// Name of the range. + IXLNamedRange NamedRange(String rangeName); /// /// Gets an object to manage how the worksheet is going to displayed by Excel. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs index 3d7a1d7..ca8c88c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/IXLNamedRanges.cs @@ -14,12 +14,6 @@ IXLNamedRange NamedRange(String rangeName); /// - /// Gets the specified named range's index - /// - /// Index of the named range. - IXLNamedRange NamedRange(Int32 rangeIndex); - - /// /// Adds a new named range. /// /// Name of the range to add. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs index c3b53da..e7eefb0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRanges.cs @@ -22,11 +22,6 @@ return namedRanges[rangeName]; } - public IXLNamedRange NamedRange(Int32 rangeIndex) - { - return namedRanges.ElementAt(rangeIndex).Value; - } - public IXLNamedRange Add(String rangeName, String rangeAddress) { return Add(rangeName, rangeAddress, null); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index c6c3dce..8856eb6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -173,29 +173,29 @@ /// All cells to the right of this range will be shifted X number of columns. /// /// Number of columns to insert. - void InsertColumnsAfter(int numberOfColumns); - void InsertColumnsAfter(int numberOfColumns, Boolean expandRange); + IXLRangeColumns InsertColumnsAfter(int numberOfColumns); + IXLRangeColumns InsertColumnsAfter(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of columns to the left of this range. /// This range and all cells to the right of this range will be shifted X number of columns. /// /// Number of columns to insert. - void InsertColumnsBefore(int numberOfColumns); - void InsertColumnsBefore(int numberOfColumns, Boolean expandRange); + IXLRangeColumns InsertColumnsBefore(int numberOfColumns); + IXLRangeColumns InsertColumnsBefore(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of rows on top of this range. /// This range and all cells below this range will be shifted X number of rows. /// /// Number of rows to insert. - void InsertRowsAbove(int numberOfRows); - void InsertRowsAbove(int numberOfRows, Boolean expandRange); + IXLRangeRows InsertRowsAbove(int numberOfRows); + IXLRangeRows InsertRowsAbove(int numberOfRows, Boolean expandRange); /// /// Inserts X number of rows below this range. /// All cells below this range will be shifted X number of rows. /// /// Number of rows to insert. - void InsertRowsBelow(int numberOfRows); - void InsertRowsBelow(int numberOfRows, Boolean expandRange); + IXLRangeRows InsertRowsBelow(int numberOfRows); + IXLRangeRows InsertRowsBelow(int numberOfRows, Boolean expandRange); /// /// Deletes this range and shifts the surrounding cells accordingly. @@ -215,8 +215,6 @@ IXLTable CreateTable(String name); IXLRange RangeUsed(); - - //IXLWorksheet Worksheet { get; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 679a856..c17d9b5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -152,6 +152,8 @@ /// Object Value { set; } + IXLRangeBase SetValue(T value); + /// /// Sets the type of the cells' data. /// Changing the data type will cause ClosedXML to covert the current value to the new data type. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs index 61865df..c7701ce 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -32,29 +32,29 @@ /// All cells to the right of this range will be shifted X number of columns. /// /// Number of columns to insert. - void InsertColumnsAfter(int numberOfColumns); - void InsertColumnsAfter(int numberOfColumns, Boolean expandRange); + IXLRangeColumns InsertColumnsAfter(int numberOfColumns); + IXLRangeColumns InsertColumnsAfter(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of columns to the left of this range. /// This range and all cells to the right of this range will be shifted X number of columns. /// /// Number of columns to insert. - void InsertColumnsBefore(int numberOfColumns); - void InsertColumnsBefore(int numberOfColumns, Boolean expandRange); + IXLRangeColumns InsertColumnsBefore(int numberOfColumns); + IXLRangeColumns InsertColumnsBefore(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of cells on top of this column. /// This column and all cells below it will be shifted X number of rows. /// /// Number of cells to insert. - void InsertCellsAbove(int numberOfRows); - void InsertCellsAbove(int numberOfRows, Boolean expandRange); + IXLCells InsertCellsAbove(int numberOfRows); + IXLCells InsertCellsAbove(int numberOfRows, Boolean expandRange); /// /// Inserts X number of cells below this range. /// All cells below this column will be shifted X number of rows. /// /// Number of cells to insert. - void InsertCellsBelow(int numberOfRows); - void InsertCellsBelow(int numberOfRows, Boolean expandRange); + IXLCells InsertCellsBelow(int numberOfRows); + IXLCells InsertCellsBelow(int numberOfRows, Boolean expandRange); /// /// Deletes this range and shifts the cells at the right. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs index a9033f4..8bb3055 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs @@ -44,29 +44,29 @@ /// All cells to the right of this row will be shifted X number of columns. /// /// Number of cells to insert. - void InsertCellsAfter(int numberOfColumns); - void InsertCellsAfter(int numberOfColumns, Boolean expandRange); + IXLCells InsertCellsAfter(int numberOfColumns); + IXLCells InsertCellsAfter(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of cells to the left of this row. /// This row and all cells to the right of it will be shifted X number of columns. /// /// Number of cells to insert. - void InsertCellsBefore(int numberOfColumns); - void InsertCellsBefore(int numberOfColumns, Boolean expandRange); + IXLCells InsertCellsBefore(int numberOfColumns); + IXLCells InsertCellsBefore(int numberOfColumns, Boolean expandRange); /// /// Inserts X number of rows on top of this row. /// This row and all cells below it will be shifted X number of rows. /// /// Number of rows to insert. - void InsertRowsAbove(int numberOfRows); - void InsertRowsAbove(int numberOfRows, Boolean expandRange); + IXLRangeRows InsertRowsAbove(int numberOfRows); + IXLRangeRows InsertRowsAbove(int numberOfRows, Boolean expandRange); /// /// Inserts X number of rows below this row. /// All cells below this row will be shifted X number of rows. /// /// Number of rows to insert. - void InsertRowsBelow(int numberOfRows); - void InsertRowsBelow(int numberOfRows, Boolean expandRange); + IXLRangeRows InsertRowsBelow(int numberOfRows); + IXLRangeRows InsertRowsBelow(int numberOfRows, Boolean expandRange); /// /// Deletes this range and shifts the cells below. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs index 58ca6c7..f5e391b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs @@ -27,5 +27,42 @@ IXLStyle Style { get; set; } IXLDataValidation DataValidation { get; } + + /// + /// Creates a named range out of these ranges. + /// If the named range exists, it will add these ranges to that named range. + /// The default scope for the named range is Workbook. + /// + /// Name of the range. + IXLRanges AddToNamed(String rangeName); + + /// + /// Creates a named range out of these ranges. + /// If the named range exists, it will add these ranges to that named range. + /// Name of the range. + /// The scope for the named range. + IXLRanges AddToNamed(String rangeName, XLScope scope); + + /// + /// Creates a named range out of these ranges. + /// If the named range exists, it will add these ranges to that named range. + /// Name of the range. + /// The scope for the named range. + /// The comments for the named range. + IXLRanges AddToNamed(String rangeName, XLScope scope, String comment); + + /// + /// Sets the cells' value. + /// If the object is an IEnumerable ClosedXML will copy the collection's data into a table starting from each cell. + /// If the object is a range ClosedXML will copy the range starting from each cell. + /// Setting the value to an object (not IEnumerable/range) will call the object's ToString() method. + /// ClosedXML will try to translate it to the corresponding type, if it can't then the value will be left as a string. + /// + /// + /// The object containing the value(s) to set. + /// + Object Value { set; } + + IXLRanges SetValue(T value); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 2157026..98b6f30 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -139,8 +139,8 @@ public IXLRangeRow Row(Int32 row) { - IXLAddress firstCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.FirstAddress.ColumnNumber); - IXLAddress lastCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.LastAddress.ColumnNumber); + IXLAddress firstCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.FirstAddress.ColumnNumber, false, false); + IXLAddress lastCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.LastAddress.ColumnNumber, false, false); return new XLRangeRow( new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet, @@ -149,8 +149,8 @@ } public IXLRangeColumn Column(Int32 column) { - IXLAddress firstCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1); - IXLAddress lastCellAddress = new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1); + IXLAddress firstCellAddress = new XLAddress(RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); + IXLAddress lastCellAddress = new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber + column - 1, false, false); return new XLRangeColumn( new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet, @@ -287,7 +287,8 @@ TransposeRange(squareSide); this.RangeAddress.LastAddress = new XLAddress( firstCell.Address.RowNumber + columnCount - 1, - firstCell.Address.ColumnNumber + rowCount - 1); + firstCell.Address.ColumnNumber + rowCount - 1, + RangeAddress.LastAddress.FixedRow, RangeAddress.LastAddress.FixedColumn); if (rowCount > columnCount) { var rng = Worksheet.Range( diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs index 2ae8ce6..75eae2b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -43,12 +43,6 @@ LastAddress = new XLAddress(lastCellAddress); } - public XLRangeAddress(Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn) - { - FirstAddress = new XLAddress(firstCellRow, firstCellColumn); - LastAddress = new XLAddress(lastCellRow, lastCellColumn); - } - public XLRangeAddress(IXLAddress firstAddress, IXLAddress lastAddress) { FirstAddress = firstAddress; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index b1aeb6a..e5d28c5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -67,29 +67,16 @@ public IXLCell Cell(Int32 row, Int32 column) { - return this.Cell(new XLAddress(row, column)); + return this.Cell(new XLAddress(row, column, false, false)); } public IXLCell Cell(String cellAddressInRange) { return this.Cell(new XLAddress(cellAddressInRange)); } - public IXLCell CellFast(String cellAddressInRange) - { - String toUse = cellAddressInRange; - //if (cellAddressInRange.Contains('$')) - // toUse = cellAddressInRange.Replace("$", ""); - //else - // toUse = cellAddressInRange; - var cIndex = 1; - while (!Char.IsNumber(toUse, cIndex)) - cIndex++; - - return this.Cell(new XLAddress(Int32.Parse(toUse.Substring(cIndex)), toUse.Substring(0, cIndex))); - } public IXLCell Cell(Int32 row, String column) { - return this.Cell(new XLAddress(row, column)); + return this.Cell(new XLAddress(row, column, false, false)); } public IXLCell Cell(IXLAddress cellAddressInRange) { @@ -158,7 +145,7 @@ } public IXLRange Range(Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn) { - var rangeAddress = new XLRangeAddress(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn); + var rangeAddress = new XLRangeAddress(new XLAddress(firstCellRow, firstCellColumn, false, false), new XLAddress(lastCellRow, lastCellColumn, false, false)); return Range(rangeAddress); } public IXLRange Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress) @@ -169,7 +156,11 @@ public IXLRange Range(IXLRangeAddress rangeAddress) { var newFirstCellAddress = (XLAddress)rangeAddress.FirstAddress + (XLAddress)this.RangeAddress.FirstAddress - 1; + newFirstCellAddress.FixedRow = rangeAddress.FirstAddress.FixedRow; + newFirstCellAddress.FixedColumn = rangeAddress.FirstAddress.FixedColumn; var newLastCellAddress = (XLAddress)rangeAddress.LastAddress + (XLAddress)this.RangeAddress.FirstAddress - 1; + newLastCellAddress.FixedRow = rangeAddress.LastAddress.FixedRow; + newLastCellAddress.FixedColumn = rangeAddress.LastAddress.FixedColumn; var newRangeAddress = new XLRangeAddress(newFirstCellAddress, newLastCellAddress); var xlRangeParameters = new XLRangeParameters(newRangeAddress, this.Worksheet, this.Style); if ( @@ -273,24 +264,23 @@ return AsRange(); } - public void InsertColumnsAfter(Int32 numberOfColumns) + public IXLRangeColumns InsertColumnsAfter(Int32 numberOfColumns) { - InsertColumnsAfter(numberOfColumns, true); + return InsertColumnsAfter(numberOfColumns, true); } - public void InsertColumnsAfter(Int32 numberOfColumns, Boolean expandRange) + public IXLRangeColumns InsertColumnsAfter(Int32 numberOfColumns, Boolean expandRange) { - this.InsertColumnsAfter(false, numberOfColumns); + var retVal = this.InsertColumnsAfter(false, numberOfColumns); // Adjust the range if (expandRange) { this.RangeAddress = new XLRangeAddress( - this.RangeAddress.FirstAddress.RowNumber, - this.RangeAddress.FirstAddress.ColumnNumber, - this.RangeAddress.LastAddress.RowNumber, - this.RangeAddress.LastAddress.ColumnNumber + numberOfColumns); + new XLAddress(RangeAddress.FirstAddress.RowNumber,RangeAddress.FirstAddress.ColumnNumber, RangeAddress.FirstAddress.FixedRow, RangeAddress.FirstAddress.FixedColumn), + new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber + numberOfColumns, RangeAddress.LastAddress.FixedRow, RangeAddress.LastAddress.FixedColumn)); } + return retVal; } - public void InsertColumnsAfter(Boolean onlyUsedCells, Int32 numberOfColumns) + public IXLRangeColumns InsertColumnsAfter(Boolean onlyUsedCells, Int32 numberOfColumns) { var columnCount = this.ColumnCount(); var firstColumn = this.RangeAddress.FirstAddress.ColumnNumber + columnCount; @@ -303,27 +293,35 @@ if (lastRow > XLWorksheet.MaxNumberOfRows) lastRow = XLWorksheet.MaxNumberOfRows; var newRange = (XLRange)this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); - newRange.InsertColumnsBefore(onlyUsedCells, numberOfColumns); + return newRange.InsertColumnsBefore(onlyUsedCells, numberOfColumns); } - public void InsertColumnsBefore(Int32 numberOfColumns) + public IXLRangeColumns InsertColumnsBefore(Int32 numberOfColumns) { - InsertColumnsBefore(numberOfColumns, false); + return InsertColumnsBefore(numberOfColumns, false); } - public void InsertColumnsBefore(Int32 numberOfColumns, Boolean expandRange) + public IXLRangeColumns InsertColumnsBefore(Int32 numberOfColumns, Boolean expandRange) { - this.InsertColumnsBefore(false, numberOfColumns); + var retVal = this.InsertColumnsBefore(false, numberOfColumns); // Adjust the range if (expandRange) { this.RangeAddress = new XLRangeAddress( - this.RangeAddress.FirstAddress.RowNumber, - this.RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, - this.RangeAddress.LastAddress.RowNumber, - this.RangeAddress.LastAddress.ColumnNumber); + new XLAddress(RangeAddress.FirstAddress.RowNumber,RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, RangeAddress.FirstAddress.FixedRow, RangeAddress.FirstAddress.FixedColumn), + new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber, RangeAddress.LastAddress.FixedRow, RangeAddress.LastAddress.FixedColumn)); } + return retVal; } - public void InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns) + public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns) { + foreach (var ws in Worksheet.Internals.Workbook.Worksheets) + { + var xlWorksheet = (XLWorksheet)ws; + foreach (var cell in xlWorksheet.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) + { + cell.ShiftFormulaColumns((XLRange)this.AsRange(), numberOfColumns); + } + } + var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); var cellsToBlank = new List(); @@ -339,9 +337,9 @@ { for (var ro = lastRow; ro >= firstRow; ro--) { - var oldKey = new XLAddress(ro, co); + var oldKey = new XLAddress(ro, co, false, false); var newColumn = co + numberOfColumns; - var newKey = new XLAddress(ro, newColumn); + var newKey = new XLAddress(ro, newColumn, false, false); IXLCell oldCell; if (this.Worksheet.Internals.CellsCollection.ContainsKey(oldKey)) { @@ -370,7 +368,7 @@ )) { var newColumn = c.Key.ColumnNumber + numberOfColumns; - var newKey = new XLAddress(c.Key.RowNumber, newColumn); + var newKey = new XLAddress(c.Key.RowNumber, newColumn, false, false); var newCell = new XLCell(newKey, c.Value.Style, Worksheet); newCell.CopyValues(c.Value); cellsToInsert.Add(newKey, newCell); @@ -392,27 +390,31 @@ } Worksheet.NotifyRangeShiftedColumns((XLRange)this.AsRange(), numberOfColumns); - + return Worksheet.Range( + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, + RangeAddress.LastAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber - numberOfColumns + ).Columns(); } - public void InsertRowsBelow(Int32 numberOfRows) + public IXLRangeRows InsertRowsBelow(Int32 numberOfRows) { - InsertRowsBelow(numberOfRows, true); + return InsertRowsBelow(numberOfRows, true); } - public void InsertRowsBelow(Int32 numberOfRows, Boolean expandRange) + public IXLRangeRows InsertRowsBelow(Int32 numberOfRows, Boolean expandRange) { - this.InsertRowsBelow(false,numberOfRows); + var retVal = this.InsertRowsBelow(false,numberOfRows); // Adjust the range if (expandRange) { this.RangeAddress = new XLRangeAddress( - this.RangeAddress.FirstAddress.RowNumber, - this.RangeAddress.FirstAddress.ColumnNumber, - this.RangeAddress.LastAddress.RowNumber + numberOfRows, - this.RangeAddress.LastAddress.ColumnNumber); + new XLAddress(RangeAddress.FirstAddress.RowNumber,RangeAddress.FirstAddress.ColumnNumber, RangeAddress.FirstAddress.FixedRow, RangeAddress.FirstAddress.FixedColumn), + new XLAddress(RangeAddress.LastAddress.RowNumber + numberOfRows, RangeAddress.LastAddress.ColumnNumber, RangeAddress.LastAddress.FixedRow, RangeAddress.LastAddress.FixedColumn)); } + return retVal; } - public void InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows) + public IXLRangeRows InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows) { var rowCount = this.RowCount(); var firstRow = this.RangeAddress.FirstAddress.RowNumber + rowCount; @@ -425,27 +427,35 @@ if (lastColumn > XLWorksheet.MaxNumberOfColumns) lastColumn = XLWorksheet.MaxNumberOfColumns; var newRange = (XLRange)this.Worksheet.Range(firstRow, firstColumn, lastRow, lastColumn); - newRange.InsertRowsAbove(onlyUsedCells, numberOfRows); + return newRange.InsertRowsAbove(onlyUsedCells, numberOfRows); } - public void InsertRowsAbove(Int32 numberOfRows) + public IXLRangeRows InsertRowsAbove(Int32 numberOfRows) { - InsertRowsAbove(numberOfRows, false); + return InsertRowsAbove(numberOfRows, false); } - public void InsertRowsAbove(Int32 numberOfRows, Boolean expandRange) + public IXLRangeRows InsertRowsAbove(Int32 numberOfRows, Boolean expandRange) { - this.InsertRowsAbove(false, numberOfRows); + var retVal = this.InsertRowsAbove(false, numberOfRows); // Adjust the range if (expandRange) { this.RangeAddress = new XLRangeAddress( - this.RangeAddress.FirstAddress.RowNumber - numberOfRows, - this.RangeAddress.FirstAddress.ColumnNumber, - this.RangeAddress.LastAddress.RowNumber, - this.RangeAddress.LastAddress.ColumnNumber); + new XLAddress(RangeAddress.FirstAddress.RowNumber - numberOfRows,RangeAddress.FirstAddress.ColumnNumber, RangeAddress.FirstAddress.FixedRow, RangeAddress.FirstAddress.FixedColumn), + new XLAddress(RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber, RangeAddress.LastAddress.FixedRow, RangeAddress.LastAddress.FixedColumn)); } + return retVal; } - public void InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows ) + public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows ) { + foreach (var ws in Worksheet.Internals.Workbook.Worksheets) + { + var xlWorksheet = (XLWorksheet)ws; + foreach (var cell in xlWorksheet.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) + { + cell.ShiftFormulaRows((XLRange)this.AsRange(), numberOfRows); + } + } + var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); var cellsToBlank = new List(); @@ -461,9 +471,9 @@ { for (var co = lastColumn; co >= firstColumn; co--) { - var oldKey = new XLAddress(ro, co); + var oldKey = new XLAddress(ro, co, false, false); var newRow = ro + numberOfRows; - var newKey = new XLAddress(newRow, co); + var newKey = new XLAddress(newRow, co, false, false); IXLCell oldCell; if (this.Worksheet.Internals.CellsCollection.ContainsKey(oldKey)) { @@ -492,7 +502,7 @@ )) { var newRow = c.Key.RowNumber + numberOfRows; - var newKey = new XLAddress(newRow, c.Key.ColumnNumber); + var newKey = new XLAddress(newRow, c.Key.ColumnNumber, false, false); var newCell = new XLCell(newKey, c.Value.Style, Worksheet); newCell.CopyValues(c.Value); cellsToInsert.Add(newKey, newCell); @@ -513,6 +523,12 @@ this.Worksheet.Cell(c).Style = styleToUse; } Worksheet.NotifyRangeShiftedRows((XLRange)this.AsRange(), numberOfRows); + return Worksheet.Range( + RangeAddress.FirstAddress.RowNumber - numberOfRows, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.LastAddress.RowNumber - numberOfRows, + RangeAddress.LastAddress.ColumnNumber + ).Rows(); } public void Clear() @@ -612,7 +628,35 @@ public void Delete(XLShiftDeletedCells shiftDeleteCells) { - //this.Clear(); + var numberOfRows = this.RowCount(); + var numberOfColumns = this.ColumnCount(); + IXLRange shiftedRangeFormula; + if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) + { + var lastCell = Worksheet.Cell(XLWorksheet.MaxNumberOfRows, RangeAddress.LastAddress.ColumnNumber); + shiftedRangeFormula = Worksheet.Range(RangeAddress.FirstAddress, lastCell.Address); + if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) + Worksheet.Internals.CellsCollection.Remove(lastCell.Address); + } + else + { + var lastCell = Worksheet.Cell(RangeAddress.LastAddress.RowNumber, XLWorksheet.MaxNumberOfColumns); + shiftedRangeFormula = Worksheet.Range(RangeAddress.FirstAddress, lastCell.Address); + if (StringExtensions.IsNullOrWhiteSpace(lastCell.GetString()) && StringExtensions.IsNullOrWhiteSpace(lastCell.FormulaA1)) + Worksheet.Internals.CellsCollection.Remove(lastCell.Address); + } + + foreach (var ws in Worksheet.Internals.Workbook.Worksheets) + { + var xlWorksheet = (XLWorksheet)ws; + foreach (var cell in xlWorksheet.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) + { + if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) + cell.ShiftFormulaRows((XLRange)shiftedRangeFormula, numberOfRows * -1); + else + cell.ShiftFormulaColumns((XLRange)shiftedRangeFormula, numberOfColumns * -1); + } + } // Range to shift... var cellsToInsert = new Dictionary(); @@ -634,10 +678,10 @@ var cellsQuery = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? shiftLeftQuery : shiftUpQuery; foreach (var c in cellsQuery) { - var newKey = new XLAddress(c.Key.RowNumber - rowModifier, c.Key.ColumnNumber - columnModifier); + var newKey = new XLAddress(c.Key.RowNumber - rowModifier, c.Key.ColumnNumber - columnModifier, false, false); var newCell = new XLCell(newKey, c.Value.Style, Worksheet); newCell.CopyValues(c.Value); - newCell.ShiftFormula(rowModifier * -1, columnModifier * -1); + //newCell.ShiftFormula(rowModifier * -1, columnModifier * -1); cellsToDelete.Add(c.Key); var canInsert = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? @@ -721,20 +765,7 @@ public override string ToString() { - var sb = new StringBuilder(); - sb.Append("'"); - sb.Append(Worksheet.Name); - sb.Append("'!"); - var firstAddress = new XLAddress(RangeAddress.FirstAddress.ToString()); - firstAddress.FixedColumn = true; - firstAddress.FixedRow = true; - sb.Append(firstAddress.ToString()); - sb.Append(":"); - var lastAddress = new XLAddress(RangeAddress.LastAddress.ToString()); - lastAddress.FixedColumn = true; - lastAddress.FixedRow = true; - sb.Append(lastAddress.ToString()); - return sb.ToString(); + return String.Format("'{0}'!{1}:{2}", Worksheet.Name, RangeAddress.FirstAddress.ToString(), RangeAddress.LastAddress.ToString()); } public String FormulaA1 @@ -814,10 +845,16 @@ (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.FirstAddress.ColumnNumber && columnsShifted > 0) || (shiftedRange.RangeAddress.FirstAddress.ColumnNumber < thisRangeAddress.FirstAddress.ColumnNumber && columnsShifted < 0) ) - thisRangeAddress.FirstAddress = new XLAddress(thisRangeAddress.FirstAddress.RowNumber, thisRangeAddress.FirstAddress.ColumnNumber + columnsShifted); + thisRangeAddress.FirstAddress = new XLAddress( + thisRangeAddress.FirstAddress.RowNumber, + thisRangeAddress.FirstAddress.ColumnNumber + columnsShifted, + thisRangeAddress.FirstAddress.FixedRow, thisRangeAddress.FirstAddress.FixedColumn); if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= thisRangeAddress.LastAddress.ColumnNumber) - thisRangeAddress.LastAddress = new XLAddress(thisRangeAddress.LastAddress.RowNumber, thisRangeAddress.LastAddress.ColumnNumber + columnsShifted); + thisRangeAddress.LastAddress = new XLAddress( + thisRangeAddress.LastAddress.RowNumber, + thisRangeAddress.LastAddress.ColumnNumber + columnsShifted, + thisRangeAddress.LastAddress.FixedRow, thisRangeAddress.LastAddress.FixedColumn); } } } @@ -853,10 +890,16 @@ (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.FirstAddress.RowNumber && rowsShifted > 0) || (shiftedRange.RangeAddress.FirstAddress.RowNumber < thisRangeAddress.FirstAddress.RowNumber && rowsShifted < 0) ) - thisRangeAddress.FirstAddress = new XLAddress(thisRangeAddress.FirstAddress.RowNumber + rowsShifted, thisRangeAddress.FirstAddress.ColumnNumber); + thisRangeAddress.FirstAddress = new XLAddress( + thisRangeAddress.FirstAddress.RowNumber + rowsShifted, + thisRangeAddress.FirstAddress.ColumnNumber, + thisRangeAddress.FirstAddress.FixedRow, thisRangeAddress.FirstAddress.FixedColumn); if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= thisRangeAddress.LastAddress.RowNumber) - thisRangeAddress.LastAddress = new XLAddress(thisRangeAddress.LastAddress.RowNumber + rowsShifted, thisRangeAddress.LastAddress.ColumnNumber); + thisRangeAddress.LastAddress = new XLAddress( + thisRangeAddress.LastAddress.RowNumber + rowsShifted, + thisRangeAddress.LastAddress.ColumnNumber, + thisRangeAddress.LastAddress.FixedRow, thisRangeAddress.LastAddress.FixedColumn); } } } @@ -936,6 +979,12 @@ } } + public IXLRangeBase SetValue(T value) + { + Cells().ForEach(c => c.SetValue(value)); + return this; + } + public XLCellValues DataType { set diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index cb3c023..7e31636 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -77,22 +77,22 @@ { Delete(XLShiftDeletedCells.ShiftCellsLeft); } - public void InsertCellsAbove(int numberOfRows) + public IXLCells InsertCellsAbove(int numberOfRows) { - InsertCellsAbove(numberOfRows, false); + return InsertCellsAbove(numberOfRows, false); } - public void InsertCellsAbove(int numberOfRows, Boolean expandRange) + public IXLCells InsertCellsAbove(int numberOfRows, Boolean expandRange) { - InsertRowsAbove(numberOfRows, expandRange); + return InsertRowsAbove(numberOfRows, expandRange).Cells(); } - public void InsertCellsBelow(int numberOfRows) + public IXLCells InsertCellsBelow(int numberOfRows) { - InsertCellsBelow(numberOfRows, true); + return InsertCellsBelow(numberOfRows, true); } - public void InsertCellsBelow(int numberOfRows, Boolean expandRange) + public IXLCells InsertCellsBelow(int numberOfRows, Boolean expandRange) { - InsertRowsBelow(numberOfRows, expandRange); + return InsertRowsBelow(numberOfRows, expandRange).Cells(); } public Int32 CellCount() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index ab4a559..ddd5c26 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -46,22 +46,22 @@ Delete(XLShiftDeletedCells.ShiftCellsUp); } - public void InsertCellsAfter(int numberOfColumns) + public IXLCells InsertCellsAfter(int numberOfColumns) { - InsertCellsAfter(numberOfColumns, true); + return InsertCellsAfter(numberOfColumns, true); } - public void InsertCellsAfter(int numberOfColumns, Boolean expandRange) + public IXLCells InsertCellsAfter(int numberOfColumns, Boolean expandRange) { - InsertColumnsAfter(numberOfColumns, expandRange); + return InsertColumnsAfter(numberOfColumns, expandRange).Cells(); } - public void InsertCellsBefore(int numberOfColumns) + public IXLCells InsertCellsBefore(int numberOfColumns) { - InsertCellsBefore(numberOfColumns, false); + return InsertCellsBefore(numberOfColumns, false); } - public void InsertCellsBefore(int numberOfColumns, Boolean expandRange) + public IXLCells InsertCellsBefore(int numberOfColumns, Boolean expandRange) { - InsertColumnsBefore(numberOfColumns, expandRange); + return InsertColumnsBefore(numberOfColumns, expandRange).Cells(); } public IXLCells Cells(String cellsInRow) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index b7eeb39..f92f72c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -187,5 +187,33 @@ return dataValidation; } } + + public IXLRanges AddToNamed(String rangeName) + { + return AddToNamed(rangeName, XLScope.Workbook); + } + public IXLRanges AddToNamed(String rangeName, XLScope scope) + { + return AddToNamed(rangeName, XLScope.Workbook, null); + } + public IXLRanges AddToNamed(String rangeName, XLScope scope, String comment) + { + ranges.ForEach(r => r.AddToNamed(rangeName, scope, comment)); + return this; + } + + public Object Value + { + set + { + ranges.ForEach(r => r.Value = value); + } + } + + public IXLRanges SetValue(T value) + { + ranges.ForEach(r => r.SetValue(value)); + return this; + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs index d22723c..7e1b2df 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs @@ -30,26 +30,26 @@ /// All rows below will be shifted accordingly. /// /// The number of rows to insert. - void InsertRowsBelow(Int32 numberOfRows); + IXLRows InsertRowsBelow(Int32 numberOfRows); /// /// Inserts X number of rows above this one. /// This row and all below will be shifted accordingly. /// /// The number of rows to insert. - void InsertRowsAbove(Int32 numberOfRows); + IXLRows InsertRowsAbove(Int32 numberOfRows); /// /// Adjusts the height of the row based on its contents, starting from the startColumn. /// /// The column to start calculating the row height. - void AdjustToContents(Int32 startColumn); + IXLRow AdjustToContents(Int32 startColumn); /// /// Adjusts the height of the row based on its contents, starting from the startColumn and ending at endColumn. /// /// The column to start calculating the row height. /// The column to end calculating the row height. - void AdjustToContents(Int32 startColumn, Int32 endColumn); + IXLRow AdjustToContents(Int32 startColumn, Int32 endColumn); /// Hides this row. void Hide(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs index a8d3233..1cc3f03 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs @@ -23,18 +23,18 @@ /// /// Adjusts the height of all rows based on its contents. /// - void AdjustToContents(); + IXLRows AdjustToContents(); /// /// Adjusts the height of all rows based on its contents, starting from the startColumn. /// /// The column to start calculating the row height. - void AdjustToContents(Int32 startColumn); + IXLRows AdjustToContents(Int32 startColumn); /// /// Adjusts the height of all rows based on its contents, starting from the startColumn and ending at endColumn. /// /// The column to start calculating the row height. /// The column to end calculating the row height. - void AdjustToContents(Int32 startColumn, Int32 endColumn); + IXLRows AdjustToContents(Int32 startColumn, Int32 endColumn); /// /// Hides all rows. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index cd7eabc..d1b3f8e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -9,7 +9,7 @@ internal class XLRow: XLRangeBase, IXLRow { public XLRow(Int32 row, XLRowParameters xlRowParameters) - : base(new XLRangeAddress(row, 1, row, XLWorksheet.MaxNumberOfColumns)) + : base(new XLRangeAddress(new XLAddress(row, 1, false, false), new XLAddress(row, XLWorksheet.MaxNumberOfColumns, false, false))) { SetRowNumber(row); Worksheet = xlRowParameters.Worksheet; @@ -46,8 +46,8 @@ } else { - RangeAddress.FirstAddress = new XLAddress(row, 1); - RangeAddress.LastAddress = new XLAddress(row, XLWorksheet.MaxNumberOfColumns); + RangeAddress.FirstAddress = new XLAddress(row, 1, RangeAddress.FirstAddress.FixedRow, RangeAddress.FirstAddress.FixedColumn); + RangeAddress.LastAddress = new XLAddress(row, XLWorksheet.MaxNumberOfColumns, RangeAddress.LastAddress.FixedRow, RangeAddress.LastAddress.FixedColumn); } } @@ -97,15 +97,16 @@ } - public new void InsertRowsBelow(Int32 numberOfRows) + public new IXLRows InsertRowsBelow(Int32 numberOfRows) { var rowNum = this.RowNumber(); this.Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum + 1, numberOfRows); XLRange range = (XLRange)this.Worksheet.Row(rowNum).AsRange(); range.InsertRowsBelow(true, numberOfRows); + return Worksheet.Rows(rowNum + 1, rowNum + numberOfRows); } - public new void InsertRowsAbove(Int32 numberOfRows) + public new IXLRows InsertRowsAbove(Int32 numberOfRows) { var rowNum = this.RowNumber(); this.Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum, numberOfRows); @@ -113,6 +114,7 @@ // and we want to use the old rowNum. XLRange range = (XLRange)this.Worksheet.Row(rowNum).AsRange(); range.InsertRowsAbove(true, numberOfRows); + return Worksheet.Rows(rowNum, rowNum + numberOfRows - 1); } public new void Clear() @@ -174,15 +176,15 @@ return Cells(XLAddress.GetColumnNumberFromLetter(firstColumn) + ":" + XLAddress.GetColumnNumberFromLetter(lastColumn)); } - public void AdjustToContents() + public IXLRow AdjustToContents() { - AdjustToContents(1); + return AdjustToContents(1); } - public void AdjustToContents(Int32 startColumn) + public IXLRow AdjustToContents(Int32 startColumn) { - AdjustToContents(startColumn, XLWorksheet.MaxNumberOfColumns); + return AdjustToContents(startColumn, XLWorksheet.MaxNumberOfColumns); } - public void AdjustToContents(Int32 startColumn, Int32 endColumn) + public IXLRow AdjustToContents(Int32 startColumn, Int32 endColumn) { Double maxHeight = 0; foreach (var c in CellsUsed().Where(cell => cell.Address.ColumnNumber >= startColumn && cell.Address.ColumnNumber <= endColumn)) @@ -209,6 +211,7 @@ maxHeight = Worksheet.RowHeight; Height = maxHeight; + return this; } public void Hide() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index df5d39a..fa55ed6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -151,17 +151,20 @@ rows.Add(row); } - public void AdjustToContents() + public IXLRows AdjustToContents() { rows.ForEach(r => r.AdjustToContents()); + return this; } - public void AdjustToContents(Int32 startColumn) + public IXLRows AdjustToContents(Int32 startColumn) { rows.ForEach(r => r.AdjustToContents(startColumn)); + return this; } - public void AdjustToContents(Int32 startColumn, Int32 endColumn) + public IXLRows AdjustToContents(Int32 startColumn, Int32 endColumn) { rows.ForEach(r => r.AdjustToContents(startColumn, endColumn)); + return this; } public void Hide() { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLAlignment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLAlignment.cs index d9d67e5..98a0b13 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLAlignment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLAlignment.cs @@ -78,5 +78,17 @@ /// (as opposed to the normal left to right). /// Boolean TopToBottom { get; set; } + + IXLStyle SetHorizontal(XLAlignmentHorizontalValues value); + IXLStyle SetVertical(XLAlignmentVerticalValues value); + IXLStyle SetIndent(Int32 value); + IXLStyle SetJustifyLastLine(); IXLStyle SetJustifyLastLine(Boolean value); + IXLStyle SetReadingOrder(XLAlignmentReadingOrderValues value); + IXLStyle SetRelativeIndent(Int32 value); + IXLStyle SetShrinkToFit(); IXLStyle SetShrinkToFit(Boolean value); + IXLStyle SetTextRotation(Int32 value); + IXLStyle SetWrapText(); IXLStyle SetWrapText(Boolean value); + IXLStyle SetTopToBottom(); IXLStyle SetTopToBottom(Boolean value); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs index ff980ff..fddbf2f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs @@ -26,27 +26,30 @@ public interface IXLBorder: IEquatable { XLBorderStyleValues LeftBorder { get; set; } - IXLColor LeftBorderColor { get; set; } - XLBorderStyleValues RightBorder { get; set; } - IXLColor RightBorderColor { get; set; } - XLBorderStyleValues TopBorder { get; set; } - IXLColor TopBorderColor { get; set; } - XLBorderStyleValues BottomBorder { get; set; } - IXLColor BottomBorderColor { get; set; } - Boolean DiagonalUp { get; set; } - Boolean DiagonalDown { get; set; } - XLBorderStyleValues DiagonalBorder { get; set; } - IXLColor DiagonalBorderColor { get; set; } + + IXLStyle SetLeftBorder(XLBorderStyleValues value); + IXLStyle SetLeftBorderColor(IXLColor value); + IXLStyle SetRightBorder(XLBorderStyleValues value); + IXLStyle SetRightBorderColor(IXLColor value); + IXLStyle SetTopBorder(XLBorderStyleValues value); + IXLStyle SetTopBorderColor(IXLColor value); + IXLStyle SetBottomBorder(XLBorderStyleValues value); + IXLStyle SetBottomBorderColor(IXLColor value); + IXLStyle SetDiagonalUp(); IXLStyle SetDiagonalUp(Boolean value); + IXLStyle SetDiagonalDown(); IXLStyle SetDiagonalDown(Boolean value); + IXLStyle SetDiagonalBorder(XLBorderStyleValues value); + IXLStyle SetDiagonalBorderColor(IXLColor value); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFill.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFill.cs index 402c1c5..8ec1e60 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFill.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFill.cs @@ -32,11 +32,14 @@ public interface IXLFill:IEquatable { IXLColor BackgroundColor { get; set; } - IXLColor PatternColor { get; set; } - IXLColor PatternBackgroundColor { get; set; } - XLFillPatternValues PatternType { get; set; } + + IXLStyle SetBackgroundColor(IXLColor value); + IXLStyle SetPatternColor(IXLColor value); + IXLStyle SetPatternBackgroundColor(IXLColor value); + IXLStyle SetPatternType(XLFillPatternValues value); + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFont.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFont.cs index e0b0890..5e14140 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLFont.cs @@ -32,7 +32,7 @@ Decorative = 5 } - public interface IXLFont: IEquatable + public interface IXLFont : IEquatable { Boolean Bold { get; set; } Boolean Italic { get; set; } @@ -44,5 +44,18 @@ IXLColor FontColor { get; set; } String FontName { get; set; } XLFontFamilyNumberingValues FontFamilyNumbering { get; set; } + + IXLStyle SetBold(); IXLStyle SetBold(Boolean value); + IXLStyle SetItalic(); IXLStyle SetItalic(Boolean value); + IXLStyle SetUnderline(); IXLStyle SetUnderline(XLFontUnderlineValues value); + IXLStyle SetStrikethrough(); IXLStyle SetStrikethrough(Boolean value); + IXLStyle SetVerticalAlignment(XLFontVerticalTextAlignmentValues value); + IXLStyle SetShadow(); IXLStyle SetShadow(Boolean value); + IXLStyle SetFontSize(Double value); + IXLStyle SetFontColor(IXLColor value); + IXLStyle SetFontName(String value); + IXLStyle SetFontFamilyNumbering(XLFontFamilyNumberingValues value); + + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLNumberFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLNumberFormat.cs index e3401c5..aacf87e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLNumberFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLNumberFormat.cs @@ -9,5 +9,7 @@ { Int32 NumberFormatId { get; set; } String Format { get; set; } + IXLStyle SetNumberFormatId(Int32 value); + IXLStyle SetFormat(String value); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs index 98cc8c5..3afb559 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLAlignment.cs @@ -276,5 +276,17 @@ ; } + 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; } + + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs index 893783e..98aa7cd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs @@ -290,5 +290,19 @@ ^ DiagonalUp.GetHashCode() ^ DiagonalDown.GetHashCode(); } + + public IXLStyle SetLeftBorder(XLBorderStyleValues value) { LeftBorder = value; return container.Style; } + public IXLStyle SetLeftBorderColor(IXLColor value) { LeftBorderColor = value; return container.Style; } + public IXLStyle SetRightBorder(XLBorderStyleValues value) { RightBorder = value; return container.Style; } + public IXLStyle SetRightBorderColor(IXLColor value) { RightBorderColor = value; return container.Style; } + public IXLStyle SetTopBorder(XLBorderStyleValues value) { TopBorder = value; return container.Style; } + public IXLStyle SetTopBorderColor(IXLColor value) { TopBorderColor = value; return container.Style; } + public IXLStyle SetBottomBorder(XLBorderStyleValues value) { BottomBorder = value; return container.Style; } + public IXLStyle SetBottomBorderColor(IXLColor value) { BottomBorderColor = value; return container.Style; } + public IXLStyle SetDiagonalUp() { DiagonalUp = true; return container.Style; } public IXLStyle SetDiagonalUp(Boolean value) { DiagonalUp = value; return container.Style; } + public IXLStyle SetDiagonalDown() { DiagonalDown = true; return container.Style; } public IXLStyle SetDiagonalDown(Boolean value) { DiagonalDown = value; return container.Style; } + public IXLStyle SetDiagonalBorder(XLBorderStyleValues value) { DiagonalBorder = value; return container.Style; } + public IXLStyle SetDiagonalBorderColor(IXLColor value) { DiagonalBorderColor = value; return container.Style; } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs index 5062393..124475e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFill.cs @@ -79,6 +79,12 @@ } } + public IXLStyle SetBackgroundColor(IXLColor value) { BackgroundColor = value; return container.Style; } + public IXLStyle SetPatternColor(IXLColor value) { PatternColor = value; return container.Style; } + public IXLStyle SetPatternBackgroundColor(IXLColor value) { PatternBackgroundColor = value; return container.Style; } + public IXLStyle SetPatternType(XLFillPatternValues value) { PatternType = value; return container.Style; } + + #endregion #region Constructors diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs index d0e2e12..96b5fda 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLFont.cs @@ -220,6 +220,18 @@ } } + public IXLStyle SetBold() { Bold = true; return container.Style; } public IXLStyle SetBold(Boolean value) { Bold = value; return container.Style; } + public IXLStyle SetItalic() { Italic = true; return container.Style; } public IXLStyle SetItalic(Boolean value) { Italic = value; return container.Style; } + public IXLStyle SetUnderline() { Underline = XLFontUnderlineValues.Single; return container.Style; } public IXLStyle SetUnderline(XLFontUnderlineValues value) { Underline = value; return container.Style; } + public IXLStyle SetStrikethrough() { Strikethrough = true; return container.Style; } public IXLStyle SetStrikethrough(Boolean value) { Strikethrough = value; return container.Style; } + public IXLStyle SetVerticalAlignment(XLFontVerticalTextAlignmentValues value) { VerticalAlignment = value; return container.Style; } + public IXLStyle SetShadow() { Shadow = true; return container.Style; } public IXLStyle SetShadow(Boolean value) { Shadow = value; return container.Style; } + public IXLStyle SetFontSize(Double value) { FontSize = value; return container.Style; } + public IXLStyle SetFontColor(IXLColor value) { FontColor = value; return container.Style; } + public IXLStyle SetFontName(String value) { FontName = value; return container.Style; } + public IXLStyle SetFontFamilyNumbering(XLFontFamilyNumberingValues value) { FontFamilyNumbering = value; return container.Style; } + + #endregion public Double GetWidth(String text) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs index b20a900..de38a6e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLNumberFormat.cs @@ -47,6 +47,9 @@ } } + public IXLStyle SetNumberFormatId(Int32 value) { NumberFormatId = value; return container.Style; } + public IXLStyle SetFormat(String value) { Format = value; return container.Style; } + #endregion #region Constructors diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs index 03b31d0..c774429 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -9,20 +9,19 @@ { internal struct XLAddress: IXLAddress { - private static Regex a1Regex = new Regex(@"^(\$?[a-zA-Z]{1,3})(\$?\d+)$"); #region Constructors /// /// Initializes a new struct using R1C1 notation. /// /// The row number of the cell address. /// The column number of the cell address. - public XLAddress(Int32 rowNumber, Int32 columnNumber) + public XLAddress(Int32 rowNumber, Int32 columnNumber, Boolean fixedRow, Boolean fixedColumn) { this.rowNumber = rowNumber; this.columnNumber = columnNumber; this.columnLetter = null; - fixedColumn = false; - fixedRow = false; + this.fixedColumn = fixedColumn; + this.fixedRow = fixedRow; } /// @@ -30,13 +29,13 @@ /// /// The row number of the cell address. /// The column letter of the cell address. - public XLAddress(Int32 rowNumber, String columnLetter) + public XLAddress(Int32 rowNumber, String columnLetter, Boolean fixedRow, Boolean fixedColumn) { this.rowNumber = rowNumber; this.columnNumber = 0; this.columnLetter = columnLetter; - fixedColumn = false; - fixedRow = false; + this.fixedColumn = fixedColumn; + this.fixedRow = fixedRow; } @@ -61,12 +60,20 @@ if (fixedRow) { - columnLetter = cellAddressString.Substring(startPos, rowPos - 1); + if (fixedColumn) + columnLetter = cellAddressString.Substring(startPos, rowPos - 1); + else + columnLetter = cellAddressString.Substring(startPos, rowPos); + rowNumber = Int32.Parse(cellAddressString.Substring(rowPos + 1), nfi); } else { - columnLetter = cellAddressString.Substring(startPos, rowPos); + if (fixedColumn) + columnLetter = cellAddressString.Substring(startPos, rowPos - 1); + else + columnLetter = cellAddressString.Substring(startPos, rowPos); + rowNumber = Int32.Parse(cellAddressString.Substring(rowPos), nfi); } @@ -76,6 +83,7 @@ #endregion #region Static + private static NumberFormatInfo nfi = CultureInfo.InvariantCulture.NumberFormat; private static readonly Int32 twoT26 = 26 * 26; /// /// Gets the column number of a given column letter. @@ -147,11 +155,12 @@ { address = address.Replace("$", ""); Int32 rowPos = 0; - while (rowPos < address.Length && (address[rowPos] > '9' || address[rowPos] < '0')) + Int32 addressLength = address.Length; + while (rowPos < addressLength && (address[rowPos] > '9' || address[rowPos] < '0')) rowPos++; return - rowPos < address.Length + rowPos < addressLength && IsValidRow(address.Substring(rowPos)) && IsValidColumn(address.Substring(0, rowPos)); } @@ -306,7 +315,6 @@ #endregion #region Overrides - private static NumberFormatInfo nfi = CultureInfo.InvariantCulture.NumberFormat; public override string ToString() { //var sb = new StringBuilder(); @@ -337,22 +345,26 @@ public static XLAddress operator +(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) { - return new XLAddress(xlCellAddressLeft.RowNumber + xlCellAddressRight.RowNumber, xlCellAddressLeft.ColumnNumber + xlCellAddressRight.ColumnNumber); + return new XLAddress(xlCellAddressLeft.RowNumber + xlCellAddressRight.RowNumber, xlCellAddressLeft.ColumnNumber + xlCellAddressRight.ColumnNumber, + xlCellAddressLeft.fixedRow, xlCellAddressLeft.fixedColumn); } public static XLAddress operator -(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) { - return new XLAddress(xlCellAddressLeft.RowNumber - xlCellAddressRight.RowNumber, xlCellAddressLeft.ColumnNumber - xlCellAddressRight.ColumnNumber); + return new XLAddress(xlCellAddressLeft.RowNumber - xlCellAddressRight.RowNumber, xlCellAddressLeft.ColumnNumber - xlCellAddressRight.ColumnNumber, + xlCellAddressLeft.fixedRow, xlCellAddressLeft.fixedColumn); } public static XLAddress operator +(XLAddress xlCellAddressLeft, Int32 right) { - return new XLAddress(xlCellAddressLeft.RowNumber + right, xlCellAddressLeft.ColumnNumber + right); + return new XLAddress(xlCellAddressLeft.RowNumber + right, xlCellAddressLeft.ColumnNumber + right, + xlCellAddressLeft.fixedRow, xlCellAddressLeft.fixedColumn); } public static XLAddress operator -(XLAddress xlCellAddressLeft, Int32 right) { - return new XLAddress(xlCellAddressLeft.RowNumber - right, xlCellAddressLeft.ColumnNumber - right); + return new XLAddress(xlCellAddressLeft.RowNumber - right, xlCellAddressLeft.ColumnNumber - right, + xlCellAddressLeft.fixedRow, xlCellAddressLeft.fixedColumn); } public static Boolean operator ==(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index aa28c00..68bc0ca 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -216,7 +216,7 @@ { var dCell = (Cell)cell; Int32 styleIndex = dCell.StyleIndex != null ? Int32.Parse(dCell.StyleIndex.InnerText) : 0; - var xlCell = (XLCell)ws.CellFast(dCell.CellReference); + var xlCell = (XLCell)ws.Cell(dCell.CellReference); if (styleIndex > 0) { @@ -312,10 +312,20 @@ if (val >= 0 && val <= DateTimeExtensions.MaxOADate) { String format = xlCell.Style.NumberFormat.Format.EndsWith(";@") ? xlCell.Style.NumberFormat.Format.Substring(0, xlCell.Style.NumberFormat.Format.Length - 2) : xlCell.Style.NumberFormat.Format; - + Double dTest; - if (!Double.TryParse(val.ToString(format) , out dTest)) + if (!Double.TryParse(val.ToString(format), out dTest)) xlCell.DataType = XLCellValues.DateTime; + + ////String format = xlCell.Style.NumberFormat.Format.EndsWith(";@") ? xlCell.Style.NumberFormat.Format.Substring(0, xlCell.Style.NumberFormat.Format.Length - 2) : xlCell.Style.NumberFormat.Format; + + ////Double dTest; + ////if (!Double.TryParse(val.ToString(format) , out dTest)) + ////DateTime dTest; + ////if (DateTime.TryParseExact(DateTime.FromOADate(val).ToString(format), format, CultureInfo.InvariantCulture, DateTimeStyles.None , out dTest)) + ////if (DateTime.TryParse(val.ToString(format), out dTest)) + //if ((numberFormatId >= 14 && numberFormatId <= 22) || (numberFormatId >= 165 && numberFormatId <= 180)) + // xlCell.DataType = XLCellValues.DateTime; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 3d1eaad..1ed2f6f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -1547,7 +1547,7 @@ { maxColumn = (UInt32)xlWorksheet.Internals.CellsCollection.Select(c => c.Key.ColumnNumber).Max(); maxRow = (UInt32)xlWorksheet.Internals.CellsCollection.Select(c => c.Key.RowNumber).Max(); - sheetDimensionReference = "A1:" + new XLAddress((Int32)maxRow, (Int32)maxColumn).ToString(); + sheetDimensionReference = "A1:" + XLAddress.GetColumnLetterFromNumber((Int32)maxColumn) + ((Int32)maxRow).ToStringLookup(); } if (xlWorksheet.Internals.ColumnsCollection.Count > 0) @@ -2057,7 +2057,9 @@ { sequence += r.RangeAddress.ToString() + " "; } - sequence = sequence.Substring(0, sequence.Length - 1); + + if (sequence.Length > 0) + sequence = sequence.Substring(0, sequence.Length - 1); DataValidation dataValidation = new DataValidation() { @@ -2460,8 +2462,6 @@ } } - - private Double GetColumnWidth(Double columnWidth) { return columnWidth + 0.71; @@ -3329,7 +3329,7 @@ if (xlTable.ShowTotalsRow) autoFilter1.Reference = xlTable.RangeAddress.FirstAddress.ToString() + ":" + - new XLAddress(xlTable.RangeAddress.LastAddress.RowNumber - 1, xlTable.RangeAddress.LastAddress.ColumnNumber).ToString(); + XLAddress.GetColumnLetterFromNumber(xlTable.RangeAddress.LastAddress.ColumnNumber) + (xlTable.RangeAddress.LastAddress.RowNumber - 1).ToStringLookup(); else autoFilter1.Reference = reference; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index f8e182d..6fc060d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -23,7 +23,7 @@ private XLWorkbook workbook; public XLWorksheet(String sheetName, XLWorkbook workbook) - : base((IXLRangeAddress)new XLRangeAddress(new XLAddress(1, 1), new XLAddress(MaxNumberOfRows, MaxNumberOfColumns))) + : base((IXLRangeAddress)new XLRangeAddress(new XLAddress(1, 1, false, false), new XLAddress(MaxNumberOfRows, MaxNumberOfColumns, false, false))) { Worksheet = this; NamedRanges = new XLNamedRanges(workbook); @@ -404,7 +404,7 @@ var usedColumns = from c in this.Internals.ColumnsCollection join dc in distinctColumns on c.Key equals dc - where !this.Internals.CellsCollection.ContainsKey(new XLAddress(row, c.Key)) + where !this.Internals.CellsCollection.ContainsKey(new XLAddress(row, c.Key, false, false)) select c.Key; usedColumns.ForEach(c => Cell(row, c)); @@ -509,6 +509,10 @@ Internals.RowsCollection.Clear(); } public IXLNamedRanges NamedRanges { get; private set; } + public IXLNamedRange NamedRange(String rangeName) + { + return NamedRanges.NamedRange(rangeName); + } public IXLSheetView SheetView { get; private set; } public IXLTables Tables { get; private set; } public IXLTable Table(String name) diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj b/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj index 93791a3..65eea9b 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj @@ -94,10 +94,6 @@ {BD5E6BFE-E837-4A35-BCA9-39667D873A20} ClosedXML - - {03A518D0-1CB7-488E-861C-C4E782B27A46} - ClosedXML_Examples -