MESProductionWeb/Central.Control.WebPage/App_Start/ExportExcelHelper.cs

using Ant.Data;
using Ant.ORM;
using MES.Production.Entity;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using ZXing;
using ZXing.QrCode;

namespace ChangFa.Machinery.WebPage.App_Start
{
    public class ExportExcelHelper
    {
        public static MemoryStream ExportSchoolExcel(dynamic schoolInfo, List<dynamic> sporterInfo)
        {
            HSSFWorkbook book = new HSSFWorkbook();

            #region 样式定义
            HSSFCellStyle titleStyle = (HSSFCellStyle)book.CreateCellStyle();
            titleStyle.Alignment = HorizontalAlignment.CENTER;
            titleStyle.VerticalAlignment = VerticalAlignment.CENTER;

            HSSFFont titlefont = (HSSFFont)book.CreateFont();
            titlefont.FontName = "黑体";
            titlefont.FontHeightInPoints = (short)12;

            titleStyle.SetFont(titlefont);

            HSSFCellStyle contentStyle = (HSSFCellStyle)book.CreateCellStyle();
            contentStyle.Alignment = HorizontalAlignment.CENTER;
            contentStyle.VerticalAlignment = VerticalAlignment.CENTER;
            contentStyle.BorderBottom = BorderStyle.THIN;
            contentStyle.BorderLeft = BorderStyle.THIN;
            contentStyle.BorderRight = BorderStyle.THIN;
            contentStyle.BorderTop = BorderStyle.THIN;

            HSSFFont contentfont = (HSSFFont)book.CreateFont();
            contentfont.FontName = "黑体";
            contentfont.FontHeightInPoints = (short)10;

            contentStyle.SetFont(contentfont);
            #endregion

            #region 运动员参赛
            ISheet sheet1 = book.CreateSheet("运动员参赛");
            //sheet1.SetColumnWidth(0, 20 * 256);
            //sheet1.SetColumnWidth(1, 20 * 256);
            //sheet1.SetColumnWidth(2, 20 * 256);
            //sheet1.SetColumnWidth(3, 20 * 256);
            //sheet1.SetColumnWidth(4, 20 * 256);
            //sheet1.SetColumnWidth(5, 20 * 256);
            //sheet1.SetColumnWidth(6, 20 * 256);
            //sheet1.SetColumnWidth(7, 20 * 256);
            //sheet1.SetColumnWidth(8, 20 * 256);
            //sheet1.SetColumnWidth(9, 20 * 256);
            //sheet1.SetColumnWidth(10, 20 * 256);

            #region 比赛名称
            IRow row1 = sheet1.CreateRow(0);

            for (int i = 0; i < 11; i++)
            {
                row1.CreateCell(i);
            }

            var cellRangeAddress = new CellRangeAddress(0, 0, 0, 10);
            sheet1.AddMergedRegion(cellRangeAddress);

            var cell = row1.GetCell(0);

            cell.CellStyle = titleStyle;

            var cellContent = schoolInfo.RaceName;

            cell.SetCellValue(cellContent);
            #endregion

            #region 代表队名单
            IRow row2 = sheet1.CreateRow(1);

            for (int i = 0; i < 11; i++)
            {
                row2.CreateCell(i);
            }

            cellRangeAddress = new CellRangeAddress(1, 1, 0, 10);
            sheet1.AddMergedRegion(cellRangeAddress);

            cell = row2.GetCell(0);

            cell.CellStyle = titleStyle;

            cellContent = "代表队名单";

            cell.SetCellValue(cellContent);
            #endregion

            #region 学校名称
            IRow row3 = sheet1.CreateRow(2);

            for (int i = 0; i < 11; i++)
            {
                row3.CreateCell(i);
            }

            cellRangeAddress = new CellRangeAddress(2, 2, 0, 10);
            sheet1.AddMergedRegion(cellRangeAddress);

            cell = row3.GetCell(0);

            cell.CellStyle = titleStyle;

            cellContent = schoolInfo.SchoolName;

            cell.SetCellValue(cellContent);
            #endregion

            #region 领队信息
            IRow row4 = sheet1.CreateRow(3);

            for (int i = 0; i < 11; i++)
            {
                row4.CreateCell(i);
            }

            cellRangeAddress = new CellRangeAddress(3, 3, 0, 10);
            sheet1.AddMergedRegion(cellRangeAddress);

            cell = row4.GetCell(0);

            cell.CellStyle = titleStyle;

            cellContent = string.Format("领队:{0}      教练员:{1}       联系电话:{2}", schoolInfo.Leader, schoolInfo.Coacher, schoolInfo.Contact);

            cell.SetCellValue(cellContent);
            #endregion

            #region 学生信息
            IRow row5 = sheet1.CreateRow(4);

            cell = row5.CreateCell(0);
            cell.CellStyle = contentStyle;
            cell.SetCellValue("注册证号");

            cell = row5.CreateCell(1);
            cell.CellStyle = contentStyle;
            cell.SetCellValue("姓名");

            cell = row5.CreateCell(2);
            cell.CellStyle = contentStyle;
            cell.SetCellValue("性别");

            cell = row5.CreateCell(3);
            cell.CellStyle = contentStyle;
            cell.SetCellValue("学籍号");

            cell = row5.CreateCell(4);
            cell.CellStyle = contentStyle;
            cell.SetCellValue("身份证号");

            cell = row5.CreateCell(5);
            cell.CellStyle = contentStyle;
            cell.SetCellValue("年级");

            cell = row5.CreateCell(6);
            cell.CellStyle = contentStyle;
            cell.SetCellValue("组别");

            cell = row5.CreateCell(7);
            cell.CellStyle = contentStyle;
            cell.SetCellValue("项目1");

            cell = row5.CreateCell(8);
            cell.CellStyle = contentStyle;
            cell.SetCellValue("项目2");

            cell = row5.CreateCell(9);
            cell.CellStyle = contentStyle;
            cell.SetCellValue("项目3");

            cell = row5.CreateCell(10);
            cell.CellStyle = contentStyle;
            cell.SetCellValue("项目4");

            var contentRowIndex = 5;

            try
            {
                foreach (dynamic item in sporterInfo)
                {
                    IRow contentRow = sheet1.CreateRow(contentRowIndex);

                    cell = contentRow.CreateCell(0);
                    cell.CellStyle = contentStyle;
                    cell.SetCellValue(item.RaceNo);
                    //sheet1.AutoSizeColumn(0);
                    //sheet1.SetColumnWidth(0, sheet1.GetColumnWidth(0) * 11 / 10);

                    cell = contentRow.CreateCell(1);
                    cell.CellStyle = contentStyle;
                    cell.SetCellValue(item.Name);
                    //sheet1.AutoSizeColumn(1);
                    //sheet1.SetColumnWidth(1, sheet1.GetColumnWidth(1) * 11 / 10);

                    cell = contentRow.CreateCell(2);
                    cell.CellStyle = contentStyle;
                    cell.SetCellValue(item.Sex);
                    //sheet1.AutoSizeColumn(2);
                    //sheet1.SetColumnWidth(2, sheet1.GetColumnWidth(2) * 11 / 10);

                    cell = contentRow.CreateCell(3);
                    cell.CellStyle = contentStyle;
                    cell.SetCellValue(item.RegNo);
                    //sheet1.AutoSizeColumn(3);
                    //sheet1.SetColumnWidth(3, sheet1.GetColumnWidth(3) * 11 / 10);

                    cell = contentRow.CreateCell(4);
                    cell.CellStyle = contentStyle;
                    cell.SetCellValue(item.IDCard);
                    //sheet1.AutoSizeColumn(4);
                    //sheet1.SetColumnWidth(4, sheet1.GetColumnWidth(4) * 11 / 10);

                    cell = contentRow.CreateCell(5);
                    cell.CellStyle = contentStyle;
                    cell.SetCellValue(item.Grade);
                    //sheet1.AutoSizeColumn(5);
                    //sheet1.SetColumnWidth(5, sheet1.GetColumnWidth(5) * 11 / 10);

                    cell = contentRow.CreateCell(6);
                    cell.CellStyle = contentStyle;
                    cell.SetCellValue(item.Group);
                    //sheet1.AutoSizeColumn(6);
                    //sheet1.SetColumnWidth(6, sheet1.GetColumnWidth(6) * 11 / 10);

                    cell = contentRow.CreateCell(7);
                    cell.CellStyle = contentStyle;
                    cell.SetCellValue(item.Project1);
                    //sheet1.AutoSizeColumn(7);
                    //sheet1.SetColumnWidth(7, sheet1.GetColumnWidth(7) * 11 / 10);

                    cell = contentRow.CreateCell(8);
                    cell.CellStyle = contentStyle;
                    cell.SetCellValue(item.Project2);

                    cell = contentRow.CreateCell(9);
                    cell.CellStyle = contentStyle;
                    cell.SetCellValue(item.Project3);

                    cell = contentRow.CreateCell(10);
                    cell.CellStyle = contentStyle;
                    cell.SetCellValue(item.Project4);

                    contentRowIndex++;
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

            #endregion
            #endregion

            #region 运动员参赛卡片

            ISheet sheet2 = book.CreateSheet("运动员参赛卡片");

            sheet2.FitToPage = false;
            sheet2.SetMargin(MarginType.RightMargin, 0.1);
            sheet2.SetMargin(MarginType.TopMargin, 0.1);
            sheet2.SetMargin(MarginType.LeftMargin, 0.1);
            sheet2.SetMargin(MarginType.BottomMargin, 0.1);
            sheet2.PrintSetup.PaperSize = (short)PaperSize.A4;

            int rowIndex = 1;

            foreach (dynamic item in sporterInfo)
            {
                QrCodeEncodingOptions options = new QrCodeEncodingOptions
                {
                    DisableECI = true,
                    CharacterSet = "UTF-8",
                    Width = 84,
                    Height = 84,
                    Margin = 0
                };
                BarcodeWriter writer = new BarcodeWriter();
                writer.Format = BarcodeFormat.QR_CODE;
                writer.Options = options;
                Bitmap QRCodeBitmap = writer.Write(item.QRCode);


                Bitmap raceCardBitMap = new Bitmap(500, 250);
                Graphics graphics = Graphics.FromImage(raceCardBitMap);
                graphics.Clear(Color.White);

                Font font1 = new Font("宋体", 10);
                Font font2 = new Font("宋体", 12);

                Brush brush1 = new System.Drawing.SolidBrush(Color.Black);

                graphics.DrawLine(new Pen(Color.Black, 1), new Point(0, 0), new Point(500, 0));
                graphics.DrawLine(new Pen(Color.Black, 1), new Point(0, 249), new Point(500, 249));

                graphics.DrawLine(new Pen(Color.Black, 1), new Point(0, 0), new Point(0, 250));
                graphics.DrawLine(new Pen(Color.Black, 1), new Point(499, 0), new Point(499, 250));

                var leftMargin = (Int32)((500 - graphics.MeasureString("***运动员电子参赛证（仅限本次比赛使用）***", font1).Width) / 2);

                graphics.DrawString("***运动员电子参赛证（仅限本次比赛使用）***", font1, brush1, new Point(leftMargin > 0 ? leftMargin : 0, 5));

                leftMargin = (Int32)((500 - graphics.MeasureString(schoolInfo.RaceName, font1).Width) / 2);

                graphics.DrawString(schoolInfo.RaceName, font1, brush1, new Point(leftMargin > 0 ? leftMargin : 0, 30));

                graphics.DrawImage(QRCodeBitmap, new Point(20, 70));

                graphics.DrawString(item.Name, font2, brush1, new Point(150, 60));

                graphics.DrawString(item.SchoolName, font1, brush1, new Point(150, 90));

                graphics.DrawString(item.Grade, font1, brush1, new Point(150, 115));

                graphics.DrawString(item.Project1 ?? string.Empty, font1, brush1, new Point(150, 140));
                graphics.DrawString(item.Project2 ?? string.Empty, font1, brush1, new Point(150, 165));
                graphics.DrawString(item.Project3 ?? string.Empty, font1, brush1, new Point(150, 190));
                graphics.DrawString(item.Project4 ?? string.Empty, font1, brush1, new Point(150, 215));

                MemoryStream bitMapMs = new MemoryStream();
                raceCardBitMap.Save(bitMapMs, System.Drawing.Imaging.ImageFormat.Bmp);
                byte[] buff = bitMapMs.GetBuffer();

                var picIndex = (int)(rowIndex / 2) + ((rowIndex % 2) > 0 ? 1 : 0);
                var picSecond = rowIndex % 2 == 0;

                IDrawing patriarch = sheet2.CreateDrawingPatriarch();
                int pic = book.AddPicture(buff, PictureType.JPEG);

                HSSFClientAnchor anchor = null;

                if (picSecond)
                {
                    anchor = new HSSFClientAnchor(0, 0, 0, 0, 6, 11 * (picIndex - 1) + 2 * picIndex, 11, 11 * (picIndex - 1) + 2 * picIndex + 11);
                }
                else
                {
                    anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 11 * (picIndex - 1) + 2 * picIndex, 5, 11 * (picIndex - 1) + 2 * picIndex + 11);
                }

                anchor.AnchorType = 2;
                patriarch.CreatePicture(anchor, pic);

                if (rowIndex % 8 == 0)
                {
                    sheet2.SetRowBreak(anchor.Row2);
                }

                rowIndex++;
            }
            #endregion

            AutoColumnWidth(sheet1, 7, 4);

            MemoryStream ms = new MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);

            return ms;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="cols"></param>
        public static void AutoColumnWidth(ISheet sheet, int cols, int rowbBeginIndex)
        {
            for (int col = 0; col <= cols; col++)
            {
                int columnWidth = 0;

                for (int rowIndex = rowbBeginIndex; rowIndex <= sheet.LastRowNum; rowIndex++)
                {
                    IRow row = sheet.GetRow(rowIndex);
                    ICell cell = row.GetCell(col);
                    if (cell != null)
                    {
                        int contextLength = Encoding.GetEncoding("gb2312").GetBytes(cell.ToString()).Length;//获取当前单元格的内容宽度
                        columnWidth = columnWidth < contextLength ? contextLength : columnWidth;

                    }
                }

                columnWidth = columnWidth * 256 * 11 / 10;

                columnWidth = columnWidth > 254 * 256 ? 254 * 256 : columnWidth;

                sheet.SetColumnWidth(col, columnWidth);

                try
                {
                    sheet.AutoSizeColumn(col);
                    sheet.SetColumnWidth(col, sheet.GetColumnWidth(col) * 11 / 10);
                }
                catch (Exception ex)
                {
                }
            }
        }


        public static MemoryStream ExportAllSchoolExcel(RaceInfo raceInfo, List<RaceContactInfo> contactInfos, List<RaceSporterInfo> sporterInfos, List<RaceStatisticsInfo> statisticsInfos)
        {
            if (raceInfo == null)
            {
                raceInfo = new RaceInfo();
            }

            if (contactInfos == null)
            {
                contactInfos = new List<RaceContactInfo>();

            }

            if (sporterInfos == null)
            {
                sporterInfos = new List<RaceSporterInfo>();
            }

            if (statisticsInfos == null)
            {
                statisticsInfos = new List<RaceStatisticsInfo>();
            }

            foreach (var itemSporter in sporterInfos)
            {
                if (itemSporter.Projects == null)
                {
                    itemSporter.Projects = new List<string>();
                }
                while (itemSporter.Projects.Count < 4)
                {
                    itemSporter.Projects.Add(string.Empty);
                }
            }

            HSSFWorkbook sourceBook = null;
            ISheet sheet = null;
            IRow row = null;
            int rowsCount = 0;
            int currentRowIndex = 0;

            using (FileStream fs = File.Open(HttpContext.Current.Server.MapPath("~/Template/资格审查导出模板.xls"), FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                sourceBook = new HSSFWorkbook(fs);
            }

            var schools = sporterInfos.OrderBy(p => p.SchoolType).GroupBy(m => new { m.SchoolID, m.SchoolName }).Distinct().Select(t => new RaceSporterInfo
            {
                SchoolID = t.Key.SchoolID,
                SchoolName = t.Key.SchoolName
            }).ToList();
            //var schools = sporterInfos.GroupBy(p => p.SchoolID).Select(p => p.).ToList();

            #region 代表队
            sheet = sourceBook.GetSheet("代表队");

            rowsCount = schools.Count * 3 + sporterInfos.Count + 2;

            for (int i = 0; i < rowsCount; i++)
            {
                row = sheet.GetRow(i);

                if (row == null)
                {
                    row = sheet.CreateRow(i);
                }
            }

            sheet.GetRow(0).GetCell(0).SetCellValue(raceInfo.RaceName ?? string.Empty);

            currentRowIndex = 1;

            foreach (var itemSchool in schools)
            {
                currentRowIndex++;
                if (currentRowIndex != 2)
                {
                    sheet.CopyRow(2, currentRowIndex);
                }
                sheet.GetRow(currentRowIndex).GetCell(0).SetCellValue(itemSchool.SchoolName);

                currentRowIndex++;
                if (currentRowIndex != 3)
                {
                    sheet.CopyRow(3, currentRowIndex);
                }
                var contactmod = contactInfos.Where(p => p.SchoolID == itemSchool.SchoolID).FirstOrDefault();
                if (contactmod.IfNotNull())
                {
                    string content = "";
                    if (!contactmod.Coach1.IsEmpty())
                    {
                        content = (content.IsEmpty()) ? contactmod.Coach1 : content + " " + contactmod.Coach1;
                    }
                    if (!contactmod.Coach2.IsEmpty())
                    {
                        content = (content.IsEmpty()) ? contactmod.Coach2 : content + " " + contactmod.Coach2;
                    }
                    if (!contactmod.Coach3.IsEmpty())
                    {
                        content = (content.IsEmpty()) ? contactmod.Coach3 : content + " " + contactmod.Coach3;
                    }
                    if (!contactmod.Coach4.IsEmpty())
                    {
                        content = (content.IsEmpty()) ? contactmod.Coach4 : content + " " + contactmod.Coach4;
                    }

                    var cellContent = string.Format("领队：{0}      教练员：{1}       联系电话：{2}", contactmod.Leader, content, contactmod.ContactPhone);
                    sheet.GetRow(currentRowIndex).GetCell(0).SetCellValue(cellContent);
                }
                else
                {
                    sheet.GetRow(currentRowIndex).GetCell(0).SetCellValue("");
                }

                currentRowIndex++;
                if (currentRowIndex != 4)
                {
                    sheet.CopyRow(4, currentRowIndex);
                }

                var sporters = sporterInfos.Where(p => p.SchoolID == itemSchool.SchoolID).OrderBy(p => p.Sex).OrderBy(p => p.GroupName).ToList();

                foreach (var itemSporter in sporters)
                {
                    currentRowIndex++;
                    if (currentRowIndex != 5)
                    {
                        sheet.CopyRow(5, currentRowIndex);
                    }

                    row = sheet.GetRow(currentRowIndex);
                    row.GetCell(0).SetCellValue("");
                    row.GetCell(1).SetCellValue(itemSporter.SporterName.Trim());
                    row.GetCell(2).SetCellValue(itemSporter.Sex.Trim());
                    row.GetCell(3).SetCellValue(itemSporter.RegNo.Trim());
                    row.GetCell(4).SetCellValue(itemSporter.IDCard.Trim());
                    row.GetCell(5).SetCellValue(itemSporter.Grade.Trim());
                    row.GetCell(6).SetCellValue(itemSporter.GroupName.Trim());
                    row.GetCell(7).SetCellValue(itemSporter.Projects[0]);
                    row.GetCell(8).SetCellValue(itemSporter.Projects[1]);
                    row.GetCell(9).SetCellValue(itemSporter.Projects[2]);
                    row.GetCell(10).SetCellValue(itemSporter.Projects[3]);
                }

                currentRowIndex++;
                if (currentRowIndex != 6)
                {
                    sheet.CopyRow(6, currentRowIndex);
                }
                row = sheet.GetRow(currentRowIndex);
                row.GetCell(1).SetCellValue("");
                row.GetCell(2).SetCellValue("");
                row.GetCell(3).SetCellValue("");
                row.GetCell(4).SetCellValue("");
                row.GetCell(5).SetCellValue("");
                row.GetCell(6).SetCellValue("");
                row.GetCell(7).SetCellValue("");
                row.GetCell(8).SetCellValue("");
                row.GetCell(9).SetCellValue("");
                row.GetCell(10).SetCellValue("");
            }
            #endregion

            #region 参赛单位人数统计表
            sheet = sourceBook.GetSheet("参赛单位人数统计表");

            row = sheet.GetRow(0);

            var groups = sporterInfos.GroupBy(p => p.GroupName).Select(p => p.Key).ToList();

            var columnIndex = 1;
            int toHide = (row.Cells.Count - 6) / 2 - groups.Count();
            while (toHide > 0)
            {
                columnIndex++;
                sheet.SetColumnHidden(columnIndex, true);
                columnIndex++;
                sheet.SetColumnHidden(columnIndex, true);

                toHide--;
            }

            row.GetCell(0).SetCellValue(raceInfo.RaceName + "报名统计报表");

            row = sheet.GetRow(1);
            toHide = (row.Cells.Count - 6) / 2 - groups.Count();
            columnIndex = 1 + toHide * 2;
            for (int i = 0; i < groups.Count(); i++)
            {
                columnIndex++;
                row.GetCell(columnIndex).SetCellValue(groups[i]);
                columnIndex += 1;
            }

            int seq = 0;
            int count = 0;
            currentRowIndex = 2;
            foreach (var itemSchool in schools)
            {
                columnIndex = 1 + toHide * 2;
                currentRowIndex++;
                seq++;

                row = sheet.GetRow(currentRowIndex);

                if (row == null)
                {
                    row = sheet.CopyRow(3, currentRowIndex);
                }

                row.GetCell(0).SetCellValue(seq);
                row.GetCell(1).SetCellValue(itemSchool.SchoolName);

                foreach (var itemGroup in groups)
                {
                    count = sporterInfos.Where(p => p.SchoolID == itemSchool.SchoolID && p.GroupName == itemGroup && p.Sex == "男").Count();
                    columnIndex++;
                    row.GetCell(columnIndex).SetCellValue(count);

                    count = sporterInfos.Where(p => p.SchoolID == itemSchool.SchoolID && p.GroupName == itemGroup && p.Sex == "女").Count();
                    columnIndex++;
                    row.GetCell(columnIndex).SetCellValue(count);
                }

                count = sporterInfos.Where(p => p.SchoolID == itemSchool.SchoolID).Count();
                columnIndex++;
                row.GetCell(columnIndex).SetCellValue(count);

                count = statisticsInfos.Where(p => p.SchoolID == itemSchool.SchoolID).Sum(p => p.LeaderCount);
                columnIndex++;
                row.GetCell(columnIndex).SetCellValue(count);

                count = statisticsInfos.Where(p => p.SchoolID == itemSchool.SchoolID).Sum(p => p.CoachCount);
                columnIndex++;
                row.GetCell(columnIndex).SetCellValue(count);

                count = sporterInfos.Where(p => p.SchoolID == itemSchool.SchoolID).Count() + statisticsInfos.Where(p => p.SchoolID == itemSchool.SchoolID).Sum(p => p.LeaderCount + p.CoachCount);
                columnIndex++;
                row.GetCell(columnIndex).SetCellValue(count);
            }

            currentRowIndex++;

            row = sheet.GetRow(currentRowIndex);

            if (row == null)
            {
                row = sheet.CopyRow(3, currentRowIndex);
            }

            columnIndex = 1 + toHide * 2;
            seq++;
            row.GetCell(0).SetCellValue(seq);
            row.GetCell(1).SetCellValue("总计");

            foreach (var itemGroup in groups)
            {
                count = sporterInfos.Where(p => p.GroupName == itemGroup && p.Sex == "男").Count();
                columnIndex++;
                row.GetCell(columnIndex).SetCellValue(count);

                count = sporterInfos.Where(p => p.GroupName == itemGroup && p.Sex == "女").Count();
                columnIndex++;
                row.GetCell(columnIndex).SetCellValue(count);
            }

            count = sporterInfos.Count();
            columnIndex++;
            row.GetCell(columnIndex).SetCellValue(count);

            count = statisticsInfos.Sum(p => p.LeaderCount);
            columnIndex++;
            row.GetCell(columnIndex).SetCellValue(count);

            count = statisticsInfos.Sum(p => p.CoachCount);
            columnIndex++;
            row.GetCell(columnIndex).SetCellValue(count);

            count = sporterInfos.Count() + statisticsInfos.Sum(p => p.LeaderCount + p.CoachCount);
            columnIndex++;
            row.GetCell(columnIndex).SetCellValue(count);
            #endregion

            #region 联系方式
            sheet = sourceBook.GetSheet("联系方式表");
            rowsCount = contactInfos.Count + 2;

            for (int i = 0; i < rowsCount; i++)
            {
                row = sheet.GetRow(i);

                if (row == null)
                {
                    row = sheet.CreateRow(i);
                }
            }

            currentRowIndex = 1;
            foreach (var itemContact in contactInfos)
            {
                currentRowIndex++;

                if (currentRowIndex != 2)
                {
                    sheet.CopyRow(2, currentRowIndex);
                }

                row = sheet.GetRow(currentRowIndex);
                row.GetCell(0).SetCellValue(itemContact.SchoolName);
                row.GetCell(1).SetCellValue(itemContact.Leader);
                row.GetCell(2).SetCellValue(itemContact.Coach1);
                row.GetCell(3).SetCellValue(itemContact.Coach2);
                row.GetCell(4).SetCellValue(itemContact.Coach3);
                row.GetCell(5).SetCellValue(itemContact.Coach4);
                row.GetCell(6).SetCellValue(itemContact.Contact);
                row.GetCell(7).SetCellValue(itemContact.ContactPhone);
            }
            #endregion

            #region 成绩表
            var templateSheet = sourceBook.GetSheet("成绩表");
            var templateSheetIndex = sourceBook.GetSheetIndex("成绩表");
            var projects = new List<string>();

            foreach (var itemGroup in groups)
            {
                projects.Clear();
                sporterInfos.Where(p => p.GroupName == itemGroup).ToList().ForEach(p => projects.AddRange(p.Projects));
                projects = projects.Where(p => !string.IsNullOrWhiteSpace(p)).Distinct().ToList();

                foreach (var itemProject in projects)
                {
                    var desSporterInfos = sporterInfos.Where(p => p.GroupName == itemGroup && p.Projects.Contains(itemProject));
                    var desSheet = sourceBook.CloneSheet(templateSheetIndex);
                    var desSheetIndex = sourceBook.GetSheetIndex(desSheet);
                    var sheetName = itemGroup + "成绩表" + "(" + itemProject + ")";
                    sourceBook.SetSheetName(desSheetIndex, sheetName);

                    desSheet.GetRow(1).GetCell(2).SetCellValue(raceInfo.RaceName ?? string.Empty);
                    desSheet.GetRow(2).GetCell(2).SetCellValue(raceInfo.RaceTime.ToString("yyyy-MM-dd"));
                    desSheet.GetRow(2).GetCell(6).SetCellValue(raceInfo.Address);
                    desSheet.GetRow(4).GetCell(0).SetCellValue(sheetName);

                    currentRowIndex = 5;
                    int recordNo = 0;

                    foreach (var itemDesSporter in desSporterInfos)
                    {
                        recordNo++;
                        currentRowIndex++;

                        row = desSheet.GetRow(currentRowIndex);

                        if (row == null)
                        {
                            row = desSheet.CreateRow(currentRowIndex);
                        }

                        if (currentRowIndex != 6)
                        {
                            desSheet.CopyRow(6, currentRowIndex);
                        }

                        row.GetCell(0).SetCellValue(recordNo);
                        row.GetCell(1).SetCellValue(itemDesSporter.SporterName);
                        row.GetCell(2).SetCellValue(itemDesSporter.Sex);
                        row.GetCell(3).SetCellValue(string.Empty);
                        row.GetCell(4).SetCellValue(itemDesSporter.IDCard);
                        row.GetCell(5).SetCellValue(itemDesSporter.StudentNo);
                        row.GetCell(6).SetCellValue(itemDesSporter.SchoolName);
                    }
                }
            }

            sourceBook.RemoveSheetAt(templateSheetIndex);
            #endregion

            MemoryStream ms = new MemoryStream();
            sourceBook.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);

            return ms;
        }

        public class RaceInfo
        {
            public string RaceName { get; set; }
            public DateTime RaceTime { get; set; }
            public string Address { get; set; }
        }
        public class RaceContactInfo
        {
            public int SchoolID { get; set; }
            public string SchoolName { get; set; }
            public string Leader { get; set; }
            public string Coach1 { get; set; }
            public string Coach2 { get; set; }
            public string Coach3 { get; set; }
            public string Coach4 { get; set; }
            public string Contact { get; set; }
            public string ContactPhone { get; set; }
        }

        public class RaceSporterInfo
        {
            public string SchoolName { get; set; }
            public int SchoolID { get; set; }

            public int SchoolType { get; set; }

            public string GroupName { get; set; }

            public string SporterName { get; set; }
            public string IDCard { get; set; }
            public string StudentNo { get; set; }
            public string Sex { get; set; }
            public string RegNo { get; set; }
            public string Grade { get; set; }
            public List<string> Projects { get; set; }
        }

        public class RaceStatisticsInfo
        {
            public int SchoolID { get; set; }
            public string SchoolName { get; set; }
            public int SporterCount { get; set; }
            public int LeaderCount { get; set; }
            public int CoachCount { get; set; }
            public int TotalCount { get; set; }

        }
    }
}