Free Support Forum - aspose.com

Smartmarker and data types?

Aspose.Cells Product Family
#1

I am evaluating Aspose Cells.Java and especially the smartmarkers feature.

What I found is that it seems that aspose completely ignores the actual data types of the Java objects that I use as the data source. Everything comes out as “text” cell type in Excel.
Why is that?
When the Java type is any numeric type (int, double, BigDecimal, …) I would expect Aspose to set the cell to numeric.
More pressing for me: When the Java type is a date type (Date, LocalDate, …) I would expect the Excel cell to also actually contain a date.

Here is an example result for some common data types:
bild.png (2.9 KB)

Why doesn’t Aspose just use the type information from the data source?
Is there any way I can use the smartmarker feature to insert an actual date in excel?

#2

@sfriedrich,

Could you use “numeric” parameter when defining those markers:
e.g.
&=Table1.Field1(numeric)

If you still find any issue, kindly do create a sample Java program (runnable) to reproduce the issue on our end. Also, attach your input file (if any) and output file for evaluation. We will check your issue soon.

PS. please zip the Excel file(s) prior attaching.

#3

Thanks, that is working for numbers. But still I think it should not be needed, because Aspose should detect automatically that the Java type is a number.

Also, there is no solution for dates, right?

I will try and put together a runnable example (though the issue should be very clear).

#4

@sfriedrich,

In MS Excel, DateTime values are also stored as numeric notations, so we only provide “numeric” parameter in Smart Markers.

Yes, kindly create a standalone Java code to reproduce the issue on our end and provide us.

#5

Please find attached a small, self-contained example.
After unzipping, you can run it using gradle (even if you donÄt have gradle installed, because it uses the gradle wrapper):
.\gradlew run

smartmarkers.zip (73.5 KB)

Here’s a screenshot of the resulting Excel file:
bild.png (9.1 KB)

Issues:

  1. There is no auto-detection of types. Aspose could easily check if the datasource member is a Number or Date. AFAICS that should be trivial to implement.
  2. All result cells still have Excel type 2 == TEXT
    Number and date cells should have type 1== NUMBER.
    As soon as I manually edit in one of the result cells and for example enter “99”, Excel automatically changes the cell type.
  3. LocalDateTime is not supported at all. Adding (numeric) here does nothing.
#6

@sfriedrich,
We will analyse this information and provide our feedback soon.

#7

Thanks a lot for looking into this.

I found another quirk: When the java type is BigDecimal “numeric” does not work at all.
bild.png (2.6 KB)
I updated the example:
smartmarkers.zip (91.0 KB)

#8

@sfriedrich,

Thanks for the samples.

It seems Smart Markers has some limitations regarding different data types. We have to investigate it thoroughly though. We have logged a ticket with an “CELLSJAVA-43369” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

#9

@sfriedrich,

Please try our latest version/fix: Aspose.Cells for Java v20.11.8 (attached)
aspose-cells-20.11.8-java.zip (7.2 MB)

We have fixed the issue of BigDecimal. As java.time.* is added since JDK1.8, so we have to support lower version now. We need more time to look into on how to support it.

#10

@sfriedrich,

This is to inform you that we have fixed your issue completely. We will share the Download link to the fixed version after performing QA and incorporating other enhancements and fixes.

#11

Thanks for the answer and the fixes. BigDecimal works fine now.

Unfortunately, I found another bug: German umlauts (and probably other non-ascii characters) do not work:
For example this text “Merkwürdige Äffchen dösen auf der Straße.”
becomes mangled:
bild.png (1.5 KB)

Here’s the example which uses the latest build you attached previously to this thread:
smartmarkers.zip (7.1 MB)
I also added Boolean, ZonedDateTime, and every standard numeric class (from byte to long and the respective wrappers).

What’s also not fixed: The cell type still is always 2 (TEXT) even for numeric, date and boolean fields.

#12

We are scheduled to publish our next official release of the product (Aspose.Cells for Java v20.12) tomorrow or at the start of next week, so you may test the new version and give us your feedback.

#13

@sfriedrich,
Please check the attached “dest.xlsx” which is generated with the latest release 20.12. Everything works fine except ZonedDateTime.

(1) Please check your attached “test-filled.xlsx”, numberic and boolean works fine.
(2) About the issue of German umlauts , we could not find it, maybe you have to set the txt file encoding in your JAVA IDE.
dest.zip (11.0 KB)