C# ExcelWorksheet类代码示例

        private void SetHourData(Models.Student student, ExcelWorksheet worksheet)
        {
            var collector = new System.Collections.Generic.Dictionary<SpreadsheetExport.Key, int>();
            foreach (var b in student.Behaviors) {
                var key = new SpreadsheetExport.Key { DayOfWeek = b.TimeRecorded.DayOfWeek.ToString(), Hour = b.TimeRecorded.Hour };
                if (collector.ContainsKey(key))
                    collector[key] += 1;
                else
                    collector.Add(key, 1);

            }
            foreach (var key in collector.Keys) {
                var value = key.Hour - 2;
                switch (key.DayOfWeek.ToString()) {
                    case "Monday": worksheet.Cells[5, value].Value = collector[key];
                        break;
                    case "Tuesday": worksheet.Cells[6, value].Value = collector[key];
                        break;
                    case "Wednesday": worksheet.Cells[7, value].Value = collector[key];
                        break;
                    case "Thursday": worksheet.Cells[8, value].Value = collector[key];
                        break;
                    case "Friday": worksheet.Cells[9, value].Value = collector[key];
                        break;
                    default:
                        break;
                }
            }
        }

        private static void ApplyColumnsAutoWidth(ExcelWorksheet ws)
        {
            int columnCount = ws.CalculateMaxUsedColumns();

            for (int i = 0; i < columnCount; i++)
                ws.Columns[i].AutoFit();
        }

		/// <summary>
		/// Creates a new instance of the ExcelColumn class.  
		/// For internal use only!
		/// </summary>
		/// <param name="Worksheet"></param>
		/// <param name="col"></param>
		protected internal ExcelColumn(ExcelWorksheet Worksheet, int col)
        {
            _worksheet = Worksheet;
            _columnMin = col;
            _columnMax = col;
            _width = _worksheet.DefaultColWidth;
        }

        public void Import(ExcelWorksheet sheet, List<EntityMetadata> emds, IOrganizationService service)
        {
            var rmds = new List<OneToManyRelationshipMetadata>();

            foreach (var row in sheet.Rows.Where(r => r.Index != 0).OrderBy(r => r.Index))
            {
                var rmd = rmds.FirstOrDefault(r => r.MetadataId == new Guid(row.Cells[1].Value.ToString()));
                if (rmd == null)
                {
                    var currentEntity = emds.FirstOrDefault(e => e.LogicalName == row.Cells[0].Value.ToString());
                    if (currentEntity == null)
                    {
                        var request = new RetrieveEntityRequest
                        {
                            LogicalName = row.Cells[0].Value.ToString(),
                            EntityFilters = EntityFilters.Relationships
                        };

                        var response = ((RetrieveEntityResponse) service.Execute(request));
                        currentEntity = response.EntityMetadata;

                        emds.Add(currentEntity);
                    }

                    rmd =
                        currentEntity.OneToManyRelationships.FirstOrDefault(
                            r => r.SchemaName == row.Cells[2].Value.ToString());
                    if (rmd == null)
                    {
                        rmd =
                            currentEntity.ManyToOneRelationships.FirstOrDefault(
                                r => r.SchemaName == row.Cells[2].Value.ToString());
                    }

                    rmds.Add(rmd);
                }

                int columnIndex = 4;

                rmd.AssociatedMenuConfiguration.Label = new Label();

                while (row.Cells[columnIndex].Value != null)
                {
                    rmd.AssociatedMenuConfiguration.Label.LocalizedLabels.Add(
                        new LocalizedLabel(row.Cells[columnIndex].Value.ToString(),
                            int.Parse(sheet.Cells[0, columnIndex].Value.ToString())));

                    columnIndex++;
                }
            }

            foreach (var rmd in rmds)
            {
                var request = new UpdateRelationshipRequest
                {
                    Relationship = rmd,
                };
                service.Execute(request);
            }
        }

 public RangeDataValidation(ExcelWorksheet worksheet, string address)
 {
     Require.Argument(worksheet).IsNotNull("worksheet");
     Require.Argument(address).IsNotNullOrEmpty("address");
     _worksheet = worksheet;
     _address = address;
 }

        /// <summary>
        /// A named range
        /// </summary>
        /// <param name="name">The name</param>
        /// <param name="nameSheet">The sheet containing the name. null if its a global name</param>
        /// <param name="sheet">Sheet where the address points</param>
        /// <param name="address">The address</param>
        public ExcelNamedRange(string name, ExcelWorksheet nameSheet , ExcelWorksheet sheet, string address) :
            base(sheet, address)
        {
            Name = name;
            _sheet = nameSheet;

        }

 private void SetMonthData(Models.Student student, ExcelWorksheet worksheet)
 {
     int m = 0, t = 0, w = 0, th = 0, f = 0;
     foreach (var b in student.Behaviors) {
         {
             switch (b.TimeRecorded.DayOfWeek.ToString()) {
                 case "Monday": m++;
                     break;
                 case "Tuesday": t++;
                     break;
                 case "Wednesday": w++;
                     break;
                 case "Thursday": th++;
                     break;
                 case "Friday": f++;
                     break;
             }
         }
         worksheet.Cells[2, 5].Value = m;
         worksheet.Cells[2, 6].Value = t;
         worksheet.Cells[2, 7].Value = w;
         worksheet.Cells[2, 8].Value = th;
         worksheet.Cells[2, 9].Value = f;
     }
 }

 internal ExcelRange(ExcelWorksheet sheet, int fromRow, int fromCol, int toRow, int toCol)
     : base(sheet)
 {
     _fromRow = fromRow;
     _fromCol = fromCol;
     _toRow = toRow;
     _toCol = toCol;
 }

 internal ExcelCell(ExcelWorksheet worksheet, string cellAddress)
 {
     _worksheet = worksheet;
     GetRowColFromAddress(cellAddress, out _row, out _col);
     if (_col < worksheet._minCol) worksheet._minCol = _col;
     if (_col > worksheet._maxCol) worksheet._maxCol = _col;
     _sharedFormulaID = int.MinValue;
     IsRichText = false;
 }

 internal ExcelCommentCollection(ExcelPackage pck, ExcelWorksheet ws, XmlNamespaceManager ns)
 {
     CommentXml = new XmlDocument();
     CommentXml.PreserveWhitespace = false;
     NameSpaceManager=ns;
     Worksheet=ws;
     CreateXml(pck);
     AddCommentsFromXml();
 }

    /****************************************************************************************/

    #region Constructors
    /// <summary>
    /// Initialize the <see cref="ExcelConditionalFormattingRule"/>
    /// </summary>
    /// <param name="type"></param>
    /// <param name="address"></param>
    /// <param name="priority">Used also as the cfRule unique key</param>
    /// <param name="worksheet"></param>
    /// <param name="itemElementNode"></param>
    /// <param name="namespaceManager"></param>
    internal ExcelConditionalFormattingRule(
      eExcelConditionalFormattingRuleType type,
      ExcelAddress address,
      int priority,
      ExcelWorksheet worksheet,
      XmlNode itemElementNode,
      XmlNamespaceManager namespaceManager)
      : base(
        namespaceManager,
        itemElementNode)
    {
      Require.Argument(address).IsNotNull("address");

  	  // While MSDN states that 1 is the "highest priority," it also defines this
	  // field as W3C XML Schema int, which would allow values less than 1. Excel
	  // itself will, on occasion, use a value of 0, so this check will allow a 0.
      Require.Argument(priority).IsInRange(0, int.MaxValue, "priority");

      Require.Argument(worksheet).IsNotNull("worksheet");

      _type = type;
      _worksheet = worksheet;
      SchemaNodeOrder = _worksheet.SchemaNodeOrder;

      if (itemElementNode == null)
      {
        // Create/Get the <cfRule> inside <conditionalFormatting>
        itemElementNode = CreateComplexNode(
          _worksheet.WorksheetXml.DocumentElement,
          string.Format(
            "{0}[{1}='{2}']/{1}='{2}'/{3}[{4}='{5}']/{4}='{5}'",
          //{0}
            ExcelConditionalFormattingConstants.Paths.ConditionalFormatting,
          // {1}
            ExcelConditionalFormattingConstants.Paths.SqrefAttribute,
          // {2}
            address.AddressSpaceSeparated,          //CF node don't what to have comma between multi addresses, use space instead.
          // {3}
            ExcelConditionalFormattingConstants.Paths.CfRule,
          //{4}
            ExcelConditionalFormattingConstants.Paths.PriorityAttribute,
          //{5}
            priority));
      }

      // Point to <cfRule>
      TopNode = itemElementNode;

      Address = address;
      Priority = priority;
      Type = type;
      if (DxfId >= 0)
      {
          worksheet.Workbook.Styles.Dxfs[DxfId].AllowChange = true;  //This Id is referenced by CF, so we can use it when we save.
          _style = worksheet.Workbook.Styles.Dxfs[DxfId].Clone();    //Clone, so it can be altered without effecting other dxf styles
      }
    }

        /// <summary>
        /// Creates new object of type SheetControl and imports data from ExcelWorksheet if available
        /// </summary>
        /// <param name="worksheet">Optional. Worksheet to import</param>
        public SheetControl(ExcelWorksheet worksheet = null)
        {
            InitializeComponent();

            // Assign argument
            this.worksheet = worksheet;

            ReloadSheet();
        }

        internal static DependencyChain Create(ExcelWorksheet ws, string Formula, ExcelCalculationOption options)
        {
            ws.CheckSheetType();
            var depChain = new DependencyChain();

            GetChain(depChain, ws.Workbook.FormulaParser.Lexer, ws, Formula, options);
            
            return depChain;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="address"></param>
        /// <param name="priority"></param>
        /// <param name="worksheet"></param>
        /// <param name="itemElementNode"></param>
        /// <param name="namespaceManager"></param>
        internal ExcelConditionalFormattingThreeColorScale(
            ExcelAddress address,
            int priority,
            ExcelWorksheet worksheet,
            XmlNode itemElementNode,
            XmlNamespaceManager namespaceManager)
            : base(eExcelConditionalFormattingRuleType.ThreeColorScale,
        address,
        priority,
        worksheet,
        itemElementNode,
        (namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
        {
            if (itemElementNode == null)
            {
                // Create the <colorScale> node inside the <cfRule> node
                var colorScaleNode = CreateComplexNode(
                  Node,
                  ExcelConditionalFormattingConstants.Paths.ColorScale);

                // LowValue default
                LowValue = new ExcelConditionalFormattingColorScaleValue(
                  eExcelConditionalFormattingValueObjectPosition.Low,
                  eExcelConditionalFormattingValueObjectType.Min,
                  ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoLowValue),
                  eExcelConditionalFormattingRuleType.ThreeColorScale,
                  address,
                  priority,
                  worksheet,
                  NameSpaceManager);

                // MiddleValue default
                MiddleValue = new ExcelConditionalFormattingColorScaleValue(
                  eExcelConditionalFormattingValueObjectPosition.Middle,
                  eExcelConditionalFormattingValueObjectType.Percent,
                  ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoMiddleValue),
                  50,
                  string.Empty,
                  eExcelConditionalFormattingRuleType.ThreeColorScale,
                  address,
                  priority,
                  worksheet,
                  NameSpaceManager);

                // HighValue default
                HighValue = new ExcelConditionalFormattingColorScaleValue(
                  eExcelConditionalFormattingValueObjectPosition.High,
                  eExcelConditionalFormattingValueObjectType.Max,
                  ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoHighValue),
                  eExcelConditionalFormattingRuleType.ThreeColorScale,
                  address,
                  priority,
                  worksheet,
                  NameSpaceManager);
            }
        }

 private static TableDescriptor GetTableDescriptor(ExcelWorksheet worksheet, ContentLoadType type)
 {
     var countOfKeys = 2;
     if (type.HasFlag(ContentLoadType.Column))
     {
         countOfKeys++;
     }
     var descriptor = TableDescriptor.GetReadDynamic(worksheet.Name, new TableKeyDescriptor((new[] {1, 2, 3}).Take(countOfKeys).ToArray()), 2);
     return descriptor;
 }