Excel操作

Mi manchi241

已于 2022-08-04 15:01:38 修改

阅读量148

点赞数

文章标签： .net

于 2022-03-31 11:36:09 首次发布

本文链接：https://blog.youkuaiyun.com/qq_56507512/article/details/123867724

版权

本文介绍了一种使用.NET Framework和.NET Core实现Excel文件上传及数据读取的方法，并提供了详细的前端与后端代码示例。此外，还展示了如何将数据导出到Excel文件。

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

需引入NPOI依赖包

前端代码：

JS：

HTML：

后端代码：

//.NET Framework写法

public string str()
        {
            HttpFileCollectionBase file = Request.Files;//获取选中文件
            hancha hancha = new hancha();
            if (file.Count > 0)
            {
                string _guid = DateTime.Now.ToString("yyyy年MM月dd日") + "-" + Guid.NewGuid().ToString().ToUpper().Replace("-", "");
                string fileName = file[0].FileName;
                Stream fs = file[0].InputStream;
                if (file != null && file[0].ContentLength > 0)
                {
                    using (StreamReader sr = new StreamReader(fs, Encoding.UTF8))
                    {
                        string save_diretion = Server.MapPath("../upload/");

                        //string save_diretion = Server.MapPath("../../Upload/");
                        string save_path = save_diretion + _guid + "-" + fileName;\

                            file[0].SaveAs(save_path);
                            if (fileName.EndsWith(".xls") || fileName.EndsWith(".xlsx"))
                            {
                                XSSFWorkbook workBook = new XSSFWorkbook(new FileStream(Path.GetFullPath(save_path), FileMode.Open));
                                XSSFSheet sheet = (XSSFSheet)workBook.GetSheetAt(0);
                                int rowCount = sheet.LastRowNum;
                                //开始添加

                            for (int j = 0; j < rowCount; j++)
                                {
                                    string Id = Guid.NewGuid().ToString();
                                    hancha.Month = sheet.GetRow(j + 1).GetCell(0).ToString().Trim().Replace("\n", "；");
                                    hancha.AmountAll = sheet.GetRow(j + 1).GetCell(1).ToString().Trim().Replace("\n", "；");
                                    hancha.CorrelationNumber = sheet.GetRow(j + 1).GetCell(2).ToString().Trim().Replace("\n", "；");
                                    hancha.ExtractionRate = sheet.GetRow(j + 1).GetCell(3).ToString().Trim().Replace("\n", "；");
                                context.hancha.Add(hancha);
                                context.SaveChanges();
                                }
                        }             
                    }

                }

            }
            return null;
        }

//.NET Core写法（引入EPPlus.Core）

  public async Task<IActionResult> ImportExcel()
        {
            //获取request中的file文件
            var file = Request.Form.Files[0];
            //获取文件名
            var filename = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName;
            // 获取扩展名
            var extName = filename.Substring(filename.LastIndexOf('.')).Replace("\"", "");
            // 新文件名
            string shortfilename = $"{Guid.NewGuid()}{extName}";
            //文件临时目录，导入完成后 删除
            string fileSavePath = _hostingEnvironment.WebRootPath + @"\upload\";
            // 新文件名（包括路径
            filename = fileSavePath + shortfilename; 
            //如果文件夹不存在则创建
            if (!Directory.Exists(fileSavePath))
            {
                Directory.CreateDirectory(fileSavePath);
            }
            // 创建新文件
            using (FileStream fs = System.IO.File.Create(filename)) 
            {
                await file.CopyToAsync(fs);// 复制文件
                fs.Flush();// 清空缓冲区数据
                //根据 filename 【文件服务器磁盘路径】可对文件进行业务操作
            }
            FileInfo file = new FileInfo(filename);
             using (ExcelPackage package = new ExcelPackage(file))
            {
                try
                {
                    //获取第一个sheet
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    //行数
                    int rowCount = worksheet.Dimension.Rows;
                    //列数
                    int columnsCount = worksheet.Dimension.Columns;
                    for (int row = 2; row <= rowCount; row++)
                    {
                        DateTime time;
                        if (DateTime.TryParse(worksheet.Cells[row, 1].Value.ToString(), out time))
                        {
                            Order.Time = time;
                        }
                        else
                        {
                            erro = "日期格式不正确";
                            break;
                        }
                        Order.ZGperson = worksheet.Cells[row, 2].Value.ToString();
                        Order.XT = worksheet.Cells[row, 3].Value.ToString();
                        Order.Team = worksheet.Cells[row, 4].Value.ToString();
                        Order.Ji = worksheet.Cells[row, 5].Value.ToString();

                        //机型和生产订单查找dss系统验证正确性
                        Order.AircraftType = worksheet.Cells[row, 6].Value.ToString();
                        Order.ProductionOrder = worksheet.Cells[row, 7].Value.ToString();

                        string sql = "select product_code  FROM [dss].[dbo].[keji_plan_info] where produce_order = '{0}'";
                        sql = string.Format(sql, Order.ProductionOrder);
                        string constr = _configuration["SqlConnectionStr:ConnectionStr2"];
                        DataTable data = SqlHelper.ExcuteQuery(sql, constr);
                        if (data.Rows.Count <= 0)
                        {
                            erro = "该订单号无数据";
                            break;
                        }
                        else
                        {
                            if (data.Rows[0]["product_code"].ToString() == Order.AircraftType)
                            {

                            }
                            else
                            {
                                erro = "生产订单和机型不匹配";
                                break;
                            }
                        }
                        int count;
                        if (int.TryParse(worksheet.Cells[row, 8].Value.ToString(), out count))
                        {
                            Order.Amount = count;
                        }
                        else
                        {
                            erro = "数量格式不正确";
                            break;
                        }
                        Order.Reason = worksheet.Cells[row, 9].Value.ToString();
                        Order.Unit = getUnit(worksheet.Cells[row, 10].Value.ToString());
                        Order.ProcessingTime = DateTime.Now;
                    }
                }
                catch (Exception ex)
                {
                    throw;
                }
            }
            if (System.IO.File.Exists(filename))
            {
                System.IO.File.Delete(filename);
            }
            if (erro != "")
            {
                return Json(new { msg = erro });
            }
            else
            {
                return Json(new { data = observer.objs.FirstOrDefault() });
            }
        }

