cshtml：
<form class="form-horizontal" id="form1" role="form" ng-submit="import ()" enctype="multipart/form-data">
<button class="btn btn-primary" type="button" ng-click="downloadTemp()">下载模板</button>
<button type="submit" class="btn btn-primary">导入</button>
</form>
js：
//下载模板
$scope.downloadTemp = function () {
downloadfile({
url: "/Temp/DownloadTemp"
});
} //导入
$scope.import = function () {
var url = "/Temp/Import";
var formData = new FormData();//使用FormData进行文件上传
formData.append("file", file.files[0]);//拿到当前文件
$http.post(url, formData, {
transformRequest: angular.identity,
headers: { 'Content-Type': undefined }
}).success(function (data, status) {
var success = "";
if (data.success = false)
{
success = "导入失败!";
}
$scope.AlertMesage(success + data.Message, 1);
}).error(function (data, status) {
$scope.AlertMesage("导入异常:[" + data.Message + "]!", 3);
});
};
Controller：
/// <summary>
/// 模板下载
/// </summary>
/// <returns></returns>
[HttpPost]
public FileResult DownloadTemp()
{
try
{
var fileName = $"数据_{DateTime.Now:yyyyMMdd}.xls";
//创建Excel文件的对象
HSSFWorkbook book = new HSSFWorkbook();
//添加一个sheet
ISheet sheet1 = book.CreateSheet("Sheet1");
#region 给sheet1添加第一行的头部标题
IRow row1 = sheet1.CreateRow();
//给sheet1添加第一行的头部标题
row1.CreateCell().SetCellValue("编号");
row1.CreateCell().SetCellValue("名称");
row1.CreateCell().SetCellValue("运能");
#endregion
//获取正常状态（自营，在用，有效）的站点运能数据
var list = GetAllListInfo();
if (list != null && list .Any())
{
for (int i = ; i < list.Count(); i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + );
rowtemp.CreateCell().SetCellValue(capacityList[i].Code);
rowtemp.CreateCell().SetCellValue(capacityList[i].Name);
rowtemp.CreateCell().SetCellValue(capacityList[i].Capacity); }
}
// 写入到客户端
MemoryStream ms = new MemoryStream();
book.Write(ms);
ms.Seek(, SeekOrigin.Begin);
return File(ms, "application/vnd.ms-excel", fileName);
}
catch (Exception ex)
{
throw new Exception("导出数据失败：" + ex.ToString());
}
}
/// <summary>
/// 导入
/// </summary>
/// <returns></returns>
[HttpPost]
public JsonResult Import()
{
try
{
//接收客户端传递过来的数据
HttpPostedFileBase file = Request.Files["file"];
if (file == null)
{
return Json(new
{
Success = false,
Message = "请选择上传的Excel文件",
});
}
//对文件的格式判断，此处省略
string fileExt = Path.GetExtension(file.FileName.Replace("\"", ""));
var supportArr = new string[] { ".xls", ".xlsx" };
if (supportArr.Contains(fileExt) == false)
{
//throw new ArgumentException(string.Format("不支持的文件类型:{0}", fileExt));
return Json(new
{
Success = false,
Message = string.Format("不支持的文件类型:{0}", fileExt),
});
} Stream inputStream = file.InputStream;
//HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputStream);
IWorkbook workbook = null;
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(inputStream); // .xlsx
}
else
{
workbook = new HSSFWorkbook(inputStream); // .xls
}
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt();
// IRow headerRow = sheet.GetRow(0);//第一行为标题行
// int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
List<StationEntity> list = new List<StationEntity>();
if (rowCount > )
{
try
{
for (int i = (sheet.FirstRowNum + ); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
StationEntity model = new StationEntity(); if (row != null)
{ model.Code = row.GetCell() != null ? GetCellValue(row.GetCell()) : string.Empty ;
model.Name = row.GetCell() != null ? GetCellValue(row.GetCell()) : string.Empty;
model.Capacity = row.GetCell() != null ? ConvertHelper.ToInt32(GetCellValue(row.GetCell())) : ;
list.Add(model);
}
}
}
catch (Exception)
{
return Json(new
{
Success = false,
Message = "请填写正确格式的数据",
});
}
} //导入运能数据
if (list != null && list.Any())
{
string msg = string.Empty;
var success = StationBLL.RefreshData(list,out msg);
return Json(new
{
Success = success,
Message = msg,
});
}
else
return Json(new
{
Success = false,
Message = "没有要导入的数据",
});
}
catch (Exception ex)
{ throw new Exception("导入数据失败：" + ex.ToString());
}
}
/// 根据Excel列类型获取列的值
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.Blank:
return string.Empty;
case CellType.Boolean:
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Numeric:
case CellType.Unknown:
default:
return cell.ToString();
case CellType.String:
return cell.StringCellValue;
case CellType.Formula:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}