How can one test against merged cells in VB.NET?
I'm writing an app that retrieves data out of an excel sheet and feeds it
into an array.
I have it working with the following code, to the state where it works as
long as it doesn't come across a merged cell in it's range.
Code:
Try
'create app session/objects
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim dataRange As Excel.Range
'connect to file
xlApp = CType(CreateObject("Excel.Application"),
Excel.Application)
xlApp.Workbooks.Open("c:\temp\product.xls")
xlBook = xlApp.Workbooks(1)
xlSheet = xlBook.Worksheets(1)
'set the cell range and assign it to the array
If txtRangeStart.Text = "" Or txtRangeEnd.Text = "" Then
MessageBox.Show("You have to enter a group of cells to
import!", "Information missing!", MessageBoxButtons.OK)
Exit Sub
Else
excelRange = txtRangeStart.Text + ":" + txtRangeEnd.Text
End If
dataRange = xlSheet.Range(excelRange)
Dim dataArray(,) As Object
dataArray = dataRange.Value
'get the array specs
Dim dataArrayRow As Short
Dim dataArrayCol As Short
dataArrayRow = dataArray.GetUpperBound(0)
dataArrayCol = dataArray.GetUpperBound(1)
'create variables for expression loops
Dim textString As String = "Values a" + vbCrLf
Dim rowCounter As Short
Dim colCounter As Short
For rowCounter = 1 To dataArrayRow
For colCounter = 1 To dataArrayCol
textString = String.Concat(textString,
dataArray(rowCounter, colCounter).ToString() + " ")
Next
textString = textString + vbCrLf
Next
txtResults.Text = textString
xlApp.Quit()
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
How can I detect and handle merged cells when retrieving data from an excel
workbook? TIA!
|
