I’m using Bumblebee to read data into Rhino. My Excel files have about 500 000 rows each but I cannot get in more than ± 65 000 rows into Grasshopper at a time. In the old days, Excel had a limit of 65 000 rows but since 2007 one file can contain up to 1 048 576 rows.
Does anybody know if it’s possible (and how) to go beyond the 65K lines?
The Bumblebee gh userobject is as follows:
Option Strict Off
Option Explicit On
Imports Rhino
Imports Rhino.Geometry
Imports Rhino.DocObjects
Imports Rhino.Collections
Imports GH_IO
Imports GH_IO.Serialization
Imports Grasshopper
Imports Grasshopper.Kernel
Imports Grasshopper.Kernel.Data
Imports Grasshopper.Kernel.Types
Imports System
Imports System.IO
Imports System.Xml
Imports System.Xml.Linq
Imports System.Linq
Imports System.Data
Imports System.Drawing
Imports System.Reflection
Imports System.Collections
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports System.Runtime.InteropServices
''' <summary>
''' This class will be instantiated on demand by the Script component.
''' </summary>
Public Class Script_Instance
Inherits GH_ScriptInstance
#Region "Utility functions"
''' <summary>Print a String to the [Out] Parameter of the Script component.</summary>
''' <param name="text">String to print.</param>
Private Sub Print(ByVal text As String)
__out.Add(text)
End Sub
''' <summary>Print a formatted String to the [Out] Parameter of the Script component.</summary>
''' <param name="format">String format.</param>
''' <param name="args">Formatting parameters.</param>
Private Sub Print(ByVal format As String, ByVal ParamArray args As Object())
__out.Add(String.Format(format, args))
End Sub
''' <summary>Print useful information about an object instance to the [Out] Parameter of the Script component. </summary>
''' <param name="obj">Object instance to parse.</param>
Private Sub Reflect(ByVal obj As Object)
__out.Add(GH_ScriptComponentUtilities.ReflectType_VB(obj))
End Sub
''' <summary>Print the signatures of all the overloads of a specific method to the [Out] Parameter of the Script component. </summary>
''' <param name="obj">Object instance to parse.</param>
Private Sub Reflect(ByVal obj As Object, ByVal method_name As String)
__out.Add(GH_ScriptComponentUtilities.ReflectType_VB(obj, method_name))
End Sub
#End Region
#Region "Members"
''' <summary>Gets the current Rhino document.</summary>
Private RhinoDocument As RhinoDoc
''' <summary>Gets the Grasshopper document that owns this script.</summary>
Private GrasshopperDocument as GH_Document
''' <summary>Gets the Grasshopper script component that owns this script.</summary>
Private Component As IGH_Component
''' <summary>
''' Gets the current iteration count. The first call to RunScript() is associated with Iteration=0.
''' Any subsequent call within the same solution will increment the Iteration count.
''' </summary>
Private Iteration As Integer
#End Region
''' <summary>
''' This procedure contains the user code. Input parameters are provided as ByVal arguments,
''' Output parameter are ByRef arguments. You don't have to assign output parameters,
''' they will have default values.
''' </summary>
Private Sub RunScript(ByVal XL_Path As List(Of Object), ByVal Origin As Point3d, ByVal Bound As Point3d, ByVal ByColumn As Boolean, ByVal Type As Integer, ByVal _Stream As Boolean, ByRef A As Object, ByRef B As Object, ByRef Data As Object)
Component.Params.Input(0).Name = "Path"
Component.Params.Input(0).Description = "Optional input for the XL Path component which specifies the Target Workbook and Sheet. If blank the active workbook and worksheet is used, or the worksheet index supplied by an origin component"
Component.Params.Input(1).Name = "Data Origin"
Component.Params.Input(1).Description = "Optional point input which specifies the lower bounds from which data will be read where X = Row, Y = Column, and Z = Sheet Index. If Z = 0 the active sheet will be used. If input is empty first used cell on active sheet will be used"
Component.Params.Input(2).Name = "Data Bound"
Component.Params.Input(2).Description = "Optional point input which specifies the upper bounds from which data will be read where X = Row, Y = Column, and Z = Sheet Index. If Z = 0 the active sheet will be used, If input is empty last used cell on active sheet will be used"
Component.Params.Input(3).Name = "By Column"
Component.Params.Input(3).Description = "Sets if data will be transposed"
Component.Params.Input(4).Name = "Type"
Component.Params.Input(4).Description = "Optional input for the XL Data Type component or integer value specifying the type of data being read [Value = 0, Value2 = 1, Formula = 2, FormulaLocal = 3, FormulaR1C1 = 4, FormulaR1C1Local = 5]"
Component.Params.Input(5).Name = "Stream"
Component.Params.Input(5).Description = "If true, data will be read"
Component.Params.Output(1).Name = "Origin"
Component.Params.Output(1).Description = "List output of points which specify the lower bounds of read data"
Component.Params.Output(2).Name = "Bound"
Component.Params.Output(2).Description = "List output of points which specify the upper bounds of read data"
Component.Params.Output(3).Name = "Data"
Component.Params.Output(3).Description = "Datatree of generic data read from Excel"
If _Stream = True Then
'If _LIVE = True Then owner.ExpireSolution(True)
Dim objExcel, excelWorkbook, excelSheet As Object
If XL_Path.Count() > 1 Then
objExcel = XL_Path.item(0)
excelWorkbook = XL_Path.item(1)
excelSheet = XL_Path.item(2)
Else
Dim oldCI As System.Globalization.CultureInfo = system.Threading.Thread.CurrentThread.CurrentCulture
System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
'Access Active Session of Excel if no Path is specified
objExcel = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")
excelWorkbook = objExcel.activeWorkbook
excelSheet = objExcel.ActiveSheet
End If
objExcel.screenUpdating = False
Dim arrStr As New datatree(Of Object)
Dim i,j,k,m,n As Integer
If XL_Path.count() <= 1 Then
If CInt(origin.z) = 0 Then
n = 0
excelSheet = objExcel.ActiveSheet
ElseIf CInt(bound.z) = 0 Then
n = 0
excelSheet = excelWorkbook.Sheets.item(CInt(origin.Z))
Else
n = (bound.z - origin.z)
excelSheet = excelWorkbook.Sheets.item(CInt(origin.Z))
End If
End If
'specify a range in excel and stream data
For m = 0 To n Step 1
If m <> 0 Then
excelSheet = excelWorkbook.Sheets.item(CInt(origin.Z + n))
End If
If Origin = Nothing Then
Origin.Y = excelSheet.usedrange.row
Origin.X = excelSheet.usedrange.column
End If
If Bound = Nothing Then
Bound.Y = excelSheet.usedrange.rows(excelSheet.usedrange.rows.count).row
Bound.X = excelSheet.usedrange.columns(excelSheet.usedrange.columns.count).column
End If
Dim rng As Object
If (origin.x = bound.x) And (origin.y = bound.y) Then
arrStr.add(excelSheet.Cells(Origin.Y, Origin.X).value, New GH_Path(0))
Else
If type = 1 Then
rng = excelSheet.Range(excelSheet.Cells(Origin.Y, Origin.X), excelSheet.Cells(Bound.Y, Bound.X)).value2
ElseIf type = 2 Then
rng = excelSheet.Range(excelSheet.Cells(Origin.Y, Origin.X), excelSheet.Cells(Bound.Y, Bound.X)).formula
ElseIf type = 3 Then
rng = excelSheet.Range(excelSheet.Cells(Origin.Y, Origin.X), excelSheet.Cells(Bound.Y, Bound.X)).formulalocal
ElseIf type = 4 Then
rng = excelSheet.Range(excelSheet.Cells(Origin.Y, Origin.X), excelSheet.Cells(Bound.Y, Bound.X)).FormulaR1C1
ElseIf type = 5 Then
rng = excelSheet.Range(excelSheet.Cells(Origin.Y, Origin.X), excelSheet.Cells(Bound.Y, Bound.X)).FormulaR1C1Local
Else
rng = excelSheet.Range(excelSheet.Cells(Origin.Y, Origin.X), excelSheet.Cells(Bound.Y, Bound.X)).value
End If
If ByColumn = True Then
rng = objExcel.transpose(rng)
End If
k = 0
For i = LBound(rng, 1) To UBound(rng, 1) Step 1
For j = LBound(rng, 2) To UBound(rng, 2) Step 1
If n = 0 Then
arrStr.add(rng(i, j), New GH_Path(k))
Else
arrStr.add(rng(i, j), New GH_Path({m,k}))
End If
Next
k = k + 1
Next
End If
Next
excelSheet.activate
objExcel.screenUpdating = True
A = New point3d(Origin.x, origin.Y, excelSheet.Index)
B = New point3d(Bound.x, Bound.Y, excelSheet.Index)
Data = arrStr
End If
End Sub
'<Custom additional code>
'</Custom additional code>
Private __err As New List(Of String)
Private __out As New List(Of String)
Private doc As RhinoDoc = RhinoDoc.ActiveDoc 'Legacy field.
Private owner As Grasshopper.Kernel.IGH_ActiveObject 'Legacy field.
Private runCount As Int32 'Legacy field.
Public Overrides Sub InvokeRunScript(ByVal owner As IGH_Component, _
ByVal rhinoDocument As Object, _
ByVal iteration As Int32, _
ByVal inputs As List(Of Object), _
ByVal DA As IGH_DataAccess)
'Prepare for a new run...
'1. Reset lists
Me.__out.Clear()
Me.__err.Clear()
'Current field assignments.
Me.Component = owner
Me.Iteration = iteration
Me.GrasshopperDocument = owner.OnPingDocument()
Me.RhinoDocument = TryCast(rhinoDocument, Rhino.RhinoDoc)
'Legacy field assignments
Me.owner = Me.Component
Me.runCount = Me.Iteration
Me.doc = Me.RhinoDocument
'2. Assign input parameters
Dim XL_Path As List(Of System.Object) = Nothing
If (inputs(0) IsNot Nothing) Then
XL_Path = GH_DirtyCaster.CastToList(Of System.Object)(inputs(0))
End If
Dim Origin As Point3d = Nothing
If (inputs(1) IsNot Nothing) Then
Origin = DirectCast(inputs(1), Point3d)
End If
Dim Bound As Point3d = Nothing
If (inputs(2) IsNot Nothing) Then
Bound = DirectCast(inputs(2), Point3d)
End If
Dim ByColumn As Boolean = Nothing
If (inputs(3) IsNot Nothing) Then
ByColumn = DirectCast(inputs(3), Boolean)
End If
Dim Type As Integer = Nothing
If (inputs(4) IsNot Nothing) Then
Type = DirectCast(inputs(4), Integer)
End If
Dim _Stream As Boolean = Nothing
If (inputs(5) IsNot Nothing) Then
_Stream = DirectCast(inputs(5), Boolean)
End If
'3. Declare output parameters
Dim A As System.Object = Nothing
Dim B As System.Object = Nothing
Dim Data As System.Object = Nothing
'4. Invoke RunScript
Call RunScript(XL_Path, Origin, Bound, ByColumn, Type, _Stream, A, B, Data)
Try
'5. Assign output parameters to component...
If (A IsNot Nothing) Then
If (GH_Format.TreatAsCollection(A)) Then
Dim __enum_A As IEnumerable = DirectCast(A, IEnumerable)
DA.SetDataList(1, __enum_A)
Else
If (TypeOf A Is Grasshopper.Kernel.Data.IGH_DataTree) Then
'merge tree
DA.SetDataTree(1, DirectCast(A, Grasshopper.Kernel.Data.IGH_DataTree))
Else
'assign direct
DA.SetData(1, A)
End If
End If
Else
DA.SetData(1, Nothing)
End If
If (B IsNot Nothing) Then
If (GH_Format.TreatAsCollection(B)) Then
Dim __enum_B As IEnumerable = DirectCast(B, IEnumerable)
DA.SetDataList(2, __enum_B)
Else
If (TypeOf B Is Grasshopper.Kernel.Data.IGH_DataTree) Then
'merge tree
DA.SetDataTree(2, DirectCast(B, Grasshopper.Kernel.Data.IGH_DataTree))
Else
'assign direct
DA.SetData(2, B)
End If
End If
Else
DA.SetData(2, Nothing)
End If
If (Data IsNot Nothing) Then
If (GH_Format.TreatAsCollection(Data)) Then
Dim __enum_Data As IEnumerable = DirectCast(Data, IEnumerable)
DA.SetDataList(3, __enum_Data)
Else
If (TypeOf Data Is Grasshopper.Kernel.Data.IGH_DataTree) Then
'merge tree
DA.SetDataTree(3, DirectCast(Data, Grasshopper.Kernel.Data.IGH_DataTree))
Else
'assign direct
DA.SetData(3, Data)
End If
End If
Else
DA.SetData(3, Nothing)
End If
Catch ex As Exception
__err.Add(String.Format("Script exception: {0}", ex.Message))
Finally
'Add errors and messages...
If (owner.Params.Output.Count > 0) Then
If (TypeOf owner.Params.Output(0) Is Grasshopper.Kernel.Parameters.Param_String) Then
Dim __errors_plus_messages As New List(Of String)
If (Me.__err IsNot Nothing) Then __errors_plus_messages.AddRange(Me.__err)
If (Me.__out IsNot Nothing) Then __errors_plus_messages.AddRange(Me.__out)
If (__errors_plus_messages.Count > 0) Then
DA.SetDataList(0, __errors_plus_messages)
End If
End If
End If
End Try
End Sub
End Class
.