C#操作Excel----Excel数据的导入导…

 

C#操作Excel,首先需要引用Excel的Office COM组件，新建项目后，添加对应Office版本的Microsoft.Office.Interop.Excel的引用.

 

 

public class ImportExportToExcel

{

        private string strConn ;   //定义Excel数据库连接字符串     

        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>
        /// <returns>DataSet</returns>       

     public DataSet ImportFromExcel()
        {
            DataSet ds=new DataSet();
            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                ds=doImport(openFileDlg.FileName);
            return ds;
        }
        

      /// <summary>
        /// 从指定的Excel文件导入
        /// </summary>
        /// <param name="strFileName">Excel文件名</param>
        /// <returns></returns>
        public DataSet ImportFromExcel(string strFileName)
        {
            DataSet ds=new DataSet();
            ds=doImport(strFileName);
            return ds;
        }
    

      /// <summary>
        /// 执行导入
        /// </summary>
        /// <param name="strFileName">文件名</param>
        /// <returns>DataSet</returns>

     private DataSet doImport(string strFileName)
        {
            if (strFileName=="") return null;
              
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" +  strFileName + 
                "';Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";//注意连接串中的单引号(')，

         //参数Excel8.0必须是和服务器上对应的Excel版本匹配

         OleDbConnection OleDB = new OleDbConnection(strConn);//打开数据源连接

         OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", OleDB);

         //上面Sheet1是要操作的Excel表的第一个页的名称，如页名已改，Sheet1必须也改成相应的的名称

            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>
        /// 导出到用户指定的Excel文件
           /// </summary>
           /// <param name="ds">要导出的DataSet</param>

      /// <param name="ds">要导入到的Excel文件名</param>
        private void doExport(DataSet ds,string strExcelFileName)
        {
            
            Excel.Application excel= new Excel.Application();
            
            int rowIndex=1;
            int colIndex=0;

            excel.Application.Workbooks.Add(true);//新增加一个工作簿，Workbook是直接保存，不会弹出

         //保存对话框，加上Application会弹出保存对话框，值为false会报错 
            System.Data.DataTable table=ds.Tables[0] ;
            foreach(DataColumn col in table.Columns) //生成Excel中列头名称
            {
                colIndex++;    
                excel.Cells[1,colIndex]=col.ColumnName;                
            }

            foreach(DataRow row in table.Rows)  //把ds当前页的数据保存在Excel中
            {
                rowIndex++;
                colIndex=0;
                foreach(DataColumn col in table.Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
                }
            }
            excel.Visible=false; //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在

         //往里写

         excel.ActiveWorkbook.SaveAs(strExcelFileName + ".xls", XlFileFormat.xlExcel7, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null,null);  //注意标红的参数，要和服务器上的Excel版本兼容          
            

          //方法二
            //保存工作簿  
            //excel.Application.Workbooks.Add(true).Save();   
            //保存excel文件  
            //excel.Save(strExcelFileName);  

 

         //确保Excel进程关闭

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

        #region 从XML导入到Dataset

        /// 
        /// 从选择的XML文件导入
        /// 
        /// DataSet
        public DataSet ImportFromXML()
        {
            DataSet ds=new DataSet();
            System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
            openFileDlg.DefaultExt="xml";
            openFileDlg.Filter= "xml文件 (*.xml)|*.xml";
            if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                try {ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
                catch {}
            return ds;
        }
        /// 
        /// 从指定的XML文件导入
        /// 
        /// XML文件名
        /// 
        public DataSet ImportFromXML(string strFileName)
        {
            if (strFileName=="")
                return null;
            DataSet ds=new DataSet();
            try

         {

              ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);

          }
            catch{}
            return ds;
        }
        
        #endregion

      #region 从DataSet导出到XML
        /// 
        /// 导出指定的XML文件
        /// 
        /// 要导出的DataSet
        /// 要导出的XML文件名
        public void ExportToXML(DataSet ds,string strXMLFileName)
         {
            if (ds.Tables.Count==0 || strXMLFileName=="") return;
            doExportXML(ds,strXMLFileName);
        }
        /// 
        /// 导出用户选择的XML文件
        /// 
        /// DataSet
        public void ExportToXML(DataSet ds)
         {
            System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog(); 
            saveFileDlg.DefaultExt="xml";
            saveFileDlg.Filter= "xml文件 (*.xml)|*.xml";
            if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
                doExportXML(ds,saveFileDlg.FileName);
        }

        /// 
        /// 执行导出
        /// 
        /// 要导出的DataSet
        /// 要导出的XML文件名
        private void doExportXML(DataSet ds,string strXMLFileName)
         {
            try
             {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
            catch(Exception ex)
             {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}    
        }

        #endregion

    
    
    }