Npoi List DataTable导出一个Excel多个sheet 下载

参考：

http://blog.csdn.net/zhouqinghe24/article/details/8649346 参考下载
http://www.cnblogs.com/dyllove98/archive/2013/08/06/3241515.html 参考多个sheet
http://www.cnblogs.com/jicheng/p/5961257.html 参考列表写入

1、nuget搜索安装Npoi

2、代码

  public class UserInfo
    {
        public string Name { get; set; }
        public string Id { get; set; }
        public string Phone { get; set; }
    }

  /// <summary>
        /// 生成excel文件到内存中
        /// </summary>
        /// <returns></returns>
        public void CreateExcel()
        {
            MemoryStream ms = new MemoryStream();

            List<UserInfo> listUser = new List<UserInfo>()
           {
               new UserInfo { Name="1", Id="1", Phone="1r" },
               new UserInfo { Name="2", Id="2", Phone="2r" },
               new UserInfo { Name="3", Id="3", Phone="3r" },
               new UserInfo { Name="4", Id="4", Phone="4r" },
               new UserInfo { Name="5", Id="5", Phone="5r" },
           };

            //创建工作簿对象
            var workbook = new HSSFWorkbook();

            #region DataTable数据
            //创建工作表
            ISheet sheet = workbook.CreateSheet("一个sheet");
            IRow row0 = sheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("用户Id");
            row0.CreateCell(1).SetCellValue("用户名称");
            row0.CreateCell(2).SetCellValue("用户备注信息");

            
            var dtSource = GetDataTable();
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                row0 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    row0.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
                }
            }

            #endregion

            #region list数据
            ISheet sheet2 = workbook.CreateSheet("另一个sheet");
            IRow row2 = sheet2.CreateRow(0);
            row2.CreateCell(0).SetCellValue("用户Id2");
            row2.CreateCell(1).SetCellValue("用户名称2");
            row2.CreateCell(2).SetCellValue("用户备注信息2");
            
            for (int r = 1; r < listUser.Count; r++)
            {
                //创建行row
                IRow row = sheet2.CreateRow(r);
                row.CreateCell(0).SetCellValue(listUser[r].Id);
                row.CreateCell(1).SetCellValue(listUser[r].Name);
                row.CreateCell(2).SetCellValue(listUser[r].Phone);
            }

            #endregion

            workbook.Write(ms);

            ms.Flush();
            ms.Position = 0;

            var fileName = "测试Excel" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";//xls
            DownloadExcel(ms, fileName);
            
        }

        /// <summary>
        /// 生成http流下载文件
        /// </summary>
        /// <param name="ms"></param>
        /// <param name="fileName"></param>
        private static void DownloadExcel(MemoryStream ms, string fileName)
        {
            #region 处理IE、火狐等浏览器文件名乱码
            if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].IndexOf("Firefox", StringComparison.Ordinal) != -1)
            {
                fileName = "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?=";
            }
            else
            {
                fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
                fileName = fileName.Replace("+", "%20");
            }
            #endregion 
            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
            System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.Length.ToString());
            System.Web.HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
            System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream;charset=utf-8";
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
        }

        /// <summary>
        /// 模拟DataTable
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            DataTable tblDatas = new DataTable("Datas");
            DataColumn dc = null;

            dc = tblDatas.Columns.Add("Name", Type.GetType("System.String"));
            dc = tblDatas.Columns.Add("Id", Type.GetType("System.String"));
            dc = tblDatas.Columns.Add("Phone", Type.GetType("System.String"));

            DataRow newRow;
            newRow = tblDatas.NewRow();
            newRow["Name"] = "大话西游";
            newRow["Id"] = "2.0";
            newRow["Phone"] = "我很喜欢";
            tblDatas.Rows.Add(newRow);

            newRow = tblDatas.NewRow();
            newRow["Name"] = "梦幻西游";
            newRow["Id"] = "3.0";
            newRow["Phone"] = "比大话更幼稚";
            tblDatas.Rows.Add(newRow);

            return tblDatas;
        }

　　

转载于:https://www.cnblogs.com/heifengwll/p/7016032.html