Exemplo n.º 1
0
        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));
        }
Exemplo n.º 2
0
        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));
        }
Exemplo n.º 3
0
        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));
        }
Exemplo n.º 4
0
        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));
        }
Exemplo n.º 5
0
        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));
        }
Exemplo n.º 6
0
        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));
        }