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(); }
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"]); }
/* 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; }
/// <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(); }