Last Updated: 

Convert XLSB to XLSX in Java

In the world of data processing and analysis, Microsoft Excel files are widely used. There are different Excel file formats, such as XLSB (Excel Binary Workbook) and XLSX (Excel Open XML Workbook). The XLSB format is a binary format, which is generally faster to read and write, especially for large files. On the other hand, the XLSX format is an XML-based format, which is more open and human - readable. In Java, there are scenarios where you might need to convert an XLSB file to an XLSX file. For example, you may have a legacy system that generates XLSB files, but your new data analytics tool only supports XLSX. In this blog post, we will explore how to perform this conversion using Java, along with core concepts, usage scenarios, common pitfalls, and best practices.

Table of Contents#

  1. Core Concepts
  2. Typical Usage Scenarios
  3. Java Code Example for Conversion
  4. Common Pitfalls
  5. Best Practices
  6. Conclusion
  7. FAQ
  8. References

Core Concepts#

XLSB (Excel Binary Workbook)#

XLSB is a binary file format introduced in Excel 2007. It stores data in a binary format, which makes it more compact and faster to read and write compared to the XML-based XLSX format. Binary files are generally more efficient for handling large amounts of data as they require less disk space and fewer processing resources.

XLSX (Excel Open XML Workbook)#

XLSX is an XML-based file format also introduced in Excel 2007. It is based on the Open XML standard, which means it stores data in a structured XML format. This format is more open and human-readable, making it easier to integrate with other systems and tools. It is also more compatible with a wider range of software applications.

Apache POI#

Apache POI is a popular Java library that provides APIs for working with Microsoft Office file formats, including Excel. It primarily supports XLSX format natively, with XLSB support available through the XSSFWorkbook interface via OPCPackage. We will use Apache POI to perform the conversion from XLSB to XLSX.

Typical Usage Scenarios#

  1. Data Integration: When integrating data from different sources, some legacy systems may generate XLSB files, while the new data warehousing or analytics tools only support XLSX. Converting the files can ensure seamless data flow.
  2. Cross-platform Compatibility: Some platforms or applications may not support the XLSB format. Converting to XLSX can make the data accessible across a wider range of systems.
  3. Data Sharing: XLSX files are more widely supported and can be easily shared with colleagues or partners who may not have the necessary software to open XLSB files.

Java Code Example for Conversion#

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
 
public class XlsbToXlsxConverter {
 
