ASP.NET MVC5+EF6+EasyUI 后台管理系统(88)-Excel导入和导出-主从表结构导出

前言

前面一篇详细讲解了导入导出,本节演示混合结构的导出功能!同时提供代码下载.. 先看效果图:这个一个混合的Excel,列表与自定义信息的混合!

  我们的步骤大概分为以下几步

  • 1.模拟数据库数据
  • 2.创建工作簿
  • 3.填充固定数据
  • 4.合并单元格
  • 5.处理动态数据

  数据及环境准备

  创建一个新的Asp.Net MVC5项目,并从Nuget包安装ClosedXML

  为了更好的跟真实数据结合,我们需要模拟一些静态数据,新建2个模型来保存数据

  public class SchoolClass
    {

        public string Id { get; set; }//班级标示
        public string Name { get; set; }//班级名称
        public string Manager { get; set; }//班主任姓名
        public string Manager2 { get; set; }//副班主任姓名
        public string PhoneNumber { get; set; }//班主任联系电话
        public string PhoneNumber2 { get; set; }//副主任联系电话
        public string Remark { get; set; } //班级说明
        public List<Students> stuList { get; set; }//一个班级对应多个学生
    }

    public class Students
    {
        public string Id { get; set; }//学号
        public string Name { get; set; }//姓名
        public string Sex { get; set; }//性别
        public string Age { get; set; }//年龄
        public string Point { get; set; }//年度得分
        public string PhoneNumber { get; set; }//电话
    }

注:从模型看出,这是一个班级信息表,一个班级对应多个学生的信息!

实现方式

1.新建一个控制器并填充数据

 public ActionResult Index()
 {
            //模拟数据库赋值,一个班级对应多个学生
            SchoolClass model = new SchoolClass();
            model.Id = "CLS0001";
            model.Name = "三年二班";
            model.Manager = "黄SIR";
            model.PhoneNumber = "13800138000";
            model.Manager2 = "李SIR";
            model.PhoneNumber2 = "13888138666";
            model.Remark = "这是一段有很多个字的班级说明,只有足够长的字,才能证明这段文字很长,如果100个字还不够长,那么就再来100个字!";
            model.stuList = new List<Students>();
            model.stuList.Add(new Students() { Id = "STU0001", Name = "牛掰掰", Sex = "男", Age = "23",Point="80", PhoneNumber = "13545678547" });
            model.stuList.Add(new Students() { Id = "STU0002", Name = "张三", Sex = "女", Age = "23", Point = "70", PhoneNumber = "13545654874" });
            model.stuList.Add(new Students() { Id = "STU0003", Name = "李四", Sex = "女", Age = "25", Point = "50", PhoneNumber = "13545633552" });
            model.stuList.Add(new Students() { Id = "STU0004", Name = "王五", Sex = "男", Age = "22", Point = "66", PhoneNumber = "13566885541" });
            model.stuList.Add(new Students() { Id = "STU0005", Name = "林蛋大", Sex = "男", Age = "26", Point = "95", PhoneNumber = "13821298458" });
            model.stuList.Add(new Students() { Id = "STU0006", Name = "刘丽丽", Sex = "女", Age = "19", Point = "95", PhoneNumber = "13821298458" });

}

2.创建作业本并填充表头

红框中表示表示表头位置,我们Excel大家都知道是按位置标示的,比如我点中的三年二班表示的是B3(左上角显示),所有我们填充表头也是这么做

   var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("班级");
            ws.Cell("A1").Value = model.Name+"班级信息表";
            //标题
            ws.Cell("A2").Value = "班级代号";
            ws.Cell("B2").Value = "班级名称";
            ws.Cell("C2").Value = "班主任";
            ws.Cell("D2").Value = "联系电话";
            ws.Cell("E2").Value = "副班主任";
            ws.Cell("F2").Value = "联系电话";
            //主表内容
            ws.Cell("A3").Value = model.Id;
            ws.Cell("B3").Value = model.Name;
            ws.Cell("C3").Value = model.Manager;
            ws.Cell("D3").Value = model.PhoneNumber;
            ws.Cell("E3").Value = model.Manager2;
            ws.Cell("F3").Value = model.PhoneNumber2;
            ws.Cell("A4").Value = model.Remark;//说明

            //明细表标题
            ws.Cell("A5").Value = "学号";
            ws.Cell("B5").Value = "姓名";
            ws.Cell("C5").Value = "性别";
            ws.Cell("D5").Value = "年龄";
            ws.Cell("E5").Value = "得分";
            ws.Cell("F5").Value = "电话号码";

