Java Hide and Show Rows or Columns in Excel

With the help of Spire.XLS for Java, we can easily hide and show the rows or columns in the Excel worksheets programmatically. In this article, we will demonstrate how to hide and show rows or columns in Excel in Java applications from the following four parts.

• Hide Excel Rows and Columns
• Show Hidden Rows and Columns in Excel
• Hide Multiple Rows and Columns
• Show All Hidden Rows and Columns

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>4.12.2</version>
    </dependency>
</dependencies>

Hide Rows and Columns

• Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Hide the column using Worksheet.hideColumn(int columnIndex)method.
• Hide the row using worksheet.hideRow(int rowIndex) method. Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;


public class HideRowsColumns {
    public static void main(String[] args) throws Exception {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("Sample.xlsx ");

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Hide the third column
        sheet.hideColumn(3);

        //Hide the third row
        sheet.hideRow(3);

        //Save the document
        wb.saveToFile("HideRowsColumns.xlsx", ExcelVersion.Version2016);
    }
}

Show Hidden Rows and Columns

• Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Show the column using Worksheet.showColumn(int columnIndex)method.
• Show the row using worksheet.showRow(int rowIndex) method.
• Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;


public class ShowRowsColumns {
    public static void main(String[] args) throws Exception {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("HideRowsColumns.xlsx ");

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Unhide the third column
        sheet.showColumn(3);

        //Unhide the third row
        sheet.showRow(3);

        //Save the document
        wb.saveToFile("ShowRowsColumns.xlsx", ExcelVersion.Version2016);
    }
}

Hide Multiple Rows and Columns

• Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Hide multiple columns using Worksheet.hideColumns(int columnIndex, int columnCount)method.
• Hide multiple rows using worksheet.hideRows(int rowIndex, int rowCount) method.
• Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;


public class HideMultiRowsColumns {
    public static void main(String[] args) throws Exception {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("Sample.xlsx ");

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Hide multiple columns
        sheet.hideColumns(2,2);

        //Hide multiple rows
        sheet.hideRows(3,3);

        //Save the document
        wb.saveToFile("HideMultiRowsColumns.xlsx", ExcelVersion.Version2016);
    }
}

Show Multiple Rows and Columns

• Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Loop through the rows and find the hidden rows using Worksheet.getRowIsHide() method.
• Show all hidden rows using Worksheet.showRow(i) method.
• Save the document to file using Workbook.saveToFile() method.

import com.spire.xls.ExcelVersion;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;


public class ShowMultiRowsColumns {
    public static void main(String[] args) throws Exception {

        //Load the sample document
        Workbook wb = new Workbook();
        wb.loadFromFile("HideMultiRowsColumns.xlsx");

        //Get the first worksheet
        Worksheet sheet = wb.getWorksheets().get(0);

        //Traverse all the rows 
        for (int i = 1; i <= sheet.getLastRow(); i++) {

                //detect if the row is hidden
                if (sheet.getRowIsHide(i)) {

                    //Show the hidden rows
                    sheet.showRow(i);
                }
            }

        //Traverse the columns and show all the hidden columns
        for (int j = 1; j <= sheet.getLastColumn(); j++) {
            if (sheet.getColumnIsHide(j)) {
                sheet.showColumn(j);
            }

            //Save the document
            wb.saveToFile("ShowMultiRowsColumns.xlsx", ExcelVersion.Version2016);
        }
    }
}