ParamQuery grid support forum

General Category => ParamQuery Pro Evaluation Support => Topic started by: Arul on June 05, 2019, 11:19:44 am

Title: Number Formatting and Dropdown Lists when importing from excel
Post by: Arul on June 05, 2019, 11:19:44 am

Hi,

When we import the excel workbook using the pq.excel.importXl method, we are facing the below issues:

1. Format of the cell is appended to the cell text. i.e. "1.00" in excel with the number formatting of "0.00" gets imported as "0.001", whereas "1" with "General" format gets imported as "1".

2. We have cells with data validation list. During edit, these cells are not displaying as dropdown values (select), but are displayed as text.

I have attached the code used, excel file used and screenshot of the issues. Can you please assist us?

Title: Re: Number Formatting and Dropdown Lists when importing from excel
Post by: paramvir on June 17, 2019, 12:51:01 pm

1. Excel uses various kind of formats ( like in below url ), all are not supported by pqgrid

https://support.office.com/en-us/article/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68

you can use "#.00" instead of "0.00" to get it properly imported in pqgrid.

2. Dropdown imports from Excel not supported yet, might be added in future versions.

Title: Re: Number Formatting and Dropdown Lists when importing from excel
Post by: Arul on June 19, 2019, 02:24:52 pm

Thank You.

It is not possible for us to modify the formatting in the client's excel files. Is there a way to set the value and number format for a cell. We intend to use like a spreadsheet rather than as a grid.

Title: Re: Number Formatting and Dropdown Lists when importing from excel
Post by: paramvir on June 19, 2019, 03:47:10 pm

Yes it's possible to set value and format of a cell, row or column through context menu like Excel.

Ex of context menu: https://paramquery.com/pro/demos/context_menu

Title: Re: Number Formatting and Dropdown Lists when importing from excel
Post by: Arul on June 19, 2019, 04:01:41 pm

Sorry. I meant Programatically.

Can I Set the value, format and number format of a cell by specifying rowIndex and columnIndex or address like "B1"?

something like: grid("B1", { value: 2000, format: "text-align: right", numberFomat: "####.##" }).