Set level rows up in the first element #2820

Open
opened 1 month ago by LuciaLux · 11 comments

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?
Owner

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 1 month ago
Poster

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

Excuse the question, but in which part of my code should I enter this code?
LuciaLux reopened this issue 1 month ago
Owner

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 1 month ago
Poster

not work for my code

not work for my code
LuciaLux reopened this issue 1 month ago
Owner

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>
Poster

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
Owner

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 1 month ago
Poster

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 1 month ago
Poster

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
Owner

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?
Poster

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.
No Milestone
No project
No Assignees
2 Participants
Notifications
Due Date

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#2820
Loading…
There is no content yet.