Show and Hide Rows, Columns, and Scroll Bars with C++

Aspose.Cells provides ways to control the visibility of rows, columns, and scroll bars of a worksheet.

Show and Hide Rows and Columns

Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a Worksheets collection that allows developers to access each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection that represents all cells in the worksheet. The Cells collection provides several methods for managing rows or columns in a worksheet. A few of these are discussed below.

Show Rows and Columns

Developers can show any hidden row or column by calling the UnhideRow and UnhideColumn methods of the Cells collection respectively. Both methods take two parameters:

Row or column index - the index of a row or column that is used to show the specific row or column.
Row height or column width - the row height or column width assigned to the row or column after unhiding.

#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Path of input excel file
    U16String inputFilePath = srcDir + u"book1.xls";

    // Path of output excel file
    U16String outputFilePath = outDir + u"output.xls";

    // Create workbook
    Workbook workbook(inputFilePath);

    // Accessing the first worksheet in the Excel file
    Worksheet worksheet = workbook.GetWorksheets().Get(0);

    // Unhiding the 3rd row and setting its height to 13.5
    worksheet.GetCells().UnhideRow(2, 13.5);

    // Unhiding the 2nd column and setting its width to 8.5
    worksheet.GetCells().UnhideColumn(1, 8.5);

    // Save the modified Excel file
    workbook.Save(outputFilePath);

    std::cout << "Excel file modified and saved successfully!" << std::endl;

    Aspose::Cells::Cleanup();
}

While making a hidden column visible, if you need to restore it to previously assigned width or to its original width, please unhide the column with a negative width. For example: worksheet->GetCells()->UnhideColumn(5, -1).

Hide Rows and Columns

Developers can hide a row or column by calling the HideRow and HideColumn methods of the Cells collection respectively. Both methods take the row and column index as a parameter to hide the specific row or column.

#include <iostream>
#include <memory>
#include "Aspose.Cells.h"

using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Path of input excel file
    U16String inputFilePath = srcDir + u"book1.xls";

    // Path of output excel file
    U16String outputFilePath = outDir + u"output.out.xls";

    // Create workbook
    Workbook workbook(inputFilePath);

    // Access the first worksheet in the Excel file
    Worksheet worksheet = workbook.GetWorksheets().Get(0);

    // Hide the 3rd row of the worksheet
    worksheet.GetCells().HideRow(2);

    // Hide the 2nd column of the worksheet
    worksheet.GetCells().HideColumn(1);

    // Save the modified Excel file
    workbook.Save(outputFilePath);

    std::cout << "Rows and columns hidden successfully!" << std::endl;

    Aspose::Cells::Cleanup();
}

It is also possible to hide a row or column by setting the row height or column width to 0 respectively.

Hide Multiple Rows and Columns

Developers can hide multiple rows or columns at once by calling the HideRows and HideColumns methods of the Cells collection respectively. Both methods take the starting row or column index and the number of rows or columns that should be hidden as parameters.

#include <iostream>
#include <memory>
#include "Aspose.Cells.h"

using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Path of input Excel file
    U16String inputFilePath = srcDir + u"book1.xls";

    // Create workbook
    Workbook workbook(inputFilePath);

    // Access the first worksheet in the Excel file
    Worksheet worksheet = workbook.GetWorksheets().Get(0);

    // Hide 3, 4, and 5 rows in the worksheet
    worksheet.GetCells().HideRows(2, 3);

    // Hide 2 and 3 columns in the worksheet
    worksheet.GetCells().HideColumns(1, 2);

    // Save the modified Excel file
    workbook.Save(outDir + u"outputxls");

    std::cout << "Rows and columns hidden successfully!" << std::endl;

    Aspose::Cells::Cleanup();
}

Show and Hide Scroll Bars

Scroll bars are used to navigate the contents of any file. Normally, there are two kinds of scroll bars:

Vertical scroll bars
Horizontal scroll bars

Microsoft Excel also provides horizontal and vertical scroll bars so that users can scroll through worksheet contents. Using Aspose.Cells, developers can control the visibility of both types of scroll bars in Excel files.

Controlling the Visibility of Scroll Bars

Aspose.Cells provides a class, Workbook, that represents an Excel file. The Workbook class provides a wide range of properties and methods for managing an Excel file. To control the visibility of scroll bars, use the Workbook class' WorkbookSettings.IsVScrollBarVisible and WorkbookSettings.IsHScrollBarVisible properties. WorkbookSettings.IsVScrollBarVisible and WorkbookSettings.IsHScrollBarVisible are Boolean properties, which means that these properties can only store true or false values.

Making Scroll Bars Visible

Make scroll bars visible by setting the Workbook class' WorkbookSettings.IsVScrollBarVisible or WorkbookSettings.IsHScrollBarVisible property to true.

Hiding Scroll Bars

Hide scroll bars by setting the Workbook class' WorkbookSettings.IsVScrollBarVisible or WorkbookSettings.IsHScrollBarVisible property to false.

Sample Code

Below is a complete code that opens an Excel file, book1.xls, hides both scroll bars, and then saves the modified file as output.xls.

#include <iostream>
#include <fstream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Path of input Excel file
    U16String inputFilePath = srcDir + u"book1.xls";

    // Path of output Excel file
    U16String outputFilePath = outDir + u"output.xls";

    // Create workbook from the input file
    Workbook workbook(inputFilePath);

    // Hide the vertical scroll bar of the Excel file
    workbook.GetSettings().SetIsVScrollBarVisible(false);

    // Hide the horizontal scroll bar of the Excel file
    workbook.GetSettings().SetIsHScrollBarVisible(false);

    // Save the modified Excel file
    workbook.Save(outputFilePath);

    std::cout << "Scroll bars hidden successfully and file saved!" << std::endl;

    Aspose::Cells::Cleanup();
}

Show and Hide Worksheets and Tabs with C++ Show and Hide Gridlines and Row Column Headers with C++