vue axios上传文件/axios下载文件，NPOI使用模板生成Excel

导入：import Axios from "axios";

按钮：备注：baseDomainUrl: `${process.env.VUE_APP_BASE_API}${abp.appPath}`,

<el-dropdown split-button type="primary" class="filter-item" size="mini">
          <label for="file-upload">
            <svg-icon icon-class="excel" />&nbsp;&nbsp;导入
            <input
              type="file"
              id="file-upload"
              style="display:none;"
              accept=".xlsx, .xls"
              @change="uploadExcel"
            />
          </label>
          <el-dropdown-menu slot="dropdown">
            <el-dropdown-item>
              <el-link v-bind:href="tempUrl" target="_blank">下载模板</el-link>
            </el-dropdown-item>
          </el-dropdown-menu>
        </el-dropdown>

事件：

uploadExcel(e) {
let file = e.target.files[0];
          /* eslint-disable no-undef */
          let param = new FormData(); // 创建form对象
          param.append("file", file); // 通过append向form对象添加数据
          param.append("warehouseId", wid); // 添加form表单中其他数据
          param.append("zoneId", zid);
          // console.log(param.get("file")); // FormData私有类对象，访问不到，可以通过get判断值是否传进去
          let config = {
            headers: { "Content-Type": "multipart/form-data" }
          };
          // 添加请求头
          Axios.post(
            "/be/api/services/app/location/Import",
            param,
            config
          ).then(res => {
            //this.getList();
          });
}

后台：

/// <summary>
        /// 导入库位
        /// </summary>
        /// <returns></returns>
        public async Task Import()
        {
            var whIdStr = System.Web.HttpContext.Current.Request.Form["warehouseId"];
            long whId;
            if (!long.TryParse(whIdStr, out whId))
            {
                throw new Abp.UI.UserFriendlyException("请求参数异常！");
            }
            var zIdStr = System.Web.HttpContext.Current.Request.Form["zoneId"];
            long zId;
            if (!long.TryParse(zIdStr, out zId))
            {
                throw new Abp.UI.UserFriendlyException("请求参数异常！");
            }

            var files = System.Web.HttpContext.Current.Request.Files;
            if (files.Count == 0)
            {
                throw new Abp.UI.UserFriendlyException("请选择文件！");
            }

            string[] jsonHeader = new string[]
            {
                "Code", "Height", "Width", "Depth", "Volume", "MaxLoad",
                "OccupiedVol","LoadedWeight","Sequence"
            };
            var res = ExcelExportJsonData.ImportExcel<ExcelOutput<LocationImportDto>, LocationImportDto>(jsonHeader);
            var locs = res.Result.Data;
            foreach (var item in locs)
            {
                Location loc = ObjectMapper.Map<Location>(item);
                loc.WarehouseId = whId;
                loc.ZoneId = zId;
                loc.IsActive = true;
                loc.IsEmpty = true;
                var z1 = ObjectMapper.Map<LocationDto>(loc);
                await CreateAsync(z1);
            }
        }

后台补充：

public string UploadImg()
        {
            var files = HttpContext.Current.Request.Files;
            var picFile = files[0];
            var fileExtName = Path.GetExtension(picFile.FileName);
            var newName = DateTime.Now.ToString("yyyyMMddHHmmss") + fileExtName;
            var pathDir = "/upload/article/";
            var pathDirUrl = HttpContext.Current.Server.MapPath("~" + pathDir);
            if (!Directory.Exists(pathDirUrl))
            {
                Directory.CreateDirectory(pathDirUrl);
            }
            var pathUrl = pathDir + newName;
            picFile.SaveAs(HttpContext.Current.Server.MapPath("~" + pathUrl));
            return pathUrl;
        }

下载文件：

