/* ==================================================================== Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for Additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. ==================================================================== */ namespace HH.WMS.Utils.NPOI.SS.UserModel { using System; using System.Collections.Generic; using HH.WMS.Utils.NPOI.SS.Util; /// /// Indicate the position of the margin. One of left, right, top and bottom. /// public enum MarginType : short { /// /// referes to the left margin /// LeftMargin = 0, /// /// referes to the right margin /// RightMargin = 1, /// /// referes to the top margin /// TopMargin = 2, /// /// referes to the bottom margin /// BottomMargin = 3, HeaderMargin = 4, FooterMargin = 5 } /// /// Define the position of the pane. One of lower/right, upper/right, lower/left and upper/left. /// public enum PanePosition : byte { /// /// referes to the lower/right corner /// LOWER_RIGHT = 0, /// /// referes to the upper/right corner /// UPPER_RIGHT = 1, /// /// referes to the lower/left corner /// LOWER_LEFT = 2, /// /// referes to the upper/left corner /// UPPER_LEFT = 3, } /// /// High level representation of a Excel worksheet. /// /// /// Sheets are the central structures within a workbook, and are where a user does most of his spreadsheet work. /// The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can /// contain text, numbers, dates, and formulas. Cells can also be formatted. /// public interface ISheet { /// /// Create a new row within the sheet and return the high level representation /// /// The row number. /// high level Row object representing a row in the sheet /// RemoveRow(Row) IRow CreateRow(int rownum); /// /// Remove a row from this sheet. All cells Contained in the row are Removed as well /// /// a row to Remove. void RemoveRow(IRow row); /// /// Returns the logical row (not physical) 0-based. If you ask for a row that is not /// defined you get a null. This is to say row 4 represents the fifth row on a sheet. /// /// row to get (0-based). /// the rownumber or null if its not defined on the sheet IRow GetRow(int rownum); /// /// Returns the number of physically defined rows (NOT the number of rows in the sheet) /// /// the number of physically defined rows in this sheet. int PhysicalNumberOfRows { get; } /// /// Gets the first row on the sheet /// /// the number of the first logical row on the sheet (0-based). int FirstRowNum { get; } /// /// Gets the last row on the sheet /// /// last row contained n this sheet (0-based) int LastRowNum { get; } /// /// whether force formula recalculation. /// bool ForceFormulaRecalculation { get; set; } /// /// Get the visibility state for a given column /// /// the column to get (0-based) /// the visiblity state of the column void SetColumnHidden(int columnIndex, bool hidden); /// /// Get the hidden state for a given column /// /// the column to set (0-based) /// hidden - false if the column is visible bool IsColumnHidden(int columnIndex); /// /// Set the width (in units of 1/256th of a character width) /// /// The maximum column width for an individual cell is 255 characters. /// This value represents the number of characters that can be displayed /// in a cell that is formatted with the standard font. /// /// the column to set (0-based) /// the width in units of 1/256th of a character width void SetColumnWidth(int columnIndex, int width); /// /// get the width (in units of 1/256th of a character width ) /// /// the column to set (0-based) /// the width in units of 1/256th of a character width int GetColumnWidth(int columnIndex); /// /// Get the default column width for the sheet (if the columns do not define their own width) /// in characters /// /// default column width measured in characters. int DefaultColumnWidth { get; set; } /// /// Get the default row height for the sheet (if the rows do not define their own height) in /// twips (1/20 of a point) /// /// default row height measured in twips (1/20 of a point) int DefaultRowHeight { get; set; } /// /// Get the default row height for the sheet (if the rows do not define their own height) in /// points. /// /// The default row height in points. float DefaultRowHeightInPoints { get; set; } /// /// Returns the CellStyle that applies to the given /// (0 based) column, or null if no style has been /// set for that column /// /// The column. ICellStyle GetColumnStyle(int column); /// /// Adds a merged region of cells (hence those cells form one) /// /// (rowfrom/colfrom-rowto/colto) to merge. /// index of this region int AddMergedRegion(HH.WMS.Utils.NPOI.SS.Util.CellRangeAddress region); /// /// Determine whether printed output for this sheet will be horizontally centered. /// bool HorizontallyCenter { get; set; } /// /// Determine whether printed output for this sheet will be vertically centered. /// bool VerticallyCenter { get; set; } /// /// Removes a merged region of cells (hence letting them free) /// /// index of the region to unmerge void RemoveMergedRegion(int index); /// /// Returns the number of merged regions /// int NumMergedRegions { get; } /// /// Returns the merged region at the specified index /// /// The index. HH.WMS.Utils.NPOI.SS.Util.CellRangeAddress GetMergedRegion(int index); /// /// Gets the row enumerator. /// /// /// an iterator of the PHYSICAL rows. Meaning the 3rd element may not /// be the third row if say for instance the second row is undefined. /// Call on each row /// if you care which one it is. /// System.Collections.IEnumerator GetRowEnumerator(); /// /// Alias for GetRowEnumerator() to allow foreach loops. /// /// /// an iterator of the PHYSICAL rows. Meaning the 3rd element may not /// be the third row if say for instance the second row is undefined. /// Call on each row /// if you care which one it is. /// System.Collections.IEnumerator GetEnumerator(); /// /// Gets the flag indicating whether the window should show 0 (zero) in cells Containing zero value. /// When false, cells with zero value appear blank instead of showing the number zero. /// /// whether all zero values on the worksheet are displayed. bool DisplayZeros { get; set; } /// /// Gets or sets a value indicating whether the sheet displays Automatic Page Breaks. /// bool Autobreaks { get; set; } /// /// Get whether to display the guts or not, /// /// default value is true bool DisplayGuts { get; set; } /// /// Flag indicating whether the Fit to Page print option is enabled. /// bool FitToPage { get; set; } /// /// Flag indicating whether summary rows appear below detail in an outline, when applying an outline. /// /// /// When true a summary row is inserted below the detailed data being summarized and a /// new outline level is established on that row. /// /// /// When false a summary row is inserted above the detailed data being summarized and a new outline level /// is established on that row. /// /// /// true if row summaries appear below detail in the outline bool RowSumsBelow { get; set; } /// /// Flag indicating whether summary columns appear to the right of detail in an outline, when applying an outline. /// /// /// When true a summary column is inserted to the right of the detailed data being summarized /// and a new outline level is established on that column. /// /// /// When false a summary column is inserted to the left of the detailed data being /// summarized and a new outline level is established on that column. /// /// /// true if col summaries appear right of the detail in the outline bool RowSumsRight { get; set; } /// /// Gets the flag indicating whether this sheet displays the lines /// between rows and columns to make editing and reading easier. /// /// true if this sheet displays gridlines. bool IsPrintGridlines { get; set; } /// /// Gets the print Setup object. /// /// The user model for the print Setup object. IPrintSetup PrintSetup { get; } /// /// Gets the user model for the default document header. ///

/// Note that XSSF offers more kinds of document headers than HSSF does /// ///

/// the document header. Never null IHeader Header { get; } /// /// Gets the user model for the default document footer. ///

/// Note that XSSF offers more kinds of document footers than HSSF does. ///

/// the document footer. Never null IFooter Footer { get; } /// /// Gets the size of the margin in inches. /// /// which margin to get /// the size of the margin double GetMargin(MarginType margin); /// /// Sets the size of the margin in inches. /// /// which margin to get /// the size of the margin void SetMargin(MarginType margin, double size); /// /// Answer whether protection is enabled or disabled /// /// true => protection enabled; false => protection disabled bool Protect { get; } /// /// Sets the protection enabled as well as the password /// /// to set for protection. Pass null to remove protection void ProtectSheet(String password); /// /// Answer whether scenario protection is enabled or disabled /// /// true => protection enabled; false => protection disabled bool ScenarioProtect { get; } /// /// Gets or sets the tab color of the _sheet /// short TabColorIndex { get; set; } /// /// Returns the top-level drawing patriach, if there is one. /// This will hold any graphics or charts for the _sheet. /// WARNING - calling this will trigger a parsing of the /// associated escher records. Any that aren't supported /// (such as charts and complex drawing types) will almost /// certainly be lost or corrupted when written out. Only /// use this with simple drawings, otherwise call /// HSSFSheet#CreateDrawingPatriarch() and /// start from scratch! /// /// The drawing patriarch. IDrawing DrawingPatriarch { get; } /// /// Sets the zoom magnication for the sheet. The zoom is expressed as a /// fraction. For example to express a zoom of 75% use 3 for the numerator /// and 4 for the denominator. /// /// The numerator for the zoom magnification. /// denominator for the zoom magnification. void SetZoom(int numerator, int denominator); /// /// The top row in the visible view when the sheet is /// first viewed after opening it in a viewer /// /// the rownum (0 based) of the top row. short TopRow { get; set; } /// /// The left col in the visible view when the sheet is /// first viewed after opening it in a viewer /// /// the rownum (0 based) of the top row short LeftCol { get; set; } /// /// Sets desktop window pane display area, when the /// file is first opened in a viewer. /// /// the top row to show in desktop window pane /// the left column to show in desktop window pane void ShowInPane(short toprow, short leftcol); /// /// Shifts rows between startRow and endRow n number of rows. /// If you use a negative number, it will shift rows up. /// Code ensures that rows don't wrap around. /// /// Calls shiftRows(startRow, endRow, n, false, false); /// /// /// Additionally shifts merged regions that are completely defined in these /// rows (ie. merged 2 cells on a row to be shifted). /// /// the row to start shifting /// the row to end shifting /// the number of rows to shift void ShiftRows(int startRow, int endRow, int n); /// /// Shifts rows between startRow and endRow n number of rows. /// If you use a negative number, it will shift rows up. /// Code ensures that rows don't wrap around /// /// Additionally shifts merged regions that are completely defined in these /// rows (ie. merged 2 cells on a row to be shifted). /// /// the row to start shifting /// the row to end shifting /// the number of rows to shift /// whether to copy the row height during the shift /// whether to set the original row's height to the default void ShiftRows(int startRow, int endRow, int n, bool copyRowHeight, bool resetOriginalRowHeight); /// /// Creates a split (freezepane). Any existing freezepane or split pane is overwritten. /// /// Horizonatal position of split /// Vertical position of split /// Top row visible in bottom pane /// Left column visible in right pane void CreateFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow); /// /// Creates a split (freezepane). Any existing freezepane or split pane is overwritten. /// /// Horizonatal position of split. /// Vertical position of split. void CreateFreezePane(int colSplit, int rowSplit); /// /// Creates a split pane. Any existing freezepane or split pane is overwritten. /// /// Horizonatal position of split (in 1/20th of a point) /// Vertical position of split (in 1/20th of a point) /// Left column visible in right pane /// Top row visible in bottom pane /// Active pane. One of: PANE_LOWER_RIGHT, PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT /// @see #PANE_LOWER_LEFT /// @see #PANE_LOWER_RIGHT /// @see #PANE_UPPER_LEFT /// @see #PANE_UPPER_RIGHT void CreateSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, PanePosition activePane); /// /// Returns the information regarding the currently configured pane (split or freeze) /// /// if no pane configured returns null else return the pane information. PaneInformation PaneInformation { get; } /// /// Returns if gridlines are displayed /// bool DisplayGridlines { get; set; } /// /// Returns if formulas are displayed /// bool DisplayFormulas { get; set; } /// /// Returns if RowColHeadings are displayed. /// bool DisplayRowColHeadings { get; set; } /// /// Returns if RowColHeadings are displayed. /// bool IsActive { get; set; } /// /// Determines if there is a page break at the indicated row /// /// The row. bool IsRowBroken(int row); /// /// Removes the page break at the indicated row /// /// The row index. void RemoveRowBreak(int row); /// /// Retrieves all the horizontal page breaks /// /// all the horizontal page breaks, or null if there are no row page breaks int[] RowBreaks { get; } /// /// Retrieves all the vertical page breaks /// /// all the vertical page breaks, or null if there are no column page breaks. int[] ColumnBreaks { get; } /// /// Sets the active cell. /// /// The row. /// The column. void SetActiveCell(int row, int column); /// /// Sets the active cell range. /// /// The firstrow. /// The lastrow. /// The firstcolumn. /// The lastcolumn. void SetActiveCellRange(int firstRow, int lastRow, int firstColumn, int lastColumn); /// /// Sets the active cell range. /// /// The cellranges. /// The index of the active range. /// The active row in the active range /// The active column in the active range void SetActiveCellRange(List cellranges, int activeRange, int activeRow, int activeColumn); /// /// Sets a page break at the indicated column /// /// The column. void SetColumnBreak(int column); /// /// Sets the row break. /// /// The row. void SetRowBreak(int row); /// /// Determines if there is a page break at the indicated column /// /// The column index. bool IsColumnBroken(int column); /// /// Removes a page break at the indicated column /// /// The column. void RemoveColumnBreak(int column); /// /// Expands or collapses a column group. /// /// One of the columns in the group. /// if set to truecollapse group.falseexpand group. void SetColumnGroupCollapsed(int columnNumber, bool collapsed); /// /// Create an outline for the provided column range. /// /// beginning of the column range. /// end of the column range. void GroupColumn(int fromColumn, int toColumn); /// /// Ungroup a range of columns that were previously groupped /// /// start column (0-based). /// end column (0-based). void UngroupColumn(int fromColumn, int toColumn); /// /// Tie a range of rows toGether so that they can be collapsed or expanded /// /// start row (0-based) /// end row (0-based) void GroupRow(int fromRow, int toRow); /// /// Ungroup a range of rows that were previously groupped /// /// start row (0-based) /// end row (0-based) void UngroupRow(int fromRow, int toRow); /// /// Set view state of a groupped range of rows /// /// start row of a groupped range of rows (0-based). /// whether to expand/collapse the detail rows. void SetRowGroupCollapsed(int row, bool collapse); /// /// Sets the default column style for a given column. POI will only apply this style to new cells Added to the sheet. /// /// the column index /// the style to set void SetDefaultColumnStyle(int column, ICellStyle style); /// /// Adjusts the column width to fit the contents. /// /// the column index /// /// This process can be relatively slow on large sheets, so this should /// normally only be called once per column, at the end of your /// processing. /// void AutoSizeColumn(int column); /// /// Adjusts the column width to fit the contents. /// /// the column index. /// whether to use the contents of merged cells when /// calculating the width of the column. Default is to ignore merged cells. /// /// This process can be relatively slow on large sheets, so this should /// normally only be called once per column, at the end of your /// processing. /// void AutoSizeColumn(int column, bool useMergedCells); /// /// Returns cell comment for the specified row and column /// /// The row. /// The column. IComment GetCellComment(int row, int column); /// /// Creates the top-level drawing patriarch. /// IDrawing CreateDrawingPatriarch(); /// /// Gets the parent workbook. /// IWorkbook Workbook { get; } /// /// Gets the name of the sheet. /// String SheetName { get; } /// /// Gets or sets a value indicating whether this sheet is currently selected. /// bool IsSelected { get; set; } /// /// Sets whether sheet is selected. /// /// Whether to select the sheet or deselect the sheet. void SetActive(bool sel); /// /// Sets array formula to specified region for result. /// /// text representation of the formula /// Region of array formula for result /// the of cells affected by this change ICellRange SetArrayFormula(String formula, CellRangeAddress range); /// /// Remove a Array Formula from this sheet. All cells contained in the Array Formula range are removed as well /// /// any cell within Array Formula range /// the of cells affected by this change ICellRange RemoveArrayFormula(ICell cell); /// /// Checks if the provided region is part of the merged regions. /// /// Region searched in the merged regions /// true, when the region is contained in at least one of the merged regions bool IsMergedRegion(CellRangeAddress mergedRegion); /// /// Create an instance of a DataValidationHelper. /// /// Instance of a DataValidationHelper IDataValidationHelper GetDataValidationHelper(); /// /// Creates a data validation object /// /// The data validation object settings void AddValidationData(IDataValidation dataValidation); /// /// Enable filtering for a range of cells /// /// the range of cells to filter IAutoFilter SetAutoFilter(CellRangeAddress range); /// /// The 'Conditional Formatting' facet for this Sheet /// /// conditional formatting rule for this sheet ISheetConditionalFormatting SheetConditionalFormatting { get; } } }