Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

GeneralException when two workbooks are opened #771

Closed
zjomzjom opened this issue Oct 11, 2019 · 16 comments
Closed

GeneralException when two workbooks are opened #771

zjomzjom opened this issue Oct 11, 2019 · 16 comments

Comments

@zjomzjom
Copy link

@zjomzjom zjomzjom commented Oct 11, 2019

Excel javascript API (both 1908 and 1909 ) throws GeneralException when workbook calls ctx.sync() and in another workbook, a cell is selected.

Expected Behavior

Selection change in the active workbook should not affect other workbooks.

Current Behavior

When some cell is selected in the second workbook, an exception is thrown in the first workbook, where ctx.sync() is called. The problem does not occur when we open a new excel workbook as a separate instance (with /x parameter).

Steps to Reproduce, or Live Example

  1. open two workbooks
  2. run code in Scriptlab
async function run() {
  setInterval(async ()=>{
    await Excel.run(async (ctx) => {
      ctx.workbook.worksheets.add("sheet");
      await ctx.sync();
      let worksheet = ctx.workbook.worksheets.getItem("sheet");
      worksheet.delete();
      await ctx.sync();
    });
  }, 3000);
}
  1. select cell in the second workbook

Context

We have long-running jobs (downloading data and loading it into excel). During that, we want to work in the second workbook.

Your Environment

  • Platform: PC desktop
  • Host: Excel
  • Office version number: 1909
  • Operating System: Windows 10

Useful logs

git

@catchingyu

This comment has been minimized.

Copy link

@catchingyu catchingyu commented Oct 12, 2019

@zjomzjom how do you create/open the second workbook ? Do you open the second workbook through Excel UI directly?

@zjomzjom

This comment has been minimized.

Copy link
Author

@zjomzjom zjomzjom commented Oct 12, 2019

@catchingyu I have tried a few ways to open a new/second workbook. No matter which way I selected, the problem still exists.

  1. Creating new workbook through Excel UI
  2. Loading an xlsl file through Excel UI
  3. By clicking on excel icon in menu-start
  4. Opening an xlsl file
@MandytMSFT

This comment has been minimized.

Copy link

@MandytMSFT MandytMSFT commented Oct 14, 2019

I can repro this issue, file BUG 3710971 for track

@MandytMSFT

This comment has been minimized.

Copy link

@MandytMSFT MandytMSFT commented Oct 17, 2019

@zjomzjom could you help explain a little on your scenario ? The root cause has figured out, we want to know more about your case, for example, why use setInterval to add bunch of new worksheet ?

@zjomzjom

This comment has been minimized.

Copy link
Author

@zjomzjom zjomzjom commented Oct 17, 2019

I have used "setInterval" method to make some async actions. I wanted to show you that there is a problem with the working of multiple workbooks parallel. It is not only a problem with "worksheet.add" method. Calling ctx.sync() with selection in other workbooks throws an exception.
Another example: Scriptlab sample snippet "Gradient", but with little modifications (to be able to change focus to the second workbook before ctx.sync) also throws GeneralException:
git second

We have got datasets with a huge amount of documents and we want to work with these datasets in separated workbooks. For example, we want to download 50 000 documents from a certain dataset and load it to the first workbook. We can get the only chunk of data from backend per 1 call.

Steps of the algorithm are:

  1. download chunk of data
  2. make a grouping based on some attribute
  3. check if worksheet for certain group already exists and create if not (usage of ctx.sync())
  4. load data into excel (usage of ctx.sync())
  5. finish/goto step 1

Or another case: show the differences between excel and backend.

  1. download chunk of data
  2. check the differences
  3. highlight the differences (usage of ctx.sync() to change the background color of the cell)

These long-running operations may take for example 30mins. During that time, we want to work with another dataset in the second workbook and we are not able to do that, because of GeneralException when we select some cell in the second workbook.

Besides long-running operations, if you want to do some small things (color some cells) which would take for example 10 seconds, you cannot change active workbook during that operation.

@edwardcawley

This comment has been minimized.

Copy link

@edwardcawley edwardcawley commented Oct 28, 2019

We're also having this issue. JS which takes a minute or two to run as it is inserting many sheets with lots of data. In the mean time the user decides to do some other Excel work and opens another book, this crashes the JS.

Not always at the same line. It seems, as others have pointed out, that the context.sync() breaks when another book is being worked on, it could be any .sync().

Good to know about forcing another instance of Excel though as a workaround.

@catchingyu

This comment has been minimized.

Copy link

@catchingyu catchingyu commented Oct 29, 2019

Currently, this is a known gap for our Excel JS API working on multiple worksheets in the same process. The best workaround currently is to force to open another instance/process of Excel to solve this problem.

@catchingyu catchingyu closed this Oct 29, 2019
@zjomzjom

