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])); }