使用DTS导出到Access-FoxPro-Word -Excel 不需要验证的大批量数据.doc

使用DTS导出到Access-FoxPro-Word -Excel 不需要验证的大批量数据.doc 使用DTS导出到Access,FoxPro,Word ,Excel 不需要验证的大批量数据。 SQL server 有个导入导出的功能，非常强大。名为DTS 。 细心的人早注意到了在使用导入导出的时候可以保存一个VB语言写的DTS包。 那就是DTS的源程序。以下的源程序是我参照生成的DTS包改成的C#导出数据。大家可以参照一下。改成导入导出的。 如何生成相应的文档如: Access ,Word , Excel ,FoxPro 请看在 .Net for Windows 的那遍 public class AppExportData { //DTS包对象 public DTS.Package2Class ObjDTS; //源文件路径 private string strSourceFilePath; //目标文件路径 private string strDestinationFilePath; //文件类型 private AppExportData.DataFileType strFileType; //导出数据的SQL语句 private string strSQL; //目标表名 private string strTableName; //得到目标列名 private ArrayList arrDestinationColumns; //得到源数据列名 private ArrayList arrSourceColumns; #region 私有属性 private ArrayList SourceColumns { get { return arrSourceColumns; } set { arrSourceColumns = value; } } ///

/// 得到目标列名 ///

private ArrayList DestinationColumns { get { return arrDestinationColumns; } set { arrDestinationColumns = value; } } #endregion #region 公共属性 ///

/// 目标表名 ///

public string TableName { get { return strTableName; } set { strTableName = value; } } ///

/// 导出数据的SQL语句 ///

public string SQL { get { return strSQL; } set { strSQL = value; } } ///

/// 导出数据保存的文件类型 ///

public AppExportData.DataFileType FileType { get { return strFileType; } set { strFileType = value; } } ///

/// 源文件所在的路径 ///

public string SourceFilePath { get { return strSourceFilePath; } set { strSourceFilePath = value; } } ///

/// 目标文件所在的路径 ///

public string DestinationFilePath { get { return strDestinationFilePath; } set { strDestinationFilePath = value; } } #endregion public AppExportData() { strTableName = "结果"; arrDestinationColumns = new ArrayList(); arrSourceColumns = new ArrayList(); // // TODO: 在此处添加构造函数逻辑 // } #region 导出的全过程 public bool ExportData() { try { ObjDTS = new DTS.Package2Class(); if ( this.arrDestinationColumns.Count == 0 || this.arrSourceColumns.Count == 0) { if ( !this.GetColumns() ) { System.Windows.Forms.MessageBox.Show( "没有获得数据,导出文件失败～","提示信息" ); return false; } } //新建一个新的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; //建立SQL的连接,设置其属性 DTS.Connection2 oConnection; oConnection = ( DTS.Connection2 )ObjDTS.Connections.New("Microsoft.Jet.OLEDB.4.0"); oConnection.ConnectionProperties.Item("Data Source").Value = this.strSourceFilePath; oConnection.ConnectionProperties.Item("Mode").Value = 1; oConnection.Name = "连接1"; oConnection.ID = 1; oConnection.Reusable = true; oConnection.ConnectImmediate = false; oConnection.DataSource = this.strSourceFilePath ; oConnection.ConnectionTimeout = 60; oConnection.UseTrustedConnection = false; oConnection.UseDSL = false; ObjDTS.Connections.Add( ( DTS.Connection )oConnection ); oConnection = null; //建立导出数据库的连接,设置其属性 oConnection = ( DTS.Connection2 )ObjDTS.Connections.New("Microsoft.Jet.OLEDB.4.0"); oConnection.ConnectionProperties.Item("Data Source").Value = this.strDestinationFilePath; switch ( this.strFileType ) { case DataFileType.Access: oConnection.ConnectionProperties.Item("Mode").Value = 3; break; case DataFileType.Excel: oConnection.ConnectionProperties.Item("Extended Properties").Value = "Excel 8.0;HDR=YES;"; break; default: oConnection.ConnectionProperties.Item("Extended Properties").Value = "dBase 5.0"; //还要把列名的长度，在foxpro中列名最长只能是10个英文字母也就是5个汉字 for ( int i = 0 ; i < this.arrDestinationColumns.Count; i ++ ) { if ( WyScore.AID.AppAssert.CheckString( this.arrDestinationColumns[i].ToString(),8 ) ) { if ( this.arrDestinationColumns[i].ToString().Length > 5 ) { this.arrDestinationColumns[i] = this.arrDestinationColumns[i].ToString().Substring( 0,5 ); } } } break; } 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; //设置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; //设置数据导出的步骤 , 设置性属 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; //设置生成表的步骤 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; oStep.PrecedenceConstraints.Add( oPrecConstraint ); oPrecConstraint = null; this.Task_Sub1( ObjDTS ); this.Task_Sub2( ObjDTS ); //执行导出数据 ObjDTS.Execute(); tracePackageError( ObjDTS ); return true; } catch { return false; } finally { ObjDTS.UnInitialize(); System.Runtime.InteropServices.Marshal.ReleaseComObject( ObjDTS ); ObjDTS = null; GC.Collect(); this.arrDestinationColumns.Clear(); this.arrSourceColumns.Clear(); } } ///

/// 导出到Word文件 ///

