We have a java application which which receives xlsx files and saves them as xlsb files. When we try to open the xlsb files in Excel subsequently, we are getting a variety of issues with named ranges - they aren’t appearing in the formula bar anymore, and we get application errors when we try to reference them with VBA e.g. “Debug.Print ActiveWorkbook.Worksheets(“Sheet1”).Range(“thenamedrange1”).Value”
How can we use Aspose to save as xlsb and still preserve the named ranges’ usability in the saved excel file?
To ensure that named ranges are preserved when saving an Excel file as XLSB using Aspose.Cells, you should follow these steps:
Create Named Ranges: Make sure that you are correctly creating named ranges in your workbook before saving it. You can use the createRange method of the Cells collection to define named ranges.
Access Named Ranges: After creating named ranges, you can access them using the getNamedRanges method of the WorksheetCollection. This will allow you to verify that the named ranges are correctly set up in your workbook.
Save the Workbook: When saving the workbook, ensure that you are using the correct save options. Use the save method of the Workbook class and specify the file format as XLSB.
Here is a sample code snippet demonstrating how to create a named range and save the workbook as XLSB:
import com.aspose.cells.*;
public class SaveAsXlsb {
public static void main(String[] args) throws Exception {
// Create a new workbook
Workbook workbook = new Workbook();
// Access the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Create a named range
Cells cells = worksheet.getCells();
cells.createRange("A1", "B2").setName("MyNamedRange");
// Save the workbook as XLSB
workbook.save("output.xlsb", SaveFormat.XLSB);
}
}
Check for Compatibility: After saving, open the XLSB file in Excel and check if the named ranges appear in the formula bar. If you encounter issues, ensure that the named ranges are correctly defined and that there are no conflicts with existing names.
By following these steps, you should be able to preserve the usability of named ranges in the saved XLSB file. If you continue to experience issues, consider checking for any specific limitations or bugs related to named ranges in the version of Aspose.Cells you are using.
For more detailed information on working with named ranges in Aspose.Cells, you can refer to the official documentation here.
After initial testing, I am able to reproduce the issue as you mentioned by converting your template XLSX file to XLSB file format. I found in the output XLSB file, named ranges are not appearing in the Name box bar anymore although the named ranges are retained/there when using Name Manager in MS Excel.
Workbook wb = new Workbook("d:\\files\\with_worksheet_scoped_name.xlsx");
WorksheetCollection worksheets = wb.getWorksheets();
Range[] namedRanges = worksheets.getNamedRanges();
for (Range r : namedRanges) {
System.err.println(r.getName() + ": " + r.getAddress());
}
wb.save("d:\\files\\aspose_handled_with_worksheet_scoped_name.xlsb", SaveFormat.XLSB);
We require thorough evaluation of the issue. 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): CELLSJAVA-46108
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.
@cwedwards92
We are pleased to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose.Cells for Java v24.9) that we plan to release in the first half of September 2024. You will be notified when the next version is released.