diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs
index 76902d0..8129cfa 100644
--- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs
+++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs
@@ -1,4 +1,5 @@
using System;
+using System.Globalization;
namespace ClosedXML.Excel
{
@@ -8,7 +9,7 @@
Worksheet
}
- public interface IXLRangeBase: IDisposable
+ public interface IXLRangeBase : IDisposable
{
IXLWorksheet Worksheet { get; }
@@ -64,7 +65,6 @@
IXLHyperlinks Hyperlinks { get; }
-
///
/// Returns the collection of cells.
///
@@ -94,6 +94,15 @@
IXLCells CellsUsed(Boolean includeFormats, Func predicate);
///
+ /// Searches the cells' contents for a given piece of text
+ ///
+ /// The search text.
+ /// The compare options.
+ /// if set to true search formulae instead of cell values.
+ ///
+ IXLCells Search(String searchText, CompareOptions compareOptions = CompareOptions.Ordinal, Boolean searchFormulae = false);
+
+ ///
/// Returns the first cell of this range.
///
IXLCell FirstCell();
@@ -236,15 +245,15 @@
IXLRange AsRange();
Boolean IsMerged();
+
Boolean IsEmpty();
+
Boolean IsEmpty(Boolean includeFormats);
-
IXLPivotTable CreatePivotTable(IXLCell targetCell);
+
IXLPivotTable CreatePivotTable(IXLCell targetCell, String name);
-
-
//IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn);
IXLAutoFilter SetAutoFilter();
diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs
index 6e34637..6ec9956 100644
--- a/ClosedXML/Excel/Ranges/XLRangeBase.cs
+++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs
@@ -2,36 +2,39 @@
using ClosedXML.Extensions;
using System;
using System.Collections.Generic;
+using System.Globalization;
using System.Linq;
-
-
namespace ClosedXML.Excel
{
internal abstract class XLRangeBase : IXLRangeBase, IXLStylized
{
public Boolean StyleChanged { get; set; }
+
#region Fields
private IXLStyle _style;
private XLSortElements _sortRows;
private XLSortElements _sortColumns;
- #endregion
+ #endregion Fields
private Int32 _styleCacheId;
+
protected void SetStyle(IXLStyle styleToUse)
{
_styleCacheId = Worksheet.Workbook.GetStyleId(styleToUse);
_style = null;
StyleChanged = false;
}
+
protected void SetStyle(Int32 styleId)
{
_styleCacheId = styleId;
_style = null;
StyleChanged = false;
}
+
public Int32 GetStyleId()
{
if (StyleChanged)
@@ -39,6 +42,7 @@
return _styleCacheId;
}
+
protected IXLStyle GetStyle()
{
return _style ?? (_style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId)));
@@ -46,18 +50,17 @@
#region Constructor
- static Int32 IdCounter = 0;
- readonly Int32 Id;
+ private static Int32 IdCounter = 0;
+ private readonly Int32 Id;
protected XLRangeBase(XLRangeAddress rangeAddress)
{
-
Id = ++IdCounter;
RangeAddress = new XLRangeAddress(rangeAddress);
}
- #endregion
+ #endregion Constructor
private XLCallbackAction _shiftedRowsAction;
@@ -71,6 +74,7 @@
}
private XLCallbackAction _shiftedColumnsAction;
+
protected void SubscribeToShiftedColumns(Action action)
{
if (Worksheet == null || !Worksheet.EventTrackingEnabled) return;
@@ -85,6 +89,7 @@
//public XLRangeAddress RangeAddress { get; protected set; }
private XLRangeAddress _rangeAddress;
+
public XLRangeAddress RangeAddress
{
get { return _rangeAddress; }
@@ -243,8 +248,6 @@
}
}
-
-
public Object Value
{
set { Cells().ForEach(c => c.Value = value); }
@@ -255,7 +258,7 @@
set { Cells().ForEach(c => c.DataType = value); }
}
- #endregion
+ #endregion IXLRangeBase Members
#region IXLStylized Members
@@ -268,9 +271,9 @@
}
}
- #endregion
+ #endregion IXLStylized Members
- #endregion
+ #endregion Public properties
#region IXLRangeBase Members
@@ -457,17 +460,17 @@
get { return GetStyle(); }
set { Cells().ForEach(c => c.Style = value); }
}
+
IXLRange IXLRangeBase.AsRange()
{
return AsRange();
}
+
public virtual XLRange AsRange()
{
return Worksheet.Range(RangeAddress.FirstAddress, RangeAddress.LastAddress);
}
-
-
public IXLRange AddToNamed(String rangeName)
{
return AddToNamed(rangeName, XLScope.Workbook);
@@ -516,7 +519,7 @@
CellsUsed(includeFormats).Cast().Any(c => c.IsEmpty(includeFormats));
}
- #endregion
+ #endregion IXLRangeBase Members
#region IXLStylized Members
@@ -539,7 +542,28 @@
set { SetStyle(value); }
}
- #endregion
+ #endregion IXLStylized Members
+
+ public IXLCells Search(String searchText, CompareOptions compareOptions = CompareOptions.Ordinal, Boolean searchFormulae = false)
+ {
+ var culture = CultureInfo.CurrentCulture;
+ return this.CellsUsed(false, c =>
+ {
+ try
+ {
+ if (searchFormulae)
+ return c.HasFormula
+ && culture.CompareInfo.IndexOf(c.FormulaA1, searchText, compareOptions) >= 0
+ || culture.CompareInfo.IndexOf(c.Value.ToString(), searchText, compareOptions) >= 0;
+ else
+ return culture.CompareInfo.IndexOf(c.GetFormattedString(), searchText, compareOptions) >= 0;
+ }
+ catch
+ {
+ return false;
+ }
+ });
+ }
public XLCell FirstCell()
{
@@ -642,7 +666,6 @@
}
}
-
if (sp.Row > 0)
return Worksheet.Cell(sp.Row, sp.Column);
@@ -734,7 +757,6 @@
}
}
-
if (sp.Row > 0)
return Worksheet.Cell(sp.Row, sp.Column);
@@ -748,7 +770,6 @@
public XLCell Cell(String cellAddressInRange)
{
-
if (XLHelper.IsValidA1Address(cellAddressInRange))
return Cell(XLAddress.Create(Worksheet, cellAddressInRange));
@@ -810,7 +831,7 @@
Int32 newCellStyleId = styleId;
- // If the default style for this range base is empty, but the worksheet
+ // If the default style for this range base is empty, but the worksheet
// has a default style, use the worksheet's default style
if (styleId == 0 && worksheetStyleId != 0)
newCellStyleId = worksheetStyleId;
@@ -904,7 +925,6 @@
public XLRange Range(IXLRangeAddress rangeAddress)
{
-
var newFirstCellAddress = new XLAddress((XLWorksheet)rangeAddress.FirstAddress.Worksheet,
rangeAddress.FirstAddress.RowNumber + RangeAddress.FirstAddress.RowNumber - 1,
rangeAddress.FirstAddress.ColumnNumber + RangeAddress.FirstAddress.ColumnNumber - 1,
@@ -1077,7 +1097,6 @@
cell.ShiftFormulaColumns(asRange, numberOfColumns);
}
-
var cellsDataValidations = new Dictionary();
var cellsToInsert = new Dictionary();
var cellsToDelete = new List();
@@ -1193,7 +1212,6 @@
: Worksheet.Style;
rangeToReturn.Row(ro).Style = styleToUse;
}
-
}
}
@@ -1284,15 +1302,17 @@
return retVal;
}
- struct DataValidationToCopy
+ private struct DataValidationToCopy
{
public XLAddress SourceAddress;
public XLDataValidation DataValidation;
}
+
public void InsertRowsAboveVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true)
{
InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true);
}
+
public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true)
{
return InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false);
@@ -1363,7 +1383,6 @@
newCell.FormulaA1 = c.FormulaA1;
cellsToInsert.Add(newKey, newCell);
cellsToDelete.Add(c.Address);
-
}
}
@@ -1381,7 +1400,6 @@
cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber, c.ColumnNumber));
cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value));
-
Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber;
Int32 lastRowReturn = RangeAddress.FirstAddress.RowNumber + numberOfRows - 1;
Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber;
@@ -1472,7 +1490,6 @@
RangeAddress.LastAddress.RowNumber,
RangeAddress.LastAddress.ColumnNumber);
-
foreach (
XLCell cell in
Worksheet.Workbook.Worksheets.Cast().SelectMany(
@@ -1501,7 +1518,6 @@
Worksheet.Internals.CellsCollection.MaxRowUsed,
RangeAddress.LastAddress.ColumnNumber);
-
int columnModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? ColumnCount() : 0;
int rowModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp ? RowCount() : 0;
var cellsQuery = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? shiftLeftQuery : shiftUpQuery;
@@ -1523,7 +1539,6 @@
cellsToInsert.Add(newKey, newCell);
}
-
cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber, c.ColumnNumber));
cellsToInsert.ForEach(
c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value));
@@ -1698,17 +1713,16 @@
// return chart;
//}
-
IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell)
{
return CreatePivotTable(targetCell);
}
+
IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell, String name)
{
return CreatePivotTable(targetCell, name);
}
-
public XLPivotTable CreatePivotTable(IXLCell targetCell)
{
return CreatePivotTable(targetCell, Guid.NewGuid().ToString());
@@ -1821,7 +1835,6 @@
return this;
}
-
#region Sort Rows
private void SortRangeRows()
@@ -1886,7 +1899,7 @@
SortingRangeRows(pivot + 1, end);
}
- #endregion
+ #endregion Sort Rows
#region Sort Columns
@@ -1951,9 +1964,9 @@
SortingRangeColumns(pivot + 1, end);
}
- #endregion
+ #endregion Sort Columns
- #endregion
+ #endregion Sort
public XLRangeColumn ColumnQuick(Int32 column)
{
@@ -2017,7 +2030,6 @@
}
}
-
internal IXLConditionalFormat AddConditionalFormat(IXLConditionalFormat source)
{
using (var asRange = AsRange())
diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs
index 9e86a67..7dedabb 100644
--- a/ClosedXML/Excel/XLWorkbook.cs
+++ b/ClosedXML/Excel/XLWorkbook.cs
@@ -4,6 +4,7 @@
using System;
using System.Collections.Generic;
using System.Data;
+using System.Globalization;
using System.IO;
using System.Linq;
@@ -650,7 +651,24 @@
return columns;
}
-#region Fields
+ ///
+ /// Searches the cells' contents for a given piece of text
+ ///
+ /// The search text.
+ /// The compare options.
+ /// if set to true search formulae instead of cell values.
+ ///
+ public IEnumerable Search(String searchText, CompareOptions compareOptions = CompareOptions.Ordinal, Boolean searchFormulae = false)
+ {
+ foreach (var ws in WorksheetsInternal)
+ {
+ foreach (var cell in ws.Search(searchText, compareOptions, searchFormulae))
+ yield return cell;
+ }
+ }
+
+
+ #region Fields
private readonly XLLoadSource _loadSource = XLLoadSource.New;
private readonly String _originalFile;
@@ -866,7 +884,7 @@
public XLWorkbook SetLockWindows(Boolean value) { LockWindows = value; return this; }
internal HexBinaryValue LockPassword { get; set; }
public Boolean IsPasswordProtected { get { return LockPassword != null; } }
-
+
public void Protect(Boolean lockStructure, Boolean lockWindows, String workbookPassword)
{
if (IsPasswordProtected && workbookPassword == null)
@@ -896,7 +914,7 @@
LockStructure = lockStructure;
LockWindows = lockWindows;
}
-
+
public void Protect()
{
Protect(true);
@@ -927,4 +945,4 @@
Protect(false, false, workbookPassword);
}
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj
index 487ea3a..b44a82f 100644
--- a/ClosedXML_Tests/ClosedXML_Tests.csproj
+++ b/ClosedXML_Tests/ClosedXML_Tests.csproj
@@ -84,6 +84,7 @@
+
diff --git a/ClosedXML_Tests/Excel/Misc/SearchTests.cs b/ClosedXML_Tests/Excel/Misc/SearchTests.cs
new file mode 100644
index 0000000..0da76e2
--- /dev/null
+++ b/ClosedXML_Tests/Excel/Misc/SearchTests.cs
@@ -0,0 +1,78 @@
+using ClosedXML.Excel;
+using NUnit.Framework;
+using System.Globalization;
+using System.Linq;
+
+namespace ClosedXML_Tests.Excel.Misc
+{
+ [TestFixture]
+ public class SearchTests
+ {
+ [Test]
+ public void TestSearch()
+ {
+ using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\Misc\CellValues.xlsx")))
+ using (var wb = new XLWorkbook(stream))
+ {
+ var ws = wb.Worksheets.First();
+
+ IXLCells foundCells;
+
+ foundCells = ws.Search("Initial Value");
+ Assert.AreEqual(1, foundCells.Count());
+ Assert.AreEqual("B2", foundCells.Single().Address.ToString());
+ Assert.AreEqual("Initial Value", foundCells.Single().GetString());
+
+ foundCells = ws.Search("Using");
+ Assert.AreEqual(2, foundCells.Count());
+ Assert.AreEqual("D2", foundCells.First().Address.ToString());
+ Assert.AreEqual("Using Get...()", foundCells.First().GetString());
+ Assert.AreEqual(2, foundCells.Count());
+ Assert.AreEqual("E2", foundCells.Last().Address.ToString());
+ Assert.AreEqual("Using GetValue()", foundCells.Last().GetString());
+
+ foundCells = ws.Search("1234");
+ Assert.AreEqual(4, foundCells.Count());
+ Assert.AreEqual("C5,D5,E5,F5", string.Join(",", foundCells.Select(c => c.Address.ToString()).ToArray()));
+
+ foundCells = ws.Search("Sep");
+ Assert.AreEqual(2, foundCells.Count());
+ Assert.AreEqual("B3,G3", string.Join(",", foundCells.Select(c => c.Address.ToString()).ToArray()));
+
+ foundCells = ws.Search("1234", CompareOptions.Ordinal, true);
+ Assert.AreEqual(5, foundCells.Count());
+ Assert.AreEqual("B5,C5,D5,E5,F5", string.Join(",", foundCells.Select(c => c.Address.ToString()).ToArray()));
+
+ foundCells = ws.Search("test case", CompareOptions.Ordinal);
+ Assert.AreEqual(0, foundCells.Count());
+
+ foundCells = ws.Search("test case", CompareOptions.OrdinalIgnoreCase);
+ Assert.AreEqual(6, foundCells.Count());
+ }
+ }
+
+ [Test]
+ public void TestSearch2()
+ {
+ using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\Misc\Formulas.xlsx")))
+ using (var wb = new XLWorkbook(stream))
+ {
+ var ws = wb.Worksheets.First();
+
+ IXLCells foundCells;
+
+ foundCells = ws.Search("3", CompareOptions.Ordinal);
+ Assert.AreEqual(10, foundCells.Count());
+ Assert.AreEqual("C2", foundCells.First().Address.ToString());
+
+ foundCells = ws.Search("A2", CompareOptions.Ordinal, true);
+ Assert.AreEqual(4, foundCells.Count());
+ Assert.AreEqual("C2,D2,B6,A11", string.Join(",", foundCells.Select(c => c.Address.ToString()).ToArray()));
+
+ foundCells = ws.Search("RC", CompareOptions.Ordinal, true);
+ Assert.AreEqual(3, foundCells.Count());
+ Assert.AreEqual("E2,E3,E4", string.Join(",", foundCells.Select(c => c.Address.ToString()).ToArray()));
+ }
+ }
+ }
+}