Messages

1

Help for ParamQuery Pro / Re: Trim unused columns in Excel Spreadsheet

« on: November 10, 2017, 07:44:54 pm »

Another piece I found is here:

https://support.office.com/en-us/article/clean-excess-cell-formatting-on-a-worksheet-e744c248-6925-4e77-9d49-4874f7474738

This is an add-on for  Excel that "cleans excess formatting". It really fixes the workbook properly.

You apply it to the whole sheet and save it. It cleans out al the empty column formats.

Now when you import it to PQGrid it properly builds a colModel with the correct number of columns.
No need for the Hide.

WooHoo FIXED

Apparently the problem originates from the very common practice people selecting the entire spreadsheet and applying formats to the whole sheet, like a global font change. This fills all 16,384 column with the formats.

Still doesn't help you much because it requires installing and actually knowing to use it.

2

Help for ParamQuery Pro / Re: Trim unused columns in Excel Spreadsheet

« on: November 10, 2017, 07:00:38 pm »

On Your hint I found this:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_2010/excel-fills-spreadsheet-with-blank-columns/4a726e7b-a4a6-40ba-8528-2946a3a39e85?auth=1

Apparently Excel will sometimes fill the sheet with not really empty columns.
I tried the solution given above. There are two given.

The first is to select all the empty columns by:

• Click the header of the first blank column
  Press Ctrl+Shift+right arrow which selects to XFD
  Press Ctrl+Minus to remove everything.

This didn't work. From the explanation Excel deletes them but adds back in the full count of columns.

The second solution works:

• Click the header of the first blank column
  Press Ctrl+Shift+right arrow which selects to XFD
  Right Click on the Selected area and select Hide.
  Save sheet.

Works great. Except it really doesn't solve the problem, it just hides it. If you inspect the colModel there are still 16,384 columns defined, they're just hidden. What a waste.

There must be some way to determine "end of sheet".
In Excel if you click Ctrl-End it properly goes to the correct lowest
right cell of the active sheet area. Somewhere they must keep track of that?
Active sheet area rather the whole skeleton.

So it looks like this is a common Microsoft Problem which doesn't bother them
but messes up your import.

Since getting people to do this Hide before using a website to display or import a sheet is impractical you really need a means to test for "empty" columns and trim them. For in house use I can trim them but a general open sheet import on the web such as your demo page will have problems.

Hope that helps.

3

Help for ParamQuery Pro / Excel Soreadsheet Odd Behavior with Filter

« on: November 09, 2017, 11:56:33 pm »

Version 4.0.1

Finally got filters working in Spreadsheets. But I have new  problem with what it filters.
Simple Filter

{                         
   type: "textbox",       
   listener: "keyup",     
   condition: 'contain'   
}                         

The cells contain multi-line text. The top row contains the word DEEP, the second row contains the word ATTENDEE.
As I enter DEEP a character at a time it properly filters. When I get to DEE it's matching two rows as it should.
When you get to DEEP all rows disappear even though the top row should match.

If I then go to DEEPP, It displays a message "No Rows to Display". Go back to DEEP and the message goes away but still no row. It's like it has selected the row but something is keeping it from displaying. Drop back to DEE and it's fine again.

I've built a simple display page you can view here that demonstrates the glitch.

http://linka.site/excel/

This page also demonstrates the extra columns problem.
It always creates 16,384 columns despite extraCols: 0.

4

Help for ParamQuery Pro / Re: Adding Filters to Excel Spreadsheets

« on: November 09, 2017, 08:08:43 pm »

Pardon I figured out my problem.  An offus by oneus error.

I was counting the numberCell column as the 0 dataindx. This had me working on a column that was off screen due to the excessive width I was trying to change, so I didn't see the change.
As far as changing width, this now works nicely.

grid.colModel.width = '50%';
refreshCM();
refresh;

I'm also able to get filters to show which was the original problem
Sorry for the goof but maybe it will help some other poor soul.

5

Help for ParamQuery Pro / Re: Adding Filters to Excel Spreadsheets

« on: November 09, 2017, 06:25:33 pm »

