2020-08-09

Excel数据导入

 

第二步：判断工作簿中是否有工作表

第三步：判断工作表中是否有数据

第四步：获取数据，保证数据的准确性和判断数据的重复性、移除空行

第五步：保存数据

（1）、if (workbook.NumberOfSheets > 0)

NumberOfSheets：判断工作表是否存在

（2）、先获取工作簿中第一个工作表，然后判断工作表中是否有数据

1、获取第一个工作表

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

        2、判断工作表是否有数据  

    if (sheet.PhysicalNumberOfRows > 0)

PhysicalNumberOfRows：获取的是物理行数，其中并不包括那些空行或隔行的情况

（3）、获取数据需要先创建一个临时表：DataTable dt = new DataTable();

然后获取表头行，为第二行，索引为1，因为第一行是说明

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

 //获取表格总列数

 int cellCount = rowHeader.LastCellNum;//从行里面获取总列数（获取到的是数值）

                            //获取表格总行数

 int rowCount = sheet.LastRowNum + 1;//从工作表里获取行总数，（获取到的是索引，所以加1）

（4）、然后将Excel表头作为临时表的列

（5）、将Excel数据作为临时表的数据

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

LastCellNum：获取某行的列数 ！！！！！（只有这个获取的是数值，其它三个获取的都是索引）

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

LastRowNum：获取最后一个实际行的下标（获取当前行最后一个单元格的值）

（6）、然后数据验证：

           验证数据的重复性和数据是否为空、输入格式是否正确

最后新增保存。

 

 

Excel数据导出

  MVC中Excel表格数据导出有三种：

第一种：在已有的模板上导出数据（前提是构建好模板）

第二种：正常的导出数据

第三种：含有合并表头的数据导出

不管是哪一种，数据导出都是建立在NPOI插件基础上的，都需要先将NPOI放到项目中bin文件夹里面，然后添加引用

 含有合并表头的数据导出：

整体思路：先创建好工作簿，然后在该工作簿中创建工作表。然后在工作表中创建第一行，接着在第一行创建单元格，然后给这个单元格设置样式、内容，需要注意的是：因为是合并表头，所以需要合并单元格。

然后接着在工作表中创建表头行，因为合并表头在第一行，所以表头行是从第二行开始的，给表头行设置行高、创建单元格、并设置值、创建表头的样式，然后通过循环表头中所有的单元格给每个单元格添加样式

遍历查询出的数据，创建行列，先有行，再有列，注意的是数据从第三行开始（合并表头占第一行、表头占第二行）最后设置列宽为自适应

定义并创建文件流：MemoryStream

return  File（）：返回文件 

参数一：文件流  参数二：文件格式  参数三：文件名称

 

然后将Excel通过文件流返回到浏览器下载，页面通过window.Open（）方法调用该方法即可

 

代码具体写法：

public ActionResult ExportLKDATA(string startEndDate)

