dennis saenz asked 9 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);
Abu Ghufran answered 9 years ago
Please send me sample data from database to test this excel export issue.
dennis saenz answered 9 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 9 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 9 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
