<process name="__selection__MS Excel VBO" type="object">
  <subsheet subsheetid="5bd911d6-6eee-4cfb-bb39-693a93c05faa" type="Normal" published="False">
    <name>Write Collection Direct</name>
    <view>
      <camerax>0</camerax>
      <cameray>-63</cameray>
      <zoom version="2">1.25</zoom>
    </view>
  </subsheet>
  <stage stageid="391ffed5-b89e-401b-8105-d35025df96f9" name="Write Collection Direct" type="SubSheetInfo">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <narrative>Writes the given collection into the specified sheet.</narrative>
    <displayx>-195</displayx>
    <displayy>-105</displayy>
    <displaywidth>180</displaywidth>
    <displayheight>90</displayheight>
    <font family="Tahoma" size="10" style="Regular" color="000000" />
  </stage>
  <stage stageid="6cf4569a-b7b2-4919-af69-7bb5e9b63a6c" name="Start" type="Start">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <loginhibit />
    <narrative></narrative>
    <displayx>45</displayx>
    <displayy>-135</displayy>
    <displaywidth>60</displaywidth>
    <displayheight>30</displayheight>
    <font family="Tahoma" size="10" style="Regular" color="000000" />
    <inputs>
      <input type="number" name="handle" narrative="The integer handle identifying the instance on which the specified collection should be written. The default of zero indicates the currently active instance." stage="handle" />
      <input type="collection" name="Collection" narrative="The collection containing the data which should be written to the worksheet." stage="Collection_GC" />
      <input type="text" name="Workbook Name" narrative="The name of the workbook within which the data should be written. The default empty value indicates the current workbook within the specified instance." stage="workbookname" />
      <input type="text" name="Worksheet Name" narrative="The name of the worksheet on which the given data should be written. The default empty value indicates the current sheet within the specified workbook." stage="worksheetname" />
      <input type="text" name="Cell Reference" narrative="The reference at which the collection should be written." stage="cellref" />
      <input type="flag" name="Include Column Names" narrative="True to indicate that the column names from the collection should be written out as the first row; False to ignore the column names and just write out the data." stage="includecolnames" />
      <input type="number" name="rowlimit" narrative="Batch size, default 5000. Set lower if getting OOM" stage="rowlimit" />
    </inputs>
    <onsuccess>76a94c0c-a0b3-46c2-ba3d-b0e6eb32f51f</onsuccess>
  </stage>
  <stage stageid="79c25ffc-d104-4392-b088-1d73e0240029" name="End" type="End">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <loginhibit />
    <narrative></narrative>
    <displayx>45</displayx>
    <displayy>60</displayy>
    <displaywidth>60</displaywidth>
    <displayheight>30</displayheight>
    <font family="Tahoma" size="10" style="Regular" color="000000" />
  </stage>
  <stage stageid="56a00ede-ce25-47b7-8ab1-b4ebe8f2c9ef" name="handle" type="Data">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <narrative></narrative>
    <displayx>-255</displayx>
    <displayy>-30</displayy>
    <displaywidth>120</displaywidth>
    <displayheight>30</displayheight>
    <font family="Tahoma" size="10" style="Regular" color="000000" />
    <datatype>number</datatype>
    <initialvalue />
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="a1323bd1-0c53-4078-bb06-8fba4962eed5" name="worksheetname" type="Data">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <narrative></narrative>
    <displayx>-255</displayx>
    <displayy>60</displayy>
    <displaywidth>120</displaywidth>
    <displayheight>30</displayheight>
    <font family="Tahoma" size="10" style="Regular" color="000000" />
    <datatype>text</datatype>
    <initialvalue />
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="1175ed17-2984-4ee1-b630-f62fd5d32e7d" name="workbookname" type="Data">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <narrative></narrative>
    <displayx>-255</displayx>
    <displayy>15</displayy>
    <displaywidth>120</displaywidth>
    <displayheight>30</displayheight>
    <font family="Tahoma" size="10" style="Regular" color="000000" />
    <datatype>text</datatype>
    <initialvalue />
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="c8f5023a-90d7-422d-987b-3f8a9035eca6" name="includecolnames" type="Data">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <narrative></narrative>
    <displayx>-135</displayx>
    <displayy>60</displayy>
    <displaywidth>120</displaywidth>
    <displayheight>30</displayheight>
    <font family="Tahoma" size="10" style="Regular" color="000000" />
    <datatype>flag</datatype>
    <initialvalue />
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="76a94c0c-a0b3-46c2-ba3d-b0e6eb32f51f" name="Activate Worksheet" type="SubSheet">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <loginhibit />
    <narrative></narrative>
    <displayx>45</displayx>
    <displayy>-75</displayy>
    <displaywidth>120</displaywidth>
    <displayheight>30</displayheight>
    <font family="Tahoma" size="10" style="Regular" color="000000" />
    <inputs>
      <input type="number" name="handle" narrative="The integer handle identifying the instance on which the worksheet which should be activated resides. The default of zero indicates the currently active instance." expr="[handle]" />
      <input type="text" name="Workbook Name" narrative="The name of the workbook in which the sheet exists - The default empty value indicates the currently active workbook on the specified instance." expr="[workbookname]" />
      <input type="text" name="Worksheet Name" narrative="The name of the sheet which should be activated. The default empty text value indicates that the currently active sheet should be, er, activated, making this a no-op." expr="[worksheetname]" />
      <input type="flag" name="Create If Missing?" narrative="True to create the sheet if it isn't present on the specified workbook; False to not do so." expr="False" />
    </inputs>
    <onsuccess>1e3b602c-be95-4738-afa1-b9495967ed96</onsuccess>
    <processid>733c2840-8162-465a-a8f2-7d023087c369</processid>
  </stage>
  <stage stageid="1e3b602c-be95-4738-afa1-b9495967ed96" name="WriteCollectionEx" type="Code">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <loginhibit />
    <narrative></narrative>
    <displayx>45</displayx>
    <displayy>-15</displayy>
    <displaywidth>120</displaywidth>
    <displayheight>30</displayheight>
    <font family="Tahoma" size="10" style="Regular" color="000000" />
    <inputs>
      <input type="number" name="handle" expr="[handle]" />
      <input type="text" name="workbookname" expr="[workbookname]" />
      <input type="text" name="worksheetname" expr="[worksheetname]" />
      <input type="text" name="cellref" expr="[cellref]" />
      <input type="collection" name="collection" expr="[Collection_GC]" />
      <input type="flag" name="includecolnames" expr="[includecolnames]" />
      <input type="number" name="rowlimit" expr="[rowlimit]" />
    </inputs>
    <onsuccess>79c25ffc-d104-4392-b088-1d73e0240029</onsuccess>
    <code><![CDATA[' Get to the cell
Dim xlInstance = GetInstance(handle)
Dim xlWorkSheet As Object = GetWorksheet(handle, workbookname, worksheetname)
Dim dtRowCount As Integer = Collection.Rows.Count
Dim dtColCount As Integer = Collection.Columns.Count
Dim objXlColHeaderData(1, dtColCount) As Object
Dim OffsetRow As Integer = 0

If rowlimit < 1 Then rowlimit = 1

For i As Integer = 0 To dtColCount - 1
  objXlColHeaderData(0, i) = Collection.Columns(i).ColumnName
Next

If includecolnames Then
    xlWorkSheet.Range(cellref).Resize(1, dtColCount).Value = objXlColHeaderData
    objXlColHeaderData = Nothing
    GC.Collect()
    OffsetRow = 1
End If

Dim blStart As Integer
Dim blRowCount As Integer
If cellref = "" then cellref = "A1"
For blStart = 0 to dtRowCount -1 step rowlimit
    If blStart+rowlimit > dtRowCount Then
        blRowCount = dtRowCount - blStart
    Else
        blRowCount = rowlimit
    End If

    Dim objXlData(blRowCount, dtColCount) As Object
    For iRow As Integer = blStart To blStart + blRowCount - 1
      For iCol As Integer = 0 To dtColCount - 1
            If IsDBNull(Collection.Rows(iRow).Item(iCol)) Then
            objXlData(iRow-blStart, iCol) = ""
          Elseif TypeOf Collection.Rows(iRow).Item(iCol) is String then
          If Left(Collection.Rows(iRow).Item(iCol),1) = "=" then
            objXlData(iRow-blStart, iCol) = "'" & Collection.Rows(iRow).Item(iCol)
          Else
            objXlData(iRow-blStart, iCol) = Collection.Rows(iRow).Item(iCol)
          End if
          Elseif TypeOf Collection.Rows(iRow).Item(iCol) is DataTable then
          Dim sw as StringWriter = new StringWriter()
          Collection.Rows(iRow).Item(iCol).WriteXml(sw)
          objXlData(iRow-blStart, iCol) = sw.ToString()
        Else
            objXlData(iRow-blStart, iCol) = Collection.Rows(iRow).Item(iCol)
                End If
            Next
    Next

    xlWorkSheet.Range(cellref).Offset(OffsetRow+blStart,0).Resize(blRowCount, dtColCount).Value = objXlData
  'below SetProperty does the same as above, but uses system Culture, which is inconsistent. I intentionally dont want to do that.
  'SetProperty(xlWorkSheet.Range(cellref).Offset(OffsetRow+blStart,0).Resize(blRowCount, dtColCount), "Value", objXlData)
    objXlData = Nothing
    GC.Collect()
Next

xlWorkSheet=Nothing
Collection = Nothing
System.Runtime.GCSettings.LargeObjectHeapCompactionMode = System.Runtime.GCLargeObjectHeapCompactionMode.CompactOnce
GC.Collect() ]]></code>
  </stage>
  <stage stageid="688464a6-a1a5-4fbd-baae-6130ebdcfb20" name="cellref" type="Data">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <narrative></narrative>
    <displayx>-135</displayx>
    <displayy>-30</displayy>
    <displaywidth>120</displaywidth>
    <displayheight>30</displayheight>
    <font family="Tahoma" size="10" style="Regular" color="000000" />
    <datatype>text</datatype>
    <initialvalue xml:space="preserve">A1</initialvalue>
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="b474d3dc-f890-4786-a9f7-fa87398e9f55" name="rowlimit" type="Data">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <narrative></narrative>
    <displayx>-135</displayx>
    <displayy>15</displayy>
    <displaywidth>120</displaywidth>
    <displayheight>30</displayheight>
    <font family="Segoe UI" size="10" style="Regular" color="000000" />
    <datatype>number</datatype>
    <initialvalue>5000</initialvalue>
    <private />
    <alwaysinit />
  </stage>
  <stage stageid="91bf4f2b-1a70-4f3c-bb8e-97a425487e65" name="Collection_GC" type="Collection">
    <subsheetid>5bd911d6-6eee-4cfb-bb39-693a93c05faa</subsheetid>
    <narrative></narrative>
    <displayx>120</displayx>
    <displayy>-135</displayy>
    <displaywidth>60</displaywidth>
    <displayheight>30</displayheight>
    <font family="Segoe UI" size="10" style="Regular" color="000000" />
    <datatype>collection</datatype>
    <private />
    <alwaysinit />
  </stage>
</process>