数据的导出

数据的导出

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

 

上一篇:spring--基于ioc的注解方式


下一篇:Spring中的四种声明式事务的配置