CODEWITHSUNDEEP
javaexcelapache-poi
boomboomboom
boomboomboom

Reputation: 151

Apache POI reading formula value of excel cell

I am working on application that uploads excel file and insert its value into database.

My code is like following:

FileInputStream inputStream = new FileInputStream(excelFile);
String fileExtension = FilenameUtils.getExtension(temp1);

Workbook wb = null;
if(fileExtension.equalsIgnoreCase("xls")){ 
    wb = new HSSFWorkbook(inputStream);
} else {
    wb = new XSSFWorkbook(inputStream);
}

if (wb != null){
    logger.info("Read File");
}

Sheet sheet = null;
Row row = null;
Cell cell = null;

dbConnect();

sheet = wb.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows() + 1;

for (int y=1; y<rows; y++){
    row = sheet.getRow(y);
    int cells = row.getPhysicalNumberOfCells();

    String sqlQuery = "INSERT INTO paycheck(" + 
        "paycheckId, payTitle, payEmployee, payDisDate, payBasicSalary, payIncentive, payAllowance, paySpecialAllowance, " + 
        "payEducation, payTotal, payTax, payOther, payAdvanced, payDeductible, payNet, payDuration, payRate)" + 
            "VALUES(";

    for (int z=0; z<cells; z++){
        cell = row.getCell(z);

        if(z != cells -1){
            sqlQuery += "'" + cell + "', ";
        } else {
            sqlQuery += cell + ")";
        }
    }
    stmt.execute(sqlQuery);
}
dbDisConnect();

So when I upload an excel file like following, it works and insert its data to db.

enter image description here

However, when I upload an excel file like following, db does not get updated.

enter image description here

May I ask you how to solve this problem, so even if the value of cell is =ROUNDUP((600),2), not 600, it still update db.

Thank you in advance.

Upvotes: 0

Views: 986

Answers (1)

Andreas
Andreas

Reputation: 159076

The POI Cell class represents a cell, and provides many methods for working with the cell.

When you do sqlQuery += "'" + cell + "', ";, you're inserting the value returned by Cell.toString(). That value is not explicitly defined to do anything, so you cannot rely on what it returns.

What you want to do, is use the cell.getStringCellValue() method, to "Get the value of the cell as a string", as the javadoc states it.

Beyond that, you DON'T want to do the string concatenation you're currently doing, because it makes your code brittle and susceptible to SQL Injection attacks, that will allow hackers to steal your data and/or delete all your tables.

You should instead use a PreparedStatement, using parameter markers (?), and the setXxx() methods.

Upvotes: 2

Related Questions