How to create workbook-scope named ranges

I can have an Excel workbook with a mix of workbook and worksheet-scoped named ranges. The worksheet-scoped ranges appear in Aspose as "ws!name" and the workbook-scoped ranges appear as just "name". This is fine. However, I can't seem to generate a workbook using Aspose with any workbook-scoped named ranges.

The attached file was generated by Aspose. On sheet Source, all of the EHC_ names were defined without the "!" yet they appear in sheet scope, not workbook scope. The duplicate Employ_QtrEnd is my problem though perhaps an error should have been thrown to indicate a duplicate name was being set.

How can I create a workbook-scoped named range using Aspose?

Thanks,
--Howard

I have some more information about this problem. If I create worksheets and range names from scratch through the API the names do appear at workbook scope. The problem apparently relates to the use of Copy on a worksheet. What I'm doing is opening an existing Excel workbook and creating a subset from it by copying sheets into another, newly-created workbook. Immediately after copying the worksheet Source, the new workbook as in it 5 named ranges, all of them at worksheet scope, even though in the original file 4 out of 5 are at workbook scope. I like the fact that Copy copies named ranges that refer to cells on the copied sheet but would like it to maintain the correct scope as it does so.

Additionally, if I delete the worksheet-scoped name using Worksheets.DeleteName("Source!EHC_Admin") it disappears from the name list. If I now try to define that same name at workbook scope it gets defined at worksheet scope again! If I try another name, such as "Test", it gets correctly defined at workbook scope.

I've attached the source workbook for your information.

Thanks,
--Howard

Hi,

Thank you for considering Aspose.

Following is the reply to all of your above mentioned issues,

1: Too Generate Workbook and Worksheet scope Ranges
.
Please check the following codes to create workbook and worksheet scoped named ranges:

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

Cells cells = sheet.Cells;

Range localRange = cells.CreateRange("A1", "A10");

localRange.Name = "Sheet1!local";

Range workbookScope = cells.CreateRange("B1", "B10");

workbookScope.Name = "workbookScope";

workbook.Save(@"F:\FileTemp\dest.xls");

2: The issue of coping worksheets.

If you copy a worksheet in MS Excel, MS Excel will copy the named ranges of this worksheet and any workbook-scoped named ranges which referred ranges belong to this worksheet to the destination worksheet and the workbook-scoped named ranges will became worksheet-scoped range. So please use Workbook.Copy() method and not copy worksheets one by one to another workbook.

3: The issue of deleting named range.

After an initial test, we have found this issue and we will fix it soon.

Thank You & Best Regards,

Hi Howard,

Thank you for considering Aspose.

Please try the attached latest fix. We have fixed the bug regarding deleting the named range.

Thank You & Best Regards,

Thank you very much for the fix. The situation is improved but there are still issues. I've attached two Excel files. The aspose.xls file is the result of taking a larger workbook with many sheets, deleting all of the named ranges one by using xxx.Worksheets.DeleteName(xxx), then deleting all sheets except the Graphs sheet, and saving the workbook. There are several problems with the aspose.xls file:

1) the deleted named ranges are still there, yet I can watch in the code as they are deleted one by one and disappear from the list in xxx.Worksheets.GetNamedRanges;

2) the file is much larger than it should be. excel.xls is the result of loading and then saving the aspose.xls using Excel. Notice that it's only 44K. I see similar results with other workbooks produced via this method.

3) the charts, which are defined using named ranges, seem to be broken. In Excel, if I delete named ranges in use by charts, the charts continue to reference those ranges even though the ranges no longer exist, and when I recreate the ranges the charts link up correctly. Aspose appears to invalidate the charts so they lose the named ranges. In the first Revenue chart, the data series should all reference named ranges like Revenue_Shelters, etc. You can see that many of the series reference #Ref, and some reference Sales_xxx named ranges which were never referenced initially in this graphic. exceldirect.xls is a version made by deleting sheets in Excel and also some of the named ranges, in particular those referenced by the first chart. You can see that the file is small and retains the named ranges in the charts. In particular, you can put a few numbers in a row and define the named range Revenue_Shelters and the line will instantly appear even though that name was deleted from the workbook before it was saved.

We wish to accomplish the same thing using Aspose.

Thanks, --Howard

Hi Howard,

1) Well, Worksheets.DeleteNames() only deletes a name's reference, it will not remove the name from the names list.

2), 3)

We are working on the issues, we will let you know soon when we figure them out.

Thank you.

Amjad, thanks for your response. I don't really understand what you mean by "deletes a name's reference." So you're saying that to remove a name I actually Remove it from the Names list? So what does DeleteName really do, how does it compare to Names.Remove, and how do I decide which method to use to remove a name from the workbook?

Thanks,
--Howard

Hi,

We will explain you later.

Thanks for being patient!

Hi,

Following are a few notes to explain you.

1) Worksheets.DeleteName

It will only make Name.RefersTo as null and hides the name too. the user cannot get the named range by Worksheets.GetRangeByName() method again, but one can get the Name object using Worksheets.Names[string text] and this Name object would be useless.

2) Names.Remove(string text)

