I have a .xls file with only one sheet called "clients" and it contains data as it follows:

Name | Surname | Full name | Date of birth | City

I need to get all the values in the "Full name" column and I need to print them in a jComboBox. Is there a way of doing that? Thank you in advance

CodePudding user response：

To read Excel files using Apache POI one needs creating a Workbook object from a Excel file. Then one chooses the needed sheet and iterates over the needed rows. Then one gets the cell values from the needed column.

See Busy Developers' Guide to HSSF and XSSF Features for how to use Apache POI.

The simplest way to get the cell values is using DataFormatter of Apache POI. DataFormatter.formatCellValue gets the cell values as string in the same format as shown in Excel. Using current apache poi 5.2.2, DataFormatter.setUseCachedValuesForFormulaCells can be set true. Then no formula evaluation is needed when cell values are formula results.

While iterating over the rows and getting cells one needs to know that totally empty rows are not stored in sheet's data. So Sheet.getRow might return null. Also unused cells are not stored. So Row.getCell also might return null. That's why null-checks are necessary. And if empty cells shall not matter, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL can be used in Row.getCell. Then empty cells also get returned as null.

The data could be stored in a data structure which then can be used to construct a JComboBox. A Vector would offer, as there is a constructor public JComboBox(Vector items).

Complete example:

import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;
import java.util.Vector; 
import javax.swing.*;

public class ReadExcelColumnToVector {
    
 private static final DataFormatter dataFormatter = new DataFormatter(java.util.Locale.US);
 private Workbook workbook; 
 
 public ReadExcelColumnToVector(String filePath) throws Exception {
  this.workbook = WorkbookFactory.create(new FileInputStream(filePath));
  this.dataFormatter.setUseCachedValuesForFormulaCells(true);
 }
        
 public Vector<String> getData(int sheetIndex, int firstRow, int dataCol) {
  Sheet sheet = this.workbook.getSheetAt(sheetIndex);   
  Vector<String> data  = new Vector<String>();
  String cellValue = "";
  for (int r = firstRow; r <= sheet.getLastRowNum(); r  ) {
   Row row = sheet.getRow(r);
   if (row != null) {
    Cell cell = row.getCell(dataCol, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
    if (cell != null) {
     cellValue = this.dataFormatter.formatCellValue(cell);
     data.add(cellValue);
    }
   }
  }
  return data;
 }
 
 public void closeWorkbook() {
  try {
   this.workbook.close();  
  } catch (Exception ex) {
   ex.printStackTrace();
  }      
 }
 
 public static void main(String[] args) throws Exception {
  
  //ReadExcelColumnToVector app = new ReadExcelColumnToVector("./ExcelExample.xlsx");   
  ReadExcelColumnToVector app = new ReadExcelColumnToVector("./ExcelExample.xls");   
  
  //get data from first sheet, starting on second row, third column
  Vector<String> data = app.getData(0, 1, 2); // all indexes ara 0-based
  
  app.closeWorkbook();
  
  System.out.println(data);
  // use Vector<String> data to construct a JComboBox
  JFrame frame = new JFrame("JComboBox Sample");
  frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
  frame.setSize(500, 100);
  JPanel panel = new JPanel();
  JComboBox<String> comboBox = new JComboBox<String>(data);
  panel.add(comboBox);
  frame.add(panel);
  frame.setVisible(true);
 
 }
}

CodePudding user response：

How would I do that? I wrote this code that does what I need but it works with .txt files only. I need the same but instead of listing .txt files in a directory, it has to list all the data contained in the 3rd column

File directoryPath = new File("G:\\Drive\\MySoftware\\Clienti");
    //List of all files and directories
    String contents[] = directoryPath.list();
    System.out.println("List of files and directories in the specified directory:");
    for (int i = 0; i < contents.length; i  ) {
        jComboBox2.addItem(contents[i]);

    }