封装Excel类的导入与导出

  1using System;
  2using System.Collections;
  3using System.ComponentModel;
  4using System.Data;
  5using System.Drawing;
  6using System.Web;
  7using System.Web.SessionState;
  8using System.Web.UI;
  9using System.Web.UI.WebControls;
 10using System.Web.UI.HtmlControls;
 11using System.Data.OleDb;
 12using Excel;
 13
 14namespace StoreManager
 15 {
 16
 17
 18    public class ImportExportToExcel
 19    {
 20        private string strConn ;
 21  
 22        public ImportExportToExcel()
 23        {
 24        }
 25
 26        //从Excel文件导入到DataSet
 27        从Excel文件导入到DataSet#region 从Excel文件导入到DataSet
 28        /**//// 从指定的Excel文件导入
 29        public DataSet ImportFromExcel(string strFileName)
 30        {
 31            DataSet ds=new DataSet();
 32            ds=doImport(strFileName);
 33            return ds;
 34        }
 35    
 36        /**//// 执行导入
 37        private DataSet doImport(string strFileName)
 38        {
 39            if (strFileName=="") return null;
 40            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
 41                "Data Source=" +  strFileName + ";" +
 42                "Extended Properties=Excel 8.0;";
 43            OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
 44            DataSet ExcelDs = new DataSet();
 45            try
 46            {
 47                ExcelDA.Fill(ExcelDs, "ExcelInfo");
 48                
 49            }
 50            catch(Exception err)
 51            {
 52                System.Console.WriteLine( err.ToString() );
 53            }
 54            return ExcelDs;
 55        }
 56        #endregion
 57
 58        //从DataSet到出到Excel
 59        从DataSet到出到Excel#region 从DataSet到出到Excel
 60        /**//// 导出指定的Excel文件
 61        public void ExportToExcel(DataSet ds,string strExcelFileName)
 62        {
 63            if (ds.Tables.Count==0 || strExcelFileName=="") return;
 64            doExport(ds,strExcelFileName);
 65    
 66
 67        }
 68        /**//// 执行导出
 69        private void doExport(DataSet ds,string strExcelFileName)
 70        {
 71            
 72            Excel.Application excel= new Excel.Application();
 73            int rowIndex=1;
 74            int colIndex=0;
 75            excel.Application.Workbooks.Add(true);
 76            System.Data.DataTable table=ds.Tables[0] ;
 77            foreach(DataColumn col in table.Columns)
 78            {
 79                colIndex++;    
 80                excel.Cells[1,colIndex]=col.ColumnName;                
 81            }
 82
 83            foreach(DataRow row in table.Rows)
 84            {
 85                rowIndex++;
 86                colIndex=0;
 87                foreach(DataColumn col in table.Columns)
 88                {
 89                    colIndex++;
 90                    excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
 91                }
 92            }
 93            excel.Visible=false;    
 94        //    excel.Sheets[0] = "sss";
 95            excel.ActiveWorkbook.SaveAs(strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null,null);
 96            excel.Quit();
 97            excel=null;
 98            GC.Collect();//垃圾回收
 99        }
100        #endregion
101
102        //从XML导入到Dataset
103        从XML导入到Dataset#region 从XML导入到Dataset
104        /**//// 从指定的XML文件导入
105        public DataSet ImportFromXML(string strFileName)
106        {
107            if (strFileName=="")
108                return null;
109            DataSet ds=new DataSet();
110            try{ds.ReadXml(strFileName,System.Data.XmlReadMode.Auto);}
111            catch{}
112            return ds;
113        }
114        #endregion    从DataSet导出到XML
115        //从DataSet导出到XML
116        从DataSet导出到XML#region 从DataSet导出到XML
117        /**//// 导出指定的XML文件
118        public void ExportToXML(DataSet ds,string strXMLFileName)
119        {
120            if (ds.Tables.Count==0 || strXMLFileName=="") return;
121            doExportXML(ds,strXMLFileName);
122        }
123        /**//// 执行导出
124        private void doExportXML(DataSet ds,string strXMLFileName)
125        {
126            try
127            {ds.WriteXml(strXMLFileName);}
128            catch(Exception ex)
129            {throw ex;}    
130        }
131        #endregion
132    }
133
134}