Write excel file using Vb.net

Help Activities
, , ,
1

Hi Community,

Below is the code to write datatable to Excel file

    Dim _excel As New Microsoft.Office.Interop.Excel.Application
    Dim wBook As Microsoft.Office.Interop.Excel.Workbook
    Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

    wBook = _excel.Workbooks.Add()
    wSheet = wBook.ActiveSheet()

    Dim dt As System.Data.DataTable = dtTemp
    Dim dc As System.Data.DataColumn
    Dim dr As System.Data.DataRow
    Dim colIndex As Integer = 0
    Dim rowIndex As Integer = 0

    For Each dc In dt.Columns
        colIndex = colIndex + 1
        _excel.Cells(1, colIndex) = dc.ColumnName
    Next
    For Each dr In dt.Rows
        rowIndex = rowIndex + 1
        colIndex = 0
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            _excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
        Next
    Next
    wSheet.Columns.AutoFit()
    Dim strFileName As String = "C:\datatable.xlsx"
    If System.IO.File.Exists(strFileName) Then
        System.IO.File.Delete(strFileName)
    End If

    wBook.SaveAs(strFileName)
    wBook.Close()
    _excel.Quit()

while compiling I am getting error at line 6 - Option strict allows conversion from Object to Worksheet

Immediate Solution is required as I am writing a datatable with 800k rows into an excel which as always uipath fails to achieve also I tried dividing it in chunks and writing that also did not work.

my time line is breaching.

Thanks

@ppr @ptrobot @Yoichi @supermanPunch can you also have a look on this.

2

Have you tested type casting?

wSheet = CType(wBook.ActiveSheet(), Microsoft.Office.Interop.Excel.Worksheet)
3

Thanks a lot it is working I was doing casting wrong!.
1 more thing

Dim _excel As New Microsoft.Office.Interop.Excel.Application
Dim wBook As Microsoft.Office.Interop.Excel.Workbook
Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

wBook = _excel.Workbooks.Add()
wSheet = CType(wBook.ActiveSheet(), Microsoft.Office.Interop.Excel.Worksheet)


Dim dc As System.Data.DataColumn
Dim colIndex As Integer = 0
Dim rowIndex As Integer = 0
Dim Nbligne As Integer = DtMesures.Rows.Count

For Each dc In DtMesures.Columns
  colIndex = colIndex + 1

  wSheet.Cells(1, colIndex) = dc.ColumnName
  wSheet.Cells(2, colIndex).Resize(Nbligne, ).Value = _excel.Application.transpose(DtMesures.Rows.OfType(Of DataRow)().[Select](Function(k) CObj(k(dc.ColumnName))).ToArray())
Next

In the above code on the last second line it is giving me late binding error can you please help on that as well.

Thanks!!

4

Sorry but I’m no expert in Excel.Interop. I hope somebody with more experience working with Excel.Interop can chime in.

5

Curious why you’re trying to do this in vb.net instead of just using the very simple activities that do this for you.

6

Thankyou @ptrobot for support

7

Hi @postwick,

because the rows are 800K plus and uipath is not able to write data in any excel file.

Thanks

8

Of course UiPath can write data to Excel.

Using code/LINQ is not faster than activities. Activities just have the same kind of code inside them.

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.