/// <summary>
/// Returne l'adresse, du type A1, de la derniere cellule non vide,
/// en partant d'une cellule dans une direction définie
/// </summary>
/// <param name="CelluleDepart"></param>
/// <param name="Direction"></param>
/// <returns></returns>
public string AtteindreDerniereCelluleNonVide(string CelluleDepart, XlDirection Direction)
{
_MonRange = _MonRange.get_Range(CelluleDepart, CelluleDepart);
_MonRange.get_End(Direction).Select();
_MonRange = _ApplicationXL.ActiveCell;
return(this.AdresseCellTypeA1(_MonRange.Row, _MonRange.Column));
}
public void DragOff(XlDirection direction, int regionIndex)
{
object[] paramArray = new object[2];
paramArray[0] = direction;
paramArray[1] = regionIndex;
InstanceType.InvokeMember("DragOff", BindingFlags.InvokeMethod, null, ComReference, paramArray, XlLateBindingApiSettings.XlThreadCulture);
}
public XlRange End(XlDirection direction)
{
object[] paramArray = new object[1];
paramArray[0] = direction;
object returnValue = InstanceType.InvokeMember("End", BindingFlags.GetProperty, null, ComReference, paramArray, XlLateBindingApiSettings.XlThreadCulture);
if (null == returnValue)
{
return(null);
}
XlRange newClass = new XlRange(this, returnValue);
ListChildReferences.Add(newClass);
return(newClass);
}
/// <summary>
/// Returns the direction as a tuple representing the row and column
/// index shift. For example, xlUp.toRowColShift() => (-1, 0).
/// </summary>
public static (int, int) toRowColShift(this XlDirection direction)
{
switch (direction)
{
case XlDirection.xlDown: return(1, 0);
case XlDirection.xlToLeft: return(0, -1);
case XlDirection.xlToRight: return(0, 1);
case XlDirection.xlUp: return(-1, 0);
default:
throw new System.Exception("XlDirection not recognized.");
}
}
/// <summary>
/// If this is a single cell, returns the cell adjacent to this one in
/// the given direction. If this is not a single cell, throws an exception.
/// If there is no adjacent cell in the given direction because we are at the
/// edge of the sheet, throws an exception.
/// </summary>
private Range AdjacentCell(XlDirection direction)
{
if (!this.IsOneByOne())
{
throw new System.Exception("Called AdjacentCell on Range containing " +
"multiple cells.");
}
int newRowIndex = _minRowIndex;
int newColIndex = _minColIndex;
if (direction == XlDirection.xlToLeft)
{
newColIndex -= 1;
}
else if (direction == XlDirection.xlUp)
{
newRowIndex -= 1;
}
else if (direction == XlDirection.xlToRight)
{
newColIndex += 1;
}
else if (direction == XlDirection.xlDown)
{
newRowIndex += 1;
}
// If we have moved off the sheet, throw an exception.
if (newRowIndex < 1 || newColIndex < 1)
{
throw new NoAdjacentCellException();
}
return(new Range(
newRowIndex,
newColIndex,
_worksheet,
_document
));
}
public static void AddColumnToSheet(
ref Worksheet ws,
int columnNumber,
int columnWidth,
bool columnWrapText,
string columnNumberFormat,
XlDirection shiftDirection,
XlInsertFormatOrigin insertFormatOrigin,
int headerRow,
string headerTitle = "",
int headerFontSize = cHeaderFontSize,
MakeBold headerBold = MakeBold.Yes,
XlUnderlineStyle headerUnderline = XlUnderlineStyle.xlUnderlineStyleSingle,
WrapText headerWrapText = WrapText.Yes,
XlHAlign headerHorizontalAlignment = XlHAlign.xlHAlignCenter,
int headerOrientation = 0)
{
// Insert the new column and apply things that pertain to all cells in the column
//((Range)ws.Columns[columnNumber]).Insert(Shift: shiftDirection, CopyOrigin: insertFormatOrigin);
Range newColumn = (Range)ws.Columns[columnNumber];
newColumn.Insert(Shift: shiftDirection, CopyOrigin: insertFormatOrigin);
newColumn.WrapText = columnWrapText;
newColumn.NumberFormat = columnNumberFormat;
// Pass all the rest on
AddColumnHeaderToSheet(ref ws,
columnNumber,
columnWidth,
headerRow,
headerTitle,
headerFontSize,
headerBold,
headerUnderline,
headerWrapText,
headerHorizontalAlignment,
headerOrientation);
}
public Range get_End(XlDirection Direaction)
{
throw new NotImplementedException();
}
/// <summary>
/// Returns a Range object that represents the cell at the end of the region that
/// contains the source range. Equivalent to pressing END+UP ARROW, END+DOWN ARROW,
/// END+LEFT ARROW, or END+RIGHT ARROW.
/// </summary>
public Range End(XlDirection direction)
{
if (!this.IsOneByOne())
{
throw new System.Exception("For now we only implement Range.End for " +
"1-by-1 ranges. Please expand the implementation if needed.");
}
Range firstCellOver;
try {
firstCellOver = this.AdjacentCell(direction);
}
catch (NoAdjacentCellException e) {
// If this cell is an edge cell and we are moving towards the edge, we
// can't go anywhere, so we just return this cell.
return(this);
}
// If this cell is nonempty *and* the first cell in the given direction is
// nonempty, then the End is the last nonempty cell in the given direction.
if (!this.IsEmptyCell() && !firstCellOver.IsEmptyCell())
{
Range curCell = this;
Range nextCell = firstCellOver;
while (!nextCell.IsEmptyCell())
{
// Shift the cur cell over.
curCell = nextCell;
// If one of the cur cell indices is infinite, this is like hitting
// the far edge of the spreadsheet, so we stop here.
if (curCell.MaxRowIndexIsInfinite() ||
curCell.MaxColIndexIsInfinite())
{
return(curCell);
}
// Try to shift the next cell over, but we have to stop with the cur
// cell if we hit the edge.
try {
nextCell = nextCell.AdjacentCell(direction);
}
catch (NoAdjacentCellException) {
return(curCell);
}
}
// When the next cell is empty, the current cell is the last nonempty
// cell, which is what we are seeking.
return(curCell);
}
// Otherwise, the End is the first nonempty cell in the given direction
// (not including this cell itself).
else
{
Range curCell = firstCellOver;
while (curCell.IsEmptyCell())
{
try {
curCell = curCell.AdjacentCell(direction);
}
catch (NoAdjacentCellException) {
// If we hit the edge, we stop here.
return(curCell);
}
// If one of the cur cell indices is infinite, this is like hitting
// the far edge of the spreadsheet, so we stop here.
if (curCell.MaxRowIndexIsInfinite() ||
curCell.MaxColIndexIsInfinite())
{
return(curCell);
}
}
return(curCell);
}
}