/// <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 + "！");
}
}
标签：XLabels,C#,Excel,VMReportStaffInfo,tempSheet,Thin,树级,XCodes,BorderStyle From： https://www.cnblogs.com/Lin--xy/p/16662244.html