Trim unused columns in Excel Spreadsheet

[arky]

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.

[paramvir]

Hopefully you already passed extraCols: 0 to importWb method.

If you still have empty columns, then import xlsx into js workbook first, remove extra columns from that and then import js workbook into the grid.

[arky]

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.

[paramvir]

In this example it pulls the columns correctly. https://paramquery.com/pro/demos/import-xlsx

In your worksheet, styles are defined for all columns, that's why it's importing all columns.

Probably it needs to ignore the columns without content even if style is defined for them.

I'm looking into it.

[arky]

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.

[arky]

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.