//导出到Excel（.NET CORE）

JS：

var url = "/Controller/Action/参数"

window.open(url)

后端代码：

private readonly IHostingEnvironment _hostingEnvironment;
  public HomeController(ILogger<HomeController> logger, DataBaseContext dataBaseContext, IHostingEnvironment hostingEnvironment)
        {
            _context = dataBaseContext;
            _logger = logger;
            _hostingEnvironment = hostingEnvironment;
            sWebRootFolder = _hostingEnvironment.WebRootPath;
        }

public IActionResult ExportNormalExcel(List<Sys_NormalSeal> _NormalSeals)
        {
            if (!Directory.Exists(sWebRootFolder))
            {
                Directory.CreateDirectory(sWebRootFolder);
            }
            string sFileName = $"{DateTime.Now.ToString("yyyyMMddhhmmss")}{Guid.NewGuid()}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            file.Delete();
            using (ExcelPackage package = new ExcelPackage(file))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("普通封存订单记录");
                worksheet.Cells[1, 1].Value = "时间";
                worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 2].Value = "质管员";
                worksheet.Cells[1, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 3].Value = "线体";
                worksheet.Cells[1, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 4].Value = "班组";
                worksheet.Cells[1, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, 4].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 5].Value = "内外机";
                worksheet.Cells[1, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, 5].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 6].Value = "机型";
                worksheet.Cells[1, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, 6].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 7].Value = "生产订单";
                worksheet.Cells[1, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, 7].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 8].Value = "数量";
                worksheet.Cells[1, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, 8].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 9].Value = "处理时间";
                worksheet.Cells[1, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, 9].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 10].Value = "状态";
                worksheet.Cells[1, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, 10].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                worksheet.Cells[1, 11].Value = "暂停原因";
                worksheet.Cells[1, 11].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                worksheet.Cells[1, 11].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                for (int i = 0; i < _NormalSeals.Count; i++)
                {
                    worksheet.Cells[i + 2, 1].Value = _NormalSeals[i].Time.ToString();
                    worksheet.Cells[i + 2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    worksheet.Cells[i + 2, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                    worksheet.Cells[i + 2, 2].Value = _NormalSeals[i].ZGperson;
                    worksheet.Cells[i + 2, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    worksheet.Cells[i + 2, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                    worksheet.Cells[i + 2, 3].Value = _NormalSeals[i].XT;
                    worksheet.Cells[i + 2, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    worksheet.Cells[i + 2, 3].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                    worksheet.Cells[i + 2, 4].Value = _NormalSeals[i].Team;
                    worksheet.Cells[i + 2, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    worksheet.Cells[i + 2, 4].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                    worksheet.Cells[i + 2, 5].Value = _NormalSeals[i].Ji;
                    worksheet.Cells[i + 2, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    worksheet.Cells[i + 2, 5].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                    worksheet.Cells[i + 2, 6].Value = _NormalSeals[i].AircraftType;
                    worksheet.Cells[i + 2, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    worksheet.Cells[i + 2, 6].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                    worksheet.Cells[i + 2, 7].Value = _NormalSeals[i].ProductionOrder;
                    worksheet.Cells[i + 2, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    worksheet.Cells[i + 2, 7].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                    worksheet.Cells[i + 2, 8].Value = _NormalSeals[i].Amount.ToString();
                    worksheet.Cells[i + 2, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    worksheet.Cells[i + 2, 8].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                    worksheet.Cells[i + 2, 9].Value = _NormalSeals[i].ProcessingTime.ToString();
                    worksheet.Cells[i + 2, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    worksheet.Cells[i + 2, 9].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                    worksheet.Cells[i + 2, 10].Value = GetState(_NormalSeals[i].State);
                    worksheet.Cells[i + 2, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    worksheet.Cells[i + 2, 10].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                    worksheet.Cells[i + 2, 11].Value = _NormalSeals[i].Reason;
                    worksheet.Cells[i + 2, 11].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    worksheet.Cells[i + 2, 11].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
                }
                package.Save();
            }
            return File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", sFileName);
        }