C# OfficeOpenXml.ExcelWorksheet类代码示例

        public SheetWrapper(ExcelWorksheet sheet,bool revriteExising=false)
        {
            ColumnMap = new Dictionary<string, int>();
            Sheet = sheet;

            if (sheet.Dimension == null || revriteExising)
            {
                RowCursor = 2;
                return;
                
            }

            if (sheet.Dimension.Rows != 0)
            {
                RowCursor = sheet.Dimension.Rows + 1;
            }

            if (sheet.Dimension.Columns != 0)
            {
                for (int i = 1; i < sheet.Dimension.Columns; i++)
                {
                    ColumnMap.AddOrOvewrite(sheet.Cells[1, i].Value.ToString(), i);
                }
            }
        }

 public override ExcelCellAddress AddItemToWorksheet(ExcelWorksheet worksheet)
 {
     var cell = worksheet.Cells[Row + 1, Column + 1];
     cell.Style.Numberformat.Format = NumberFormat;
     cell.Value = Value;
     return cell.End;
 }

        private List<UploadViewFileColumn> MakeColumnTypeList(int headerRow, ExcelWorksheet worksheet)
        {
            // this assumes that columns in the spreadsheet with blank header rows do
            // not contain relevant data

            List<UploadViewFileColumn> columnList = new List<UploadViewFileColumn>();

            for (int column = 1; column <= worksheet.Dimension.End.Column; column++)
            {
                if (worksheet.Cells[headerRow, column].Value != null)
                {
                    if (worksheet.Cells[headerRow, column].Value.ToString() != "")
                    {
                        UploadViewFileColumn homeViewColumn = new UploadViewFileColumn
                        {
                            ColumnName = worksheet.Cells[headerRow, column].Value.ToString().Trim(),
                            DataType = SetType(worksheet.Cells[headerRow + 1, column].Value)
                        };
                        if (homeViewColumn.ColumnName == "") homeViewColumn.ColumnName = "NONAME";
                        columnList.Add(homeViewColumn);
                    }
                }

            }

            return columnList;
        }

 /// <summary>
 /// Creates a new ExcelWorksheetView which provides access to all the view states of the worksheet.
 /// </summary>
 /// <param name="ns"></param>
 /// <param name="node"></param>
 /// <param name="xlWorksheet"></param>
 internal ExcelWorksheetView(XmlNamespaceManager ns, XmlNode node,  ExcelWorksheet xlWorksheet)
     : base(ns, node)
 {
     _worksheet = xlWorksheet;
     SchemaNodeOrder = new string[] { "sheetViews", "sheetView", "pane", "selection" };
     Panes = LoadPanes();
 }

		/// <summary>
		/// Creates a new instance of the ExcelRow class. 
		/// For internal use only!
		/// </summary>
		/// <param name="Worksheet">The parent worksheet</param>
		/// <param name="row">The row number</param>
		protected internal ExcelRow(ExcelWorksheet Worksheet, int row)
		{
			_xlWorksheet = Worksheet;

			//  Search for the existing row
			_rowElement = (XmlElement) Worksheet.WorksheetXml.SelectSingleNode(string.Format("//d:sheetData/d:row[@r='{0}']", row), _xlWorksheet.NameSpaceManager);
			if (_rowElement == null)
			{
				// We didn't find the row, so add a new row element.
				// HOWEVER we MUST insert new row in the correct position - otherwise Excel 2007 will complain!!!
				_rowElement = Worksheet.WorksheetXml.CreateElement("row", ExcelPackage.schemaMain);
				_rowElement.SetAttribute("r", row.ToString());

				// now work out where to insert the new row
				XmlNode sheetDataNode = Worksheet.WorksheetXml.SelectSingleNode("//d:sheetData", _xlWorksheet.NameSpaceManager);
				if (sheetDataNode != null)
				{
					XmlNode followingRow = null;
					foreach (XmlNode currentRow in Worksheet.WorksheetXml.SelectNodes("//d:sheetData/d:row", _xlWorksheet.NameSpaceManager))
					{
						int rowFound = Convert.ToInt32(currentRow.Attributes.GetNamedItem("r").Value);
						if (rowFound > row)
						{
							followingRow = currentRow;
							break;
						}
					}
					if (followingRow == null)
						// no data rows exist, so just add row
						sheetDataNode.AppendChild(_rowElement);
					else
						sheetDataNode.InsertBefore(_rowElement, followingRow);
				}
			}
		}

        private void ExportTable(ExcelWorksheet workSheet, ReportTable table, ICollection<string> names)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table");
            }
            if (workSheet == null)
            {
                throw new ArgumentNullException("workSheet");
            }

            const int firstColumn = 2;
            var firstLine = workSheet.Dimension != null ? workSheet.Dimension.End.Row + 2 : 2;
            var xlWriter = new ExcelWriter(workSheet, firstLine, firstColumn, names.Count);

            xlWriter.PutTableHead(table.Name, names);

            ICollection<int> totalRating = null;
            foreach (var group in table.ReportGroups)
            {
                var groupResults = GenerateGroup(xlWriter, group);
                totalRating = totalRating == null ? groupResults : SummarizeResults(totalRating, groupResults);
            }

            xlWriter.PutTableResults(totalRating);
            xlWriter.SetGlobalStyles();
        }

        /// <summary>
        /// Populate award objects from spreadsheet
        /// </summary>
        /// <param name="workSheet"></param>
        /// <param name="firstRowHeader"></param>
        /// <returns></returns>
        static IEnumerable<AcademyAward> PopulateAwards(ExcelWorksheet workSheet, bool firstRowHeader)
        {
            IList<AcademyAward> awards = new List<AcademyAward>();

            if (workSheet != null)
            {
                Dictionary<string, int> header = new Dictionary<string,int>();

                for (int rowIndex = workSheet.Dimension.Start.Row; rowIndex <= workSheet.Dimension.End.Row; rowIndex++)
                {
                    //Assume the first row is the header.  Then use the column match ups by name to determine the index.
                    //This will allow you to have the order of the columns change without any affect.
                    
                    if (rowIndex == 1 && firstRowHeader)
                    {
                        header = ExcelHelper.GetExcelHeader(workSheet, rowIndex);
                    }
                    else
                    {
                        awards.Add(new AcademyAward{
                            Year = ExcelHelper.ParseWorksheetValue(workSheet, header, rowIndex, "Year"),
                            Category = ExcelHelper.ParseWorksheetValue(workSheet, header, rowIndex, "Category"),
                            Nominee = ExcelHelper.ParseWorksheetValue(workSheet, header, rowIndex, "Nominee"),
                            AdditionalInfo = ExcelHelper.ParseWorksheetValue(workSheet, header, rowIndex, "AdditionalInfo"),
                            Won = ExcelHelper.ParseWorksheetValue(workSheet, header, rowIndex, "Won?")
                        });

                    }
                }
            }

            return awards;
        }

        /// <summary>
        /// BuildTeachersTable
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="Teachers"></param>
        private static void BuildTeachersTable(ExcelWorksheet ws, IEnumerable<OfficeVisitsByTeacher> Teachers)
        {

            ws.Column(1).Width = 17.86;
            ws.Column(2).Width = 12.43;

            //Set Header titles
            ws.Cells[4, 1].Value = "Teachers";
            ws.Cells[4, 1].Style.Font.Bold = true;
            ws.Cells[5, 1].Value = "Teacher Name";
            ws.Cells[5, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
           // ws.Cells[5, 1].AutoFilter = true;
            ws.Cells[5, 2].Value = "Office Visits";
            ws.Cells[5, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
           // ws.Cells[5, 2].AutoFilter = true;

            //Get Data for Teachers       
            for (int i = 0; i < Teachers.Count(); i++)
            {
                ws.Cells[i + 6, 1].Value = Teachers.ElementAt(i).sent_by_contact_name;
                ws.Cells[i + 6, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin);
                ws.Cells[i + 6, 2].Value = Teachers.ElementAt(i).total_visits;
                ws.Cells[i + 6, 2].Style.Border.BorderAround(ExcelBorderStyle.Thin);
            }

            //Set Header style
            using (ExcelRange rng = ws.Cells[4, 1, 5 + Teachers.Count(), 2])
            {
                rng.Style.Border.BorderAround(ExcelBorderStyle.Medium);            
            }

        }

 private void AddSteps(ExcelWorksheet xlWorkSheet, ITestAction testAction, Dictionary<string, string> replacements, ref int row)
 {
     var testStep = testAction as ITestStep;
     var group = testAction as ITestActionGroup;
     var sharedRef = testAction as ISharedStepReference;
     if (null != testStep)
     {
         CleanupText(xlWorkSheet.Cells[row, 3], testStep.Title.ToString(), replacements);
         CleanupText(xlWorkSheet.Cells[row, 5], testStep.ExpectedResult.ToString(), replacements);
     }
     else if (null != group)
     {
         foreach (var action in group.Actions)
         {
             AddSteps(xlWorkSheet, action, replacements, ref row);
         }
     }
     else if (null != sharedRef)
     {
         var step = sharedRef.FindSharedStep();
         foreach (var action in step.Actions)
         {
             AddSteps(xlWorkSheet, action, replacements, ref row);
         }
     }
     row++;
 }

        private void ApplyDataToShopSignWorksheet(System.Data.DataTable dt, ExcelWorksheet worksheet)
        {
            int colIndex = 3;
            ExcelRange columnTemplate = worksheet.Cells["C1:C7"];
            ExcelRange pastedColumn = worksheet.Cells["D1"];//1, colIndex, 7, colIndex];
            //columnTemplate.Copy(pastedColumn);
            for (int i = 3; i < dt.Columns.Count; i++)
            {
                if (colIndex > 3)
                {
                    pastedColumn = worksheet.Cells[1, colIndex];//1, colIndex, 7, colIndex];
                    columnTemplate.Copy(pastedColumn);
                }

                pastedColumn[1, colIndex].Value = dt.Columns[i].ColumnName;
                pastedColumn[2, colIndex].Value = dt.Rows[0][i];
                pastedColumn[3, colIndex].Value = dt.Rows[1][i];
                pastedColumn[4, colIndex].Value = dt.Rows[2][i];
                pastedColumn[5, colIndex].Value = dt.Rows[3][i];
                pastedColumn[6, colIndex].Value = dt.Rows[4][i];
                pastedColumn[7, colIndex].Value = dt.Rows[5][i];

                colIndex++;

            }
            //Xoa cot template
        }

        private static void BuildTitle(ExcelWorksheet ws)
        {
  
            ws.Cells[1, 5].Value = "Dashboards  To " + DateTime.Now.ToString("MM/dd/yyyy");
            ws.Cells[1, 5].Style.Font.Bold = true;

        }

        private ExcelWorksheet PerformFinalFormatting(ExcelWorksheet sheet)
        {
            //Header
            sheet.HeaderFooter.FirstHeader.LeftAlignedText = "VIRGINIA TECH FOUNDATION INC.\n"
                + "UNRESTRICTED BUDGET\n" +
                "FY " + WebConfigurationManager.AppSettings["FiscalYear"].ToString();

            //Footer
            sheet.HeaderFooter.FirstFooter.CenteredText = System.DateTime.Now.ToShortDateString() +
                " Summary of VT Foundation Funding Request FY " +
                WebConfigurationManager.AppSettings["FiscalYear"].ToString();

            //Printing
            sheet.PrinterSettings.Orientation = eOrientation.Landscape;
            sheet.PrinterSettings.FitToPage = true;
            sheet.PrinterSettings.FitToWidth = 1;
            sheet.PrinterSettings.FitToHeight = 0;
            ExcelRange range_numberFormatting =
                sheet.Cells[1, NUM_COLUMNS - SUMMARY_DATA_COLUMNS + 1, 100, NUM_COLUMNS];

            //Cell styling
            range_numberFormatting.Style.Numberformat.Format = "_($* #,##0_);_($* (#,##0);_($* \"-\"_);_(@_)";

            sheet.Cells.AutoFitColumns();

            return sheet;
        }

		public CalculationDataDrawable(CalculationData data, string clientNick, int columnCount, ExcelWorksheet excel)
		{
			_data = data;
			_clientNick = clientNick;
			_excel = excel;
			_columnCount = columnCount;
		}

 private static void AddWebpage(List<Webpage> webpages, int index, ExcelWorksheet wsItems)
 {
     var rowId = index + 2;
     var webpage = webpages[index];
     wsItems.Cells["A" + rowId].Value = webpage.UrlSegment;
     wsItems.Cells["A" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
     wsItems.Cells["B" + rowId].Value = webpage.Parent != null ? webpage.Parent.UrlSegment : String.Empty;
     wsItems.Cells["B" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
     wsItems.Cells["C" + rowId].Value = webpage.DocumentType;
     wsItems.Cells["D" + rowId].Value = webpage.Name;
     wsItems.Cells["D" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
     wsItems.Cells["E" + rowId].Value = webpage.BodyContent;
     wsItems.Cells["E" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Fill;
     wsItems.Cells["F" + rowId].Value = webpage.MetaTitle;
     wsItems.Cells["G" + rowId].Value = webpage.MetaDescription;
     wsItems.Cells["G" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Fill;
     wsItems.Cells["H" + rowId].Value = webpage.MetaKeywords;
     wsItems.Cells["I" + rowId].Value = string.Join(",", webpage.Tags.Select(tag => tag.Name));
     wsItems.Cells["I" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
     wsItems.Cells["J" + rowId].Value = webpage.RevealInNavigation;
     wsItems.Cells["K" + rowId].Value = webpage.DisplayOrder;
     wsItems.Cells["L" + rowId].Value = webpage.RequiresSSL;
     wsItems.Cells["M" + rowId].Value = webpage.PublishOn.HasValue
         ? webpage.PublishOn.Value.ToString("yyyy-MM-dd HH:mm:ss")
         : String.Empty;
     wsItems.Cells["N" + rowId].Value = string.Join(",", webpage.Urls.Select(history => history.UrlSegment));
     wsItems.Cells["N" + rowId].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
 }

        public static ExcelRange GetLastFullEmptyRow(ExcelWorksheet sheet, params string[] columnsToCheck)
        {
            ExcelRange firstCellOfEmptyRow = sheet.Cells["A1"];
            List<ExcelRange> lastRows = new List<ExcelRange>();
            foreach (string column in columnsToCheck) {
                // Start at the top left corner
                var cell = sheet.Cells[column + "1"];

                // Get to last empty row
                while (!string.IsNullOrEmpty(cell.Text)) {
                    cell = cell.NextRow();
                }
                lastRows.Add(cell);
            }

            var lastEmptyCellColumn = GetRow(firstCellOfEmptyRow);
            foreach (ExcelRange row in lastRows) {
                var rowColumn = GetRow(row);
                if (rowColumn > lastEmptyCellColumn) {
                    lastEmptyCellColumn = rowColumn;
                }
            }

            return sheet.Cells["A" + lastEmptyCellColumn];
        }