更多详细内容
view页面：
function Download()
{
//多个查询条件
dateStart = $("#j_dataTimeStart").datebox("getValue");
dateEnd = $("#j_dataTimeEnd").datebox("getValue");
name = $("#j_name").val();
sex = $('#j_sex').val();
window.location.href = "/Main/DownLoadExcel?dateStart=" + dateStart + "&dateEnd=" + dateEnd + "&name=" + name + "&sex=" + sex;
}
Controller
1 #region 09-获取导出数据
2 /// <summary>
3 /// 09-获取导出数据
4 /// </summary>
5 /// <param name="student">用来接收用户信息（涵盖多条件）</param>
6 /// <param name="dateStart">筛选的起始时间</param>
7 /// <param name="dateEnd">筛选的结束时间</param>
8 /// <returns></returns>
9 public List<student> GetStudentList(student student, string dateStart, string dateEnd)
10 {
11 try
12 {
13 //1.获取数据源
14 var data = db.Set<student>().Where(u => true);
15
16 //2.过滤查询
17 //2.1对学生姓名进行查询
18 if (!String.IsNullOrEmpty(student.name))
19 {
20 data = data.Where(u => u.name.Contains(student.name));
21 }
22 //2.2对学生性别进行查询
23 if (!String.IsNullOrEmpty(student.sex))
24 {
25 data = data.Where(u => u.sex == student.sex);
26 }
27 //2.3对学生年龄进行查询
28 if (!String.IsNullOrEmpty(student.age))
29 {
30 data = data.Where(u => u.age.Contains(student.age));
31 }
32 //2.4对学生记录时间段进行查询
33 if ((!String.IsNullOrEmpty(dateStart)) && (!String.IsNullOrEmpty(dateEnd)))
34 {
35 DateTime dateS = Convert.ToDateTime(dateStart);//开始时间
36 DateTime dateE = Convert.ToDateTime(dateEnd);//结束时间
37 data = data.Where(u => u.dataTime >= dateS && u.dataTime <= dateE);
38 }
39 //3.返回结果数据
40 var list = data.OrderByDescending(u => u.dataTime).ToList();
41 return list;
42 }
43 catch (Exception ex)
44 {
45 FileOperateHelp.WriteFile("E:/ErrorLog333.txt", ex.Message);
46 }
47 return new List<student>();
48 }
49 #endregion
50
51 #region 10-导出学生信息方法
52 public void ExportStudentExcel(List<student> studentList, out string pathFileName)
53 {
54 #region 创建存放Excel的文件夹、文件
55 //创建存放Excel的文件夹
56 string path = Server.MapPath("~/Upload/Excel/BackFee/");
57 string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + ".xlsx";
58 pathFileName = path + fileName;
59
60 //如果上传目录不存在就创建
61 if (!Directory.Exists(path))
62 {
63 Directory.CreateDirectory(path);
64 }
65
66 FileInfo newFile = new FileInfo(pathFileName);
67 if (newFile.Exists)
68 {
69 newFile.Delete();
70 newFile = new FileInfo(pathFileName);
71 }
72 #endregion
73
74 //创建工作簿和工作表
75 using (ExcelPackage package = new ExcelPackage(newFile))
76 {
77 ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("sheet1");
78
79 #region 添加表头
80 /*添加表头*/
81 workSheet.InsertRow(1, 1);
82 using (var range = workSheet.Cells[1, 1, 1, 5])
83 {
84 range.Merge = true;//合并单元格
85 range.Style.Font.SetFromFont(new Font("Britannic Bold", 18, FontStyle.Regular));//为字体设置样式
86 range.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;//水平居中
87 range.Style.Fill.PatternType = ExcelFillStyle.Solid;
88 range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));//设置背景色
89 range.Style.Font.Color.SetColor(Color.Black);//字体颜色
90 range.Value = "学生信息统计表";
91 }
92 #endregion
93
94 #region 设置列宽
95 //设置列宽
96 workSheet.Column(1).Width = 20;
97 workSheet.Column(2).Width = 20;
98 workSheet.Column(3).Width = 20;
99 workSheet.Column(4).Width = 20;
100 workSheet.Column(5).Width = 20;
101 workSheet.Column(6).Width = 20;
102 workSheet.Column(7).Width = 20;
103 workSheet.Column(8).Width = 25;
104 #endregion
105
106 #region 设置标题
107 /*设置标题*/
108 workSheet.Cells[2, 1].Value = "学生姓名";
109 workSheet.Cells[2, 2].Value = "性别";
110 workSheet.Cells[2, 3].Value = "年龄";
111 workSheet.Cells[2, 4].Value = "自我介绍";
112 workSheet.Cells[2, 5].Value = "填写时间";
113 #endregion
114
115 using (var range = workSheet.Cells[2, 1, 2, 5])
116 {
117 range.Style.Font.Bold = true;//设置字体为粗体
118 range.Style.Fill.PatternType = ExcelFillStyle.Solid;
119 range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);//设置背景颜色
120 range.Style.Font.Color.SetColor(Color.White);//设置字体颜色
121 //range.AutoFilter = true;
122 }
123
124 #region 设置单元格内容
125 /*设置单元格内容*/
126 int row = 3;
127 foreach (student item in studentList)
128 {
129
130 workSheet.Cells[row, 1].Value = item.name;
131 workSheet.Cells[row, 2].Value = item.sex;
132 workSheet.Cells[row, 3].Value = item.age;
133 workSheet.Cells[row, 4].Value = item.content;
134 workSheet.Cells[row, 5].Value = item.dataTime.ToString();
135 workSheet.Cells[row, 6].Style.Numberformat.Format = "yyyy-MM-dd";
136 workSheet.Cells[row, 1, row, 10].Style.WrapText = true;//自动换行
137 row++;
138 }
139 #endregion
140
141 workSheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
142 //workSheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
143 package.Save();
144 }
145 }
146 #endregion
View Code
1 #region 08-导出学生信息
2 /// <summary>
3 ///08-导出学生信息
4 /// 【下载到本地】
5 /// </summary>
6 /// <param name="student">用来接收用户信息（涵盖多条件）</param>
7 /// <param name="dateStart">筛选的起始时间</param>
8 /// <param name="dateEnd">筛选的结束时间</param>
9 /// <returns></returns>
10 public FileResult DownLoadExcel(student student, string dateStart, string dateEnd)
11 {
12 List<student> studentList = GetStudentList(student, dateStart, dateEnd);
13 string pathFileName = string.Empty;
14 ExportStudentExcel(studentList, out pathFileName);
15 string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + ".xlsx";
16
17 return File(pathFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
18 }
19 #endregion
导出学生信息
1 #region 09-获取导出数据
2 /// <summary>
3 /// 09-获取导出数据
4 /// </summary>
5 /// <param name="student">用来接收用户信息（涵盖多条件）</param>
6 /// <param name="dateStart">筛选的起始时间</param>
7 /// <param name="dateEnd">筛选的结束时间</param>
8 /// <returns></returns>
9 public List<student> GetStudentList(student student, string dateStart, string dateEnd)
10 {
11 try
12 {
13 //1.获取数据源
14 var data = db.Set<student>().Where(u => true);
15
16 //2.过滤查询
17 //2.1对学生姓名进行查询
18 if (!String.IsNullOrEmpty(student.name))
19 {
20 data = data.Where(u => u.name.Contains(student.name));
21 }
22 //2.2对学生性别进行查询
23 if (!String.IsNullOrEmpty(student.sex))
24 {
25 data = data.Where(u => u.sex == student.sex);
26 }
27 //2.3对学生年龄进行查询
28 if (!String.IsNullOrEmpty(student.age))
29 {
30 data = data.Where(u => u.age.Contains(student.age));
31 }
32 //2.4对学生记录时间段进行查询
33 if ((!String.IsNullOrEmpty(dateStart)) && (!String.IsNullOrEmpty(dateEnd)))
34 {
35 DateTime dateS = Convert.ToDateTime(dateStart);//开始时间
36 DateTime dateE = Convert.ToDateTime(dateEnd);//结束时间
37 data = data.Where(u => u.dataTime >= dateS && u.dataTime <= dateE);
38 }
39 //3.返回结果数据
40 var list = data.OrderByDescending(u => u.dataTime).ToList();
41 return list;
42 }
43 catch (Exception ex)
44 {
45 FileOperateHelp.WriteFile("E:/ErrorLog333.txt", ex.Message);
46 }
47 return new List<student>();
48 }
49 #endregion
获取导出数据
1 #region 10-导出学生信息方法
2 public void ExportStudentExcel(List<student> studentList, out string pathFileName)
3 {
4 #region 创建存放Excel的文件夹、文件
5 //创建存放Excel的文件夹
6 string path = Server.MapPath("~/Upload/Excel/BackFee/");
7 string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + ".xlsx";
8 pathFileName = path + fileName;
9
10 //如果上传目录不存在就创建
11 if (!Directory.Exists(path))
12 {
13 Directory.CreateDirectory(path);
14 }
15
16 FileInfo newFile = new FileInfo(pathFileName);
17 if (newFile.Exists)
18 {
19 newFile.Delete();
20 newFile = new FileInfo(pathFileName);
21 }
22 #endregion
23
24 //创建工作簿和工作表
25 using (ExcelPackage package = new ExcelPackage(newFile))
26 {
27 ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("sheet1");
28
29 #region 添加表头
30 /*添加表头*/
31 workSheet.InsertRow(1, 1);
32 using (var range = workSheet.Cells[1, 1, 1, 5])
33 {
34 range.Merge = true;//合并单元格
35 range.Style.Font.SetFromFont(new Font("Britannic Bold", 18, FontStyle.Regular));//为字体设置样式
36 range.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;//水平居中
37 range.Style.Fill.PatternType = ExcelFillStyle.Solid;
38 range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));//设置背景色
39 range.Style.Font.Color.SetColor(Color.Black);//字体颜色
40 range.Value = "学生信息统计表";
41 }
42 #endregion
43
44 #region 设置列宽
45 //设置列宽
46 workSheet.Column(1).Width = 20;
47 workSheet.Column(2).Width = 20;
48 workSheet.Column(3).Width = 20;
49 workSheet.Column(4).Width = 20;
50 workSheet.Column(5).Width = 20;
51 workSheet.Column(6).Width = 20;
52 workSheet.Column(7).Width = 20;
53 workSheet.Column(8).Width = 25;
54 #endregion
55
56 #region 设置标题
57 /*设置标题*/
58 workSheet.Cells[2, 1].Value = "学生姓名";
59 workSheet.Cells[2, 2].Value = "性别";
60 workSheet.Cells[2, 3].Value = "年龄";
61 workSheet.Cells[2, 4].Value = "自我介绍";
62 workSheet.Cells[2, 5].Value = "填写时间";
63 #endregion
64
65 using (var range = workSheet.Cells[2, 1, 2, 5])
66 {
67 range.Style.Font.Bold = true;//设置字体为粗体
68 range.Style.Fill.PatternType = ExcelFillStyle.Solid;
69 range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);//设置背景颜色
70 range.Style.Font.Color.SetColor(Color.White);//设置字体颜色
71 //range.AutoFilter = true;
72 }
73
74 #region 设置单元格内容
75 /*设置单元格内容*/
76 int row = 3;
77 foreach (student item in studentList)
78 {
79
80 workSheet.Cells[row, 1].Value = item.name;
81 workSheet.Cells[row, 2].Value = item.sex;
82 workSheet.Cells[row, 3].Value = item.age;
83 workSheet.Cells[row, 4].Value = item.content;
84 workSheet.Cells[row, 5].Value = item.dataTime.ToString();
85 workSheet.Cells[row, 6].Style.Numberformat.Format = "yyyy-MM-dd";
86 workSheet.Cells[row, 1, row, 10].Style.WrapText = true;//自动换行
87 row++;
88 }
89 #endregion
90
91 workSheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
92 //workSheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
93 package.Save();
94 }
95 }
96 #endregion
导出学生信息方法
<下拉框>
ExcelRange range = worksheet.Cells[idxRow, idxCell];
string strData = "北京,上海,广州";
string[] Data = strData.Split(',');
var val = worksheet.DataValidations.AddListValidation(range.Address);
for (int idxV = 0; idxV < Data.Length; idxV++)
{
val.Formula.Values.Add(Data[idxV]);
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
