NET web NPOI操作excel-优快云博客

本文链接：https://blog.youkuaiyun.com/CsharpDonet/article/details/41957427

本文介绍如何从用户信息数据库中导出数据，并通过代码实现将数据转换为Excel文件，便于用户查看和管理。

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

1、所需dll

public ActionResult exportData(Userinfor userinfor, Pager pager,String parentId,String sort,String direction,String isPage)
        {
            
            Boolean paging = false;
            if (isPage == null || "".Equals(isPage))
            {
                paging = false;
            }
            else
            {
                if ("1".Equals(isPage))
                {
                    paging = true;
                }
                else
                {
                    paging = false;
                }
            }


            int parentid = 0;
            if (parentId != null && !"".Equals(parentId))
            {
                try
                {
                    parentid = Convert.ToInt16(parentId);
                }
                catch (Exception)
                {
                    parentid = 0;
                }
            }
            else
            {
                parentid = 0;
            }
            
            List<Userinfor> userList = this.userService.getUsers(userinfor, pager, parentid, sort, direction, paging);
            HSSFWorkbook workbook = new HSSFWorkbook();
            Stream outputStream = Response.OutputStream;
            HSSFSheet sheet = workbook.CreateSheet("人员信息");
            try
            {
                if (workbook != null)
                {
                    HSSFRow headRow = sheet.CreateRow(0);
                    headRow.CreateCell(0).SetCellValue("姓名");
                    headRow.CreateCell(1).SetCellValue("所属部门");
                    headRow.CreateCell(2).SetCellValue("性别");
                    headRow.CreateCell(3).SetCellValue("入职时间");
                    headRow.CreateCell(4).SetCellValue("学历");
                }


                for (int i = 0; i < userList.Count; i++)
                {
                    int row = i + 1;
                    Userinfor u = userList.ElementAt(i);
                    HSSFRow dataRow = sheet.CreateRow(row);


                    dataRow.CreateCell(0).SetCellValue(u.userName);


                    if (u.organization != null && u.organization.orgName != null)
                    {
                        dataRow.CreateCell(1).SetCellValue(u.organization.orgName);
                    }
                    else
                    {
                        dataRow.CreateCell(1).SetCellValue("");
                    }


                    if (u.userSex != null)
                    {
                        dataRow.CreateCell(2).SetCellValue((bool)u.userSex ? "男" : "女");
                    }
                    else
                    {
                        dataRow.CreateCell(2).SetCellValue("");
                    }


                    if (u.userEmployTime != null)
                    {
                        dataRow.CreateCell(3).SetCellValue(((DateTime)u.userEmployTime).ToString("yyyy-MM-dd"));
                    }
                    else
                    {
                        dataRow.CreateCell(3).SetCellValue("");
                    }


                    String education = u.userEducation;
                    if ("1".Equals(education))
                    {
                        education = "专科";
                    }
                    else if ("2".Equals(education))
                    {
                        education = "本科";
                    }
                    else if ("3".Equals(education))
                    {
                        education = "硕士";
                    }
                    else if ("4".Equals(education))
                    {
                        education = "博士";
                    }
                    else
                    {
                        education = "";
                    }
                    dataRow.CreateCell(4).SetCellValue(education);
                }


                Response.Clear();
                workbook.Write(outputStream);
                
                Response.Buffer = true;
                Response.AppendHeader("Content-Disposition", "attachment;filename=用户信息.xls");
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/vnd.ms-excel";
                Response.Flush();
            }
            catch (Exception e)
            {
            }
            finally
            {
                workbook = null;
            }
            
            return null;
        }

  public string getValue(HSSFCell cell)
        {
            if (cell.CellType == HSSFCell.CELL_TYPE_NUMERIC)
            {
                return cell.NumericCellValue.ToString();
            }
            if (cell.CellType == HSSFCell.CELL_TYPE_STRING)
            {
                return cell.StringCellValue;
            }
            if (cell.CellType == HSSFCell.CELL_TYPE_BOOLEAN)
            {
                return cell.BooleanCellValue.ToString();
            }
            return "";
        }

3、具体可参考qui中的使用或求助度娘！