Solvedsheetjs how to set the automatic width?

Hello, use it for the first time. I don't know how to set the col width. The content is hidden

17 Answers

✔️Accepted Answer

@cjlhll

Please see my solution

  1. get maximum width from the json data
  2. set column width
let objectMaxLength = []; 
    for (let i = 0; i < json.length; i++) {
      let value = <any>Object.values(json[i]);
      for (let j = 0; j < value.length; j++) {
        if (typeof value[j] == "number") {
          objectMaxLength[j] = 10;
        } else {
          objectMaxLength[j] =
            objectMaxLength[j] >= value[j].length
              ? objectMaxLength[j]
              : value[j].length;
        }
      }
    }
    console.log(objectMaxLength);

    var wscols = [
      { width: objectMaxLength[0] },  // first column
      { width: objectMaxLength[1] }, // second column
      { width: objectMaxLength[2] }, //...
      { width: objectMaxLength[3] }, 
      { width: objectMaxLength[4] },
      { width: objectMaxLength[5] }, 
      { width: objectMaxLength[6] }, 
      { width: objectMaxLength[7] }, 
      { width: objectMaxLength[8] },
      { width: objectMaxLength[9] }
    ];

    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
    worksheet["!cols"] = wscols;

Other Answers:

Creds: @chenlitchian + @dave-wind

This is esp useful when you are user header option that will change the order of your cols. (Please note this is typescript. Just remove the types from fun-args).

I ended up using:

  private autofitColumns(json: any[], worksheet: XLSX.WorkSheet, header?: string[]) {

    const jsonKeys = header ? header : Object.keys(json[0]);

    let objectMaxLength = []; 
    for (let i = 0; i < json.length; i++) {
      let value = json[i];
      for (let j = 0; j < jsonKeys.length; j++) {
        if (typeof value[jsonKeys[j]] == "number") {
          objectMaxLength[j] = 10;
        } else {

          const l = value[jsonKeys[j]] ? value[jsonKeys[j]].length : 0;

          objectMaxLength[j] =
            objectMaxLength[j] >= l
              ? objectMaxLength[j]
              : l;
        }
      }

      let key = jsonKeys;
      for (let j = 0; j < key.length; j++) {
        objectMaxLength[j] =
          objectMaxLength[j] >= key[j].length
            ? objectMaxLength[j]
            : key[j].length;
      }
    }

    const wscols = objectMaxLength.map(w => { return { width: w} });

    worksheet["!cols"] = wscols;

  }

You can set the width of a column doing this

let newSheet = utils.json_to_sheet(yourData)
newSheet['!cols'].push({ width: 20 })

I created a library that does it automatically and uses this xlsx library, check out json-as-xlsx

@chenlitchian i've edited your code for complete autofit with values (your versione) and headers

		for (let i = 0; i < json.length; i++) {
		let value = <any>Object.values(json[i]);
		for (let j = 0; j < value.length; j++) {
			if (typeof value[j] == "number") {
				objectMaxLength[j] = 10;
			} else {
				objectMaxLength[j] =
					objectMaxLength[j] >= value[j].length
						? objectMaxLength[j]
						: value[j].length;
			}
		}
		let key = <any>Object.keys(json[i]);
		for (let j = 0; j < key.length; j++) {
			objectMaxLength[j] =
				objectMaxLength[j] >= key[j].length
					? objectMaxLength[j]
					: key[j].length;
		}
	}

for those just passing a worksheet i found this to be the fasted way to get an autofit (using the lodash range function)

export function autofitColumns(worksheet: WorkSheet) {
  let objectMaxLength: ColInfo[] = [];
  const [startLetter, endLetter] = worksheet['!ref']?.replace(/\d/, '').split(':')!;
  const ranges = range(startLetter.charCodeAt(0), endLetter.charCodeAt(0) + 1);
  ranges.forEach((c) => {
    const cell = String.fromCharCode(c);
    const cellLength = worksheet[`${cell}1`].v.length + 1;
    objectMaxLength.push({ width: cellLength });
  });
  worksheet['!cols'] = objectMaxLength;
}

