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>15.6.3</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>15.6.3</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.