Excel的导入导出.Net

/**//*------------------------------------------------------------------------------------
 * 文件名：ExcelCtrol.cs
 * 文件功能描述：Excel的导入导出
 *
 * 创建标识：吴璐     20080722
 *
 * 修改标识：
 * 修改描述：
 *
* ----------------------------------------------------------------------------------*/


using System;
using System.Text;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO;
using Wiscom.Data;
using Excel=Microsoft.Office.Interop.Excel;

namespace Wiscom.Urp.ReceiveFreshman
{
    /**//// <summary>
    /// Excel的控制和基本操作
    /// </summary>
    public class ExcelCtrol
    {

 

        /**//// <summary>
        ///  <br>获取Excel中的表名字</br>
        /// <br>2008-07-22 ljx</br>
        /// </summary>
        /// <param name="filepath">Excel文件路径</param>
       
        public static ArrayList GetExcelSheetName(string filepath)  
        {
            ArrayList al = new ArrayList();
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();
                DataTable sheetNames = conn.GetOleDbSchemaTable
                (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                conn.Close();
                foreach (DataRow dr in sheetNames.Rows)
                {
                    al.Add(dr[2]);
                }
            }
            catch (Exception ex) {}


            return al;
        }


        /**//// <summary>
        ///  <br>excel文件读取到DataSet</br>
        /// <br>2008-07-22 ljx</br>
        /// </summary>      
        /// <param name="filepath">Excel文件路径</param>
        /// <param name="sheetname">Excel文件中的表</param>
        public static DataSet ExcelDataSet(string filepath, string sheetname)
        {
            string strConn;
            DataSet ds = new DataSet();
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
                oada.Fill(ds);
            }
            catch (Exception ex) { }
            return ds;

        }

