XLWorksheet.hpp

Go to the documentation of this file.

#include <optional>
#include <memory>
 
#ifndef OPENXLSX_XLWORKSHEET_HPP
#    define OPENXLSX_XLWORKSHEET_HPP
 
#    include "OpenXLSX-Exports.hpp"
#    include "XLConstants.hpp"
#    include "XLSheetBase.hpp"
#    include <string>
#    include <string_view>
#    include <vector>
 
#    include "XLCell.hpp"
#    include "XLCellRange.hpp"
#    include "XLCellReference.hpp"
#    include "XLChart.hpp"
#    include "XLColumn.hpp"
#    include "XLConditionalFormatting.hpp"
#    include "XLPageSetup.hpp"
#    include "XLRow.hpp"
#    include "XLSparkline.hpp"
 
namespace OpenXLSX
{
    struct XLWorksheetImpl;
    class XLComments;
    class XLDataValidations;
    class XLDrawing;
    class XLVmlDrawing;
    class XLMergeCells;
    class XLPivotTable;
    class XLPivotTableOptions;
    struct XLSlicerOptions;
    class XLRelationships;
    class XLStreamReader;
    class XLStreamWriter;
    class XLTableCollection;
    class XLThreadedComments;
 
    const std::vector<std::string_view> XLWorksheetNodeOrder = {    // worksheet XML root node required child sequence
        "sheetPr",
        "dimension",
        "sheetViews",
        "sheetFormatPr",
        "cols",
        "sheetData",
        "sheetCalcPr",
        "sheetProtection",
        "protectedRanges",
        "scenarios",
        "autoFilter",
        "sortState",
        "dataConsolidate",
        "customSheetViews",
        "mergeCells",
        "phoneticPr",
        "conditionalFormatting",
        "dataValidations",
        "hyperlinks",
        "printOptions",
        "pageMargins",
        "pageSetup",
        "headerFooter",
        "rowBreaks",
        "colBreaks",
        "customProperties",
        "cellWatches",
        "ignoredErrors",
        "smartTags",
        "drawing",
        "legacyDrawing",
        "legacyDrawingHF",
        "drawingHF",
        "picture",
        "oleObjects",
        "controls",
        "webPublishItems",
        "tableParts",
        "pivotTables",
        "extLst"};
 
    const std::vector<std::string_view> XLSheetViewNodeOrder = {    // worksheet XML <sheetView> child sequence
        "pane",
        "selection",
        "pivotSelection",
        "extLst"};
 
    struct OPENXLSX_EXPORT XLSheetProtectionOptions {
        bool sheet = true;
        bool objects = false;
        bool scenarios = false;
 
        bool formatCells = false;
        bool formatColumns = false;
        bool formatRows = false;
        bool insertColumns = false;
        bool insertRows = false;
        bool insertHyperlinks = false;
        bool deleteColumns = false;
        bool deleteRows = false;
        bool sort = false;
        bool autoFilter = false;
        bool pivotTables = false;
 
        bool selectLockedCells = true;
        bool selectUnlockedCells = true;
    };
 
    class OPENXLSX_EXPORT XLWorksheet final : public XLSheetBase<XLWorksheet>
    {
        friend class XLCell;
        friend class XLRow;
        friend class XLWorkbook;
        friend class XLTableCollection;
        friend class XLSheetBase<XLWorksheet>;
 
    public:
        XLWorksheet();
        explicit XLWorksheet(XLXmlData* xmlData);
        ~XLWorksheet();
        XLWorksheet(const XLWorksheet& other);
        XLWorksheet(XLWorksheet&& other) noexcept;
        XLWorksheet& operator=(const XLWorksheet& other);
        XLWorksheet& operator=(XLWorksheet&& other);
 
        XLCellAssignable cell(const std::string& ref) const;
        XLCellAssignable cell(const XLCellReference& ref) const;
        XLCellAssignable cell(uint32_t rowNumber, uint16_t columnNumber) const;
        XLCellAssignable cell(XLRowIndex row, XLColIndex col) const { return cell(row.val, col.val); }
 
        XLStreamWriter streamWriter();
 
        XLStreamReader streamReader() const;
 
        XLCellAssignable findCell(const std::string& ref) const;
        XLCellAssignable findCell(const XLCellReference& ref) const;
        XLCellAssignable findCell(uint32_t rowNumber, uint16_t columnNumber) const;
 
