/// <summary>
/// Creates a <see cref="CellAccessor"/> instance that represents all effective cells on a <see cref="Worksheet"/>.
/// </summary>
/// <param name="worksheet">The worksheet.</param>
/// <remarks>
/// <para>
/// The created instance maintains several child objects of <paramref name="worksheet"/>.
/// Don't dispose <paramref name="worksheet"/> until you've done with it.
/// </para>
/// <para>
/// This class <i>modifies</i> some formatting of the given worksheet for its own purposes.
/// It is not a good idea to save the worksheet after using this class.
/// </para>
/// </remarks>
public CellAccessor(Worksheet worksheet)
{
ExcelApp = worksheet.Application;
Cells = worksheet.Cells;
VisibleCells = Cells.SpecialCells(XlCellType.xlCellTypeVisible);
// To avoid <see cref="Range.Text"/> to return #### for a narrow column.
// This is the only modification we make on the worksheet as mentioned in the remarks above.
Cells.ShrinkToFit = true;
// Extract the meaningful range of cells on the sheet.
var bottom_right = Cells
.SpecialCells(XlCellType.xlCellTypeLastCell)
.Address(true, true, XlReferenceStyle.xlA1);
if (bottom_right != "$A$1")
{
// The normal case.
// I expect that most cells on a glossary worksheet contain text data,
// and their Value2 property usually return a string object.
// We will utilize them at maximum to reduce the COM overhead.
CachedCellValues = Cells.Range("$A$1", bottom_right).Value2 as object[, ];
}
else if (string.IsNullOrWhiteSpace(Cells.Range("$A$1").Text as string))
{
// The worksheet was empty.
CachedCellValues = Array.CreateInstance(typeof(object), new int[] { 0, 0 }, new int[] { 1, 1 }) as object[, ];
}
else
{
// The worksheet contained only one value at $A$1.
CachedCellValues = Array.CreateInstance(typeof(object), new int[] { 1, 1 }, new int[] { 1, 1 }) as object[, ];
CachedCellValues[1, 1] = Cells.Range("$A$1").Value2;
}
var rows = CachedCellValues.GetLength(0);
var columns = CachedCellValues.GetLength(1);
// Trim empty columns at the end if any.
while (columns > 0 &&
Enumerable.Range(1, rows).All(row => string.IsNullOrWhiteSpace(CachedCellValues[row, columns]?.ToString())))
{
--columns;
}
Rows = rows;
Columns = columns;
}
public XLAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks)
{
if (!Enabled)
{
throw new InvalidOperationException("Filter has not been enabled.");
}
var ws = Range.Worksheet as XLWorksheet;
ws.SuspendEvents();
Range.Range(Range.FirstCell().CellBelow(), Range.LastCell()).Sort(columnToSortBy, sortOrder, matchCase,
ignoreBlanks);
Sorted = true;
SortOrder = sortOrder;
SortColumn = columnToSortBy;
// Recalculate shown / hidden rows
var rows = Range.Rows(2, Range.RowCount());
foreach (IXLRangeRow row in rows)
{
row.WorksheetRow().Unhide();
}
foreach (var kp in Filters)
{
Boolean firstFilter = true;
foreach (XLFilter filter in kp.Value)
{
var condition = filter.Condition;
var isText = filter.Value is String;
var isDateTime = filter.Value is DateTime;
foreach (IXLRangeRow row in rows)
{
//TODO : clean up filter matching - it's done in different place
Boolean match;
if (isText)
{
match = condition(row.Cell(kp.Key).GetFormattedString());
}
else if (isDateTime)
{
match = row.Cell(kp.Key).DataType == XLDataType.DateTime &&
condition(row.Cell(kp.Key).GetDateTime());
}
else
{
match = row.Cell(kp.Key).DataType == XLDataType.Number &&
condition(row.Cell(kp.Key).GetDouble());
}
if (firstFilter)
{
if (match)
{
row.WorksheetRow().Unhide();
}
else
{
row.WorksheetRow().Hide();
}
}
else
{
if (filter.Connector == XLConnector.And)
{
if (!row.WorksheetRow().IsHidden)
{
if (match)
{
row.WorksheetRow().Unhide();
}
else
{
row.WorksheetRow().Hide();
}
}
}
else if (match)
{
row.WorksheetRow().Unhide();
}
}
}
firstFilter = false;
}
}
ws.ResumeEvents();
return(this);
}
public XLAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks)
{
if (!Enabled)
{
throw new ApplicationException("Filter has not been enabled.");
}
var ws = Range.Worksheet as XLWorksheet;
ws.SuspendEvents();
Range.Range(Range.FirstCell().CellBelow(), Range.LastCell()).Sort(columnToSortBy, sortOrder, matchCase,
ignoreBlanks);
Sorted = true;
SortOrder = sortOrder;
SortColumn = columnToSortBy;
if (Enabled)
{
using (var rows = Range.Rows(2, Range.RowCount()))
{
foreach (IXLRangeRow row in rows)
{
row.WorksheetRow().Unhide();
}
}
foreach (KeyValuePair <int, List <XLFilter> > kp in Filters)
{
Boolean firstFilter = true;
foreach (XLFilter filter in kp.Value)
{
Boolean isText = filter.Value is String;
using (var rows = Range.Rows(2, Range.RowCount()))
{
foreach (IXLRangeRow row in rows)
{
Boolean match = isText
? filter.Condition(row.Cell(kp.Key).GetString())
: row.Cell(kp.Key).DataType == XLCellValues.Number &&
filter.Condition(row.Cell(kp.Key).GetDouble());
if (firstFilter)
{
if (match)
{
row.WorksheetRow().Unhide();
}
else
{
row.WorksheetRow().Hide();
}
}
else
{
if (filter.Connector == XLConnector.And)
{
if (!row.WorksheetRow().IsHidden)
{
if (match)
{
row.WorksheetRow().Unhide();
}
else
{
row.WorksheetRow().Hide();
}
}
}
else if (match)
{
row.WorksheetRow().Unhide();
}
}
}
firstFilter = false;
}
}
}
}
ws.ResumeEvents();
return(this);
}