DataSet与Excel的互相导入导出

一、Dataset ->Excel

          public static System.Data.DataSet ExcelToDataTable(string strExcelFileName, string strSheetName)
  {
   //源的定义
   string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
        "Data Source=" + strExcelFileName + ";" +
        "Extended Properties="Excel 8.0;HDR=YES;IMEX=1"";//"Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";

  // System.Data.DataTable   schemaTable   =   objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);  
  // string   tableName=schemaTable.Rows[0][2].ToString().Trim();  
 
   //Sql语句
   //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
   string strExcel = "select * from  [sheet1$]";

   //定义存放的数据表
   DataSet ds = new DataSet();
   
   //连接数据源
   OleDbConnection conn = new OleDbConnection(strConn);

   conn.Open();

   //适配到数据源
   OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
   adapter.Fill(ds, strSheetName);

   conn.Close();

   return ds; //.Tables[strSheetName];
  }

二、Excel -> DataSet

     Excel.Application objExcel = new Excel.ApplicationClass();
    Excel.Workbooks  objBooks = null;
    Excel._Workbook  objBook = null;
    Excel.Sheets  objSheets = null;
    Excel._Worksheet objSheet = null;
    Excel.Range   objRange = null;

    objExcel.DisplayAlerts  = false ;//设置保存文件存在时不提示
    
    object objOpt  = System.Reflection.Missing.Value;
    objBooks   = (Excel.Workbooks) objExcel.Workbooks ;
    objBook    = (Excel._Workbook)(objBooks.Add(objOpt)) ;
    objSheets   = (Excel.Sheets) objBook.Worksheets ;
    objSheet   = (Excel._Worksheet)(objSheets.get_Item(1)) ;

    objSheet.Name  = "sheet1" ; // this.getFileName(dlgSaveFile.FileName) ;

    object[]  objTital = { "销售部门", "高级经理", "经理","零售店编号","零售店", "零售店级别" } ;
    objRange   = objSheet.get_Range("A1", "F1") ;
    objRange.set_Value(objOpt, objTital) ;

 

    objBook.SaveAs(dlgSaveFile.FileName,objOpt,objOpt,objOpt,objOpt,objOpt,Excel.XlSaveAsAccessMode.xlNoChange,
     objOpt,objOpt,objOpt,objOpt,objOpt) ;
    objBook.Close(true,objOpt,objOpt) ;
    objBooks.Close() ;
    objExcel.Quit() ;