Related Issues:

246
sheetjs How to simply export a Worksheet to xlsx?
There are two issues: each object is mapped to a row so if you want a row with name John and city Se...
79
sheetjs Doesn't work with browserify or webpack.
I was able to get it building by adding the following to my webpack config: EDIT: please raise a new...
45
sheetjs Corrupt XLSX file after downloading
Ok Sorry for the monologue but I solved it: In the frontend when making the GET Request ...
44
sheetjs Change header´s title when using json_to_sheet
There's no json_to_xlsx function you probably mean json_to_sheet That being said the easiest way is ...
27
sheetjs how to set the automatic width?
@cjlhll Please see my solution get maximum width from the json data set column width ...
20
sheetjs merge cells from Array of Arrays
You need to build up the worksheet first then add the merges to the worksheet Hi ...
15
sheetjs format Date question
On the read side you also need to pass cellText:false: In the conversion you shouldn't set raw: raw:...
14
sheetjs Date conversion loses 1 day
My date in excel is 2019-03-04 and finally i got 2019-03-03T15:59:17.000Z when XLSX.utils.sheet_to_j...
11
sheetjs Get the header column from excel
@kalai7890 If you just want to pull the header row the easiest way is to just walk the cells in the ...
885
components Error: mat-form-field must contain a MatFormFieldControl.
You have to add to app.module.ts: Bug feature request or proposal: When doing a karma/jasmine test I...
608
angular Angular5.x lazyLoad problem, undefined is not a function
For others that find this issue via Google as i did: I had the same problem when trying to lazy load...
348
angular Cyclic dependency error with HttpInterceptor
I resolved simply not setting authService in constructor but getting in the intercept function. ...
286
angular cli Issue: Cannot read property 'thisCompilation' of undefined
Here is the answer from issues #9793 : npm remove webpack npm install webpack@3.11.0 ...
277
angular Uncaught Error: Can't resolve all parameters for ...
You are missing an @Injectable() annotation on your ApiService Support requests like these should li...
275
ngx bootstrap fix(tooltip): tooltip flickering when hover the mouse on the border on the tooltip position
Add pointer-events: none; to the tooltip css problem solved! .tooltip { pointer-events: none; } ...
266
angular Force reload/refresh current route with RouteReuseStrategy
Hi If you really need to trick the Router into reloading the component on each routerLink click ...
260
angular Misleading error message "Cannot find a differ supporting object '[object Object]'"
I just ran into the same issue I'm not sure if the recommended solution will work for my case ...
255
angular cli Error | Could not find plugin "proposal-numeric-separator" | Generating ES5 bundles for differential loading
Try to install @babel/compat-data: ~7.8.0 to devDependencies This is the only workaround which helpe...
251
angularfire Missing exported members after updating to Angular 6.0.0
It looks like rxjs-compat is missing Use npm i rxjs-compat to install the missing rxjs-compat. ...
236
angular cli Using invalid environment causes error in build (was "ERROR in Cannot read property 'length' of undefined")
Probably not the same cause but we had the same error (Cannot read property 'length' of undefined) w...
232
angular cli ng serve method ip not open in another machines
The solution is simple Just bind to the correct interface address via ng serve --host=0.0.0.0 ...
226
angular cli Incorrect JS paths when ng build -prod is deployed in a subfolder
When you're deploying to non-root path within a domain you'll need to manually update the <base href...
224
angular update 2 to 4 has problem [ts] Property 'map' does not exist on type 'Observable<Response>'.
I met the same problem with the angular cli 6.0.0 and rxjs 6.1.0 And I solved the problem by replaci...
218
angular cli Installation error: permission denied for node-sas
sudo npm install -g --unsafe-perm --verbose @angular/cli Installation error (node-sass) with: node v...
208
angular cli ng build -prod Module not found: Error: Can't resolve './$$_gendir/app/app.module.ngfactory'
Instead of use ng build --prod use ng build --env=prod UPDATE As many of us (thx) have mention this ...
184
angularfire ERROR TypeError: Object(…) is not a function” using AngularFirestore and firebase
@jamesdaniels Yes npm install rxjs@6 rxjs-compat@6 --save It solved the issue. I want to use firebas...
175
storybook Webpack 4 support?
I'm getting the below error when running Storybook with a project using Webpack 4; This is preventin...
173
angular cli Property 'controls' does not exist on type 'AbstractControl'.
Got it! Looked at the docs - FormArray The FormArray class contains the controls property ...
170
angular Angular2 AoT Compiler Errors
pls try /cc @chuckjaz When I try to compile my project with ngc it throws the below error: Error: Er...
167
angular cli [Bug] cli needs to be reloaded when a lazy loaded module is added in routes (FIXED)
I recomended change { path: 'user-panel' loadChildren: './user-panel/user-panel.module#UserPanelModu...
167
prettier Change HTML/JSX formatting to have one attribute/prop per line
The point I am trying to make is that the vertical space matters Wasting it (too much) has negative ...
164
angularfire @firebase/firestore: Firestore (5.8.0): timestampsInSnapshots
eg: app.module.ts Version info Angular: 7.2.1 Firebase: 5.8.0 AngularFire: 5.1.1 In the newest fireb...
162
angular cli AOT build fails
I noticed same problem and after npm i enhanced-resolve@3.3.0 I can build with AOT ...
161
angular cli Cannot find name 'require'. using relative paths...
Ahhh here we go try adding declare var require: any; To src/typings.d.ts I am using a config with re...
160
components [Table] Add example with dynamic columns
For those interested Bug feature request ...
157
angular starter How to include Twitter Bootstrap 3 using webpack
I just tested it now Hi I have taken a look at #214 & #215 but these don't really explain how to set...
152
angular HttpClient.delete() cannot handle a body in its request
It would be great to have body param in .delete() We just migrated our project to HttpClient and for...
147
ng bootstrap No provider for NgbActiveModal! => "Components as content"
try to provide NgbActiveModal on app.module Bug description: When trying to implement the Modal usin...
146
angular cli "No NgModule metadata found for 'AppModule'" after Upgrade to Angular 5.1.0 and AngularCli 1.6.0
I got the same error The issue is fixed only when i manually re installed angular/cli The webpack sh...
142
angularfire 5.2.1 AngularFireAuthGuard causes TypeError: source.lift is not a function
I worked in this way Performing basic docs for AngularFireAuthGuard produces an error related to sou...
141
angular cli ng serve doesn't listen other than localhost
Works with command ng serve --host 0.0.0.0 OS? Windows 7 8 or 10 Linux (which distribution) Mac OSX ...
140
angular Http - Observable completed function not firing
Third callback haven't been called when error occures ES6 promises hasn't method finally only then a...
133
angular Using multiple components in different modules causing "Type X is part of the declarations of 2 modules" error
as @brandonroberts saids create a shared module like this: then use the SharedModule like this.. ...
129
core How to unit test a component with TranslateService and the translate pipe?
I'm just coming up to speed on this stack and was trying to wrap some tests around an Ionic componen...
125
angular cli ng: command not found
Removing NODE and using NVM instead fixed a lot of issues After installing angular-cli with the help...
125
angular cli ng build fails with error Cannot find module 'webpack/lib/node/NodeTemplatePlugin' after updating to v1.3.0
I faced this issue many times recently and always the fix worked for me is : Bug Report or Feature R...
124
angular cli -bash: ng: command not found
This was a pain to figure out Please provide us with the following information: OS? Mac OSX (Seirra)...
124
angular cli Uncaught TypeError: Cannot read property 'apply' of undefined on new install
Downgraded to zone.js@0.8.5 and the problem disappeared. Bug Report or Feature Request (mark with an...
122
angular cli I am on Angular 5 and CLI 1.5 and I get an error
I faced this error after updating a non-CLI project to Angular 5 and @ngtools/webpack 1.8.0 ...