从数据库导出数据到word、excel、.txt(一)

using System;
using System.Data;
using System.Drawing;
using System.Data.SqlClient;
using Excel;
using Word;
using System.IO;
namespace Common
{
 /// <summary>
 /// 把数据导入到.doc、.txt、.xls文件中
 /// </summary>
 public class Export
 {
  private const string DATAWORDPATH = @"C:/folder/doc/datadoc/";
  private const string IMAGEWORDPATH = @"C:/folder/doc/imagedoc/";
  private const string IMAGEPATH = @"C:/folder/image/";
  private const string EXCELPATH = @"C:/folder/excel/";
  private const string TXTPATH = @"C:/folder/txt/";
  private const string IMAGEPOSTFIX = ".bmp";
  private const string WORDPOSTFIX = ".doc";
  private const string EXCELPOSTFIX = ".xls";
  private const string TXTPOSTFIX = ".txt";
  private const int DATADISTANCE = 5;
  private const int TABDISTANCE = 8;
 
  public Export()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }

  /// <summary>
  /// 获得数据集Dataset--------------------------------用于调试
  /// </summary>
  /// <returns>Dataset</returns>
  public DataSet GetData()
  {
   try
   {
    string sConnectionString;
    sConnectionString = "workstation id=GUOFU;packet size=4096;user id=sa;data source=GUOFU;persist security info=True;initial catalog=YC;password=sc";
    SqlConnection objConn = new SqlConnection(sConnectionString);
    objConn.Open();                                           
    SqlDataAdapter daPoint = new SqlDataAdapter("Select * From Point", objConn);
    DataSet dsYC = new DataSet("YC");
    daPoint.FillSchema(dsYC,SchemaType.Mapped, "Point");
    daPoint.Fill(dsYC,"Point");
    daPoint = new SqlDataAdapter("Select * From Employee", objConn);
    daPoint.FillSchema(dsYC,SchemaType.Mapped, "Employee");
    daPoint.Fill(dsYC,"Employee");
    return dsYC;
   }
   catch(Exception ex)
   {
    throw new Exception(ex.Message);
   }

  }

  /// <summary>
  /// 把数据文件导入到.xls文件
  /// </summary>
  /// <param name="ds"></param>
  public void ExportToExcel(DataSet ds)
  {

   if(ds.Tables.Count!=0)
   {
    //生成.xls文件完整路径名
    string tempFileName = GetTempFileName();
    object filename = EXCELPATH+tempFileName+EXCELPOSTFIX;
    object Nothing = System.Reflection.Missing.Value;
   
    //创建excel文件，文件名用系统时间生成精确到毫秒
    Excel.Application myExcel = new Excel.ApplicationClass();
    myExcel.Application.Workbooks.Add(Nothing);

    try
    {
     //把Dataset中的数据插入excel文件中
     int totalCount = 0;
     for(int k =0;k<ds.Tables.Count;k++)
     {
      int row = ds.Tables[k].Rows.Count;
      int column = ds.Tables[k].Columns.Count;
   
      for(int i = 0;i<column;i++)
      {
       myExcel.Cells[totalCount+2,1+i] = ds.Tables[k].Columns[i].ColumnName;
      }

      for(int i = 0;i<row;i++)
      {
       for(int j =0;j<column;j++)
       {
        myExcel.Cells[totalCount+3+i,1+j] = "'" + ds.Tables[k].Rows[i][j].ToString();
       }
      }
      totalCount = totalCount + row +4;
     }

     try
     {
      //保存excel文件到指定的目录下，文件名用系统时间生成精确到毫秒
      myExcel.ActiveWorkbook._SaveAs(filename,Nothing,Nothing,Nothing,Nothing,Nothing,XlSaveAsAccessMode.xlExclusive,Nothing,Nothing,Nothing,Nothing);
     }
     catch
     {
      System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件：  "+EXCELPATH+tempFileName+EXCELPOSTFIX);
      return;
     }
     //让生成的excel文件可见
     myExcel.Visible = true;
    }
    catch(Exception e)
    {
     System.Windows.Forms.MessageBox.Show("向excel文件中写入数据出错：  " + e.Message);
    }
   }
   else
   {
    System.Windows.Forms.MessageBox.Show("No Data");
   }
  }