    public static void convertXlsbToXlsx(String xlsbFilePath, String xlsxFilePath) {
        try (FileInputStream fis = new FileInputStream(new File(xlsbFilePath));
             OPCPackage pkg = OPCPackage.open(fis);
             XSSFWorkbook xlsbWorkbook = new XSSFWorkbook(pkg);
             XSSFWorkbook xlsxWorkbook = new XSSFWorkbook()) {
 
            // Iterate through each sheet in the XLSB workbook
            for (int i = 0; i < xlsbWorkbook.getNumberOfSheets(); i++) {
                Sheet xlsbSheet = xlsbWorkbook.getSheetAt(i);
                // Create a corresponding sheet in the XLSX workbook
                Sheet xlsxSheet = xlsxWorkbook.createSheet(xlsbSheet.getSheetName());
 
                // Iterate through each row in the XLSB sheet
                for (Row xlsbRow : xlsbSheet) {
                    Row xlsxRow = xlsxSheet.createRow(xlsbRow.getRowNum());
 
                    // Iterate through each cell in the XLSB row
                    for (Cell xlsbCell : xlsbRow) {
                        Cell xlsxCell = xlsxRow.createCell(xlsbCell.getColumnIndex());
                        // Set the cell value in the XLSX cell based on cell type
                        switch (xlsbCell.getCellType()) {
                            case STRING:
                                xlsxCell.setCellValue(xlsbCell.getStringCellValue());
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(xlsbCell)) {
                                    xlsxCell.setCellValue(xlsbCell.getDateCellValue());
                                } else {
                                    xlsxCell.setCellValue(xlsbCell.getNumericCellValue());
                                }
                                break;
                            case BOOLEAN:
                                xlsxCell.setCellValue(xlsbCell.getBooleanCellValue());
                                break;
                            case FORMULA:
                                xlsxCell.setCellFormula(xlsbCell.getCellFormula());
                                break;
                            case BLANK:
                                xlsxCell.setBlank();
                                break;
                            case ERROR:
                                xlsxCell.setCellErrorValue(xlsbCell.getErrorCellValue());
                                break;
                            default:
                                xlsxCell.setCellValue(xlsbCell.getStringCellValue());
                                break;
                        }
                    }
                }
            }
 
            // Write the XLSX workbook to the output file
            try (FileOutputStream fos = new FileOutputStream(new File(xlsxFilePath))) {
                xlsxWorkbook.write(fos);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
 
    public static void main(String[] args) {
        String xlsbFilePath = "input.xlsb";
        String xlsxFilePath = "output.xlsx";
        convertXlsbToXlsx(xlsbFilePath, xlsxFilePath);
    }
}

Explanation of the Code#

  1. Import Statements: We import the necessary classes from the Apache POI library for working with Excel files.
  2. convertXlsbToXlsx Method: This method takes the file paths of the input XLSB file and the output XLSX file as parameters.
    • It first opens the XLSB file using OPCPackage and creates an XSSFWorkbook object from it.
    • Then it creates a new XSSFWorkbook object for the output XLSX file.
    • It iterates through each sheet, row, and cell in the XLSB workbook and copies the data to the corresponding sheet, row, and cell in the XLSX workbook.
    • Finally, it writes the XLSX workbook to the output file.
  3. main Method: This method calls the convertXlsbToXlsx method with sample file paths.

Common Pitfalls#

  1. Memory Issues: If the XLSB file is very large, loading the entire workbook into memory can cause out-of-memory errors. You may need to process the file in chunks or use a streaming approach.
  2. Data Loss: Some advanced Excel features, such as macros or complex formatting, may not be fully supported during the conversion. You may need to handle these cases separately.
  3. Dependency Management: Make sure you have the correct versions of the Apache POI library and its dependencies. Incompatible versions can lead to runtime errors.

Best Practices#

  1. Error Handling: Always implement proper error handling in your code to handle exceptions such as file not found, invalid file format, or out-of-memory errors.
  2. Resource Management: Use try-with-resources statements to ensure that file streams and workbooks are properly closed after use.
  3. Testing: Test the conversion process with different types of XLSB files, including files with different data types, formatting, and sizes, to ensure the conversion is accurate.

Conclusion#

Converting an XLSB file to an XLSX file in Java can be easily achieved using the Apache POI library. By understanding the core concepts, typical usage scenarios, and following best practices, you can perform this conversion effectively in real-world situations. However, you need to be aware of the common pitfalls and handle them appropriately to ensure a smooth conversion process.

FAQ#

Q1: Can I convert XLSB files with macros to XLSX?#

A: Macros are not supported in the XLSX format. You will lose the macros during the conversion. If you need to preserve the macros, you may need to use a different approach or keep the file in the XLSB format.

Q2: How can I handle large XLSB files without running out of memory?#

A: You can use a streaming approach provided by Apache POI. Instead of loading the entire workbook into memory, you can process the file row by row or in chunks.

Q3: Do I need to have Microsoft Excel installed on the machine to perform the conversion?#

A: No, you do not need to have Microsoft Excel installed. The conversion is done using the Apache POI library, which is a Java-based solution.

References#

  1. Apache POI Official Website: https://poi.apache.org/
  2. Microsoft Excel File Formats Documentation: https://support.microsoft.com/en-us/office/file-formats-that-are-supported-in-excel - 0943ff2c - 6014 - 4e8d - af60 - 402d2311f686
2025-10