数据导出为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(1, 1);
using (var range = workSheet.Cells[1, 1, 1, 5])
{
range.Merge = true;//合并单元格
range.Style.Font.SetFromFont(new Font("Britannic Bold", 18, FontStyle.Regular));//为字体设置样式
range.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;//水平居中
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));//设置背景色
range.Style.Font.Color.SetColor(Color.Black);//字体颜色
range.Value = "学生信息统计表";
}
#endregion

#region 设置列宽
//设置列宽
workSheet.Column(1).Width = 20;
workSheet.Column(2).Width = 20;
workSheet.Column(3).Width = 20;
workSheet.Column(4).Width = 20;
workSheet.Column(5).Width = 20;
workSheet.Column(6).Width = 20;
workSheet.Column(7).Width = 20;
workSheet.Column(8).Width = 25;
#endregion

#region 设置标题
/*设置标题*/
workSheet.Cells[2, 1].Value = "学生姓名";
workSheet.Cells[2, 2].Value = "性别";
workSheet.Cells[2, 3].Value = "年龄";
workSheet.Cells[2, 4].Value = "自我介绍";
workSheet.Cells[2, 5].Value = "填写时间";
#endregion

using (var range = workSheet.Cells[2, 1, 2, 5])
{
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 = 3;
foreach (student item in studentList)
{

workSheet.Cells[row, 1].Value = item.name;
workSheet.Cells[row, 2].Value = item.sex;
workSheet.Cells[row, 3].Value = item.age;
workSheet.Cells[row, 4].Value = item.content;
workSheet.Cells[row, 5].Value = item.dataTime.ToString();
workSheet.Cells[row, 6].Style.Numberformat.Format = "yyyy-MM-dd";
workSheet.Cells[row, 1, row, 10].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(1, 1);
                using (var range = workSheet.Cells[1, 1, 1, 5])
                {
                    range.Merge = true;//合并单元格
                    range.Style.Font.SetFromFont(new Font("Britannic Bold", 18, FontStyle.Regular));//为字体设置样式
                    range.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;//水平居中
                    range.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));//设置背景色
                    range.Style.Font.Color.SetColor(Color.Black);//字体颜色
                    range.Value = "学生信息统计表";
                }
                #endregion

                #region 设置列宽
                //设置列宽
                workSheet.Column(1).Width = 20;
                workSheet.Column(2).Width = 20;
                workSheet.Column(3).Width = 20;
                workSheet.Column(4).Width = 20;
                workSheet.Column(5).Width = 20;
                workSheet.Column(6).Width = 20;
                workSheet.Column(7).Width = 20;
                workSheet.Column(8).Width = 25;
                #endregion

                #region 设置标题
                /*设置标题*/
                workSheet.Cells[2, 1].Value = "学生姓名";
                workSheet.Cells[2, 2].Value = "性别";
                workSheet.Cells[2, 3].Value = "年龄";
                workSheet.Cells[2, 4].Value = "自我介绍";
                workSheet.Cells[2, 5].Value = "填写时间";
                #endregion

                using (var range = workSheet.Cells[2, 1, 2, 5])
                {
                    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 = 3;
                foreach (student item in studentList)
                {
                  
                    workSheet.Cells[row, 1].Value = item.name;
                    workSheet.Cells[row, 2].Value = item.sex;
                    workSheet.Cells[row, 3].Value = item.age;
                    workSheet.Cells[row, 4].Value = item.content;
                    workSheet.Cells[row, 5].Value = item.dataTime.ToString();
                    workSheet.Cells[row, 6].Style.Numberformat.Format = "yyyy-MM-dd";
                    workSheet.Cells[row, 1, row, 10].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]);
}