所以位置决定内容,填充表头后。我们再填充列表

   for (int i = 0; i < model.stuList.Count(); i++)
            {
                ws.Cell(i + 6, 1).Value = model.stuList[i].Id;
                ws.Cell(i + 6, 2).Value = model.stuList[i].Name;
                ws.Cell(i + 6, 3).Value = model.stuList[i].Sex;
                ws.Cell(i + 6, 4).Value = model.stuList[i].Age;
                ws.Cell(i + 6, 5).Value = model.stuList[i].PhoneNumber;
                ws.Cell(i + 6, 6).Value = model.stuList[i].PhoneNumber;
            }

由于我们列表数据是动态并不是固定的,工作簿也是支持二维数组位置填充!看代码从第6行开始,一行一行填充!

3.合并和填充

       var rngTable = ws.Range("A1:F" + (model.stuList.Count() + 5));

            //合并表头
            var rngHeaders = rngTable.Range("A1:F1");
            ws.Row(1).Height = 20;
            rngHeaders.FirstCell().Style
             .Font.SetBold()
             .Fill.SetBackgroundColor(XLColor.Buff)
             .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
            rngHeaders.FirstRow().Merge();

            //第二行表头样式
            rngHeaders = rngTable.Range("A2:F2"); // The address is relative to rngTable (NOT the worksheet)
            rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            rngHeaders.Style.Font.Bold = true;
            //rngHeaders.Style.Font.FontColor = XLColor.DarkBlue;
            rngHeaders.Style.Fill.BackgroundColor = XLColor.Aqua;


            //合并说明
            var rngRemark = rngTable.Range("A4:F4");
            ws.Row(4).Height = 30;
            rngRemark.Style.Alignment.WrapText = true;
            rngRemark.FirstCell().Comment.Style.Size.SetAutomaticSize();
            rngRemark.FirstRow().Merge();

            rngTable = ws.Range("A5:F" + (model.stuList.Count() + 5));
            var excelTable = rngTable.CreateTable();

            ws.Columns().AdjustToContents(); 

最后我们要做:填充颜色,合并单元格就好,比如说明:说明内容我们是填充在A4,但是我们要合并A1-F4的位置

 public ActionResult Index()
        {
            //模拟数据库赋值,一个班级对应多个学生
            SchoolClass model = new SchoolClass();
            model.Id = "CLS0001";
            model.Name = "三年二班";
            model.Manager = "黄SIR";
            model.PhoneNumber = "13800138000";
            model.Manager2 = "李SIR";
            model.PhoneNumber2 = "13888138666";
            model.Remark = "这是一段有很多个字的班级说明,只有足够长的字,才能证明这段文字很长,如果100个字还不够长,那么就再来100个字!";
            model.stuList = new List<Students>();
            model.stuList.Add(new Students() { Id = "STU0001", Name = "牛掰掰", Sex = "男", Age = "23",Point="80", PhoneNumber = "13545678547" });
            model.stuList.Add(new Students() { Id = "STU0002", Name = "张三", Sex = "女", Age = "23", Point = "70", PhoneNumber = "13545654874" });
            model.stuList.Add(new Students() { Id = "STU0003", Name = "李四", Sex = "女", Age = "25", Point = "50", PhoneNumber = "13545633552" });
            model.stuList.Add(new Students() { Id = "STU0004", Name = "王五", Sex = "男", Age = "22", Point = "66", PhoneNumber = "13566885541" });
            model.stuList.Add(new Students() { Id = "STU0005", Name = "林蛋大", Sex = "男", Age = "26", Point = "95", PhoneNumber = "13821298458" });
            model.stuList.Add(new Students() { Id = "STU0006", Name = "刘丽丽", Sex = "女", Age = "19", Point = "95", PhoneNumber = "13821298458" });



            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("班级");
            ws.Cell("A1").Value = model.Name+"班级信息表";
            //标题
            ws.Cell("A2").Value = "班级代号";
            ws.Cell("B2").Value = "班级名称";
            ws.Cell("C2").Value = "班主任";
            ws.Cell("D2").Value = "联系电话";
            ws.Cell("E2").Value = "副班主任";
            ws.Cell("F2").Value = "联系电话";
            //主表内容
            ws.Cell("A3").Value = model.Id;
            ws.Cell("B3").Value = model.Name;
            ws.Cell("C3").Value = model.Manager;
            ws.Cell("D3").Value = model.PhoneNumber;
            ws.Cell("E3").Value = model.Manager2;
            ws.Cell("F3").Value = model.PhoneNumber2;
            ws.Cell("A4").Value = model.Remark;//说明

            //明细表标题
            ws.Cell("A5").Value = "学号";
            ws.Cell("B5").Value = "姓名";
            ws.Cell("C5").Value = "性别";
            ws.Cell("D5").Value = "年龄";
            ws.Cell("E5").Value = "得分";
            ws.Cell("F5").Value = "电话号码";

   

            for (int i = 0; i < model.stuList.Count(); i++)
            {
                ws.Cell(i + 6, 1).Value = model.stuList[i].Id;
                ws.Cell(i + 6, 2).Value = model.stuList[i].Name;
                ws.Cell(i + 6, 3).Value = model.stuList[i].Sex;
                ws.Cell(i + 6, 4).Value = model.stuList[i].Age;
                ws.Cell(i + 6, 5).Value = model.stuList[i].PhoneNumber;
                ws.Cell(i + 6, 6).Value = model.stuList[i].PhoneNumber;
            }

            var rngTable = ws.Range("A1:F" + (model.stuList.Count() + 5));

            //合并表头
            var rngHeaders = rngTable.Range("A1:F1");
            ws.Row(1).Height = 20;
            rngHeaders.FirstCell().Style
             .Font.SetBold()
             .Fill.SetBackgroundColor(XLColor.Buff)
             .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
            rngHeaders.FirstRow().Merge();

            //第二行表头样式
            rngHeaders = rngTable.Range("A2:F2"); // The address is relative to rngTable (NOT the worksheet)
            rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            rngHeaders.Style.Font.Bold = true;
            //rngHeaders.Style.Font.FontColor = XLColor.DarkBlue;
            rngHeaders.Style.Fill.BackgroundColor = XLColor.Aqua;


            //合并说明
            var rngRemark = rngTable.Range("A4:F4");
            ws.Row(4).Height = 30;
            rngRemark.Style.Alignment.WrapText = true;
            rngRemark.FirstCell().Comment.Style.Size.SetAutomaticSize();
            rngRemark.FirstRow().Merge();

            rngTable = ws.Range("A5:F" + (model.stuList.Count() + 5));
            var excelTable = rngTable.CreateTable();

            ws.Columns().AdjustToContents(); 

            var exportFileName = string.Concat(
                    "ExcelSample",
                    DateTime.Now.ToString("yyyyMMddHHmmss"),
                    ".xlsx");

            return new ExportExcelResult(wb)
            {
                SheetName = "人员列表",
                FileName = exportFileName,
                Workbook = wb
            };


        }

