一简单的C#操作Excel代码。

来源：互联网发布：linux phpmyadmin配置编辑：程序博客网时间：2024/04/27 23:44

C#操作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() ;