The core requirement revolves around a highly structured and user-friendly data presentation format, specifically the widely adopted “master-and-details” layout. This architectural pattern is favored by many customers for its power in organizing complex datasets, offering both a high-level overview and the ability to drill down into granular information.
At its heart, this structure involves a master sheet and multiple detail worksheets.
- The master sheet serves as the central hub, providing a consolidated, summary view of the data. It acts as a dashboard or an index, presenting key aggregated metrics, a list of items, or a high-level overview without getting bogged down in specifics.
- Conversely, each detail worksheet is dedicated to a specific record or entity, presenting comprehensive, granular information related to that particular item. These sheets are designed to display all relevant attributes, histories, or sub-components once a user selects an item from the master view.
Common examples of data types frequently organized in this master-and-details structure include:
- Products:
- Master: A summary list showing Product ID, Name, current stock levels, overall sales figures, or product category.
- Details (per product): Individual worksheets for each product containing full descriptions, SKUs, pricing, supplier information, detailed sales history, inventory movement logs, and specifications.
- Customers:
- Master: An overview list of customers, perhaps with Customer ID, Name, total lifetime value, last order date, or geographical region.
- Details (per customer): Dedicated sheets for each customer providing full contact information, billing and shipping addresses, complete order history, communication logs, and payment terms.
- Orders:
- Master: A list of all orders, displaying Order ID, Date, Customer Name, and Total Amount.
- Details (per order): Individual worksheets for each order detailing the specific line items (products, quantities, unit prices), shipping status, payment information, and delivery tracking.
- Audit Findings:
- Master: A summary of all audit findings, listing Finding ID, Severity, Status, and Responsible Department.
- Details (per finding): Specific sheets for each finding containing a detailed description of the issue, supporting evidence, proposed corrective actions, follow-up dates, and resolution status.
To illustrate this with a concrete example, imagine a customer building a report for their inventory.
The master worksheet would typically contain a summary of all products, perhaps listing their Product IDs, Names, current stock levels, and total sales figures. From this master view, a user could then select a specific Product ID.
Upon selection, a dedicated detail worksheet, meticulously named after the product’s unique ID or its full name (e.g., ‘PROD_PC_GAMING_001’ or ‘Gaming PC Pro X200’), would display all specific details related to that single product. This could include its detailed specifications, historical sales data, supplier information, inventory movement logs, and customer reviews.
This design philosophy ensures that users can quickly grasp the overall picture and then seamlessly “drill down” into the precise data they require for in-depth analysis or operational tasks.
@sagaofsilence.dev,
Thank you for sharing your detailed scenario and highlighting the significance of utilizing the master-and-details layout with its structured approach to handle complex datasets effectively. We will carefully evaluate your request for the master-details report generation feature and assess its feasibility (we may log appropriate ticket(s)). Once we have completed our review, we will get back to you.
@sagaofsilence.dev
It’s better that you can share an actual report file to help us learn your request.
1, I guess that you may need to add a lot of hyperlinks to the main table, then Users can simply jump to detail
2, Please check the limitation of Excel: Excel specifications and limits - Microsoft Support
There will be too many worksheets if one worksheet only contains one order description.
I will provide the revised templates and reports soon.
@sagaofsilence.dev
Thank you for your cooperation!
Enhancing Master-Detail Worksheet Modeling in Aspose Cells: Further Considerations and Ideas
Thank you for reviewing the initial proposal on modeling master-detail worksheets. While two primary options have been put forth, it’s crucial to emphasize that these are by no means exhaustive. The intention is to foster an open discussion, and we strongly encourage you to critically revise the proposed options or, ideally, suggest entirely new and potentially superior approaches. Flexibility and configurability are key objectives for this functionality.
To stimulate further thought and discussion, here are several key ideas and principles to consider regarding the implementation details and extensibility of this master-detail solution within the Aspose Cells engine:
- Optional Master Worksheet: A fundamental aspect of the proposed flexibility is that the master worksheet itself can be entirely optional. This design choice acknowledges that not all reporting scenarios necessitate a summary or aggregate sheet. Regardless of whether a master worksheet is present, the detail worksheets will always be generated and output, as they contain the granular data and specific views that are fundamental to the reporting requirements. This ensures that the core data always manifests, even if its summarized entry point is omitted.
- Configurable Linking to Detail Worksheets: Furthermore, the linking mechanism from the master worksheet to its corresponding detail worksheets should also be configurable and optional. This typically refers to the creation of hyperlinks within the master sheet that navigate directly to specific detail sheets. While often highly beneficial for user navigation and data exploration, there may be use cases where such direct linking is not required, or where navigation is facilitated through alternative means. Providing this optionality would likely be controlled via a dedicated parameter in the smart marker used in the master worksheet.
- Standardized Naming Prefix for Templates: To ensure clear identification, prevent naming conflicts, and facilitate easier management of template assets, it is strongly suggested that both parameter names and worksheet names used in the templates adopt a consistent prefix. The proposed default prefix is
DEMO_MULTI_. This prefix clearly identifies elements that are part of the multi-sheet master-detail structure. Importantly, this default prefix is not hardcoded; it can be easily overridden through specific configuration options provided by the Aspose Cells engine, allowing for custom branding or seamless integration into existing naming conventions of various projects.
- Meaningful Worksheet Naming Convention: Building upon the common prefix, a consistent naming convention for master and detail worksheets is proposed to facilitate immediate identification and correlation between related sheets. The convention would typically involve the common prefix, followed by an indicator for “MASTER” or “DETAIL,” and then a unique identifier often derived from the data itself. For example, if a specific dataset or report section is named ‘STYLES1’, its master worksheet might be named
DEMO_MULTI_MASTER_STYLES1 and its corresponding detail worksheet DEMO_MULTI_DETAIL_STYLES1. This pattern instantly conveys that DEMO_MULTI_MASTER_STYLES1 serves as the summary or entry point for the ‘STYLES1’ data, with DEMO_MULTI_DETAIL_STYLES1 providing the granular breakdown. This systematic naming significantly improves template readability and maintainability.
- Dynamic Detail Worksheet Naming via Data Property: To enable dynamic and meaningful naming of individual detail worksheets based on the underlying data, a specific parameter, tentatively named
DEMO_MULTI_PARAM_WSNAME, is suggested. This parameter would be associated with one or more data properties whose values will be used to construct the detail worksheet name. For example, if a data property ProductName is tagged with DEMO_MULTI_PARAM_WSNAME, each generated detail sheet could be dynamically named after the product it represents. The name DEMO_MULTI_PARAM_WSNAME itself is the default for this parameter and, like other prefixes, can be custom-defined via Aspose Cells configuration options.
- Concatenation for Complex Names: In scenarios where a single data property is insufficient to uniquely identify a detail worksheet, multiple properties can be tagged with
DEMO_MULTI_PARAM_WSNAME. In such cases, the final worksheet name would be formed by concatenating the values of these tagged properties. To ensure readability and structure in concatenated names, a separator character would be used between the property values. The default separator character is proposed as a hyphen (-), but this, too, would be fully configurable through the Aspose Cells engine options, allowing for diverse naming conventions (e.g., _, ., or space).
- Prefix for Named Ranges (Option Two): Furthermore, specifically within the context of “Option Two” (which explicitly leverages named ranges for data definition and processing within the template), it is proposed that these range names also adhere to the consistent
DEMO_MULTI_ prefix. For instance, the named range containing the master data might be named DEMO_MULTI_MASTER_STYLES1, and the range defining the detail section within a template could be DEMO_MULTI_DETAIL_STYLES1. This consistency across worksheet names, parameter names, and named ranges enhances overall template readability, simplifies programmatic access, and streamlines template management. As with all other suggested prefixes, this is a default value that can be fully overridden via the Aspose Cells engine’s configuration settings, ensuring maximum adaptability to various project requirements.
For practical reference and a clearer understanding of the concepts discussed, please refer to the attached zip file Multi_Worksheets_2.zip (77.9 KB). It contains example templates, illustrations of the desired outcomes/reports, and the corresponding JSON payloads used to generate them.
We look forward to your valuable insights and questions as we refine this critical functionality.
@sagaofsilence.dev,
Thank you for sharing the revised templates, reports, and your suggestions, ideas, and principles. We appreciate your effort and will carefully check/review everything before reaching back to you.
@sagaofsilence.dev
ShirtStyleTemplate.zip (14.9 KB)
I think we can add the parameter of smart markers to link master and detail. Please check above template file.
Another question: if Style1,Style2,Style3 and so on… are variable, it’s hard to know how many template master sheet should be added.
I liked the proposed syntax for creating and linking detail sheets in a master worksheet using JSON payload. The syntax allows for flexibility and customization based on the data in the JSON payload.
&=node.Styles1.StyleID(DetailSheet:DETAIL_STYLES,DetailTable:Style,DetailSheetNewName:{node.Styles1.StyleID},DetailLink)
This syntax is added to the master worksheet and used to create (and optionally link) to one or more detail sheets. This syntax ensures that the detail worksheet will be named after the StyleID property value of the item object in the node.Styles1 array.
For example, if the node.Styles1 array contains 5 items, then the syntax will create 5 detail worksheets, with each worksheet named after the StyleID property value of the respective item object. This allows for easy organization and management of data in the master worksheet and its corresponding detail sheets.
Furthermore, as suggested earlier, the master worksheet is optional. This means that the detail worksheet can be defined and populated using the data in the JSON payload, without the need for a master worksheet. In this case, the DetailTable and DetailSheetNewName can be provided to specify the table and worksheet name, respectively, in the detail worksheet. This allows for greater flexibility in creating and managing worksheets based on the data in the JSON payload.
@sagaofsilence.dev
If the master worksheet does not exist, I can not find a simple way to place those detail smart markers.
DetailTable refers to the worksheet itself?
The detail worksheet is configured to contain all necessary parameters. Consequently, when the master worksheet is excluded from the output, the smart marker definition shifts from the master to the detail worksheet.
The full smart marker previously used in the master worksheet, &=node.Styles1.StyleID(DetailSheet:DETAIL_STYLES,DetailTable:Style,DetailSheetNewName:{node.Styles1.StyleID},DetailLink) , is replaced by a more streamlined version directly within the detail worksheet: &=node.Styles1.StyleID(DetailTable:Style,DetailSheetNewName:{node.Styles1.StyleID}) .
This simplification occurs because the DetailSheet and DetailLink parameters are specifically for managing connections between master and detail worksheets, thus becoming redundant when only the detail worksheet is generated.
This functionality supports the creation of templates designed to produce detail-only worksheet outputs.
@sagaofsilence.dev
if the current sheet is detail sheet, we can set smart markers as :
&=node.Styles1.StyleID(DetailSheet,DetailSheetNewName:{node.Styles1.StyleID})
Simple DetailSheet smart parameter means this sheet is a detail template sheet.
Is it fine?
Yes. As long as it helps create the detail worksheets with details.
@sagaofsilence.dev,
Thanks for the confirmation on detailsheet marker. We will review and look into it soon.
1 Like
Similar to the worksheet naming example you shared above ShirtStyleTemplate.xlsx:
&=node.Styles3.StyleID(DetailSheet:DETAIL_STYLES,DetailTable:Style,DetailSheetNewName:{node.Styles3.StyleID}&"_"&{node.Styles3.StyleName},DetailLink)
When we have only a detail worksheet, it should be possible to concatenate more than one property name:
&=node.Styles3.StyleID(DetailSheet,DetailSheetNewName:{node.Styles3.StyleID}&"_"&{node.Styles3.StyleName})
@sagaofsilence.dev
We will evaluate this requirement too.
@sagaofsilence.dev,
Moreover, having gathered most of your requirements so far, we decided to create the following new ticket(s) in our internal issue tracking system. The fix/enhancement will be delivered in accordance with the terms outlined in support policies.
Issue ID(s): CELLSJAVA-46477 - Support Master-Detail smart markers
@sagaofsilence.dev
Please check the attached files：
Detail_Master.zip (62.2 KB)
Are they your excepted result?
We generated result with the following codes:
var wbk1 = new Workbook(dir + "template.xlsx");
WorkbookDesigner d = new WorkbookDesigner();
d.Workbook = wbk1;
d.SetJsonDataSource(null, File.ReadAllText(dir + "ShirtsStyleData.json"));
d.Process();
wbk1.Save(dir + "dest.xlsx");
@sagaofsilence.dev,
This is to notify that your issue (Ticket ID: “CELLSJAVA-46477”) has been resolved. The feature/enhancement will be included in the next release (Aspose.Cells v25.9), which is scheduled for the next week of September 2025. We will inform you once the new version is available.
The issues you have found earlier (filed as CELLSJAVA-46477) have been fixed in Aspose.Cells for Java 25.9.