/// <summary>
/// Pegar los datos de una celda o celdas seleccionadas.
/// </summary>
public override void pegarValoresCelda()
{
XlPasteType tipo = XlPasteType.xlPasteAll;
XlPasteSpecialOperation operacion = XlPasteSpecialOperation.xlPasteSpecialOperationNone;
_seleccionado.PasteSpecial(tipo, operacion, false, false);
}
/// <summary>
/// Copy a range from matching position in a provided file to the current worksheet.
/// It works exactly same as manual open of provided Excel file,
/// copy (special) same position range from it to the current worksheet
/// closing provided file.
/// </summary>
/// <param name="sourceFile">Full path to .xlsx file.</param>
/// <param name="targetRange">Single-area range in the current worksheet to copy to.</param>
/// <param name="pasteType">Mode of special copy. Can choose to copy all, only values, only styles etc.</param>
/// <param name="sheetName">Optional name of source sheet in the provided file.
/// If missing, the method uses first sheet.</param>
public void CopyFromFileSpecial(string sourceFile, Range targetRange, XlPasteType pasteType, string sheetName = null)
{
if (sourceFile == null)
{
throw new ArgumentNullException(nameof(sourceFile));
}
if (!File.Exists(sourceFile))
{
throw new FileNotFoundException("File does not exist", sourceFile);
}
if (targetRange == null)
{
throw new ArgumentNullException(nameof(targetRange));
}
Workbook sourceWorkbook = targetRange.Application.Workbooks.Open(sourceFile);
sourceWorkbook.Windows[1].Visible = false;
Range sourceRange = sourceWorkbook.Worksheets[sheetName ?? (object)1].Range[targetRange.Address];
CopySpecial(sourceRange, targetRange, pasteType);
sourceWorkbook.Close();
ClearClipboard();
}
/// <summary>
/// Pegar una formula de la celda seleccionada.
/// </summary>
public override void pegarFormula()
{
XlPasteType tipo = XlPasteType.xlPasteFormulasAndNumberFormats;
XlPasteSpecialOperation operacion = XlPasteSpecialOperation.xlPasteSpecialOperationNone;
_seleccionado.PasteSpecial(tipo, operacion, false, false);
}
public void CopyPaste(IVTRange range, int firstRow, int firstColumn, bool copyStyleOnly = false)
{
NativeExcel.IRange iRange = range.Range;
NativeExcel.IRange oRange = Worksheet.Range[firstRow, firstColumn, firstRow + iRange.Rows.Count - 1,
firstColumn + iRange.Columns.Count - 1];
XlPasteType type = copyStyleOnly ? XlPasteType.xlPasteFormats : XlPasteType.xlPasteAll;
iRange.Copy(oRange, type);
}
private bool CopyWorkBook(IWorksheet src, IWorksheet dest, XlPasteType pastetype)
{
bool success = false;
IRange used = src.UsedRange;
IRange srcrange = src.Range[1, 1, used.Row + used.Rows.Count, used.Column + used.Columns.Count];
IRange dstrange = dest.Range[1, 1, used.Row + used.Rows.Count, used.Column + used.Columns.Count];
srcrange.Copy(dstrange, pastetype);
return(success);
}
public void CutPaste(IVTRange range, int firstRow, int firstColumn)
{
NativeExcel.IRange iRange = range.Range;
NativeExcel.IRange oRange = Worksheet.Range[firstRow, firstColumn, firstRow + iRange.Rows.Count - 1,
firstColumn + iRange.Columns.Count - 1];
XlPasteType type = XlPasteType.xlPasteAll;
iRange.Copy(oRange, type);
oRange.Delete();
}
/// <summary>
/// Copy a range to a separate .xlsx file,
/// to a sheet with same name as source sheet,
/// to the same position as source range.
/// It works exactly same as manual copy special from one Excel file to a new file
/// then saving and closing new file.
/// Note: remove this temporary .xlsx file after using,
/// because processes have limitation on amount of created temporary files.
/// </summary>
/// <param name="sourceRange">Single-area range that will be copied.</param>
/// <param name="pasteType">Mode of special copy. Can choose to copy all, only values, only styles etc.</param>
/// <returns>Full path to a new temporary .xlsx file with copied range.</returns>
public string CopyToFileSpecial(Range sourceRange, XlPasteType pasteType)
{
if (sourceRange == null)
{
throw new ArgumentNullException(nameof(sourceRange));
}
var target = SetupWorkbookAndSheet(sourceRange);
CopySpecial(sourceRange, GetTargetRange(sourceRange, target.Item2), pasteType);
return(SaveAndClose(target.Item1));
}
public void CopyPasteSameColumnWidth(IVTRange range, int firstRow, int firstColumn, bool copyStyleOnly = false)
{
NativeExcel.IRange iRange = range.Range;
NativeExcel.IRange oRange = Worksheet.Range[firstRow, firstColumn, firstRow + iRange.Rows.Count - 1,
firstColumn + iRange.Columns.Count - 1];
XlPasteType type = copyStyleOnly ? NativeExcel.XlPasteType.xlPasteFormats : NativeExcel.XlPasteType.xlPasteAll;
iRange.Copy(oRange, type);
List <double> listWidth = new List <double>();
for (int i = 1; i <= iRange.Columns.Count; i++)
{
listWidth.Add(iRange.Columns[i].ColumnWidth);
}
for (int i = 1; i <= iRange.Columns.Count; i++)
{
oRange.Columns[i].ColumnWidth = listWidth[i - 1];
}
}
public object PasteSpecial(XlPasteType Paste, XlPasteSpecialOperation Operation, object SkipBlanks, object Transpose)
{
throw new NotImplementedException();
}
/// <summary>
/// Paste Method
/// </summary>
/// <param name="iStartRow">Start Row</param>
/// <param name="iStartCol">Start Column</param>
/// <param name="iEndRow">End Row</param>
/// <param name="iEndCol">End Column</param>
/// <param name="strFormat">Format</param>
/// <param name="pasteType">PasteType</param>
public void Paste(int iStartRow, int iStartCol, int iEndRow, int iEndCol, string strFormat, XlPasteType pasteType)
{
m_objExcel.Range[m_objExcel.Cells[iStartRow, iStartCol], m_objExcel.Cells[iEndRow, iEndCol]].PasteSpecial(pasteType, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
}
protected override void Execute(NativeActivityContext context)
{
try
{
bool excelFileVisible = false;
string cutCopyModeFilePath = CutCopyModeFilePath.Get(context);
if (File.Exists(cutCopyModeFilePath))
{
if (true == NeedToOpen)
{
excelFileVisible = true;
}
ExcelHelper.Shared.Close_OpenedFile(cutCopyModeFilePath);
ExcelHelper.Shared.GetApp(excelFileVisible).DisplayAlerts = false;
string workbookNamecutCopyMode = string.Empty;
workbookNamecutCopyMode = Path.GetFileName(cutCopyModeFilePath);
string worksheetNamecutCopyMode = CutCopyModeWorksheetName.Get(context);
dynamic workBookObjectcutCopyMode = null;
workBookObjectcutCopyMode = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(cutCopyModeFilePath);
bool sheetExistcutCopyMode = ExcelHelper.Shared.GetWorksheetByName(workbookNamecutCopyMode, worksheetNamecutCopyMode, false) != null;
if (false == sheetExistcutCopyMode)
{
if (NeedToClose == true)
{
workBookObjectcutCopyMode.Close();
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Worksheet \"" + worksheetNamecutCopyMode + "\" does not exist in activity Excel_CopyPaste", LogLevel.Error);
}
else
{
string workbookFullName = FilePath.Get(context);
if (File.Exists(workbookFullName))
{
ExcelHelper.Shared.Close_OpenedFile(workbookFullName);
ExcelHelper.Shared.GetApp(excelFileVisible).DisplayAlerts = false;
string workbookName = string.Empty;
workbookName = Path.GetFileName(workbookFullName);
string worksheetName = WorksheetName.Get(context);
dynamic workBookObject = null;
workBookObject = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
bool sheetExist = ExcelHelper.Shared.GetWorksheetByName(workbookName, worksheetName, false) != null;
if (false == sheetExist)
{
if (NeedToClose == true)
{
workBookObject.Close();
workBookObjectcutCopyMode.Close();
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Worksheet \"" + worksheetName + "\" does not exist in activity Excel_CopyPaste", LogLevel.Error);
}
else
{
string cells = Cells.Get(context);
string cellsCutCopyMode = CutCopyModeCells.Get(context);
Range xlRange = null;
Worksheet xlWorksheet = null;
Worksheet xlWorksheetCutCopyMode = null;
bool result = false;
workBookObject = ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
xlWorksheet = workBookObject.Sheets[worksheetName];
xlRange = xlWorksheet.Range[cells];
xlWorksheetCutCopyMode = workBookObjectcutCopyMode.Sheets[worksheetNamecutCopyMode];
Range xlRangeCutCopyMode = xlWorksheetCutCopyMode.Range[cellsCutCopyMode];
Range cutCopyRange = xlRangeCutCopyMode;
Range insertRange = xlRange;
cutCopyRange.Copy();
string pt = ToDescriptionString(PasteType);
XlPasteType ptEnum = (XlPasteType)Enum.Parse(typeof(XlPasteType), pt);
result = insertRange.PasteSpecial(ptEnum, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
if (cutCopyModeFilePath.Equals(workbookFullName))
{
var range = xlWorksheet.get_Range("A1", "A1");
range.Select();
workBookObject.Save();
}
if (!cutCopyModeFilePath.Equals(workbookFullName))
{
workBookObjectcutCopyMode.Close();
workBookObject.Save();
var range = xlWorksheet.get_Range("A1", "A1");
range.Select();
workBookObject.Save();
}
Result.Set(context, result);
if (true == NeedToClose)
{
workBookObject.Close();
}
if (false == NeedToClose && false == NeedToOpen)
{
workBookObject.Close();
}
if (false == NeedToClose && true == NeedToOpen)
{
workBookObject.Close();
ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Open(workbookFullName);
}
if (ExcelHelper.Shared.GetApp(excelFileVisible).Workbooks.Count == 0)
{
ExcelHelper.Shared.Dispose();
}
}
}
else
{
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Excel file does not exist:\"" + workbookFullName + "\" in activity Excel_CopyPaste", LogLevel.Error);
}
}
}
else
{
if (!ContinueOnError)
{
context.Abort();
}
Log.Logger.LogData("Excel file does not exist:\"" + cutCopyModeFilePath + "\" in activity Excel_CopyPaste", LogLevel.Error);
}
//if (!ContinueOnError)
//{
// context.Abort();
//}
}
catch (Exception ex)
{
Result.Set(context, false);
Log.Logger.LogData(ex.Message + " in activity Excel_CopyPaste", LogLevel.Error);
if (!ContinueOnError)
{
context.Abort();
}
}
}
public void PasteSpecial(XlPasteType pasteType)
{
object[] paramArray = new object[1];
paramArray[0] = pasteType;
InstanceType.InvokeMember("PasteSpecial", BindingFlags.InvokeMethod, null, ComReference, paramArray, XlLateBindingApiSettings.XlThreadCulture);
}
/// <summary>将 Range 从剪贴板粘贴到指定的区域中。
/// </summary>
/// <param name="Paste">要粘贴的区域部分。</param>
/// <param name="Operation">粘贴操作。</param>
/// <param name="SkipBlanks">如果为 True，则不将剪贴板上区域中的空白单元格粘贴到目标区域中。默认值为 False。</param>
/// <param name="Transpose">如果为 True，则在粘贴区域时转置行和列。默认值为 False。</param>
public dynamic PasteSpecial(XlPasteType Paste = XlPasteType.xlPasteAll, XlPasteSpecialOperation Operation = XlPasteSpecialOperation.xlPasteSpecialOperationNone, bool? SkipBlanks = null, bool? Transpose = null)
{
_objaParameters = new object[4] {
Paste,
Operation,
SkipBlanks == null ? System.Type.Missing : SkipBlanks,
Transpose == null ? System.Type.Missing : Transpose
};
return _objRange.GetType().InvokeMember("PasteSpecial", BindingFlags.InvokeMethod, null, _objRange, _objaParameters);
}
/// <summary>将剪贴板中的图表数据粘贴到指定的图表中。
/// </summary>
/// <param name="Type">指定要粘贴的图表信息（如果剪贴板中有一个图表）。可以为以下 XlPasteType 常量之一：xlPasteFormats、xlPasteFormulas 或 xlPasteAll。默认值为 xlPasteAll。如果剪贴板上的数据不是图表数据，则不能使用该参数。</param>
public void Paste(XlPasteType? Type = null)
{
_objaParameters = new object[1] { Type == null ? System.Type.Missing : Type };
_objChart.GetType().InvokeMember("Paste", BindingFlags.InvokeMethod, null, _objChart, _objaParameters);
}
static void CopySpecial(Range source, Range target, XlPasteType pasteType)
{
source.Copy();
target.PasteSpecial(pasteType);
}
