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

最新推荐文章于 2024-04-15 23:25:56 发布

转载最新推荐文章于 2024-04-15 23:25:56 发布 · 726 阅读

3 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zhou711688/p/9602903.html

文章标签：

#c# #测试 #json

本文介绍了一种利用前端HTML表单和Ajax技术上传Excel文件至服务器的方法，并通过C#与NPOI库解析Excel数据，同时提供了导出Excel模板的功能。详细步骤包括前端表单设置、Ajax上传文件、C#解析Excel、错误处理及导出模板。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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(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+"_模板"));
        }