When using DataFrame.readExcel, how do you prevent...

# datascience

[Richard Glen]

When using DataFrame.readExcel, how do you prevent dates to be converted to LocalDateTime?

Using this guide in the documentation, I was able to avoid numbers from being converted to double. How can I do the same for dates to avoid it from being formatted?

[Nikita Klimenko [JB]]

Hi! What type do you want them to be? Double?

[Richard Glen]

I want to read them as is. As strings.

Like telling apache poi to read cells just their plain value

[Nikita Klimenko [JB]]

Hm, readExcel converts values that apache poi sees as numeric to LocalDateTime

CellType.NUMERIC -> {
    val number = numericCellValue
    when {
        DateUtil.isCellDateFormatted(this) -> DateUtil.getLocalDateTime(number).toKotlinLocalDateTime()
        else -> number
    }
}

There's no direct way to get cell value as String now. I assumed this is a lossless conversion and `KotlinLocalDateTime`can be converted back to any value that you need. Maybe i overlooked something

DateUtil.getLocalDateTime(number).toKotlinLocalDateTime()

[Nikita Klimenko [JB]]

The problem here is that let's say stringCellValue will throw an exception for numeric too.. :c

/**
 * Get the value of the cell as a string
 * <p>
 * For numeric cells we throw an exception. For blank cells we return an empty string.
 * For formulaCells that are not string Formulas, we throw an exception.
 * </p>
 * @return the value of the cell as a string
 */
String getStringCellValue();

[Richard Glen]

Oh. I see. There's no way to force to get the cell's display value I guess. Thanks for the immediate response!

[Nikita Klimenko [JB]]

Oh, this is an interesting point
Looks like cell's display value can be read like this. Then it should be an option for readExcel

val style: CellStyle = cell.getCellStyle()
val dataFormatter = DataFormatter()
val formattedCellValue = dataFormatter.formatCellValue(cell, style)

Returns the formatted value of a cell as a String regardless of the cell type.
formatCellValue

But unfortunately i don't see a workaround that you can use until this is implemented on the library side

[Nikita Klimenko [JB]]

Here's an issue to follow
https://github.com/Kotlin/dataframe/issues/669