{

//1、声明一个workbook对象（工作簿）

            HSSFWorkbook workbook = new HSSFWorkbook();

     //2、创建工作表 sheet 并设置名称

            ISheet sheet = workbook.CreateSheet();//在工作簿里创建工作表

            workbook.SetSheetName(0, "旅客信息");//设置工作簿名称（也就是Excel文件名称）

     //3、设置标题、合并单元格、设置样式

            //3.1 创建第一个行 索引0

            IRow row0 = sheet.CreateRow(0);//创建第一行    其实IRo意思和var差别 声明一个变量来接收右边给的行，只不过是指明了类型，而var是右边给什么类型，左边就是什么类型，没有直接指明

            //3.2创建一个单元格，索引0

            ICell cell0 = row0.CreateCell(0);//创建单元格

            string strTitle = "旅客信息";

            if (!string.IsNullOrEmpty(startEndDate))

            {

                strTitle = strTitle + "   " + startEndDate;//如果筛选时段不为空，那么名称 = 旅客信息 + 筛选时间段

            }

            cell0.SetCellValue(strTitle);//这里是给表格设置标题（属于表格内）SetCellValue：给单元格设置值   给第一行的第一个单元格赋值

 

            //3.2.1、创建一个单元格样式

            ICellStyle cellStyle_Title = workbook.CreateCellStyle();//声明并创建样式

            cellStyle_Title.Alignment = HorizontalAlignment.Center;//水平居中

            cellStyle_Title.VerticalAlignment = VerticalAlignment.Center;//垂直居中

 

            IFont font_title = workbook.CreateFont();//声明字体

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

            font_title.Boldweight = (Int16)FontBoldWeight.Bold;//加粗

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

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

            //3.2、合并单元格（第几行开始，到第几行结束，第几列开始，到第几列结束）

            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));

            //3.2.2 设置单元格样式（给合并表头的标题设置样式）

            cell0.CellStyle = cellStyle_Title;

 

    //4、设置表头（第二行，因为第一行是合并后的标题）

            //4.1 创建一行 索引为1

            NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(1);//（第二行，因为第一行是合并后的标题）

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

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

            //4.2 创建单元格 并设置值

            row1.CreateCell(0).SetCellValue("序号");//这里代表工作表第二行第1列

            row1.CreateCell(1).SetCellValue("旅客姓名");

            row1.CreateCell(2).SetCellValue("旅客类型");

            row1.CreateCell(3).SetCellValue("证件类型");

            row1.CreateCell(4).SetCellValue("证件号码");

            row1.CreateCell(5).SetCellValue("联系人姓名");

            row1.CreateCell(6).SetCellValue("联系人电话");

            //4.3创建表头的样式

            ICellStyle cellStyle_header = workbook.CreateCellStyle();//声明并创建样式

            cellStyle_header.Alignment = HorizontalAlignment.Center;//水平居中

            cellStyle_header.VerticalAlignment = VerticalAlignment.Center;//垂直居中

            //设置背景颜色   FillPattern：填充颜色      SolidForeground：代表一个颜色，单色（因为填充颜色里面也有渐变色）

            cellStyle_header.FillPattern = FillPattern.SolidForeground;

            cellStyle_header.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Aqua.Index;//Aqua：浅绿色

            //设置边框线为实线

            cellStyle_header.BorderLeft = BorderStyle.Thin;

            cellStyle_header.BorderRight = BorderStyle.Thin;

            cellStyle_header.BorderTop = BorderStyle.Thin;

            cellStyle_header.BorderBottom = BorderStyle.Thin;

            //声明字体

            IFont font_header = workbook.CreateFont();

            font_header.Boldweight = (Int16)FontBoldWeight.Bold;//字体加粗

            font_header.FontHeightInPoints = 10;//字体大小

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

            //4.4 给每个单元格添加样式

            for (int i = 0; i < row1.Cells.Count; i++)//循环第二行（表头行）里面所有的单元格

            {

                row1.GetCell(i).CellStyle = cellStyle_header;

            }

 #region 导出Excel数据

   //5、遍历查询到的数据，设置Excel表格数据

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

            ICellStyle cellstyle_value = workbook.CreateCellStyle();//声明样式

            cellstyle_value.Alignment = HorizontalAlignment.Center;//水平居中

            cellstyle_value.VerticalAlignment = VerticalAlignment.Center;//垂直居中

            //设置边框线为实线

            cellstyle_value.BorderLeft = BorderStyle.Thin;

            cellstyle_value.BorderTop = BorderStyle.Thin;

            cellstyle_value.BorderRight = BorderStyle.Thin;

            cellstyle_value.BorderBottom = BorderStyle.Thin;

            //5.2=遍历数据，创建数据部分行列

            for (int i = 0; i < list.Count; i++)//循环数据条数，有多少条数据，就创建多少个行

            {

                //5.2.1-创建行

                NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 2);//因为合并表头在第一行，表头在第二行，所以数据从第三行开始

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

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

                row.CreateCell(1).SetCellValue(list[i].passengerName);//旅客姓名

                row.CreateCell(2).SetCellValue(list[i].passengerType);//旅客类型

                row.CreateCell(3).SetCellValue(list[i].certificatesType);//证件类型

                row.CreateCell(4).SetCellValue(list[i].certificatesCode);//证件号码

                row.CreateCell(5).SetCellValue(list[i].contactName);//联系人姓名

                row.CreateCell(6).SetCellValue(list[i].contactPhone);//联系人电话

                //5.2.2-给每个单元格添加样式

                for (int j = 0; j < row.Cells.Count; j++)//循环行里面的单元格，每创建一行，就循环一次

                {

//GetCell：给单元格设置    CellStyle：样式

                    row.GetCell(j).CellStyle = cellstyle_value;                }

                  }

//6、设置列宽为自适应（防止内容过长溢出单元格）

//循环第一个工作表中的第二行中所有的单元格的个数

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

 {

    sheet.AutoSizeColumn(i);//AutoSizeColumn：内容自适应属性从第二行第一个单元格开始设置

sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) * 17 / 10);

//设置宽度  参数一：设置的单元格  参数二：要设置的宽度

 }

 

            #endregion

 

 //7、将Excel通过文件流返回到浏览器下载

//定义下载文件名称

string fileName = "旅客信息" + DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss-ffff") + ".xls";

 //将Excel转化为文件流，输出

MemoryStream BookStream = new MemoryStream();//定义文件流

 workbook.Write(BookStream);//将工作簿写入文件流

//检查文件流长度（防止文件损坏）

 BookStream.Seek(0, SeekOrigin.Begin);

//File：返回文件   参数一：文件流  参数二：返回文件流的格式  参数三：文件名称

return File(BookStream, "application/vnd.ms-excel", fileName);

 

 

 

含有合并表头导出和直接导出（无模板）共同点：

 

参数一：要返回的流

参数二：保存的格式

参数三：文件名称

 

有模板导出：

首先需要检查模板文件是否存在

 

然后使用NPOI打开模板文件、构建单元格样式、设置标题、填充数据，最后通过File将数据返回

 

1. 导入数据，必须先要准备一个模板文件，供用户下载，然后在控制器       中写一个下载模板的方法
2. 在视图中通过window.open()方法调用控制器中的这个方法就好了
3. 同样的，接收Excel也是要用HttpPostFileBase这个类来接收的

4. 导入数据大概思路：

   第一步：判断文件类型是否是.xls，判断正确后再执行下面操作

5. 获取上传的文件
6. 把文件转换为二进制数组
7. 将二进制数组转化为内存流
8. 利用NPOI把内存流中的数据读取成Excel
9. 使用NPOI读取数据
10. 创建工作簿、从工作簿里面提取第一个工作表，，包        括设置单元格样式、字体都是一样的
11. 都需要将Excel转换为文件流，然后将工作簿写入      文件流
12. 返回格式都是 return File（）