前言
前面一篇详细讲解了导入导出，本节演示混合结构的导出功能！同时提供代码下载..
先看效果图：这个一个混合的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
};
}
Index完整代码
总结
代码不难看懂，基本情景我都用到：比如合并、高度、自动换行、等等，是一篇值得参考的文章，同时也见证ClosedXML的强大
参考代码：https://github.com/closedxml/closedxml
作者：YmNets
出处：http://ymnets.cnblogs.com/
随时随地看视频