This method will remove the Name from the Worksheets.Names list.

3) Worksheets.GetRangeByName(string text)

We do not store the named range object. If there is such a defined name in the Worksheets.Names list and the defined name refers to a range, we will create and return a Range object, otherwise we will return null.

Hope it will give you some insight.

Thank you.

Hi,

2),3)

Please try the attached fix (4.6.0.12). We have fixed the issue regarding size of the generated file and the data source for the chart will be updated when calling Worksheets.RemoveAt() method to remove worksheet.

If you still find any problem, please post your template file and sample codes here, we will check it soon.

Thank you.

Thanks for the new release. While it does make significantly smaller files, a file with charts in it crashes Excel. I've attached one of the examples with a sheet of graphics in it.

Thanks,
--Howard

Hi,

Thank you for considering Aspose.

Please post your template file (which you used to generate the output.xls). We will check it soon.

Thank You & Best Regards,

I cannot post the complete template, but I've attached a pared-down version. We create the output file by loading the attached, deleting all of the named ranges using both DeleteName and Names.Remove, deleting the Employ_Early sheet, then saving the result.

Thanks for your support,
--Howard

Hi Howard,

Well, if you want to remove the name with Names.Remove method, please make sure that the name should not be referred by any formula or chart. We will definitely check whether it can be referred in this method later on for your requirements.

Please simply use DeleteName method for the time being, although you may still see a defined name in the Names list, but it does not effect again.

Thank you.

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have fixed your mentioned issue.Now for Names.RemoveAt() method, if the defined name is referred by others, we will only set the Name.ReferTo as null not remove it from the names list.

Also, we have added a new method Names.Clear(), this method will remove all the defined names which are not referred by others and remove the Referernce of the defined names which are refered by others.

Thank You & Best Regards,

Thanks for your rapid response. In attempting to verify this solution a new problem has emerged. The attached workbook was created by starting with a blank workbook, adding the sheets up to Source and merging in another workbook that provides the Source sheet along with a number of named ranges. Finally, we fill in the formulas and data on the sheets we created. However, the new formulas that reference the named ranges on the Source sheet fail to correctly link to the names and result in #NAME? errors in Excel. This worked fine in earlier versions. When you F2/Enter on these cells, they evaluate correctly.

I stopped in the debugger after merging the Source workbook but before creating the formulas and Worksheets.GetNamedRanges contains the list below, so it's not clear why a formula like =INDEX(Source!EHC_MARKETING,1,1) [cell Test!B10] wouldn't work.

Thanks again,
--Howard

[0]: "Source!EHC_Admin"
[1]: "Source!EHC_Engineering"
[2]: "Source!EHC_Marketing"
[3]: "Source!EHC_Sales"
[4]: "Source!Employ_QtrEnd"
[5]: "Period"
[6]: "Period_Time_Period"
[7]: "Period_Date"
[8]: "HC_From_DB_Sheet_Marketing"
[9]: "HC_From_DB_Sheet_Engineering"
[10]: "HC_From_DB_Sheet_Sales"
[11]: "HC_From_DB_Sheet_Admin"
[12]: "HC_From_DB_Sheet_Department"
[13]: "HC_From_DB_Sheet_Time_Period"
[14]: "HC_From_DB_Sheet_Date"

Using Excel, I can take a workbook that has two sheets, call them Input and Output. The Input sheet has named ranges on it and the Output sheet references those named ranges. You can see this in Stage1. To get to Stage2 I first delete the input worksheet. The references on the Output sheet turn into #REF's. I then, in Excel 2003, go into the Insert|Name|Define... dialog and delete the two names. The references turn into #NAME. That's what you find in Stage2 I can then copy the Input sheet from Stage1 back into the Stage2 file and the named ranges all hook up correctly and I get values in the cells on the Output sheet.

Could you please give me a short C# program fragment that would allow me to do exactly the same thing in Aspose? I can't seem to find any combination of deleting ranges and removing names that works. Names.Clear seems to be too aggressive, because in some cases Output will have names defined on it that I don't want to remove.

I also need this to work when the range references are in chart series.

Thanks very much,
--Howard

Hi Howard,

Thanks for providing us details.

We will look into the issue soon.

Thank you.

Hi Howard,

I think you may try to use the following options for your issues:

1) The issue of copying

Please use Workbook.Copy method instead of copying each worksheet with Worksheet.Copy method.

2) The issue of deleting

Please try the following codes:

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\Stage1.xls");


Range[] ranges = workbook.Worksheets.GetNamedRanges();
if (ranges != null)
{
for (int i = 0; i < ranges.Length; i++)
{
if (ranges[i].Worksheet.Name == "Input")
{
workbook.Worksheets.DeleteName(ranges[i].Name);
}
}
}
workbook.Worksheets.RemoveAt("Input");
workbook.Save(@"F:\FileTemp\dest.xls");

Thank you.

Thanks very much, I will try this approach and let you know.

Please note, however, that the latest version of Aspose.Cells is TOTALLY UNUSABLE for me until the problem reported in post 165712 is fixed. This is urgent as I am now relying on the new Worksheets.Save behavior.

Thanks,
--Howard