Net.Core导入EXCel文件里的数据

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

1、前台的表单：

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();

            headRow.Height = ;

            for (int i = ; 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 = ;

            return File(stream, "application/ms-excel", string.Format("{0}.xlsx", tableName+"_模板"));

        }