private static TeamMemberCountModel GetTeamMemberCountByTeamNameAndMatchId(string matchId, string teamNameAndGroupName) { var result = new TeamMemberCountModel(); var teamName = teamNameAndGroupName.Split(';')[0]; var groupName = teamNameAndGroupName.Split(';')[1]; var teamShortName = teamNameAndGroupName.Split(';')[2]; result.TeamName = teamName; result.GroupName = groupName; result.TeamShortName = teamShortName; var sql = $" select * from (select count(*) as MenAthletesCount from athletes where " + $"match_id = '{matchId}' and team_name = '{teamName}' and group_name='{groupName}' and sex = '男') " + $" union all " + $"select * from (select count(*) as WomenAthletesCount from athletes where " + $"match_id = '{matchId}' and team_name = '{teamName}' and group_name='{groupName}' and sex = '女')"; var dataSet = SQLiteHelper.ExecuteDataSet(SQLiteHelper.LocalDbConnectionString, sql, CommandType.Text); var rows = dataSet.Tables[0].Rows; result.MenAthletesCount = rows[0].ItemArray[0] != DBNull.Value ? Convert.ToInt32(rows[0].ItemArray[0]) : 0; result.WomenAthletesCount = rows[1].ItemArray[0] != DBNull.Value ? Convert.ToInt32(rows[1].ItemArray[0]) : 0; var sqlSearchLeaderCoach = $"select count(*) from others where (title='教练' or title='领队') and match_id = '{matchId}' and team_name = '{teamName}' and group_name='{groupName}'"; var dataSetLeaderCoach = SQLiteHelper.ExecuteDataSet(SQLiteHelper.LocalDbConnectionString, sqlSearchLeaderCoach, CommandType.Text); var countRows = dataSetLeaderCoach.Tables[0].Rows; var count = DBHelper.GetTableItemNumberValue(countRows, 0, 0); result.LeaderCoachCount = count; return(result); }
private static int SetEveyTeamCountInfoFooter(IWorkbook workbook, ISheet sheet, int startRow, TeamMemberCountModel team, List <TeamMemberCountModel> teams) { var row = startRow; sheet.AddMergedRegion(new CellRangeAddress(row, row, 1, 2)); // 设置小计 var countRow = sheet.CreateRow(row); var lableCell = countRow.CreateCell(0); lableCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); lableCell.SetCellValue("小计"); var teamCountCell = countRow.CreateCell(1); teamCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); teamCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Count()}"); var manCountCell = countRow.CreateCell(3); manCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); manCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.MenAthletesCount).Sum()}"); var womenCountCell = countRow.CreateCell(4); womenCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); womenCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.WomenAthletesCount).Sum()}"); var leaderCountCell = countRow.CreateCell(5); leaderCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); leaderCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.LeaderCoachCount).Sum()}"); row++; // 设置总计 sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 2)); sheet.AddMergedRegion(new CellRangeAddress(row, row, 3, 4)); var allcountRow = sheet.CreateRow(row); var alllableCell = allcountRow.CreateCell(0); alllableCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); alllableCell.SetCellValue("总计"); var allSexCountCell = allcountRow.CreateCell(3); allSexCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); allSexCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.WomenAthletesCount).Sum() + teams.Where(x => x.GroupName == team.GroupName).Select(x => x.MenAthletesCount).Sum()}"); var allleaderCountCell = allcountRow.CreateCell(5); allleaderCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); allleaderCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.LeaderCoachCount).Sum()}"); var alllCountCell = allcountRow.CreateCell(6); alllCountCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); alllCountCell.SetCellValue($"{teams.Where(x => x.GroupName == team.GroupName).Select(x => x.WomenAthletesCount).Sum() + teams.Where(x => x.GroupName == team.GroupName).Select(x => x.MenAthletesCount).Sum() + teams.Where(x => x.GroupName == team.GroupName).Select(x => x.LeaderCoachCount).Sum()}"); return(row + 3); }
private static int SetEveyTeamCountInfo(IWorkbook workbook, ISheet sheet, int startRow, TeamMemberCountModel team, int num, string groupName = "") { var row = startRow; if (groupName != "") { // 设置组别名 var groupNameRow = sheet.CreateRow(row); sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 6)); var groupNameCell = groupNameRow.CreateCell(0); groupNameCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true, 480); groupNameCell.SetCellValue($"{groupName}"); row++; // 设置表头 sheet.AddMergedRegion(new CellRangeAddress(row, row, 0, 2)); sheet.AddMergedRegion(new CellRangeAddress(row, row, 3, 4)); sheet.AddMergedRegion(new CellRangeAddress(row, row + 1, 5, 5)); sheet.AddMergedRegion(new CellRangeAddress(row, row + 1, 6, 6)); var headerRow = sheet.CreateRow(row); var nameCell = headerRow.CreateCell(0); nameCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); nameCell.SetCellValue("代表队"); var athletesCell = headerRow.CreateCell(3); athletesCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); athletesCell.SetCellValue("运动员人数"); var leaderCell = headerRow.CreateCell(5); leaderCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); leaderCell.CellStyle.WrapText = true; leaderCell.SetCellValue("领队教练人数"); var allCell = headerRow.CreateCell(6); allCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); allCell.SetCellValue("总人数"); row++; var sexRow = sheet.CreateRow(row); var teamNameCell = sexRow.CreateCell(1); teamNameCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); teamNameCell.SetCellValue("全称"); var teamShortNameCell = sexRow.CreateCell(2); teamShortNameCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); teamShortNameCell.SetCellValue("简称"); var menCell = sexRow.CreateCell(3); menCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); menCell.SetCellValue("男"); var womenCell = sexRow.CreateCell(4); womenCell.CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row, true); womenCell.SetCellValue("女"); row++; } var TeamCountRow = sheet.CreateRow(row); TeamCountRow.CreateCell(0).SetCellValue($"{num}"); TeamCountRow.CreateCell(1).SetCellValue($"{team.TeamName}"); TeamCountRow.CreateCell(2).SetCellValue($"{team.TeamShortName}"); TeamCountRow.CreateCell(3).SetCellValue($"{team.MenAthletesCount}"); TeamCountRow.CreateCell(4).SetCellValue($"{team.WomenAthletesCount}"); TeamCountRow.CreateCell(5).SetCellValue($"{team.LeaderCoachCount}"); TeamCountRow.CreateCell(6).SetCellValue($"{team.MenAthletesCount + team.WomenAthletesCount + team.LeaderCoachCount}"); TeamCountRow.GetCell(0).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row); TeamCountRow.GetCell(1).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row); TeamCountRow.GetCell(2).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row); TeamCountRow.GetCell(3).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row); TeamCountRow.GetCell(4).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row); TeamCountRow.GetCell(5).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row); TeamCountRow.GetCell(6).CellStyle = SetEveyTeamCountCommonStyle(workbook, sheet, row); return(++row); }