diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 51d7e4c..3b8ae88 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -1067,8 +1067,21 @@ return false; if (includeFormats) - return Style.Equals(Worksheet.Style) && !IsMerged() && !HasComment && !HasDataValidation; + { + if (!Style.Equals(Worksheet.Style) || IsMerged() || HasComment || HasDataValidation) + return false; + if (_style == null) + { + XLRow row; + if (Worksheet.Internals.RowsCollection.TryGetValue(Address.RowNumber, out row) && !row.Style.Equals(Worksheet.Style)) + return false; + + XLColumn column; + if (Worksheet.Internals.ColumnsCollection.TryGetValue(Address.ColumnNumber, out column) && !column.Style.Equals(Worksheet.Style)) + return false; + } + } return true; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs index c554986..047fb91 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs @@ -58,8 +58,7 @@ range.LastAddress.RowNumber, range.LastAddress.ColumnNumber) .Where(c => - (!c.IsEmpty(_includeFormats) - || (_includeFormats && c.HasComment) ) + !c.IsEmpty(_includeFormats) && (_predicate == null || _predicate(c)) ); @@ -124,8 +123,7 @@ cir => cir.Value.Select(a => cir.Key.Internals.CellsCollection.GetCell(a)).Where( cell => cell != null && ( - (!cell.IsEmpty(_includeFormats) - || (_includeFormats && cell.HasComment)) + !cell.IsEmpty(_includeFormats) && (_predicate == null || _predicate(cell)) ))); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index 54a86b7..aa8f2d8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -743,5 +743,18 @@ } #endregion + + public new Boolean IsEmpty() + { + return IsEmpty(false); + } + + public new Boolean IsEmpty(Boolean includeFormats) + { + if (includeFormats && !Style.Equals(Worksheet.Style)) + return false; + + return base.IsEmpty(includeFormats); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 82b008e..f3e044b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -290,9 +290,6 @@ var asRange = AsRange(); Worksheet.Internals.MergedRanges.Add(asRange); - // Call every cell in the merge to make sure they exist - asRange.Cells().ForEach(c => { }); - return asRange; } @@ -510,43 +507,66 @@ public XLCell FirstCellUsed(Boolean includeFormats, Func predicate) { - var sp = Worksheet.Internals.CellsCollection.FirstPointUsed( - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.LastAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber, - includeFormats, predicate); - - if(sp.Row > 0) - return Worksheet.Cell(sp.Row, sp.Column); + Int32 fRow = RangeAddress.FirstAddress.RowNumber; + Int32 lRow = RangeAddress.LastAddress.RowNumber; + Int32 fColumn = RangeAddress.FirstAddress.ColumnNumber; + Int32 lColumn = RangeAddress.LastAddress.ColumnNumber; if (includeFormats) { - Int32 ro = 0; - var rowsUsed = Worksheet.Internals.RowsCollection.Where(r => - r.Key >= RangeAddress.FirstAddress.RowNumber - && r.Key <= RangeAddress.LastAddress.RowNumber); + var rowsUsed = + Worksheet.Internals.RowsCollection.Where(r => r.Key >= fRow && r.Key <= lRow && !r.Value.IsEmpty(true)); + var columnsUsed = + Worksheet.Internals.ColumnsCollection.Where(c => c.Key >= fColumn && c.Key <= lColumn && !c.Value.IsEmpty(true)); + + // If there's a row or a column then check if the style is different + // and pick the first cell and check the style of it, if different + // than default then it's your cell. + + Int32 ro = 0; if (rowsUsed.Any()) ro = rowsUsed.First().Key; - - if (ro > 0) + Int32 co = 0; + if (columnsUsed.Any()) + co = columnsUsed.First().Key; + + if (ro > 0 && co > 0) + return Worksheet.Cell(ro, co); + + if (ro > 0 && lColumn < ExcelHelper.MaxColumnNumber) { - Int32 co = 0; - var columnsUsed = Worksheet.Internals.ColumnsCollection.Where(r => - r.Key >= RangeAddress.FirstAddress.ColumnNumber - && r.Key <= RangeAddress.LastAddress.ColumnNumber); - - if (columnsUsed.Any()) - ro = columnsUsed.First().Key; - - if (co > 0) - return Worksheet.Cell(ro, co); + for (co = fColumn; co <= lColumn; co++) + { + var cell = Worksheet.Cell(ro, co); + if (!cell.IsEmpty(true)) return cell; + } + } + else if (co > 0 && lRow < ExcelHelper.MaxRowNumber) + { + for (ro = fRow; ro <= lRow; ro++) + { + var cell = Worksheet.Cell(ro, co); + if (!cell.IsEmpty(true)) return cell; + } } + if (Worksheet.MergedRanges.Any(r => r.Intersects(this))) + { + Int32 minRo = + Worksheet.MergedRanges.Where(r => r.Intersects(this)).Min(r => r.RangeAddress.FirstAddress.RowNumber); + Int32 minCo = + Worksheet.MergedRanges.Where(r => r.Intersects(this)).Min(r => r.RangeAddress.FirstAddress.ColumnNumber); + + return Worksheet.Cell(minRo, minCo); + } } + var sp = Worksheet.Internals.CellsCollection.FirstPointUsed(fRow, fColumn, lRow, lColumn, includeFormats, predicate); + + if (sp.Row > 0) + return Worksheet.Cell(sp.Row, sp.Column); return null; } @@ -578,14 +598,68 @@ public XLCell LastCellUsed(Boolean includeFormats, Func predicate) { - var sp = Worksheet.Internals.CellsCollection.LastPointUsed( - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.LastAddress.RowNumber, - RangeAddress.LastAddress.ColumnNumber, - includeFormats, predicate); + Int32 fRow = RangeAddress.FirstAddress.RowNumber; + Int32 lRow = RangeAddress.LastAddress.RowNumber; + Int32 fColumn = RangeAddress.FirstAddress.ColumnNumber; + Int32 lColumn = RangeAddress.LastAddress.ColumnNumber; - return sp.Row == 0 ? null : Worksheet.Cell(sp.Row, sp.Column); + if (includeFormats) + { + var rowsUsed = + Worksheet.Internals.RowsCollection.Where(r => r.Key >= fRow && r.Key <= lRow && !r.Value.IsEmpty(true)); + + var columnsUsed = + Worksheet.Internals.ColumnsCollection.Where(c => c.Key >= fColumn && c.Key <= lColumn && !c.Value.IsEmpty(true)); + + // If there's a row or a column then check if the style is different + // and pick the first cell and check the style of it, if different + // than default then it's your cell. + + Int32 ro = 0; + if (rowsUsed.Any()) + ro = rowsUsed.Last().Key; + + Int32 co = 0; + if (columnsUsed.Any()) + co = columnsUsed.Last().Key; + + if (ro > 0 && co > 0) + return Worksheet.Cell(ro, co); + + if (ro > 0 && lColumn < ExcelHelper.MaxColumnNumber) + { + for (co = lColumn; co >= fColumn; co--) + { + var cell = Worksheet.Cell(ro, co); + if (!cell.IsEmpty(true)) return cell; + } + } + else if (co > 0 && lRow < ExcelHelper.MaxRowNumber) + { + for (ro = lRow; ro >= fRow; ro--) + { + var cell = Worksheet.Cell(ro, co); + if (!cell.IsEmpty(true)) return cell; + } + } + + if (Worksheet.MergedRanges.Any(r => r.Intersects(this))) + { + Int32 minRo = + Worksheet.MergedRanges.Where(r => r.Intersects(this)).Max(r => r.RangeAddress.LastAddress.RowNumber); + Int32 minCo = + Worksheet.MergedRanges.Where(r => r.Intersects(this)).Max(r => r.RangeAddress.LastAddress.ColumnNumber); + + return Worksheet.Cell(minRo, minCo); + } + } + + var sp = Worksheet.Internals.CellsCollection.LastPointUsed(fRow, fColumn, lRow, lColumn, includeFormats, predicate); + + if (sp.Row > 0) + return Worksheet.Cell(sp.Row, sp.Column); + + return null; } public XLCell Cell(Int32 row, Int32 column) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index 262a42c..07fd967 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -667,5 +667,18 @@ } #endregion + + public new Boolean IsEmpty() + { + return IsEmpty(false); + } + + public new Boolean IsEmpty(Boolean includeFormats) + { + if (includeFormats && !Style.Equals(Worksheet.Style)) + return false; + + return base.IsEmpty(includeFormats); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index 9b354df..9786658 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -444,5 +444,6 @@ RangeAddress.LastAddress.FixedRow, RangeAddress.LastAddress.FixedColumn); } + } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs index 9569b72..926a114 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs @@ -57,7 +57,7 @@ var table = rangeTable.CopyTo(wsTable.Column(wsTable.LastColumnUsed().ColumnNumber() + 3)).CreateTable(); table.Sort("Column2, Column3 Desc, Column1 ASC"); - + wsTable.Row(1).InsertRowsAbove(2); wsTable.Cell(1, 1) .SetValue(".Sort(\"Column2, Column3 Desc, Column1 ASC\") = Sort table Top to Bottom, Col 2 Asc, Col 3 Desc, Col 1 Asc, Ignore Blanks, Ignore Case") diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj index 27ee61b..324d39d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -81,6 +81,8 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/MergedRangesTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/MergedRangesTests.cs new file mode 100644 index 0000000..efa7a75 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/MergedRangesTests.cs @@ -0,0 +1,29 @@ +using ClosedXML.Excel; +using Microsoft.VisualStudio.TestTools.UnitTesting; +using System.Linq; +using System; +using System.IO; +using System.Drawing; + +namespace ClosedXML_Tests +{ + [TestClass] + public class MergedRangesTests + { + [TestMethod] + public void LastCellFromMerge() + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Sheet"); + ws.Range("B2:D4").Merge(); + + var first = ws.FirstCellUsed(true).Address.ToStringRelative(); + var last = ws.LastCellUsed(true).Address.ToStringRelative(); + + Assert.AreEqual("B2", first); + Assert.AreEqual("D4", last); + } + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/RangeUsedTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/RangeUsedTests.cs new file mode 100644 index 0000000..f55b0ae --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Ranges/RangeUsedTests.cs @@ -0,0 +1,23 @@ +using ClosedXML.Excel; +using Microsoft.VisualStudio.TestTools.UnitTesting; +using System.Linq; +using System; +using System.IO; +using System.Drawing; + +namespace ClosedXML_Tests +{ + [TestClass] + public class RangeUsedTests + { + //[TestMethod] + //public void FirstLastRows() + //{ + // var wb = new XLWorkbook(); + // var ws = wb.Worksheets.Add("Sheet"); + + //} + + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx index ba8688f..152d37a 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx index 62f2350..ad5a396 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx index 8eb3bab..05078f1 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx index 0cebd6f..1c135b7 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/DefiningRanges.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/DefiningRanges.xlsx index a33f5d3..a0ec55f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/DefiningRanges.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/DefiningRanges.xlsx Binary files differ