I am trying to find an equivalent method in Aspose.Cells to Styles.Merge in VBA. Does such a method exist, and if not, can you provide a solution to accomplish this?
In Aspose.Cells, there isn’t a direct equivalent to the VBA Styles.Merge method. However, you can achieve similar functionality by merging cells and applying styles programmatically.
To merge cells in Aspose.Cells, you can use the Merge method available in the Cells class. This method allows you to specify the range of cells you want to merge. Here’s a code example demonstrating how to merge cells and apply styles to the merged cell:
// Create a new workbook
Workbook workbook = new Workbook();
// Get the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(0);
// Get the cells collection
Cells cells = worksheet.getCells();
// Merge cells from C6 to E7
cells.merge(5, 2, 2, 3); // Merges cells C6:E7
// Set value for the merged cell (C6)
cells.get(5, 2).putValue("This is my value");
// Create a style object to apply formatting
Style style = cells.get(5, 2).getStyle();
Font font = style.getFont();
font.setName("Times New Roman");
font.setSize(18);
font.setColor(Color.getBlue());
font.setBold(true);
font.setItalic(true);
// Set background color for the merged cell
style.setForegroundColor(Color.getRed());
style.setPattern(BackgroundType.SOLID);
// Apply the style to the merged cell
cells.get(5, 2).setStyle(style);
// Save the workbook
workbook.save("MergedCellsExample.xls");
In this example:
- We create a new workbook and access the first worksheet.
- We merge the cells from C6 to E7 using the
mergemethod. - We set a value for the merged cell and apply various styles, such as font name, size, color, and background color.
- Finally, we save the workbook.
This approach effectively simulates the merging and styling capabilities you would find in VBA’s Styles.Merge method. For more details on merging and unmerging cells, you can refer to the official Aspose documentation on Merging and Unmerging Cells [1].
Sources:
[1]: Merging and Unmerging Cells - Aspose Documentation
Yeah, this isn’t going to accomplish what I want. If you actually look at the VBA method signature for Styles.Merge (Styles.Merge method (Excel) | Microsoft Learn), it’s meant to merge cell styles from one workbook into another and has nothing to do with merging cells together. How can I accomplish this in Aspose.Cells?
@bytefyre
Please try the following codes to check whether it works as Styles.Merge in VBA
Workbook source = new Workbook(dir + "book1.xlsx");
Workbook dest = new Workbook(dir + "book2.xlsx");
int count = source.CountOfStylesInPool;
for (int i = 0; i < count; i++)
{
Style style = source.GetStyleInPool(i);
if(style.Name != null)
{
Style ds = dest.GetNamedStyle(style.Name);
if(ds == null)
{
ds = dest.CreateStyle();
}
ds.Copy(style);
ds.Name = style.Name;
}
}
dest.Save(dir + "dest.xlsx");
Aspose.Cells does not provide a direct method equivalent to VBA’s Styles.Merge method. In VBA, Styles.Merge allows you to merge custom styles from one workbook into another. While Aspose.Cells supports getting, creating and copying styles, it does not expose a bulk merge method for styles.
However, you can manually implement your requirements. Please refer to the example shared by @simon.zhao above.
Please note, when merging workbooks (having same named styles), Aspose.Cells copies the style settings of other workbook except the name of the style.
After thorough evaluation of your requirements, we have decided to provide APIs equivalent to VBA’s Styles.Merge. 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 support policies.
- Issue ID(s): CELLSNET-58559
Once we have an update on it, we will let you know here.
Thank you…I appreciate the update. I would also go on to say that, given how long Aspose.Cells has been around, it should take steps to recreate as much of Excel’s VBA API as possible and encapsulate the lower-level OpenXML operations.
Thanks for your suggestion.
Over the years, we have been doing this. We have streamlined developments for users familiar with Excel’s VBA, making the library more accessible and feature-rich for automating complex workflows and tasks. By integrating Excel’s native functionality into Aspose.Cells, we aim to significantly enhance its value proposition for developers. Our commitment remains focused in delivering APIs that align with Excel’s capabilities and advanced automation features (VBA).
As Aspose.Cells is a library, it may not support all VBA functionalities; however, we strive to implement as many features as possible. Many of our users share their desired VBA functionalities in the forums for integration into Aspose.Cells. Then, we evaluate it and deliver it. If you have specific requests for additional Excel VBA APIs or OpenXML operations, please provide detailed information, and we will evaluate their feasibility for implementation in Aspose.Cells.
@bytefyre
Please try Workbook.MergeNamedStyles() method in the last version Downloads ---New Releases-aspose.cells-for-.net-25.6
My apologies for taking so long to give feedback on this method. Unfortunately, the new method doesn’t appear to be working properly. If I merge styles from one source workbook followed by another source workbook, the shared style entries for unnamed styles in the pool are also being affected. My conclusion is the styles are being overwritten or their indexes are being shifted and the cells which use that shared style aren’t getting updated accordingly. Please advise when you might have a fix in place and if there is any workaround I could try in the meantime.
We are sorry that the new method does not work precisely for your requirements. We will evaluate it further and get back to you soon.
Sure, no problem. I’ve attached the first two workbooks that get merged in. The order of operations that causes this is:
- Merge styles from Input_STRUCTURE workbook into target workbook.
- Copy STRUCTURE named range from source workbook into target workbook at cell A1 with Range.Copy (text should be bold according to the shared style).
- Merge styles from Input_ECON_EXP_PENSION workbook into target workbook (cell A1’s shared style should have the same index but now no longer be bold).
Dev.zip (94.3 KB)
Thank you for the provided resources. We will investigate it and give feedback when we get some findings.
This is to inform you that your issue (Ticket ID: “CELLSNET-58559”) has been resolved precisely now as per your needs. The fix will be included in the upcoming release (Aspose.Cells v25.7) that we plan to release in the first half of July 2025. You will be notified when the next version is published.
The issues you have found earlier (filed as CELLSNET-58559) have been fixed in this update. This message was posted using Bugs notification tool by leoluo