NPOI导入导出Excel文件

导出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();
}