Net.Core匯入EXCel檔案裡的資料

1、前臺的表單：

<form enctype=”multipart/form-data” method=”post” id=”inportFile” name=”inportFile”><input type=”file” name=”excelfile”  id=”excelfile”/></form>

2、資料打包

var postData = new FormData($(“#inportFile”)[0]);

3、ajax上傳 (使用：$.postUploadAjax()) 

封裝後的程式碼

postUploadAjax: function (url, postData, callBackSuccessFunc, callBackErrorFunc) {
$.ajax({
url: url + "&r=" + Math.random(),
data: postData,
type: "POST",
dataType: "json",
processData: false,
contentType: false,
success: function (jsonData) {
if (typeof callBackSuccessFunc === "function") {
callBackSuccessFunc(jsonData);
}
},
error: function (e) {
if (typeof callBackErrorFunc === "function") {
callBackErrorFunc(e);
}
}
});
},

 

 

5、c#程式碼 【匯入部分】（引用using Npoi.Core.HSSF.UserModel;）

    [HttpPost]
        public ActionResult ImportPost(IFormFile excelfile)
        {
            var result = new AjaxResult();
            result.State = AjaxState.SUCCESS;
            result.Message = "匯入成功！";
            try
            {
                var errorList = new List<string>();
             string path = AppDomain.CurrentDomain.BaseDirectory + "\temp";
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                
            string fileName = $"{Guid.NewGuid()}.xlsx";
            var fullPath = path + "\" + fileName;
            var businessid = WebHelper.GetQueryInt("businessid");

                SelectRule selectRule = new SelectRule("V_APP_BUSINESS_MODEL_DETAIL", "IS_HIDE", false);
                selectRule.AddColumnWhere("DISABLED", false);
                selectRule.AddColumnWhere("BUSINESS_ID", businessid);
                selectRule.OrderBy = "order by ORDER_BY asc ";
                List<APP_BUSINESS_MODEL_DETAILModel> list = _service.GSF.GetDataTable(selectRule).ToListModel<APP_BUSINESS_MODEL_DETAILModel>();


                FileInfo file = new FileInfo(Path.Combine(path, fileName));
            using (FileStream fss = new FileStream(file.ToString(), FileMode.Create))
            {
                excelfile.CopyTo(fss);
                fss.Flush();
            }
            IWorkbook wk = null;
           string extension = System.IO.Path.GetExtension(fullPath);
      
                FileStream fs = new FileStream(fullPath, FileMode.Open);
                if (extension.Equals(".xls"))
                {
                    //把xls檔案中的資料寫入wk中
                    wk = new HSSFWorkbook(fs);
                }
                else
                {
                    //把xlsx檔案中的資料寫入wk中
                    wk = new XSSFWorkbook(fs);
                }



                fs.Close();
                //讀取當前表資料
                ISheet sheet = wk.GetSheetAt(0);
                IRow row = sheet.GetRow(0);  //讀取當前行資料
                string strJsonArrary = "";
                for (int i = 1; i <= sheet.LastRowNum; i++)
                {
                    row = sheet.GetRow(i);  //讀取當前行資料
                    if (row != null)
                    {
                        string  strJson="";
                        //LastCellNum 是當前行的總列數
                        for (int j = 0; j < row.LastCellNum; j++)
                        {
                            //讀取該行的第j列資料
                            string key = sheet.GetRow(0).GetCell(j).ToString();
                            var model = list.Find(x => x.COLUMN_NAME == key);
                            string value = row.GetCell(j).ToString();
                            var keyValue = GetJson(model, value);
                            if (keyValue != "")
                            {
                                strJson += keyValue;
                            }
  
                        }
                        if (strJson != "")
                        {
                            strJson ="{"+strJson.Substring(0, strJson.Length - 1)+"},";
                            strJsonArrary += strJson;
                        }
                    }
                }
                strJsonArrary = "["+strJsonArrary.Substring(0, strJsonArrary.Length - 1) + "]";
                result.Data = JArray.Parse(strJsonArrary);
            }
            catch (Exception e)
            {
                result.State = AjaxState.ERROR;
                result.Message = e.Message;
            }

            return Json(result);
        }

　　

6、匯出部分(Excel）

 

        public ActionResult DownTemplet(int business_id,string  tableName)
        {
            SelectRule selectRule = new SelectRule("V_APP_BUSINESS_MODEL_DETAIL", "IS_HIDE", false);
            selectRule.AddColumnWhere("DISABLED", false);
            selectRule.AddColumnWhere("BUSINESS_ID", business_id);
            selectRule.OrderBy = "order by ORDER_BY asc ";
            List<APP_BUSINESS_MODEL_DETAILModel> list = _service.GSF.GetDataTable(selectRule).ToListModel<APP_BUSINESS_MODEL_DETAILModel>();

            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(tableName);
            IRow headRow = sheet.CreateRow(0);
            headRow.Height = 500;
            for (int i = 0; i < list.Count; i++)
            {
                ICell cell = headRow.CreateCell(i);
                cell.SetCellValue(GetTitle(list[i].COLUMN_NAME));
            }
            var stream = new NPOIMemoryStream();
            workbook.Write(stream);
            stream.Flush();
            stream.Position = 0;
            return File(stream, "application/ms-excel", string.Format("{0}.xlsx", tableName+"_模板"));
        }