Issue with getStringValue() Returning Empty String for Date Cells in Custom Format

Aspose.Cells Product Family
1

Product and Version:
Aspose.Cells for Java, Version 7.5.3.2

Issue: When attempting to retrieve the value of a cell containing a date in the custom format "yyyy/mm/dd hh:mm" using cell.getStringValue(), the method returns an empty string (""). This behavior occurs with a specific Excel file, and the cell does contain a visible date value when viewed in Excel. This issue persists despite attempts to read the cell in alternative ways.

Details:

  1. Expected Result: cell.getStringValue() should return the date value as a string.
  2. Observed Result: cell.getStringValue() returns an empty string.
  3. Cell Format in Excel: The date cells are formatted with the custom format "yyyy/mm/dd hh:mm".
  4. Alternative Retrieval Methods:
  • Other methods such as cell.getValue() and cell.getDateTimeValue() were also tested, but they either returned null or did not provide the correct value.
2

@dilipvp

It seems you are experiencing an issue with the getStringValue() method returning an empty string for date cells formatted with a custom date format. This can happen due to how the Aspose.Cells library handles cell formatting and value retrieval.

To address this issue, you can try using the getStringValue(CellValueFormatStrategy) method with a specific formatting strategy. Here’s a code example that demonstrates how to retrieve the string value of a cell while considering its formatting:

import com.aspose.cells.Cell;
import com.aspose.cells.CellValueFormatStrategy;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;

public class RetrieveCellValue {
    public static void main(String[] args) throws Exception {
        // Load your workbook
        Workbook workbook = new Workbook("path/to/your/excel/file.xlsx");
        
        // Access the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);
        
        // Access the specific cell (e.g., A1)
        Cell cell = worksheet.getCells().get("A1");
        
        // Retrieve the string value using the CELL_STYLE strategy
        String formattedValue = cell.getStringValue(CellValueFormatStrategy.CELL_STYLE);
        System.out.println("Formatted String Value: " + formattedValue);
        
        // Alternatively, retrieve the string value without any formatting
        String unformattedValue = cell.getStringValue(CellValueFormatStrategy.NONE);
        System.out.println("Unformatted String Value: " + unformattedValue);
    }
}

In this example, replace "path/to/your/excel/file.xlsx" with the actual path to your Excel file. The CellValueFormatStrategy.CELL_STYLE option should help you retrieve the formatted string value correctly. If the cell still returns an empty string, ensure that the cell indeed contains a valid date value and is not formatted in a way that Aspose.Cells cannot interpret.

If you continue to face issues, consider checking the version of Aspose.Cells you are using, as there may have been updates or bug fixes in later versions that address this behavior.

3

CellValueFormatStrategy.CELL_STYLE is not available in the version that im using

4

@dilipvp,

Yes, new versions have more methods and enumerations for retrieving data including Dates. It is hard to evaluate your issue using older versions. Neither we could include any fixes or share concrete solutions using the older versions. It is best to upgrade to or use newer versions of the API (if possible). Anyways, could you please zip and attach your template Excel file. We will check your issue soon.