protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
string sheetName = SheetName.Get(context);
string cellName_Begin = CellName_Begin.Get(context);
string cellName_End = CellName_End.Get(context);
int cellRow_Begin = CellRow_Begin.Get(context);
int cellColumn_Begin = CellColumn_Begin.Get(context);
int cellRow_End = CellRow_End.Get(context);
int cellColumn_End = CellColumn_End.Get(context);
Excel::_Worksheet sheet;
if (sheetName != null)
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
else
{
sheet = excelApp.ActiveSheet;
}
Excel::Range range1, range2;
range1 = cellName_Begin == null ? sheet.Cells[cellRow_Begin, cellColumn_Begin] : sheet.Range[cellName_Begin];
range2 = cellName_End == null ? sheet.Cells[cellRow_End, cellColumn_End] : sheet.Range[cellName_End];
Excel::Range range = sheet.Range[range1, range2];
if (!ShiftCells)
{
range.Clear();
}
else
{
if (ShiftType == ShiftTypes.ShiftUp)
{
range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
else if (ShiftType == ShiftTypes.ShiftLeft)
{
range.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
}
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
sheet = null;
range = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL删除区域执行过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
string cellName_Begin = CellName_Begin.Get(context);
string cellName_End = CellName_End.Get(context);
int cellRow_Begin = CellRow_Begin.Get(context);
int cellColumn_Begin = CellColumn_Begin.Get(context);
int cellRow_End = CellRow_End.Get(context);
int cellColumn_End = CellColumn_End.Get(context);
int destCellRow = DestCellRow.Get(context);
int destCellColumn = DestCellColumn.Get(context);
string copySheet = CopySheet.Get(context);
string destSheet = DestSheet.Get(context);
string destCell = DestCell.Get(context);
string destDestFilePath = DestFilePath.Get(context);
Excel::Range range1, range2;
Excel::_Worksheet sheet;
if (copySheet != null)
{
sheet = excelApp.ActiveWorkbook.Sheets[copySheet];
}
else
{
sheet = excelApp.ActiveSheet;
}
range1 = cellName_Begin == null ? sheet.Cells[cellRow_Begin, cellColumn_Begin] : sheet.Range[cellName_Begin];
range2 = cellName_End == null ? sheet.Cells[cellRow_End, cellColumn_End] : sheet.Range[cellName_End];
sheet.Range[range1, range2].Copy(Type.Missing);
Excel::_Worksheet pasteSheet;
if (destDestFilePath != null && destDestFilePath != "")
{
if (!File.Exists(destDestFilePath))
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "文件不存在，请检查路径有效性", destDestFilePath);
new CommonVariable().realaseProcessExit(excelApp);
}
else
{
Excel::Workbook workbook2 = excelApp.Workbooks._Open(destDestFilePath);
if (destSheet != null)
{
pasteSheet = workbook2.Sheets[destSheet];
}
else
{
pasteSheet = workbook2.ActiveSheet;
}
Excel::Range pasteRange = destCell == null ? pasteSheet.Cells[destCellRow, destCellColumn] : pasteSheet.Range[destCell];
pasteSheet.Paste(pasteRange);
workbook2.Save();
workbook2.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(pasteSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(pasteRange);
pasteRange = null;
}
}
else
{
if (destSheet != null)
{
pasteSheet = excelApp.ActiveWorkbook.Sheets[destSheet];
}
else
{
pasteSheet = excelApp.ActiveSheet;
}
Excel::Range pasteRange = destCell == null ? sheet.Cells[destCellRow, destCellColumn] : sheet.Range[destCell];
pasteSheet.Paste(pasteRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(pasteSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(pasteRange);
pasteRange = null;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
range1 = null; range2 = null;
sheet = null; pasteSheet = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL复制粘贴过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
string cellName_Begin = CellName_Begin.Get(context);
string cellName_End = CellName_End.Get(context);
int cellRow_Begin = CellRow_Begin.Get(context);
int cellColumn_Begin = CellColumn_Begin.Get(context);
int cellRow_End = CellRow_End.Get(context);
int cellColumn_End = CellColumn_End.Get(context);
string findData = FindData.Get(context);
string replaceData = ReplaceData.Get(context);
object obj = null;
Excel::Range range1, range2;
Excel::_Worksheet sheet = null;
Excel::_Worksheet currSheet = null;
Excel.XlLookAt lookAt = IsCellMatch ? Excel.XlLookAt.xlWhole : Excel.XlLookAt.xlPart;
if (SheetName.Get(context) != null)
{
sheet = excelApp.ActiveWorkbook.Sheets[SheetName];
}
else
{
sheet = excelApp.ActiveSheet;
}
try
{
if (AllSheets) //全部工作表
{
int sheetCount = excelApp.ActiveWorkbook.Worksheets.Count;
for (int i = 1; i < sheetCount + 1; i++)
{
if (AllRange)
{
excelApp.ActiveWorkbook.Worksheets[i].Range["A1", "IV65535"].Replace(obj, replaceData, lookAt, Type.Missing, IsTextTransform, isSemiFull);
}
else
{
currSheet = excelApp.ActiveWorkbook.Worksheets[i];
range1 = cellName_Begin == null ? currSheet.Cells[cellRow_Begin, cellColumn_Begin] : currSheet.Range[cellName_Begin];
range2 = cellName_End == null ? currSheet.Cells[cellRow_End, cellColumn_End] : currSheet.Range[cellName_End];
currSheet.Range[range1, range2].Replace(findData, replaceData, lookAt, Type.Missing, IsTextTransform, isSemiFull);
}
}
}
else
{
if (AllRange) //全部区域
{
sheet.Range["A1", "IV65535"].Replace(findData, replaceData, lookAt, Type.Missing, IsTextTransform, isSemiFull);
}
else
{
range1 = cellName_Begin == null ? sheet.Cells[cellRow_Begin, cellColumn_Begin] : sheet.Range[cellName_Begin];
range2 = cellName_End == null ? sheet.Cells[cellRow_End, cellColumn_End] : sheet.Range[cellName_End];
sheet.Range[range1, range2].Replace(findData, replaceData, lookAt, Type.Missing, IsTextTransform, isSemiFull);
}
}
/* 资源回收释放 */
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
if (currSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(currSheet);
currSheet = null;
}
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL查找替换过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
string cellName_Begin = CellName_Begin.Get(context);
string cellName_End = CellName_End.Get(context);
int cellRow_Begin = CellRow_Begin.Get(context);
int cellColumn_Begin = CellColumn_Begin.Get(context);
int cellRow_End = CellRow_End.Get(context);
int cellColumn_End = CellColumn_End.Get(context);
double rowHeight = RowHeight.Get(context);
double colWidth = ColWidth.Get(context);
Int32 fontSize = FontSize.Get(context);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet = null;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
Excel::Range range1, range2;
range1 = cellName_Begin == null ? sheet.Cells[cellRow_Begin, cellColumn_Begin] : sheet.Range[cellName_Begin];
range2 = cellName_End == null ? sheet.Cells[cellRow_End, cellColumn_End] : sheet.Range[cellName_End];
Excel::Range range = sheet.Range[range1, range2];
/*对齐设置*/
if ((int)_AlignStyle != 0)
{
range.HorizontalAlignment = (AlignEnum)_AlignStyle;
}
/*字体*/
range.Font.Bold = isBold;
range.Font.Italic = isItalic;
range.Font.Underline = isUnderLine;
if (Font != 0)
{
range.Font.Name = ConvertFont(Font.ToString());
}
range.Font.Size = fontSize;
if ((int)_FontColor != 0)
{
range.Font.ColorIndex = (int)_FontColor;
}
/*填充色*/
if ((int)_CellColor != 0)
{
range.Interior.ColorIndex = (int)_CellColor;
}
/*行列宽度*/
range.RowHeight = rowHeight;
range.ColumnWidth = colWidth;
/*边框*/
if ((int)_BorderStyle != 0)
{
switch ((int)_BorderType)
{
case 0:
{
range.Borders.LineStyle = (int)_BorderStyle;
break;
}
case 1:
{
range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = (int)_BorderStyle;
break;
}
case 2:
{
range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = (int)_BorderStyle;
break;
}
case 3:
{
range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = (int)_BorderStyle;
break;
}
case 4:
{
range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = (int)_BorderStyle;
break;
}
default:
break;
}
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
sheet = null;
range = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL区域设置执行过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context, AsyncCallback callback, object state)
{
PropertyDescriptor property = context.DataContext.GetProperties()[ExcelCreate.GetExcelAppTag];
Excel::Application excelApp = property.GetValue(context.DataContext) as Excel::Application;
try
{
string sheetName = SheetName.Get(context);
string cellName_Begin = CellName_Begin.Get(context);
string cellName_End = CellName_End.Get(context);
int cellRow_Begin = CellRow_Begin.Get(context);
int cellColumn_Begin = CellColumn_Begin.Get(context);
int cellRow_End = CellRow_End.Get(context);
int cellColumn_End = CellColumn_End.Get(context);
Excel::_Worksheet sheet;
if (sheetName != null)
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
else
{
sheet = excelApp.ActiveSheet;
}
Excel::Range range1, range2;
range1 = cellName_Begin == null ? sheet.Cells[cellRow_Begin, cellColumn_Begin] : sheet.Range[cellName_Begin];
range2 = cellName_End == null ? sheet.Cells[cellRow_End, cellColumn_End] : sheet.Range[cellName_End];
Excel::Range range3 = sheet.Range[range1, range2];
System.Data.DataTable dt = new System.Data.DataTable();
int iRowCount = range3.Rows.Count;
int iColCount = range3.Columns.Count;
int rowBegin = range3.Row;
int colBegin = range3.Column;
//生成列头
for (int i = 0; i < iColCount; i++)
{
var name = "column" + i;
if (isTitle)
{
var txt = ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[rowBegin, i + colBegin]).Text.ToString();
if (!string.IsNullOrEmpty(txt))
{
name = txt;
}
}
while (dt.Columns.Contains(name))
{
name = name + "_1";//重复行名称会报错。
}
dt.Columns.Add(new System.Data.DataColumn(name, typeof(string)));
}
//生成行数据
Microsoft.Office.Interop.Excel.Range range;
int rowIdx = isTitle ? 2 : 1;
for (int iRow = rowIdx; iRow <= iRowCount; iRow++)
{
System.Data.DataRow dr = dt.NewRow();
for (int iCol = 1; iCol <= iColCount; iCol++)
{
range = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[iRow + rowBegin - 1, iCol + colBegin - 1];
dr[iCol - 1] = (range.Value2 == null) ? "" : range.Value2;
}
dt.Rows.Add(dr);
}
DataTable.Set(context, dt);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
GC.Collect();
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL读取区域执行过程出错", e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
m_Delegate = new runDelegate(Run);
return(m_Delegate.BeginInvoke(callback, state));
}