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