Tried that. There seems to be a problem updating the column model in spreadsheets.
Here's what I'm doing trying to change a column width.

             grid.showLoading();
         //import xlsx file from remote location.
         pq.excel.importXl( {url: '<?php echo $url;?>'}, function( wb ){
            grid.importWb({
               workbook: wb,
               extraRows: 0,
               extraCols: 0
            });
            grid.hideLoading();
            var colM = grid.option('colModel');
            colM[2].width= "50%";
            var colM = grid.option('colModel', colM);
            grid.refreshCM();
            grid.refresh();

If that is correct code then it isn't working. Also tried refreshDataAndView()

6

Help for ParamQuery Pro / Re: Trim unused columns in Excel Spreadsheet

« on: November 09, 2017, 05:59:08 pm »

Yes II've set the rows and Cols to zero
   
                grid.importWb({
               workbook: wb,
               extraRows: 0,
               extraCols: 0
            });

Rows are correct, but it always make 16,384 total columns?

I tried extraCols: 1; Still got 16,384 columns. extraCols appears to be ignored.

7

Help for ParamQuery Pro / Adding Filters to Excel Spreadsheets

« on: November 08, 2017, 10:16:52 pm »

Version 4.0.1

Is it possible to add filters to an Excel spreadsheet?

I've tried adding through colModel and by inspection I can see the filter is added, but the sheet doesn't show it.

8

Help for ParamQuery Pro / Trim unused columns in Excel Spreadsheet

« on: November 08, 2017, 09:03:40 pm »

Version 4.0.1

Any way to trim or hide the umpteen zillion empty columns on the right of a spread sheet?

If I've only got a dozen active columns it makes using the elevator in the horizontal scrollbar impossible.

9

Help for ParamQuery Pro / Excel Grid Height changes When Scrolling Horizontally

« on: November 08, 2017, 04:26:01 pm »

Version 4.0.1

Importing a Spreadsheet. I would like it to fit the content of the spreadsheet  with no extra rows or columns.
I can do this and looks fine. Column 2 wraps to multiple lines in each cell. All other columns are one line of text.

Problems is when I scroll right, the moment column 2 is no longer visible the height of the grid collapses to the one line fields.
Very abrupt. Can it be set to remember the max height and keep it on scrolling horizontally.
I thought maybe {flex: one:true} would help but no luck.

I've attached the sample xlsx demonstrating the problem in your Excel spreadsheet demo page.

10

Bug Report / Importing Excel Spreadsheets

« on: October 26, 2017, 12:34:53 am »

PQ Grid 4.0.1

I made what I thought was a fairly simple Excel sheet and tried Importing it through your demo page at

https://paramquery.com/pro/demos/import-xlsx

It pretty much fell apart. Is it wortn pursuing this or is it just to new for prime time.?

I've attached a copy of the xlsx file for you to try.

11

Bug Report / Re: Horizontal Scrollbar Doesn't Adjust for Changing Viewport Width

« on: September 14, 2017, 04:19:29 am »

A bit more of a probably related problem.

If you have a table setup as above so that it needs to scroll horizontally and the column on the right edge is barely showing, like:



If you click the header of the partially hidden column to sort it, the change in focus pulls the header over to display all of it but it leaves the columns below behind.

12

Help for ParamQuery Pro / Sorting Indicator Question

« on: September 14, 2017, 02:49:08 am »

v4.0.1

Just learning this version.

When local sorting it appears that the sort goes through three states.

No Indicator: Sort in natural delivered DB order.
Down Indicator: Sort decending
Up Indicator: Sort ascending

and that each time you click on the header it cycles through all three states round and round.

This surprised me as I'm used to only two states, up and down. This confused one of my customers. Is there away to limit the toggle to two states, up and down?

13

Bug Report / Horizontal Scrollbar Doesn't Adjust for Changing Viewport Width

« on: September 13, 2017, 09:24:01 pm »

v4.0.1

If I create a table i Flex mode it will create for the current screen size with Horizontal scroll if wider than screen.
If you then change the viewport width the scrollbar doesn't necessarily adjust to fit. If you change to wider the scroll increases to match. If you change to narrower the scroll does not adjust and you loose the right scroll arrow.
If the screen was initially wide enough to not require a scroll and you narrow it, the scroll does not appear and you can't access the right side of the table.

Real use case like when using a tablet and you change between landscape and portrait and vice a versa.

Setup is

         height: 'flex',
         width: 'flex',
         flex:{on: true},
         maxWidth: '100%',

And event handler as per your demo

      pq.grid("#grid", obj)
      .on("refresh refreshCell", function (evt, ui) {
         if (ui.source != 'flex') {
            this.flex();
         }
      });

14

Help for ParamQuery Pro / Request Download Link

« on: September 12, 2017, 09:48:23 pm »

Request Download Link please
Transaction ID xxxxxxxxxxx0781L