Set level rows up in the first element #2820

New Issue

LuciaLux · 2022-11-08T10:59:40Z

LuciaLux commented

2022-11-08 10:59:40 +00:00

this is my code:

if (isHeader && type_error == 'warning' || isHeader && type_error == 'notice'){
        wsrows.push({'hpt' : 27 , level:0});
		....
    }
else if(isHeader) {
        wsrows.push({'hpt' : 27 , level:0});
        ....
    }
else{
        wsrows.push({'hpt' : 27 , level:1});
		....
    }

in practice I go through the errors and group them by giving levels.
my problem is as follows (see img):
however, the grouping ends with the last unequal element.
is there the possibility to move the + up directly in the row of the first element?

this is my code: ``` if (isHeader && type_error == 'warning' || isHeader && type_error == 'notice'){ wsrows.push({'hpt' : 27 , level:0}); .... } else if(isHeader) { wsrows.push({'hpt' : 27 , level:0}); .... } else{ wsrows.push({'hpt' : 27 , level:1}); .... } ``` in practice I go through the errors and group them by giving levels. my problem is as follows (see img): however, the grouping ends with the last unequal element. is there the possibility to move the + up directly in the row of the first element?

Cattura.PNG

14 KiB

sheetjs commented

2022-11-08 16:11:38 +00:00

In the screenshot, rows 106:110 have a deeper level assignment. There are two possible rows for the collapse/expand icon (the +/- symbol): the row after the group (111) or the row before the group (105).

The default behavior is to show the grouping icon after the group (111 in your example). To show it on row 105, set the !outline property as described in https://docs.sheetjs.com/docs/csf/sheet#worksheet-object :

if(!ws["!outline"]) ws["!outline"] = {};
ws["!outline"].above = true; // summary rows above detail

In the screenshot, rows 106:110 have a deeper level assignment. There are two possible rows for the collapse/expand icon (the +/- symbol): the row after the group (111) or the row before the group (105). The default behavior is to show the grouping icon after the group (111 in your example). To show it on row 105, set the `!outline` property as described in https://docs.sheetjs.com/docs/csf/sheet#worksheet-object : ```js if(!ws["!outline"]) ws["!outline"] = {}; ws["!outline"].above = true; // summary rows above detail ```

sheetjs closed this issue

2022-11-08 16:11:39 +00:00

LuciaLux commented

2022-11-08 16:24:16 +00:00

Excuse the question, but in which part of my code should I enter this code?

LuciaLux reopened this issue

2022-11-08 17:02:49 +00:00

sheetjs commented

2022-11-08 17:04:13 +00:00

In your example, there is a wsrows variable that represents the rows array. You are either:

A) building wsrows and assigning back to the worksheet:

// assuming ws is the worksheet object
ws["!rows"] = wsrows;

B) setting wsrows to the worksheet property and mutating:

// assuming ws is the worksheet object
if(!ws["!rows"]) ws["!rows"] = [];
wsrows = ws["!rows"];

You can add the line right after either block, replacing ws with the name of the worksheet variable

In your example, there is a `wsrows` variable that represents the rows array. You are either: A) building `wsrows` and assigning back to the worksheet: ```js // assuming ws is the worksheet object ws["!rows"] = wsrows; ``` B) setting `wsrows` to the worksheet property and mutating: ```js // assuming ws is the worksheet object if(!ws["!rows"]) ws["!rows"] = []; wsrows = ws["!rows"]; ``` You can add the line right after either block, replacing `ws` with the name of the worksheet variable

sheetjs closed this issue

2022-11-08 17:04:13 +00:00

LuciaLux commented

2022-11-08 17:50:27 +00:00

not work for my code

LuciaLux reopened this issue

2022-11-08 17:50:27 +00:00

sheetjs commented

2022-11-08 18:02:07 +00:00

Note that it is a worksheet-level setting, so you cannot show some icons below and some icons above within the same worksheet.

Ensure you are using the latest version: https://docs.sheetjs.com/docs/getting-started/#installation

When you write, pass the option cellStyles: true.

Here is a live example https://jsfiddle.net/se26pz1x/

Code (click to show)

The resource https://cdn.sheetjs.com/xlsx-0.19.0/package/dist/xlsx.full.min.js is loaded before the script is run

function make_worksheet() {
  /* create worksheet */
  var data = Array.from({length: 10}, (_,i) => [i, "Row" + (i+1)]);
  var ws = XLSX.utils.aoa_to_sheet(data);

  /* set up rows structure */
  if(!ws["!rows"]) ws["!rows"] = [];
  var wsrows = ws["!rows"];

  for(var R = 1; R < 9; ++R) wsrows[R] = { hpt: 27, level: 1 };
  return ws;
}

