下面就给大家详细讲解一下“C#使用NPOI对Excel数据进行导入导出”的完整攻略。
一、NPOI是什么?
NPOI是一个跨平台的第三方.xls和.xlsx格式操作组件库,适用于.NET平台,可以方便地读取、创建和编辑Excel文件,提供了针对Excel文件的内存读写支持,支持xls、xlsx、doc、docx、ppt、pptx等多种Office格式。
二、NPOI应用的常用类
在使用NPOI进行Excel读写时,常用的类有以下几个:
- HSSFWorkbook:对应xls格式
- XSSFWorkbook:对应xlsx格式
- HSSFSheet:对应Excel工作表(xls)
- XSSFSheet:对应Excel工作表(xlsx)
- HSSFRow:对应Excel工作表中的行(xls)
- XSSFRow:对应Excel工作表中的行(xlsx)
三、C#使用NPOI进行Excel导出
3.1 创建Excel文件
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Web;
public ActionResult Export()
{
//创建工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook();
//创建工作表对象
ISheet sheet = workbook.CreateSheet("sheet1");
//创建行对象
IRow row1 = sheet.CreateRow(0);
//创建列对象
ICell cell1 = row1.CreateCell(0);
cell1.SetCellValue("姓名");
ICell cell2 = row1.CreateCell(1);
cell2.SetCellValue("性别");
ICell cell3 = row1.CreateCell(2);
cell3.SetCellValue("年龄");
//创建行对象
IRow row2 = sheet.CreateRow(1);
//创建列对象
ICell cell4 = row2.CreateCell(0);
cell4.SetCellValue("小明");
ICell cell5 = row2.CreateCell(1);
cell5.SetCellValue("男");
ICell cell6 = row2.CreateCell(2);
cell6.SetCellValue(18);
//流方式,下载文件
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=FileName.xls"));
Response.BinaryWrite(ms.ToArray());
return new EmptyResult();
}
3.2 Excel导出模板
public ActionResult ExportTemplate()
{
//导出模板文件的路径
string excelTemplatePath = System.Web.HttpContext.Current.Request.MapPath("~/UploadFiles/Template.xls");
//创建工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook(new FileStream(excelTemplatePath, FileMode.Open, FileAccess.Read));
//创建工作表对象
ISheet sheet = workbook.GetSheetAt(0);
//获取表头行
IRow rowHeader = sheet.GetRow(0);
//获取需要导出的数据列表
List<User> userList = new List<User>()
{
new User { Name = "小明", Gender = "男", Age = 20 },
new User { Name = "小红", Gender = "女", Age = 18 }
};
//导出数据到Excel
int rowIndex = 1;
foreach (User user in userList)
{
IRow row = sheet.CreateRow(rowIndex);
ICell cell1 = row.CreateCell(0);
cell1.SetCellValue(user.Name);
ICell cell2 = row.CreateCell(1);
cell2.SetCellValue(user.Gender);
ICell cell3 = row.CreateCell(2);
cell3.SetCellValue(user.Age);
rowIndex++;
}
//流方式,下载文件
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=FileName.xls"));
Response.BinaryWrite(ms.ToArray());
return new EmptyResult();
}
四、C#使用NPOI进行Excel导入
4.1 读取Excel文件
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
using System.Web;
public ActionResult Import()
{
//获取要上传的Excel文件
HttpPostedFileBase file = Request.Files["fileUpload"];
//Excel文件的后缀名(xls/xlsx)
string fileExtension = Path.GetExtension(file.FileName);
//Excel文件的完整路径
string filePath = Path.Combine(HttpContext.Server.MapPath("~/UploadFiles/"), file.FileName);
file.SaveAs(filePath);
//根据Excel文件的后缀名,创建对应的工作簿对象
IWorkbook workbook = null;
if (fileExtension == ".xls")
{
workbook = new HSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read));
}
else if (fileExtension == ".xlsx")
{
workbook = new XSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read));
}
//获取第一个工作表
ISheet sheet = workbook.GetSheetAt(0);
List<User> userList = new List<User>();
//遍历工作表中的行
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
User user = new User();
user.Name = row.GetCell(0).ToString();
user.Gender = row.GetCell(1).ToString();
user.Age = Convert.ToInt32(row.GetCell(2).ToString());
userList.Add(user);
}
//删除已上传的Excel文件
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
return View(userList);
}
4.2 解析Excel内容
public ActionResult Import()
{
//获取要上传的Excel文件
HttpPostedFileBase file = Request.Files["fileUpload"];
//Excel文件的后缀名(xls/xlsx)
string fileExtension = Path.GetExtension(file.FileName);
//Excel文件的完整路径
string filePath = Path.Combine(HttpContext.Server.MapPath("~/UploadFiles/"), file.FileName);
file.SaveAs(filePath);
//根据Excel文件的后缀名,创建对应的工作簿对象
IWorkbook workbook = null;
if (fileExtension == ".xls")
{
workbook = new HSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read));
}
else if (fileExtension == ".xlsx")
{
workbook = new XSSFWorkbook(new FileStream(filePath, FileMode.Open, FileAccess.Read));
}
//获取第一个工作表
ISheet sheet = workbook.GetSheetAt(0);
List<User> userList = new List<User>();
//获取表头行
IRow rowHeader = sheet.GetRow(0);
//获取需要解析的列的索引
int nameIndex = -1;
int genderIndex = -1;
int ageIndex = -1;
for (int i = rowHeader.FirstCellNum; i < rowHeader.LastCellNum; i++)
{
string cellValue = rowHeader.GetCell(i).ToString();
if (cellValue == "姓名")
{
nameIndex = i;
}
else if (cellValue == "性别")
{
genderIndex = i;
}
else if (cellValue == "年龄")
{
ageIndex = i;
}
}
//遍历工作表中的行
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
User user = new User();
if (nameIndex != -1)
{
user.Name = row.GetCell(nameIndex).ToString();
}
if (genderIndex != -1)
{
user.Gender = row.GetCell(genderIndex).ToString();
}
if (ageIndex != -1)
{
user.Age = Convert.ToInt32(row.GetCell(ageIndex).ToString());
}
userList.Add(user);
}
//删除已上传的Excel文件
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
return View(userList);
}
以上就是C#使用NPOI对Excel数据进行导入导出的完整攻略,希望能够对大家有所帮助。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:C#使用NPOI对Excel数据进行导入导出 - Python技术站