My Entity class

@Data
@Entity
@Table(name = "rates")
public class ExchangeRate {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @OneToOne
    @JsonIgnore
    private LocalCurrency localCurrency;

    @Column(name = "rate")
    private BigDecimal rate;

    @Column(name = "date")
    private Date date;
}

Excel uploader

public static List<ExchangeRate> excelToExchangeRate(InputStream is) {
        try {
            Workbook workbook = new XSSFWorkbook(is);
            Sheet sheet = workbook.getSheet("FX Rates");
            Iterator<Row> rows = sheet.iterator();
            List<ExchangeRate> rates = new ArrayList<>();
            int rowNumber = 1;
            while (rows.hasNext()) {
                Row currentRow = rows.next();
                if (rowNumber == 1) {
                    rowNumber  ;
                    continue;
                }
                Iterator<Cell> cellsInRow = currentRow.iterator();
                ExchangeRate exchangeRate = new ExchangeRate();
                int cellIdx = 0;
                while (cellsInRow.hasNext()) {
                    Cell currentCell = cellsInRow.next();
                    switch (cellIdx) {
                        case 0:
                            exchangeRate.setId((long) currentCell.getNumericCellValue());
                            break;
                        case 1:
                            exchangeRate.setDate(currentCell.getDateCellValue());
                            break;
                        case 2:
                            exchangeRate.setRate(BigDecimal.valueOf(currentCell.getNumericCellValue()));
                            break;
                    }
                    cellIdx  ;
                }
                rates.add(exchangeRate);
            }
            workbook.close();
            return rates;
        } catch (IOException e) {
            throw new RuntimeException("fail to parse Excel file: "   e.getMessage());
        }
    }

This code does not work exactly as I need, that is, I want the excel file to be read line by line

Excel that I read:

And this is how I want to get the output:

The question is the following. There are four columns in my database: id, date, rates and localcarrency (where the data is taken from another table). And in the uploaded file, the data looks different (that is, the table structure is different) In the uploaded file, there are columns: Date and then the name (which in my database are taken from another table) and below these names are the numbers themselves. How can I take data in code?

CodePudding user response：

LocalCurrency (This may be differ, modify as per your requirement)

public class LocalCurrency {
    String currency;

    @Override
    public String toString() {
        return String.format("{LocalCurrency: {currency: %s}}", currency);
    }
}

ExchangeRate

public  class ExchangeRate {
    private Long id;
    private LocalCurrency localCurrency;
    private BigDecimal rate;
    private Date date;
    
    @Override
    public String toString() {
        return String.format("{ExchangeRate: {id: %s, localCurrency: %s, rate: %s, date: %s}}", id, localCurrency, rate, date);
    }
}

Main Logic

public class Test {
    
    public static List<ExchangeRate> excelToExchangeRate(InputStream is) {
        try {
            Workbook workbook = new XSSFWorkbook(is);
            Sheet sheet = workbook.getSheet("Field Option");
            Iterator<Row> rows = sheet.iterator();
            List<ExchangeRate> rates = new ArrayList<>();
            
            List<String> headers = new ArrayList<>();
            while (rows.hasNext()) {
                Row currentRow = rows.next();
                int rowNumber = currentRow.getRowNum();
                Iterator<Cell> cellsInRow = currentRow.iterator();
                if (rowNumber == 0) {
                    for (int k = 1; k < currentRow.getLastCellNum(); k  ) {
                        headers.add(currentRow.getCell(k).getStringCellValue());
                    }
                } else {
                    Date date = null;
                    List<BigDecimal> colRates = new ArrayList<>();
                    int cellIdx = 0;
                    while (cellsInRow.hasNext()) {
                        Cell currentCell = cellsInRow.next();
                        if(cellIdx == 0) {
                            date = currentCell.getDateCellValue();              
                        } else {
                            colRates.add(BigDecimal.valueOf(currentCell.getNumericCellValue()));
                        }
                        cellIdx  ;
                    }
                    
                    List<ExchangeRate> exchangeRateList = new ArrayList<>();
                    for (int i = 0; i < colRates.size(); i  ) {
                        ExchangeRate exchangeRate = new ExchangeRate();
                        exchangeRate.setDate(date);
                        exchangeRate.setRate(colRates.get(i));
                        
                        LocalCurrency localCurrency = new LocalCurrency();  //set localCurrency as per your object
                        localCurrency.setCurrency(headers.get(i));          //headers.get(i) contains Currency e.g. USD, EUR
                        
                        exchangeRate.setLocalCurrency(localCurrency);
                        
                        exchangeRateList.add(exchangeRate);
                    }
                    rates.addAll(exchangeRateList);
                }
            }
            workbook.close();
            return rates;
        } catch (IOException e) {
            throw new RuntimeException("fail to parse Excel file: "   e.getMessage());
        }
    }

Output:

{ExchangeRate: {id: null, localCurrency: {LocalCurrency: {currency: USD}}, rate: 1.0, date: Thu Apr 07 00:00:00 IST 2022}}
{ExchangeRate: {id: null, localCurrency: {LocalCurrency: {currency: EUR}}, rate: 10422.0, date: Thu Apr 07 00:00:00 IST 2022}}
{ExchangeRate: {id: null, localCurrency: {LocalCurrency: {currency: CHF}}, rate: 9612.0, date: Thu Apr 07 00:00:00 IST 2022}}
{ExchangeRate: {id: null, localCurrency: {LocalCurrency: {currency: MXN}}, rate: 20277.0, date: Thu Apr 07 00:00:00 IST 2022}}
{ExchangeRate: {id: null, localCurrency: {LocalCurrency: {currency: BWP}}, rate: 808.0, date: Thu Apr 07 00:00:00 IST 2022}}

Excel Input: