C# Excel树级数据导出

　　　　 /// <summary>
        /// 简单导出
        /// </summary>
        /// <param name="args"></param>
        /// <returns></returns>
        public VMSharedExportResult SimpleExport(VMSharedExport<VMReportStaffInfoCondition> args)
        {
            try
            {
                var caller = Caller.Current;
                using var db = new DataContext();

                DBQuery dBQuery = Options.DataAccess.NewQuery();
                BPReportStaffInfoFilter.Instance.Where(caller, db, dBQuery, args.Condition);
                BPReportStaffInfoFilter.Instance.Join(caller, dBQuery);
                BPReportStaffInfoFilter.Instance.Sort(dBQuery, args.OrderByList, args.Condition);
                List<PMStaff> pmList = BPReportStaffInfoOptions.Instance.DataAccess.FindList(caller, db, dBQuery);
                if (pmList.Count == 0) throw new BusinessException("没有可导出的数据！");

                var pmOrganizeList = RoadFlow.Data.Organize.Instance.GetAll(caller, db);
                var pmStateList = RoadFlow.Data.Dictionary.Instance.GetListByCode(caller, db, CDDictionaryBasic.Staff.State);
                var pmProjectRegisterStaffList = DAProjectRegisterStaff.Instance.FindList(caller, db, DDProjectRegisterStaff.FStaffId.WIn(pmList.SelectEx(a => a.FId)));
                var pmProjectList = DAProject.Instance.FindList(caller, db, DDProject.FId.WIn(pmProjectRegisterStaffList.SelectEx(a => a.FProjectId)));
                var pmStaffCertificateList = DAStaffCertificate.Instance.FindList(caller, db, DDStaffCertificate.FStaff.WIn(pmList.SelectEx(a => a.FId)));
                var pmCategoryList = DACategory.Instance.GetAll(caller, db);
                var pmLocationList = DALocation.Instance.GetAll(caller, db);

                HSSFWorkbook hss = new HSSFWorkbook();
                //创建特殊颜色  浅色 矢车菊蓝
                HSSFPalette palette = hss.GetCustomPalette();
                palette.SetColorAtIndex(48, 155, 194, 230);

                //创建Excel
                //HSSFWorkbook hss = new HSSFWorkbook();
                //创建Excel信息信息
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "rlsd";
                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Subject = "人员信息报表";
                hss.DocumentSummaryInformation = dsi;
                hss.SummaryInformation = si;
                ISheet tempSheet = hss.CreateSheet("人员信息报表");

                #region 样式：水平居中、垂直居中、前三行的背景颜色、设置单元格的宽高
                //主表头样式
                IFont font = hss.CreateFont();
                font.IsBold = true;
                font.FontHeightInPoints = 20.0;
                ICellStyle Istyle = hss.CreateCellStyle();
                Istyle.Alignment = HorizontalAlignment.Center;
                Istyle.SetFont(font);
                Istyle.BorderLeft = BorderStyle.Thin;
                Istyle.BorderRight = BorderStyle.Thin;
                Istyle.BorderTop = BorderStyle.Thin;
                Istyle.BorderBottom = BorderStyle.Thin;

                //次级表头样式
                IFont subfont = hss.CreateFont();
                subfont.IsBold = true;
                subfont.FontHeightInPoints = 10.0;
                ICellStyle substyle = hss.CreateCellStyle();
                substyle.Alignment = HorizontalAlignment.Center;
                substyle.SetFont(subfont);
                substyle.BorderLeft = BorderStyle.Thin;
                substyle.BorderRight = BorderStyle.Thin;
                substyle.BorderTop = BorderStyle.Thin;
                substyle.BorderBottom = BorderStyle.Thin;

                //表头行样式
                IFont titleRowfont = hss.CreateFont();
                titleRowfont.IsBold = true;
                titleRowfont.FontHeightInPoints = 10.0;
                ICellStyle titleRowStyle = hss.CreateCellStyle();
                titleRowStyle.Alignment = HorizontalAlignment.Center;
                titleRowStyle.SetFont(titleRowfont);
                titleRowStyle.BorderLeft = BorderStyle.Thin;
                titleRowStyle.BorderRight = BorderStyle.Thin;
                titleRowStyle.BorderTop = BorderStyle.Thin;
                titleRowStyle.BorderBottom = BorderStyle.Thin;
                //titleRowStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
                titleRowStyle.FillForegroundColor = palette.FindColor(155, 194, 230).Indexed;
                titleRowStyle.FillPattern = FillPattern.SolidForeground;

                //行样式
                ICellStyle rowStyle = hss.CreateCellStyle();
                rowStyle.BorderLeft = BorderStyle.Thin;
                rowStyle.BorderRight = BorderStyle.Thin;
                rowStyle.BorderTop = BorderStyle.Thin;
                rowStyle.BorderBottom = BorderStyle.Thin;

                //次级行样式
                ICellStyle subRowStyle = hss.CreateCellStyle();
                subRowStyle.BorderLeft = BorderStyle.Thin;
                subRowStyle.BorderRight = BorderStyle.Thin;
                subRowStyle.BorderTop = BorderStyle.Thin;
                subRowStyle.BorderBottom = BorderStyle.Thin;
                //subRowStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
                subRowStyle.FillForegroundColor = palette.FindColor(155, 194, 230).Indexed;
                subRowStyle.FillPattern = FillPattern.SolidForeground;
                #endregion

                //设置列宽度
                foreach (var item in args.ColumnList)
                {
                    int index = args.ColumnList.IndexOf(item);
                    if (item.Width.HasValue) tempSheet.SetColumnWidth(index, item.Width.Value * 35);
                }

                #region 设置前两行内容
                //合并单元格
                tempSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, args.ColumnList.Count-1));

                //设置第一行内容
                tempSheet.CreateRow(0).CreateCell(0).SetCellValue("");
                for (int i = 1; i <= args.ColumnList.Count; i++)
                {
                    tempSheet.GetRow(0).CreateCell(i).SetCellValue("");
                }
                tempSheet.GetRow(0).GetCell(0).SetCellValue("人员信息报表");
                //设置第一行样式
                tempSheet.GetRow(0).GetCell(0).CellStyle = Istyle;
                tempSheet.GetRow(0).HeightInPoints = 25f;

                //设置第二行
                foreach (var item in args.ColumnList)
                {
                    int index = args.ColumnList.IndexOf(item);
                    var name = item.Name switch
                    {
                        VMReportStaffInfo.XCodes.Sequence => VMReportStaffInfo.XLabels.Sequence,
                        VMReportStaffInfo.XCodes.Name => VMReportStaffInfo.XLabels.Name,
                        VMReportStaffInfo.XCodes.Sex => VMReportStaffInfo.XLabels.Sex,
                        VMReportStaffInfo.XCodes.Organize => VMReportStaffInfo.XLabels.Organize,
                        VMReportStaffInfo.XCodes.Post => VMReportStaffInfo.XLabels.Post,
                        VMReportStaffInfo.XCodes.CertificateNum => VMReportStaffInfo.XLabels.CertificateNum,
                        VMReportStaffInfo.XCodes.IdentityCode => VMReportStaffInfo.XLabels.IdentityCode,
                        VMReportStaffInfo.XCodes.Phone => VMReportStaffInfo.XLabels.Phone,
                        VMReportStaffInfo.XCodes.State => VMReportStaffInfo.XLabels.State,
                        VMReportStaffInfo.XCodes.IsPayMedicalInsurance => VMReportStaffInfo.XLabels.IsPayMedicalInsurance,
                        VMReportStaffInfo.XCodes.IsPaySocialInsurance => VMReportStaffInfo.XLabels.IsPaySocialInsurance,
                        VMReportStaffInfo.XCodes.IsPayHousingFund => VMReportStaffInfo.XLabels.IsPayHousingFund,
                        VMReportStaffInfo.XCodes.ProjectId => VMReportStaffInfo.XLabels.ProjectId,
                        VMReportStaffInfo.XCodes.ProjectName => VMReportStaffInfo.XLabels.ProjectName,
                        VMReportStaffInfo.XCodes.PlanCompletionDate => VMReportStaffInfo.XLabels.PlanCompletionDate,
                        VMReportStaffInfo.XCodes.CompletionDayNum => VMReportStaffInfo.XLabels.CompletionDayNum,
                        _ => string.Empty,
                    };

                    if(index.Equals(0)) tempSheet.CreateRow(1).CreateCell(index).SetCellValue(name);
                    else tempSheet.GetRow(1).CreateCell(index).SetCellValue(name);

                    tempSheet.GetRow(1).GetCell(index).CellStyle = substyle;
                }
                #endregion

                int pmSequence = 1;
                int itemcount = 0;
                foreach (var pm in pmList)
                {
                    var pmProjectRegisterStaff = pmProjectRegisterStaffList.Where(a => a.FStaffId.EqualsEx(pm.FId)).FirstOrDefault();
                    var pmProject = new PMProject();
                    if (!pmProjectRegisterStaff.IsNullEx()) pmProject = pmProjectList.WhereEx(a => a.FId.EqualsEx(pmProjectRegisterStaff?.FProjectId)).FirstOrDefault();
                    decimal completionDayNum = 0;
                    if (!pmProject.IsNullEx() && !pmProject.FPlanStartDate.IsNullEx() && !pmProject.FPlanCompletionDate.IsNullEx())
                    {
                        TimeSpan start = new TimeSpan(((DateTime)pmProject.FPlanStartDate).Ticks);
                        TimeSpan end = new TimeSpan(((DateTime)pmProject.FPlanCompletionDate).Ticks);
                        TimeSpan poor = start.Subtract(end).Duration();
                        completionDayNum = (decimal)poor.TotalDays;
                    }

                    foreach(var item in args.ColumnList)
                    {
                        int index = args.ColumnList.IndexOf(item);
                        var value = item.Name switch
                        {
                            VMReportStaffInfo.XCodes.Sequence => pmSequence.ToString(),
                            VMReportStaffInfo.XCodes.Name => pm.FName,
                            VMReportStaffInfo.XCodes.Sex => pm.FSex,
                            VMReportStaffInfo.XCodes.Organize => pm.FOrganize.GetItemFromRFEx(pmOrganizeList)?.Name,
                            VMReportStaffInfo.XCodes.Post => pm.FPost,
                            VMReportStaffInfo.XCodes.CertificateNum => pmStaffCertificateList.Where(a => a.FStaff.EqualsEx(pm.FId)).Count().ToString(),
                            VMReportStaffInfo.XCodes.IdentityCode => pm.FIdentityCode,
                            VMReportStaffInfo.XCodes.Phone => pm.FPhone,
                            VMReportStaffInfo.XCodes.State => pm.FState.GetPairEx(pmStateList)?.Value,
                            VMReportStaffInfo.XCodes.IsPayMedicalInsurance => pm.FIsPayMedicalInsurance == true ? "是" : "否",
                            VMReportStaffInfo.XCodes.IsPaySocialInsurance => pm.FIsPaySocialInsurance == true ? "是" : "否",
                            VMReportStaffInfo.XCodes.IsPayHousingFund => pm.FIsPayHousingFund == true ? "是" : "否",
                            VMReportStaffInfo.XCodes.ProjectId => pmProject?.FId,
                            VMReportStaffInfo.XCodes.ProjectName => pmProject?.FName,
                            VMReportStaffInfo.XCodes.PlanCompletionDate => pmProject?.FPlanCompletionDate?.ToString("yyyy-MM-dd"),
                            VMReportStaffInfo.XCodes.CompletionDayNum => completionDayNum.ToString(),
                            _ => string.Empty,
                        };

                        if (index.Equals(0)) tempSheet.CreateRow(itemcount+2).CreateCell(index).SetCellValue(value);
                        else tempSheet.GetRow(itemcount + 2).CreateCell(index).SetCellValue(value);

                        tempSheet.GetRow(itemcount + 2).GetCell(index).CellStyle = rowStyle;
                    }
                    itemcount++;

                    var findStaffCertificateList = pmStaffCertificateList.Where(a => a.FStaff.EqualsEx(pm.FId)).ToList();
                    if (findStaffCertificateList.Count > 0)
                    {
                        tempSheet.CreateRow(itemcount + 2).CreateCell(0).SetCellValue("");
                        tempSheet.GetRow(itemcount + 2).CreateCell(1).SetCellValue(VMReportStaffCertificate.XLabels.Sequence);
                        tempSheet.GetRow(itemcount + 2).CreateCell(2).SetCellValue(VMReportStaffCertificate.XLabels.CertificateName);
                        tempSheet.GetRow(itemcount + 2).CreateCell(3).SetCellValue(VMReportStaffCertificate.XLabels.Professional);
                        tempSheet.GetRow(itemcount + 2).CreateCell(4).SetCellValue(VMReportStaffCertificate.XLabels.CertificateCode);
                        tempSheet.GetRow(itemcount + 2).CreateCell(5).SetCellValue(VMReportStaffCertificate.XLabels.IssuingAuthority);
                        tempSheet.GetRow(itemcount + 2).CreateCell(6).SetCellValue(VMReportStaffCertificate.XLabels.EffectiveDate);
                        tempSheet.GetRow(itemcount + 2).CreateCell(7).SetCellValue(VMReportStaffCertificate.XLabels.ExpiredDate);
                        tempSheet.GetRow(itemcount + 2).CreateCell(8).SetCellValue(VMReportStaffCertificate.XLabels.CollectDate);
                        tempSheet.GetRow(itemcount + 2).CreateCell(9).SetCellValue(VMReportStaffCertificate.XLabels.ArchiveLocation);
                        tempSheet.GetRow(itemcount + 2).CreateCell(10).SetCellValue(VMReportStaffCertificate.XLabels.ArchiveCode);
                        tempSheet.GetRow(itemcount + 2).CreateCell(11).SetCellValue(VMReportStaffCertificate.XLabels.State);

                        tempSheet.GetRow(itemcount + 2).GetCell(0).CellStyle = substyle;
                        for (var i = 1; i <= 11; i++) tempSheet.GetRow(itemcount + 2).GetCell(i).CellStyle = titleRowStyle;
                        itemcount++;

                        int subSequence = 1;
                        foreach (var item in findStaffCertificateList)
                        {
                            tempSheet.CreateRow(itemcount + 2).CreateCell(0).SetCellValue("");
                            tempSheet.GetRow(itemcount + 2).CreateCell(1).SetCellValue(pmSequence + "-" + subSequence);
                            tempSheet.GetRow(itemcount + 2).CreateCell(2).SetCellValue(item.FCategory.GetItemFromEx(pmCategoryList)?.FName);
                            tempSheet.GetRow(itemcount + 2).CreateCell(3).SetCellValue(item.FProfessional);
                            tempSheet.GetRow(itemcount + 2).CreateCell(4).SetCellValue(item.FCertificateCode);
                            tempSheet.GetRow(itemcount + 2).CreateCell(5).SetCellValue(item.FIssuingAuthority);
                            tempSheet.GetRow(itemcount + 2).CreateCell(6).SetCellValue(item.FEffectiveDate?.ToString("yyyy-MM-dd"));
                            tempSheet.GetRow(itemcount + 2).CreateCell(7).SetCellValue(item.FExpiredDate?.ToString("yyyy-MM-dd"));
                            tempSheet.GetRow(itemcount + 2).CreateCell(8).SetCellValue(item.FCollectDate?.ToString("yyyy-MM-dd"));
                            tempSheet.GetRow(itemcount + 2).CreateCell(9).SetCellValue(item.FArchiveLocation.GetPairEx(pmLocationList)?.Value);
                            tempSheet.GetRow(itemcount + 2).CreateCell(10).SetCellValue(item.FArchiveCode);
                            tempSheet.GetRow(itemcount + 2).CreateCell(11).SetCellValue(item.FState);

                            tempSheet.GetRow(itemcount + 2).GetCell(0).CellStyle = substyle;
                            for (var i = 1; i <= 11; i++) tempSheet.GetRow(itemcount + 2).GetCell(i).CellStyle = subRowStyle;
                            itemcount++;
                            subSequence++;
                        }
                    }

                    pmSequence++;
                }

                return CreateExport(hss, BPReportStaffInfoOptions.Instance.Name);
            }
            catch (Exception ex)
            {
                throw new BusinessException("人员信息报表导出失败，失败理由：" + ex.Message + "！");
            }
        }