var wb = XLSX.utils.book_new();

var ws1 = make_worksheet();
XLSX.utils.book_append_sheet(wb, ws1, "Below");

var ws2 = make_worksheet();
ws2["!outline"] = { above: true };
XLSX.utils.book_append_sheet(wb, ws2, "Above");

XLSX.writeFile(wb, "issue2820.xlsx", {cellStyles: true});

Note that it is a worksheet-level setting, so you cannot show some icons below and some icons above within the same worksheet. Ensure you are using the latest version: <https://docs.sheetjs.com/docs/getting-started/#installation> When you write, pass the option `cellStyles: true`. Here is a live example https://jsfiddle.net/se26pz1x/ <details><summary><b>Code</b> (click to show)</summary> The resource <https://cdn.sheetjs.com/xlsx-0.19.0/package/dist/xlsx.full.min.js> is loaded before the script is run ```js function make_worksheet() { /* create worksheet */ var data = Array.from({length: 10}, (_,i) => [i, "Row" + (i+1)]); var ws = XLSX.utils.aoa_to_sheet(data); /* set up rows structure */ if(!ws["!rows"]) ws["!rows"] = []; var wsrows = ws["!rows"]; for(var R = 1; R < 9; ++R) wsrows[R] = { hpt: 27, level: 1 }; return ws; } var wb = XLSX.utils.book_new(); var ws1 = make_worksheet(); XLSX.utils.book_append_sheet(wb, ws1, "Below"); var ws2 = make_worksheet(); ws2["!outline"] = { above: true }; XLSX.utils.book_append_sheet(wb, ws2, "Above"); XLSX.writeFile(wb, "issue2820.xlsx", {cellStyles: true}); ``` </details>

LuciaLux commented

2022-11-08 18:33:39 +00:00

my code is structured in a much more complex way, is there any way to share it?

ps: I have the latest version of xlsx

my code is structured in a much more complex way, is there any way to share it? ps: I have the latest version of xlsx

sheetjs commented

2022-11-08 18:39:07 +00:00

We've explained the situation and provided a live demo.

If you want a SheetJS teammate to take a look at your code, we can offer paid support. Please send an email to hello@sheetjs.com for more info.

We've explained the situation and provided a live demo. If you want a SheetJS teammate to take a look at your code, we can offer paid support. Please send an email to hello@sheetjs.com for more info.

sheetjs closed this issue

2022-11-08 18:39:07 +00:00

LuciaLux commented

2022-11-08 18:49:07 +00:00

I saw the live but the problem is not solved in that case anyway.

as we said, the result that I would like is that the grouping does not end with - in the element following the grouping itself but in the first with a +.

I saw the live but the problem is not solved in that case anyway. as we said, the result that I would like is that the grouping does not end with - in the element following the grouping itself but in the first with a +.

LuciaLux reopened this issue

2022-11-08 18:49:08 +00:00

LuciaLux commented

2022-11-08 18:50:08 +00:00

I'm sorry for bothering you and sorry for the questions.
however, the problem is not solved.

good evening

I'm sorry for bothering you and sorry for the questions. however, the problem is not solved. good evening

sheetjs commented

2022-11-08 18:56:36 +00:00

If you open the demo page, it will generate an Excel file. Open the file in Excel. There are two worksheets. In each worksheet, rows 2:9 (SheetJS rows 1 to 8) are grouped.

In the "Below" worksheet (see "issue2820below" screenshot), the grouping symbol shows up in Excel row 10

In the "Above" worksheet (see "issue2820above" screenshot), the outline property is set and the grouping symbol shows up in Excel row 1

The symbol is (-) because each row is visible (and clicking the icon will collapse the rows.

Are you asking about something different?

If you open the demo page, it will generate an Excel file. Open the file in Excel. There are two worksheets. In each worksheet, rows 2:9 (SheetJS rows 1 to 8) are grouped. In the "Below" worksheet (see "issue2820below" screenshot), the grouping symbol shows up in Excel row 10 In the "Above" worksheet (see "issue2820above" screenshot), the outline property is set and the grouping symbol shows up in Excel row 1 The symbol is (-) because each row is visible (and clicking the icon will collapse the rows. Are you asking about something different?

issue2820below.png

60 KiB

issue2820above.png

53 KiB

LuciaLux commented

2022-11-10 13:12:49 +00:00

Sorry, the code is certainly exact but it varies according to the excel with which I open the file.
furthermore, loading it in the drive does not bring the changes back.

this work with openoffice but not with excel or drive

Sorry, the code is certainly exact but it varies according to the excel with which I open the file. furthermore, loading it in the drive does not bring the changes back. this work with openoffice but not with excel or drive

Sign in to join this conversation.