npoi导出到EXCEL

最新推荐文章于 2023-02-09 20:34:54 发布

放羊的星星_morelife

最新推荐文章于 2023-02-09 20:34:54 发布

阅读量444

点赞数

分类专栏： NPOI

本文链接：https://blog.csdn.net/u013430672/article/details/39698173

版权

NPOI 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

前台代码：

<%@ Page Title="主页" Language="C#" AutoEventWireup="true"

CodeBehind="Default.aspx.cs" Inherits="NPOI导出到EXCEL._Default" %>

<html>
<body>
<body>
<form runat="server">

<asp:Button ID="btnExport" runat="server" Text="导出" />
<asp:GridView ID="grdRylist" runat="server" AutoGenerateColumns="false" >
<Columns>
<asp:BoundField HeaderText="员工编号" DataField="ygbh" />
<asp:BoundField HeaderText="员工姓名" DataField="ygxm" />
<asp:BoundField HeaderText="入职日期" DataField="rzrq" />
</Columns>

</asp:GridView>
</form>
</body>

</body>

</html>

后台代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using NPOI.SS.UserModel;
using System.Collections;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;

namespace NPOI导出到EXCEL
{
    public partial class _Default : System.Web.UI.Page
    {
        //采用VIEWSTATE保存数据源，方便导出的时候调用
        public DataTable DataSource
        {
            get
            {
                return ViewState["source"] == null ? null : (DataTable)ViewState["source"];
            }
            set
            {
                ViewState["source"] = value;
            }
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            btnExport.Click += new EventHandler(btnExport_Click);
            if (!IsPostBack)
            {
                Bind();
            }
        }

        /// <summary>
        /// 导出到EXCEL
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void btnExport_Click(object sender, EventArgs e)
        {

            IWorkbook book = new HSSFWorkbook();
            ISheet sheet = book.CreateSheet();//创建一个工作薄，所以如果要将不同的数据写入到另外一个工作薄，也是可以操作的
            DataTable dt = DataSource;//获取数据源
            int index = 0;
            foreach(DataRow dr in dt.Rows)
            {
                IRow row = sheet.CreateRow(index);//创建一行
                int j = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    string colvalue = dr[col].ToString();
                    if (col.ColumnName != "__sno")
                    {
                        ICell cell = row.CreateCell(j);//创建一个单元格
                        switch (col.DataType.ToString())//根据列的类型不同，给单元格赋值
                        {
                            case "System.String":
                                cell.SetCellValue(colvalue);
                                break;
                            case "System.DateTime":
                                DateTime temp;
                                DateTime.TryParse(colvalue,out temp);
                                cell.SetCellValue(colvalue);
                                break;
                            default:
                                cell.SetCellValue("");
                                break;
                        }
                        j++;
                    }
                }
                index++;
            }
            MemoryStream stream = new MemoryStream();
            book.Write(stream);
            stream.Flush();
            stream.Position = 0;
            //将数据先存在服务器上D盘，CS模式就可以采用这种方式来导出EXCEL
            using(FileStream fs = new FileStream("d:\\"+System.DateTime.Now.ToString("yyyyMMddHHmmss")+".xls",FileMode.Create,FileAccess.Write))
            {
                byte[] data = stream.ToArray();
                fs.Write(data, 0, data.Length);
            }
            //WEB方式导出到EXCEL
            HttpContext context = HttpContext.Current;
            context.Response.ContentType = "application/vnd.mx-excel";
            context.Response.ContentEncoding = Encoding.UTF8;
            context.Response.Charset = "";
            context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(System.DateTime.Now.ToString("yyyyMMddHHmmss")+".xls", Encoding.UTF8));
            context.Response.BinaryWrite(stream.GetBuffer());
            context.Response.End();
        }

        /// <summary>
        /// 绑定GRIDVIEW
        /// </summary>
        private void Bind()
        {
            string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            try
            {
                SqlDataAdapter adp = new SqlDataAdapter("select id,ygbh,ygxm,rzrq from ryxx ", conn);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                grdRylist.DataSource = ds.Tables[0];
                DataSource = ds.Tables[0];
                grdRylist.DataBind();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                conn.Close();
            }
        }
    }
}