Check if Worksheet Has Errors, Check if Series has errors

jamieMacabacus · March 28, 2025, 10:44pm

Hello!
I am using the python sdk 25.3.1. I’m trying to do some checks on an excel workbook before I upload it, but I can’t seem to find the way to do them through aspose cloud cells. Is it possible to:

Check if a workbook/worksheet has any formula errors in it’s cells (like #REF for example)
Check if a chart has any formula errors in it’s series
Check if a workbook has any cells that reference data in another workbook
Check if a worksheet has any cells that reference data on another worksheet
Check if a series formula references any external workbooks/worksheets

I’ve looked through the docs, but I can’t seem to find anything related to these topics. The response to get_workbook_properties includes some fields like HyperlinksChanged and LinksUpToDate, but I’m not sure what those fields actually represent. The docs don’t describe them.

Also, the general info links on Series, Charts and Chartpoints seem to broken (I get 404s for all of them):
https://docs.aspose.cloud/cells/specification/model/series/
https://docs.aspose.cloud/cells/specification/model/seriesresponse/
https://docs.aspose.cloud/cells/specification/model/chartpoints/
https://docs.aspose.cloud/cells/specification/model/chart/

Professionalize.Discourse · March 28, 2025, 10:44pm

@jamieMacabacus

Can you please specify which programming language you are using for the checks on the Excel workbook? Additionally, could you clarify what specific errors you are looking for in the formulas?

jamieMacabacus · March 28, 2025, 10:45pm

I’m using the Aspose Cells Cloud Python SDK 25.3.1 and I’m looking to perform a few pre-upload checks on Excel workbooks.

Formula Error Checks

I’d like to check for any and all formula errors, such as:

#REF! – Invalid cell reference
#VALUE! – Incorrect data type
#DIV/0! – Division by zero
#NAME? – Unrecognized name or function
#N/A – Lookup errors
#NUM! – Invalid numeric value
#NULL! – Invalid intersection
#SPILL! – Array overflow (Excel 365/2021 feature)

These errors may appear in worksheet cells or in chart series formulas, so I’d like to check both.

External Reference Checks

I’m also looking to:

Detect cells referencing external workbooks.
Identify worksheet-level references to other sheets.
Validate whether a chart’s series formula references an external workbook or sheet.

wangtao · March 30, 2025, 1:46pm

@jamieMacabacus ,

We have received your requirements and will analyze them promptly to provide a solution and development estimate as soon as possible.

xuejianzhang · March 30, 2025, 3:17pm

@jamieMacabacus Hello, currently we don’t have a way to quickly complete these functions through the existing API. Can you describe in more detail the content you need to check? We will develop a new API for this purpose, and control it to call 1-2 times to complete a check.

xuejianzhang · March 31, 2025, 2:58am

@jamieMacabacus
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): {CELLSCLOUD-13149}
Issue ID(s): {CELLSCLOUD-13150}

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

jamieMacabacus · March 31, 2025, 4:46pm

Howdy! Thanks for opening up the tickets.

