数据的导出
Excel数据的导出并没有导入复杂,可根据筛选条件导出你想导出的数据
实例：学生信息数据的导出
Js代码:
function excelExport() {
var formDataText = $("#formSearch").serialize();
window.open("ExportExcel?" + formDataText);
}
筛选数据代码和页面筛选数据代码时一样的，可以用前面的代码
c#代码：
public ActionResult ExportExcel(int? academeID, int? specialtyID,
Nullable<int> gradeID, int? classID, string strstu)
{
//数据的导出
//第一步：查询出需要导出的数据
//第二步：将需要导出的数据写入到Excel中
#region 查询语句
List<StudentOv> list = (from tbStudent in myModel.SYS_Student
join tbAcademe in myModel.SYS_Academe on tbStudent.academeID equals tbAcademe.academeID
join tbSpecialty in myModel.SYS_Specialty on tbStudent.specialtyID equals tbSpecialty.specialtyID
join tbGrade in myModel.SYS_Grade on tbStudent.gradeID equals tbGrade.gradeID
join tbClass in myModel.SYS_Class on tbStudent.classID equals tbClass.classID
select new StudentOv
{
studentID = tbStudent.studentID,
academeID = tbStudent.academeID,
gradeID = tbStudent.gradeID,
specialtyID = tbStudent.specialtyID,
classID = tbStudent.classID,
studentName = tbStudent.studentName,
studentSex = tbStudent.studentSex,
studentIDNum = tbStudent.studentIDNum,
studentNumber = tbStudent.studentNumber,
studentPictureName = tbStudent.studentPictureName,
academeName = tbAcademe.academeName,
className = tbClass.className,
specialtyName = tbSpecialty.specialtyName,
gradeName = tbGrade.gradeName
}).ToList();
#endregion
#region 条件拼接
if (academeID != null && academeID > 0)
{
list = list.Where(o => o.academeID == academeID).ToList();
}
if (specialtyID != null && specialtyID > 0)
{
list = list.Where(o => o.specialtyID == specialtyID).ToList();
}
if (gradeID != null && gradeID > 0)
{
list = list.Where(o => o.gradeID == gradeID).ToList();
}
if (classID != null && classID > 0)
{
list = list.Where(o => o.classID == classID).ToList();
}
if (!string.IsNullOrEmpty(strstu))
{
strstu = strstu.Trim();
list = list.Where(o => o.studentName.Contains(strstu) ||
o.studentIDNum.Contains(strstu) || o.studentNumber.Contains(strstu)).ToList();
}
#endregion
//==构建Excel
//1、创建工作簿
NPOI.SS.UserModel.IWorkbook workbook = new HSSFWorkbook();
//2、创建工作表
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("工作表名称");
workbook.SetSheetName(0, "学生信息");
//3、创建标题行
NPOI.SS.UserModel.IRow rowTitle = sheet.CreateRow(0);
rowTitle.HeightInPoints = 35;//设置行高度
//3-2创建单元格
NPOI.SS.UserModel.ICell cell0 = rowTitle.CreateCell(0);
//3-3单元格设置值
cell0.SetCellValue("导出的学生信息");
//合并单元格
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8));
//表头的一个样式 单元格样式
NPOI.SS.UserModel.ICellStyle cellstyle_Title = workbook.CreateCellStyle();
//文本水平居中
cellstyle_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//文本垂直居中
cellstyle_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//创建字体样式
NPOI.SS.UserModel.IFont font_title = workbook.CreateFont();
//添加字体颜色
font_title.Color = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
// font_title.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;//加粗-旧版本
//新版本的字体加粗
font_title.IsBold = true;
cellstyle_Title.SetFont(font_title);
//字体大小
font_title.FontHeightInPoints = 16;
//给 单元格的边框绘制线 左上右下
cellstyle_Title.BorderLeft = NPOI.SS.UserModel.BorderStyle.Double;
cellstyle_Title.BorderTop = NPOI.SS.UserModel.BorderStyle.Double;
cellstyle_Title.BorderRight = NPOI.SS.UserModel.BorderStyle.Double;
cellstyle_Title.BorderBottom = NPOI.SS.UserModel.BorderStyle.Double;
//将单元格样式给第一行的第一个单元格
cell0.CellStyle = cellstyle_Title;
//4.创建表头行并设置字段
NPOI.SS.UserModel.IRow rowHeader = sheet.CreateRow(1);
rowHeader.CreateCell(0).SetCellValue("序号");
rowHeader.CreateCell(1).SetCellValue("学号");
rowHeader.CreateCell(2).SetCellValue("姓名");
rowHeader.CreateCell(3).SetCellValue("身份证号");
rowHeader.CreateCell(4).SetCellValue("性别");
rowHeader.CreateCell(5).SetCellValue("学院");
rowHeader.CreateCell(6).SetCellValue("专业");
rowHeader.CreateCell(7).SetCellValue("年级");
rowHeader.CreateCell(8).SetCellValue("班级");
//创建表头样式
//声明样式
NPOI.SS.UserModel.ICellStyle cellStyle_header = workbook.CreateCellStyle();
//水平居中
cellStyle_header.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//垂直居中
cellStyle_header.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
//设置背景填充
cellStyle_header.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
//设置背景填充颜色 Aqua浅绿色
cellStyle_header.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Aqua.Index;
//设置边框线
cellStyle_header.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle_header.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle_header.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle_header.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//设置字体
//声明字体
NPOI.SS.UserModel.IFont font_header = workbook.CreateFont();
//font_header.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;//加粗
//新版本的字体加粗
font_header.IsBold = true;
//字体大小
font_header.FontHeightInPoints = 12;
//加入单元格样式中
cellStyle_header.SetFont(font_header);
//给rowheader单元格设置样式循环
for(int i = 0; i < rowHeader.Cells.Count; i++)
{
rowHeader.GetCell(i).CellStyle = cellStyle_header;
}
//设置单元格样式
//创建数据单元格的样式
NPOI.SS.UserModel.ICellStyle cellStyle_value = workbook.CreateCellStyle();
cellStyle_value.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平居中
cellStyle_value.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中
//四周边框线
cellStyle_value.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle_value.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle_value.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle_value.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//遍历数据
for (int i = 0; i < list.Count; i++)
{
//创建行
NPOI.SS.UserModel.IRow row = sheet.CreateRow(2 + i);//标题和表头已经占了两行
row.HeightInPoints = 22;//设置行高
//创建列并赋值
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(list[i].studentNumber);
row.CreateCell(2).SetCellValue(list[i].studentName);
row.CreateCell(3).SetCellValue(list[i].studentIDNum);
row.CreateCell(4).SetCellValue(list[i].studentSex);
row.CreateCell(5).SetCellValue(list[i].academeName);
row.CreateCell(6).SetCellValue(list[i].specialtyName);
row.CreateCell(7).SetCellValue(list[i].gradeName);
row.CreateCell(8).SetCellValue(list[i].className);
//给每个单元添加样式
for (int j = 0; j < row.Cells.Count; j++)
{
row.GetCell(j).CellStyle = cellStyle_value;
}
}
//6.设置列宽自动适应
for(int i = 0; i < sheet.GetRow(1).Cells.Count; i++)
{
sheet.AutoSizeColumn(i);
sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) * 15/10);//放大1.5倍
}
//把创建好的Excel输出到浏览器
string fileName = "学生信息" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-SS-ffff") + ".xls";
//把Excel转化为流输出
MemoryStream BookStream = new MemoryStream();//定义流
workbook.Write(BookStream);//将工作簿写入流
//输出之前调用Seek(偏移量，游标位置)
BookStream.Seek(0, SeekOrigin.Begin);
return File(BookStream, "application/vnd.ms-excel", fileName);
}
导出的表格 实例：
113