Author Topic: Number Formatting and Dropdown Lists when importing from excel  (Read 195 times)

Arul

  • Newbie
  • *
  • Posts: 3
    • View Profile
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?

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 4038
    • View Profile
Re: Number Formatting and Dropdown Lists when importing from excel
« Reply #1 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.
« Last Edit: June 17, 2019, 07:57:04 pm by paramvir »

Arul

  • Newbie
  • *
  • Posts: 3
    • View Profile
Re: Number Formatting and Dropdown Lists when importing from excel
« Reply #2 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.

paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 4038
    • View Profile
Re: Number Formatting and Dropdown Lists when importing from excel
« Reply #3 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

Arul

  • Newbie
  • *
  • Posts: 3
    • View Profile
Re: Number Formatting and Dropdown Lists when importing from excel
« Reply #4 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: "####.##" }).