best possible way of mapping excel fields with java object

Question

I am trying to map an excel field with the java object. What will be the best possible way of doing it rather then being dependent on the column number.

Cell orderId=row.getCell(0);
System.out.println("orderId" +orderId);
Cell trackingId=row.getCell(1);

Cell orderTitle = row.getCell(2);

Cell customerName = row.getCell(3);

Cell customerAddress = row.getCell(5);

How to map the column with the object variable without bieng dependent on column numbers?

i am looking for something where i can map the row header with object and the rest should be independent of the row number. Any suggestions?

Grab all the cells of the first row, format as strings, use that as a lookup between "title" and column number, then process each row in turn looking things up that way? — Gagravarr, Aug 15 '17 at 12:00
@Gagravarr in that case i am again dependent on column number is there way where i shud be less dependent on column number and more on the column header to read the excel data. — irfan shafi, Aug 15 '17 at 13:01

score 3 · Answer 1 · answered Aug 15 '17 at 17:36

If you want to map the column with the object variable without being dependent on column numbers, then you need to have some way to match class fieldName with excel file header name and set value accordingly. The one way is to use reflection:

Read all fields of given class using reflection,
Iterate through all rows of excel file.
Match field names with headers and get header index of all fields.
Get value from cell for each index.
Finally set the values to each field of object using reflection.
Return list of objects.

I have created a simple Java library to convert Excel file into list of object some time ago. This might help you.

works for me, just a question, did this considered to close() the FileInputStream? How can I edit your method to do this? — Ramiro Arizpe Giacomelli, Jan 21 '20 at 00:59

score 0 · Answer 2 · answered Aug 15 '17 at 12:02

0

If I understand your question correctly, you want to map a property to a column, so that you can get the property of an order by referring to the property instead of a column number.

int orderIdColumn = 0;
int trackingIdColumn = 1;
int orderTitleColumn = 2;
int customerNameColumn = 3;
int customerAddressColumn = 5;

Depending on your needs, you could also declare an int field denoting the row containing the header cells.

Where you declare these variables depends on the design of the rest of your program, but it would obviously have to be outside the object representing an order, since they're not specific to the order, but to the excel spreadsheet.

answered Aug 15 '17 at 12:02

Stingy

234
1
9

thanks for your answer but i am trying to know the best possible way to match the object property with the column without bieng dependent on column number bcoz may be the "orderId " property is on column number 1 in excel and in another sheet its at number 3 . i hope you get my point – irfan shafi Aug 15 '17 at 13:00
2

@irfanshafi Based on that comment alone, I would have thought so, but then, I would have suggested something similar to Gagravarr's suggestion, which would basically be the same as my answer, except the variables would be assigned at runtime instead of being hard-coded into the program. But there, you replied you would still depend on the column number, so I don't think I understand what you want. You are reading from an Excel file, so of course you would depend on the column number at one point or another. The only question is where you abstract the logic you need from the Excel format. – Stingy Aug 15 '17 at 13:14

score 0 · Answer 3 · edited Mar 31 '21 at 03:15

package com.jcg.example;
 
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.xssf.usermodel.XSSFWorkbook;
 
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class WriteExcelFileExample {
    private static final String FILE_PATH = "<Path to your excel file>";
   
    private static final WriteExcelFileExample INSTANCE = new WriteExcelFileExample();
 
    public static WriteExcelFileExample getInstance() {
        return INSTANCE;
    }
 
    private WriteExcelFileExample() {
    }
 
    public static void main(String args[]){
 
        List studentList = new ArrayList();
        studentList.add(new Student("Magneto","90","100","80"));
        studentList.add(new Student("Wolverine","60","60","90"));
        studentList.add(new Student("ProfX","100","100","100"));
 
        writeStudentsListToExcel(studentList);
 
    }
 
    public static void writeStudentsListToExcel(List studentList){
 
        // Using XSSF for xlsx format, for xls use HSSF
        Workbook workbook = new XSSFWorkbook();
 
        Sheet studentsSheet = workbook.createSheet("Students");
 
        int rowIndex = 0;
        for(Student student : studentList){
            Row row = studentsSheet.createRow(rowIndex++);
            int cellIndex = 0;
            //first place in row is name
            row.createCell(cellIndex++).setCellValue(student.getName());
 
            //second place in row is marks in maths
            row.createCell(cellIndex++).setCellValue(student.getMaths());
 
            //third place in row is marks in Science
            row.createCell(cellIndex++).setCellValue(student.getScience());
 
            //fourth place in row is marks in English
            row.createCell(cellIndex++).setCellValue(student.getEnglish());
 
        }
 
        //write this workbook in excel file.
        try {
            FileOutputStream fos = new FileOutputStream(FILE_PATH);
            workbook.write(fos);
            fos.close();
 
            System.out.println(FILE_PATH + " i[enter link description here][1]s successfully written");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
 
 
    }
}


  [1]: https://examples.javacodegeeks.com/core-java/writeread-excel-files-in-java-example/

the above code is one good example for writing objects directly into excel please refer this link "https://examples.javacodegeeks.com/core-java/writeread-excel-files-in-java-example/" — Piyush Gharote, Apr 02 '19 at 07:42

best possible way of mapping excel fields with java object

3 Answers3