Example #1
0
        private void button4_Click(object sender, EventArgs e)
        {
            string _strFileName = @"r:\abcd.xls";

            MSOffice.Application App    = new MSOffice.Application();
            MSOffice.Workbook    Book   = App.Workbooks.Add(MSOffice.XlWBATemplate.xlWBATWorksheet);
            MSOffice.Worksheet   Sheet  = (MSOffice.Worksheet)Book.ActiveSheet;
            MSOffice.Worksheets  sheets = (MSOffice.Worksheets)Book.Sheets;

            App.Visible       = false;
            App.DisplayAlerts = false;

            object obj = App.Caller[1];

            obj = App.Charts;
            obj = App.ClipboardFormats;
            obj = App.CutCopyMode;
            obj = App.StatusBar;
            obj = App.Workbooks[1];
            obj = Book.ActiveChart;
            obj = Sheet.PageSetup.FirstPage.CenterFooter.Picture.Application;
            obj = Sheet.Range["", ""].Font.Application;
            obj = Sheet.Range["", ""].Borders[MSOffice.XlBordersIndex.xlDiagonalUp].Application;
            obj = App.Windows[""].SheetViews[""];
            obj = App.Charts;
            obj = Sheet.Names;
            obj = Sheet.Names.Item(1);

            Sheet.Copy();

            MSOffice.Areas a = null;
            obj = a.Application;
            // MSOffice.Hyperlink

            App.Workbooks.Open("");
            //     _xlsApp.Selection
            App.Windows[1].Zoom = 150;
            //xlsSheet.Rows[1, 1];
            MSOffice.Range rng = Sheet.Range[1, 2];


            MSOffice.Shape shape = Sheet.Shapes.AddShape(Microsoft.Office.Core.MsoAutoShapeType.msoShapeRectangle, 0, 48, 72, 60);
            shape.Fill.Visible    = Microsoft.Office.Core.MsoTriState.msoFalse;
            shape.Shadow.Obscured = Microsoft.Office.Core.MsoTriState.msoCTrue;
            shape.Shadow.Type     = Microsoft.Office.Core.MsoShadowType.msoShadow18;
            shape.Fill.UserPicture(@"r:\test.jpg");

            //xlsSheet.Shapes.AddOLEObject
            //shape.GroupItems.Item
            //shape.Glow.Color

            Sheet.get_Range("");

            App.Windows[1].Height = 100;

            textBox1.Text = App.Version;

            App.Quit();
        }
Example #2
0
 public static Excel.Range CopyPivotTable(Excel.PivotTable pt)
 {
     Excel.Application XlApp       = pt.Application;
     Excel.Worksheet   sourceSheet = (Excel.Worksheet)pt.Parent;
     sourceSheet.Select();
     pt.PivotSelect("", Excel.XlPTSelectionMode.xlDataAndLabel, true);
     Excel.Range sourceRange = (Excel.Range)XlApp.Selection;
     sourceRange.Copy();
     Excel.Worksheets sheets    = (Excel.Worksheets)XlApp.Sheets;
     Excel.Worksheet  destSheet = (Excel.Worksheet)sheets.Add();
     destSheet.Paste();
     return(destSheet.Range["A1"]);
 }
