摘要:C# 產生EXECL檔案
下面的例子有OPEN一個已經做好美化的範例EXECL檔,再由這個檔槷另存新檔 因為這個c#程式是在ssis的script task寫的
所以變取得的方式是 Dts.Variables["User::xxxxx"].Value.ToString() 變數可以改成您自己要的變數值
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Drawing;
using System.Runtime.InteropServices;
namespace ST_5c52bc7d213249f6b280fcb9c498d659.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
string FilePath = Dts.Variables["User::FilePath"].Value.ToString().Trim();
try
{
ConnectionManager cm;
System.Data.SqlClient.SqlConnection sqlConn;
cm = Dts.Connections["ADONET.ooo"];
sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);
SqlCommand com = new SqlCommand("SP_NAME", sqlConn);
com.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter Total_CNT = com.Parameters.Add("@Total_CNT", SqlDbType.Int );
Total_CNT.Direction = System.Data.ParameterDirection.Output;
SqlParameter Total_SUM = com.Parameters.Add("@Total_SUM", SqlDbType.Decimal, 18);
Total_SUM.Direction = System.Data.ParameterDirection.Output;
SqlParameter RETURNCODE = com.Parameters.Add("@RETURNCODE", SqlDbType.SmallInt );
RETURNCODE.Direction = System.Data.ParameterDirection.Output;
SqlParameter RETURNMSG = com.Parameters.Add("@RETURNMSG", SqlDbType.VarChar,100);
RETURNMSG.Direction = System.Data.ParameterDirection.Output;
SqlDataReader reader = com.ExecuteReader();
System.Data.DataTable dtQryData = new System.Data.DataTable();
dtQryData.Load(reader);
reader.Close();
string TempFilePathName = Dts.Variables["User::TempFilePathName"].Value .ToString();
Microsoft.Office.Interop.Excel.Application m_objExcel = new Microsoft.Office.Interop.Excel.Application();
Workbook m_objBooks = m_objExcel.Workbooks.Open(TempFilePathName, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Worksheet m_objWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBooks.Sheets[1];
m_objExcel.DisplayAlerts = false; m_objExcel.Visible = false; m_objExcel.UserControl = false;
//報表日期
string strDate_Btn = DateTime.Now.AddDays(-11).ToString("yyyy/MM/dd") +"~" + DateTime.Now.AddDays(-5).ToString("yyyy/MM/dd");
//表頭
m_objWorkSheet.Cells[3, 4] = "日期：" + strDate_Btn;
if (dtQryData.Rows.Count == 0)
{
m_objWorkSheet.Cells[5, 4] = "查無資料!!";
Microsoft.Office.Interop.Excel.Range excelRange = (Range)m_objWorkSheet.get_Range(m_objWorkSheet.Cells[5, 4], m_objWorkSheet.Cells[5, 4]);
excelRange.Cells.Font.Bold = true;//粗體字
}
else
{
//將整個DataTable貼到EXECL
for (int i = 0; i < dtQryData.Rows.Count; i++)
{
for (int j = 0; j < dtQryData.Columns.Count; j++)
{
m_objWorkSheet.Cells[i + 5, j + 2] = dtQryData.Rows[i][j].ToString();
}
if (dtQryData.Rows[i][1].ToString() == "小計")
{
Microsoft.Office.Interop.Excel.Range excelRange = (Range)m_objWorkSheet.get_Range(m_objWorkSheet.Cells[i + 5, 2], m_objWorkSheet.Cells[i + 5, 5]);
excelRange.Cells.Font.Bold = true;//粗體字
excelRange.Cells.Interior.Color = System.Drawing.Color.FromArgb(234, 241, 221).ToArgb();//背景顏色
}
}
//表尾
m_objWorkSheet.Cells[dtQryData.Rows.Count + 5, 2] = strDate_Btn + "總計 " + Convert.ToDecimal(Total_SUM.Value).ToString("#,0") + " 元";
Microsoft.Office.Interop.Excel.Range Range = (Range)m_objWorkSheet.get_Range(m_objWorkSheet.Cells[dtQryData.Rows.Count + 5, 2], m_objWorkSheet.Cells[dtQryData.Rows.Count + 5, 5]);
Range.Cells.HorizontalAlignment = Constants.xlLeft; //字體靠左
Range.Cells.Font.Bold = true; //粗體字
Range.Cells.Interior.Color = System.Drawing.Color.FromArgb(154, 214, 194).ToArgb(); //背景顏色
}
//把sheet存成另一個檔案
if (!Directory.Exists(FilePath))
{
DirectoryInfo di = Directory.CreateDirectory(FilePath);
}
//另存檔名路徑
string FileName = FilePath + "oooo_" + DateTime.Today.ToString("yyyymmdd") + ".xlsx";
m_objBooks.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange
, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
m_objBooks.Close(false, Type.Missing, Type.Missing);
m_objExcel.Quit();
Marshal.FinalReleaseComObject(m_objWorkSheet);
Marshal.FinalReleaseComObject(m_objBooks);
Marshal.FinalReleaseComObject(m_objExcel);
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (Exception ex)
{
Dts.Variables["User::ErrMsg"].Value = ex.ToString();
Dts.TaskResult = (int)ScriptResults.Failure;
return;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
