Dear Laurence,
Calling the Cell.getValue method returns objects of the type Double for
cells containing texts like 1000 even when the cell is formatted as
text. In my opinion cells formatted as text should return String
objects, otherwise there is no way of getting the value exactly as it
looks in Excel.
Thanks,
Ulf
Hi Ulf,
Cell.getValue method should return the exact data in the cell. Otherwise, if you change cell formatting, the returned data will also be changed. That will make huge confusion.
For this issue, I think we can add another method: getStringValue. It will return the displayed text data.
For example, if you put 1234.5678 in a cell and format it as "General" or "Text", getStringValue will return "1234.5678". However, if you format it as two-digit number, it will return "1234.58". And if you format it as Date, it may return 1903-5-18, and so on.
For your specific case, you can use toString method to convert the double value to string.
Dear Laurence,
Thanks for your answer. We are in desperate need of a getStringValue method as you suggested. Let me explain our problem:
Our users are using excel to upload thousands of items into our system.
Sometimes when they define the name of an item they give it a name
containing only digits in a text formatted column. If they have
formatted the column as text before they entered the digits Excel
apparently stores it as text and Apose Excel returns a String object.
If they instead enter the names a digits and afterwards format the
column as text, Excel stores the values as numbers and Aspose returns
Double objects. Currently we use your suggested method and simply
convert the double to a string. However, converting a double to a
string is highly locale dependent and we are getting the string 1,000.0
for a text column that looks like 1000 in Excel. We could of course
write a method that converts a double to the string 1000 for this
particular case. But we have no way of knowing how the value actually
looked in excel and thus cannot create the items with the exact names
as they appear in Excel.
Do you think you could add that getStringValue method you mentioned? What would be the time frame for this?
Best regards,
Ulf
It will take a relative long time to totally support this feature. However, we can make it to serve your special needs. Could you post a file to show your cell formattings? We will implement them first.
Dear Laurence,
We will use the following workaround until you fully support the
getStringValue method. We will use a mapping from the builtin formats
to the corresponding java formats. We will also try to code some
conversion from custom formats to java formats, but I think we can live
with the following for now:
/
* Java versions of the builtin excel formats.
*/
private static final Map<Integer, Format> cFormats = new HashMap<Integer, Format>(32);
static {
// Date formats
cFormats.put(0xe, new SimpleDateFormat(“dd/MM/yyyy”));
cFormats.put(0xf, new SimpleDateFormat(“d-MMM-yy”));
cFormats.put(0x10, new SimpleDateFormat(“d-MMM”));
cFormats.put(0x11, new SimpleDateFormat(“MMM-yy”));
cFormats.put(0x12, new SimpleDateFormat(“h:mm a”));
cFormats.put(0x13, new SimpleDateFormat(“h:mm:ss a”));
cFormats.put(0x14, new SimpleDateFormat(“H:mm”));
cFormats.put(0x15, new SimpleDateFormat(“H:mm:ss”));
cFormats.put(0x16, new SimpleDateFormat(“M/d/yy H:mm”));
cFormats.put(0x2d, new SimpleDateFormat(“mm:ss”));
cFormats.put(0x2e, new SimpleDateFormat(“H:mm:ss”));
cFormats.put(0x2f, new SimpleDateFormat(“mm:ss.S”));
// Number formats
cFormats.put(0x1, new DecimalFormat(“0”));
cFormats.put(0x2, new DecimalFormat(“0.00”));
cFormats.put(0x3, new DecimalFormat("#,##0"));
cFormats.put(0x4, new DecimalFormat("#,##0.00"));
cFormats.put(0x5, new DecimalFormat("$#,##0;($#,##0)"));
cFormats.put(0x6, new DecimalFormat("$#,##0;($#,##0)"));
cFormats.put(0x7, new DecimalFormat("$#,##0.00;($#,##0.00)"));
cFormats.put(0x8, new DecimalFormat("$#,##0.00;($#,##0.00)"));
cFormats.put(0x9, new DecimalFormat(“0%”));
cFormats.put(0xa, new DecimalFormat(“0.00%”));
cFormats.put(0xb, new DecimalFormat(“0.00E00”));
cFormats.put(0x25, new DecimalFormat("#,##0;(#,##0)"));
cFormats.put(0x26, new DecimalFormat("#,##0;(#,##0)"));
cFormats.put(0x27, new DecimalFormat("#,##0.00;(#,##0.00)"));
cFormats.put(0x18, new DecimalFormat("#,##0.00;(#,##0.00)"));
cFormats.put(0x29, new DecimalFormat("#,##0;(#,##0)"));
cFormats.put(0x2a, new DecimalFormat("$#,##0;($#,##0)"));
cFormats.put(0x2b, new DecimalFormat("#,##0.00;(#,##0.00)"));
cFormats.put(0x2c, new DecimalFormat("$#,##0.00;($#,##0.00)"));
cFormats.put(0x30, new DecimalFormat("##0.0E0"));
}
/
* The default formatter to convert numbers into strings
*/
private static DecimalFormat defaultNumberFormat = new DecimalFormat("#.###");
/**
* The default formatter to convert dates into strings
*/
private static DateFormat defaultDateFormat = DateFormat.getInstance();
Regards,
Ulf
Hi Ulf,
Please try this attached fix.
GetStringValue method is provided. Currently only your listed builtin formats are supported.
We are working on chart features so we have to postpone more support on custom number formats to the later releases.
Dear Laurence,
Thanks a lot for the update. We will try it immediately. What happens
if we call the new getStringValue method for a cell containing a format
that is not supported? Will it use the toString() method or fail with
an exception or something else?
Thanks,
Ulf
If the format is not supported, we will try to use the default number format to convert it. If the default number format is still not supported, we will use toString() method to return a string. Exception will not be thrown when you can getStringValue method.