In Microsoft Excel, suppose a workbook contains lots of worksheets and you need to count them, you can use the Sheets Function, the Define Name Command or a simple VBA code to achieve it. Within this tutorial, I’ll show you how to programmatically count the number of worksheets in Excel using Spire.XLS for Java.

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>14.4.1</version>
    </dependency>
</dependencies>
    

Count the Number of Worksheets in Excel

Spire.XLS for Java supports counting the number of worksheets in Excel using the getCount() method provided by the IWorksheets interface. The following are detailed steps.

• Create a Workbook instance.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a collection of worksheets using Workbook.getWorksheets() method and obtain the number of worksheets in the collection using the getCount() method.

import com.spire.xls.Workbook;

public class CountNumberOfWorsheets {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load a sample Excel file
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");

        //Get the number of worksheets
        int sheetCount=workbook.getWorksheets().getCount();

        //Output the result
        System.out.println("The number of sheets is "+sheetCount);
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Sometimes we need to hide some rows and columns in Excel worksheets so that the data appears completely on one screen. At other times, we need to show all the hidden rows and columns to view the data completely. In this article, you will learn 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>14.4.1</version>
    </dependency>
</dependencies>
    

Hide Rows and Columns

The detailed steps are listed as below.

• Create a Workbook instance and load a sample Excel document using Workbook.loadFromFile() method.
• Get a specified worksheet using Workbook.getWorksheets().get() method.
• Hide a specific column using Worksheet.hideColumn(int columnIndex)method.
• Hide a specific 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 a hidden column using Worksheet.showColumn(int columnIndex)method.
• Show a hidden 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("Sample01.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);
        }
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Office Open XML (also referred to as OOXML) is a zipped, XML-based format for Excel, Word and Presentation documents. Sometimes, you may need to convert an Excel file to Office Open XML in order to make it readable on various applications and platforms. Likewise, you might also want to convert Office Open XML to Excel for data calculations. In this article, you will learn how to Convert Excel to Office Open XML and vice versa in Java using Spire.XLS for Java library.

• Convert Excel to Office Open XML in Java
• Convert Office Open XML to Excel in Java

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>14.4.1</version>
    </dependency>
</dependencies>
    

Convert Excel to Office Open XML in Java

The following are the steps to convert an Excel file to Office Open XML:

• Create an instance of Workbook class.
• Load an Excel file using Workbook.loadFromFile() method.
• Call Workbook.saveAsXml() method to save the Excel file as Office Open XML.

import com.spire.xls.Workbook;

public class ExcelToOpenXML {
    public static void main(String []args){
        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

        //Save as Office Open XML file format
        workbook.saveAsXml("ToXML.xml");
    }
}

Convert Office Open XML to Excel in Java

The following are the steps to convert an Office Open XML file to Excel:

• Create an instance of Workbook class.
• Load an Office Open XML file using Workbook.loadFromXml() file.
• Call Workbook.saveToFile() method to save the Office Open XML file as Excel.

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

public class OpenXmlToExcel {
    public static void main(String []args){
        //Create an instance of Workbook class
        Workbook workbook = new Workbook();
        //Load an Office Open XML file
        workbook.loadFromXml("ToXML.xml");

        //Save as Excel XLSX file format
        workbook.saveToFile("ToExcel.xlsx", ExcelVersion.Version2016);
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

Subtotal is a built-in function in Microsoft Excel that enables you to quickly calculate a range of data using a summary function, such as SUM, AVERAGE, COUNT, or MIN. This article will demonstrate how to add subtotals to a data range in Excel in Java using Spire.XLS for Java.

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>14.4.1</version>
    </dependency>
</dependencies>
    

Add Subtotals to a Data Range

The XlsWorksheet.subtotal() method is used to add subtotals to a data range. It accepts the following parameters:

• IXLSRange: the specific data range.
• int: the column index (zero-based) that you want to base the subtotals on.
• int[]: an array of column indexes (zero-based) on which the subtotals are calculated.
• SubtotalTypes: the function (SUM, AVERAGE etc.) used to calculate the subtotals.
• boolean: Indicates whether to replace existing subtotals.
• boolean: Indicates whether to insert page breaks between groups.
• boolean: Indicates whether to add summary rows below data.

The following are the steps to add subtotals to a data range:

• Create an instance of Workbook class.
• Load an Excel file using Workbook.loadFromFile() method.
• Get the desired worksheet using Workbook.getWorksheets().get() method.
• Access the range that you wish to subtotal using Worksheet.getCellRange() method.
• Add subtotals to the range using XlsWorksheet.subtotal() method.
• Save the result file using Workbook.saveToFile() method.

import com.spire.xls.*;

public class AddSubtotalsToDataRange {
    public static void main(String []args){

        //Create a Workbook instance
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Report.xlsx");

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

        //Access the range that contains data you wish to subtotal
        CellRange range = sheet.getCellRange("A2:C11");

        //Add subtotals to the range, the function is Sum and it will be applied to the 3rd column in the range 
        sheet.subtotal(range, 0, new int[] { 2 }, SubtotalTypes.Sum, true, false, true);

        //Save the result file
        workbook.saveToFile("AddSubtotal.xlsx", ExcelVersion.Version2016);
        workbook.dispose();
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.