Delete Blank Rows and Columns in Excel using Java

DeleteBlankRowsColumnsInExcel_Java.md

Learn how to delete blank rows and columns in Excel using Java

The following topics are covered in this article:

1. Java API to Delete Blank Rows and Columns in Excel
2. Delete Blank Rows in Excel using Java
3. Delete Blank Columns in Excel using Java
4. Update References Automatically while Deleting Blank Rows and Columns

DeleteBlankRowsColumnsInExcel_Java_DeleteBlankColumns.java

// Open an existing excel file.
Workbook workbook = new Workbook("C:\\Files\\Cells\\sample_rows_cols.xlsx");

// Get the worksheets collection in the spreadsheet.
WorksheetCollection sheets = workbook.getWorksheets();

// Get first Worksheet from WorksheetCollection by index.
Worksheet sheet = sheets.get(2);

// Delete the blank columns.
sheet.getCells().deleteBlankColumns(options);

// Save the updated excel file.
workbook.save("C:\\Files\\Cells\\output_DeleteBlankColumns.xlsx");	

DeleteBlankRowsColumnsInExcel_Java_DeleteBlankRows.java

// Load an existing excel file.
Workbook workbook = new Workbook("C:\\Files\\Cells\\sample_rows_cols.xlsx");

// Get the worksheets collection in the spreadsheet.
WorksheetCollection sheets = workbook.getWorksheets();

// Get first Worksheet from WorksheetCollection by index.
Worksheet sheet = sheets.get(0);
// Or by name.
// Worksheet sheet = sheets.get("Sheet1");

// Delete the Blank Rows from the worksheet.
sheet.getCells().deleteBlankRows();

// Save the updated excel file.
workbook.save("C:\\Files\\Cells\\output.xlsx");

DeleteBlankRowsColumnsInExcel_Java_DeleteBlankRowsFromWorkbook.java

// Open an existing excel file.
Workbook workbook = new Workbook("C:\\Files\\Cells\\sample_rows_cols.xlsx");

// Get worksheets collection
WorksheetCollection worksheets = workbook.getWorksheets();

// Iterate over the worksheets.
for (int i=0; i<worksheets.getCount(); i++)
{
    // Access worksheets one by one
    Worksheet sheet = worksheets.get(i);
  
    // Delete the Blank Rows from the worksheet.
    sheet.getCells().deleteBlankRows();
}

// Save the updated excel file.
workbook.save("C:\\Files\\Cells\\output.xlsx");

DeleteBlankRowsColumnsInExcel_Java_UpdateReferencesAutomatically.java

// Open an existing excel file.
Workbook workbook = new Workbook("C:\\Files\\Cells\\sample_rows_cols.xlsx");

// Get the worksheets collection in the spreadsheet.
WorksheetCollection sheets = workbook.getWorksheets();

// Get first Worksheet from WorksheetCollection by index.
Worksheet sheet = sheets.get(0);

// This option will ensure the references (in formulas, charts)
// are updated while deleting blank rows.
DeleteOptions options = new DeleteOptions();
options.setUpdateReference(true);

// Delete the Blank Rows from the worksheet.
sheet.getCells().deleteBlankRows(options);

// Save the updated excel file.
workbook.save("C:\\Files\\Cells\\output_UpdateReferencesAutomatically.xlsx");