        /**//// <summary>
        /// 得到宿舍信息Excel文件的DataSet
        /// <br>2008-07-22 ljx</br>
        /// </summary>
        /// <param name="filepath">Excel文件路径</param>
        /// <param name="sheetname">Excel文件中的表</param>
        /// <returns></returns>
        public static DataSet Get_Ssxx_DataSet(string filepath, string sheetname) //把excel文件读取到DataSet
        {
            string strConn;
            DataSet ds = new DataSet();
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                OleDbDataAdapter oada = new OleDbDataAdapter("select 学号,姓名,学院,楼栋,寝室号 from [" + sheetname + "]", strConn);
                oada.Fill(ds);

            }
            catch (Exception ex) { }
            return ds;

        }


        public static DataSet Get_StudentInfo_DataSet(string filepath, string sheetname) //把excel文件读取到DataSet
        {
            string strConn;
            DataSet ds = new DataSet();
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                OleDbDataAdapter oada = new OleDbDataAdapter("select 学号,姓名,性别,院系代码,专业代码,班级代码,学籍状态,出生年月,生源地,籍贯,身份证号,民族编码,政治面貌码 from [" + sheetname + "]", strConn);
                oada.Fill(ds);

            }
            catch (Exception ex) { }
            return ds;

        }

 

 

        /**//// <summary>
        /// 得到宿舍信息Excel文件的DataSet
        /// <br>2008-07-22 ljx</br>
        /// </summary>
        /// <param name="filepath">文件路径</param>
        /// <param name="sheetname">Excel文件中的表</param>
        /// <returns></returns>
        public static DataSet Get_Ssxx_DataSet(string filepath, ArrayList sheetname) //把excel文件读取到DataSet
        {
            string strConn;
            DataSet ds = new DataSet();
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                OleDbDataAdapter oada = new OleDbDataAdapter("select 学号,姓名,学院,楼栋,寝室号 from [" + sheetname + "]", strConn);
                oada.Fill(ds);

            }
            catch (Exception ex) { }
            return ds;

        }

        /**//// <summary>
        /// 导出 DataSet为Excel文件格式[!导出的Excel没有加工!]
        /// <br>2008-07-22 ljx</br>
        /// </summary>
        /// <param name="strFileName">导出名称</param>
        /// <param name="dt">DataSet</param>
        public static void ExportExcel(string strFileName, DataSet dt)
        {
            //清除Response缓存内容
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            strFileName = strFileName + ".xls";
            //确定字符的编码格式
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName));
            HttpContext.Current.Response.ContentType = "aapplication/vnd.ms-excel";
            HttpContext.Current.Response.Charset = "gb2312";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            GridView dv = new GridView();
            dv.DataSource = dt;
            //dv.RowDataBound += GridView2_RowDataBound;//事件
            dv.DataBind();


            try
            {
                dv.Page.EnableViewState = false;
            }
            catch
            { }
            System.IO.StringWriter swBody = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter hwBody = new System.Web.UI.HtmlTextWriter(swBody);
            dv.RenderControl(hwBody);
            //消除乱码特别设定，非常规方法
            string strExcel = "";
            strExcel = "";
            strExcel += hwBody.InnerWriter.ToString();
            HttpContext.Current.Response.Write(strExcel);
            HttpContext.Current.Response.End();
        }


        /**//// <summary>
        /// 已控件格式导出(2008-07-23)
        /// public override void VerifyRenderingInServerForm(Control control)  
        /// 必须调用该函数之前,重写VerifyRenderingInServerForm
        /// </summary>
        /// <param name="ctl">GridView控件</param>
        /// <param name="FileName">文件输出名字</param>

        public static void ToExcel(Control ctl, string FileName)
        {
            HttpContext.Current.Response.Charset = "Gb2312";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName);
            ctl.Page.EnableViewState = false;
            System.IO.StringWriter tw = new System.IO.StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(tw);
            ctl.RenderControl(hw);
            HttpContext.Current.Response.Write(tw.ToString());
            HttpContext.Current.Response.End();
        }

        public static void ToExcel(String Sql, string FileName)
        {
            //Add Response header
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.csv", FileName));

            HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            //GET Data From Database
            try
            {
               
                SqlDataReader dr = (SqlDataReader)DataHelper.GetDataReader(Sql);
                StringBuilder sb = new StringBuilder();
                //
                //Add Header
                //
                for (int count = 0; count < dr.FieldCount; count++)
                {
                    if (dr.GetName(count) != null)
                        sb.Append(dr.GetName(count));
                    if (count < dr.FieldCount - 1)
                    {
                        sb.Append(",");
                    }
                }
                HttpContext.Current.Response.Write(sb.ToString() + "\n");
                HttpContext.Current.Response.Flush();
                //
                //Append Data
                //
                while (dr.Read())
                {
                    sb = new StringBuilder();

                    for (int col = 0; col < dr.FieldCount - 1; col++)
                    {
                        if (!dr.IsDBNull(col))
                            sb.Append(dr.GetValue(col).ToString().Replace(",", " "));
                        sb.Append(",");
                    }
                    if (!dr.IsDBNull(dr.FieldCount - 1))
                        sb.Append(dr.GetValue(dr.FieldCount - 1).ToString().Replace(",", " "));
                    HttpContext.Current.Response.Write(sb.ToString() + "\n");
                    HttpContext.Current.Response.Flush();
                }
                dr.Dispose();
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write(ex.Message);
            }
            finally
            {
        
            }
            HttpContext.Current.Response.End();
        }

 
        public static void ToExcel(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.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS", Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null);
            excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS", Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
           
            //wkbNew.SaveAs strBookName


            //excel.Save(strExcelFileName);
            excel.Quit();
            excel = null;

            GC.Collect();//垃圾回收
        }
   

 

 

        /**//// <summary>
        /// 删除Excel文件
        /// 2008-07-02 ljx
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        public static void DelExcel(string FilePath)
        {
           
            FileInfo ExcelFile = new FileInfo(FilePath);
            ExcelFile.Delete();
       
        }

 

 

 
    }

 
}