Conversion of Excel file to .txt file with little program .NET

News Tutorials
1

Hello,

I Use a little program .NET for Conversion of Excel file to .txt file

Module_Main.vb (1,7 Ko)

Module Module_Main

    Sub Main()
        Dim MyExcel As New Microsoft.Office.Interop.Excel.Application
        Dim FileExcel = "C:\Users\Uipath\Downloads\File.xls"

        Dim Book = MyExcel.Workbooks.Open(FileExcel, False, True)

        Dim Sheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(Book.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)

        Dim R As Integer = Sheet.Rows.Count
        Dim C As Integer = Sheet.Columns.Count

        Console.WriteLine("R = " + R.ToString)
        Console.WriteLine("C = " + C.ToString)

        Dim Table As Object(,)

        Table = DirectCast(Sheet.Range("A1:" + ColumnIndexToColumnLetter(C) + R.ToString).Value, Object(,))

        Dim txt As String = ""
        For Row As Integer = 1 To CInt(Table.Length / C)
            Dim subtxt As String = ""
            For i As Integer = 1 To C
                If Table(Row, i) IsNot Nothing Then
                    subtxt += Table(Row, i).ToString + vbTab
                End If
            Next
            If subtxt.Length > 0 Then txt += subtxt + vbCrLf
        Next

        My.Computer.FileSystem.WriteAllText(FileExcel + ".txt", txt, False)

        Book.Close(False)

    End Sub


    Private Function ColumnIndexToColumnLetter(colIndex As Integer) As String
        Dim div As Integer = colIndex
        Dim colLetter As String = String.Empty
        Dim modnum As Integer

        While div > 0
            modnum = (div - 1) Mod 26
            colLetter = Chr(65 + modnum) & colLetter
            div = CInt((div - modnum) \ 26)
        End While

        Return colLetter
    End Function

End Module
2

Add “Microsoft.Office.Interop.Excel” to the solution

image
image1190×813 22.4 KB

3

Otherwise by adding the dependency “Microsoft.Office.Interop.Excel” in uipath studio with a block “Invoke code”, the following code does the same thing directly without external program.

 Dim MyExcel As  Microsoft.Office.Interop.Excel.Application =  New  Microsoft.Office.Interop.Excel.Application
 
 Dim FileExcel as String= "C:\Users\Uipath\Downloads\File.xls"

        Dim Book  as Microsoft.Office.Interop.Excel.Workbook= MyExcel.Workbooks.Open(FileExcel, False, True)

        Dim Sheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(Book.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)

        Dim R As Integer = Sheet.Rows.Count
        Dim C As Integer = Sheet.Columns.Count

        Console.WriteLine("R = " + R.ToString)
        Console.WriteLine("C = " + C.ToString)

		   Dim div As Integer = C 
        Dim colLetter As String = String.Empty
        Dim modnum As Integer

        While div > 0
            modnum = (div - 1) Mod 26
            colLetter = Chr(65 + modnum) & colLetter
            div = CInt((div - modnum) \ 26)
        End While
		
		
        Dim Table As Object(,)

        Table = DirectCast(Sheet.Range("A1:" + colLetter + R.ToString).Value, Object(,))

        Dim txt As String = ""
        For Row As Integer = 1 To CInt(Table.Length / C)
            Dim subtxt As String = ""
            For i As Integer = 1 To C
                If Table(Row, i) IsNot Nothing Then
                    subtxt += Table(Row, i).ToString + vbTab
                End If
            Next
            If subtxt.Length > 0 Then txt += subtxt + vbCrLf
        Next
		
		File.WriteAllText(FileExcel + "V2.txt", txt)
         Book.Close(False)
4

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