总结

代码不难看懂,基本情景我都用到:比如合并、高度、自动换行、等等,是一篇值得参考的文章,同时也见证ClosedXML的强大 参考代码:https://github.com/closedxml/closedxml

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏10km的专栏

jface databinding:重写doSetValue方法ComputedValue实现双向多对一的数据绑定

需求说明 如下是一个简单的测试对话框,我们希望当”起始日期”按钮为勾选时,数据对象dataBean的date属性为日期组件DateTime选择的值,否则为nul...

25390
来自专栏码匠的流水账

聊聊spring cloud gateway的LoadBalancerClientFilter

本文主要研究一下spring cloud gateway的LoadBalancerClientFilter

18610
来自专栏拂晓风起

jQuery 和 json 简单例子(注意callback函数的处理!!) (servlet返回json,jquery更新,java json)

13630
来自专栏上善若水

CG008收费软件库Asprise ocr库识别图片验证码

15730
来自专栏马洪彪

IniHelper——INI操作辅助类

使用INI配置文件,简单便捷。 该辅助工具类为C#操作INI文件的辅助类,源码在某位师傅的基础上完善的来,因为忘记最初的来源了,因此不能提及引用,在此深感遗憾,...

42170
来自专栏马洪彪

使用NOPI读取Word、Excel文档内容

使用NOPI读取Excel的例子很多,读取Word的例子不多。 Excel的解析方式有多中,可以使用ODBC查询,把Excel作为一个数据集对待。也可以使用文档...

47760
来自专栏菩提树下的杨过

利用Boost影响Lucene查询结果的排序

以下代码在Lucene2.1下通过,主要是通过设置Document的Boost来影响文档的权重,以达到控制查询结果顺序的目的(前提是不利用Sort排序的情况下...

20280
来自专栏跟着阿笨一起玩NET

LINQ 从 CSV 文件生成 XML

本文参考:http://msdn.microsoft.com/zh-cn/library/bb387090.aspx

9210
来自专栏Java成神之路

Java企业微信开发_02_通讯录同步

       登录企业微信—>管理工具—>通讯录同步助手—>开启“API接口同步”  ; 开启后,即可看到通讯录密钥,也可设置通讯录API的权限:读取或者编辑通...

1K20
来自专栏菩提树下的杨过

利用Boost影响Lucene查询结果的排序

以下代码在Lucene2.1下通过,主要是通过设置Document的Boost来影响文档的权重,以达到控制查询结果顺序的目的(前提是不利用Sort排序的情况下...

20660

扫码关注云+社区

领取腾讯云代金券