reading specific column of excel into java program

Question

I need to read specific column of an excel sheet and then declare the variables in java. The program that I have done reads the entire content of excel sheet. But I need to read a fixed column like C.

This is what I have done:

import java.io.File;
import java.io.IOException;
import jxl.Cell; 
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class JavaApplication4 
{

private String inputFile;
String[][] data = null;
public void setInputFile(String inputFile) 
{
    this.inputFile = inputFile;
}

public String[][] read() throws IOException  
{
    File inputWorkbook = new File(inputFile);
    Workbook w;

    try 
    {
        w = Workbook.getWorkbook(inputWorkbook);
        // Get the first sheet


        Sheet sheet = w.getSheet(0);
        data = new String[sheet.getColumns()][sheet.getRows()];
        // Loop over first 10 column and lines
   //     System.out.println(sheet.getColumns() +  " " +sheet.getRows());
        for (int j = 0; j <sheet.getColumns(); j++) 
        {
            for (int i = 0; i < sheet.getRows(); i++) 
            {
                Cell cell = sheet.getCell(j, i);
                data[j][i] = cell.getContents();
              //  System.out.println(cell.getContents());
            }
        }

       for (int j = 0; j < data.length; j++) 
        {
            for (int i = 0; i <data[j].length; i++) 
            {

                System.out.println(data[j][i]);
            }
        } 

    } 

    catch (BiffException e) 
    {
        e.printStackTrace();
    }
return data;
}

public static void main(String[] args) throws IOException 
{
    JavaApplication4 test = new JavaApplication4();
    test.setInputFile("C://users/admin/Desktop/Content.xls");
    test.read();
}

}

Here is my excel sheet,

From a bowl of chits numbered /@v1@/ to /@v2@/ , a single chit is randomly drawn. Find the probability that the chit drawn is a number that is a multiple of /@v3@/ or /@ v4@/?

I need to read this data and by matching the pattern /@v1@1, I need to declare the variables. How can I do this?

Gowtham Murugesan · Accepted Answer · 2015-06-01 14:17:43Z

2

What you can do, you should first get all the columns from the sheet by using sheet.getColumns() and store all columns in a list . Then you can match get all values based on columns. or you can get for only column "C".try using below code. let me know if this works.

int masterSheetColumnIndex = sheet.getColumns();
    List<String> ExpectedColumns = new ArrayList<String>();
    for (int x = 0; x < masterSheetColumnIndex; x++) {
        Cell celll = sheet.getCell(x, 0);
        String d = celll.getContents();
        ExpectedColumns.add(d);
    }
    LinkedHashMap<String, List<String>> columnDataValues = new LinkedHashMap<String, List<String>>();

    List<String> column1 = new ArrayList<String>();
    // read values from driver sheet for each column
    for (int j = 0; j < masterSheetColumnIndex; j++) {
        column1 = new ArrayList<String>();
        for (int i = 1; i < sheet.getRows(); i++) {
            Cell cell = sheet.getCell(j, i);
            column1.add(cell.getContents());
        }
        columnDataValues.put(ExpectedColumns.get(j), column1);
    }

edited Jun 1, 2015 at 14:17

Gowtham Murugesan

4172 gold badges6 silver badges18 bronze badges

answered Dec 17, 2014 at 11:33

Umesh Kumar

1,3972 gold badges18 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sandeep Over a year ago

thanks alot, I tried implementing it but it shows me error in the main funtion. can you help out

Manimaran Samuthirapandi · Accepted Answer · 2017-03-21 11:41:42Z

