manually set Dimension ref #2840

New Issue

desplodur · 2022-12-20T13:35:54Z

desplodur commented

2022-12-20 13:35:54 +00:00

Dear Support team,

i want to manually set the "Dimension ref" of the sheets in the exported xlsx document.
Unfortunately the ref stays the same even if i change it before the XLSX.writefile function.

Does sheetsjs handles the dimension ref or is this not implemented?

Kind regards
Laurenz Rudolph

Dear Support team, i want to manually set the "Dimension ref" of the sheets in the exported xlsx document. Unfortunately the ref stays the same even if i change it before the XLSX.writefile function. Does sheetsjs handles the dimension ref or is this not implemented? Kind regards Laurenz Rudolph

sheetjs commented

2022-12-22 20:57:42 +00:00

You should be able to manually edit the worksheet range. For example:

var ws = XLSX.utils.aoa_to_sheet([[1]]);
ws["!ref"] = "A1:D3";
var wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
XLSX.writeFile(wb, "issue2840.xlsx");

(live demo https://jsfiddle.net/pyskLu1c/)

To verify, go to https://oss.sheetjs.com/cfb-editor/#/ and drag-drop the generated file, then click "xl/worksheets/sheet1.xml" and click "view as text" in the main panel. You should see:

  <dimension ref="A1:D3"/>

You should be able to manually edit the worksheet range. For example: ```js var ws = XLSX.utils.aoa_to_sheet([[1]]); ws["!ref"] = "A1:D3"; var wb = XLSX.utils.book_new(); XLSX.utils.book_append_sheet(wb, ws, "Sheet1"); XLSX.writeFile(wb, "issue2840.xlsx"); ``` (live demo https://jsfiddle.net/pyskLu1c/) To verify, go to https://oss.sheetjs.com/cfb-editor/#/ and drag-drop the generated file, then click "xl/worksheets/sheet1.xml" and click "view as text" in the main panel. You should see: ```xml <dimension ref="A1:D3"/> ```

sheetjs commented

2022-12-29 18:30:53 +00:00

Closing for now, but please reopen if you are finding issues editing the worksheet range.

sheetjs closed this issue

2022-12-29 18:30:53 +00:00

desplodur commented

2023-01-12 14:55:34 +00:00

Thanks for the fast response. I tried this and it didnt work in our case.

After some research i´ve noticed that our template has a dimension ref which doenst get overwritten.

I tried to reproduce it in a simple example with jsfiddle, but i cannot access our bought @sheet/edit library there.

Our Case (Does not update the dimension ref):

data.forEach(([position, value]) => {
	XLSX.utils.template_set_aoa(template, worksheet_name, position, [
      [value],
    ]);
})

Simple example (Does update the dimension ref):

data.forEach(([position, value]) => {
 XLSX.utils.sheet_add_aoa(template, [[value]], , {origin: position})
 }

Is there a possibility to add this functionality into the template_set_aoa function?

Kind regards
Laurenz

Thanks for the fast response. I tried this and it didnt work in our case. After some research i´ve noticed that our template has a dimension ref which doenst get overwritten. I tried to reproduce it in a simple example with jsfiddle, but i cannot access our bought @sheet/edit library there. Our Case (Does not update the dimension ref): ``` data.forEach(([position, value]) => { XLSX.utils.template_set_aoa(template, worksheet_name, position, [ [value], ]); }) ``` Simple example (Does update the dimension ref): ``` data.forEach(([position, value]) => { XLSX.utils.sheet_add_aoa(template, [[value]], , {origin: position}) } ``` Is there a possibility to add this functionality into the template_set_aoa function? Kind regards Laurenz

desplodur reopened this issue

2023-01-12 14:55:37 +00:00

sheetjs commented

2023-01-12 17:30:00 +00:00

Please send an email to pro@sheetjs.com with a sample file / value to be changed. The editor should be updating the range if you are inserting new cells.

sheetjs closed this issue

2023-01-12 17:30:00 +00:00

Sign in to join this conversation.