MVC中的导入导出（2）

上次只有导入还没有导出的代码，是因为导出的代码比导入的代码更长，因为导出的时候可以对XCL进行修改所以作者的导出代码中有对XCL的样式进行修改的代码，所以会比较长。

        public ActionResult ExportExcel(int? bookId,string searchText){List list = (from book in myModel.S_bookjoin library in myModel.S_library on book.libraryId equals library.libraryIdjoin booktype in myModel.S_BookType on book.bookTypeId equals booktype.bookTypeIdselect new BookAnLibrary{bookId = book.bookId,bookName = book.bookName,ISBN = book.ISBN,author = book.author,press = book.press,price = book.price,volume = book.volume,allowVolume = book.allowVolume,libraryId = library.libraryId,libraryName = library.libraryName,bookTypeId = booktype.bookTypeId,bookType = booktype.bookType}).ToList();if (bookId != null && bookId > 0){list = list.Where(o => o.bookId == bookId).ToList();}if (!string.IsNullOrEmpty(searchText)){searchText = searchText.Trim();list = list.Where(o => o.bookName.Contains(searchText) ||o.ISBN.Contains(searchText) || o.author.Contains(searchText)||o.press.Contains(searchText)||o.libraryName.Contains(searchText)||o.bookType.Contains(searchText)).ToList();}//==构建Excel//1、创建工作簿NPOI.SS.UserModel.IWorkbook workbook = new HSSFWorkbook();//xls//2、创建工作表NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("工作表名称");workbook.SetSheetName(0, "图书信息");//3、创建标题(行)NPOI.SS.UserModel.IRow rowTitle = sheet.CreateRow(0);//0 行索引  第一行rowTitle.HeightInPoints = 35;//3-2、创建单元格NPOI.SS.UserModel.ICell cell0 = rowTitle.CreateCell(0);//3-3、单元格设置值cell0.SetCellValue("导出的图书信息");//Excel单元格合并sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8));//表头的一个样式 单元格的样式NPOI.SS.UserModel.ICellStyle cellStyle_Title = workbook.CreateCellStyle();//文本对其（水平）方式 Center居中cellStyle_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//文本对其（垂直）方式 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);//给单元格的边框绘制线  左上右下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("ISBN");rowHeader.CreateCell(3).SetCellValue("出版社");rowHeader.CreateCell(4).SetCellValue("作者");rowHeader.CreateCell(5).SetCellValue("图书馆名称");rowHeader.CreateCell(6).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 = 10;//加入单元格样式中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].bookName);row.CreateCell(2).SetCellValue(list[i].ISBN);row.CreateCell(3).SetCellValue(list[i].press);row.CreateCell(4).SetCellValue(list[i].author);row.CreateCell(5).SetCellValue(list[i].libraryName);row.CreateCell(6).SetCellValue(list[i].bookType);//给每个单元格添加样式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);}//========把创建好的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);}}

第一步：将NPOI引入到MVC的“解决方案管理器中”，并包含在项目中。

第二步：在“引用”中点击鼠标右键>选择添加引用>在程序集中找到Sysem.Transctions。勾选后点击确定。 

 只有添加了Sysem.Transctions之后才能将数据引入到数据库中

最后附上导出后的图片：

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！