  /// <summary>
  /// 把数据导入到.doc文件
  /// </summary>
  /// <param name="ds"></param>
  public void ExportToWord(DataSet ds)
  {
   if(ds.Tables.Count!=0)
   {  
    string tempFileName = null;
    object filename = null;
   
    object tableBehavior = Word.WdDefaultTableBehavior.wdWord9TableBehavior;
    object autoFitBehavior = Word.WdAutoFitBehavior.wdAutoFitFixed;

    object unit = Word.WdUnits.wdStory;
    object extend = System.Reflection.Missing.Value;
    object breakType = (int)Word.WdBreakType.wdSectionBreakNextPage;

    object count = 1;
    object character = Word.WdUnits.wdCharacter;

    object Nothing =  System.Reflection.Missing.Value;
   
    try
    {
     tempFileName = GetTempFileName();

     //生成.doc文件完整路径名
     filename = DATAWORDPATH+tempFileName+WORDPOSTFIX;
    
     //创建一个word文件，文件名用系统时间生成精确到毫秒
     Word.Application myWord= new Word.ApplicationClass();
     Word._Document myDoc = new Word.DocumentClass();
     myDoc = myWord.Documents.Add(ref Nothing,ref Nothing,ref Nothing,ref Nothing);
     myDoc.Activate();

     //向把dataset中的表插入到word的文件中
   
     for(int totalTable = 0;totalTable<ds.Tables.Count;totalTable++)
     {
      myWord.Application.Selection.TypeText(ds.Tables[totalTable].TableName+"表的数据如下");
      myWord.Application.Selection.TypeParagraph();    
      myWord.Application.Selection.TypeParagraph();
      Word.Range para = myWord.Application.Selection.Range;
      myDoc.Tables.Add(para,ds.Tables[totalTable].Rows.Count+1,ds.Tables[totalTable].Columns.Count,ref tableBehavior,ref autoFitBehavior);
      for(int column = 0; column<ds.Tables[totalTable].Columns.Count;column++)
      {
       myDoc.Tables.Item(totalTable+1).Cell(1,column+1).Range.InsertBefore(ds.Tables[0].Columns[column].ColumnName.Trim());
      }   
      for(int row = 0;row<ds.Tables[totalTable].Rows.Count;row++)
      {
       for(int column = 0;column<ds.Tables[totalTable].Columns.Count;column++)
       {
        myDoc.Tables.Item(totalTable+1).Cell(row+2,column+1).Range.InsertBefore(ds.Tables[totalTable].Rows[row][column].ToString().Trim());
       }
      }
      myWord.Application.Selection.EndKey(ref unit,ref extend);
      myWord.Application.Selection.TypeParagraph();
      myWord.Application.Selection.TypeParagraph();
      myWord.Application.Selection.InsertBreak(ref breakType);   
     }
     myWord.Application.Selection.TypeBackspace();
     myWord.Application.Selection.Delete(ref character,ref count);
     myWord.Application.Selection.HomeKey(ref unit,ref extend);
   
     //保存word文件到指定的目录下
     try
     {
      myDoc.SaveAs(ref filename,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing);
      myWord.Visible = true;
     }
     catch
     {
      System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件：  "+DATAWORDPATH+tempFileName+WORDPOSTFIX);
      return;
     }
     //让生成的excel文件可见
     myWord.Visible = true;
    }
    catch(Exception ex)
    {
     System.Windows.Forms.MessageBox.Show("向word文件中写入数据出错：  " + ex.Message);
    }
   }
   else
   {
    System.Windows.Forms.MessageBox.Show("No Data");
   }
  }
  /// <summary>
  /// 把图片文件导入到.doc文件
  /// </summary>
  /// <param name="bp"></param>
  public void ExportToWord(Bitmap bp)
  {
   string tempFileName = null;
   string bmpPath = null;
   object filename = null;
   object Nothing = null;
   tempFileName = GetTempFileName();

   //生成.bmp文件完整路径名
   bmpPath = IMAGEPATH+tempFileName+IMAGEPOSTFIX;

   //生成.doc文件完整路径名
   filename = IMAGEWORDPATH+tempFileName+WORDPOSTFIX;
   Nothing = System.Reflection.Missing.Value;
 
   //创建一个word文件，文件名用系统时间生成精确到毫秒
   Word.Application myWord= new Word.ApplicationClass();
   Word._Document myDoc = new Word.DocumentClass();
   myDoc = myWord.Documents.Add(ref Nothing,ref Nothing,ref Nothing,ref Nothing); 

   try
   {
    //把bitmap对象保存到系统所生成文件完整路径中
    bp.Save(bmpPath);
   }
   catch
   {
    System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件：  "+bmpPath);
    return;
   }
 
   try
   {
    //往word文件中插入图片
    myDoc.InlineShapes.AddPicture(bmpPath,ref Nothing,ref Nothing,ref Nothing);
   }
   catch
   {
    System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件：  "+bmpPath);
    return;
   }
 
   try
   {
    //保存word文件到指定的目录下
    myDoc.SaveAs(ref filename,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing,ref Nothing);
   }
   catch
   {
    System.Windows.Forms.MessageBox.Show("系统找不到指定目录下的文件：  "+IMAGEWORDPATH+tempFileName+WORDPOSTFIX);
    return;
   }

   //让生成的word文件可见
   myWord.Visible = true;
  }