CODEWITHSUNDEEP
javaapache-poi
Sritam Jagadev
Sritam Jagadev

Reputation: 975

Remove considering Blank while filtering Columns in Excell in POI

Hi I am using autoFilter for filtering all Columns in Excell,it's considering all the blank rows (rows beyond the total no of rows having data) while filtering. but, i want to avoid considering Blank rows while i am trying to filter any column.

I am using below code, sheet.setAutoFilter(CellRangeAddress.valueOf("A8:L13"));

I am currently getting like this I am currently getting like this

I need like this I need like this

Upvotes: 2

Views: 1052

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

The AutoFilter [ ] (Blanks) option only disappears if there are not blank cells in the filter range. So we need setting the AutoFilter cell range only to the used range.

Example:

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

import java.util.Random;
import java.io.*;

class AutoFilterTest {

 private static void setCellData(Sheet sheet) {

  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);
  cell.setCellValue("Name");
  cell = row.createCell(1);
  cell.setCellValue("Value1");
  cell = row.createCell(2);
  cell.setCellValue("Value2");
  cell = row.createCell(3);
  cell.setCellValue("City");

  Random rnd = new Random();

  for (int r = 1; r < 10 + rnd.nextInt(100); r++) {
   row = sheet.createRow(r);
   cell = row.createCell(0);
   cell.setCellValue("Name " + ((r-1) % 4 + 1));
   cell = row.createCell(1);
   cell.setCellValue(r * rnd.nextDouble());
   cell = row.createCell(2);
   cell.setCellValue(r * rnd.nextDouble());
   cell = row.createCell(3);
   cell.setCellValue("City " + ((r-1) % 3 + 1));  
  }

 }

 public static void main(String[] args) {
  try {
   XSSFWorkbook wb = new XSSFWorkbook();
   XSSFSheet sheet = wb.createSheet();

   //create random rows of data
   setCellData(sheet);

   for (int c = 0; c < 4; c++) sheet.autoSizeColumn(c);

   int lastRow = sheet.getLastRowNum();
   sheet.setAutoFilter(new CellRangeAddress(0, lastRow, 0, 3));

   wb.write(new FileOutputStream("AutoFilterTest.xlsx"));
   wb.close();
  } catch (Exception e) {
    e.printStackTrace();
  }
 }
}

Upvotes: 1

Related Questions