完整的代码如下:
using System;
using System.Data;
using System.Collections;
using System.Data.SqlClient;
using System.IO;
namespace DtsExcel
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
//****************************
//Create by chx of Telegnosis
//2007-3
//****************************
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 通过Dts把SQL数据库的数据导入到EXCEL
/// </summary>
///
public class AppExportData
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//DTS包对象
public DTS.Package2Class ObjDTS;
//源文件路径
private string dataBase;
//目标文件路径
private string strDestinationFilePath;
//导出数据的SQL语句
private string strSQL;
//目标表名
private string strTableName;
//得到目标列名
private ArrayList arrDestinationColumns;
//得到源数据列名
private ArrayList arrSourceColumns;
//用户名
private string connectionString;
//SQL服务器地址
private string server;
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
///数据库连接字符
/// </summary>
public string Server
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return server;
}
![](/Images/OutliningIndicators/InBlock.gif)
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
server = value;
}
}
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
///数据库连接字符
/// </summary>
public string ConnectionString
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return connectionString;
}
![](/Images/OutliningIndicators/InBlock.gif)
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
connectionString = value;
}
}
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
私有属性#region 私有属性
private ArrayList SourceColumns
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return arrSourceColumns;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
arrSourceColumns = value;
}
}
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 得到目标列名
/// </summary>
private ArrayList DestinationColumns
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return arrDestinationColumns;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
arrDestinationColumns = value;
}
}
#endregion
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
公共属性#region 公共属性
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 目标表名
/// </summary>
public string TableName
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return strTableName;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strTableName = value;
}
}
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 导出数据的SQL语句
/// </summary>
public string SQL
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return strSQL;
}
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strSQL = value;
}
}
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 源文件所在的路径
/// </summary>
public string DataBase
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return dataBase;
}
![](/Images/OutliningIndicators/InBlock.gif)
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
dataBase = value;
}
}
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 目标文件所在的路径
/// </summary>
public string DestinationFilePath
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
get
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return strDestinationFilePath;
}
![](/Images/OutliningIndicators/InBlock.gif)
set
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strDestinationFilePath = value;
}
}
#endregion
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
构造函数#region 构造函数
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 构造函数
/// </summary>
public AppExportData(string StrDesPath,string TableName,string StrSql,string ConnectionString)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strTableName = "结果";
arrDestinationColumns = new ArrayList();
arrSourceColumns = new ArrayList();
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string[] str=ConnectionString.Split(new char[]
{';'});
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string[] strserver=str[0].Split(new char []
{'='});
server=strserver[1];
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string[] struid=str[1].Split(new char []
{'='});
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string[] strdatabase=str[3].Split(new char []
{'='});
dataBase=strdatabase[1];
dataBase=DataBase;
strDestinationFilePath=StrDesPath;
CreateDity(StrDesPath);
![](/Images/OutliningIndicators/InBlock.gif)
strTableName=TableName;
strSQL=StrSql;
connectionString=ConnectionString;
server=Server;
}
![](/Images/OutliningIndicators/InBlock.gif)
#endregion
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
创建文件夹#region 创建文件夹
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 创建文件夹
/// </summary>
/// <param name="Path"></param>
public void CreateDity(string Path)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if(!File.Exists(Path))
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
string[] dirty=Path.Split(new char[]
{'\\'});
string path=dirty[0];
for(int i=1;i<dirty.Length-1;i++)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
path+="\\"+dirty[i];
Directory.CreateDirectory(path);
}
}
}
catch(Exception ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw(ex);
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/InBlock.gif)
}
#endregion
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
导出的全过程#region 导出的全过程
public bool ExportData()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
ObjDTS = new DTS.Package2Class();
![](/Images/OutliningIndicators/InBlock.gif)
if ( this.arrDestinationColumns.Count == 0 || this.arrSourceColumns.Count == 0)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
try
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.GetColumns();
}
catch(Exception ex)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw(ex);
}
}
//新建一个新的DTS包,设置它的属性
ObjDTS.Name = "新建包";
ObjDTS.Description = "DTS 包描述";
ObjDTS.WriteCompletionStatusToNTEventLog = false;
ObjDTS.FailOnError = false;
ObjDTS.PackagePriorityClass = ( DTS.DTSPackagePriorityClass )2;
ObjDTS.MaxConcurrentSteps = 4;
ObjDTS.LineageOptions = 0;
ObjDTS.UseTransaction = true;
ObjDTS.TransactionIsolationLevel = ( DTS.DTSIsolationLevel )4096;
ObjDTS.AutoCommitTransaction = true;
ObjDTS.RepositoryMetadataOptions = 0;
ObjDTS.UseOLEDBServiceComponents = true;
ObjDTS.LogToSQLServer = false;
ObjDTS.LogServerFlags = 0;
ObjDTS.FailPackageOnLogFailure = false;
ObjDTS.ExplicitGlobalVariables = false;
ObjDTS.PackageType = 0;
![](/Images/OutliningIndicators/InBlock.gif)
//建立SQL的连接,设置其属性
DTS.Connection2 oConnection;
oConnection =(DTS.Connection2) ObjDTS.Connections.New("SQLOLEDB");
oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI";
oConnection.ConnectionProperties.Item("Persist Security Info").Value = true;
oConnection.ConnectionProperties.Item("Initial Catalog").Value = dataBase;
oConnection.ConnectionProperties.Item("Data Source").Value =server;
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS 导入/导出向导";
oConnection.Name = "连接1";
oConnection.ID = 1;
oConnection.Reusable = true;
oConnection.ConnectImmediate = false;
oConnection.DataSource = server;
oConnection.ConnectionTimeout = 60;
oConnection.Catalog =this.dataBase;
ObjDTS.Connections.Add( ( DTS.Connection)oConnection );
oConnection = null;
![](/Images/OutliningIndicators/InBlock.gif)
//建立导出数据库的连接,设置其属性
oConnection = ( DTS.Connection2 )ObjDTS.Connections.New("Microsoft.Jet.OLEDB.4.0");
oConnection.ConnectionProperties.Item("Data Source").Value = this.strDestinationFilePath;
oConnection.ConnectionProperties.Item("Extended Properties").Value = "Excel 8.0;HDR=YES;";
oConnection.Name = "连接2";
oConnection.ID = 2;
oConnection.Reusable = true;
oConnection.ConnectImmediate = false;
oConnection.DataSource = this.strDestinationFilePath;
oConnection.ConnectionTimeout = 60;
oConnection.UseTrustedConnection = false;
oConnection.UseDSL = false;
//将其加入DTS包中
ObjDTS.Connections.Add( ( DTS.Connection )oConnection );
oConnection = null;
![](/Images/OutliningIndicators/InBlock.gif)
//设置DTS执行的步骤
DTS.Step2 oStep;
DTS.PrecedenceConstraint oPrecConstraint;
oStep = ( DTS.Step2 ) ObjDTS.Steps.New();
//创建表,设置属性
oStep.Name = "创建表 " + this.strTableName + " 步骤";
oStep.Description = "创建表 "+ this.strTableName + " 步骤";
oStep.ExecutionStatus = ( DTS.DTSStepExecStatus )1;
oStep.TaskName = "创建表 " + this.strTableName + " 任务";
oStep.CommitSuccess = false;
oStep.RollbackFailure = false;
oStep.ScriptLanguage = "VBScript";
oStep.AddGlobalVariables = true;
oStep.RelativePriority = ( DTS.DTSStepRelativePriority )3;
oStep.CloseConnection = false;
oStep.ExecuteInMainThread = false;
oStep.IsPackageDSORowset = false;
oStep.JoinTransactionIfPresent = false;
oStep.DisableStep = false;
oStep.FailPackageOnError = false;
//将其加入DTS包中
ObjDTS.Steps.Add( oStep );
oStep = null;
![](/Images/OutliningIndicators/InBlock.gif)
//设置数据导出的步骤 , 设置性属
oStep = ( DTS.Step2 )ObjDTS.Steps.New();
oStep.Name = "Copy Data from 结果 to " + this.strTableName + " 步骤";
oStep.Description = "Copy Data from 结果 to " + this.strTableName + " 步骤";
oStep.ExecutionStatus = ( DTS.DTSStepExecStatus )1;
oStep.TaskName = "Copy Data from 结果 to " + this.strTableName + " 任务";
oStep.CommitSuccess = false;
oStep.RollbackFailure = false;
oStep.ScriptLanguage = "VBScript";
oStep.AddGlobalVariables = true;
oStep.RelativePriority = ( DTS.DTSStepRelativePriority )3;
oStep.CloseConnection = false;
oStep.ExecuteInMainThread = true;
oStep.IsPackageDSORowset = false;
oStep.JoinTransactionIfPresent = false;
oStep.DisableStep = false;
oStep.FailPackageOnError = false;
//将其添加到DTS包中
ObjDTS.Steps.Add( oStep );
oStep = null;
![](/Images/OutliningIndicators/InBlock.gif)
//设置生成表的步骤
oStep = ( DTS.Step2 )ObjDTS.Steps.Item("Copy Data from 结果 to " + this.strTableName + " 步骤");
oPrecConstraint = oStep.PrecedenceConstraints.New("创建表 " + this.strTableName + " 步骤");
oPrecConstraint.StepName = "创建表 " + this.strTableName + " 步骤";
oPrecConstraint.PrecedenceBasis = 0;
oPrecConstraint.Value = 4;
![](/Images/OutliningIndicators/InBlock.gif)
oStep.PrecedenceConstraints.Add( oPrecConstraint );
oPrecConstraint = null;
![](/Images/OutliningIndicators/InBlock.gif)
this.Task_Sub1(ObjDTS);
this.Task_Sub2(ObjDTS);
//执行导出数据
ObjDTS.Execute();
tracePackageError( ObjDTS );
return true;
}
catch
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return false;
}
finally
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
ObjDTS.UnInitialize();
System.Runtime.InteropServices.Marshal.ReleaseComObject( ObjDTS );
ObjDTS = null;
GC.Collect();
this.arrDestinationColumns.Clear();
this.arrSourceColumns.Clear();
}
}
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
private void tracePackageError( DTS.Package2Class oPackage)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
int ErrorCode;
string ErrorSource;
string ErrorDescription;
string ErrorHelpFile;
int ErrorHelpContext;
string ErrorIDofInterfaceWithError;
for( int i = 1 ; i < oPackage.Steps.Count ; i ++ )
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ( oPackage.Steps.Item(i).ExecutionResult == DTS.DTSStepExecResult.DTSStepExecResult_Failure )
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
oPackage.Steps.Item(i).GetExecutionErrorInfo(out ErrorCode,out ErrorSource,out ErrorDescription,out ErrorHelpFile,out ErrorHelpContext,out ErrorIDofInterfaceWithError);
//添加抛出异常语句
}
}
}
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 创建表
/// </summary>
/// <param name="goPackage">DTS包对象</param>
private void Task_Sub1( DTS.Package2Class ObjDTS )
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DTS.Task oTask;
DTS.ExecuteSQLTask2 oCustomTask1;
oTask = ObjDTS.Tasks.New("DTSExecuteSQLTask");
oTask.Name = "创建表 " + this.strTableName + " 任务";
oCustomTask1 = ( DTS.ExecuteSQLTask2) oTask.CustomTask;
![](/Images/OutliningIndicators/InBlock.gif)
oCustomTask1.Name = "创建表 " + this.strTableName + " 任务";
oCustomTask1.Description = "创建表 " + this.strTableName + " 任务";
oCustomTask1.SQLStatement = "CREATE TABLE `" + this.strTableName + "` (" + "\n";
![](/Images/OutliningIndicators/InBlock.gif)
//arrDestinationColumns[0]="管理号";
for( int i = 0 ; i < this.arrDestinationColumns.Count ; i ++ )
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if ( i == this.arrDestinationColumns.Count - 1 )
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement + "[" + this.arrDestinationColumns[i].ToString() +"] VarChar(50) " + "\n";
else
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement + "[" + this.arrDestinationColumns[i].ToString() +"] VarChar(50), " + "\n";
}
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement + ")";
oCustomTask1.ConnectionID = 2;
oCustomTask1.CommandTimeout = 0;
oCustomTask1.OutputAsRecordset = false;
ObjDTS.Tasks.Add( oTask );
oCustomTask1 = null;
oTask = null;
}
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 执行导出的SQL语句
/// </summary>
/// <param name="ObjDTS">DTS包对象</param>
private void Task_Sub2( DTS.Package2Class ObjDTS )
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DTS.Task oTask;
DTS.DataPumpTask2 oCustomTask2;
oTask = ObjDTS.Tasks.New("DTSDataPumpTask");
oTask.Name = "Copy Data from 结果 to " + this.strTableName + " 任务";
oCustomTask2 = (DTS.DataPumpTask2 )oTask.CustomTask;
![](/Images/OutliningIndicators/InBlock.gif)
oCustomTask2.Name = "Copy Data from 结果 to " + this.strTableName + " 任务";
oCustomTask2.Description = "Copy Data from 结果 to " + this.strTableName + " 任务";
oCustomTask2.SourceConnectionID = 1;
oCustomTask2.SourceSQLStatement = this.strSQL;
oCustomTask2.DestinationConnectionID = 2;
oCustomTask2.DestinationObjectName = this.strTableName;
oCustomTask2.ProgressRowCount = 1000;
oCustomTask2.MaximumErrorCount = 0;
oCustomTask2.FetchBufferSize = 1;
oCustomTask2.UseFastLoad = true;
oCustomTask2.InsertCommitSize = 0;
oCustomTask2.ExceptionFileColumnDelimiter = "|";
oCustomTask2.ExceptionFileRowDelimiter = @"\n";
oCustomTask2.AllowIdentityInserts = false;
oCustomTask2.FirstRow = 0;
oCustomTask2.LastRow = 0;
oCustomTask2.FastLoadOptions = ( DTS.DTSFastLoadOptions )2;
oCustomTask2.ExceptionFileOptions = ( DTS.DTSExceptionFileOptions )1;
oCustomTask2.DataPumpOptions = 0;
![](/Images/OutliningIndicators/InBlock.gif)
this.oCustomTask2_Trans_Sub1( oCustomTask2 );
ObjDTS.Tasks.Add( oTask );
oCustomTask2 = null;
oTask = null;
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 设置导出的源列和目标列的对应关系
/// </summary>
/// <param name="oCustomTask2"></param>
private void oCustomTask2_Trans_Sub1( DTS.DataPumpTask2 oCustomTask2 )
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DTS.Transformation2 oTransformation;
DTS.Properties oTransProps;
DTS.Column oColumn;
oTransformation = ( DTS.Transformation2 )oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy");
oTransformation.Name = "DirectCopyXform";
oTransformation.TransformFlags = 63;
oTransformation.ForceSourceBlobsBuffered = ( DTS.DTSForceMode )1;
oTransformation.ForceBlobsInMemory = false;
oTransformation.InMemoryBlobSize = 1048576;
oTransformation.TransformPhases = 4;
for( int i = 0 ;i< this.arrSourceColumns.Count ; i++ )
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
oColumn = oTransformation.SourceColumns.New( this.arrSourceColumns[i].ToString(),i + 1 );
oColumn.Name = this.arrSourceColumns[i].ToString();
oColumn.Ordinal = i + 1;
oColumn.Flags = 8;
oColumn.Size = 50;
oColumn.DataType = 130;
oColumn.Precision = 0;
oColumn.NumericScale = 0;
oColumn.Nullable = false;
![](/Images/OutliningIndicators/InBlock.gif)
oTransformation.SourceColumns.Add( oColumn );
oColumn = null;
![](/Images/OutliningIndicators/InBlock.gif)
}
for( int j = 0 ; j < this.arrDestinationColumns.Count; j++ )
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
oColumn = oTransformation.DestinationColumns.New( this.arrDestinationColumns[j].ToString(),j + 1 );
oColumn.Name = this.arrDestinationColumns[j].ToString();
oColumn.Ordinal = j + 1;
oColumn.Flags = 8;
oColumn.Size = 50 ;
oColumn.DataType = 130;
oColumn.Precision = 0;
oColumn.NumericScale = 0;
oColumn.Nullable = false;
![](/Images/OutliningIndicators/InBlock.gif)
oTransformation.DestinationColumns.Add( oColumn );
oColumn = null;
}
oTransProps = oTransformation.TransformServerProperties;
oTransProps = null;
![](/Images/OutliningIndicators/InBlock.gif)
oCustomTask2.Transformations.Add( oTransformation );
oTransformation = null;
}
#endregion
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ContractedSubBlock.gif)
得到目标表的列名#region 得到目标表的列名
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 在SQL语句中得到目标表的列名 , 本来是想根据SQL语句来决定列名,没有找到高效的分解SQL语句的方法。
/// </summary>
private void GetColumns()
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SqlConnection conn=new SqlConnection(connectionString);
SqlDataAdapter da=new SqlDataAdapter(strSQL,conn);
DataSet ds = new DataSet();
da.Fill(ds,"qqq");
for ( int i = 0; i < ds.Tables[0].Columns.Count; i++ )
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
this.arrDestinationColumns.Add(ds.Tables[0].Columns[i].ColumnName );
this.arrSourceColumns.Add(ds.Tables[0].Columns[i].ColumnName);
}
}
#endregion
}
![](/Images/OutliningIndicators/InBlock.gif)
}用法如下:
string strdespath="c:\\File\\File\\abc.xls";//该文件夹和文件都可以实际不存在,程序能自动创建
string tablename="authors"; //表名(用于导入到EXCEL里Sheet的名字,对程序毫无用处)
//以下几种都可以
// 第一种
// string strsql="SELECT [au_lname]"+
// " FROM [pubs].[dbo].[authors]";//要显示的列信息
// //第二种
// string strsql="SELECT [au_lname]"+
// " FROM [pubs].[dbo].[authors] WHERE au_id='172-32-1176'";//要显示的列信息
//第三种
string strsql="SELECT * "+ " FROM [pubs].[dbo].[authors]";//要显示的列信息
string connctionstring="server=(local);uid=sa;pwd=;database=pubs";//数据库连接
DtsExcel.AppExportData dtsclass=new DtsExcel.AppExportData(strdespath,tablename,strsql,connctionstring);
if(dtsclass.ExportData())//导出
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//成功;
} 这种方式性能比较好,特别适用于数据量很大的情况,但是灵活性不高。各位看着用吧。