使用C#,SSIS将数据添加到excel文件 [英] Adding data to an excel file using C# , SSIS
问题描述
我有一个SSIS包,其中我添加了一个脚本任务。在脚本任务中,我给了一个oledb连接管理器并从数据库中获取了一个数据表,查询工作正常。现在我必须将数据表放入excel文件中。这是我需要达到的主要解决方案。
虽然有两种情况
场景1:创建一个没有HEADERS的Excel文件模板并将数据放入该excel文件中(DataTable有标题也包括DataTable的标题
场景2:动态创建一个excel文件并将数据放入其中(我正在寻找这个场景的答案)很长一段时间。
请指导我找到解决方案:)
下面是我在我的脚本任务中实现的代码:
public void Main()
{
DataTable DtUsers = new DataTable();
ConnectionManager ConnManager;
OleDbConnection ConnOledb;
OleDbDataReader dataReader;
string SqlString =MYQUERY;
try
{
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 ConnParams;
ConnManager = Dts.Connections [OledbConnectionUnit];
ConnParams =(Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100)ConnManager.InnerObject;
ConnOledb =(OleDbConnection)ConnParams.GetConnectionForSchema();
OleDbCommand CommOledb = new OleDbCommand();
OleDbDataAdapter DaUsers = new OleDbDataAdapter();
try
{
CommOledb.CommandText = SqlString.ToString();
CommOledb.CommandType = CommandType.Text;
CommOledb.Connection = ConnOledb;
dataReader = CommOledb.ExecuteReader();
DtUsers.Load(dataReader);
foreach(DtUsers.Rows中的DataRow DrRows)
{
DrRows [Column_Name]。ToString();
}
}
catch(Exception ex)
{
throw ex;
}
Dts.TaskResult =(int)ScriptResults.Success;
}
catch(exception ex)
{
throw ex;
}
}
}
提前致谢
Hurrrrraaaaaaayyyyyyyyyy !!!!!!!!!!!!
我自己解决了。我动态创建了excel文件:D我已经用VB来做了。我得到了解决方案。我创建了标题太oooooooo:D。一切都是动态创建的。
进口系统
进口System.Data
进口System.Math
导入Microsoft.SqlServer.Dts.Runtime
导入Microsoft.Office.Interop.Excel
< system.addin.addin(scriptmain,> _
< system.clscompliantattribute(false)> _
部分公共类ScriptMain
继承Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dim cmConnMgr As ConnectionManager
Dim cmParams As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100
Dim ConnOledb As OleDb.OleDbConnection
cmConnMgr = Dts.Connections(OLEDBConn)
cmP arams = CType(cmConnMgr.InnerObject,Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100)
ConnOledb = CType(cmParams.GetConnectionForSchema(),OleDb.OleDbConnection)
Dim strSQL As System.Text.StringBuilder = New System.Text.StringBuilder
Dim drReader As OleDb.OleDbDataReader
Dim cmdCommand As New OleDb.OleDbCommand
Dim dtDetails As New System.Data.DataTable
strSQL.Append(SELECT first_name,email FROM xxxx where user_id< = 2000)
尝试
cmdCommand.CommandText = strSQL.ToString
cmdCommand.CommandType = CommandType.Text
cmdCommand.Connection = ConnOledb
drReader = Nothing
drReader = cmdCommand.ExecuteReader()
如果drReader.HasRows那么
dtDetails.Load(drReader)
结束如果
drReader。关闭()
Catch ex As Exception
drReader = Nothing
结束尝试
MsgBox(dtDetails.Columns.Count)
MsgBox( dtDetails.Rows.Count)
Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass()
Dim Format As XlFileFormat = XlFileFormat.xlExcel8
使用excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()
''创建列标题
Dim i As Integer = 1
for col = 0 To dtDetails.Columns.Count() - 1
.Cells(1,i).value = dtDetails.Columns(col ).ColumnName.ToString
.Cells(1,i).EntireRow.Font.Bold = True
i + = 1
Next
i = 2
Dim k As Integer = 1
col = 0 To dtDetails.Columns.Count() - 1
i = 2
For row = 0 To dtDetails.Rows.Count() - 1
.Cells(i,k).Value = dtDetails.Rows(row).Item(col).ToString
i + = 1
下一个
k + = 1
下一个
.ActiveCell.Worksheet.SaveAs(C:\ testing.xls,Format)
.Workbooks.Close()
End with
excel.Quit()
System.Runtime.InteropServices。 Marshal.ReleaseComObject(excel)
excel = Nothing
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
I have a SSIS package in which I''ve added a Script Task. In the Script Task I''ve given a oledb connection manager and acquired a data table from the database and the query is just working fine. Now I have to put the data table into an excel file. This is the primary solution I need to arrive at.
There are two scenarios though
Scenario 1 : Creating an excel file template WITHOUT HEADERS and put the data into that excel file (DataTable has headers too) including DataTable''s headers
Scenario 2 : Dynamically creating an excel file and put the data into it (I''m searching an answer for this scenario for a long time).
Please guide me through to find a solution :)
Below is my code which I''ve implemented in my Script Task :
public void Main() { DataTable DtUsers = new DataTable(); ConnectionManager ConnManager; OleDbConnection ConnOledb; OleDbDataReader dataReader; string SqlString = "MYQUERY"; try { Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 ConnParams; ConnManager = Dts.Connections["OledbConnectionUnit"]; ConnParams = (Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100)ConnManager.InnerObject; ConnOledb = (OleDbConnection)ConnParams.GetConnectionForSchema(); OleDbCommand CommOledb = new OleDbCommand(); OleDbDataAdapter DaUsers = new OleDbDataAdapter(); try { CommOledb.CommandText = SqlString.ToString(); CommOledb.CommandType = CommandType.Text; CommOledb.Connection = ConnOledb; dataReader=CommOledb.ExecuteReader(); DtUsers.Load(dataReader); foreach (DataRow DrRows in DtUsers.Rows) { DrRows["Column_Name"].ToString(); } } catch (Exception ex) { throw ex; } Dts.TaskResult = (int)ScriptResults.Success; } catch(Exception ex) { throw ex; } } }
Thanks in advance
Hurrrrraaaaaaayyyyyyyyyy!!!!!!!!!!!!
I solved it myself . I created excel file dynamically :D I''ve used VB to do it. I got the solution. I''ve created headers toooooooooo :D . Everything is getting created dynamically.
Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.Office.Interop.Excel <system.addin.addin("scriptmain",> _ <system.clscompliantattribute(false)> _ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum Public Sub Main() Dim cmConnMgr As ConnectionManager Dim cmParams As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 Dim ConnOledb As OleDb.OleDbConnection cmConnMgr = Dts.Connections("OLEDBConn") cmParams = CType(cmConnMgr.InnerObject, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100) ConnOledb = CType(cmParams.GetConnectionForSchema(), OleDb.OleDbConnection) Dim strSQL As System.Text.StringBuilder = New System.Text.StringBuilder Dim drReader As OleDb.OleDbDataReader Dim cmdCommand As New OleDb.OleDbCommand Dim dtDetails As New System.Data.DataTable strSQL.Append("SELECT first_name,email FROM xxxx where user_id <=2000") Try cmdCommand.CommandText = strSQL.ToString cmdCommand.CommandType = CommandType.Text cmdCommand.Connection = ConnOledb drReader = Nothing drReader = cmdCommand.ExecuteReader() If drReader.HasRows Then dtDetails.Load(drReader) End If drReader.Close() Catch ex As Exception drReader = Nothing End Try MsgBox(dtDetails.Columns.Count) MsgBox(dtDetails.Rows.Count) Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass() Dim Format As XlFileFormat = XlFileFormat.xlExcel8 With excel .SheetsInNewWorkbook = 1 .Workbooks.Add() .Worksheets(1).Select() ''Create Column Header Dim i As Integer = 1 For col = 0 To dtDetails.Columns.Count() - 1 .Cells(1, i).value = dtDetails.Columns(col).ColumnName.ToString .Cells(1, i).EntireRow.Font.Bold = True i += 1 Next i = 2 Dim k As Integer = 1 For col = 0 To dtDetails.Columns.Count() - 1 i = 2 For row = 0 To dtDetails.Rows.Count() - 1 .Cells(i, k).Value = dtDetails.Rows(row).Item(col).ToString i += 1 Next k += 1 Next .ActiveCell.Worksheet.SaveAs("C:\testing.xls", Format) .Workbooks.Close() End With excel.Quit() System.Runtime.InteropServices.Marshal.ReleaseComObject(excel) excel = Nothing Dts.TaskResult = ScriptResults.Success End Sub End Class
这篇关于使用C#,SSIS将数据添加到excel文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!