C# IXLWorksheet类代码示例

        private void MakeHeaders(IXLWorksheet sheet, TestSuite suite)
        {
            int column = 1;

            for (; column <= deep; column++)
            {
                string header = string.Empty;

                for (int i = 1; i < column; i++)
                    header += "Sub-";

                sheet.Cell(row, column).Value = header + "Category";
                sheet.Column(column).Width = 20.86;
            }

            sheet.Cell(row, deep + 1).Value = "Name";
            sheet.Column(deep + 1).Width = 30;
            sheet.Cell(row, deep + 2).Value = "Id";
            sheet.Column(deep + 2).Width = 13;
            sheet.Cell(row, deep + 3).Value = "Summary";
            sheet.Column(deep + 3).Width = 42;
            sheet.Cell(row, deep + 4).Value = "Preconditions";
            sheet.Column(deep + 4).Width = 56.43;
            sheet.Cell(row, deep + 5).Value = "Actions";
            sheet.Column(deep + 5).Width = 100;
            sheet.Cell(row, deep + 6).Value = "Expected Results";
            sheet.Column(deep + 6).Width = 100;
            row++;
        }

        private void ProcessWorksheet(IXLWorksheet xlWorksheet)
        {
            if (!_uniqueIdMapping.Any())
                _uniqueIdMapping =
                    _pcfService.GetForChurch(this._churchId)
                               .Select(i => new { i.UniqueId, i.Id })
                               .ToDictionary(t => t.UniqueId, t => t.Id);
            var rows = xlWorksheet.RowsUsed();
            foreach (var row in rows)
            {
                var pcfUniqueId = row.FirstCellUsed().GetString();
                var pcfId =
                    _uniqueIdMapping.FirstOrDefault(
                        i => i.Key.Equals(pcfUniqueId, StringComparison.InvariantCultureIgnoreCase)).Value;

                if (pcfId != 0)
                {
                    var m = new StagedCells()
                    {
                        Name = row.FirstCellUsed().CellRight().GetString(),
                        UniqueId = UniqueIdGenerator.GenerateUniqueIdForCell(name: row.FirstCellUsed().GetString()),
                        PCFId = pcfId,
                        ChurchId = this._churchId
                    };
                    _cellService.Insert(m);
                }
            }
        }

 /// <summary>
 /// Creates a new parser using the given <see cref="IXLWorksheet"/> and <see cref="CsvConfiguration"/>.
 /// </summary>
 /// <param name="worksheet">The <see cref="IXLWorksheet"/> with the data.</param>
 /// <param name="configuration">The configuration.</param>
 public ExcelParser(IXLWorksheet worksheet, CsvConfiguration configuration)
 {
     workbook = worksheet.Workbook;
     this.worksheet = worksheet;
     this.configuration = configuration;
     FieldCount = worksheet.RowsUsed().CellsUsed().Max(cell => cell.Address.ColumnNumber);
 }

        public void Format(IXLWorksheet worksheet, Feature feature)
        {
            worksheet.Cell("A1").Style.Font.SetBold();
            worksheet.Cell("A1").Value = feature.Name;
            worksheet.Cell("B2").Value = feature.Description;
            worksheet.Cell("B2").Style.Alignment.WrapText = false;

            var results = testResults.GetFeatureResult(feature);

            if (configuration.HasTestResults && results.WasExecuted)
            {
                worksheet.Cell("A1").Style.Fill.SetBackgroundColor(results.WasSuccessful
                                                                       ? XLColor.AppleGreen
                                                                       : XLColor.CandyAppleRed);
            }

            int row = 4;
            foreach (IFeatureElement featureElement in feature.FeatureElements)
            {
                var scenario = featureElement as Scenario;
                if (scenario != null)
                {
                    excelScenarioFormatter.Format(worksheet, scenario, ref row);
                }

                var scenarioOutline = featureElement as ScenarioOutline;
                if (scenarioOutline != null)
                {
                    excelScenarioOutlineFormatter.Format(worksheet, scenarioOutline, ref row);
                }

                row++;
            }
        }

        public void Format(IXLWorksheet worksheet, ScenarioOutline scenarioOutline, ref int row)
        {
            int originalRow = row;
            worksheet.Cell(row++, "B").Value = scenarioOutline.Name;
            worksheet.Cell(row++, "C").Value = scenarioOutline.Description;

            var results = this.testResults.GetScenarioOutlineResult(scenarioOutline);
            if (this.configuration.HasTestResults && (results != TestResult.Inconclusive))
            {
                worksheet.Cell(originalRow, "B").Style.Fill.SetBackgroundColor(results == TestResult.Passed
                    ? XLColor.AppleGreen
                    : XLColor.CandyAppleRed);
            }

            foreach (Step step in scenarioOutline.Steps)
            {
                this.excelStepFormatter.Format(worksheet, step, ref row);
            }

            row++;

            foreach (var example in scenarioOutline.Examples)
            {
                worksheet.Cell(row++, "B").Value = "Examples";
                worksheet.Cell(row, "C").Value = example.Description;
                this.excelTableFormatter.Format(worksheet, example.TableArgument, ref row);
            }
        }

        public void Format(IXLWorksheet worksheet, Table table, ref int row)
        {
            int startRow = row;
            int headerColumn = TableStartColumn;
            foreach (string cell in table.HeaderRow)
            {
                worksheet.Cell(row, headerColumn).Style.Font.SetBold();
                worksheet.Cell(row, headerColumn).Style.Font.SetItalic();
                worksheet.Cell(row, headerColumn).Style.Fill.SetBackgroundColor(XLColor.AliceBlue);
                worksheet.Cell(row, headerColumn++).Value = cell;
            }
            row++;

            foreach (TableRow dataRow in table.DataRows)
            {
                int dataColumn = TableStartColumn;
                foreach (string cell in dataRow)
                {
                    worksheet.Cell(row, dataColumn++).Value = cell;
                }
                row++;
            }

            int lastRow = row - 1;
            int lastColumn = headerColumn - 1;

            worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.TopBorder =
                XLBorderStyleValues.Thin;
            worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.LeftBorder =
                XLBorderStyleValues.Thin;
            worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.BottomBorder =
                XLBorderStyleValues.Thin;
            worksheet.Range(startRow, TableStartColumn, lastRow, lastColumn).Style.Border.RightBorder =
                XLBorderStyleValues.Thin;
        }

 public static void AppendOmnitureStandardPageView(IXLWorksheet omniture)
 {
     var values = new string[] {
         "t-1",
         "",
         "DUNLOP",
         "HOMEPAGE",
         "HOMEPAGE",
         "INDEX",
         "index",
         "",
         "",
         "DUNLOP | HOMEPAGE | HOMEPAGE | INDEX",
         "",
         "HOMEPAGE",
         "HOMEPAGE",
         "HOMEPAGE | HOMEPAGE",
         "INDEX",
         "HOMEPAGE | INDEX",
         "HOMEPAGE | HOMEPAGE  | INDEX",
         "INDEX",
         "HOMEPAGE | INDEX",
         "HOMEPAGE | INDEX | INDEX",
         "HOMEPAGE | HOMEPAGE | HOMEPAGE | INDEX"
     };
 }

        public static double getTotalWidth(IXLWorksheet ws, int startCol)
        {
            var totalWidth = 0.0;
            foreach (var col in ws.Columns(startCol, ws.LastColumnUsed().ColumnNumber()))
            {
                totalWidth += col.Width * 5.69;
            }

            return totalWidth;
        }

        public void Format(IXLWorksheet worksheet, string documentString, ref int row)
        {
            string[] documentStringLines = documentString.Split(new[] {"\n", "\r"},
                                                                StringSplitOptions.RemoveEmptyEntries);

            foreach (string line in documentStringLines)
            {
                worksheet.Cell(row++, 4).Value = line;
            }
        }

        public static double getTotalHeight(IXLWorksheet ws, int startRow)
        {
            var totalHeight = 0.0;
            foreach (var row in ws.Rows(startRow, ws.LastRowUsed().RowNumber()))
            {
                totalHeight += row.Height;
            }

            return totalHeight;

        }

        public void Format(IXLWorksheet worksheet, Feature feature)
        {
            worksheet.Cell("A1").Style.Font.SetBold();
            worksheet.Cell("A1").Value = feature.Name;

            if (feature.Description.Length <= short.MaxValue)
            {
                worksheet.Cell("B2").Value = feature.Description;
            }
            else
            {
                var description = feature.Description.Substring(0, short.MaxValue);
                Log.Warn("The description of feature {0} was truncated because of cell size limitations in Excel.", feature.Name);
                worksheet.Cell("B2").Value = description;
            }

            worksheet.Cell("B2").Style.Alignment.WrapText = false;

            var results = this.testResults.GetFeatureResult(feature);

            if (this.configuration.HasTestResults && results != TestResult.Inconclusive)
            {
                worksheet.Cell("A1").Style.Fill.SetBackgroundColor(results == TestResult.Passed
                    ? XLColor.AppleGreen
                    : XLColor.CandyAppleRed);
            }

            var featureElementsToRender = new List<IFeatureElement>();
            if (feature.Background != null)
            {
                featureElementsToRender.Add(feature.Background);
            }

            featureElementsToRender.AddRange(feature.FeatureElements);

            var row = 4;
            foreach (var featureElement in featureElementsToRender)
            {
                var scenario = featureElement as Scenario;
                if (scenario != null)
                {
                    this.excelScenarioFormatter.Format(worksheet, scenario, ref row);
                }

                var scenarioOutline = featureElement as ScenarioOutline;
                if (scenarioOutline != null)
                {
                    this.excelScenarioOutlineFormatter.Format(worksheet, scenarioOutline, ref row);
                }

                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);
            }
        }

 public static void AutoFitWorksheet(IXLWorksheet ws, int startCol, int startRow, double lineHeight)
 {
     foreach (var row in ws.RowsUsed().Skip(startRow))
     {
         var maxLines = 1.0;
         foreach (var cell in row.CellsUsed().Skip(startCol))
         {
             if (cell.Value != null)
             {
                 var lines = cell.Value.ToString().Length / (cell.WorksheetColumn().Width * 1.15);
                 maxLines = maxLines < lines ? lines : maxLines;
             }
         }
         row.Height = lineHeight * Math.Ceiling(maxLines);
     }
 }

 public void BuildTableOfContents(XLWorkbook workbook, IXLWorksheet worksheet, ref int row, int column, GeneralTree<INode> features)
 {
     foreach (var childNode in features.ChildNodes)
     {
         var featureChildNode = childNode.Data as FeatureNode;
         if (featureChildNode != null)
         {
             var featureWorksheet = FindFirstMatchingA1TitleUsingFeatureName(workbook, featureChildNode);
             this.WriteFileCell(worksheet, ref row, column, featureWorksheet);
         }
         else if (childNode.Data.NodeType == NodeType.Structure)
         {
             this.WriteDirectoryCell(worksheet, ref row, column, childNode.Data.Name);
             this.BuildTableOfContents(workbook, worksheet, ref row, column + 1, childNode);
         }
     }
 }

        public void Format(IXLWorksheet worksheet, Step step, ref int row)
        {
            worksheet.Cell(row, "C").Style.Font.SetBold();
            worksheet.Cell(row, "C").Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);
            worksheet.Cell(row, "C").Value = step.NativeKeyword;
            worksheet.Cell(row++, "D").Value = step.Name;

            if (step.TableArgument != null)
            {
                this.excelTableFormatter.Format(worksheet, step.TableArgument, ref row);
            }

            if (!string.IsNullOrEmpty(step.DocStringArgument))
            {
                this.excelDocumentStringFormatter.Format(worksheet, step.DocStringArgument, ref row);
            }
        }