        std::optional<XLCell> peekCell(const std::string& ref) const;
        std::optional<XLCell> peekCell(const XLCellReference& ref) const;
        std::optional<XLCell> peekCell(uint32_t rowNumber, uint16_t columnNumber) const;
        std::optional<XLCell> peekCell(XLRowIndex row, XLColIndex col) const { return peekCell(row.val, col.val); }
 
        XLCellRange range() const;
        XLCellRange range(const XLCellReference& topLeft, const XLCellReference& bottomRight) const;
        XLCellRange range(std::string const& topLeft, std::string const& bottomRight) const;
        XLCellRange range(std::string const& rangeReference) const;
 
        XLRowRange rows() const;
        XLRowRange rows(uint32_t rowCount) const;
        XLRowRange rows(uint32_t firstRow, uint32_t lastRow) const;
 
        XLRow row(uint32_t rowNumber) const;
 
        void appendRow(const std::vector<XLCellValue>& values);
 
        template<typename T,
                 typename = std::enable_if_t<!std::is_same_v<T, std::vector<XLCellValue>> &&
                                                 std::is_base_of_v<std::bidirectional_iterator_tag,
                                                                   typename std::iterator_traits<typename T::iterator>::iterator_category>,
                                             T>>
        void appendRow(const T& values)
        { row(rowCount() + 1).values() = values; }
 
        XLColumn column(uint16_t columnNumber) const;
        XLColumn column(std::string const& columnRef) const;
 
        void autoFitColumn(uint16_t columnNumber);
 
        void groupRows(uint32_t rowFirst, uint32_t rowLast, uint8_t outlineLevel = 1, bool collapsed = false);
        void groupColumns(uint16_t colFirst, uint16_t colLast, uint8_t outlineLevel = 1, bool collapsed = false);
 
        void         setAutoFilter(const XLCellRange& range);
        void         clearAutoFilter();
        bool         hasAutoFilter() const;
        std::string  autoFilter() const;
        XLAutoFilter autofilterObject() const;
 
        void addSortCondition(const std::string& ref, uint16_t colId, bool descending = false);
 
        void applyAutoFilter();
 
        XLCellReference lastCell() const noexcept;
        uint16_t        columnCount() const noexcept;
        uint32_t        rowCount() const noexcept;
 
        bool deleteRow(uint32_t rowNumber);
 
        bool insertRow(uint32_t rowNumber, uint32_t count = 1);
 
        bool deleteRow(uint32_t rowNumber, uint32_t count);
 
        bool insertColumn(uint16_t colNumber, uint16_t count = 1);
 
        bool deleteColumn(uint16_t colNumber, uint16_t count = 1);
        void updateSheetName(const std::string& oldName, const std::string& newName);
        void updateDimension();
 
        XLMergeCells&      merges();
        XLDataValidations& dataValidations();
 
        XLCellRange mergeCells(XLCellRange const& rangeToMerge, bool emptyHiddenCells = false);
        XLCellRange mergeCells(const std::string& rangeReference, bool emptyHiddenCells = false);
        void        unmergeCells(XLCellRange const& rangeToMerge);
        void        unmergeCells(const std::string& rangeReference);
 
        XLStyleIndex getColumnFormat(uint16_t column) const;
        XLStyleIndex getColumnFormat(const std::string& column) const;
        bool         setColumnFormat(uint16_t column, XLStyleIndex cellFormatIndex);
        bool         setColumnFormat(const std::string& column, XLStyleIndex cellFormatIndex);
 
        XLStyleIndex getRowFormat(uint16_t row) const;
        bool         setRowFormat(uint32_t row, XLStyleIndex cellFormatIndex);
 
        XLConditionalFormats conditionalFormats() const;
        void                 addConditionalFormatting(const std::string& sqref, const XLCfRule& rule);
        void                 addConditionalFormatting(const std::string& sqref, const XLCfRule& rule, const XLDxf& dxf);
        void                 addConditionalFormatting(const XLCellRange& range, const XLCfRule& rule);
        void                 addConditionalFormatting(const XLCellRange& range, const XLCfRule& rule, const XLDxf& dxf);
 
        void removeConditionalFormatting(const std::string& sqref);
        void removeConditionalFormatting(const XLCellRange& range);
        void clearAllConditionalFormatting();
 
        XLPageMargins  pageMargins() const;
        XLPrintOptions printOptions() const;
        XLPageSetup    pageSetup() const;
        XLHeaderFooter headerFooter() const;
 
        void setPrintArea(const std::string& sqref);
 
        void setPrintTitleRows(uint32_t firstRow, uint32_t lastRow);
 
        void setPrintTitleCols(uint16_t firstCol, uint16_t lastCol);
 
        bool protect(const XLSheetProtectionOptions& options, std::string_view password = "");
 
