webapi excel导入

 效果图

 

 

引入命名空间：

using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF;
using NPOI.XSSF.UserModel;
using Xiaowu_Ship.Model;

/// <summary>
/// 导入数据
/// </summary>
/// <param name="excelFile"></param>
/// <returns></returns>
[HttpPost]
public async Task<bool> ImportProduct(IFormFileCollection excelFile)
{
try
{
List<stu> stuList = new List<stu>();
foreach (var file in Request.Form.Files)
{

 

var postFile = file;// Request.Form.Files[0];
string extName = Path.GetExtension(postFile.FileName);
if (!new string[] { ".xls", ".xlsx" }.Contains(extName))
{
return false;
}
MemoryStream ms = new MemoryStream();
postFile.CopyTo(ms);
ms.Position = 0;
IWorkbook wb = null;
if (extName.ToLower().Equals(".xls")) // 97-2003版本
{
wb = new HSSFWorkbook(ms);
}
else
{
wb = new XSSFWorkbook(ms); // 2007以上版本
}
ISheet sheet = wb.GetSheetAt(0);
//总行数（0开始）
int totalRow = sheet.LastRowNum;
// 总列数（1开始）
int totalColumn = sheet.GetRow(0).LastCellNum;
for (int i = 1; i <= totalRow; i++)
{
IRow row = sheet.GetRow(i);
// 判定第5列的值是不是日期，日期的值类型可以按日期来读，也可以用数据的方式来读
var isDate = DateUtil.IsCellDateFormatted(row.GetCell(4));
string StuName = row.GetCell(0).StringCellValue;
int Sex = row.GetCell(1).StringCellValue == "男" ? 0 : 1;
string Phone = ((long)row.GetCell(2).NumericCellValue).ToString();
int CId = (int)row.GetCell(3).NumericCellValue;
DateTime InDate = row.GetCell(4).DateCellValue;
decimal JF = (decimal)row.GetCell(5).NumericCellValue;

// 第6列有可能是空的
string Pic = "";
if (row.GetCell(6) != null)
{
CellType type = row.GetCell(6).CellType;
if (type != CellType.Blank)
{
Pic = row.GetCell(6).StringCellValue;
}
}
int State = 0;
if (row.GetCell(7) != null)
{
State = (int)row.GetCell(7).NumericCellValue;
}
var stu = new stu
{
StuName = StuName,
Sex = Sex,
Phone = Phone,
CId = CId,
InDate = InDate,
JF = JF,
Pic = Pic,
State = State,
IsOk = true,
};
stuList.Add(stu);
}
wb.Close();
}
return true;
}
catch (Exception)
{
throw;
}
}

 

实体类:

 

public class stu
{
public string StuName { get; set; }
public int Sex { get; set; }
public string Phone { get; set; }
public int CId { get; set; }
public DateTime InDate { get; set; }
public decimal JF { get; set; }
public string Pic { get; set; }
public int State { get; set; }
public bool IsOk { get; set; }
}