打赏

按模板导出Excel

说明：开发环境 vs2012 asp.net mvc4 c# 

         注意：Excel模板有多个sheet页，导出Excel的时候，同时给多个sheet页填充数据

 1、项目结构

3、Excel模板（注意sheet页的名称）

4、HTML代码（导出按钮）

      

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EasyuiCombotree.aspx.cs" Inherits="MvcAppTest.EasyuiCombotree" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title>按模板导出Excel数据</title>

    <script type="text/javascript">

        function ExportExcel()
        {
            window.location.href = "Home/ExportExcel3";
        }
    </script>
</head>
<body>
  <div>
     <input type="button" value="导出Excel" style="width:120px;" onclick="ExportExcel()" />
  </div>    
</body>
</html>

5、Home控制器代码

public void ExportExcel3()
        {
            string path = Server.MapPath("~/loaclData/Excel/Student.xlsx");
            List<Room> myList = new List<Room>() { 
              new Room(){ ID=1, Name="班级1", Num=10},
              new Room(){ ID=2, Name="班级2", Num=20},
              new Room(){ ID=3, Name="班级3", Num=30},
              new Room(){ ID=4, Name="班级4", Num=40},
              new Room(){ ID=5, Name="班级5", Num=50}
            };
            var list01 = new List<object>() {    
                new { ID=1, Name="班级1", Num=10},
                new { ID=2, Name="班级2", Num=20},
                new { ID=3, Name="班级3", Num=30},
                new { ID=4, Name="班级4", Num=40},
                new { ID=5, Name="班级5", Num=50}};

       

            List<Company> comList = new List<Company>() { 
            new Company(){ PID=1, ADDRICE="地块位置1", NAME="企业1", Price="pr1"},
            new Company(){ PID=2, ADDRICE="地块位置2", NAME="企业2",Price="pr2"},
            new Company(){ PID=3, ADDRICE="地块位置3", NAME="企业3",Price="pr3"},
            new Company(){ PID=4, ADDRICE="地块位置4", NAME="企业4",Price="pr4"},
            new Company(){ PID=5, ADDRICE="地块位置5", NAME="企业5",Price="pr5"},
            new Company(){ PID=6, ADDRICE="地块位置6", NAME="企业6",Price="pr6"}
            };
            DataTable CompanyDt = ListToDataTable(comList);
            CompanyDt.TableName = "Company";
            DataTable ClassRoom = ListToDataTable(list01);
            ClassRoom.TableName = "Room";
            List<DataU> DataUModel = new List<DataU>() { 
            new DataU(){ dtModel=CompanyDt, shName="批而未供"},
            new DataU(){ dtModel=ClassRoom, shName="Sheet1"}
            };
            DataTableExportIsExcelList(DataUModel, "/loaclData/Excel/Student.xlsx", "sheet1", "测试1");
        }
View Code
  public class Room
    {
        public Int32 ID { get; set; }
        public string Name { get; set; }
        public Int32 Num { get; set; }
    }
View Code
    public class Company {
        public Int32 PID { get; set; }
        public string NAME { get; set; }
        public string ADDRICE { get; set; }
        public string Price { get; set; }
    }
View Code
  public class DataU
    {
        public DataTable dtModel { get; set; }
        public string shName { get; set; }
    }
View Code
     /// <summary>
        /// 讲list集合转换成datatable
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public static System.Data.DataTable ListToDataTable(IList list)
        {
            System.Data.DataTable result = new System.Data.DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    //获取类型
                    Type colType = pi.PropertyType;
                    //当类型为Nullable<>时
                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                    {
                        colType = colType.GetGenericArguments()[0];
                    }
                    result.Columns.Add(pi.Name, colType);
                }
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }
View Code
  public void DataTableExportIsExcelList(List<DataU> model, string templateFileName, string sheetName, string fileNameHead)
        {

            MemoryStream ms = OutModelFileToStreamList(model, templateFileName, sheetName);
            byte[] bt = ms.ToArray();

            string fileName = fileNameHead + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; //客户端保存的文件名  
            //以字符流的形式下载文件  
            HttpContext.Response.ContentType = "application/vnd.ms-excel";
            //   //通知浏览器下载文件而不是打开

            HttpContext.Response.AddHeader("Content-Disposition",
            "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
            HttpContext.Response.Charset = "UTF-8";
            HttpContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
            HttpContext.Response.BinaryWrite(bt);
            HttpContext.Response.Flush();
            HttpContext.Response.End();
            Response.ContentType = "application/vnd.ms-excel";
        }
View Code

 

posted @ 2019-01-17 14:39  学习靠自己  阅读(1302)  评论(0)  编辑  收藏  举报