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