getValueType for empty cells

Aspose.Cells Product Family
1

Hi,

I have an excel template where I set the data type using the Format->Cells feature of excel. Based on that I want to decide the setValue for the cell while populating it with data. Ideally, the template should be able to drive the type of the data that has to appear in the final excel sheet (populated with data).

But when I do a getValue on the cells, which are empty but with the data types set (as mentioned above), I always get 0!

Due to this I cannot decide the datatype in setValue(datatype) while I am populating the template with data (from an xml)!

What am I doing wrong here?

Regards,

rfcExcel!

2

Hi rfcExcel,

Could you explain it more, I tried your scenario and it works fine and I got null not '0' for a cell which is empty but set with some number type format. Do you use Cell.getValueType() method. This method returns CellValueType, please check:

https://reference.aspose.com/cells/java/com.aspose.cells/cellvaluetype/

which denotes the cell value types. And ‘0’ means the first cell value type which is: CellValueType.NULL

Could you create a sample test code with a template excel file, post it here to show the issue. We will check it soon.

Thank you.

3

Hi Amjad,

Let me reframe my question. Is it possible to read the data type of cell which does not contain any data/value but has the dataformat set.

I have a template where I would like to read the data type from one of the cells (which does not contain any data/value) and then use that to set the correct type of data into another cell of some other worksheet.

Let me know if you need any further information regarding the requirement.

Regards,

rfcExcel!

4

Hi,

I think you may utilize e.g., cells.getCell("A1").getStyle().getNumber() to get the the integer value to match the number format table to specify the a cell's number formatting type.

Thank you.

5

Hi,

Please try this attached version. there is a new and more convenient method for Style object:, isDateStyle(), to check whether a cell’s value should be Date type. For example:

Cell cell = ...;

if(cell.getStyle().isDateStyle())

{

...;

}

6

Thanks Johnson and Amjad!

The isDate method looks quite useful. But unfortunately, I have requirements for other types like Currency, etc. which does not have such methods yet.

I tried the getNumber() method from the Style class. That one looks quite useful as well, but it does not work for all data types.

Following is my finding when I ran a test code on different cells with different formats (check out the ones in red):

General=0
Number=4
Currency=0
Accounting=44
Date=0
Time=0
%=10
Text=49

The code simply contains : cell1.getStyle().getNumber()

Please let me know if I am doing something wrong here.

Regards,

- rfcExcel!

7

Hi rfcExcel,

Please try this fix.

We add style.getNumberFormatType() method to get the number format type of the style.See following codes:

Cells cells = workbook.getWorksheets().getSheet(0).getCells();
switch(cells.getCell("A1").getStyle().getNumberFormatType())
{
case NumberFormatType.DATE:
//it contains Date and Time format.
break;
case NumberFormatType.NUMBER:
//it contains Number,Currency,Accounting,Fraction format.
break;
case NumberFormatType.GERNAL:
case NumberFormatType.TEXT:
case NumberFormatType.UNKNOWN:
break;
}