using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace DS_EXCEL
{
 /// <summary>
 /// ImportExportToExcel 的摘要说明。
 /// </summary>
 public class ImportExportToExcel
 {
  private string strConn ;
      private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
  private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();     
      public ImportExportToExcel()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
   this.openFileDlg.DefaultExt = "xls";
   this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";

   this.saveFileDlg.DefaultExt="xls";
   this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";

  }
  #region 从Excel文件导入到DataSet
           /// <summary>
           /// 从Excel导入文件
           /// </summary>
           /// <param name="strExcelFileName">Excel文件名</param>
           /// <returns>返回DataSet</returns>
  
  
  public DataSet ImportFromExcel()
  {
   DataSet ds=new DataSet();
   if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    ds=doImport(openFileDlg.FileName);
   else ds=null;
   return ds;
  }
  public DataSet ImportFromExcel(string strFileName)
  {
   DataSet ds=new DataSet();
   ds=doImport(strFileName);
   return ds;
  }

 
  private DataSet doImport(string strFileName)
  {
   if (strFileName=="") return null;
             
   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" +  strFileName + ";" +
    "Extended Properties=Excel 8.0;";
   OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

   DataSet ExcelDs = new DataSet();
   try
   {
    ExcelDA.Fill(ExcelDs, "ExcelInfo");
               
   }
   catch(Exception err)
   {
    System.Console.WriteLine( err.ToString() );
   }
   return ExcelDs;
                             
  }
  #endregion


  #region 从DataSet到出到Excel
   /**//// <summary>
   /// 导出指定的Excel文件
   /// </summary>
   /// <param name="ds">要导出的DataSet</param>
   /// <param name="strExcelFileName">要导出的Excel文件名</param>
   public void ExportToExcel(DataSet ds,string strExcelFileName)
   {
    if (ds.Tables.Count==0 || strExcelFileName=="") return;
    doExport(ds,strExcelFileName);
   

   }
  /**//// <summary>
  /// 导出用户选择的Excel文件
  /// </summary>
  /// <param name="ds">DataSet</param>
  public void ExportToExcel(DataSet ds)
  {
   if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    doExport(ds,saveFileDlg.FileName);
           
  }
  /**//// <summary>
  /// 执行导出
  /// </summary>
  /// <param name="ds">要导出的DataSet</param>
  /// <param name="strExcelFileName">要导出的文件名</param>
  private void doExport(DataSet ds,string strExcelFileName)
  {
           
   Microsoft.Office.Interop.Excel.Application excel= new Microsoft.Office.Interop.Excel.Application();
           
   //            Excel.Workbook obj=new Excel.WorkbookClass();
   //            obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);

   int rowIndex=1;
   int colIndex=0;

   excel.Application.Workbooks.Add(true);
           
   
   System.Data.DataTable table=ds.Tables[0] ;
   foreach(DataColumn col in table.Columns)
   {
    colIndex++;   
    excel.Cells[1,colIndex]=col.ColumnName;               
   }

   foreach(DataRow row in table.Rows)
   {
    rowIndex++;
    colIndex=0;
    foreach(DataColumn col in table.Columns)
    {
     colIndex++;
     excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
    }
   }
   excel.Visible=false;   
   //excel.Sheets[0] = "sss";
   excel.ActiveWorkbook._SaveAs(strExcelFileName,Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795,null,null,false,false,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
           
            MessageBox.Show("已将EXCEL表格导出到"+strExcelFileName,"提示");
   //wkbNew.SaveAs strBookName


   //excel.Save(strExcelFileName);
   excel.Quit();
   excel=null;
           
   GC.Collect();//垃圾回收
  }
  #endregion


 }
}