        bool protectSheet(bool set = true);
        bool protectObjects(bool set = true);
        bool protectScenarios(bool set = true);
 
        bool allowInsertColumns(bool set = true);
        bool allowInsertRows(bool set = true);
        bool allowDeleteColumns(bool set = true);
        bool allowDeleteRows(bool set = true);
        bool allowFormatCells(bool set = true);
        bool allowFormatColumns(bool set = true);
        bool allowFormatRows(bool set = true);
        bool allowInsertHyperlinks(bool set = true);
        bool allowSort(bool set = true);
        bool allowAutoFilter(bool set = true);
        bool allowPivotTables(bool set = true);
        bool allowSelectLockedCells(bool set = true);
        bool allowSelectUnlockedCells(bool set = true);
 
        bool denyInsertColumns() { return allowInsertColumns(false); }
        bool denyInsertRows() { return allowInsertRows(false); }
        bool denyDeleteColumns() { return allowDeleteColumns(false); }
        bool denyDeleteRows() { return allowDeleteRows(false); }
        bool denyFormatCells() { return allowFormatCells(false); }
        bool denyFormatColumns() { return allowFormatColumns(false); }
        bool denyFormatRows() { return allowFormatRows(false); }
        bool denyInsertHyperlinks() { return allowInsertHyperlinks(false); }
        bool denySort() { return allowSort(false); }
        bool denyAutoFilter() { return allowAutoFilter(false); }
        bool denyPivotTables() { return allowPivotTables(false); }
        bool denySelectLockedCells() { return allowSelectLockedCells(false); }
        bool denySelectUnlockedCells() { return allowSelectUnlockedCells(false); }
 
        bool setPasswordHash(std::string hash);
        bool setPassword(std::string password);
        bool clearPassword();
        bool clearSheetProtection();
 
        bool sheetProtected() const;
        bool objectsProtected() const;
        bool scenariosProtected() const;
 
        bool insertColumnsAllowed() const;
        bool insertRowsAllowed() const;
        bool deleteColumnsAllowed() const;
        bool deleteRowsAllowed() const;
        bool formatCellsAllowed() const;
        bool formatColumnsAllowed() const;
        bool formatRowsAllowed() const;
        bool insertHyperlinksAllowed() const;
        bool sortAllowed() const;
        bool autoFilterAllowed() const;
        bool pivotTablesAllowed() const;
        bool selectLockedCellsAllowed() const;
        bool selectUnlockedCellsAllowed() const;
 
        std::string passwordHash() const;
        bool        passwordIsSet() const;
        std::string sheetProtectionSummary() const;
 
        void addSparkline(const std::string& location, const std::string& dataRange, XLSparklineType type = XLSparklineType::Line);
        
        void addSparkline(const std::string& location, const std::string& dataRange, const XLSparklineOptions& options);
 
        bool hasRelationships() const;
        bool hasDrawing() const;
        bool hasVmlDrawing() const;
        bool hasComments() const;
        bool hasThreadedComments() const;
        bool hasTables() const;
 
        XLDrawing& drawing();
        void       addImage(const std::string&    name,
                            const std::string&    data,
                            uint32_t              row,
                            uint32_t              col,
                            uint32_t              width,
                            uint32_t              height,
                            const XLImageOptions& options = XLImageOptions());
 
        void addShape(std::string_view cellReference, const XLVectorShapeOptions& options);
 
        void addTableSlicer(std::string_view       cellReference,
                            const XLTable&         table,
                            std::string_view       columnName,
                            const XLSlicerOptions& options = XLSlicerOptions());
 
        void addPivotSlicer(std::string_view       cellReference,
                            const XLPivotTable&    pivotTable,
                            std::string_view       columnName,
                            const XLSlicerOptions& options = XLSlicerOptions());
 
        void insertImage(const std::string& cellReference, const std::string& imagePath);
 
        void insertImage(const std::string& cellReference, const std::string& imagePath, const XLImageOptions& options);
        void addScaledImage(const std::string& name, const std::string& data, uint32_t row, uint32_t col, double scalingFactor = 1.0);
        std::vector<XLDrawingItem> images();
 
        XLChart addChart(XLChartType type, std::string_view name, uint32_t row, uint32_t col, uint32_t width, uint32_t height);
        XLChart addChart(XLChartType type, const XLChartAnchor& anchor);
 
        std::vector<XLPivotTable> pivotTables();
 
        XLPivotTable addPivotTable(const XLPivotTableOptions& options);
 
        bool deletePivotTable(std::string_view name);
 
