Column filtering in excel using java poi

Question

I have a large excel file. I want to filter the column “Mostly used for” for values ​​that are mostly used for mobile devices. Then I need to save the corresponding values ​​in the "Number Series" column in the list. I have a code to start. However, I cannot do the filtering part and store it in a list of arrays. Could you help me here.

I did a bit of work and changed the code. However, I could not satisfy my requirements. I have the following problems -

* The code selects only two columns and displays their contents. Unable to filter: (

* Excel has column names with spaces. Therefore, I get an error message. Since excel is user generated, we do not control column names. How to deal with a column name with spaces?

* Excel has alpha-numeric values, how to deal with them?

Could you help me here.

package com.excel; import java.io.File; import java.io.FileInputStream; import java.math.BigDecimal; import java.io.FileOutputStream; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFWorkbook;*/ import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFSheet; public class Test { public static void main(String[] args) throws Exception { File excel = new File("D:\\FileDownload\\example.xls"); //File excel = new File("D:\\FileDownload\\Sample_Filtered.xls"); FileInputStream fis = new FileInputStream(excel); //XSSFWorkbook wb = new XSSFWorkbook(fis); HSSFWorkbook wb = new HSSFWorkbook(fis); //org.apache.poi.ss.usermodel.Workbook wb = WorkbookFactory.create(fis); HSSFSheet ws = wb.getSheetAt(0); // org.apache.poi.ss.usermodel.Sheet ws = wb.getSheetAt(0); ws.setForceFormulaRecalculation(true); int rowNum = ws.getLastRowNum() + 1; int colNum = ws.getRow(0).getLastCellNum(); int mainlyUsedForHeaderIndex = -1, mobileSeriesHeaderIndex = -1; //Read the headers first. Locate the ones you need HSSFRow rowHeader = ws.getRow(0); for (int j = 0; j < colNum; j++) { HSSFCell cell = rowHeader.getCell(j); String cellValue = cellToString(cell); if("Mainly used for".equalsIgnoreCase(cellValue)) { //if("MainlyFor".equalsIgnoreCase(cellValue)) { mainlyUsedForHeaderIndex = j; } else if("Number Series".equalsIgnoreCase(cellValue)) { //else if("MobileSeries".equalsIgnoreCase(cellValue)) { mobileSeriesHeaderIndex = j; } } if(mainlyUsedForHeaderIndex == -1 || mobileSeriesHeaderIndex == -1) { throw new Exception("Could not find header indexes\n Mainly used for : " + mainlyUsedForHeaderIndex + " | Number Series: " + mobileSeriesHeaderIndex); }else{ System.out.println("Indexes are found!!!"); } //createnew workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("data"); for (int i = 1; i < rowNum; i++) { HSSFRow row = ws.getRow(i); //row = sheet.createRow(rowNum++); String MainlyUsed = cellToString(row.getCell(mainlyUsedForHeaderIndex)); String ForMobile = cellToString(row.getCell(mobileSeriesHeaderIndex)); int cellIndex = 0; XSSFRow newRow = sheet.createRow(i-1); newRow.createCell(cellIndex++).setCellValue(MainlyUsed); newRow.createCell(cellIndex++).setCellValue(ForMobile ); } FileOutputStream fos = new FileOutputStream(new File("D:\\FileDownload\\test1.xlsx")); System.out.println("File generated"); workbook.write(fos); fos.close(); } public static String cellToString(HSSFCell cell) { int type; Object result = null; type = cell.getCellType(); switch (type) {/* case HSSFCell.CELL_TYPE_NUMERIC: result = BigDecimal.valueOf(cell.getNumericCellValue()) .toPlainString(); break; case HSSFCell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: result = ""; break; case HSSFCell.CELL_TYPE_FORMULA: result = cell.getCellFormula();*/ case HSSFCell.CELL_TYPE_BLANK: result=""; break; case HSSFCell.CELL_TYPE_BOOLEAN: // result = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_ERROR: // break; case HSSFCell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: // result = cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: result= cell.getRichStringCellValue(); // result = cell.getStringCellValue(); break; } return result.toString(); } } 

Answer 1

I can fulfill my requirement using a completely different approach.

 package com.excel; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; public class ExcelRead { public static void main(String[] args) throws IOException{ String fileName = "D:\\FileDownload\\example.xls"; String cellContent = "Mainly used for mobile"; int rownr=0; int colnr = 0; //column from which you need data to store in array list InputStream input = new FileInputStream(fileName); HSSFWorkbook wb = new HSSFWorkbook(input); HSSFSheet sheet = wb.getSheetAt(0); List MobileSeries=new ArrayList(); MobileSeries = findRow(sheet, cellContent); if(MobileSeries !=null){ for(Iterator iter=MobileSeries.iterator();iter.hasNext();){ System.out.println(iter.next()); } } //output(sheet, rownr, colnr); finish(); } private static void output(HSSFSheet sheet, int rownr, int colnr) { /* * This method displays the total value of the month */ HSSFRow row = sheet.getRow(rownr); HSSFCell cell = row.getCell(colnr); System.out.println("Your total is: " + cell); } private static List findRow(HSSFSheet sheet, String cellContent) { List MobileSeries=new ArrayList(); for (Row row : sheet) { for (Cell cell : row) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getRichStringCellValue().getString().trim().equals(cellContent)) { //System.out.println("Row numbers are"+row.getRowNum()); int rownumber=row.getRowNum(); //return row.getRowNum(); HSSFRow row1 = sheet.getRow(rownumber); HSSFCell cell1 = row1.getCell(0); MobileSeries.add(cell1); } } } } return MobileSeries; } private static void finish() { System.exit(0); } }