function handleExport() {
  let param = new FormData(); // 创建form对象
  // param.append("warehouseId", wid); // 添加form表单中其他数据
  // param.append("zoneId", zid);
  let config = {
    responseType: "blob"
  };
  // 添加请求头
  Axios.post(
    "/dev-api/be/api/services/app/outBound/exportVerifyData",
    param,
    config
  ).then(res => {
    const content = res;
    const blob = new Blob([content]); // 构造一个blob对象来处理数据，java中返回的数据是res.data，其中res.data.type是application/octet-stream
    let d = new Date();
    const fileName = d.getFullYear() + "-" + d.getMonth() + "-" + d.getDay() + ".xlsx"; // 导出文件名
    if ("download" in document.createElement("a")) {
      // 支持a标签download的浏览器
      const link = document.createElement("a"); // 创建a标签
      link.download = fileName; // a标签添加属性
      link.style.display = "none";
      link.href = URL.createObjectURL(blob);
      document.body.appendChild(link);
      link.click(); // 执行下载
      URL.revokeObjectURL(link.href); // 释放url
      document.body.removeChild(link); // 释放标签
    } else {
      // 其他浏览器
      window.navigator.msSaveBlob(blob, fileName);
    }
  });
}

后端代码：使用NPOI

public void ExportVerifyData(EntityDto<long> input)
        {
            //查出库单
            long outboundId = input.Id;//
            var outboundRec = _repository.GetAll().Where(x => x.Id == outboundId).Select(x => new
            {
                ClientName = x.Client.Company.Name,
                CustomerName = x.Customer.Company.Name,
                x.SalesDate,
                x.DocNo,
                x.Address,
                x.ContactInfo,
                x.Client.Company.BALicense,
                x.Id
            }).FirstOrDefault();

            //查出库单的拣货列表
            var outboundLineIdList = _outBoundLineRepository.GetAll().Where(x => x.OutBoundId == outboundId).Select(x => x.Id);
            var lines = _pickingListRepository.GetAll().Where(x => outboundLineIdList.Contains(x.OutBoundLineId)).Select(x => new
            {
                ItemName = x.OutBoundLine.ClientItem.Name,
                x.OutBoundLine.ClientItem.Item.Spec,
                x.OutBoundLine.ClientItem.Item.RegNo,
                x.OutBoundLine.ClientItem.Item.BALicense,
                x.OutBoundLine.ClientItem.Item.Manufacturer,
                x.OutBoundLine.ClientItem.Item.ProductionLicenseNo,
                x.OutBoundLine.ClientItem.Item.Uom,
                x.Qty,
                x.OutBoundLine.UnitPrice,
                x.OutBoundLine.LineAmount,
                x.OutBoundLine.LotNo,
                x.OutBoundLine.SN,
                x.InventoryDetail.InboundLine.ExpiryDate,
                x.OutBoundLine.QualityStatus,
                x.OutBoundLine.ClientItem.Item.StorageCondition
            }).ToList();

            string tempPath = HttpContext.Current.Server.MapPath("~/temp/DeliveryReview.xlsx");
            string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            string shortPath = "/download/" + fileName;
            string destPath = HttpContext.Current.Server.MapPath("~" + shortPath);

            IWorkbook workbook = null;
            using (FileStream fs = new FileStream(tempPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                #region 创建工作表
                fs.Position = 0;
                if (destPath.IndexOf(".xlsx") > 0) // 2007版本
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (destPath.IndexOf(".xls") > 0) // 2003版本
                {
                    workbook = new HSSFWorkbook(fs);
                }
                var sheet = workbook.GetSheet("sheet1");
                if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                {
                    sheet = workbook.GetSheetAt(0);
                }
                #endregion

                #region 赋值单元格
                int rowNum = 1;
                sheet.GetRow(rowNum).Cells[1].SetCellValue(outboundRec.ClientName);
                rowNum++;
                sheet.GetRow(rowNum).Cells[1].SetCellValue(outboundRec.CustomerName);
                sheet.GetRow(rowNum).Cells[3].SetCellValue(outboundRec.SalesDate.HasValue ? outboundRec.SalesDate.Value.ToString("yyyy-MM-dd") : "");
                sheet.GetRow(rowNum).Cells[5].SetCellValue(outboundRec.DocNo);
                rowNum++;
                sheet.GetRow(rowNum).Cells[1].SetCellValue(outboundRec.Address);
                sheet.GetRow(rowNum).Cells[3].SetCellValue(outboundRec.ContactInfo);
                rowNum += 2;
                for (int i = 0; i < lines.Count; i++)
                {
                    var lineModel = lines[i];
                    var row = sheet.CreateRow(rowNum);
                    row.CreateCell(0).SetCellValue(lineModel.ItemName);
                    row.CreateCell(1).SetCellValue(lineModel.Spec);
                    row.CreateCell(2).SetCellValue(lineModel.RegNo + "/" + lineModel.BALicense);
                    row.CreateCell(3).SetCellValue(lineModel.Manufacturer);
                    row.CreateCell(4).SetCellValue(lineModel.ProductionLicenseNo + "/" + outboundRec.BALicense);
                    row.CreateCell(5).SetCellValue(lineModel.Uom);
                    row.CreateCell(6).SetCellValue(lineModel.Qty.ToString());
                    row.CreateCell(7).SetCellValue(lineModel.UnitPrice.HasValue ? Math.Round(lineModel.UnitPrice.Value, 2, MidpointRounding.AwayFromZero).ToString() : "0");
                    row.CreateCell(8).SetCellValue(lineModel.LineAmount.HasValue ? Math.Round(lineModel.LineAmount.Value, 2, MidpointRounding.AwayFromZero).ToString() : "0");
                    row.CreateCell(9).SetCellValue(lineModel.LotNo + "/" + lineModel.SN + "/" + (lineModel.ExpiryDate.HasValue ? lineModel.ExpiryDate.Value.ToString("yyyy-MM-dd") : ""));
                    row.CreateCell(10).SetCellValue(lineModel.QualityStatus);
                    row.CreateCell(11).SetCellValue(lineModel.StorageCondition.GetEnumDes());
                    rowNum++;
                }
                #endregion

                //var sheet = workbook.CreateSheet("sheet1");
                //IRow row = sheet.CreateRow(0);
                //row.CreateCell(0).SetCellValue("深圳闪链医疗供应链管理有限公司第三方物流发货出库复核单（发货指令）");
                //CellRangeAddress region0 = new CellRangeAddress(1, 1, 1, 12);
                //sheet.AddMergedRegion(region0);

                using (FileStream fs2 = new FileStream(destPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    workbook.Write(fs2);
                }
                //HttpContext.Current.Response.ContentType = "application/octet-stream";
                //HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                //workbook.Write(HttpContext.Current.Response.OutputStream);

                #region 以字符流的形式下载文件
                byte[] bytes = null;
                using (FileStream fs3 = new FileStream(destPath, FileMode.Open))
                {
                    bytes = new byte[(int)fs3.Length];
                    fs3.Read(bytes, 0, bytes.Length);
                    fs3.Close();
                    HttpContext.Current.Response.ContentType = "application/octet-stream";
                    //通知浏览器下载文件而不是打开
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                    //    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.BinaryWrite(bytes);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();
                }
                File.Delete(destPath);
                #endregion
            }
        }

 

using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace WMS2020.Common
{
    public class FileUploadDto
    {
        public Success Success { get; set; }
        public Error Error { get; set; }
    }

    public class Success
    {
        public int Row { get; set; }
    }
    public class Error
    {
        public string Msg { get; set; }
        public List<Info> Infos { get; set; }
    }
    public class Info
    {
        public int RowIndex { get; set; }
        public int CelIndex { get; set; }
        public string Msg { get; set; }
    }
    public class ExcelOutput<O> : FileUploadDto
    {
        /// <summary>
        /// 产线需求集合
        /// </summary>
        public List<O> Data { get; set; }
    }
    /// <summary>
    /// 将excel数据分析导出json格式数据
    /// </summary>
    public class ExcelExportJsonData
    {
        /// <summary>
        /// 导入Excel将数据分析出来
        /// </summary>
        /// <typeparam name="EO">最终导出数据</typeparam>
        /// <typeparam name="O">内部list数据</typeparam>
        /// <param name="jsonHeader"></param>
        /// <returns></returns>
        public static Task<EO> ImportExcel<EO, O>(string[] jsonHeader)
        where EO : ExcelOutput<O>, new()
        where O : new()
        {
            Stream stream = null;
            HttpFileCollection files = HttpContext.Current.Request.Files;
            if (files.Count > 0)
            {
                HttpPostedFile file1 = files[0];
                if (!string.IsNullOrEmpty(file1.FileName))
                {
                    stream = file1.InputStream;
                }
            }

            if (stream == null)
            {
                EO uploadDto = new EO()
                {
                    Error = new Error() { Infos = new List<Info>(), Msg = "请选择上传文件" }
                };
                return Task.FromResult(uploadDto);
            }
            //反射类型获取header对应的数据类型
            Type[] types = new Type[jsonHeader.Length];
            PropertyInfo[] properties = new O().GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
            for (var i = 0; i < jsonHeader.Length; i++)
            {
                types[i] = properties.First(w => w.Name == jsonHeader[i]).PropertyType;
            }

            string errMsg = string.Empty;
            //读取Excel
            JArray array = new ExcelHelp().ReadExcel(jsonHeader, 0, 1, stream);
            foreach (var arr in array)
            {
                for (var i = 0; i < jsonHeader.Length; i++)
                {
                    //不为字符串类型则判断类型是否正确
                    if (types[i] != typeof(string))
                    {
                        var jt = ((JObject)arr)[jsonHeader[i]];
                        if (jt != null && !string.IsNullOrEmpty(jt.ToString()))
                        {
                            try
                            {
                                Convert.ChangeType(jt, types[i]);
                            }
                            catch (FormatException ex)
                            {
                                errMsg = string.Format("Excel中第{0}列对应的值应为{1}", i + 1, GetTypeStr(types[i]));
                                break;
                            }
                            catch (InvalidCastException ex)
                            {
                                errMsg = string.Format("Excel中第{0}列对应的值应为{1}", i + 1, GetTypeStr(types[i]));
                                break;
                            }
                        }
                        else if (jt != null && string.IsNullOrEmpty(jt.ToString()) && !(types[i].IsGenericType && types[i].GetGenericTypeDefinition().Equals(typeof(Nullable<>))))
                        {
                            errMsg = string.Format("Excel中第{0}列对应的值应为{1}", i + 1, GetTypeStr(types[i]));
                            break;
                        }
                    }
                }
                if (!string.IsNullOrEmpty(errMsg))
                {
                    break;
                }
            }
            if (!string.IsNullOrEmpty(errMsg))
            {
                EO uploadDto = new EO()
                {
                    Error = new Error() { Infos = new List<Info>(), Msg = errMsg }
                };
                return Task.FromResult(uploadDto);
            }
            List<O> sodList = array.ToObject<List<O>>();
            EO sodRes = new EO()
            {
                Success = new Success() { Row = sodList.Count },
                Data = sodList
            };
            return Task.FromResult(sodRes);
        }

        private static string GetTypeStr(Type type)
        {
            string retStr = string.Empty;

            if (type == typeof(bool))
            {
                retStr = "布尔类型";
                return retStr;
            }
            if (type == typeof(decimal)
                || type == typeof(double)
                || type == typeof(float)
                || type == typeof(int)
                || type == typeof(long)
                || type == typeof(short)
                || type == typeof(decimal?)
                || type == typeof(double?)
                || type == typeof(float?)
                || type == typeof(int?)
                || type == typeof(long?)
                || type == typeof(short?))
            {
                retStr = "数值类型";
                return retStr;
            }
            if (type == typeof(DateTime))
            {
                retStr = "日期类型";
                return retStr;
            }
            retStr = "非法类型";
            return retStr;
        }
    }
}