MVC Excel导入导出

最新推荐文章于 2022-06-18 16:21:59 发布

萧氏萧然

最新推荐文章于 2022-06-18 16:21:59 发布

阅读量295

点赞数

分类专栏： JavaScript运算符文章标签： c# visual studio code

本文链接：https://blog.csdn.net/weixin_46767263/article/details/107988679

版权

JavaScript运算符专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MVC Excel导入导出


开发工具与关键技术：Microsoft Visual Studio C#

作者：萧氏萧然

撰写时间：2020年8月 5日

1、导出

数据的导出都是利用了浏览器的下载功能

在新页面打开下载的路径

利用微软的msoffice excel 比较麻烦上传端和下载端的excel版本需要一致小版本也要一致

否则就会报错而且导出的一端也必须要安装版本一致的office

利用.NET的POI库简称NPOI专门用来操作文档，主要是用xls(1997~2003)版本的后缀名兼容性比较好 xlsx也是支持的不过写法更为复杂
首先把NPOI引用到项目中
Excel组成工作簿、工作表、行、列、单元格
查询出要导出的数据
NPOI导出Excel
创建一个工作簿

NPOI

.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();

创建命
```名空间 名称 = 声明 主体 （HSSFWorkbook 工作簿）

2.   在创建的工作簿中创建一个工作表

(1)
NPOI.SS.UserModel.ISheet sheet1 =workbook.CreateSheet("旅客信息");

创建命名空间 名称=工作簿.工作表（直接命名工作表名称）

          (2)NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet(); 创建工作表workbook.SetSheetName(0, "旅客信息");0工作表索引为第一个工作表     再修改工作表名称

            (3)设置表的标题：

                  ①创建行

                       在工作表里创建行其索引为0 所以是创建第一行

                       NPOI.SS.UserModel.IRow rowTitle =sheet1.CreateRow(0);

                      rowTitle.HeightInPoints = 35; 设置行高

行高 HeightInPoints的单位是点，而Height的单位是1/20个点，所以Height的值永远是HeightInPoints的20倍

                      ②创建单元格

                          在行里面创建单元格其索引为0所以是创建第一个单元格

                          NPOI.SS.UserModel.ICell cell0 = rowTitle.CreateCell(0);

                      ③给单元格设置值

                          string strTitle = "旅客数据";  单元格文本内容

                          拼接个日期当日期不为空时文本内容加上日期

                          if (!string.IsNullOrEmpty(startEndDate))

                          {

                               strTitle += "    " + startEndDate;

                          }

                          cell0.SetCellValue(strTitle); 回填数据

                      ④合并单元格

                          行. AddMergedRegion(合并单元格)（new NPOI.SS.Util.CellRangeAddress (int firstRow, int lastRow, int firstCol, int lastCol)）(int 起始行下标, int 结束行下标, int 起始列下标, int 结束列下标)

                         sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(x, x, x, x));

                      ⑤设置单元格样式

                      NPOI.SS.UserModel.ICellStyle A = workbook.CreateCellStyle();

                      A.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平居中

                      A.VerticalAlignment =NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中

                      NPOI.SS.UserModel.IFont B =workbook.CreateFont();//声明字体

                      B.Color =NPOI.HSSF.Util.HSSFColor.Blue.Index;//设置字体颜色

                      B.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;//加粗

                      B.FontHeightInPoints= 18;//字体大小

 

                      cellStyle_Title.SetFont(B);//设置单元格字体      

                      cell0.CellStyle = A;//设置单元格样式

                  (4)设置表头

                      ①创建一行索引为1

                      NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(1);给sheet添加第一行的头部标题row1.Height = 22 *20;设置行高②在行内创建单元格 并设置值row1.CreateCell(索引值).SetCellValue("表头名");

                      row1.CreateCell(0).SetCellValue("表头名");

                      row1.CreateCell(1).SetCellValue("表头名");

                      row1.CreateCell(2).SetCellValue("表头名");row1.CreateCell(3).SetCellValue("表头名");③创建表头的样式NPOI.SS.UserModel.ICellStyle C =workbook.CreateCellStyle();//声明样式

                      C.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平居中

                      C.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直居中

                      ④设置背景颜色（填充颜色）及边框

                      设置填充方式颜色

                      cellStyle_header.FillPattern= NPOI.SS.UserModel.FillPattern.SolidForeground;

                      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.FontHeightInPoints = 10;//字体大小

                      cellStyle_header.SetFont(font_header);//加入单元格

                   //给单元格设置样式 循环

                      for (int i = 0; i < row1.Cells.Count; i++)

                      {

                            row1.GetCell(i).CellStyle = cellStyle_header;

                       }

                      (5)遍历查询到的数据，这是表格数据

                      ①创建数据内部部分单元格样式

                      ②遍历数据 创建数据部分行列

                      for (int i = 0; i < list.Count; i++) list为查询到的数据

                      {

                           创建行NPOI.SS.UserModel.IRow row =sheet1.CreateRow(2 + i);//标题和表头已经占了两行从第三行开始

                           row.Height = 22 * 20;//设置行高

                          创建列，并设置值

                          row.CreateCell(0).SetCellValue(list[i].属性值);

                           row.CreateCell(0).SetCellValue(i + 1);//序号

                           row.CreateCell(1).SetCellValue(list[i].属性值 对应表头);

                           row.CreateCell(2).SetCellValue(list[i]. 属性值 对应表头);

                           row.CreateCell(3).SetCellValue(list[i]. 属性值 对应表头);

                           row.CreateCell(4).SetCellValue(list[i]. 属性值 对应表头);

                           row.CreateCell(5).SetCellValue(list[i]. 属性值 对应表头);

                           给每个单元格添加样式 

                      }

 

                      (6)设置列宽为自动适应 取这一列最宽的哪一个作为它的宽度

                      for (int i = 0; i < sheet1.GetRow(1).Cells.Count; i++)

                          {

                               sheet1.AutoSizeColumn(i);默认单元格

                               sheet1.SetColumnWidth(i, sheet1.GetColumnWidth(i) * 17 /10);设置宽度

                          }

                      (7)把创建好的Excel输出到浏览器string fileName = "旅客信息" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";拼接的文件名

                      //把Excel转化为流输出

                      MemoryStream BookStream = new MemoryStream();定义流  IO 内存流

                      workbook.Write(BookStream);将工作薄写入流

                      输出之前调用Seek（偏移量，游标位置）从文件开传输始不然会丢失部分数据文件导致异常

                      BookStream.Seek(0, SeekOrigin.Begin); 

                      return File(BookStream, "application/vnd.ms-excel", fileName);文件类型/文件名称

 

2、 导入数据

（1）导入数据首先要获取到要导入的文件路径

string templateFilePath =Server.MapPath("~/Document/用户导入模板.xls");

（2）判断文件模板是否存在即是否空值，有的话获取文件名称以流的形式返回文件没有则提示文件不存在

获取模板文件的路径

            string templateFilePath = Server.MapPath("~/Document/用户导入模板.xls");

            判断模板是否存在

            if (System.IO.File.Exists(templateFilePath))

            {

                获取文件名称

                string fileName = System.IO.Path.GetFileName(templateFilePath);

                以流的形式返回文件

                return File(new System.IO.FileStream(templateFilePath, System.IO.FileMode.Open),

                    "application/octet-stream", fileName);文件格式名称

            }

            else

            {

                return Content("导入模板不存在，请联系网站管理人员");

            }

（3）判断后缀，声明一个和文件大小一致的二进制数组，将上传的文件转成二进制数组再转为工作簿

//判断文件后缀

         string fileExtension = System.IO.Path.GetExtension(excelFile.FileName);文件名

     if (".xls".Equals(fileExtension, StringComparison.CurrentCultureIgnoreCase))比较后缀名是否为.xls
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200813191017100.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Njc2NzI2Mw==,size_16,color_FFFFFF,t_70#pic_center)



 

    （4）获取出第一个工作表

          NPOI.SS.UserModel.ISheet sheet =workbook.GetSheetAt(0);

          判断工作表中是否存在行PhysicalNumberOfRows 是物理行的行数，不包含空行

          获取Excel中的标题行，设置dataTable的列名 第二行为标题行，索引为1

          NPOI.SS.UserModel.IRow rowHeader =sheet.GetRow(1);

          FirstCellNum：获取某行第一个单元格下标

          LastCellNum：获取某行的列数 

          FirstRowNum：获取第一个实际行的下标

          LastRowNum：获取最后一个实际行的下标

          获取表格的列数

          int cellCount = rowHeader.LastCellNum;

          获取表格的行数

          int rowCount = sheet.LastRowNum + 1;

          创建DateTable中的列，

          for (int i = rowHeader.FirstCellNum; i < cellCount; i++)

          {

           通过遍历行中的每一个单元格，获取标题行各个单元格的数据

           DataColumn dtCol = new DataColumn(rowHeader.GetCell(i).StringCellValue.Trim());

           把列添加到DataTable中

           dtExcel.Columns.Add(dtCol);

           }

    （5）读取Excel中的数据

         (sheet.FirstRowNum+2) 第一行是说明行 第二行是标题行，第三行开始才是数据

         for (int i = (sheet.FirstRowNum+2); i < rowCount; i++)

          {

           获取行

           NPOI.SS.UserModel.IRow row =sheet.GetRow(i);

           DataTable中创建一行

           DataRow dtRow = dtExcel.NewRow();

           遍历行中列获取数据

            if (row != null)

                  {

                  for (int j =row.FirstCellNum; j < cellCount; j++)

                      {

                          if (row.GetCell(j) != null)

                              {

                                   dtRow[j] =row.GetCell(j).ToString();

                               }

                        }

                   }

                   将一行的数据添加到Datatable

                   dtExcel.Rows.Add(dtRow);

             }

    （6）将数据放入定义的存放容器中

         将dataTable中的数据转换为List<S_User>

         List<S_User> saveUsers = new List<S_User>();

         利用for循环把数据放入S_User里面

         for (int i = 0; i < dtExcel.Rows.Count; i++) 循环行数

         {

             DataRow dr = dtExcel.Rows[i]; 声明行

             创建一个S_User实例保存一条用户数据

              S_User addUser = new S_User();

         用户组号 根据用户组号查询用户组ID  dataRow["列名"]

         string userGroupNumber = dr["用户组号"].ToString().Trim();

         根据表头名字和数据库表头名对比一致绑定数据addUser.userGroupID =userGroups.Single(o =>o.userGroupNumber == userGroupNumber).userGroupID;

         角色

         string userType = dr["角色"].ToString().Trim();

         addUser.userTypeID =userTypes.Single(o => o.userType == userType).userTypeID;

         

         添加到要保存的列表saveUsers

         saveUsers.Add(addUser);

         添加到用于查重的列表allUsers

         allUsers.Add(addUser);}

(7)保存数据

       保存用户数据

        myModel.S_User.Add(saveUser);myModel.SaveChanges();