导入导出数据的总结
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
数据的导出(excel表格)
数据的导出有两种方式:
第一种:在控制器内部定义工作簿的样式,在表头复杂的时候,代码量较多,
相对麻烦;
第二种:在外部提前定义好一个工作簿的模板,引入即可,可省略大部分定义表头标题的
代码,相对简便;
1. 数据的导出,其实就是利用了浏览器的下载功能;
2. 一般都会在新页面打开下载的url,调用window.open();
3. 如果在本页面打开下载的url,浏览器将很难识别下载;
4. 在C#中,是不支持直接对excel表格的操作的;因此,需要使用第三方主键;
有两种方法:
第一种:msoffice excel, 这个比较麻烦,且不具有兼容性,使用时,必须要安装office,
同时,如果在别的电脑运行,office的版本(包括小版本)必须要一致;否则就会报错;
第二种:NPOI (JAVA POI)(JAVA NET.POI)两个版本,它其实就是一个库;
使用里面的xls的功能,基本版本,兼容性相对好一点;还是另外一个xlsx版本
xlsx版本,这个版本是由微软开发的一个新版本,扩展版本;
5. 在数据导出之前,需要创建一个(excel 工作簿),其实就相当于,在存放物品的时候,先准备一个用于存放物品的盒子,盒子准备好后,才将物品放进来;
6. 创建工作簿的步骤:(第一种导出方式)
1. 创建一个工作簿;
NPOI.HSSF.UserModel.HSSFWorkbook 工作簿名 = new
NPOI.HSSF.UserModel.HSSFWorkbook();
2.创建工作表;(创建工作表的前提是要在工作簿下创建)
(也可修改工作表的名称,有两种方法)
NPOI.SS.UserModel.ISheet 工作表名 =工作簿名.CreateSheet(“第一种方法”);
工作簿名.SetSheetName(索引, "第二种方法");
注:第一种是直接在创建工作表的时候,在括号内设置表名;
第二种是通过索引设置表名;
3.创建行;
NPOI.SS.UserModel.IRow 行名 =工作表名.CreateRow(0); 括号内填写下标/索引;
3.1.设置行高
行名.HeightInPoints = 35;
行高 HeightInPoints的单位是点,而Height的单位是1/20个点,所以Height的值永远是HeightInPoints的20倍
4.创建单元格;
NPOI.SS.UserModel.ICell 单元格名 = 行名.CreateCell(0); 括号内填写下标/索引;
7.在给单元格设置值之前,还需要设置单元格的样式;
--合并单元格(括号内数字为下标,指从哪一行/列开始合并)
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 6));
NPOI.SS.UserModel.ICellStyle 样式名 =
workbook.CreateCellStyle(); --声明样式
7.1.垂直居中
样式名.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
7.2.声明字体
NPOI.SS.UserModel.IFont 字体名 = workbook.CreateFont();
7.3. 设置字体颜色
字体名.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
7.4. 加粗
字体名.Boldweight =
(short)NPOI.SS.UserModel.FontBoldWeight.Bold;
7.5.字体大小
字体名.FontHeightInPoints = 18;
7.6.设置单元格字体
样式名.SetFont(font_title);
7.7. 设置单元格样式
cell0.CellStyle = 样式名;
8.设置表头
NPOI.SS.UserModel.IRow 标题名 = 表名.CreateRow(1); 给sheet添加第一行的头部标题
6.1.然后给表头的每个单元格设置值(第一个括号为索引,第二个为值)
标题名.CreateCell(0).SetCellValue("序号");
标题名.CreateCell(1).SetCellValue("旅客姓名");
把需要设置的样式设置好后,便可开始遍历数据;
9.使用for循环遍历数据,把数据设置给每一行/每一个单元格;
7.在for循环里面设置行;
NPOI.SS.UserModel.IRow 行名2 = 表名.CreateRow(2 + i);
--标题和表头已经占了两行
行名2.CreateCell(0).SetCellValue(i
+ 1); --遍历序号
行名2.CreateCell(1).SetCellValue(list[i].passengerName); --遍历姓名
8.然后再嵌套一个for循环,给每一个单元格设置样式
行名2.GetCell(j).CellStyle
= 样式名;
10. 最后把创建好的Excel输出到浏览器(导出数据的核心)
10.1.设置文件名
string 文件名 = "旅客信息" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
10.2. 把Excel转化为流输出
MemoryStream 流名称 = new MemoryStream();--定义流
10.3. 将工作薄写入流
工作簿名.Write(流名称);
10.4. 输出之前调用Seek(偏移量,游标位置)
流名称.Seek(0, SeekOrigin.Begin); --从开始的位置输出;
10.5. return返回数据(到页面)
return File(流名称, "application/vnd.ms-excel", 文件名称);
11.导出数据的方法,整体上,大部分都是在定义excel工作簿(表格)的样式,其实核心部分代码只有短短的几句;
模板导出数据 Excel(第二种导出方式)
1. 读取模板; (把文件路径转换为文件实际所在的路径)
先读取模板的文件路径; Server.MapPath()把相对路径转换为绝对路径;
string 路径变量名 = Server.MapPath("~/Document/ARDataTemplate.xls");
2. 使用NPOI打开模板Excel
使用文件打开模板文件
FileStream fileSteam = new FileStream(templateFilePath,
FileMode.Open);
打开模板所在第一个工作表 (即读取模板,其实就是一个工作簿)
NPOI.HSSF.UserModel.HSSFWorkbook 工作簿名=
new
NPOI.HSSF.UserModel.HSSFWorkbook(fileSteam);
打开模板所在第一个工作表
NPOI.SS.UserModel.ISheet 表名2 =
excelBookTemplate.GetSheetAt(0);
2. 给每个数据单元格设置样式;
NPOI.SS.UserModel.ICellStyle 样式2 =
excelBookTemplate.CreateCellStyle();
水平垂直居中对齐
样式2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
样式2.VerticalAlignment =
NPOI.SS.UserModel.VerticalAlignment.Center;
设置边框为实线
样式2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
样式2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
样式2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
样式2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
3. 然后往每个单元格里填充数据
假如表头占了两行,那就从第三行开始填充数据,以此类推;
在for循环中回填数据;
NPOI.SS.UserModel.IRow 行名2 = sheet.CreateRow(index);
行名2.CreateCell(0).SetCellValue(i + 1); 在括号内回填数据的属性值;
行名2.CreateCell(1).SetCellValue(list[i].orderNo);
4.以流的方式返回
设置文件名称
string 文件名 = "旅客信息" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ffff") + ".xls";
把Excel转化为流输出
MemoryStream 流名称 = new MemoryStream();--定义内存流
将工作薄写入内存流
工作簿名.Write(流名称);
输出之前调用Seek(偏移量,游标位置)方法:获取文件流的长度
流名称.Seek(0, SeekOrigin.Begin); --从开始的位置输出;
return返回数据(到页面)
return File(流名称, "application/vnd.ms-excel", 文件名称);
5.IO是Input和Output的缩写;
6. Input是读取的意思,而Output就是输出的意思,它们的组合就是流,内存流;文件流,都是以流的方式读取输出数据;
7.在导出数据之前,需要把Excel 工作簿模板给关闭掉,否则无法导出数据;
数据的导入(Excel)
1.
导出数据其实就是把数据库中的数据信息写入到Excel工作簿中,
2.
而导入数据,就是把已经准备好的模板数据,写进数据库里面,在顺序上,两者的操作是相反的;
3.
导入数据可分为三大步:
第一步:下载模板 .xls 类型的;
凡是涉及到跟下载相关的功能,都是使用window.open(),这样浏览器更容易识别下载功能,这个是下载的主要方式;
第二步:用户根据模板填写数据;
第三步:用户上传数据 - - 导入数据
4.
准备好模板后,获取文件模板的路径;(导入模板下载)
导入数据的同样需要把文件路径转换为绝对路径;
string 路径名3 = Server.MapPath("~/Document/用户导入模板.xls");
获取文件名称
string 文件名称3 = System.IO.Path.GetFileName(路径名3);
以流的形式返回文件
return File(new System.IO.FileStream(templateFilePath,
System.IO.FileMode.Open),"文件类型",文件名称3);
文件类型:"application/octet-stream",这是已知的一种文件类型固定写法;
5. 文件上传使用XMLHttpRequest
先通过ID选择器获取文件;
var files =
document.getElementById("fileExcel").files;
获取文件
var excelFile =
files[0]; 括号内为索引
然后创建FormData对象
var formData = new FormData();
把数据添加到FormData对象中
formData.append("excelFile", excelFile);
创建XMLHttpRequest对象
var xhr = new XMLHttpRequest();
打开指定的URL 文件上传, 提交方式也只能使用POST提交
xhr.open("POST", "@Url.Content("~/OthersMaintenance/UserDataMaintain/ImportExcel")");
使用xhr.onload回调方法
数据导入的具体思路(核心)
1. 获取上传的文件;
2. 把文件转换为二进制数组;
3. 二进制数组转换为内存流;
4. 利用NPOI把内存流中的数据读取成Excel;
5. 实际上来说,所有的文件都可以转换成二进制数组,而二进制数组其实就是0和1组成的;
6. 文件(数据)导入的大致步骤:
转换成二进制数组 (声明一个和文件大小一致的二进制数组)
byte[] 二进制数组名 = new byte[excelFile.ContentLength];
将上传的文件转成二进制数组(把excelFile文件读取到二进制数组中)
excelFile.InputStream.Read(fileBytes, 0, fileBytes.Length);
将二进制数组转为内存流
MemoryStream 内存流名 = new MemoryStream(二进制数组名);
将内存流转为工作簿
NPOI.SS.UserModel.Iworkbook 工作簿名=
new
NPOI.HSSF.UserModel.HSSFWorkbook(内存流名);
7. 转换为工作簿后,接下来就是获取表,行,单元格,并且获取数据;
8. 在Excel中关系,是先存在工作簿,然后是表,再是行,最后才是列,单元格;
9. PhysicalNumberOfRows 表示是物理行的行数 ,不包含空行
FirstCellNum:获取某行第一个单元格下标 (下标就是索引)
LastCellNum:获取某行的列数 !!!!! (获取的是最后的列数)
FirstRowNum:获取第一个实际行的下标
LastRowNum:获取最后一个实际行的下标
获取出第一个工作表
NPOI.SS.UserModel.ISheet 表名3 = workbook.GetSheetAt(0);
定义一个DataTable, 然后将数据保存到DataTable中
DataTable dtExcel = new DataTable();
获取Excel中的标题行,设置dataTable的列名 第二行为标题行,索引为1
NPOI.SS.UserModel.IRow rowHeader = sheet.GetRow(1);
获取表格的列数
int cellCount = rowHeader.LastCellNum;
获取表格的行数
int rowCount = sheet.LastRowNum + 1;
通过for循环遍历行中的每一个单元格,获取标题行各个单元格的数据
DataColumn dtCol = new DataColumn(rowHeader.GetCell(i).StringCellValue.Trim());
然后把列添加到DataTable中
dtExcel.Columns.Add(dtCol);
读取Excel中的数据
在for循环里面变量数据,(一般除标题,表头,从第三行开始遍历)
先创建行
NPOI.SS.UserModel.IRow row =
sheet.GetRow(i);
DataTable中创建一行
DataRow dtRow =
dtExcel.NewRow();
把每遍历一行数据,就将数据添加到DataTable
dtExcel.Rows.Add(dtRow);
然后使用遍历datatable中的数据
再创建一个S_User实例保存一条用户数据
S_User addUser = new S_User();
添加到要保存的列表saveUsers
saveUsers.Add(addUser);
最后将数据保存即可;
注:在导入之前。建议把Excel工作簿关闭,否则可能会导致报错;