2009-09-10 17:03

    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";

        }

        從Excel文件導入到DataSet#region 從Excel文件導入到DataSet
        //        /// <summary>
        //        /// 從Excel導入文件
        //        /// </summary>
        //        /// <param name="strExcelFileName">Excel文件名</param>
        //        /// <returns>返回DataSet</returns>
        //        public DataSet ImportFromExcel(string strExcelFileName)
        //        {
        //            return doImport(strExcelFileName);
        //        }
        /**//// <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;";
            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

        從DataSet到出到Excel#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)
        {
           
            Excel.Application excel= new 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+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
           
           
            //wkbNew.SaveAs strBookName


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

        從XML導入到Dataset#region 從XML導入到Dataset

        /**//// <summary>
        /// 從選擇的XML文件導入
        /// </summary>
        /// <returns>DataSet</returns>
        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;
        }
        /**//// <summary>
        /// 從指定的XML文件導入
        /// </summary>
        /// <param name="strFileName">XML文件名</param>
        /// <returns></returns>
        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

        從DataSet導出到XML#region 從DataSet導出到XML
        /**//// <summary>
        /// 導出指定的XML文件
        /// </summary>
        /// <param name="ds">要導出的DataSet</param>
        /// <param name="strXMLFileName">要導出的XML文件名</param>
        public void ExportToXML(DataSet ds,string strXMLFileName)
        {
            if (ds.Tables.Count==0 || strXMLFileName=="") return;
            doExportXML(ds,strXMLFileName);
        }
        /**//// <summary>
        /// 導出用戶選擇的XML文件
        /// </summary>
        /// <param name="ds">DataSet</param>
        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);
        }

        /**//// <summary>
        /// 執行導出
        /// </summary>
        /// <param name="ds">要導出的DataSet</param>
        /// <param name="strExcelFileName">要導出的XML文件名</param>
        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
    }

giga0066 發表在 痞客邦 PIXNET 留言(0) 人氣()