导出Excel文件
//nget下载NPOI
///
/// 导出商品信息
///
///
[HttpGet]
public IActionResult daochu()
{
//读取要导出的数据
var list = _db.GoodsAllData();
XSSFWorkbook sheets = new XSSFWorkbook();
var sheet1 = sheets.CreateSheet("商品信息");//创建表
var row1 = sheet1.CreateRow(0);////获取表头
row1.CreateCell(0).SetCellValue("编号");
row1.CreateCell(1).SetCellValue("商品名称");
row1.CreateCell(2).SetCellValue("商品价格");
row1.CreateCell(3).SetCellValue("商品库存");
row1.CreateCell(4).SetCellValue("商品图片");
row1.CreateCell(5).SetCellValue("生产日期");
row1.CreateCell(6).SetCellValue("订购数量");
for (int i = 0; i < list.Count; i++)
{
var row = sheet1.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(list[i].G_Id);//创建第0行，第0列单元格
row.CreateCell(1).SetCellValue(list[i].G_Name);
row.CreateCell(2).SetCellValue((double)list[i].G_Price);
row.CreateCell(3).SetCellValue(list[i].G_Num);
row.CreateCell(4).SetCellValue(list[i].G_Pic);
row.CreateCell(5).SetCellValue(list[i].G_Date.ToString("yyyy-MM-dd"));
row.CreateCell(6).SetCellValue(list[i].O_Num);
}
//类型转换
var ms = new MemoryStream();
sheets.Write(ms);
//通过API下载文件
byte[] b = ms.ToArray();
return File(b, System.Net.Mime.MediaTypeNames.Application.Octet, "商品信息.xls"); //关键语句
}
//导入
///
/// 导入
///
[HttpPost]
public IActionResult DaoRu(IFormFile file)
{
///XSSFWorkbook处理的是.xlsx版本; HSSFWorkbook处理的是.xls版本
// HSSFWorkbook sheets = new HSSFWorkbook(file.OpenReadStream());
XSSFWorkbook sheets = new XSSFWorkbook(file.OpenReadStream());
var sheet1 = sheets.GetSheet("商品信息");//读取一张表
var count = sheet1.LastRowNum;
for (int i = 1; i <= count; i++)
{
var row = sheet1.GetRow(i);
var vid = row.GetCell(0).NumericCellValue;
var vname = row.GetCell(1).StringCellValue;
var vprice = row.GetCell(2).NumericCellValue;
var vnum = row.GetCell(3).NumericCellValue;
var vpic = row.GetCell(4).StringCellValue;
var vTime = row.GetCell(5).StringCellValue;
var vonum = row.GetCell(6).NumericCellValue;
//添加到表中
_db.AddData(new Goods
{
G_Name = vname,
G_Price = (decimal)vprice,
G_Num = (int)vnum,
G_Pic = vpic,
G_Date = DateTime.Parse(vTime),
O_Num = (int)vonum,
});
}
return Ok();
}