Example #3
0
        /* To use a variable in this script, first ensure that the variable has been added to
         * either the list contained in the ReadOnlyVariables property or the list contained in
         * the ReadWriteVariables property of this script task, according to whether or not your
         * code needs to write to the variable.  To add the variable, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and
         * ReadWriteVariables properties in the Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         *
         * Example of reading from a variable:
         *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
         *
         * Example of writing to a variable:
         *  Dts.Variables["User::myStringVariable"].Value = "new value";
         *
         * Example of reading from a package parameter:
         *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;
         *
         * Example of reading from a project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;
         *
         * Example of reading from a sensitive project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
         * */

        #endregion

        #region Help:  Firing Integration Services events from a script

        /* This script task can fire events for logging purposes.
         *
         * Example of firing an error event:
         *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
         *
         * Example of firing an information event:
         *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
         *
         * Example of firing a warning event:
         *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
         * */
        #endregion

        #region Help:  Using Integration Services connection managers in a script

        /* Some types of connection managers can be used in this script task.  See the topic
         * "Working with Connection Managers Programatically" for details.
         *
         * Example of using an ADO.Net connection manager:
         *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
         *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
         *
         * Example of using a File connection manager
         *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
         *  string filePath = (string)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
         * */
        #endregion


        /// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
        ///

        public void Main()
        {
            string SMTPServer = "smtp.com";
            string MailFrom   = "*****@*****.**";
            string MailTo     = "*****@*****.**";
            string MailCC     = "*****@*****.**";

            string Database = Dts.Variables["User::Database"].Value.ToString();
            string Server   = Dts.Variables["User::Server"].Value.ToString();

            string SQLConnString = @"Data Source=" + Server + ";Initial Catalog=" + Database + ";Integrated Security=SSPI;"; // SQL Connection string

            Excel.Application ExcelApp = new Excel.Application();                                                            // Initialize Excel Application
            ExcelApp.DisplayAlerts = false;
            string TemplateFilewithPath = Dts.Variables["User::FILE_TemplateFolder"].Value.ToString() + Dts.Variables["User::FILE_FileName"].Value.ToString();

            Excel.Workbook   WB = ExcelApp.Workbooks.Open(TemplateFilewithPath);                       // Initialize Excel Workbook
            Excel.Worksheets WS = ExcelApp.ActiveSheet as Excel.Worksheets;                            // Initialize Excel Worksheet

            CallManagerData_To_Excel(ExcelApp, WB, MailFrom, MailTo, MailCC, SMTPServer);              // Write CallManager data to Template

            string FinalOutputFileName = CreateFileFormTemplate();                                     // Create a copy of from Template file to Output file

            WB = ExcelApp.Workbooks.Open(FinalOutputFileName);                                         // Initialize Excel Workbook with output file
            WS = ExcelApp.ActiveSheet as Excel.Worksheets;                                             // Initialize Excel Worksheet with output file

            Notifications_To_Excel(ExcelApp, WB, SQLConnString, MailFrom, MailTo, MailCC, SMTPServer); // Export data from SQL Server to Excel
            Occurences_To_Excel(ExcelApp, WB, SQLConnString, MailFrom, MailTo, MailCC, SMTPServer);    // Export data from SQL Server to Excel
            Settelments_To_Excel(ExcelApp, WB, SQLConnString, MailFrom, MailTo, MailCC, SMTPServer);   // Export data from SQL Server to Excel
            Outstandings_To_Excel(ExcelApp, WB, SQLConnString, MailFrom, MailTo, MailCC, SMTPServer);  // Export data from SQL Server to Excel

            WB.Save();
            WB.Close(0);
            ExcelApp.Quit();
            GC.Collect();

            Dts.TaskResult = (int)ScriptResults.Success;
        }
Example #4
0
 /// <summary>
 /// 释放Excel对应的对象资源
 /// </summary>
 public void Dispose()
 {
     //释放Excel对应的对象
     if (mRange != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(mRange);
         mRange = null;
     }
     if (mCurWorkSheet != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(mCurWorkSheet);
         mWorkSheets = null;
     }
     if (mWorkSheets != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(mWorkSheets);
         mWorkSheets = null;
     }
     if (mWorkBook != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(mWorkBook);
         mWorkBook = null;
     }
     if (mWorkBooks != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(mWorkBooks);
         mWorkBooks = null;
     }
     mXLApp.Application.Workbooks.Close();
     mXLApp.Quit();
     if (mXLApp != null)
     {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(mXLApp);
         mXLApp = null;
     }
     GC.Collect();
 }