Am trying to group together rows in a sheet by merging the common column.
So am iterating through the sheet and comparing values with the previous row and merging them if they are same.
But I get an error when I try to merge a row with an already merged row. For ex. if row 2 and row 3 are merged, the next time we iterate it throws an error if row 3 is being merged with row 4.
Is there any way around this?
sample code:
For counter = 1 To sheet.Cells.Rows.Count - 1
Dim value As Integer = sheet.Cells(counter, 0).IntValue
Dim rowCount As Integer = 0
If Not counter = 1 Then
If sheet.Cells(counter - 1, 0).IntValue = value Then
sheet.Cells.Merge(counter - 1, 9, 2, 1)
End If
End If
Next
Thanks!
This message was posted using Page2Forum from Merging / UnMerging Cells in the Worksheet - Aspose.Cells for .NET.
Please see this code, which I have written to illustrate, how
to merge your row with previous row which has already been merged. To
do this, we will calculate CellArea .
Once,
you will run this code, it will create an xls file, in which you will
see, all the consecutive cells which are found to be true have been
merged as single cell. Please see the outMerged.xls attached with this post. VB.NET
Imports Aspose.Cells
Imports Aspose.Cells.Charts
Imports System.Drawing
Namespace AsposeCellsVbPractice
Public Class Practice
Sub MergingCells()
'Create a workbook
Dim m_workbook = New Workbook
'Access the worksheets
Dim m_worksheet As Worksheet = m_workbook.Worksheets(0)
'cells we want to merge in first column
'all consecutive cells which are true will be merged
'Dim cellsToBeMerged() = { 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 }
Dim cellsToBeMerged() = {False, True, True, True, False, True, True, False, False, True, True, True, True, False, False, False, True, True}
'Column is fixed which is 0
Dim fixedCol As Integer = 0
'Number of rows to process
Dim numOfRowsToProcess As Integer = 18
'Iterate all the rows
For currentRow As Integer = 1 To numOfRowsToProcess - 1
'Check if current row and previous row is to be merged
If cellsToBeMerged(currentRow) = True And cellsToBeMerged(currentRow - 1) = True Then
'get previous row’s cell
Dim prevCell As Cell = m_worksheet.Cells(currentRow - 1, fixedCol)
'if previous row has not been merged before, we will just merge
'it with current row
If prevCell.IsMerged = False Then
m_worksheet.Cells.Merge(currentRow - 1, fixedCol, 2, 1)
Else
'if previous cell has been merged before, we will now calculate merge area
Dim isFound As Boolean = False
Dim area As CellArea = GetCellArea(m_worksheet.Cells, prevCell)
Dim numRows As Integer = currentRow - area.StartRow + 1
Dim numCols As Integer = fixedCol - area.StartColumn + 1
m_worksheet.Cells.Merge(area.StartRow, area.StartColumn, numRows, numCols)
End If
End If
Next
'save the workbook
m_workbook.Save(“f:\downloads\outMerged.xls”)
End Sub
Function GetCellArea(ByVal m_cells As Cells, ByVal cl As Cell) As CellArea
'empty cell area
Dim area As CellArea = New CellArea
For Each obj As CellArea In m_cells.MergedCells
area = obj
If area.StartRow <= cl.Row And cl.Row <= area.EndRow And _
area.StartColumn <= cl.Column And cl.Column <= area.EndColumn Then
Return area
End If
Next
Return area
End Function
End Class
End Namespace