Hello,

I have a worksheet that has three cells merged together into a single cell, and I've placed a SmartMarker into the cell.

The data that gets merged into the merge field has multiple rows.  The (mail) merge process works except that on the successive rows, the cells are no longer merged together.  My application is not specific to this template, so using Cells to find and merge together the cells programmatically isn't really feasible.

Any suggestions?  It would be great if Cells would handle this situation automatically.

I'll attach a copy of the template, the output and the simple code I'm using:

    protected void Button1_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable("MyData");
        dt.Columns.Add(new DataColumn("MyField"));
        DataRow dr = dt.NewRow();
        dr["MyField"] = "Alpha";
        dt.Rows.Add(dr);
        dr = dt.NewRow();
        dr["MyField"] = "Beta";
        dt.Rows.Add(dr);
        dr = dt.NewRow();
        dr["MyField"] = "Gamma";
        dt.Rows.Add(dr);

        WorkbookDesigner designer = new WorkbookDesigner();
        designer.Workbook.Open(@"C:\MergedCellsExample.xls");
        designer.SetDataSource(dt);
        designer.Process(false);
        designer.Workbook.Save(@"C:\Output.xls", FileFormatType.Default);
    }

Hi,

When the smart markers are processed,  the blank rows are inserted and then filled with data against smart markers based on your datasource. Currently we don't support to make the cells merged if the starting cell is a merged one. We will look into it if we can support it soon.

I think you can do the merging cells process by yourself after processing the smart markers, e.g..,

designer.Process(false);
for(int i = 4;i<=designer.Workbook.Worksheets[0].Cells.MaxDataRow;i++)
 {
    designer.Workbook.Worksheets[0].Cells.Merge(i,1,1,4);           
 }
designer.Workbook.Save(@"C:\Output.xls", FileFormatType.Default);

 

Thank you.

---

Amjad Sahi
Support Developer,
Aspose Nanjing Team
Contact Us

Hello Amjad,

Any idea whether this idea of "retaining" cells that have been merged together, after populating them with data from Smart Markers?

Your suggestion of manually merging together the cells after the data has been populated would be fine except that my users provide templates that I've not seen before, so I can't really develop functionality specific to a particular template.

It would be a really excellent update to Cells to be able to merge data into a merged cell, and retain the "merged cell" status in the new rows that are added.

Thanks for considering it.

Mike

Hi Mike,

Well, I think you may create your own code to get the merged cells using Cells.MergedCells API and can merge all the data cells in a column after processing the smart markers.

May the following sample code help you for your need, I think you may consult it and try to create your own for your template files.

e.g..,

Sample code:

designer.Process(false);

           Cells cells = designer.Workbook.Worksheets[0].Cells;
            ArrayList al = new ArrayList();
            al = cells.MergedCells;
            CellArea ca;
            int frow, fcol, erow, ecol, trows, tcols;
           
            for (int i = 0; i < al.Count; i++)
            {
                ca = new CellArea();
                ca = (CellArea)al[i];
                frow = ca.StartRow;
                fcol = ca.StartColumn;
                erow = ca.EndRow;
                ecol = ca.EndColumn;

                    trows = erow - frow + 1;
                    tcols = ecol - fcol + 1;
                    for (int j = frow; j <= cells.MaxDataRowInColumn(fcol); j++)
                    {

                        cells.Merge(j, fcol, trows, tcols);
                    }
               
            }

            designer.Workbook.Save(@"f:\test\new_Output_MergedCellsExample5.xls", FileFormatType.Default);

 

And we will check if we can consider this feature (automatically merging cells after processing the smart markers) after completing some of the important tasks on hand.

Thanks for your time and understanding.

 

---

Amjad Sahi
Support Developer,
Aspose Nanjing Team
Contact Us

Thank you very much, Amjad.  I really appreciate the excellent service I always get from my friends at Aspose!

I've tried the code, and I think the Excel template I'm working on is quite complicated and the code doesn't work well.  Just to give you an idea of the file, I'll attach it here.  The cells in question are F134-I134.  That "merged cell" has a single Smart Marker, which could represent multiple rows of data.

The data for this is too complex to try to represent here, but I'll attach the output file after using this code, just to give you an idea of what I'm seeing.

I think the biggest challenge with this code is that I only want the cells that previously had Smart Markers (that have been populated) so that any new rows added to the worksheet have the same merged cells.

The way this looks now, it evaluates all possible merged cells in the workbook, and merges together cells from the following rows.

You're probably on the right track -- thanks for any further thoughts.

Mike

Hi Mike,

OK, we will start working on the feature ( i.e.., automatically merge data cells accordingly after processing the smart markers if the markers are placed in the merged cells) and hopefully we can complete it soon (it might take 5-10 days to complete the task if there is no other complication involved). We will let you know when it is completed.

Thank you.

---

Amjad Sahi
Support Developer,
Aspose Nanjing Team
Contact Us

That's great news, Amjad.  Thanks again... I'll look forward to hearing more soon.

Mike

Hi Mike,

Please try this fix.

We have supported merged cells when the cell(which contains a smart marker) is merged.

---

Simon Zhao
Developer
Aspose Nanjing Team
Contact Us

Hi Simon,

This works GREAT!  You guys are such a delight to work with, and I think this will be an excellent feature for many other users.

Many thanks!
Mike