public static ExcelRange Validation(this ExcelRange range,
ExcelRange validValues,
string errorTitle,
string errorText
)
{
return(range.Validation(String.Format("='{0}'!{1}",
validValues.GetInteropVersion().Worksheet.Name,
validValues.GetInteropVersion().get_AddressLocal()),
errorTitle,
errorText));
}
public static object[,] ValuesAsArray(this ExcelRange range)
{
if (range.GetInteropVersion().Cells.Count == 1)
{
var result = new Object[2, 2];
result[1, 1] = range.GetInteropVersion().Value2;
return(result);
}
else
{
return(range.GetInteropVersion().Value2);
}
}
public static ExcelListObject ConvertIntoTable(this ExcelRange range, string tableName)
{
Worksheet currentWorksheet = range.GetInteropVersion().Worksheet;
var result = currentWorksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
range.GetInteropVersion(),
System.Type.Missing,
XlYesNoGuess.xlYes,
System.Type.Missing);
result.Name = tableName;
return(new ExcelListObject(result));
}
public static ExcelRange GetSingleCellByOffset(this ExcelRange range, int rowOffset, int columnOffset)
{
var rangeInteropVersion = range.GetInteropVersion();
var currentRow = rangeInteropVersion.Row;
var currentColumn = rangeInteropVersion.Column;
return(new ExcelRange(rangeInteropVersion.Worksheet.Cells[currentRow + rowOffset, currentColumn + columnOffset]));
}
private static ExcelRange ExtendRangeSize(this ExcelRange range, int extendRows, int extendColumns)
{
var rangeInteropVersion = range.GetInteropVersion();
Range topLeft = range.GetLeftTopCell().GetInteropVersion();
var columnAbsoluteIndex = topLeft.Column;
var rowAbsoluteIndex = topLeft.Row;
var columnsWide = rangeInteropVersion.Columns.Count + extendColumns;
var rowsHeight = rangeInteropVersion.Rows.Count + extendRows;
Range bottomRight = topLeft.Worksheet.Cells[rowAbsoluteIndex + rowsHeight - 1, columnAbsoluteIndex + columnsWide - 1]; // -1 reason one base
return(new ExcelRange(topLeft.Worksheet.get_Range(topLeft, bottomRight)));
}
public static ExcelRange Validation(this ExcelRange range,
string validationFormula,
string errorTitle,
string errorText
)
{
var interopRange = range.GetInteropVersion();
interopRange.Validation.Delete();
interopRange.Validation.Add(
XlDVType.xlValidateList,
XlDVAlertStyle.xlValidAlertInformation,
XlFormatConditionOperator.xlBetween,
validationFormula,
Type.Missing);
interopRange.Validation.IgnoreBlank = true;
interopRange.Validation.ErrorMessage = string.IsNullOrEmpty(errorText) ? string.Empty : errorText;
interopRange.Validation.ErrorTitle = string.IsNullOrEmpty(errorTitle) ? "Error" : errorTitle;
interopRange.Validation.InCellDropdown = true;
interopRange.Validation.ShowError = string.IsNullOrEmpty(errorText);
return(range);
}
public static ExcelRange Group(this ExcelRange range)
{
range.GetInteropVersion().Group();
return(range);
}
public static ExcelRange GetColumnsFromRange(this ExcelRange range, int startcolumn, int endColumn)
{
var startrange = range.GetLeftTopCell().GetSingleCellByOffset(0, startcolumn - 1);
return(startrange.ExtendRangeSize(range.GetInteropVersion().Rows.Count - 1, endColumn - startcolumn));
}
public static ExcelRange Merge(this ExcelRange range)
{
range.GetInteropVersion().Merge(false);
return(range);
}
public static ExcelRange Width(this ExcelRange range, int widthInPoints)
{
range.GetInteropVersion().ColumnWidth = widthInPoints;
return(range);
}
public static ExcelRange Format(this ExcelRange range, string format)
{
range.GetInteropVersion().NumberFormat = format;
return(range);
}
public static ExcelRange SetValue(this ExcelRange range, object value)
{
range.GetInteropVersion().Value2 = value;
return(range);
}
public static ExcelRange GetLeftTopCell(this ExcelRange range)
{
return(new ExcelRange(range.GetInteropVersion().Cells[1, 1]));
}
public static ExcelRange WriteData(this ExcelRange range, IListObjectDataObject data)
{
range.GetInteropVersion().Value2 = data.AllValues;
return(range);
}
public static ExcelRange AutoFit(this ExcelRange range)
{
range.GetInteropVersion().EntireColumn.AutoFit();
return(range);
}
public static ExcelRange Wrap(this ExcelRange range, bool wrapText)
{
range.GetInteropVersion().WrapText = wrapText;
return(range);
}
public static ExcelRange Font(this ExcelRange range, int fontSize)
{
range.GetInteropVersion().Font.Size = fontSize;
return(range);
}
public static ExcelRange FontStyling(this ExcelRange range, Microsoft.Office.Interop.Excel.Font fontStyle)
{
range.GetInteropVersion().Font.FontStyle = fontStyle;
return(range);
}
public static ExcelRange Orientation(this ExcelRange range, int angle)
{
range.GetInteropVersion().Orientation = angle;
return(range);
}
public static ExcelRange BackgroundColor(this ExcelRange range, Color color)
{
range.GetInteropVersion().Interior.Color = color;
return(range);
}
public static string Address(this ExcelRange range)
{
return(range.GetInteropVersion().get_Address());
}
private static bool isRangeSizeValid(ExcelRange range, object[,] data)
{
var internalRange = range.GetInteropVersion();
return(internalRange.Columns.Count == data.GetLength(xLengthIndex) && internalRange.Rows.Count == data.GetLength(yLengthIndex) + NumberOfHeaders);
}
private static ExcelRange GetBottomRightCell(this ExcelRange range)
{
var internalRange = range.GetInteropVersion();
return(new ExcelRange(internalRange.Cells[internalRange.Rows.Count, internalRange.Columns.Count]));
}