Namespace EasyXLS

Class ExcelTable


  • public class ExcelTable
    extends System.Object
    This class simulates a worksheet table. It is assigned to an ExcelWorksheet class. It allows to access the spreadsheet cells, to add rows and columns, to set default row height and column width, to add horizontal and vertical page breaks for page break preview.
    • Constructor Summary

      Constructors 
      Constructor Description
      ExcelTable()
      Defines an instance of the worksheet table.
    • Method Summary

      Modifier and Type Method Description
      ExcelTable Clone()
      Creates and returns a copy of this object.
      int ColumnCount()
      Returns the number of columns from the table.
      void Dispose()
      Disposes any resources that it has allocated.
      void easy_addColumn()
      Adds a column to table.
      void easy_addColumn​(ExcelColumn columnData)
      Adds the specified column to table.
      void easy_addRow()
      Adds a row to table.
      void easy_addRow​(ExcelRow rowData)
      Adds the specified row to table.
      void easy_freezePanes​(int splitRow, int splitColumn)
      Sets the freeze panes for the table.
      void easy_freezePanes​(int splitRow, int splitColumn, int topVisibleRow_BottomPane, int leftVisibleColumn_RightPane)
      Sets the freeze panes for the table.
      ExcelCell easy_getCell​(int row, int column)
      If a cell does not exist at the specified position, it creates one, and returns the cell.
      ExcelCell easy_getCell​(System.String cellReference)
      Returns the cell at the specified reference.
      ExcelCell easy_getCellAt​(int row, int column)
      Returns the cell at the specified row and column.
      int easy_getCellMergingFirstCol​(int nIndex)
      Returns the first column of the specified cell merging range.
      int easy_getCellMergingFirstRow​(int nIndex)
      Returns the first row of the specified cell merging range.
      int easy_getCellMergingLastCol​(int nIndex)
      Returns the last column of the specified cell merging range.
      int easy_getCellMergingLastRow​(int nIndex)
      Returns the last row of the specified cell merging range.
      ExcelColumn easy_getColumnAt​(int column)
      Returns the column at the specified position.
      System.String easy_getMergingRangeAsA1Reference​(int nIndex)
      Internal use only Returns the cell merging range at the specified range as A1 reference (e.g.
      ExcelRow easy_getRowAt​(int row)
      Returns the row at specified position.
      void easy_insertColumn​(int column, ExcelColumn columnData)
      Inserts the specified column to table into the specified position.
      void easy_insertPageBreakAtColumn​(int column)
      Inserts a page break after the specified column.
      void easy_insertPageBreakAtRow​(int row)
      Inserts a page break after the specified row.
      void easy_insertRow​(int row, ExcelRow rowData)
      Inserts the specified row to table into the specified position.
      void easy_mergeCells​(int startRow, int startColumn, int endRow, int endColumn)
      Merging the cells inside the specified range.
      void easy_mergeCells​(System.String range)
      Merging the cells inside the specified range.
      void easy_mergeCells​(System.String startCellReference, System.String endCellReference)
      Merging the cells inside the range limited by the specified cell references.
      void easy_removeCellMerging​(int nIndex)
      Removes the cell merging area at the specified index from the table
      void easy_removeColumn​(int column)
      Removes the column at the specified index from the table
      void easy_removeColumnRange​(int startColumn, int count)
      Removes a range of columns from the table
      void easy_removeRow​(int row)
      Removes the row at the specified index from the table
      void easy_removeRowRange​(int startRow, int count)
      Removes a range of rows from the table
      void easy_removeSplit()
      Removes the split option for the table.
      void easy_resetAllPageBreaks()
      Resets all the defined page breaks.
      void easy_setCellAt​(ExcelCell cell, int row, int column)
      Sets the cell at the specified position.
      void easy_setRangeAutoFormat​(int startRow, int startColumn, int endRow, int endColumn, ExcelAutoFormat xlsAutoFormat)
      Sets the specified autoformat for the range.
      void easy_setRangeAutoFormat​(System.String range, ExcelAutoFormat xlsAutoFormat)
      Sets the specified autoformat for the range.
      void easy_setRangeAutoFormat​(System.String startCellReference, System.String endCellReference, ExcelAutoFormat xlsAutoFormat)
      Sets the specified autoformat for the range limited by the specified cell references.
      void easy_setRangeStyle​(int startRow, int startColumn, int endRow, int endColumn, ExcelStyle style)
      Sets the specified style for the range limited by the rows and columns.
      void easy_setRangeStyle​(System.String range, ExcelStyle style)
      Sets the specified style for the range.
      void easy_setRangeStyle​(System.String startCellReference, System.String endCellReference, ExcelStyle style)
      Sets the specified style for the range limited by the specified cell references.
      void easy_split​(int splitRow, int splitColumn)
      Sets the split option for the table.
      void easy_split​(int splitRow, int splitColumn, int topVisibleRow_BottomPane, int leftVisibleColumn_RightPane)
      Sets the split option for the table.
      void easy_unfreezePanes()
      Unfreezes panes for the table.
      List findAll​(System.String value, int lookIn, bool matchCase, bool matchEntireCellContents)
      Searches all the occurrences of the cell that matches the criteria.
      Cell findFirst​(System.String value, int lookIn, bool matchCase, bool matchEntireCellContents)
      Searches the first cell that matches the criteria.
      int getColumnWidth()
      Returns the default width of the columns.
      int getColumnWidth​(int column)
      Returns the width of the specified column.
      List getHorizontalPageBreaks()
      Returns a list with all horizontal page breaks
      int getRowHeight()
      Returns the default height of the rows.
      int getRowHeight​(int row)
      Returns the height of the specified row.
      List getVerticalPageBreaks()
      Returns a list with all vertical page breaks
      bool IsFreezePaneSet()
      Returns true if the row/columns are frozen, false otherwise.
      bool IsRowHeightSet()
      Returns true if the default row height was set, false otherwise.
      bool IsSplitSet()
      Returns true if the row/columns are split, false otherwise.
      int MergeCellRangesCount()
      Returns the count of the cell merging ranges.
      int RowCount()
      Returns the number of rows from the table.
      void setColumnCount​(int count)
      Sets the number of columns for the table.
      void setColumnWidth​(int width)
      Sets the default width of the columns.
      void setColumnWidth​(int column, int columnWidth)
      Sets the width for the specified column.
      void setFreezePaneSet​(bool bValue)
      Internal use only
      void setRowCount​(int count)
      Sets the number of rows for the table.
      void setRowHeight​(int height)
      Sets the default height of the rows.
      void setRowHeight​(int row, int rowHeight)
      Sets the height for the specified row.
      void setRowHeightSet​(bool bValue)
      Internal use only
      void setSplitSet​(bool bValue)
      Internal use only
      void validate()
      Finalize the changes applied on the rows, columns and cells.
    • Constructor Detail

      • ExcelTable

        public ExcelTable()
        Defines an instance of the worksheet table.
    • Method Detail

      • validate

        public void validate()
        Finalize the changes applied on the rows, columns and cells.
      • RowCount

        public int RowCount()
        Returns the number of rows from the table.
        Returns:
        the number of rows from the table
        See Also:
        setRowCount(int)
      • setRowCount

        public void setRowCount​(int count)
        Sets the number of rows for the table.
        Parameters:
        count - the number of rows from the table
        See Also:
        RowCount()
      • easy_insertRow

        public void easy_insertRow​(int row,
                                   ExcelRow rowData)
        Inserts the specified row to table into the specified position.
        Parameters:
        row - the position where the row will be inserted
        rowData - the row to be inserted
        See Also:
        easy_addRow(), easy_removeRow(int)
      • IsRowHeightSet

        public bool IsRowHeightSet()
        Returns true if the default row height was set, false otherwise.
        Returns:
        true if the default row height was set, false otherwise
        See Also:
        setRowHeight(int)
      • setRowHeightSet

        public void setRowHeightSet​(bool bValue)
        Internal use only
      • getRowHeight

        public int getRowHeight​(int row)
        Returns the height of the specified row.
        Parameters:
        row - the index of the row for which to return the height
        Returns:
        the height of the specified row
        See Also:
        getRowHeight(), setRowHeight(int), setRowHeight(int,int)
      • setRowHeight

        public void setRowHeight​(int row,
                                 int rowHeight)
        Sets the height for the specified row.
        Parameters:
        row - the index of the row
        rowHeight - the new value of the height
        See Also:
        getRowHeight(), setRowHeight(int), getRowHeight(int)
      • easy_getRowAt

        public ExcelRow easy_getRowAt​(int row)
        Returns the row at specified position.
        Parameters:
        row - the row position
        Returns:
        the row at specified position
      • ColumnCount

        public int ColumnCount()
        Returns the number of columns from the table.
        Returns:
        the number of columns from the table
        See Also:
        setColumnCount(int)
      • setColumnCount

        public void setColumnCount​(int count)
        Sets the number of columns for the table.
        Parameters:
        count - the number of columns for the table
        See Also:
        ColumnCount()
      • easy_insertColumn

        public void easy_insertColumn​(int column,
                                      ExcelColumn columnData)
        Inserts the specified column to table into the specified position.
        Parameters:
        column - the position where the column will be inserted
        columnData - the column to be inserted
        See Also:
        easy_addColumn(), easy_removeColumn(int)
      • setColumnWidth

        public void setColumnWidth​(int column,
                                   int columnWidth)
        Sets the width for the specified column.
        Parameters:
        column - the column position
        columnWidth - the new width of the column
        See Also:
        getColumnWidth(), setColumnWidth(int), getColumnWidth(int)
      • easy_getColumnAt

        public ExcelColumn easy_getColumnAt​(int column)
        Returns the column at the specified position.
        Parameters:
        column - the column position
        Returns:
        the column at specified position
      • easy_getCellAt

        public ExcelCell easy_getCellAt​(int row,
                                        int column)
        Returns the cell at the specified row and column. If the cell does not exist an 'array index out of range exception' will be thrown.
        Parameters:
        row - the row that specifies the cell position
        column - the column that specifies the cell position
        Returns:
        the cell at specified position
        See Also:
        easy_getCell(int, int), easy_setCellAt(EasyXLS.ExcelCell, int, int)
      • easy_getCell

        public ExcelCell easy_getCell​(int row,
                                      int column)
        If a cell does not exist at the specified position, it creates one, and returns the cell. Note: Slower than easy_getCellAt method.
        Parameters:
        row - the row that specifies the cell position
        column - the column that specifies the cell position
        Returns:
        the cell at specified position
        See Also:
        easy_getCellAt(int, int), easy_setCellAt(EasyXLS.ExcelCell, int, int)
      • easy_setCellAt

        public void easy_setCellAt​(ExcelCell cell,
                                   int row,
                                   int column)
        Sets the cell at the specified position.
        Parameters:
        cell - the cell to be set
        row - the row position
        column - the column position
        See Also:
        easy_getCellAt(int, int), easy_getCell(int, int)
      • easy_setRangeAutoFormat

        public void easy_setRangeAutoFormat​(int startRow,
                                            int startColumn,
                                            int endRow,
                                            int endColumn,
                                            ExcelAutoFormat xlsAutoFormat)
        Sets the specified autoformat for the range.
        Parameters:
        startRow - the row that limits the left upper corner of the cell range
        startColumn - the column that limits the left upper corner of the cell range
        endRow - the row that limits the right down corner of the cell range
        endColumn - the column that limits the right down corner of the cell range
        xlsAutoFormat - the autoformat to be applied
      • easy_setRangeAutoFormat

        public void easy_setRangeAutoFormat​(System.String range,
                                            ExcelAutoFormat xlsAutoFormat)
        Sets the specified autoformat for the range.
        Parameters:
        range - the range where the autoformat will be applied. It can be a cell reference or a range.
        xlsAutoFormat - the autoformat to be applied
      • easy_setRangeAutoFormat

        public void easy_setRangeAutoFormat​(System.String startCellReference,
                                            System.String endCellReference,
                                            ExcelAutoFormat xlsAutoFormat)
        Sets the specified autoformat for the range limited by the specified cell references.
        Parameters:
        startCellReference - the cell reference that limits the left upper corner of the cell range
        endCellReference - the cell reference that limits the right down corner of the cell range
        xlsAutoFormat - the autoformat to be applied
      • easy_setRangeStyle

        public void easy_setRangeStyle​(int startRow,
                                       int startColumn,
                                       int endRow,
                                       int endColumn,
                                       ExcelStyle style)
        Sets the specified style for the range limited by the rows and columns.
        Parameters:
        startRow - the row that limits the left upper corner of the cell range
        startColumn - the column that limits the left upper corner of the cell range
        endRow - the row that limits the right down corner of the cell range
        endColumn - the column that limits the right down corner of the cell range
        style - the style for the cells
      • easy_setRangeStyle

        public void easy_setRangeStyle​(System.String range,
                                       ExcelStyle style)
        Sets the specified style for the range.
        Parameters:
        range - the range where the style will be applied. It can be a cell reference or a range.
        style - the style for the cells
      • easy_setRangeStyle

        public void easy_setRangeStyle​(System.String startCellReference,
                                       System.String endCellReference,
                                       ExcelStyle style)
        Sets the specified style for the range limited by the specified cell references.
        Parameters:
        startCellReference - the cell reference that limits the left upper corner of the cell range
        endCellReference - the cell reference that limits the right down corner of the cell range
        style - the style for the cells
      • easy_mergeCells

        public void easy_mergeCells​(int startRow,
                                    int startColumn,
                                    int endRow,
                                    int endColumn)
        Merging the cells inside the specified range.
        Parameters:
        startRow - the row that limits the left upper corner of the cell range
        startColumn - the column that limits the left upper corner of the cell range
        endRow - the row that limits the right down corner of the cell range
        endColumn - the column that limits the right down corner of the cell range
      • easy_mergeCells

        public void easy_mergeCells​(System.String startCellReference,
                                    System.String endCellReference)
        Merging the cells inside the range limited by the specified cell references.
        Parameters:
        startCellReference - the cell reference that limits the left upper corner of the cell range
        endCellReference - the cell reference that limits the right down corner of the cell range
        See Also:
        easy_mergeCells(int,int,int,int), easy_mergeCells(String)
      • easy_getMergingRangeAsA1Reference

        public System.String easy_getMergingRangeAsA1Reference​(int nIndex)
        Internal use only Returns the cell merging range at the specified range as A1 reference (e.g. B2:G9).
        Parameters:
        nIndex - the merge cells range index
        Returns:
        cell merging range at the specified range as A1 reference (e.g. B2:G9)
      • MergeCellRangesCount

        public int MergeCellRangesCount()
        Returns the count of the cell merging ranges.
        Returns:
        the count of the cell merging ranges
      • easy_removeCellMerging

        public void easy_removeCellMerging​(int nIndex)
        Removes the cell merging area at the specified index from the table
        Parameters:
        nIndex - the cell merging area index
      • easy_insertPageBreakAtColumn

        public void easy_insertPageBreakAtColumn​(int column)
        Inserts a page break after the specified column.
        Parameters:
        column - the column after which the page break will be inserted
        See Also:
        easy_insertPageBreakAtRow(int), easy_resetAllPageBreaks()
      • getHorizontalPageBreaks

        public List getHorizontalPageBreaks()
        Returns a list with all horizontal page breaks
        Returns:
        a list with all horizontal page breaks
        See Also:
        getVerticalPageBreaks()
      • getVerticalPageBreaks

        public List getVerticalPageBreaks()
        Returns a list with all vertical page breaks
        Returns:
        a list with all vertical page breaks
        See Also:
        getHorizontalPageBreaks()
      • easy_freezePanes

        public void easy_freezePanes​(int splitRow,
                                     int splitColumn)
        Sets the freeze panes for the table.
        Parameters:
        splitRow - the row position of the split
        splitColumn - the column position of the split
        See Also:
        easy_freezePanes(int,int,int,int), easy_unfreezePanes()
      • easy_freezePanes

        public void easy_freezePanes​(int splitRow,
                                     int splitColumn,
                                     int topVisibleRow_BottomPane,
                                     int leftVisibleColumn_RightPane)
        Sets the freeze panes for the table.
        Parameters:
        splitRow - the row position of the split
        splitColumn - the column position of the split
        topVisibleRow_BottomPane - the top row visible in the bottom pane
        leftVisibleColumn_RightPane - the left most column visible in the right pane
        See Also:
        easy_freezePanes(int,int), easy_unfreezePanes()
      • easy_split

        public void easy_split​(int splitRow,
                               int splitColumn)
        Sets the split option for the table.
        Parameters:
        splitRow - the row position of the split
        splitColumn - the column position of the split
        See Also:
        easy_split(int,int,int,int), easy_removeSplit()
      • easy_split

        public void easy_split​(int splitRow,
                               int splitColumn,
                               int topVisibleRow_BottomPane,
                               int leftVisibleColumn_RightPane)
        Sets the split option for the table.
        Parameters:
        splitRow - the row position of the split
        splitColumn - the column position of the split
        topVisibleRow_BottomPane - the top row visible in the bottom pane
        leftVisibleColumn_RightPane - the left most visible column in the right pane
        See Also:
        easy_split(int,int), easy_removeSplit()
      • IsFreezePaneSet

        public bool IsFreezePaneSet()
        Returns true if the row/columns are frozen, false otherwise.
        Returns:
        true if the row/columns are frozen, false otherwise.
        See Also:
        easy_freezePanes(int,int)
      • setFreezePaneSet

        public void setFreezePaneSet​(bool bValue)
        Internal use only
      • IsSplitSet

        public bool IsSplitSet()
        Returns true if the row/columns are split, false otherwise.
        Returns:
        true if the row/columns are split, false otherwise.
        See Also:
        easy_split(int,int,int,int)
      • setSplitSet

        public void setSplitSet​(bool bValue)
        Internal use only
      • findFirst

        public Cell findFirst​(System.String value,
                              int lookIn,
                              bool matchCase,
                              bool matchEntireCellContents)
        Searches the first cell that matches the criteria.
        Parameters:
        value - the string to be searched
        lookIn - shows whether the search is in formulas, in values or in comments. Possible values are available in Table class.
        matchCase - true if the search is case sensitive, false otherwise
        matchEntireCellContents - true if the search matches the entire cell contents, false otherwise
        Returns:
        the first cell that matches the criteria
        See Also:
        findAll(System.String, int, bool, bool)
      • findAll

        public List findAll​(System.String value,
                            int lookIn,
                            bool matchCase,
                            bool matchEntireCellContents)
        Searches all the occurrences of the cell that matches the criteria.
        Parameters:
        value - the string to be searched
        lookIn - shows whether the search is in formulas, in values or in comments. Possible values are available in Table class.
        matchCase - true if the search is case sensitive, false otherwise
        matchEntireCellContents - true if the search matches the entire cell contents, false otherwise
        Returns:
        all the occurrences of the cell that matches the criteria
        See Also:
        findFirst(System.String, int, bool, bool)
      • Dispose

        public void Dispose()
        Disposes any resources that it has allocated.
      • Clone

        public ExcelTable Clone()
        Creates and returns a copy of this object.
        Returns:
        a clone of this instance.