Converting XLSM to XLS in Java

In the realm of data handling and spreadsheet management, Java offers powerful libraries to manipulate Excel files. The need to convert XLSM (Excel Macro - Enabled Workbook) files to XLS (Excel Binary Workbook) format often arises in various business scenarios. XLSM files can contain VBA macros, which might not be required or compatible in certain environments, while XLS is a more traditional and widely supported format. This blog post will guide you through the process of converting XLSM to XLS using Java, covering core concepts, typical usage scenarios, common pitfalls, and best practices.

Table of Contents#

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

Core Concepts#

XLSM and XLS Formats#

  • XLSM: This is an Excel file format introduced in Excel 2007. It supports VBA macros, which are used to automate tasks within the spreadsheet. XLSM files have the .xlsm extension.
  • XLS: The XLS format is the older binary file format used by Excel. It does not support macros. XLS files have the .xls extension.

Apache POI Library#

Apache POI is a popular Java library for working with Microsoft Office file formats, including Excel. It provides classes and methods to read, write, and manipulate Excel files. To convert XLSM to XLS, we will use the HSSF (Horrible Spreadsheet Format) and XSSF (XML Spreadsheet Format) components of Apache POI.

Typical Usage Scenarios#

  • Compatibility: When sharing Excel files with users who have older versions of Excel that do not support the XLSM format, converting to XLS ensures compatibility.
  • Security: Removing macros from a file can enhance security, especially when sharing files externally. Macros can potentially contain malicious code.
  • Data Transfer: Some systems or applications may only accept the XLS format for data import. Converting XLSM to XLS allows seamless data transfer.

Prerequisites#

  • Java Development Kit (JDK) installed on your system.
  • Apache POI library added to your project. You can add the following dependencies to your pom.xml if you are using Maven:
<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
</dependencies>

Code Example#

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
 
public class XLSMtoXLSConverter {
 
    public static void main(String[] args) {
        String inputFilePath = "input.xlsm";
        String outputFilePath = "output.xls";
 
        try {
            // Read the XLSM file
            FileInputStream inputStream = new FileInputStream(inputFilePath);
            Workbook workbook = new XSSFWorkbook(inputStream);
 
            // Create a new XLS workbook
            Workbook newWorkbook = new HSSFWorkbook();
 
            // Copy sheets from the XLSM workbook to the XLS workbook
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sourceSheet = workbook.getSheetAt(i);
                Sheet newSheet = newWorkbook.createSheet(sourceSheet.getSheetName());
 
                // Copy rows and cells
                for (int j = 0; j <= sourceSheet.getLastRowNum(); j++) {
                    Row sourceRow = sourceSheet.getRow(j);
                    if (sourceRow != null) {
                        Row newRow = newSheet.createRow(j);
                        for (int k = 0; k < sourceRow.getLastCellNum(); k++) {
                            Cell sourceCell = sourceRow.getCell(k);
                            if (sourceCell != null) {
                                Cell newCell = newRow.createCell(k);
                                newCell.setCellValue(sourceCell.getStringCellValue());
                            }
                        }
                    }
                }
            }
 
            // Write the new XLS workbook to a file
            FileOutputStream outputStream = new FileOutputStream(outputFilePath);
            newWorkbook.write(outputStream);
 
            // Close the streams and workbooks
            outputStream.close();
            newWorkbook.close();
            workbook.close();
            inputStream.close();
 
            System.out.println("Conversion successful!");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Code Explanation#

  1. Reading the XLSM File: We use FileInputStream to read the XLSM file and create an XSSFWorkbook object to represent the workbook.
  2. Creating a New XLS Workbook: We create a new HSSFWorkbook object to represent the XLS workbook.
  3. Copying Sheets, Rows, and Cells: We iterate through each sheet, row, and cell in the XLSM workbook and copy them to the XLS workbook.
  4. Writing the XLS Workbook: We use FileOutputStream to write the new XLS workbook to a file.
  5. Closing Resources: We close all the streams and workbooks to release system resources.

Common Pitfalls#

  • Macro Loss: Converting from XLSM to XLS will result in the loss of VBA macros. Make sure this is acceptable in your use case.
  • Formatting Loss: Some advanced formatting features available in XLSM may not be fully supported in XLS. For example, conditional formatting and certain chart types may not be preserved.
  • Data Limitations: XLS has a maximum limit of 65,536 rows and 256 columns, while XLSM supports much larger datasets. If your XLSM file exceeds these limits, data may be lost during conversion.

Best Practices#

  • Backup the Original File: Always keep a backup of the original XLSM file before conversion in case you need to access the macros or advanced formatting later.
  • Test the Conversion: Before performing the conversion on a large number of files, test the process on a sample file to ensure that the data and formatting are transferred correctly.
  • Handle Exceptions: Use proper exception handling in your code to handle errors such as file not found, insufficient permissions, or memory issues.

Conclusion#

Converting XLSM to XLS in Java using the Apache POI library is a straightforward process, but it comes with certain limitations and considerations. By understanding the core concepts, typical usage scenarios, common pitfalls, and best practices, you can effectively convert XLSM files to XLS and meet your business requirements.

FAQ#

Q1: Can I convert XLS to XLSM using the same approach?#

A1: No, the process is different. To convert XLS to XLSM, you would need to create an XSSFWorkbook and copy the data from the HSSFWorkbook to it.

Q2: Will the converted XLS file have the same file size as the original XLSM file?#

A2: Not necessarily. The file size may change due to differences in the file formats and the loss of macros and advanced formatting.

Q3: Can I convert a password-protected XLSM file?#

A3: Yes, but you need to provide the password when opening the XLSM file using Apache POI. You can use the XSSFWorkbook constructor that accepts a password parameter.

References#

2025-10