Avatar of Wilder1626
Wilder1626Flag for Canada

asked on 

VB.NET - Export to Excel in TEXT Format

Hi.

I'm new to VB.Net but i'm trying to adjust myself compare to VB6.

I have below code that i'm using to export to excel what i have in my DataGridView1.
What i have in my DataGrid:
User generated image
In my column ID, as you can see, I'm having leading zeros.

But when I export to excel, I'm loosing them.
User generated image
How can i export to excel as Text format all columns so that i can keep leading zeros.

At the same time, i would like to change the sheet name from "Sheet1" to "Data".

Thanks for your help.

    Private Sub btnImport_Click(sender As Object, e As EventArgs) Handles btnImport.Click
        Dim xlApp As Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer
        Dim Handler As MessageBox

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("Sheet1")

        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                For k As Integer = 1 To DataGridView1.Columns.Count
                    xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                    xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                Next
            Next
        Next
        On Error GoTo Handler
        xlWorkSheet.SaveAs("C:\Users\" & strUserLabel.Text & "\Desktop\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\Users\" & strUserLabel.Text & "\Desktop\vbexcel.xlsx")
        Exit Sub
Handler:
        MsgBox("The Excel file his openned. Please close it and then resume.")
    End Sub

Open in new window


Visual Basic.NET

Avatar of undefined
Last Comment
Wilder1626
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Wilder1626
Wilder1626
Flag of Canada image

ASKER

Thanks.
You reminded me that the text format is "@".

xlWorkSheet.Name = "Data"
xlWorkSheet.Range("A:Z").NumberFormat = "@"

Open in new window

Visual Basic.NET
Visual Basic.NET

Visual Basic .NET (VB.NET) is an object-oriented programming language implemented on the .NET framework, but also supported on other platforms such as Mono and Silverlight. Microsoft launched VB.NET as the successor to the Visual Basic language. Though it is similar in syntax to Visual Basic pre-2002, it is not the same technology,

96K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo