Merging rows into single using spreadsheet gear while exporting to Excel

Question

I have to merge the rows of excel using spreadsheet gear controls is it possible. Only specific rows of single column

All detail is being included in this screencast

The changes that has been done by me is

            DataTable dt = (DataTable)ViewState["dtGrid"]

            System.Random rd = new System.Random(DateTime.Now.Millisecond);
            int MyValue = rd.Next(1000000, 99999999);
            sUniqueName = MyValue.ToString();

            // Create a new workbook.
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
            SpreadsheetGear.IRange cells = workbook.Worksheets[0].Cells;

            cells.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.None);


            cells.Rows[0, 0, 0, 51].Interior.Color = System.Drawing.Color.Navy;
            cells.Rows[0, 0, 0, 51].Font.Color = System.Drawing.Color.White;
            cells["A:R"].Columns.AutoFit();

            string filename = string.Format("{0}-{1}-{2}", "AOMIndoorInventoryReport", DateTime.Now.ToString("MM-dd-yy"), sUniqueName);
            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");
            workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.Excel8);
            Response.End();

What should be added?

Answer 1

You can merge cells by calling IRange.Merge() on the desired cells. Example:

cells["A7:A8"].Merge();
cells[8, 0, 22, 0].Merge();

---

UPDATE: You've asked how to dynamically merge a range of cells based on adjacent rows in a column which have the same value. To accomplish this would require looping through each row in this column and comparing each cell's value with the previous value, merging when appropriate as you go down the column.

I am providing some sample code below which demonstrates one way in which you might go about this (there are certainly many other ways). I had to make some assumptions about your underlying data and requirements, so some modification on your end might be needed. Note the use of some handy methods under the IRange interface (see the IRange.Intersect(...) / Subtract(...) / Union(...) methods) which allow you to "interact" two IRanges to create a new third IRange.

...

// Create a new workbook and some local variables for convenience.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[0];
SpreadsheetGear.IRange cells = worksheet.Cells;

// Copy data from DataTable to worksheet
cells.CopyFromDataTable(dt, SpreadsheetGear.Data.SetDataFlags.None);

// Here I am creating an IRange representing the used part of column a and without
// the header row, which I presume is in Row 1, and should not be included in this
// merging routine.
SpreadsheetGear.IRange usedColA = worksheet.UsedRange.Intersect(
    cells["A:A"]).Subtract(cells["1:1"]);

// No point in attempting to merge cells if there's only a single row.
if (usedColA.RowCount > 1)
{
    // Some variables to keep track of the content of the "current" and "previous"
    // cells as we loop through "usedColA".
    string prevCellVal = "";
    string curCellVal = "";

    // We'll use this variable to keep track of ranges which will need to be merged
    // during the routine.  Here I seed "mergeRange" with the first cell in usedColA.
    SpreadsheetGear.IRange mergeRange = usedColA[0, 0];

    // Loop through each cell in the used part of Column A.
    foreach (SpreadsheetGear.IRange cell in usedColA)
    {
        // Get text of current "cell".
        curCellVal = cell.Text;

        // Your screenshot seems to indicate that you don't care about merging empty
        // cells so this routine takes this into account.  Either an empty cell or
        // mismatched text from the "current" and "previous" cell indicate we should
        // merge whatever cells we've accumulated in "mergeRange" and then reset this
        // range to look for more ranges to merge further down the column.
        if (curCellVal.Length == 0 || curCellVal != prevCellVal)
        {
            // Only merge if this range consists of more than 1 cell.
            if (mergeRange.CellCount > 1)
                mergeRange.Merge();

            // Reset merged range to the "current" cell.
            mergeRange = cell;
            prevCellVal = curCellVal;
        }
        // If the current and previous cells contain the same text, add this "cell"
        // to the "mergeRange".  Note the use of IRange.Union(...) to combine two 
        // IRange objects into one.
        else if (curCellVal == prevCellVal)
            mergeRange = mergeRange.Union(cell);
    }

    // One last check for any cells to merge at the very end of the column.
    if (mergeRange.CellCount > 1)
        mergeRange.Merge();
}
...