GridView与Excel数据导入导出

GridView与Excel数据导入导出

一、将GridView的数据导入到Excel表格中

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace FileManageDll
{
    /// <summary>
    /// 将页面的控件上的数据导入到文件中公共类
    /// 需要在调用的页面中重写VerifyRenderingInServerForm方法，否则将出现错误
    ///  public override void VerifyRenderingInServerForm( Control control )
    ///  {
    ///  }
    /// 
    /// </summary>
    public class HtmlDataToDoc
    {
        private Page _InvokePage;
        /// <summary>
        /// 调用的页面
        /// </summary>
        public Page InvokePage
        {
            get { return _InvokePage; }
            set { _InvokePage = value; }
        }              
        
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="invoke_page">调用的页面，一般参数传this.Page就可以了</param>
        public HtmlDataToDoc(Page invoke_page)
        {
            _InvokePage = invoke_page;
        }

        /// <summary>
        /// 将GridView的数据导入到Excel表格中
        /// </summary>
        /// <param name="gdv">页面的GridView</param>
        /// <param name="fileName">默认的Excel文件名</param>
        public void GridViewDataToExcel( System.Web.UI.WebControls.GridView gdv ,string fileName)
        {
            if(fileName.ToLower().IndexOf(".xls") == -1)
            {
                fileName = fileName + ".xls";
            }

            Export(gdv, fileName, "application/ms-excel"); 
        }

        /// <summary>
        /// 将GridView的数据导入到Word文件中
        /// </summary>
        /// <param name="gdv">页面的GridView</param>
        /// <param name="fileName">默认的Word文件名</param>
        public void GridViewDataToWord( System.Web.UI.WebControls.GridView gdv, string fileName )
        {
            if(fileName.ToLower().IndexOf(".doc") == -1)
            {
                fileName = fileName + ".doc";
            }

            Export(gdv, fileName, "application/ms-word");
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="gdv"></param>
        /// <param name="fileName"></param>
        /// <param name="typeName"></param>
        private void Export( System.Web.UI.WebControls.GridView gdv, string fileName, string typeName )
        {
            System.Web.HttpResponse httpResponse = _InvokePage.Response;
            httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            httpResponse.ContentType = typeName;
            _InvokePage.EnableViewState = false;
            StringWriter tw = new StringWriter();
            System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
            gdv.RenderControl(hw);
            httpResponse.Write(tw.ToString());
            httpResponse.End();
        } 
    }
}



页面调用代码：
 protected void lbtnToExcel_Click( object sender, EventArgs e )
        {
            string fileName = "导出数据";

            FileManageDll.HtmlDataToDoc hdtd = new FileManageDll.HtmlDataToDoc(this.Page);
            hdtd.GridViewDataToExcel(this.gridViewList, fileName);
        }
      
     //必须重写改方法
        public override void VerifyRenderingInServerForm( Control control )
        {
          }

并且有一点需要注意的是，页面的EnableViewState必须为True，否则导出的文件数据会为空。

二、将Excel的数据导入到数据库中

        public void InsertToDataBase()

        {

            string sql = "insert into tbStudent(studentNum,studentName,studentClassNum,studentSpecialty,studentDepartment,studentEnrollmentYear) select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=" + Server.MapPath("~/excelUpload/") + "uploadStudent.xls',sheet1$)";

            StudentBLL stu = new StudentBLL();

            int tag = stu.InsertIntoDataBase(sql);

            if (tag != 0)

            {

                alert.showAndGo("导入成功！", "studentInfoQuery.aspx");

            }

            else

            {

                alert.show("导入失败！");

            }

        }

 

        protected void btnImport_Click(object sender, EventArgs e)

        {

            string HonorPath = @"~/excelUpload/";

            if (fpLoad.HasFile)

            {

                string ext = Path.GetExtension(fpLoad.FileName).ToLower();

                if (!(ext == ".xls"))

                {

                    alert.show("上传的文件必须是.xls的文件！");

                    return;

                }

                else

                {

                    string logoFile = "uploadStudent.xls";

                    fpLoad.SaveAs(Server.MapPath(HonorPath + logoFile));

                }

            }

            InsertToDataBase();

        }

用上面这种方法，Excel文件的“选项卡”文件名必须是“sheet1”。

 

下面对InsertToDataBase（）方法做些改进：

 

        public void InsertToDataBase()
        {
            try
            {
                string fileName = Server.MapPath("~/excelUpload/") + "uploadStudent.xls";
                object opt = System.Reflection.Missing.Value;
                Application app = new Application();
                _Workbook book = app.Workbooks.Open(fileName, opt, true, opt, opt, opt, true, opt, opt, opt, true, opt, opt, opt, opt);    // 只读，可编辑;
                Sheets sheets = book.Worksheets;
                _Worksheet[] wsArray = new _Worksheet[sheets.Count];
                wsArray[0] = (_Worksheet)sheets.get_Item(1);
                string sheetName = wsArray[0].Name;

                string sql = "insert into tbStudent(studentNum,studentName,studentClassNum,studentSpecialty,studentDepartment,studentEnrollmentYear) select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=" + fileName + "'," + sheetName + "$)";
                StudentBLL stu = new StudentBLL();
                stu.InsertIntoDataBase(sql);
                alert.showAndGo("导入成功！", "studentInfoQuery.aspx");
            }
            catch (Exception ex)
            {
                alert.show("导入失败！");
            }
        }