数据导出为Excel（未完）

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

 #region 09-获取导出数据
 /// <summary>
 /// 09-获取导出数据
 /// </summary>
 /// <param name="student">用来接收用户信息（涵盖多条件）</param>
 /// <param name="dateStart">筛选的起始时间</param>
 /// <param name="dateEnd">筛选的结束时间</param>
 /// <returns></returns>
 public List<student> GetStudentList(student student, string dateStart, string dateEnd)
 {
 try
 {
 //1.获取数据源
 var data = db.Set<student>().Where(u => true); //2.过滤查询
 //2.1对学生姓名进行查询
 if (!String.IsNullOrEmpty(student.name))
 {
 data = data.Where(u => u.name.Contains(student.name));
 }
 //2.2对学生性别进行查询
 if (!String.IsNullOrEmpty(student.sex))
 {
 data = data.Where(u => u.sex == student.sex);
 }
 //2.3对学生年龄进行查询
 if (!String.IsNullOrEmpty(student.age))
 {
 data = data.Where(u => u.age.Contains(student.age));
 }
 //2.4对学生记录时间段进行查询
 if ((!String.IsNullOrEmpty(dateStart)) && (!String.IsNullOrEmpty(dateEnd)))
 {
 DateTime dateS = Convert.ToDateTime(dateStart);//开始时间
 DateTime dateE = Convert.ToDateTime(dateEnd);//结束时间
 data = data.Where(u => u.dataTime >= dateS && u.dataTime <= dateE);
 }
 //3.返回结果数据
 var list = data.OrderByDescending(u => u.dataTime).ToList();
 return list;
 }
 catch (Exception ex)
 {
 FileOperateHelp.WriteFile("E:/ErrorLog333.txt", ex.Message);
 }
 return new List<student>();
 }
 #endregion #region 10-导出学生信息方法
 public void ExportStudentExcel(List<student> studentList, out string pathFileName)
 {
 #region 创建存放Excel的文件夹、文件
 //创建存放Excel的文件夹
 string path = Server.MapPath("~/Upload/Excel/BackFee/");
 string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + ".xlsx";
 pathFileName = path + fileName; //如果上传目录不存在就创建
 if (!Directory.Exists(path))
 {
 Directory.CreateDirectory(path);
 } FileInfo newFile = new FileInfo(pathFileName);
 if (newFile.Exists)
 {
 newFile.Delete();
 newFile = new FileInfo(pathFileName);
 }
 #endregion //创建工作簿和工作表
 using (ExcelPackage package = new ExcelPackage(newFile))
 {
 ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("sheet1"); #region 添加表头
 /*添加表头*/
 workSheet.InsertRow(, );
 using (var range = workSheet.Cells[, , , ])
 {
 range.Merge = true;//合并单元格
 range.Style.Font.SetFromFont(new Font("Britannic Bold", , FontStyle.Regular));//为字体设置样式
 range.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;//水平居中
 range.Style.Fill.PatternType = ExcelFillStyle.Solid;
 range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(, , ));//设置背景色
 range.Style.Font.Color.SetColor(Color.Black);//字体颜色
 range.Value = "学生信息统计表";
 }
 #endregion #region 设置列宽
 //设置列宽
 workSheet.Column().Width = ;
 workSheet.Column().Width = ;
 workSheet.Column().Width = ;
 workSheet.Column().Width = ;
 workSheet.Column().Width = ;
 workSheet.Column().Width = ;
 workSheet.Column().Width = ;
 workSheet.Column().Width = ;
 #endregion #region 设置标题
 /*设置标题*/
 workSheet.Cells[, ].Value = "学生姓名";
 workSheet.Cells[, ].Value = "性别";
 workSheet.Cells[, ].Value = "年龄";
 workSheet.Cells[, ].Value = "自我介绍";
 workSheet.Cells[, ].Value = "填写时间";
 #endregion using (var range = workSheet.Cells[, , , ])
 {
 range.Style.Font.Bold = true;//设置字体为粗体
 range.Style.Fill.PatternType = ExcelFillStyle.Solid;
 range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);//设置背景颜色
 range.Style.Font.Color.SetColor(Color.White);//设置字体颜色
 //range.AutoFilter = true;
 } #region 设置单元格内容
 /*设置单元格内容*/
 int row = ;
 foreach (student item in studentList)
 { workSheet.Cells[row, ].Value = item.name;
 workSheet.Cells[row, ].Value = item.sex;
 workSheet.Cells[row, ].Value = item.age;
 workSheet.Cells[row, ].Value = item.content;
 workSheet.Cells[row, ].Value = item.dataTime.ToString();
 workSheet.Cells[row, ].Style.Numberformat.Format = "yyyy-MM-dd";
 workSheet.Cells[row, , row, ].Style.WrapText = true;//自动换行
 row++;
 }
 #endregion workSheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
 //workSheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
 package.Save();
 }
 }
 #endregion

  #region 08-导出学生信息
         /// <summary>
         ///08-导出学生信息
         /// 【下载到本地】
         /// </summary>
         /// <param name="student">用来接收用户信息（涵盖多条件）</param>
         /// <param name="dateStart">筛选的起始时间</param>
         /// <param name="dateEnd">筛选的结束时间</param>
         /// <returns></returns>
         public FileResult DownLoadExcel(student student, string dateStart, string dateEnd)
         {
             List<student> studentList = GetStudentList(student, dateStart, dateEnd);
             string pathFileName = string.Empty;
             ExportStudentExcel(studentList, out pathFileName);
             string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + ".xlsx";             return File(pathFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
         }
         #endregion

导出学生信息

 #region 09-获取导出数据
         /// <summary>
         /// 09-获取导出数据
         /// </summary>
         /// <param name="student">用来接收用户信息（涵盖多条件）</param>
         /// <param name="dateStart">筛选的起始时间</param>
         /// <param name="dateEnd">筛选的结束时间</param>
         /// <returns></returns>
         public List<student> GetStudentList(student student, string dateStart, string dateEnd)
         {
             try
             {
                 //1.获取数据源
                 var data = db.Set<student>().Where(u => true);                 //2.过滤查询
                 //2.1对学生姓名进行查询
                 if (!String.IsNullOrEmpty(student.name))
                 {
                     data = data.Where(u => u.name.Contains(student.name));
                 }
                 //2.2对学生性别进行查询
                 if (!String.IsNullOrEmpty(student.sex))
                 {
                     data = data.Where(u => u.sex == student.sex);
                 }
                 //2.3对学生年龄进行查询
                 if (!String.IsNullOrEmpty(student.age))
                 {
                     data = data.Where(u => u.age.Contains(student.age));
                 }
                 //2.4对学生记录时间段进行查询
                 if ((!String.IsNullOrEmpty(dateStart)) && (!String.IsNullOrEmpty(dateEnd)))
                 {
                     DateTime dateS = Convert.ToDateTime(dateStart);//开始时间
                     DateTime dateE = Convert.ToDateTime(dateEnd);//结束时间
                     data = data.Where(u => u.dataTime >= dateS && u.dataTime <= dateE);
                 }
                 //3.返回结果数据
                 var list = data.OrderByDescending(u => u.dataTime).ToList();
                 return list;
             }
             catch (Exception ex)
             {
                 FileOperateHelp.WriteFile("E:/ErrorLog333.txt", ex.Message);
             }
             return new List<student>();
         }
         #endregion

获取导出数据

 #region 10-导出学生信息方法
         public void ExportStudentExcel(List<student> studentList, out string pathFileName)
         {
             #region 创建存放Excel的文件夹、文件
             //创建存放Excel的文件夹
             string path = Server.MapPath("~/Upload/Excel/BackFee/");
             string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + ".xlsx";
             pathFileName = path + fileName;             //如果上传目录不存在就创建
             if (!Directory.Exists(path))
             {
                 Directory.CreateDirectory(path);
             }             FileInfo newFile = new FileInfo(pathFileName);
             if (newFile.Exists)
             {
                 newFile.Delete();
                 newFile = new FileInfo(pathFileName);
             }
             #endregion             //创建工作簿和工作表
             using (ExcelPackage package = new ExcelPackage(newFile))
             {
                 ExcelWorksheet workSheet = package.Workbook.Worksheets.Add("sheet1");                 #region 添加表头
                 /*添加表头*/
                 workSheet.InsertRow(, );
                 using (var range = workSheet.Cells[, , , ])
                 {
                     range.Merge = true;//合并单元格
                     range.Style.Font.SetFromFont(new Font("Britannic Bold", , FontStyle.Regular));//为字体设置样式
                     range.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;//水平居中
                     range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                     range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(, , ));//设置背景色
                     range.Style.Font.Color.SetColor(Color.Black);//字体颜色
                     range.Value = "学生信息统计表";
                 }
                 #endregion                 #region 设置列宽
                 //设置列宽
                 workSheet.Column().Width = ;
                 workSheet.Column().Width = ;
                 workSheet.Column().Width = ;
                 workSheet.Column().Width = ;
                 workSheet.Column().Width = ;
                 workSheet.Column().Width = ;
                 workSheet.Column().Width = ;
                 workSheet.Column().Width = ;
                 #endregion                 #region 设置标题
                 /*设置标题*/
                 workSheet.Cells[, ].Value = "学生姓名";
                 workSheet.Cells[, ].Value = "性别";
                 workSheet.Cells[, ].Value = "年龄";
                 workSheet.Cells[, ].Value = "自我介绍";
                 workSheet.Cells[, ].Value = "填写时间";
                 #endregion                 using (var range = workSheet.Cells[, , , ])
                 {
                     range.Style.Font.Bold = true;//设置字体为粗体
                     range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                     range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);//设置背景颜色
                     range.Style.Font.Color.SetColor(Color.White);//设置字体颜色
                     //range.AutoFilter = true;
                 }                 #region 设置单元格内容
                 /*设置单元格内容*/
                 int row = ;
                 foreach (student item in studentList)
                 {                     workSheet.Cells[row, ].Value = item.name;
                     workSheet.Cells[row, ].Value = item.sex;
                     workSheet.Cells[row, ].Value = item.age;
                     workSheet.Cells[row, ].Value = item.content;
                     workSheet.Cells[row, ].Value = item.dataTime.ToString();
                     workSheet.Cells[row, ].Style.Numberformat.Format = "yyyy-MM-dd";
                     workSheet.Cells[row, , row, ].Style.WrapText = true;//自动换行
                     row++;
                 }
                 #endregion                 workSheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                 //workSheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
                 package.Save();
             }
         }
         #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]);
}

个人收藏笔记记录

开通VIP