C# ExcelWorksheet.Row方法代码示例

		private static void DrawAwb(string currentAwb, ExcelWorksheet excel, int iRow, int count)
		{
			var row = excel.Row(iRow);
			row.Height = ExcelConstants.AwbRowHeight;
			row.Style.Font.Bold = true;

			excel.Cells[iRow, 1].Value = currentAwb;

			var range = excel.Cells[iRow, 1, iRow, count];
			range.Merge = true;
			range.Style.Fill.PatternType = ExcelFillStyle.Solid;
			range.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
		}

        public static void AddDataRow(ExcelWorksheet worksheet, int row, IEnumerable<object> values)
        {
            int ca = 0;
            foreach (object v in values)
            {
                ca++;
                using (ExcelRange cell = worksheet.Cells[row, ca])
                {
                    object value = v;
                    cell.Value = value;
                }
            }

            worksheet.Row(row).Height = 10.2;
        }

        private static void PenaltiesGenerateSheetPerPeriod(ref Color pink1, ref Color pink2, ref Color pinkDark, ref Color lightBlue, ExcelWorksheet reportSheet, int period, int row, int doubleColumn)
        {
            try
            {
                reportSheet.Row(row).Height = 30;
                reportSheet.Cells[row, 1 + doubleColumn, row + 1, 8 + doubleColumn].SetValue("Home Team").Merge().SetBackgroundColor(pink1).SetFontSize(14).SetCenterAlign();

                reportSheet.Cells[row, 9 + doubleColumn, row, 10 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 11 + doubleColumn].SetBackgroundColor(pink1).SetBorderBottom(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 12 + doubleColumn, row, 13 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 14 + doubleColumn, row + 1, 21 + doubleColumn].SetValue("Away Team").Merge().SetBackgroundColor(pink1).SetFontSize(14).SetCenterAlign();
                reportSheet.Cells[row, 22 + doubleColumn, row, 23 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 24 + doubleColumn].SetFontSize(24).SetCenterAlign().SetValue(period.ToString()).SetFontBold().SetBackgroundColor(pinkDark).SetFontColor(Color.White);

                row += 1; //2
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 9 + doubleColumn, row, 10 + doubleColumn].Merge().SetValue("Color").SetCenterAlign();
                reportSheet.Cells[row, 11 + doubleColumn].SetBackgroundColor(pink1).SetValue("Date").SetCenterAlign();
                reportSheet.Cells[row, 12 + doubleColumn, row, 13 + doubleColumn].Merge().SetValue("Penalty Tracker").SetCenterAlign();
                reportSheet.Cells[row, 22 + doubleColumn, row, 23 + doubleColumn].Merge().SetValue("Color").SetCenterAlign();
                reportSheet.Cells[row, 24 + doubleColumn].SetBackgroundColor(pinkDark);


                row += 1;//3
                reportSheet.Row(row).Height = 15;
                PenaltiesRowHeaderOfSheet(reportSheet, row, doubleColumn);

                row += 1; //4
                DisplayPenaltyCodes(reportSheet, row, doubleColumn);

                int modulateRowColor = 1;
                int notesRow = 1;
                for (int i = row; i < row + 40; i += 2)
                {
                    reportSheet.Row(i).Height = 16.50;
                    reportSheet.Row(i + 1).Height = 16.50;
                    //we need to set the row colors first because they are overwritten by other colors after
                    // the cells.
                    //full row of home or away secition
                    if (modulateRowColor % 2 == 0)
                    {
                        reportSheet.Cells[i, 2 + doubleColumn, i + 1, 10 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
                        reportSheet.Cells[i, 1 + doubleColumn, i + 1, 1 + doubleColumn].Merge().SetBackgroundColor(pink1).SetBorder(ExcelBorderStyle.Medium);
                    }
                    else
                    {
                        reportSheet.Cells[i, 2 + doubleColumn, i + 1, 10 + doubleColumn].SetBorder(ExcelBorderStyle.Medium).SetBackgroundColor(pink1);
                        reportSheet.Cells[i, 1 + doubleColumn, i + 1, 1 + doubleColumn].Merge().SetBackgroundColor(pink2).SetBorder(ExcelBorderStyle.Medium);
                    }
                    //full row of home or away secition
                    if (modulateRowColor % 2 == 0)
                    {
                        reportSheet.Cells[i, 14 + doubleColumn, i + 1, 23 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
                        reportSheet.Cells[i, 14 + doubleColumn, i + 1, 14 + doubleColumn].Merge().SetBackgroundColor(pink1).SetBorder(ExcelBorderStyle.Medium);
                    }
                    else
                    {
                        reportSheet.Cells[i, 14 + doubleColumn, i + 1, 23 + doubleColumn].SetBorder(ExcelBorderStyle.Medium).SetBackgroundColor(pink1);
                        reportSheet.Cells[i, 14 + doubleColumn, i + 1, 14 + doubleColumn].Merge().SetBackgroundColor(pink2).SetBorder(ExcelBorderStyle.Medium);
                    }



                    //displays the penalty Jam Row
                    for (int j = 2; j < 9; j++)
                    {
                        reportSheet.Cells[i, j + doubleColumn].SetBorder(ExcelBorderStyle.Thin);
                        reportSheet.Cells[i + 1, j + doubleColumn].SetBorder(ExcelBorderStyle.Thin);
                    }
                    //Fo/EXP column
                    reportSheet.Cells[i, 9 + doubleColumn].SetBorder(ExcelBorderStyle.Dotted).SetBackgroundColor(pink2);
                    reportSheet.Cells[i + 1, 9 + doubleColumn].SetBorder(ExcelBorderStyle.Dotted).SetBackgroundColor(pink2);
                    reportSheet.Cells[i, 9 + doubleColumn, i + 1, 9 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
                    //total column
                    reportSheet.Cells[i, 10 + doubleColumn, i + 1, 10 + doubleColumn].Merge().SetBorder(ExcelBorderStyle.Medium);


                    //row color sets background color
                    reportSheet.Cells[i, 11 + doubleColumn, i, 13 + doubleColumn].Merge().SetValue((notesRow).ToString()).SetCenterAlign();
                    reportSheet.Cells[i + 1, 11 + doubleColumn, i + 1, 13 + doubleColumn].Merge().SetValue((notesRow + 1).ToString()).SetBackgroundColor(lightBlue).SetCenterAlign();


                    //displays the penalty Jam Row
                    for (int j = 15; j < 22; j++)
                    {
                        reportSheet.Cells[i, j + doubleColumn].SetBorder(ExcelBorderStyle.Thin);
                        reportSheet.Cells[i + 1, j + doubleColumn].SetBorder(ExcelBorderStyle.Thin);
                    }

                    reportSheet.Cells[i, 22 + doubleColumn].SetBorder(ExcelBorderStyle.Dotted).SetBackgroundColor(pink2);
                    reportSheet.Cells[i + 1, 22 + doubleColumn].SetBorder(ExcelBorderStyle.Dotted).SetBackgroundColor(pink2);
                    //fo/exp column away team
                    reportSheet.Cells[i, 22 + doubleColumn, i + 1, 22 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
                    reportSheet.Cells[i, 23 + doubleColumn, i + 1, 23 + doubleColumn].Merge().SetBorder(ExcelBorderStyle.Medium);

                    reportSheet.Cells[i, 2 + doubleColumn, i + 1, 10 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
                    notesRow += 2;
                    modulateRowColor += 1;
                }

//.........这里部分代码省略.........

 private static void LineupsBottomMessages(ExcelWorksheet reportSheet, int row, int doubleColumn)
 {
     try
     {
         reportSheet.Row(row).Height = 12;
         reportSheet.Cells[row, 1 + doubleColumn, row + 4, 19 + doubleColumn].SetBorder(ExcelBorderStyle.Medium);
         reportSheet.Cells[row, 1 + doubleColumn, row, 19 + doubleColumn].Merge().SetFontSize(8).SetValue("Write the jam number, starting from 1 each period, in the JAM column as each jam happens. Enter skater numbers by position. If no Pivot is fielded, enter an X in the noPivot box.");
         row += 1;
         reportSheet.Row(row).Height = 12;
         reportSheet.Cells[row, 1 + doubleColumn, row, 19 + doubleColumn].Merge().SetFontSize(8).SetValue("During the jam, circle the opposing jammer's current pass. When a skater sits in the penalty box, write that pass number in the left BOX column, or an S if the skater starts the jam in the box.");
         row += 1;
         reportSheet.Row(row).Height = 12;
         reportSheet.Cells[row, 1 + doubleColumn, row, 19 + doubleColumn].Merge().SetFontSize(8).SetValue("When the skater reenters the track from the box, write the current pass in the right column. If jam is called for injury, indicate it with an INJ in the BOX column of the injured skater.");
         row += 1;
         reportSheet.Row(row).Height = 12;
         reportSheet.Cells[row, 1 + doubleColumn, row, 19 + doubleColumn].Merge().SetFontSize(8).SetValue("When a star pass occurs, enter SP as in the JAM column on a new row, with jammer and pivot reversed (mark noPivot), same blockers, and maintain current pass count.");
         row += 1;
         reportSheet.Row(row).Height = 12;
         reportSheet.Cells[row, 1 + doubleColumn, row, 19 + doubleColumn].Merge().SetFontSize(8).SetValue("Note any box time carrying over from first period onto the period 2 sheet before turning in period 1 sheet.");
     }
     catch (Exception exception)
     {
         ErrorViewModel.Save(exception, exception.GetType());
     }
 }

        private static void LineupsGenerateSheetPerPeriodPerTeam(Color blue1, Color blue2, Color blueDark, Color offPink, ExcelWorksheet reportSheet, int period, int row)
        {
            try
            {
                reportSheet.Row(row).Height = 30;
                int doubleColumn = 0;
                reportSheet.Cells[row, 1 + doubleColumn, row + 1, 8 + doubleColumn].SetValue("Home Team").Merge().SetBackgroundColor(blue1).SetFontSize(14).SetCenterAlign();
                //line up
                reportSheet.Cells[row, 9 + doubleColumn, row, 14 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 15 + doubleColumn, row, 17 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin).SetBackgroundColor(blue1);
                reportSheet.Cells[row, 18 + doubleColumn].SetBackgroundColor(blueDark).SetValue(period.ToString()).SetFontSize(24).SetCenterAlign().SetFontBold().SetFontColor(Color.White);
                reportSheet.Cells[row, 21 + doubleColumn, row, 22 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);

                reportSheet.Cells[row, 23 + doubleColumn, row + 1, 30 + doubleColumn].SetValue("Away Team").Merge().SetBackgroundColor(blue1).SetFontSize(14).SetCenterAlign();
                //line up
                reportSheet.Cells[row, 31 + doubleColumn, row, 36 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
                //date
                reportSheet.Cells[row, 37 + doubleColumn, row, 39 + doubleColumn].Merge().SetBackgroundColor(blue1).SetBorderBottom(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 40 + doubleColumn].SetBackgroundColor(blueDark).SetValue(period.ToString()).SetFontSize(24).SetFontBold().SetCenterAlign().SetFontColor(Color.White);
                reportSheet.Cells[row, 43 + doubleColumn, row, 44 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);

                row += 1; //2
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 9 + doubleColumn, row, 14 + doubleColumn].Merge().SetValue("Lineup Tracker").SetCenterAlign();
                reportSheet.Cells[row, 15 + doubleColumn, row, 17 + doubleColumn].SetBackgroundColor(blue1).SetValue("Date").SetCenterAlign().Merge();
                reportSheet.Cells[row, 18 + doubleColumn].SetBackgroundColor(blueDark);
                reportSheet.Cells[row, 21 + doubleColumn, row, 22 + doubleColumn].Merge().SetValue("Color").SetCenterAlign();
                reportSheet.Cells[row, 31 + doubleColumn, row, 36 + doubleColumn].Merge().SetValue("Lineup Tracker").SetCenterAlign();
                reportSheet.Cells[row, 37 + doubleColumn, row, 39 + doubleColumn].Merge().SetBackgroundColor(blue1).SetValue("Date").SetCenterAlign();
                reportSheet.Cells[row, 40 + doubleColumn].SetBackgroundColor(blueDark);
                reportSheet.Cells[row, 43 + doubleColumn, row, 44 + doubleColumn].Merge().SetValue("Color").SetCenterAlign();

                int secondSheetStart = 22;

                row += 1; //3
                reportSheet.Row(row).Height = 15;
                LineupsSetHeaderRow(reportSheet, row, doubleColumn);
                LineupsSetHeaderRow(reportSheet, row, secondSheetStart);

                row += 1;
                int modulation = 1;
                for (int i = row; i < row + 76; i += 2)
                {
                    reportSheet.Row(i).Height = 14.25;
                    reportSheet.Row(i + 1).Height = 14.25;
                    LineupsDisplayRow(blue1, blue2, offPink, reportSheet, i, doubleColumn, modulation % 2 == 0);
                    LineupsDisplayRow(blue1, blue2, offPink, reportSheet, i, secondSheetStart, modulation % 2 == 0);
                    modulation += 1;
                }
                row += 75;

                row += 1;//80
                LineupsBottomMessages(reportSheet, row, doubleColumn);
                LineupsBottomMessages(reportSheet, row, secondSheetStart);
            }
            catch (Exception exception)
            {
                ErrorViewModel.Save(exception, exception.GetType());
            }
        }

		private static int DrawInfo(ExcelWorksheet ws, CalculationAwbInfo info, int iRow, int count)
		{
			var range = ws.Cells[iRow, 1, iRow, count];
			range.Merge = true;
			range.Style.Fill.PatternType = ExcelFillStyle.Solid;
			range.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
			ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
			iRow++;

			ws.Cells[iRow, 1].Value = "sender";
			ws.Cells[iRow, 1].Style.Font.Bold = true;
			ws.Cells[iRow, 8].Value = info.CostPerKgOfSender;
			ws.Cells[iRow, 9].Style.Font.Bold = true;
			ws.Cells[iRow, 9].Value = info.TotalSenderRate;
			ws.Cells[iRow, 14].Value = info.TotalScotchCost;
			ws.Cells[iRow, 16].Value = info.TotalFactureCost;
			ws.Cells[iRow, 17].Value = info.TotalFactureCostEx;
			ws.Cells[iRow, 18].Value = info.TotalPickupCost;
			ws.Cells[iRow, 20].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Value = info.TotalOfSender;
			ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
			iRow++;

			ws.Cells[iRow, 1].Value = "flight";
			ws.Cells[iRow, 1].Style.Font.Bold = true;
			ws.Cells[iRow, 9].Style.Font.Bold = true;
			ws.Cells[iRow, 9].Value = info.FlightCostPerKg;
			ws.Cells[iRow, 20].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Value = info.FlightCost;
			ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
			iRow++;

			ws.Cells[iRow, 1].Value = "custom";
			ws.Cells[iRow, 1].Style.Font.Bold = true;
			ws.Cells[iRow, 9].Style.Font.Bold = true;
			ws.Cells[iRow, 9].Value = info.CustomCostPerKg;
			ws.Cells[iRow, 20].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Value = info.CustomCost;
			ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
			iRow++;

			ws.Cells[iRow, 1].Value = "broker";
			ws.Cells[iRow, 1].Style.Font.Bold = true;
			ws.Cells[iRow, 9].Style.Font.Bold = true;
			ws.Cells[iRow, 9].Value = info.BrokerCostPerKg;
			ws.Cells[iRow, 20].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Value = info.BrokerCost;
			ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
			iRow++;

			ws.Cells[iRow, 1].Value = "insurance";
			ws.Cells[iRow, 1].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Value = info.TotalInsuranceCost;
			ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
			iRow++;

			ws.Cells[iRow, 1].Value = "forwarder";
			ws.Cells[iRow, 1].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Value = info.TotalTransitCost;
			ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
			iRow++;

			ws.Cells[iRow, 1].Value = "additional";
			ws.Cells[iRow, 1].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Value = info.AdditionalCost;
			ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
			iRow++;

			ws.Cells[iRow, 1].Value = "cost total";
			ws.Cells[iRow, 1].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Value = info.TotalExpenses;
			var rangeCost = ws.Cells[iRow, 1, iRow, count];
			rangeCost.Style.Fill.PatternType = ExcelFillStyle.Solid;
			rangeCost.Style.Fill.BackgroundColor.SetColor(Color.HotPink);
			ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
			iRow++;

			ws.Cells[iRow, 19].Value = info.ProfitPerKg;
			if (info.ProfitPerKg <= 0)
			{
				ws.Cells[iRow, 19].Style.Font.Color.SetColor(Color.Red);
			}
			ws.Cells[iRow, 20].Style.Font.Bold = true;
			ws.Cells[iRow, 20].Value = info.Profit;
			if (info.Profit <= 0)
			{
				ws.Cells[iRow, 20].Style.Font.Color.SetColor(Color.Red);
			}
			ws.Row(iRow).Height = ExcelConstants.DefaultRowHeight;
			iRow++;

			return iRow;
		}

		private static void HeaderStyles(ExcelWorksheet ws, ExcelRange cell)
		{
			cell.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
			cell.Style.Font.Bold = true;
			cell.Style.Locked = true;
			ws.View.FreezePanes(2, 1);
			ws.Row(1).Height = ExcelConstants.DefaultRowHeight;
		}

        private static int IBRFCreateSectionTwo(ExcelWorksheet reportSheet)
        {
            try
            {
                int row = 36;
                reportSheet.Row(row).Height = 12;
                reportSheet.Cells[row, 1, row, 12].SetValue("Section 2. SCORE (Complete DURING or IMMEDIATELY AFTER bout)").SetTitleFont().SetBorder(ExcelBorderStyle.Thin);

                row += 1;
                reportSheet.Row(row).Height = 12.75;
                reportSheet.Cells[row, 1, row, 6].SetValue("HOME TEAM").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 7, row, 12].SetValue("VISITING TEAM").SetHeader1().SetBorder(ExcelBorderStyle.Thin);

                row += 1;//38
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 1].SetValue("Period 1").SetHeader2().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 2].SetValue("Points").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 3].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 4].SetValue("Penalties").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 5, row, 6].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 7].SetValue("Period 1").SetHeader2().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 8].SetValue("Points").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 9].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 10].SetValue("Penalties").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 11, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);

                row += 1;//39
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 1].SetValue("Period 2").SetHeader2().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 2].SetValue("Points").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 3].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 4].SetValue("Penalties").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 5, row, 6].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 7].SetValue("Period 2").SetHeader2().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 8].SetValue("Points").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 9].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 10].SetValue("Penalties").SetHeader3().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 11, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);

                row += 1; //40  
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 1, row, 2].SetValue("TOTAL POINTS:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 3].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 4].SetValue("PENALTIES:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 5, row, 6].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 7, row, 8].SetValue("TOTAL POINTS:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 9].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 10].SetValue("PENALTIES:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 11, row, 12].SetLavenderSpace().SetBorder(ExcelBorderStyle.Thin);

                row += 1;//41
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 1, row, 3].SetValue("Expulsion/suspension notes:").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 4, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);

                row += 1;//42
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 1, row, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
                return row;
            }
            catch (Exception exception)
            {
                ErrorViewModel.Save(exception, exception.GetType());
            }
            return 0;
        }

        private static int IBRFCreateSectionFourNonSkatingOfficials(ExcelWorksheet reportSheet)
        {
            try
            {
                int row = 54;
                reportSheet.Row(row).Height = 18;
                reportSheet.Cells[row, 1, row, 12].SetValue("LIST OF NON-SKATING OFFICIALS/STAT TRACKERS").SetTitleFont().SetBorder(ExcelBorderStyle.Thin);

                row += 1; //55
                reportSheet.Row(row).Height = 12.75;
                reportSheet.Cells[row, 1, row, 3].SetValue("Official/Tracker's Name").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 4, row, 7].SetValue("Non-Skating Official Position").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 8, row, 9].SetValue("League").SetHeader1().SetBorder(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 10, row, 12].SetValue("Certification").SetHeader1().SetBorder(ExcelBorderStyle.Thin);

                row += 1; //56
                for (int i = row; i < row + 21; i++)
                {
                    reportSheet.Row(i).Height = 12.75;
                    reportSheet.Cells[i, 1, i, 3].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
                    reportSheet.Cells[i, 4, i, 7].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
                    reportSheet.Cells[i, 8, i, 9].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
                    reportSheet.Cells[i, 10, i, 12].SetWhiteSpace().SetBorder(ExcelBorderStyle.Thin);
                }

                row = 76;

                row += 1; //77
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 1, row, 12].SetValue(ScannedMessage).SetWhiteSpace().SetCenterAlign();

                row += 1; //78
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 1, row, 12].SetValue(RevMessage).SetWhiteSpace().SetCenterAlign();

                return row;
            }
            catch (Exception exception)
            {
                ErrorViewModel.Save(exception, exception.GetType());
            }
            return 0;
        }

 public static ExcelRow getRow(int iRowIndex, ExcelWorksheet oExcelWorksheet)
 {
     var oExcelRow = oExcelWorksheet.Row(iRowIndex);
     return oExcelRow;
 }

 void SetStyling(ExcelWorksheet ws)
 {
     ws.Cells.Style.Font.Size = 11.0f;
     ws.Row(1).Style.Font.Bold = true;
     ws.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
     ws.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
 }

 /// <summary>
 ///     添加表头
 /// </summary>
 /// <param name="worksheet">工作簿</param>
 /// <param name="title">标题</param>
 private static void AddTitle(ExcelWorksheet worksheet, string title)
 {
     worksheet.Cells["B2:F2"].Merge = true;
     worksheet.Cells["B2"].Value = title;
     worksheet.Cells["B2"].Style.Font.Bold = true;
     worksheet.Cells["B2"].Style.Font.Name = "宋体";
     worksheet.Cells["B2"].Style.Font.Size = 14;
     worksheet.Row(2).Height = 19;
     worksheet.Cells["B2"].Style.Indent = 8;
     worksheet.Cells["B2"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
 }

        /// <summary>
        ///     添加时间
        /// </summary>
        /// <param name="worksheet">工作簿</param>
        /// <param name="info">信息</param>
        private static void AddExtInfo(ExcelWorksheet worksheet, string info)
        {
            if (string.IsNullOrWhiteSpace(info))
            {
                info = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }

            //自适应高度
            var result = info.Split(new[] {"\r\n"}, StringSplitOptions.None);
            var count = result.Count();
            worksheet.Cells["B3"].Value = info;
            worksheet.Cells["B3"].Style.Font.Bold = true;
            worksheet.Cells["B3"].Style.Font.Name = "宋体";
            worksheet.Cells["B3"].Style.Font.Size = 10;
            worksheet.Cells["B3"].Style.Indent = 8;
            worksheet.Cells["B3"].Style.WrapText = true;
            worksheet.Row(3).Height = worksheet.Row(3).Height*count;
            worksheet.Cells["B3"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            worksheet.Cells["B3:F3"].Merge = true;
        }

        private static void BoutClockPage(Color lightGray, Color darkGray, Color aqua, Color purple, Color offPink, ExcelWorksheet reportSheet, ref int row, int doubleColumn, int teamNumber)
        {
            try
            {
                reportSheet.Row(row).Height = 30;
                reportSheet.Cells[row, 1 + doubleColumn].SetValue("Home:").SetRightAlignment();
                reportSheet.Cells[row, 2 + doubleColumn, row, 4 + doubleColumn].SetBackgroundColor(lightGray).Merge().SetFontSize(14).SetValue("Home Team").SetCenterAlign();

                reportSheet.Cells[row, 5 + doubleColumn, 1 + row, 7 + doubleColumn].Merge().SetBorderBottom(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 8 + doubleColumn, 1 + row, 8 + doubleColumn].Merge().SetBackgroundColor(lightGray).SetBorderBottom(ExcelBorderStyle.Thin);
                reportSheet.Cells[row, 9 + doubleColumn, 1 + row, 9 + doubleColumn].Merge().SetBackgroundColor(darkGray).SetFontSize(24).SetCenterAlign().SetFontColor(Color.White).SetFontBold().SetValue(teamNumber.ToString());

                row += 1;
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 1 + doubleColumn, 1 + row, 1 + doubleColumn].SetValue("Away:").Merge().SetRightAlignment();
                reportSheet.Cells[row, 2 + doubleColumn, 1 + row, 4 + doubleColumn].SetBackgroundColor(lightGray).Merge().SetFontSize(14).SetValue("Away Team").SetCenterAlign();

                row += 1; //3
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 5 + doubleColumn, row, 7 + doubleColumn].Merge().SetValue("Period Timer").SetCenterAlign();
                reportSheet.Cells[row, 8 + doubleColumn].SetBackgroundColor(lightGray).SetCenterAlign().SetValue("Date");
                reportSheet.Cells[row, 9 + doubleColumn].SetBackgroundColor(darkGray);

                row += 1;//4
                reportSheet.Row(row).Height = 15;
                reportSheet.Cells[row, 1 + doubleColumn].SetBackgroundColor(Color.Black);
                reportSheet.Cells[row, 2 + doubleColumn, row, 3 + doubleColumn].SetValue("NAME").Merge().SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
                reportSheet.Cells[row, 4 + doubleColumn, row, 6 + doubleColumn].SetValue("TIMEOUTS TAKEN").SetCenterAlign().Merge().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
                reportSheet.Cells[row, 7 + doubleColumn].SetValue("REVIEW").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
                reportSheet.Cells[row, 8 + doubleColumn, row, 9 + doubleColumn].SetValue("TEAM").SetCenterAlign().Merge().SetBackgroundColor(Color.Black).SetFontColor(Color.White);

                row += 1;//5
                reportSheet.Row(row).Height = 21;
                reportSheet.Row(row + 1).Height = 21;
                BoutClockCaptainsLine("Home", reportSheet, row, doubleColumn);
                row += 2;
                reportSheet.Row(row).Height = 21;
                reportSheet.Row(row + 1).Height = 21;
                BoutClockCaptainsLine("Away", reportSheet, row, doubleColumn);
                row += 2;
                reportSheet.Row(row + 1).Height = 12.75;
                reportSheet.Cells[row, 1 + doubleColumn, row, 9 + doubleColumn].SetValue("Identify captains and alternates before game. Indicate time on period clock when TO or OR used.").Merge().SetCenterAlign();

                row += 1;
                reportSheet.Row(row + 1).Height = 15;
                reportSheet.Cells[row, 1 + doubleColumn].SetValue("JAM").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
                reportSheet.Cells[row, 2 + doubleColumn].SetValue("JAM TIME").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
                reportSheet.Cells[row, 3 + doubleColumn].SetValue("PACK LAPS").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
                reportSheet.Cells[row, 4 + doubleColumn].SetValue("EVENT").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);

                reportSheet.Cells[row, 5 + doubleColumn, row, 7 + doubleColumn].SetValue("TEAM/SKATER").SetCenterAlign().Merge().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
                reportSheet.Cells[row, 8 + doubleColumn].SetValue("Laps/Min").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);
                reportSheet.Cells[row, 9 + doubleColumn].SetValue("Pts/Min").SetCenterAlign().SetBackgroundColor(Color.Black).SetFontColor(Color.White);

                row += 1;
                for (int i = row; i < row + 38; i++)
                {
                    reportSheet.Row(i).Height = 21;
                    BoutClockRow(aqua, purple, offPink, reportSheet, i, doubleColumn);
                }
                row += 38;
                reportSheet.Row(row + 1).Height = 12.75;
                reportSheet.Cells[row, 1 + doubleColumn].SetValue("Team EVENTs: ");
                reportSheet.Cells[row, 3 + doubleColumn].SetValue("Record TO for team timeout or OR for official review, and indicate the team.");
                row += 1;
                reportSheet.Row(row + 1).Height = 12.75;
                reportSheet.Cells[row, 1 + doubleColumn].SetValue("Skater EVENTs:   ");
                reportSheet.Cells[row, 3 + doubleColumn].SetValue("Record INJ for jam-ending injury or EX for expulsion, indicate skater color & number.");
                row += 1;
                reportSheet.Row(row + 1).Height = 13.50;
                reportSheet.Cells[row, 1 + doubleColumn].SetValue("Other EVENTs:   ");
                reportSheet.Cells[row, 3 + doubleColumn].SetValue("Record OFF for any other official timeout. ");
            }
            catch (Exception exception)
            {
                ErrorViewModel.Save(exception, exception.GetType());
            }
        }

        private XmlElement GetOrCreateCellElement(ExcelWorksheet xlWorksheet, int row, int col)
        {
            XmlElement cellNode = null;
            // this will create the row if it does not already exist
            XmlNode rowNode = xlWorksheet.Row(row).Node;
            if (rowNode != null)
            {
                cellNode = (XmlElement) rowNode.SelectSingleNode(string.Format("./d:c[@" + ExcelWorksheet.tempColumnNumberTag + "='{0}']", col), _xlWorksheet.NameSpaceManager);
                if (cellNode == null)
                {
                    //  Didn't find the cell so create the cell element
                    cellNode = xlWorksheet.WorksheetXml.CreateElement("c", ExcelPackage.schemaMain);
                    cellNode.SetAttribute(ExcelWorksheet.tempColumnNumberTag, col.ToString());

                    // You must insert the new cell at the correct location.
                    // Loop through the children, looking for the first cell that is
                    // beyond the cell you're trying to insert. Insert before that cell.
                    XmlNode biggerNode = null;
                    XmlNodeList cellNodes = rowNode.SelectNodes("./d:c", _xlWorksheet.NameSpaceManager);
                    if (cellNodes != null)
                    {
                        foreach (XmlNode node in cellNodes)
                        {
                            XmlNode colNode = node.Attributes[ExcelWorksheet.tempColumnNumberTag];
                            if (colNode != null)
                            {
                                int colFound = Convert.ToInt32(colNode.Value);
                                if (colFound > col)
                                {
                                    biggerNode = node;
                                    break;
                                }
                            }
                        }
                    }
                    if (biggerNode == null)
                    {
                        rowNode.AppendChild(cellNode);
                    }
                    else
                    {
                        rowNode.InsertBefore(cellNode, biggerNode);
                    }
                }
            }
            return (cellNode);
        }