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
{
Int32 sheetIndex = SheetIndex.Get(context);
string oldSheetName = OldSheetName.Get(context);
string sheetName = SheetName.Get(context);
Excel::Sheets sheets = excelApp.ActiveWorkbook.Sheets;
if (oldSheetName != "" && oldSheetName != null)
{
sheets.Item[oldSheetName].Name = sheetName;
}
else
{
sheets.Item[sheetIndex].Name = sheetName;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
sheets = null;
GC.Collect();
}
catch (Exception e)
{
new CommonVariable().realaseProcessExit(excelApp);
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL重命名工作表执行过程出错", e.Message);
}
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 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));
}
public void realaseProcess(Excel::Application app)
{
//if (CommonVariable.range != null)
//{
// Marshal.ReleaseComObject(CommonVariable.range);
// CommonVariable.range = null;
//}
//if (CommonVariable._wsh != null)
//{
// Marshal.ReleaseComObject(CommonVariable._wsh);
// CommonVariable._wsh = null;
//}
//if (CommonVariable.shs != null)
//{
// Marshal.ReleaseComObject(CommonVariable.shs);
// CommonVariable.shs = null;
//}
//if (CommonVariable._wbk != null)
//{
// Marshal.ReleaseComObject(CommonVariable._wbk);
// CommonVariable._wbk = null;
//}
//if (CommonVariable.wbks != null)
//{
// Marshal.ReleaseComObject(CommonVariable.wbks);
// CommonVariable.wbks = null;
//}
if (app != null)
{
Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();
}
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
{
m_Delegate = new runDelegate(Run);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
object data = new object();
Excel.Range xCopyFromRange = null;
Int32 rowColNum = RowColNum.Get(context);
if (CurrReadType == ReadType.读取行)
{
data = sheet.Rows[rowColNum].Value;
xCopyFromRange = sheet.Rows[rowColNum] as Excel.Range;
xCopyFromRange.Copy();
}
else
{
data = sheet.Columns[rowColNum].Value;
xCopyFromRange = sheet.Columns[rowColNum] as Excel.Range;
xCopyFromRange.Copy();
}
//Collection<object> _data = new Collection<object>();
//foreach (object cell in ((object[,])data))
//{
// if (cell != null)
// {
// _data.Add(cell);
// }
// else
// break;
//}
RolColData.Set(context, data);
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);
}
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);
int cellRow_Begin = CellRow_Begin.Get(context);
int cellColumn_Begin = CellColumn_Begin.Get(context);
System.Data.DataTable dt = DataTable.Get(context);
Excel::_Worksheet sheet;
if (sheetName != null)
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
else
{
sheet = excelApp.ActiveSheet;
}
Excel::Range range = cellName_Begin == null ? sheet.Cells[cellRow_Begin, cellColumn_Begin] : sheet.Range[cellName_Begin];
int iRowBegin = range.Row;
int iColBegin = range.Column;
int i = 0;
if (isTitle)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
sheet.Cells[iRowBegin + i, j + iColBegin] = dt.Columns[j].ColumnName;
}
i++;
}
for (int a = 0; a <= dt.Rows.Count - 1; a++)
{
for (int j = 0; j <= dt.Columns.Count - 1; j++)
{
sheet.Cells[iRowBegin + i + a, j + iColBegin] = dt.Rows[a][j];
}
}
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));
}
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
{
m_Delegate = new runDelegate(Run);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
int rowCounts = 0, colCounts = 0;
//有效行列数 不包含中间的空行
if (isValid)
{
rowCounts = sheet.UsedRange.Rows.Count;
colCounts = sheet.UsedRange.Columns.Count;
}
//空行/列截止
else if (isActive)
{
rowCounts = sheet.UsedRange.CurrentRegion.Rows.Count;
colCounts = sheet.UsedRange.CurrentRegion.Columns.Count;
}
else if (isSingleLine)
{
rowCounts = sheet.get_Range("A65535").get_End(Excel.XlDirection.xlUp).Row;
colCounts = sheet.get_Range("IV1").get_End(Excel.XlDirection.xlToLeft).Column;
}
RowCounts.Set(context, rowCounts);
ColCounts.Set(context, colCounts);
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);
}
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);
//string sheetName = SheetName.Get(context);
//Excel::_Worksheet sheet;
//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::_Worksheet sheet;
Excel::Range range1, range2;
RangeFunction.GetRange(excelApp, context, SheetName, CellName_Begin, CellName_End,
CellRow_Begin, CellColumn_Begin, CellRow_End, CellColumn_End,
out sheet, out range1, out range2);
if (cellMergeOrUnMerge == CellMergeOrUnMerge.合并单元格)
{
sheet.Range[range1, range2].Merge();
}
else
{
sheet.Range[range1, range2].UnMerge();
}
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));
}
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
{
Int32 cellColumn = CellColumn.Get(context);
Int32 cellRow = CellRow.Get(context);
string cellName = CellName.Get(context);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
object cellContent = null;
if (cellName == null)
{
cellContent = sheet.Cells[cellRow, cellColumn].Value2;
}
else
{
cellContent = sheet.Range[cellName].Value2;
}
CellContent.Set(context, cellContent);
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));
}
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);
excelApp.Worksheets.Add();
excelApp.ActiveSheet.Name = sheetName;
}
catch (Exception e)
{
new CommonVariable().realaseProcessExit(excelApp);
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL增加新工作表执行过程出错", e.Message);
}
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
{
m_Delegate = new runDelegate(Run);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
object data = new object();
Int32 rowColNum = RowColNum.Get(context);
if (CurrInsertType == InsertType.制行)
{
sheet.Rows[rowColNum].Value = RolColData.Get(context);
}
else
{
sheet.Columns[rowColNum].Value = RolColData.Get(context);
}
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);
}
return(m_Delegate.BeginInvoke(callback, state));
}
public void RunExcelMacro(Excel::Application excelApp, string macroName, object[] parameters, out object rtnValue)
{
object oMissing = System.Reflection.Missing.Value;
object[] paraObjects;
if (parameters == null)
{
paraObjects = new object[] { macroName };
}
else
{
// 宏参数组长度
int paraLength = parameters.Length;
paraObjects = new object[paraLength + 1];
paraObjects[0] = macroName;
for (int i = 0; i < paraLength; i++)
{
paraObjects[i + 1] = parameters[i];
}
}
rtnValue = "";
try
{
rtnValue = excelApp.GetType().InvokeMember(
"Run",
System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod,
null,
excelApp,
paraObjects
);
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "宏执行异常,请检查宏名称与参数是否匹配", e.Message);
}
Excel._Workbook oBook = excelApp.ActiveWorkbook;
oBook.Save();
}
protected override void Execute(NativeActivityContext context)
{
try
{
Thread.Sleep(DelayBefore.Get(context));
string filePath = PathUrl.Get(context);
excelApp = new Excel::Application();
excelApp.Visible = IsVisible;
excelApp.DisplayAlerts = false;
if (_NewDoc == true)
{
excelApp.Workbooks.Add(true);
}
else
{
if (!File.Exists(filePath))
{
// 文件不存在，请检查路径有效性
SharedObject.Instance.Output(SharedObject.enOutputType.Error, Localize.LocalizedResources.GetString("xFileNotExist"));
new CommonVariable().realaseProcessExit(excelApp);
return;
}
else
{
//可用Open或Add函数打开文件，但对于执行VBA，Add无保存权限
excelApp.Workbooks.Open(filePath);
}
}
context.ScheduleAction(Body, excelApp, OnCompleted, OnFaulted);
}
catch (Exception e)
{
// EXCEL执行过程出错
SharedObject.Instance.Output(SharedObject.enOutputType.Error, Localize.LocalizedResources.GetString("xExcelExecError"), e.Message);
new CommonVariable().realaseProcessExit(excelApp);
}
}
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
{
List <string> list = new List <string>();
for (int i = 1; i < excelApp.ActiveWorkbook.Worksheets.Count + 1; i++)
{
list.Add(excelApp.ActiveWorkbook.Worksheets[i].Name);
}
SheetNames.Set(context, list.ToArray());
}
catch (Exception e)
{
new CommonVariable().realaseProcessExit(excelApp);
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL增加新工作表执行过程出错", e.Message);
}
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
{
if (string.IsNullOrEmpty(VBAName.Get(context)))
{
throw new System.Exception("请输入宏的名称");
}
object returnValue;
string macroName = VBAName.Get(context);
object[] parameters = null;
if (Parameters != null)
{
List <object> paraList = new List <object>();
foreach (var param in Parameters)
{
paraList.Add(param.Value.Get(context));
}
parameters = paraList.ToArray();
}
RunExcelMacro(excelApp, macroName, parameters, out returnValue);
if (returnValue != null)
{
ReturnValue.Set(context, returnValue);
}
}
catch (Exception e)
{
new CommonVariable().realaseProcessExit(excelApp);
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL执行VBA出错", e.Message);
}
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::_Worksheet sheet;
Excel::Range range1, range2;
RangeFunction.GetRange(excelApp, context, SheetName, CellName_Begin, CellName_End,
CellRow_Begin, CellColumn_Begin, CellRow_End, CellColumn_End,
out sheet, out range1, out range2);
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.Text.ToString();
}
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));
}
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));
}
/// <summary>
/// 将数据表保存到Excel表格中
/// </summary>
/// <param name="addr">Excel表格存放地址（程序运行目录后面的部分）</param>
/// <param name="dt">要输出的DataTable</param>
public static void SaveToExcel(string addr, System.Data.DataTable dt)
{
//0.注意：
// * Excel中形如Cells[x][y]的写法，前面的数字是列，后面的数字是行!
// * Excel中的行、列都是从1开始的，而不是0
//1.制作一个新的Excel文档实例
Excel::Application xlsApp = new Excel::Application();
xlsApp.Workbooks.Add(true);
/* 示例输入：需要注意Excel里数组以1为起始（而不是0）
* for (int i = 1; i < 10; i++)
* {
* for (int j = 1; j < 10; j++)
* {
* xlsApp.Cells[i][j] = "-";
* }
* }
*/
//2.设置Excel分页卡标题
xlsApp.ActiveSheet.Name = dt.TableName;
//3.合并第一行的单元格
string temp = "";
if (dt.Columns.Count < 26)
{
temp = ((char)('A' + dt.Columns.Count)).ToString();
}
else if (dt.Columns.Count <= 26 + 26 * 26)
{
temp = ((char)('A' + (dt.Columns.Count - 26) / 26)).ToString()
+ ((char)('A' + (dt.Columns.Count - 26) % 26)).ToString();
}
else
{
throw new Exception("列数过多");
}
Excel::Range range = xlsApp.get_Range("A1", temp + "1");
range.ClearContents(); //清空要合并的区域
range.MergeCells = true; //合并单元格
//4.填写第一行：表名，对应DataTable的TableName
xlsApp.Cells[1][1] = dt.TableName;
xlsApp.Cells[1][1].Font.Name = "黑体";
xlsApp.Cells[1][1].Font.Size = 25;
xlsApp.Cells[1][1].Font.Bold = true;
xlsApp.Cells[1][1].HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //居中
xlsApp.Rows[1].RowHeight = 60; //第一行行高为60（单位：磅）
//5.合并第二行单元格，用于书写表格生成日期
range = xlsApp.get_Range("A2", temp + "2");
range.ClearContents(); //清空要合并的区域
range.MergeCells = true; //合并单元格
//6.填写第二行：生成时间
xlsApp.Cells[1][2] = "报表生成于：" + DateTime.Now.ToString();
xlsApp.Cells[1][2].Font.Name = "宋体";
xlsApp.Cells[1][2].Font.Size = 15;
//xlsApp.Cells[1][2].HorizontalAlignment = 4;//右对齐
xlsApp.Cells[1][2].HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //居中
xlsApp.Rows[2].RowHeight = 30; //第一行行高为60（单位：磅）
//7.填写各列的标题行
xlsApp.Cells[1][3] = "序号";
for (int i = 0; i < dt.Columns.Count; i++)
{
xlsApp.Cells[i + 2][3] = dt.Columns[i].ColumnName;
}
xlsApp.Rows[3].Font.Name = "宋体";
xlsApp.Rows[3].Font.Size = 15;
xlsApp.Rows[3].Font.Bold = true;
xlsApp.Rows[3].HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //居中
//设置颜色
range = xlsApp.get_Range("A3", temp + "3");
range.Interior.ColorIndex = 33;
//8.填写DataTable中的数据
for (int i = 0; i < dt.Rows.Count; i++)
{
xlsApp.Cells[1][i + 4] = i.ToString();
for (int j = 0; j < dt.Columns.Count; j++)
{
xlsApp.Cells[j + 2][i + 4] = dt.Rows[i][j];
}
}
range = xlsApp.get_Range("A4", temp + (dt.Rows.Count + 3).ToString());
range.Interior.ColorIndex = 37;
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
//9.描绘边框
range = xlsApp.get_Range("A1", temp + (dt.Rows.Count + 3).ToString());
range.Borders.LineStyle = 1;
range.Borders.Weight = 3;
//10.打开制作完毕的表格
//xlsApp.Visible = true;
//11.保存表格到根目录下指定名称的文件中
xlsApp.ActiveWorkbook.SaveAs(addr);
xlsApp.Quit();
xlsApp = null;
GC.Collect();
}
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
{
m_Delegate = new runDelegate(Run);
string sheetName = SheetName.Get(context);
Excel::_Worksheet sheet;
if (sheetName == null)
{
sheet = excelApp.ActiveSheet;
}
else
{
sheet = excelApp.ActiveWorkbook.Sheets[sheetName];
}
Int32 rowColBegin = RowColBegin.Get(context);
Int32 rowColEnd = RowColEnd.Get(context);
if (rowColBegin > rowColEnd)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "EXCEL行列操作开始值不能大于结束值");
}
switch ((int)Operation)
{
case 1:
{
if (RowColSel == (RowCols)1)
{
sheet.Range[
sheet.Cells[rowColBegin, 1],
sheet.Cells[rowColEnd, sheet.Columns.Count]].
Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
}
else
{
sheet.Range[
sheet.Cells[1, rowColBegin],
sheet.Cells[sheet.Rows.Count, rowColEnd]]
.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
}
break;
}
case 2:
{
if (RowColSel == (RowCols)1)
{
sheet.Range[
sheet.Cells[rowColBegin, 1],
sheet.Cells[rowColEnd, 1]].
EntireRow.Hidden = true;
}
else
{
sheet.Range[
sheet.Cells[1, rowColBegin],
sheet.Cells[sheet.Rows.Count, rowColEnd]].
EntireColumn.Hidden = true;
}
break;
}
case 3:
{
if (RowColSel == (RowCols)1)
{
sheet.Range[
sheet.Cells[rowColBegin, 1],
sheet.Cells[rowColEnd, sheet.Columns.Count]].
Insert(Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
}
else
{
sheet.Range[
sheet.Cells[1, rowColBegin],
sheet.Cells[sheet.Rows.Count, rowColEnd]].
Insert(Excel.XlInsertShiftDirection.xlShiftToRight);
}
break;
}
default:
return(m_Delegate.BeginInvoke(callback, state));
}
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);
}
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 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));
}
/// <summary>
/// 将数据表保存到Excel表格中
/// </summary>
/// <param name="addr">Excel表格存放地址（程序运行目录后面的部分）</param>
/// <param name="dt">要输出的DataTable</param>
public void SaveToExcel(string addr, System.Data.DataTable dt)
{
//0.注意：
// * Excel中形如Cells[x][y]的写法，前面的数字是列，后面的数字是行!
// * Excel中的行、列都是从1开始的，而不是0
//1.制作一个新的Excel文档实例
Excel::Application xlsApp = new Excel::Application();
xlsApp.Workbooks.Add(true);
/* 示例输入：需要注意Excel里数组以1为起始（而不是0）
* for (int i = 1; i < 10; i++)
* {
* for (int j = 1; j < 10; j++)
* {
* xlsApp.Cells[i][j] = "-";
* }
* }
*/
//2.设置Excel分页卡标题
xlsApp.ActiveSheet.Name = "表格";
//xlsApp.ActiveSheet.Name = dt.TableName;
//3.合并第一行的单元格
string temp = "";
if (dt.Columns.Count < 26)
{
temp = ((char)('A' + dt.Columns.Count)).ToString();
}
else if (dt.Columns.Count <= 26 + 26 * 26)
{
temp = ((char)('A' + (dt.Columns.Count - 26) / 26)).ToString()
+ ((char)('A' + (dt.Columns.Count - 26) % 26)).ToString();
}
else
{
throw new Exception("列数过多");
}
Excel::Range range = xlsApp.get_Range("A1", temp + "1");
range.ClearContents(); //清空要合并的区域
range.MergeCells = true; //合并单元格
//4.填写第一行：表名，对应DataTable的TableName
//xlsApp.Cells[1][1] = dt.TableName;
xlsApp.Cells[1][1] = "零件检查报表";
xlsApp.Cells[1][1].Font.Name = "黑体";
xlsApp.Cells[1][1].Font.Size = 25;
xlsApp.Cells[1][1].Font.Bold = true;
xlsApp.Cells[1][1].HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //居中
xlsApp.Rows[1].RowHeight = 60; //第一行行高为60（单位：磅）
//5.合并第二行单元格，用于书写表格生成日期
range = xlsApp.get_Range("A2", temp + "2");
range.ClearContents(); //清空要合并的区域
range.MergeCells = true; //合并单元格
//6.填写第二行：生成时间
xlsApp.Cells[1][2] = "报表生成于：" + DateTime.Now.ToString();
xlsApp.Cells[1][2].Font.Name = "宋体";
xlsApp.Cells[1][2].Font.Size = 15;
//xlsApp.Cells[1][2].HorizontalAlignment = 4;//右对齐
xlsApp.Cells[1][2].HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //居中
xlsApp.Rows[2].RowHeight = 30; //第一行行高为60（单位：磅）
//7.填写各列的标题行
xlsApp.Cells[1][3] = "序号";
for (int i = 0; i < dt.Columns.Count; i++)
{
xlsApp.Cells[i + 2][3] = dt.Columns[i].ColumnName + 1;
}
xlsApp.Rows[3].Font.Name = "宋体";
xlsApp.Rows[3].Font.Size = 15;
xlsApp.Rows[3].Font.Bold = true;
xlsApp.Rows[3].HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; //居中
//设置颜色
range = xlsApp.get_Range("A3", temp + "3");
// range.Interior.ColorIndex = 33;
// xlsApp.Columns[1].ColumnWidth = 20;
// xlsApp.Columns[1].HorizontalAlignment = Excel.XlVAlign.xlVAlignJustify;
xlsApp.Columns[2].ColumnWidth = 20;
xlsApp.Columns[2].NumberFormat = "@";
// xlsApp.Columns[2].HorizontalAlignment = Excel.
xlsApp.Columns[3].ColumnWidth = 20;
//xlsApp.Columns[4].ColumnWidth = 20;
xlsApp.Columns[3].NumberFormat = "@";
//xlsApp.Columns[2].NumberFormat = "@";
int cur_lie = dataGridView1.ColumnCount;
xlsApp.Columns[cur_lie + 1].NumberFormat = "@";
xlsApp.Columns[cur_lie + 1].ColumnWidth = 20;
//8.填写DataTable中的数据
for (int i = 0; i < dt.Rows.Count; i++)
{
xlsApp.Cells[1][i + 4] = (1 + i).ToString();
for (int j = 0; j < dt.Columns.Count; j++)
{
xlsApp.Cells[j + 2][i + 4] = dt.Rows[i][j];
if (j < dataGridView1.ColumnCount)
{
if (dataGridView1.Rows[i].Cells[j].Style.BackColor == Color.LightSalmon)
{
xlsApp.Cells[j + 2][i + 4].Font.ColorIndex = 3;
}
if (dataGridView1.Rows[i].Cells[j].Style.BackColor == Color.Cyan)
{
xlsApp.Cells[j + 2][i + 4].Font.ColorIndex = 23;
}
if (dataGridView1.Rows[i].Cells[j].Style.BackColor == Color.Lime)
{
xlsApp.Cells[j + 2][i + 4].Font.ColorIndex = 50;
}
if (dataGridView1.Rows[i].Cells[j].Value.ToString() == "NG")
{
xlsApp.Cells[j + 2][i + 4].Interior.ColorIndex = 3;
}
}
// Console.WriteLine("color : %s", dgv.Rows[i].Cells[j].Style.ForeColor);
}
}
range = xlsApp.get_Range("A4", temp + (dt.Rows.Count + 3).ToString());
// range.Interior.ColorIndex = 37;
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
//9.描绘边框
range = xlsApp.get_Range("A1", temp + (dt.Rows.Count + 3).ToString());
range.Borders.LineStyle = 1;
//range.Borders.Weight = 3;
//10.打开制作完毕的表格
//xlsApp.Visible = true;
//11.保存表格到根目录下指定名称的文件中
//xlsApp.ActiveWorkbook.SaveAs(Application.StartupPath + "/" + addr);
xlsApp.ActiveWorkbook.SaveAs(addr);
xlsApp.Quit();
xlsApp = null;
GC.Collect();
}
