Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Delete Blank Rows and Columns in Excel using 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");
// 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");
// 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");
// 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");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment