pq.formatNumber

[queensgambit9]

Hi

Using

Code: [Select]

format: function (val) { return (val==null || val==="")? "": (pq.formatNumber(val, "#,###.00")); },
I get ,01 in grid but value is 0.01 from db...why is preceding 0 dropped?

[paramvir]

It's because of the "#,###.00" formatting.

Correct format for your requirement is "#,##0.00"

[queensgambit9]

Thanks.
Other question regrding formatting:

A Column has data like 123.23.
Column in DB (MySQL) has decimal datatype and is received as string to grid.

Export to csv works fine...in Excel I get some rows with comma (',') and some with dot ('.').

Any idea why?

[paramvir]

1) Please set the dataType of that column to float in grid.

2) Check the formatting applied to cells in different rows in Excel — it may give you a clue about what’s causing the issue.

[queensgambit9]

dataType in grid is set to float.

Types assigned in Excel.
Number - These values have wrong format, ex: 64,86.
Text - These values are correct, ex: 50.00.

I noticed that all values that ends with 0 works fine, ex 154.80
If the datatype in DB is float. Grid receives a float value (not a string) and it seems to work. But I want to use decimal datatype in DB.

[paramvir]

grid expects numbers in dataType: float columns.

you can parse the strings to numbers in dataModel.getData callback

Example: https://paramquery.com/pro/api#option-dataModel-getData

[queensgambit9]

In colModel I have:

Code: [Select]

getData: function (dataJSON) {
              var data = dataJSON.data;
              data.forEach((rowData)=>{
                rowData.value = parseFloat(rowData.value).toFixed(2); //parse the number values.
            });
              return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: data };
          }
      }
value still seems to have "" around it, example: "65.13"...shouldn't it be 65.13 without quotes in the json returned to grid? Still not work in Excel export (same problem still there).

[paramvir]

toFixed(2) converts the number back to a string, which is why you still see quotes around it.

Instead of parseFloat(rowData.value).toFixed(2), try this:

rowData.value = parseFloat(rowData.value);


This will keep value as a number, which is what the grid expects for dataType: "float" columns and should also fix the Excel export issue.

If you need to show two decimal places only for display, you can use a column-level format instead of converting it to a string in the data.

[queensgambit9]

ok, I tried without toFixed:

Code: [Select]

getData: function (dataJSON) {
              var data = dataJSON.data;
              data.forEach((rowData)=>{
                rowData.value = parseFloat(rowData.value); //parse the number values.
            });
              return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: data };
          }
      }
It does not seem to make any difference. The json returned to grid still contain quotes for that column....

[paramvir]

https://paramquery.com/pro/api#option-dataModel-getData

getData is a property of dataModel. add a breakpoint in it to check whether it actually runs when remote data is loaded.

Also substutute value in rowData.value with actual dataIndx of your column

Code: [Select]

    dataModel:{
        getData:function( dataJSON, textStatus, jqXHR ){
            dataJSON.data.forEach((rowData)=>{
                rowData.price = parseFloat(rowData.price); //parse the number values.
            });
        return { data: dataJSON.data, curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords };
        }
    }


[queensgambit9]

Ok, thanks. Seem to work.
Parsefloat drops trailing zeros after decimal. How can I force keep 2 decimals (as in db) so they show up in excel export as well?

[paramvir]

you can force 2 decimals by setting column.format or global fmtNumber option

https://paramquery.com/pro/api10#option-fmtNumber

[queensgambit9]

Using in column:

Code: [Select]

format: "$ #,##0.00"
Works in grid and Export to Excel but not export to CSV (don't seem to consider format).
Also is it possible to replace isNaN with empty ("") and still have filtering and export fully functional?

[paramvir]

1) Export to csv:  Please use exportRender: true in the column or pass render: true to exportData / exportCsv method.

2) Please replace NaN values with null or undefined.