C# IXLWorksheet.Row方法代码示例

        private static void CopyRowAsRange(IXLWorksheet originalSheet, int originalRowNumber, IXLWorksheet destSheet, int destRowNumber)
        {
            {
                var destinationRow = destSheet.Row(destRowNumber);
                destinationRow.Clear();

                var originalRow = originalSheet.Row(originalRowNumber);
                int columnNumber = originalRow.LastCellUsed(true).Address.ColumnNumber;

                var originalRange = originalSheet.Range(originalRowNumber, 1, originalRowNumber, columnNumber);
                var destRange = destSheet.Range(destRowNumber, 1, destRowNumber, columnNumber);
                originalRange.CopyTo(destRange);
            }
        }

        private static void SetRows(IXLWorksheet ws)
        {
            ws.Cell(10, 1).Value = Model.NameOfMaterial;
            ws.Cell(20, 43).Value = Model.NameOfMaterial;
            var rowCount = Model.ResultTemperaturesModels.Count;
            if (rowCount > 1)
            {
                ws.Row(20).InsertRowsBelow(rowCount - 1);
                ws.Range(20, 43, 19 + rowCount, 44).Merge();

                ws.Row(10).InsertRowsBelow(rowCount - 1);
                ws.Range(10, 1, 9 + rowCount, 1).Merge();
            }
        }

 public int GetColumnCount(string sheetName, int rowNumber)
 {
     worksheet = workbook.Worksheet(sheetName);
     return worksheet.Row(rowNumber).CellsUsed().Count();
 }

        private int GetColumnNumber(String sheetName, String colName)
        {
            worksheet = workbook.Worksheet(sheetName);
            IXLRow r =  worksheet.Row(1);
            while (r != worksheet.LastRowUsed())
            {

                foreach(IXLCell c in r.CellsUsed())
                {
                    if (c.Value.Equals(colName))
                        return c.Address.ColumnNumber; // Starts with 0
                }

                r = r.RowBelow();
            }
            return -1;
        }

        public void AddContainerInfo(IXLWorksheet ws, Container container)
        {
            ws.Cell("A1").SetValue(container.ExporterName).Style.Font.FontSize = 20;
            ws.Range("A1:E1").Merge();

            ws.Cell("A2").SetValue(container.ExporterAddress).Style.Alignment.WrapText = true;
            ws.Range("A2:B3").Merge();

            ws.Cell("A5").SetValue("Shipped Per");
            ws.Cell("B5").SetValue(container.ShippedPer);
            ws.Range("B5:C5").Merge();

            ws.Cell("A6").SetValue("On/About");
            ws.Cell("B6").SetValue(container.OnAbout);
            ws.Range("B6:C6").Merge();

            ws.Cell("A7").SetValue("From");
            ws.Cell("B7").SetValue(container.From);
               // ws.Row(7)
              //  .Style
             //   .Alignment.SetVertical(XLAlignmentVerticalValues.Top)
             //   .Alignment.SetWrapText(true);
            ws.Range("B7:C7").Merge();
            ws.Row(7).Height = 70;

            ws.Cell("A8").SetValue("Airway Bill No. \nor B/L No.");
            ws.Cell("B8").SetValue(container.AirwayBillNumber);
            ws.Range("B8:C8").Merge();

            ws.Row(8).Height = 30;
               // ws.Row(8).Style.Alignment.SetWrapText(true);

            ws.Cell("A9").SetValue("Letter of\nCredit No.");
            ws.Cell("B9").SetValue(container.LetterOfCreditNumber);
            ws.Range("B9:C9").Merge();
            ws.Row(9).Height = 30;
            ws.Cell("A10").SetValue("Drawn Under");
            ws.Cell("B10").SetValue(container.DrawnUnder)
                .Style
                    .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
               //         .Alignment.SetWrapText(true);
            ws.Range("B10:C10").Merge();
            ws.Row(10).Height = 70;
             //   ws.Row(10).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);

            ws.Range("A1:A10").Style.Font.Bold = true;
            ws.Range("B5:C10").Style.Border.BottomBorder = XLBorderStyleValues.Thin;
            //    ws.Range("B5:C10").Style.Alignment.WrapText = true;

            ws.Rows("5:10").Style.Alignment.SetWrapText(true)
                .Alignment.SetVertical(XLAlignmentVerticalValues.Top);

            //Importer
            ws.Cell("E5").SetValue(container.ImporterName +
                                        "\n" + container.ImporterAddress +
                                        "\n" + "(TAX CERTIFICATE NO. " + container.ImporterTaxCertificateNumber + ")")
                                        .Style
                                            .Alignment.SetVertical(XLAlignmentVerticalValues.Top)
                                            .Alignment.SetWrapText();

            ws.Range("E5:H10").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Medium;

            //Container Number + Container Date
            ws.Cell("F2").SetValue("INVOICE NO:");
            ws.Cell("G2").SetValue(container.CustomsInvoiceNumber);

            ws.Cell("F3").SetValue("DATE:");
            ws.Cell("G3").SetValue(container.Date);

            ws.Range("F2:F3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
            ws.Range("G2:H2").Merge();
            ws.Range("G3:H3").Merge();
            ws.Range("G2:G3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
            ws.Range("F2:H3").Style.Font.Bold = true;
        }

 private void AddPerformanceTitles(IXLWorksheet performanceSheet)
 {
     //global styles
     performanceSheet.Style.Font.FontName = "verdana";
     performanceSheet.Style.Font.FontSize = 10d;
     //headings
     performanceSheet.Cell(1, 1).Value = "Date";
     performanceSheet.Cell(1, 2).Value = "Comment";
     performanceSheet.Cell(1, 3).Value = "DAU";
     performanceSheet.Cell(1, 4).Value = "Q Users";
     performanceSheet.Cell(1, 5).Value = "% Users";
     performanceSheet.Cell(1, 6).Value = "MAU";
     performanceSheet.Cell(1, 7).Value = "Q users";
     performanceSheet.Cell(1, 8).Value = "% users";
     performanceSheet.Cell(1, 9).Value = "DAU/MAU";
     performanceSheet.Cell(1, 10).Value = "DAU INDEX";
     performanceSheet.Cell(1, 11).Value = "Instalaciones";
     performanceSheet.Cell(1, 12).Value = "K Factor (overall)";
     performanceSheet.Cell(1, 13).Value = "K Factor invite";
     performanceSheet.Cell(1, 14).Value = "K Notification";
     performanceSheet.Cell(1, 15).Value = "Viral Installs";
     performanceSheet.Cell(1, 16).Value = "MKT Installs";
     performanceSheet.Cell(1, 17).Value = "Visits";
     performanceSheet.Cell(1, 18).Value = "Daily Sessions";
     performanceSheet.Cell(1, 19).Value = "Time on site";
     performanceSheet.Cell(1, 20).Value = "Daily Revenue";
     performanceSheet.Cell(1, 21).Value = "ARP DAU";
     performanceSheet.Cell(1, 22).Value = "ARP MAU";
     performanceSheet.Cell(1, 23).Value = "ARP PU";
     performanceSheet.Cell(1, 24).Value = "Pay Users";
     performanceSheet.Cell(1, 25).Value = "% Pay. Users";
     performanceSheet.Cell(1, 26).Value = "LTV";
     performanceSheet.Cell(1, 27).Value = "CPA";
     performanceSheet.Cell(1, 28).Value = "LVL 1";
     performanceSheet.Cell(1, 29).Value = "%DAU";
     performanceSheet.Cell(1, 30).Value = "LVL 2";
     performanceSheet.Cell(1, 31).Value = "%DAU";
     performanceSheet.Cell(1, 32).Value = "% Pasaje";
     performanceSheet.Cell(1, 33).Value = "LVL 3";
     performanceSheet.Cell(1, 34).Value = "%DAU";
     performanceSheet.Cell(1, 35).Value = "% Pasaje";
     performanceSheet.Cell(1, 36).Value = "LVL 4";
     performanceSheet.Cell(1, 37).Value = "%DAU";
     performanceSheet.Cell(1, 38).Value = "% Pasaje";
     performanceSheet.Cell(1, 39).Value = "LVL5";
     performanceSheet.Cell(1, 40).Value = "%DAU";
     performanceSheet.Cell(1, 41).Value = "% Pasaje";
     performanceSheet.Cell(1, 42).Value = "Weekly Average";
     performanceSheet.Cell(1, 43).Value = "Monthly Average";
     //row one styles
     performanceSheet.Row(1).Style.Font.Bold = true;
 }

        private void AddFacebookInsight(IXLWorksheet performanceSheet, int n, FacebookInsights insight)
        {
            performanceSheet.Cell(n, 1).Value = insight.Date; //Date
            performanceSheet.Cell(n, 3).Value = insight.DailyActiveUsers; //DAU
            performanceSheet.Cell(n, 4).FormulaA1 = string.Format("C{0}-C{1}", n, n + 1); //Q Users
            performanceSheet.Cell(n, 5).FormulaA1 = string.Format("IF(C{0}=0,0, D{1}/C{2})", n + 1, n, n + 1); //% Users
            performanceSheet.Cell(n, 6).Value = insight.MonthlyActiveUsers; //MAU
            performanceSheet.Cell(n, 7).FormulaA1 = string.Format("F{0}-F{1}", n, n + 1); //Q users
            performanceSheet.Cell(n, 8).FormulaA1 = string.Format("IF(F{0}=0,0,G{1}/F{2})", n + 1, n, n + 1); //% Users
            performanceSheet.Cell(n, 9).FormulaA1 = string.Format("IF(F{0}=0,0,C{1}/F{2})", n, n, n); //DAU/MAU
            performanceSheet.Cell(n, 10).FormulaA1 = string.Format("IF(G{0}=0,0,D{1}/G{2})", n, n, n); //DAU INDEX
            performanceSheet.Cell(n, 11).Value = insight.DailyAppInstalls; //Instalaciones

            var virality = repository.Get<Virality, DateTime>(insight.Date);
            if (virality == null) virality = new Virality();
            performanceSheet.Cell(n, 12).Value = virality.Total; //K Factor (overall)
            performanceSheet.Cell(n, 13).Value = virality.Invite; //K Factor invite
            performanceSheet.Cell(n, 14).Value = virality.Notification; //K Notification
            performanceSheet.Cell(n, 15).Value = virality.TotalInstallations; //Viral Installs
            performanceSheet.Cell(n, 16).FormulaA1 = string.Format("K{0}-O{0}", n, n); //MKT Installs

            var analytics = repository.Get<Analytics, DateTime>(insight.Date);
            if (analytics == null) analytics = new Analytics();
            performanceSheet.Cell(n, 17).Value = analytics.Visits; //Visits
            performanceSheet.Cell(n, 18).FormulaA1 = string.Format("IF(C{0}=0,0,Q{1}/C{2})", n, n, n); //Daily Sessions
            TimeSpan timeOnSite = new TimeSpan(0, 0, analytics.TimeOnSite);
            performanceSheet.Cell(n, 19).Value = timeOnSite.ToString(); //Time on site

            var revenue = repository.Get<Revenue, DateTime>(insight.Date);
            if (revenue == null) revenue = new Revenue();
            performanceSheet.Cell(n, 20).Value = revenue.TotalRevenue; //Daily Revenue
            performanceSheet.Cell(n, 21).FormulaA1 = string.Format("IF(C{0}=0,0,T{1}/C{2})", n, n, n); //ARP DAU
            performanceSheet.Cell(n, 22).FormulaA1 = string.Format("IF(F{0}=0,0,T{1}/F{2})", n, n, n); //ARP MAU
            performanceSheet.Cell(n, 23).FormulaA1 = string.Format("IF(X{0}=0,0, T{1}/X{2})", n, n, n); //ARP PU
            performanceSheet.Cell(n, 24).Value = revenue.TransactionCount; ///Pay Users
            performanceSheet.Cell(n, 25).FormulaA1 = string.Format("X{0}/C{0}", n, n); //% Pay. Users
            performanceSheet.Cell(n, 26).FormulaA1 = string.Format("L{0}*T{0}*I{0}", n, n); //LTV
            performanceSheet.Cell(n, 27).Value = string.Empty; //CPA

            performanceSheet.Cell(n, 28).FormulaA1 = "K" + n.ToString(); // Level 1
            performanceSheet.Cell(n, 29).FormulaA1 = string.Format("IF(C{0}=0,0,AB{1}/C{2})", n, n, n); //%DAU

            performanceSheet.Cell(n, 30).Value = analytics.Level2; //Level 2
            performanceSheet.Cell(n, 31).FormulaA1 = string.Format("IF(C{0}=0,0,AD{1}/C{2})", n, n, n); //%DAU
            performanceSheet.Cell(n, 32).FormulaA1 = string.Format("IF(AB{0}=0,0,AD{1}/AB{2})", n, n, n); //% Pasaje

            performanceSheet.Cell(n, 33).Value = analytics.Level3; //Level 3
            performanceSheet.Cell(n, 34).FormulaA1 = string.Format("IF(C{0}=0,0,AG{1}/C{2})", n, n, n); //%DAU
            performanceSheet.Cell(n, 35).FormulaA1 = string.Format("IF(AD{0}=0,0,AG{1}/AD{2})", n, n, n); //% Pasaje

            performanceSheet.Cell(n, 36).Value = analytics.Level4; //Level 4
            performanceSheet.Cell(n, 37).FormulaA1 = string.Format("IF(C{0}=0,0,AJ{1}/C{2})", n, n, n); //%DAU
            performanceSheet.Cell(n, 38).FormulaA1 = string.Format("IF(AG{0}=0,0,AJ{1}/AG{2})", n, n, n); //% Pasaje

            performanceSheet.Cell(n, 39).Value = analytics.Level5; //Level 5
            performanceSheet.Cell(n, 40).FormulaA1 = string.Format("IF(C{0}=0,0,AM{1}/C{2})", n, n, n); //%DAU
            performanceSheet.Cell(n, 41).FormulaA1 = string.Format("IF(AJ{0}=0,0,AM{1}/AJ{2})", n, n, n); //% Pasaje

            performanceSheet.Cell(n, 42).Value = string.Empty; //Weekly Average
            performanceSheet.Cell(n, 43).Value = string.Empty; //Monthly Average
            if (insight.Date.DayOfWeek == DayOfWeek.Saturday || insight.Date.DayOfWeek == DayOfWeek.Sunday)
            {
                performanceSheet.Row(n).Style.Fill.BackgroundColor = XLColor.Yellow;
            }
        }

		private void CreateVerticalNumberHeader(IXLWorksheet workSheet, string HeadLineText,
			int LineNumber, int StartColumnIndex)
			{
			workSheet.Cell(LineNumber, StartColumnIndex).Style
				.Font.SetBold()
				.Font.SetFontSize(9)
				.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left)
				.Alignment.SetTextRotation(90);
			workSheet.Cell(LineNumber, StartColumnIndex).Style
				.Alignment.WrapText = true;
			workSheet.Cell(LineNumber, StartColumnIndex).Value = HeadLineText;
			workSheet.Row(LineNumber).Height = 75;
			workSheet.Column(StartColumnIndex).Width = 4;
			}

 private static IXLRow FindRowById(string id, IXLWorksheet worksheet)
 {
     var lastRowUsed = worksheet.LastRowUsed().RowNumber();
     for (int rowIndex = 0; rowIndex <= lastRowUsed; rowIndex++) {
         if (worksheet.Row(rowIndex).Cell(1).GetValue<string>().ToLower() == id.ToLower()) {
             return worksheet.Row(rowIndex);
         }
     }
     return null;
 }

            public static IEnumerable<TranslationColumn> ReadCultures(IXLWorksheet worksheet)
            {
                var textValues = worksheet.Row(1).Cells().Where(cell => cell.Value != null).Select(cell => cell.Value.ToString()).ToList<String>();
                var cols = textValues.Skip(2).Where(s => !s.Contains("[Comments]")).ToList<String>();

                var list = new List<TranslationColumn>();
                foreach (var s in cols)
                {
                    try
                    {
                        var textColumn = new TranslationColumn(new CultureInfo(s));
                        textColumn.TextColumnIndex = textValues.IndexOf(s);

                        string commentsKey = s;
                        if (s != "")
                            commentsKey += " [Comments]";
                        else
                            commentsKey += "[Comments]";

                        var commentColumn = textValues.Skip(2).Where(t => t.Equals(commentsKey)).FirstOrDefault();
                        if (commentColumn != null)
                            textColumn.CommentColumnIndex = textValues.IndexOf(commentColumn);

                        list.Add(textColumn);
                    }
                    catch (CultureNotFoundException)
                    {
                    }
                }

                return list;
            }

 private static void AppendArray(IXLWorksheet worksheet, string[] headers)
 {
     for (int index = 0; index < headers.Length; index++) {
         worksheet.Row(1).Cell(index + 1).Value = headers[index];
     }
 }