5 Ways to Add Columns in Excel Using Java

In today's data-driven business environment, Excel remains one of the most versatile tools for data analysis, manipulation, and reporting. While Excel itself provides a user-friendly GUI for everyday tasks, integrating Excel with programming languages like Java can enhance automation, accuracy, and efficiency. Here's how you can add columns to Excel files using Java through five different methods:

Using Apache POI

Apache POI is a powerful library for working with Microsoft documents including Excel. To add a column using Apache POI, you’ll need to follow these steps:

• Download and add the Apache POI jars to your project’s classpath.
• Read the existing Excel file.
• Create a new column by shifting the existing columns or by inserting a column at a specific position.
• Save the changes back to an Excel file.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelColumnAdder {
    public static void addColumn(Workbook workbook, int sheetIndex, int columnIndex) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        sheet.shiftColumns(columnIndex, sheet.getRow(0).getLastCellNum(), 1, true, true);

    for (Row row : sheet) {
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue("New Column Value");
    }
}
public static void main(String[] args) throws Exception {
    Workbook workbook = new XSSFWorkbook("input.xlsx");
    addColumn(workbook, 0, 3);
    FileOutputStream fileOut = new FileOutputStream("output.xlsx");
    workbook.write(fileOut);
    fileOut.close();
    workbook.close();
}


}

JXL (Java Excel API)

JXL is another option for manipulating Excel files, though it’s limited to handling .xls files only. Here’s how to add a column:

• Add the JXL library to your project.
• Open the workbook and select the sheet you want to modify.
• Shift existing columns to make room for a new column.
• Insert the new column with desired values.
• Write changes back to a file.

import jxl.;
import jxl.write.;
import java.io.*;

public class JxlColumnAdder {
    public static void addColumn(File inputFile, File outputFile, int sheetIndex, int columnIndex) throws Exception {
        Workbook existingWorkbook = Workbook.getWorkbook(inputFile);
        WritableWorkbook newWorkbook = Workbook.createWorkbook(outputFile, existingWorkbook);
        WritableSheet sheet = newWorkbook.getSheet(sheetIndex);

    sheet.insertColumn(columnIndex);

    for (int row = 0; row < sheet.getRows(); row++) {
        Label label = new Label(columnIndex, row, "New Column Value");
        sheet.addCell(label);
    }

    newWorkbook.write();
    newWorkbook.close();
}


}

Using Aspose Cells

Aspose.Cells provides robust Excel manipulation features. Here’s how to add a column:

• Include Aspose.Cells in your project.
• Open an Excel file.
• Insert a new column.
• Set values or formulas for the new column.
• Save the modified file.

import com.aspose.cells.*;

public class AsposeColumnAdder {
    public static void addColumn(String filePath, int sheetIndex, int columnIndex) throws Exception {
        Workbook workbook = new Workbook(filePath);
        Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
        worksheet.getCells().insertColumn(columnIndex);

    for (int i = 0; i < worksheet.getCells().getMaxDataRow(); i++) {
        worksheet.getCells().get(i, columnIndex).putValue("New Column Value");
    }

    workbook.save(filePath, SaveFormat.XLSX);
}


}

Using JExcelApi

Similar to JXL, JExcelApi works with .xls files. Here’s the method:

• Set up the JExcelApi library.
• Read the workbook and sheet.
• Shift existing columns.
• Create the new column with content.
• Save modifications.

import jxl.write.;
import jxl.;

public class JExcelApiColumnAdder {
    public static void addColumn(File inputFile, File outputFile, int sheetIndex, int columnIndex) throws Exception {
        Workbook existingWorkbook = Workbook.getWorkbook(new FileInputStream(inputFile));
        WritableWorkbook workbook = Workbook.createWorkbook(new FileOutputStream(outputFile), existingWorkbook);
        WritableSheet sheet = workbook.getSheet(sheetIndex);

    for (int col = sheet.getColumns() - 1; col >= columnIndex; col--) {
        for (int row = 0; row < sheet.getRows(); row++) {
            Cell cell = sheet.getCell(col, row);
            Label label = new Label(col + 1, row, cell.getContents());
            sheet.addCell(label);
        }
    }

    for (int row = 0; row < sheet.getRows(); row++) {
        Label label = new Label(columnIndex, row, "New Column Value");
        sheet.addCell(label);
    }

    workbook.write();
    workbook.close();
}


}

Using Excel API with XML Editing

While not common, you can directly manipulate Excel files by editing the underlying XML structure. Here’s a basic outline:

• Extract the Excel file’s zip contents.
• Modify the worksheet XML to insert the column.
• Re-zip the contents back into an Excel file.

⚠️ Note: Editing XML files directly can be error-prone and should be used cautiously. Always backup your data before attempting such operations.

In conclusion, adding columns in Excel through Java can be accomplished in several ways, each with its advantages. Apache POI stands out for its versatility and robustness, while options like JXL or JExcelApi might be preferred for their simplicity with older Excel formats. Aspose.Cells provides a commercial-grade option for extensive Excel manipulation. Understanding these methods allows developers to automate and customize Excel data handling in line with specific project requirements.

Which method is best for adding columns to Excel?

+

Apache POI is generally recommended for its comprehensive features and support for newer Excel formats. However, if you are dealing with older .xls files or prefer simplicity, JXL or JExcelApi might be more suitable.

Can these methods modify existing columns instead of just adding new ones?

+

Yes, all these methods allow for reading, modifying, and writing back to Excel files, which means you can also alter existing data in columns, rename columns, or change their values.

What are the performance implications of these methods?

+

Performance can vary based on the size of the Excel file, the complexity of operations, and the library used. Apache POI tends to be performant for most operations but requires more memory for large files. For simpler operations, JXL or JExcelApi might perform faster due to their focused scope on older Excel formats.