/// 源数据集 /// 标签 public bool ExportWord( DataTable Table , System.Windows.Forms.Label label ) { object Missing = System.Reflection.Missing.Value; int NumRows, NumColumns, rowIndex, colIndex; //保存word文件的路径 object FileName = this.strDestinationFilePath; Word.ApplicationClass wordApp = new Word.ApplicationClass(); Word.Document myDoc = null; Word.Table oTable; rowIndex = 1; colIndex = 0; try { wordApp.Documents.Add(ref Missing,ref Missing,ref Missing, ref Missing); myDoc = wordApp.ActiveDocument; oTable = myDoc.Tables.Add(myDoc.Range(ref Missing, ref Missing),NumRows=Table.Rows.Count + 1, NumColumns=Table.Columns.Count,ref Missing,ref Missing); //将所得到的表的列名,赋值给单元格 foreach(DataColumn Col in Table.Columns) { colIndex = colIndex + 1; oTable.Cell(1, colIndex).Range.InsertAfter(Col.ColumnName); } //得到的表所有行,赋值给单元格 foreach(DataRow Row in Table.Rows) { rowIndex = rowIndex + 1; colIndex = 0; foreach(DataColumn Col in Table.Columns) { colIndex = colIndex + 1; oTable.Cell(rowIndex, colIndex).Range.InsertAfter(Row[Col.ColumnName].ToString()); } label.Text = "正在导出数据，" + (rowIndex - 1).ToString() + "/" + Table.Rows.Count.ToString(); System.Windows.Forms.Application.DoEvents(); } oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleDashDot; oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDot; //true:word文件显示 false:word文件不显示 wordApp.Visible = false; myDoc.SaveAs2000(ref FileName,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing,ref Missing ); return true; } catch { return false; } finally { myDoc.Close( ref Missing,ref Missing,ref Missing ); System.Runtime.InteropServices.Marshal.ReleaseComObject( myDoc ); myDoc = null; GC.Collect(); wordApp.Quit( ref Missing,ref Missing, ref Missing ); System.Runtime.InteropServices.Marshal.ReleaseComObject( wordApp ); wordApp = null; GC.Collect(); } } ///

/// 异常处理 ///

/// DTS包对象 private void tracePackageError( DTS.Package2Class oPackage) { int ErrorCode; string ErrorSource; string ErrorDescription; string ErrorHelpFile; int ErrorHelpContext; string ErrorIDofInterfaceWithError; for( int i = 1 ; i < oPackage.Steps.Count ; i ++ ) { if ( oPackage.Steps.Item(i).ExecutionResult == DTS.DTSStepExecResult.DTSStepExecResult_Failure ) { oPackage.Steps.Item(i).GetExecutionErrorInfo(out ErrorCode,out ErrorSource,out ErrorDescription,out ErrorHelpFile,out ErrorHelpContext,out ErrorIDofInterfaceWithError); } } } ///

/// 创建表 ///

/// DTS包对象 private void Task_Sub1( DTS.Package2Class ObjDTS ) { DTS.Task oTask; DTS.ExecuteSQLTask2 oCustomTask1; oTask = ObjDTS.Tasks.New("DTSExecuteSQLTask"); oTask.Name = "创建表 " + this.strTableName + " 任务"; oCustomTask1 = ( DTS.ExecuteSQLTask2 )oTask.CustomTask; oCustomTask1.Name = "创建表 " + this.strTableName + " 任务"; oCustomTask1.Description = "创建表 " + this.strTableName + " 任务"; oCustomTask1.SQLStatement = "CREATE TABLE `" + this.strTableName + "` (" + "\n"; for( int i = 0 ; i < this.arrDestinationColumns.Count ; i ++ ) { 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; } ///

/// 执行导出的SQL语句 ///

/// DTS包对象 private void Task_Sub2( DTS.Package2Class ObjDTS ) { DTS.Task oTask; DTS.DataPumpTask2 oCustomTask2; oTask = ObjDTS.Tasks.New("DTSDataPumpTask"); oTask.Name = "Copy Data from 结果 to " + this.strTableName + " 任务"; oCustomTask2 = (DTS.DataPumpTask2 )oTask.CustomTask; 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; if ( this.strFileType == AppExportData.DataFileType.Excel ) oCustomTask2.DestinationObjectName = this.strTableName; else 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; this.oCustomTask2_Trans_Sub1( oCustomTask2 ); ObjDTS.Tasks.Add( oTask ); oCustomTask2 = null; oTask = null; } ///

/// 设置导出的源列和目标列的对应关系 ///

/// private void oCustomTask2_Trans_Sub1( DTS.DataPumpTask2 oCustomTask2 ) { 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++ ) { 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; oTransformation.SourceColumns.Add( oColumn ); oColumn = null; } for( int j = 0 ; j < this.arrDestinationColumns.Count; j++ ) { 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; oTransformation.DestinationColumns.Add( oColumn ); oColumn = null; } oTransProps = oTransformation.TransformServerProperties; oTransProps = null; oCustomTask2.Transformations.Add( oTransformation ); oTransformation = null; } #endregion #region 从SQL语句中提出源列名和目标列名，这是创建表和设置对应关系必须要的 public enum DataFileType { ///

/// Excel类型数据导入 ///

Excel = 1, ///

/// Access类型数据导入 ///

Access = 2, ///

/// FoxPro类型数据导入 ///

FoxPro = 3, } ///

/// 在SQL语句中得到目标表的列名 , 本来是想根据SQL语句来决定列名,没有找到高效的分解SQL语句的方法。如果大家有什么好方法。请告诉我，谢谢 ///

private bool GetColumns() { try { OleDbConnection con = new OleDbConnection(AppConfig.ConnString); OleDbDataAdapter da = new OleDbDataAdapter( this.strSQL,con); DataSet ds = new DataSet(); da.Fill(ds,"temp"); for ( int i = 0; i < ds.Tables["temp"].Columns.Count; i++ ) { this.arrDestinationColumns.Add( ds.Tables["temp"].Columns[i].ColumnName ); this.arrSourceColumns.Add( ds.Tables["temp"].Columns[i].ColumnName ); } return true; } catch { return false; } } #endregion }