Regarding your request for more detail/context:
I have a an api service that allows users to upload excel files. I want to check these files for errors before I allow the upload. User’s might want to only upload one worksheet, one chart, a named range, or the entire workbook, so I’d love to be able to check for errors at these different levels of granularity (e.g. sometimes I might want to check if there’s an error in a specific named_range and sometimes I might want to check if there is an error anywhere on the worksheet.

Here’s a complete list of the checks I’d like to perform, along with the target locations where these checks would be applied:

Check if a chart series formula is error-free
- Detects any invalid or broken references (#REF!, #VALUE!, etc.) in a chart’s series formula.
Check if a chart series formula references external workbooks or a separate worksheet
- Identifies whether a chart series formula points to:
  - Another workbook.
  - A different worksheet within the same workbook.
Check if a worksheet has formula errors in any of the cells
- Scans all cells in the worksheet to detect errors like #REF!, #VALUE!, #DIV/0!, etc.
Check if a worksheet has cells that reference data on another worksheet or in another workbook
- Flags any cell formulas that pull data:
  - From a different worksheet within the same workbook.
  - From an external workbook.
Check if a named range has any formula errors
- Validates that all cells inside a named range are error-free.
Check if a named range contains cells that reference data on another worksheet or in another workbook
- Checks whether any cells inside a named range reference:
  - Another worksheet.
  - An external workbook.
Check if a named range has multiple areas
- Ensures that the named range references a single contiguous range instead of multiple non-contiguous areas.
Get the shapes inside a named range
- Retrieves all shapes (charts, images, form controls, etc.) inside a named range.

While efficiency is always appreciated, some of these checks are quite dissimilar from each other, so I totally understand if it will take several API calls to perform them all.

Thank you for opening up the tickets! I really appreciate it

wangtao · April 1, 2025, 12:28am

@jamieMacabacus ,

We receive your feedback, and we analyze and complete it as soon as possible

xuejianzhang · April 24, 2025, 8:28am

@jamieMacabacus We updated the latest cells cloud version 25.4.0 and added the checkexternalreference and checkformulaerrors API to meet this function.
The sample code to check whether an external workbook or worksheet is referenced is as follows:

var url = BuildUrl(string.Format("checkexternalreference"));
string bodyParam = null;
Dictionary<string, string> headParams = null;
Dictionary<string, object> formParams = null;
DTO.Options.CheckExternalReferenceOptions checkExternalReferenceOptions = new DTO.Options.CheckExternalReferenceOptions();
checkExternalReferenceOptions.FileInfo = GetFileInfo("BookFormula.xlsx");
bodyParam = JsonConvert.SerializeObject(checkExternalReferenceOptions);
var response = ApiInvoker.InvokeApiAsync<DTO.Response.CheckedExternalReferenceResponse>(url, "POST", bodyParam, headParams, formParams).Result;
Assert.IsNotNull(response);
DownloadObject(response, "BookFormula_ExternalReference.json");

The sample code for checking formula errors is as follows (different levels correspond to different parameter settings):
This is to check the formula errors of the entire file

 var url = BuildUrl(string.Format("checkformulaerrors"));
 string bodyParam = null;
 Dictionary<string, string> headParams = null;
 Dictionary<string, object> formParams = null;
 DTO.Options.CheckFormulaErrorOptions checkFormulaErrorOptions = new DTO.Options.CheckFormulaErrorOptions();
 checkFormulaErrorOptions.FileInfo = GetFileInfo("FormulaError.xlsx");
 bodyParam = JsonConvert.SerializeObject(checkFormulaErrorOptions);
 var response = ApiInvoker.InvokeApiAsync<DTO.Response.CheckedFormulaErrorsResponse>(url, "POST", bodyParam, headParams, formParams).Result;
 Assert.IsNotNull(response);
 DownloadObject(response, "FormulaError.json");

Check the formula errors of a certain worksheet：

var url = BuildUrl(string.Format("checkformulaerrors"));
string bodyParam = null;
Dictionary<string, string> headParams = null;
Dictionary<string, object> formParams = null;
DTO.Options.CheckFormulaErrorOptions checkFormulaErrorOptions = new DTO.Options.CheckFormulaErrorOptions();
checkFormulaErrorOptions.FileInfo = GetFileInfo("FormulaError.xlsx");
checkFormulaErrorOptions.SheetName = "Sheet1";
bodyParam = JsonConvert.SerializeObject(checkFormulaErrorOptions);
var response = ApiInvoker.InvokeApiAsync<DTO.Response.CheckedFormulaErrorsResponse>(url, "POST", bodyParam, headParams, formParams).Result;
Assert.IsNotNull(response);
DownloadObject(response, "FormulaError_worksheet.json");

Check the corresponding formula errors according to name：

var url = BuildUrl(string.Format("checkformulaerrors"));
string bodyParam = null;
Dictionary<string, string> headParams = null;
Dictionary<string, object> formParams = null;
DTO.Options.CheckFormulaErrorOptions checkFormulaErrorOptions = new DTO.Options.CheckFormulaErrorOptions();
checkFormulaErrorOptions.FileInfo = GetFileInfo("BookFormula.xlsx");
checkFormulaErrorOptions.Names = new List<string> { "Bon"};
bodyParam = JsonConvert.SerializeObject(checkFormulaErrorOptions);
var response = ApiInvoker.InvokeApiAsync<DTO.Response.CheckedFormulaErrorsResponse>(url, "POST", bodyParam, headParams, formParams).Result;
Assert.IsNotNull(response);
DownloadObject(response, "BookFormula_name.json");

Check the corresponding formula errors according to chart：

var url = BuildUrl(string.Format("checkformulaerrors"));
string bodyParam = null;
Dictionary<string, string> headParams = null;
Dictionary<string, object> formParams = null;
DTO.Options.CheckFormulaErrorOptions checkFormulaErrorOptions = new DTO.Options.CheckFormulaErrorOptions();
checkFormulaErrorOptions.FileInfo = GetFileInfo("BookFormula.xlsx");
checkFormulaErrorOptions.ChartIndex = 0;
bodyParam = JsonConvert.SerializeObject(checkFormulaErrorOptions);
var response = ApiInvoker.InvokeApiAsync<DTO.Response.CheckedFormulaErrorsResponse>(url, "POST", bodyParam, headParams, formParams).Result;
Assert.IsNotNull(response);
DownloadObject(response, "BookFormula_chart.json");