This is the very simple and efficient code and Working as expected

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class TestExcelFile {

    public static void main(String[] args) {
        String envFilePath = System.getenv("AZURE_FILE_PATH");

        // upload list of files/directory to blob storage
        File folder = new File(envFilePath);
        File[] listOfFiles = folder.listFiles();

        for (int i = 0; i < listOfFiles.length; i++) {
            if (listOfFiles[i].isFile()) {
                System.out.println("File " + listOfFiles[i].getName());

                Workbook workbook;
                //int masterSheetColumnIndex = 0;
                try {
                        workbook = WorkbookFactory.create(new FileInputStream(envFilePath + "\\"+ listOfFiles[i].getName()));
                    // Get the first sheet.
                    Sheet sheet = workbook.getSheetAt(0);

                    //we will search for column index containing string "Your Column Name" in the row 0 (which is first row of a worksheet
                    String columnWanted = "Column_Name";
                    Integer columnNo = null;
                    //output all not null values to the list
                    List<Cell> cells = new ArrayList<Cell>();

                    // Get the first cell.
                    Row row = sheet.getRow(0);
                    //Cell cell = row.getCell(0);
                    for (Cell cell : row) {
                        // Column header names.
                        //System.out.println(cell.toString());  
                        if (cell.getStringCellValue().equals(columnWanted)){
                            columnNo = cell.getColumnIndex();
                        }
                    }

                    if (columnNo != null){
                        for (Row row1 : sheet) {
                           Cell c = row1.getCell(columnNo);
                           if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {
                              // Nothing in the cell in this row, skip it
                           } else {
                              cells.add(c);
                              //System.out.println(c);
                           }
                        }

                    }else{
                        System.out.println("could not find column " + columnWanted + " in first row of " + listOfFiles[i].getName());
                    }

                } catch (InvalidFormatException | IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Rahul Jain · Accepted Answer · 2020-02-13 03:18:02Z

Reading Particular column from excel file

    File myFile = new File(path);
    FileInputStream fis = new FileInputStream(myFile);

    // Finds the workbook instance for XLSX file
    XSSFWorkbook myWorkBook = new XSSFWorkbook (fis);

    //XSSFWorkbook workBook = new XSSFWorkbook();
    //Reading sheet at number 0 in spreadsheet(image attached for reference
    Sheet sheet = myWorkBook.getSheetAt(0);

    //creating a Sheet object to retrieve object  
    Iterator<Row> itr = sheet.iterator();//iterating over excel file  

    while (itr.hasNext())                 
    {  
        Row row = itr.next();
        Iterator<Cell> cellIterator = row.cellIterator();//iterating over each column
        //Reading cell in my case column name is ppm
        Cell ppmEx= row.getCell(0);

        //Cell cell = cellIterator.next();
        while (cellIterator.hasNext())
        {  
            Cell cell = cellIterator.next();
            //Check the cell type and format accordingly
            switch (cell.getCellType()) 
            {
                case Cell.CELL_TYPE_NUMERIC:
                    //System.out.println(cell.getNumericCellValue() + "    ");
                    al.add(cell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING:
                    //System.out.println(cell.getStringCellValue()+" ");
                    al.add(cell.getStringCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    //System.out.println(cell.getBooleanCellValue()+" ");
                    al.add(cell.getBooleanCellValue());
                case Cell.CELL_TYPE_BLANK:
                    //System.out.println("blank");
                    al.add("blank");

            }  
        }  
        System.out.println("-");
    }

KhaledZero · Accepted Answer · 2018-09-01 11:41:15Z

XLSX_File

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
 package xlsxreader;

 import java.io.File;
 import java.io.FileInputStream;
 import java.io.FileNotFoundException;
 import java.io.IOException;
 import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
 import org.apache.poi.ss.usermodel.*;

 /**
  *
  * @author khaled
 */
public class XlsxReader {

/**
 * @param args the command line arguments
 */
public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {
    File file = new File("C:\\Users\\khaled\\Desktop\\myXLSX file.xlsx");
    Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
    Sheet sheet = workbook.getSheetAt(0);
    int column_index_1 = 0;
    int column_index_2 = 0;
    int column_index_3 = 0;
    Row row = sheet.getRow(0);
    for (Cell cell : row) {
        // Column header names.
        switch (cell.getStringCellValue()) {
            case "MyFirst Column":
                column_index_1 = cell.getColumnIndex();
                break;
            case "3rd Column":
                column_index_2 = cell.getColumnIndex();
                break;
            case "forth Column":
                column_index_3 = cell.getColumnIndex();
                break;
        }
    }

    for (Row r : sheet) {
        if (r.getRowNum()==0) continue;//hearders
        Cell c_1 = r.getCell(column_index_1);
        Cell c_2 = r.getCell(column_index_2);
        Cell c_3 = r.getCell(column_index_3);
        if (c_1 != null && c_1.getCellType() != Cell.CELL_TYPE_BLANK
                &&c_2 != null && c_2.getCellType() != Cell.CELL_TYPE_BLANK
                &&c_3 != null && c_3.getCellType() != Cell.CELL_TYPE_BLANK) {
            System.out.print("  "+c_1 + "   " + c_2+"   "+c_3+"\n");
        }
    }

}

}

OutPut

Collectives™ on Stack Overflow

reading specific column of excel into java program

4 Answers 4

1 Comment

Comments

Reading Particular column from excel file

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Reading Particular column from excel file

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related