This comment has been minimized.

Copy link
Author

@zjomzjom zjomzjom commented Feb 20, 2020

Even if you force Excel to open in the new instance, opening files through the Windows Explorer still causes the problem:

https://docs.microsoft.com/en-us/office/troubleshoot/excel/force-excel-to-open-new-instance

"This method works only when you use the Excel icon to open the application. If you use File Open within the Excel application or double-click a file in Windows Explorer, the files will still open in the same instance as designed."

And

"There are some known issues in opening each Excel spreadsheet in its own instance. For example, see You cannot paste any attributes into a workbook in another instance of Excel. Therefore, you cannot copy any one of the following elements:
Formulas
Values
Formats
Comments"

@catchingyu, @MandytMSFT, @kbrandl could you tell me whether you will fix this issue soon?

@mitoihs

This comment has been minimized.

Copy link

@mitoihs mitoihs commented Feb 21, 2020

I was going to submit another issue, but it's very similar to mine, so I'll just post it there:

We're developing Excel addin. Sometimes, when our addin is currently working and I'm opening another Excel workbook, I'm getting:

GeneralException: There was an internal error while processing the request. at RuntimeError (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:10059:5) at ClientRequestContext.prototype.processRequestExecutorResponseMessage (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:13147:5) at Anonymous function (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:13054:5) at run (http://localhost:8080/app.js:49737:13) at Anonymous function (http://localhost:8080/app.js:49750:30) at flush (http://localhost:8080/app.js:45586:9)

Expected Behavior

No error or at least more descriptive message about what & why happened.

Current Behavior

GeneralException

Steps to Reproduce, or Live Example

It's hard to reproduce, it happens rarely. In our code it's happening like once for 20-30 new workbooks opened. I'll try to find out better repro and will provide it if/when found.

We're not opening/closing workbooks using office-js. We're just opening new Excel instances using Windows Start menu.

Context

We're trying to keep addin running stable no matter if user is currently opening/closing any workbooks. Our addin downloads and puts lots of data into Excel. Also we use ctx.workbook.worksheets.onAdded / ctx.workbook.worksheets.onDeleted events to keep track of already existing worksheets in our addin.

Your Environment

  • Platform [PC desktop, Mac, iOS, Office Online]: PC desktop
  • Host [Excel, Word, PowerPoint, etc.]: Excel
  • Office version number: 1912 (Build 12325.20298)
  • Operating System: Windows, Version 1909

So addin is running on Edge's WebView.

Useful logs

  • Console errors

GeneralException: There was an internal error while processing the request. at RuntimeError (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:10059:5) at ClientRequestContext.prototype.processRequestExecutorResponseMessage (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:13147:5) at Anonymous function (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.debug.js:13054:5) at run (http://localhost:8080/app.js:49737:13) at Anonymous function (http://localhost:8080/app.js:49750:30) at flush (http://localhost:8080/app.js:45586:9)

Surrounding statements:

var workbook=context.workbook;
var worksheets=workbook.worksheets;
// >>>>>
worksheets.load(["*"]);
// <<<<<
  • Screenshots
    image
  • Test file (if only happens on a particular file)
@lumine2008

This comment has been minimized.

Copy link

@lumine2008 lumine2008 commented Feb 24, 2020

Thank mitoihsThis is a known gap for our Excel JS API working on multiple worksheets,but there is a workaround that you could try is to open a new excel workbook as a separate instance.

@mitoihs

This comment has been minimized.

Copy link

@mitoihs mitoihs commented Feb 24, 2020

Thank you for the workaround. We can use it locally, but we cannot demand that our users will "hack" registry. (if I understand it right, the workaround is to use this)

@lumine2008

This comment has been minimized.

Copy link

@lumine2008 lumine2008 commented Feb 24, 2020

Correct, this is the workaround

@mitoihs

This comment has been minimized.

Copy link

@mitoihs mitoihs commented Feb 24, 2020

And is there an issue to follow about that "known gap"? Because I see that one is closed.

@lumine2008

This comment has been minimized.

Copy link

@lumine2008 lumine2008 commented Feb 24, 2020

it's in our backlog, the effort is not small, I would suggest that you could submit your request in uservoice and upvote for this feature for Excel online at https://officespdev.uservoice.com/

@mitoihs

This comment has been minimized.

Copy link

@mitoihs mitoihs commented Feb 24, 2020

According to your suggestion, I've submitted UserVoice request

@zjomzjom

This comment has been minimized.

Copy link
Author

@zjomzjom zjomzjom commented Feb 25, 2020

@lumine2008 As I wrote above, running in separated instances is not a sufficient workaround, because if you open Excel file through the explorer, it is being loaded to an existing instance. Additionally, you cannot copy formulas, values, formats, and comments through instances.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
9 participants
You can’t perform that action at this time.