c#+vue之通過excel批量新增

1.添加相應的庫，可以去vue的官網查找

2.設置文件導入按鈕

 <a style="float: right;cursor: pointer;"
                           @@click="ExcelDataUpload = true">
                            <img src="excel.gif" /> Excel導入
                        </a>
                        <el-dialog title="導入" :visible.sync="ExcelDataUpload">
                            <el-upload class="upload-demo"
                                       action="@Url.Action("ExcelDataUpload")"
                                       accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"
                                       :limit="1"
                                       :on-exceed="handleExceed"
                                       :on-success="handleSuccess">
                                <el-button type="primary" size="medium" slot="trigger" plain round>導入<i class="el-icon-upload el-icon--right"></i></el-button>
                                <label>　　　模板：</label>
                                <a href="導入模板.xlsx">導入模板.xlsx</a>
                            </el-upload>
                        </el-dialog>

圖標就是上圖，點開就會有預設的模板和選擇文件

3.前端方法

var form = new Vue({
            data() {
                return {
                    //關閉彈窗
                    authShowBox: false,
                    // Excel導入數據操作框
                    ExcelDataUpload: false,
                }
            },
            created() {

            },
            // 实例被挂载后调用
            mounted() {
            },
            beforeUpdate() {
            },
            // 方法
            methods: {
                //超過文件個數
                handleExceed() {
                    this.$message.warning("只能選擇一個文件");
                },
                //上傳完成
                handleSuccess(response, file, fileList) {
                    if (response.IsSucceed) {
                        this.$message({
                            showClose: true,
                            type: "success",
                            message: response.Message
                        });
                        setTimeout(function () { window.location.reload(); }, 1000);
                        //this.getTableData();
                    } else {
                        if (response.Result != null) {
                            this.$message({
                                showClose: true,
                                type: "error",
                                message: response.Message
                            });
                            this.fileList = [];
                            var url = 'fileName=' + response.Result;
                            window.open(url, '_parent',
                                'height=400,width=430, resizable=1,scrollbars=0,status=1,menubar=no,toolbar=no,location=no,menu=no,left=" + button.Style["left"] + " , top=" + button.Style["top"] + "',
                            );
                        } else {
                            this.$message({
                                showClose: true,
                                type: "error",
                                message: response.Message
                            });
                        }
                    }
                    this.$refs.upload.clearFiles();
                },
        }).$mount("#main");

4.後端方法

 /// <summary>
        /// 用戶數據導入
        /// </summary>
        /// <returns></returns>
        public ActionResult ExcelDataUpload()
        {
            DataResult result = new DataResult
            {
                IsSucceed = true,
                Result = "",
                Message = "",
                StatusCode = 0,
                Total = 0,
            };
            var userId = Session["UserID"]?.ToString();
            var msg = "";
            bool flag = true;
            //bool isSucceed = true;
            var file = Request.Files[Request.Files.Count - 1];
            var newPath = "";
            string fileName = Path.GetFileName(file.FileName.ToString());//获得到文件名
            string fileNameEx = Path.GetExtension(fileName);//获得文件扩展名
            Hashtable extTable = new Hashtable();
            extTable.Add("file", ".xls,.xlsx");
            if (!((string)extTable["file"]).Contains(fileNameEx))
            {
                //文件类型不正确
                DataResult res = new DataResult
                {
                    IsSucceed = false,
                    Result = "",
                    Message = "文件类型不正确",
                    StatusCode = 0,
                    Total = 0,
                };
                return Json(res);
            }
            string path = System.Web.HttpContext.Current.Server.MapPath("~/UploadFiles/");
            string uploadFileDirectory = path + DateTime.Now.ToString("yyyy-MM");
            if (!Directory.Exists(uploadFileDirectory)) Directory.CreateDirectory(uploadFileDirectory);
            var newName = DateTime.Now.ToString("ddHHmmssffff") + fileName;
            newPath = Path.Combine(uploadFileDirectory, newName);
            file.SaveAs(newPath);
            var str = ExcelToJson(newPath.Replace("\\", "/")).ToString();
            str = str.Replace("^", ",");
            // 取到文件中的數據
            List<aaa> whh = JsonConvert.DeserializeObject<List<sss>>("[" + str + "]");
            List<aaa> aaaList = new List<aaa>();
            List<bbb> bbbError = new List<bbb>();
            List<string> title = new List<string>();
            List<string> titlename = new List<string>();
            ErrorResult errorResult = new ErrorResult();
            Stream stream = file.InputStream;
            IWorkbook hSSWorkbook = WorkbookFactory.Create(stream);
            ISheet sheet = hSSWorkbook.GetSheetAt(0);
            IRow header = sheet.GetRow(sheet.FirstRowNum);

            try
            {
               
                foreach (var item in whh)
                {
                    //寫基本邏輯，然後對有錯誤的標記一個flag=false,並且將全部正確和錯誤加上錯誤的err信息的放進seatError裡面
                    //對於全部正確的才可以提交到數據庫
                    //判斷如果沒有錯就_db.SaveChanges();，如果有錯flag=flase,跳到SeatInfoImportError(seatError, fileName);並下載錯誤後的excel
                        if (err != "")
                        {
                            flag = false;
                        }
                    
                }
                if (flag)
                {
                    _db.SaveChanges();
                    result.Message = "添加成功";
                }
                else
                {
                    result.IsSucceed = false;
                    result.Result = ImportError(bbbError , fileName);
                    result.Message = "添加失敗，具體內容請看excel";
                }
            }
            catch (Exception ex)
            {
                result.Message = "系統內部異常" + ex;
                result.IsSucceed = false;
            }
            return Json(result);
        }
        /// <summary>
        /// 導入異常後操作
        /// </summary>
        /// <param name="SeatError"></param>
        /// <returns></returns>
        private string ImportError(List<bbbError> seat, string filename)
        {
            IWorkbook workbook = CreatPersonInfoExcel(seat);
            string file = "~/Download/";
            filename += "_" + DateTime.Now.ToString("MMddyyyy") + "_error.xlsx";
            string filePath = Server.MapPath(file + filename);
            using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(fileStream);
            }
            return filename;
        }
        /// <summary>
        /// 創建座位文件數據流
        /// </summary>
        /// <param name="SeatError"></param>
        public IWorkbook CreatInfoExcel(List<bbbError > aaa)
        {
            string path = "~/Download/導入模板.xlsx";
            string filePath = Server.MapPath(path);
            FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);
            byte[] bytes = new byte[fileStream.Length];
            fileStream.Read(bytes, 0, bytes.Length);
            fileStream.Close();
            Stream stream = new MemoryStream(bytes);
            IWorkbook workbook = WorkbookFactory.Create(stream);
            ISheet sheet = workbook.GetSheetAt(0);
            ICellStyle style2 = workbook.CreateCellStyle();
            style2 = GetColor(workbook, "Yellow");
            //內容
            for (int x = 0; x < aaa.Count; x++)
            {
                IRow row = sheet.CreateRow(x + 1);
                row.HeightInPoints = 25;
                for (int y = 0; y < aaa的一行有多少列; y++)
                {
                    //判斷是否存在列
                    ICell cell = row.GetCell(y);
                    if (cell == null)
                    {
                        cell = row.CreateCell(y);
                    }
                    switch (y)
                    {
                        case 0:
                            cell.SetCellValue(aaa[x].代碼aaa中名稱);
                            break;
                        case 1:
                            cell.SetCellValue(aaa[x].代碼aaa中名稱);
                            break;
                        case 2:
                            cell.SetCellValue(aaa[x].代碼aaa中名稱);
                            break;
///錯誤信息
                        case 3:
                            cell.SetCellValue(aaa[x].msg);
                            if (seat[x].msg != "")
                            {
                                cell.CellStyle = style2;
                            }
                            break;
                        default:
                            break;
                    }
                }
            }
            return workbook;
        }
        public ICellStyle GetColor(IWorkbook workbook, string ColorCode = "")
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            if (ColorCode != "")
            {
                style.FillPattern = FillPattern.SolidForeground;
            }
            switch (ColorCode)
            {
                case "PaleBlue":
                    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
                    break;
                case "Yellow":
                    style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
                    break;
                default:
                    break;
            }
            return style;
        }
        //下載文件
        public void DownExcel(string fileName)
        {
            string path = "~/Download/" + fileName;
            string filePath = Server.MapPath(path);
            string type = fileName.Substring(fileName.LastIndexOf('.') + 1);
            if (type == "xlsx")
            {
                using (var sw = new FileStream(filePath, FileMode.Open))
                {
                    var bytes = new byte[sw.Length];
                    sw.Read(bytes, 0, bytes.Length);
                    sw.Close();
                    Response.Clear();
                    Response.ClearContent();
                    Response.ClearHeaders();
                    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
                    Response.AddHeader("Content-Length", bytes.Length.ToString());
                    Response.AddHeader("Content-Transfer-Encoding", "binary");
                    Response.ContentType = "application/octet-stream;charset=UTF-8";
                    Response.BinaryWrite(bytes);
                    Response.Flush();
                    Response.End();
                }
                FileInfo finfo = new FileInfo(filePath);

                finfo.Delete();
            }
        }
        /// <summary>
        /// 使用ExcelReader组件来读取。
        /// </summary>
        public static DataSet ExcelReader(string filePath)
        {
            DataSet result = new DataSet();
            using (var stream = System.IO.File.Open(filePath, FileMode.Open, FileAccess.Read))
            {
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    result = reader.AsDataSet();
                    reader.Dispose();
                }
                stream.Dispose();
            }
            return result;
        }
        /// <summary>
        /// 读取excel转json
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public object ExcelToJson(string filePath)
        {
            var str = "";
            DataTable dataTable = ExcelReader(filePath).Tables[0];
            var dt = dataTable.Copy();
            int columnCount = dt.Columns.Count;
            for (int i = 0; i < columnCount; i++)
            {
                int columnsname = i;
                dt.Columns[columnsname].ColumnName = dt.Rows[0][columnsname].ToString();
            }
            dt.Rows.Remove(dt.Rows[0]);
            foreach (DataRow dataRow in dt.Rows)
            {
                dynamic row = new JObject();
                foreach (DataColumn column in dt.Columns)
                {
                    string colName = "";
                    switch (column.ColumnName)
                    {
                        case "標題頭列1": colName = "代碼aaa中名稱"; break;
                        case "標題頭列2": colName = "代碼aaa中名稱"; break;
                        case "標題頭列3": colName = "代碼aaa中名稱"; break;
                        default: break;
                    }
                    row.Add(colName, dataRow[column.ColumnName].ToString());
                }
                str += row + "^";
            }
            return str.TrimEnd('^');
        }

如此就可以用excel導入文件批量新增並對文檔返回錯誤信息下載文檔