bug exporting excel

Questionsbug exporting excel
dennis saenz asked 8 years ago

An apostrohpe appears in the excel result file during export in number cells, i don't know what it can be.

I used the formatter float, currency and number for this purpose and i got the same apostrophe in some cells and numbers in others. I'm sure the problem is not in the db query. Greetings.

$col["formatter"] = "currency";
$col["formatoptions"] = array("thousandsSeparator" => ",",
"decimalSeparator" => ".",
"decimalPlaces" => 2);

$col["formatter"] = "number";
$col["formatoptions"] = array("prefix" => "$",
"suffix" => '',
"thousandsSeparator" => ",",
"decimalSeparator" => ".",
"decimalPlaces" => 2);

5 Answers
Abu Ghufran answered 8 years ago

Please send me sample data from database to test this excel export issue.

dennis saenz answered 8 years ago

Sure, im going to post a public link to my dropbox with the zip file
https://www.dropbox.com/s/r1s9d3npbd1okze/sample%20data.zip?dl=0

in the zip file there are 2 excels with the same data (columns) one exported (bug -apostrohpe in the columns AI, AJ and AL) and the other excel is with the data direct from DB, also, there is a txt file with the same data pure.

The formatters are

$col = array();
$col["title"] = "Monto Total Venta"; // caption of column
$col["name"] = "Monto Total Venta"; // grid column name, must be exactly same as returned column-name from sql (tablefield or field-alias)
$col["editable"] = false;
$col["align"] = "center";
$col["formatter"] = "currency";
$col["formatoptions"] = array("thousandsSeparator" => ",",
"decimalSeparator" => ".",
"decimalPlaces" => 2);
$cols[] = $col;

$col = array();
$col["title"] = "Costo"; // caption of column
$col["name"] = "Costo"; // grid column name, must be exactly same as returned column-name from sql (tablefield or field-alias)
$col["editable"] = false;
$col["align"] = "center";
$col["formatter"] = "number";
$col["formatoptions"] = array("prefix" => "$",
"suffix" => '',
"thousandsSeparator" => ",",
"decimalSeparator" => ".",
"decimalPlaces" => 2);

$cols[] = $col;

$col = array();
$col["title"] = "Utilidad"; // caption of column
$col["name"] = "Utilidad"; // grid column name, must be exactly same as returned column-name from sql (tablefield or field-alias)
$col["editable"] = false;
$col["width"] = "6";
$cols[] = $col;

—————————————————————-
Thanks in advance.

Abu Ghufran answered 8 years ago

I need few more details as i am still unable to generate this issue.
Which database are you using and what are the datatypes of these fields in db.

dennis saenz answered 8 years ago

Hi, im using sql server 2014, the columns are a result of some operations, the origin datatypes of those fields are:

T1.[TreeType] char(1)
T1.[Quantity] numeric(19, 6)
T1.[TotalSumSy] numeric(19, 6)
T1.[U_PRECIOVTA] numeric(19, 6)
T1.[GPTtlBasPr] numeric(19, 6)
T1.[U_FLETE] numeric(19, 6)
TC.[Rate] numeric(19, 6)
OCDET.[TotalSumSy] numeric(19, 6)

and the operations are:

CASE WHEN T1.[TreeType]<>'I' THEN T1.[TotalSumSy]
ELSE T1.[Quantity]*T1.[U_PRECIOVTA] END AS 'Monto Total Venta',

CASE WHEN T1.[TreeType]<>'I' THEN T1.[GPTtlBasPr]/TC.[Rate]
ELSE OCDET.[TotalSumSy] END AS 'Costo',

CASE WHEN T1.[TreeType]<>'I' THEN T1.[TotalSumSy]-(T1.[GPTtlBasPr]/TC.[Rate])- ISNULL(T1.[U_FLETE],0)
ELSE T1.[TotalSumSy]-OCDET.[TotalSumSy]-ISNULL(T1.[U_FLETE],0) END AS 'Utilidad'

The problem is persistent with that aphostrophe appearing in those results columns in excels export and csv as well

Abu Ghufran answered 8 years ago

Please follow email for further resolution.

Your Answer

4 + 14 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?