I am using a foreach in my Excel file and wanted to know if some of the tags used in Word can also work in Excel.
I understand the cellMerge -horz tag in Word and what this aims to accomplish, this works how I would expect when applying to a Word template with a table.
if I use just the -horz tag in the same foreach in Excel it returns an inline error as the ‘-’ is unexpected. I am aiming to try and get a foreach to run horizontally in Excel to create headers.
This is the foreach I am using (which works in Word)
<<foreach[d in data] -horz>> Lyft <<[d.MilestoneNumber]>>
<<[d.Month]>><>
Thank you!
@Kayleigh
Summary
The -horz modifier that can be used with foreach in Aspose.Words mail‑merge is not supported by Aspose.Cells. When the tag <<foreach[d in data] -horz>> appears in an Excel template, the Cells mail‑merge parser treats the hyphen as an unexpected token and raises an error.
Why it happens
Aspose.Words and Aspose.Cells implement different smart‑markup syntaxes. The -horz option is documented only for Word tables (to repeat a row horizontally). Aspose.Cells’ mail‑merge engine recognises a limited set of tags (e.g., <<#for each>>, <<TableStart:Name>>, <<TableEnd:Name>>) and does not parse any hyphen‑prefixed modifiers after foreach. Consequently the loop can iterate only vertically (down rows) by default.
What to do next
- Remove the
-horz flag and verify that the basic foreach works in vertical mode.
// Excel template example
// <<foreach[d in data]>>
// <<[d.MilestoneNumber]>>
// <<[d.Month]>>
// <</foreach>>
- Create the horizontal layout yourself after the merge. Two practical approaches are:
a. Transpose the populated range
// Assume the mail‑merge has already been executed
Workbook wb = new Workbook("Template.xlsx");
Worksheet ws = wb.Worksheets[0];
// Data were written vertically starting at A2
int rowCount = data.Count; // number of items merged
Range source = ws.Cells.CreateRange($"A2:A{rowCount + 1}");
Range dest = ws.Cells.CreateRange($"B2:{CellsHelper.ColumnIndexToName(rowCount)}2");
source.Copy(dest); // copy the vertical values
dest.Transpose(); // turn them into a horizontal row
wb.Save("Result.xlsx");
b. Write values column‑by‑column in a custom loop
Workbook wb = new Workbook("Template.xlsx");
Worksheet ws = wb.Worksheets[0];
int startRow = 1; // zero‑based index for the first header row
int startCol = 1; // zero‑based index for the first header column
for (int i = 0; i < data.Count; i++)
{
ws.Cells[startRow, startCol + i].PutValue(data[i].MilestoneNumber);
ws.Cells[startRow + 1, startCol + i].PutValue(data[i].Month);
}
wb.Save("Result.xlsx");
Both methods produce a horizontal series of values without relying on a -horz tag.
-
Check the supported mail‑merge syntax in the Aspose.Cells documentation (search for “Mail‑Merge with Excel” or “Smart Markup”). The documentation lists the tags that Cells recognises. At present there is no built‑in horizontal iteration modifier.
-
If a single‑tag horizontal iteration is required, you would need to wait for a future release that adds such a feature, or submit a feature request through Aspose’s public feedback channels. Until then, the manual transpose or column‑by‑column approaches are the recommended work‑arounds.
Note: The retrieved evidence does not contain information about the -horz tag in Excel templates, so the answer is based on the documented capabilities of Aspose.Cells and the known differences between Aspose.Words and Aspose.Cells smart‑markup syntaxes.
I could not fully verify this explanation against the retrieved sources. Please double-check the behaviour in your environment and confirm the exact SDK/version.
@Kayleigh,
I am sorry but there’s no built-in -horz option or direct “horizontal foreach” equivalent concept or relevant LINQ Reporting Engine in Aspose.Cells. However, Aspose.Cells has Smart Markers template engine, which is different and does not support Word’s LINQ-style syntax or the -horz modifiers. But I think you might use parameters while using Smart Markers, e.g., horizontal - This parameter will make sure to write data left-to-right, instead of top-to-bottom.
Alternativley, tou can manually use some loop to populate cells horizontally (in different columns) with Cell.putValue() method in your Java code using Aspose.Cells API:
e.g.,
Workbook workbook = new Workbook("template.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
String[] milestones = new String[] {"M1", "M2", "M3"};
int col = 1; // Start at column B
for (String milestone : milestones) {
sheet.getCells().get(0, col).putValue(milestone);
col++;
}
workbook.save("output.xlsx");