|
'Instantiate the spreadsheet creation engine.
Dim excelEngine As New ExcelEngine()
'Instantiate the excel application object.
Dim application As IApplication = excelEngine.Excel
'The workbook is opened.
Dim workbook As IWorkbook = application.Workbooks.Open("opendata_donations1,4m.xlsx")
'The first worksheet object in the worksheets collection is accessed.
Dim worksheet As IWorksheet = workbook.Worksheets(0)
worksheet.EnableSheetCalculations()
Dim lastRow As Integer = worksheet.UsedRange.LastRow
Dim lastColumn As Integer = worksheet.UsedRange.LastColumn
Dim cell As IMigrantRange = worksheet.MigrantRange
Dim sheet As WorksheetImpl = TryCast(worksheet, WorksheetImpl)
For row As Integer = 1 To lastRow
For column As Integer = 1 To lastColumn
Dim valType As TRangeValueType = sheet.GetCellType(row, column, False)
Dim cellValue As Object
Select Case valType
Case TRangeValueType.Blank
cellValue = String.Empty
Exit Select
Case TRangeValueType.[String]
cellValue = sheet.GetText(row, column)
Exit Select
Case TRangeValueType.Number
cellValue = sheet.GetNumber(row, column)
Exit Select
Case TRangeValueType.[Boolean]
cellValue = sheet.GetBoolean(row, column).ToString().ToUpper()
Exit Select
Case TRangeValueType.Formula
Dim cellRef As String = RangeInfo.GetAlphaLabel(Column) + Row.ToString()
sheet.CalcEngine.PullUpdatedValue(cellRef)
Dim rangeType As TRangeValueType = sheet.GetCellType(row, column, True)
Dim code As Integer = CInt(rangeType)
Select Case code
Case 9
'TRangeValueType.Error
cellValue = sheet.GetFormulaErrorValue(row, column)
Exit Select
Case 10
'TRangeValueType.Boolean
cellValue = sheet.GetFormulaBoolValue(row, column).ToString().ToUpper()
Exit Select
Case 12
'TRangeValueType.Number
cellValue = sheet.GetFormulaNumberValue(row, column)
Exit Select
Case 24
'TRangeValueType.String
cellValue = sheet.GetFormulaStringValue(row, column)
Exit Select
Case Else
' returns for blank record
cellValue = String.Empty
Exit Select
End Select
Exit Select
Case Else
cell.ResetRowColumn(row, column)
cellValue = cell.Value2
Exit Select
End Select
Next
Next
| 