        XLVmlDrawing& vmlDrawing();
        XLComments&   comments();
        XLThreadedComments& threadedComments();
        void addNote(std::string_view cellRef, std::string_view text, std::string_view author = "");
 
        void deleteComment(std::string_view cellRef);
 
        void deleteNote(std::string_view cellRef);
 
        XLThreadedComment addComment(std::string_view cellRef, std::string_view text, std::string_view author = "");
 
        XLThreadedComment addReply(const std::string& parentId, const std::string& text, const std::string& author = "");
 
        XLTableCollection& tables();
 
        void                      addHyperlink(std::string_view cellRef, std::string_view url, std::string_view tooltip = "");
        void                      addInternalHyperlink(std::string_view cellRef, std::string_view location, std::string_view tooltip = "");
        [[nodiscard]] bool        hasHyperlink(std::string_view cellRef) const;
        [[nodiscard]] std::string getHyperlink(std::string_view cellRef) const;
        void                      removeHyperlink(std::string_view cellRef);
 
        [[nodiscard]] bool hasPanes() const;
        void               freezePanes(uint16_t column, uint32_t row);
 
        void freezePanes(const std::string& topLeftCell);
 
        void splitPanes(double xSplit, double ySplit, std::string_view topLeftCell = "", XLPane activePane = XLPane::BottomRight);
        void clearPanes();
 
        void                   setZoom(uint16_t scale);
        [[nodiscard]] uint16_t zoom() const;
 
        void insertRowBreak(uint32_t row);
        void insertColBreak(uint16_t col);
        void removeRowBreak(uint32_t row);
        void removeColBreak(uint16_t col);
 
        void                   setSheetViewMode(std::string_view mode);
        [[nodiscard]] std::string sheetViewMode() const;
 
        void                   setShowGridLines(bool show);
        [[nodiscard]] bool     showGridLines() const;
 
        void                   setShowRowColHeaders(bool show);
        [[nodiscard]] bool     showRowColHeaders() const;
 
        void fitToPages(uint32_t fitToWidth, uint32_t fitToHeight);
 
        [[nodiscard]] static std::string makeInternalLocation(std::string_view sheetName, std::string_view cellRef);
 
    private:
        uint16_t         sheetXmlNumber() const;
        XLRelationships& relationships();
 
        XLColor getColor_impl() const;
        void    setColor_impl(const XLColor& color);
        bool    isSelected_impl() const;
        void    setSelected_impl(bool selected);
        bool    isActive_impl() const;
        bool    setActive_impl();
 
        XMLNode prepareSheetViewForPanes();
 
        // ── Row/Column structural shift helpers ──────────────────────────────
        // Each helper updates one subsystem for a row or column shift.
        // rowDelta / colDelta > 0 means insert (push out); < 0 means delete (pull in).
        // fromRow / fromCol is the 1-based first affected coordinate.
 
        [[nodiscard]] static std::string
            shiftCellRef(std::string_view ref, int32_t rowDelta, int32_t colDelta, uint32_t fromRow, uint16_t fromCol);
 
        [[nodiscard]] static std::string
            shiftFormulaRefs(std::string_view formula, int32_t rowDelta, int32_t colDelta, uint32_t fromRow, uint16_t fromCol);
 
        void shiftSheetDataRows(int32_t delta, uint32_t fromRow);
 
        void shiftSheetDataCols(int32_t delta, uint16_t fromCol);
 
        void shiftColsNode(int32_t delta, uint16_t fromCol);
 
        void shiftFormulas(int32_t rowDelta, int32_t colDelta, uint32_t fromRow, uint16_t fromCol);
 
        void shiftDrawingAnchors(int32_t rowDelta, int32_t colDelta, uint32_t fromRow, uint16_t fromCol);
 
        void shiftDataValidations(int32_t rowDelta, int32_t colDelta, uint32_t fromRow, uint16_t fromCol);
 
        void shiftAutoFilter(int32_t rowDelta, int32_t colDelta, uint32_t fromRow, uint16_t fromCol);
 
    private:
        std::unique_ptr<XLWorksheetImpl> m_impl;
        inline static const std::vector<std::string_view>& m_nodeOrder = XLWorksheetNodeOrder;
 
        // O(1) Hint Cache for cell node DOM traversal
        mutable uint32_t                                   m_hintRowNumber{0};
        mutable XMLNode                                    m_hintRowNode{};
        mutable uint16_t                                   m_hintColNumber{0};
        mutable XMLNode                                    m_hintCellNode{};
    };
}    // namespace OpenXLSX
 
#endif