How to detect merged cells with C#

[Mars]

Hi,

I do Excel programming first time, and I create a
program to detect merged cells , by this way:

1.Microsoft.Office.Interop.Excel.Application excelApp = new
Microsoft.Office.Interop.Excel.Application();

2.Get every cells in the worksheet, and check it is merged cell, or not.

※There is one worksheet in the Excel only.
but the worksheet has 60 columns and 10000 rows.
and it take long time to finish detecting a worksheet.

Is there any better way to do it?

Best,
Mars

 

[Dave Peterson]

What version of excel are you supporting?

xl2002 (it's there in xl2003 for sure!) added an option to search by format.
Before excel offered that Edit|find option, the only way I know is to loop
through the .usedrange.


Try this manually in excel.

Start a new workbook.
Merge a few cells (mark them nicely with a fill color???).
Then do Edit|find
Click on the Options button if you don't see them.
Click on the Format button
Remove everything that you don't want, but make sure "merge" is selected on the
Alignment tab.

Then you could use Find in your code to look for just merged cells.

If I remember correctly, VBA's .FindNext doesn't remember the format settings,
so you'll have to use successive .Find's.

In excel's VBA, I'd use:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myMergedCells As Range
Dim FoundCell As Range
Dim FirstAddress As String

Set wks = ActiveSheet

With wks
'clear any existing formatting that was used
Application.FindFormat.Clear

'just the merged cells
With Application.FindFormat
.MergeCells = True
End With

Set FoundCell = .Cells.Find(What:="", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=True)

If FoundCell Is Nothing Then
'not one unlocked cell!
Else
'keep track of where the first one was, so we can quit
'when we find this again.
FirstAddress = FoundCell.Address

'start building the range of unlocked cells
Set myMergedCells = FoundCell

Do
Set FoundCell = .Cells.Find(What:="", _
After:=FoundCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=True)

If FoundCell Is Nothing Then
Exit Do
End If

If FoundCell.Address = FirstAddress Then
Exit Do
End If

'add to the growing range
Set myMergedCells = Union(myMergedCells, FoundCell)

Loop

End If
End With

If myMergedCells Is Nothing Then
MsgBox "None found!"
Else
Application.Goto myMergedCells ', scroll:=True
MsgBox myMergedCells.Address
End If
End Sub

 

[Mars]

Hello Dave,

Thank you for your reply.
The version of excel is 2003, and i want to detect that if the worksheet
contains merged cell, with c#.

anyway,thank you very much.

Best Regards
Mars

 

[Dave Peterson]

I understood your question, but since I don't speak the C#, I thought that
offering you a way to do it in VBA or a way of doing it manually may have helped
you.

Sorry it didn't.