C#操作Excel有两种操作法

最新推荐文章于 2024-11-04 10:04:23 发布

nengzhong

最新推荐文章于 2024-11-04 10:04:23 发布

阅读量335

点赞数

文章标签： excel c# dataset properties string null

本文链接：https://blog.csdn.net/nengzhong/article/details/2879288

版权

一、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() ;