Align text left when exporting data from datagridview to Excel?

Question

_{Thursday, March 23, 2017 9:35 PM}

I unable to align text left on the execl spread sheet. I tried xlWorkSheet.Range("E, E").Style.HorizontalAlignment = HorizontalAlignType.Left , but I receive an error.

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        TextBox1.Text = Format(Now, "MMddyy" & "_" & "HHmmss")
        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 Int16, j As Int16

        xlApp = New Microsoft.Office.Interop.Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        With xlWorkSheet.Range("A2:A40")
            .NumberFormat = "@"

        End With
        With xlWorkSheet
            .Columns("A").ColumnWidth = 10
            .Columns("B").ColumnWidth = 4
            .Columns("C").ColumnWidth = 50
            .Columns("D").ColumnWidth = 5
            .Columns("E").ColumnWidth = 4
            .Columns("F").ColumnWidth = 5
            .Columns("G").ColumnWidth = 130
            .Columns("H").ColumnWidth = 10
            .Columns("I").ColumnWidth = 50
            .Columns("J").ColumnWidth = 8
            .Columns("K").ColumnWidth = 22
            .Columns("L").ColumnWidth = 9
            .Columns("M").ColumnWidth = 6
            .Columns("N").ColumnWidth = 6
            .Columns("O").ColumnWidth = 75
           xlWorkSheet.Range("E,E").Style.HorizontalAlignment = HorizontalAlignType.Left
            For Each column As DataGridViewColumn In DataGridView1.Columns
                .Cells(1, column.Index + 1) = column.HeaderText
            Next
            For i = 1 To Me.DataGridView2.RowCount
                .Cells(i + 1, 1) = Me.DataGridView2.Rows(i - 1).Cells("test").Value
                For j = 1 To DataGridView2.Columns.Count - 1
                    .Cells(i + 1, j + 1) = DataGridView2.Rows(i - 1).Cells(j).Value
                Next
            Next
        End With




        xlWorkBook.SaveAs("C:\Users\Rick\Desktop\TEST\test" & "_" & TextBox1.Text & ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
        xlWorkBook.Close(True, misValue, misValue)
        xlApp.Quit()

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

        MessageBox.Show("Excel File Created", "Exported")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

All replies (3)

_{Thursday, March 23, 2017 9:59 PM ✅Answered}

 Try it like this...

xlWorkSheet.Range("E,E").HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft

If you say it can`t be done then i`ll try it

_{Thursday, March 23, 2017 10:48 PM}

Hello,

Here in the code sample below I focus solely on the alignment of a column. I took an existing sheet, column C, changed alignment to right then ran this code and the alignment was changed to left.

The key was to select the column before changing the alignment.

Option Strict On
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Imports System.IO

Module OpenWorkSheets1
    Public Sub SetColumnToText(
        ByVal OpenFileName As String,
        ByVal SheetName As String,
        ByVal Column As String)

        If File.Exists(OpenFileName) Then

            Dim Proceed As Boolean = False

            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(OpenFileName)

            xlApp.Visible = False

            xlWorkSheets = xlWorkBook.Sheets

            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If

                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next
            If Proceed Then
                xlCells = xlWorkSheet.Range(Column & ":" & Column)
                xlCells.Select()
                xlCells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
            Else
                MessageBox.Show(SheetName & " not found.")
            End If

            xlWorkSheet.SaveAs(OpenFileName)

            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlCells)
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        Else
            MessageBox.Show("'" & OpenFileName & "' not located. Try one of the write examples first.")
        End If
    End Sub
    Private Sub ReleaseComObject(ByVal excelObject As Object)
        Try
            If excelObject IsNot Nothing Then
                Marshal.ReleaseComObject(excelObject)
                excelObject = Nothing
            End If
        Catch ex As Exception
            excelObject = Nothing
        End Try
    End Sub
End Module

Called it as shown below

Dim FileName As String = IO.Path.Combine(Application.StartupPath, "Customers.xlsx")
SetColumnToText(FileName, "Customers", "C")

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Thursday, March 23, 2017 11:24 PM}

 If you want to change an entire column,  you can just use the following.  This seems to work fine without selecting the column first.

ExcelSheet.Range("E1").EntireColumn.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft

If you say it can`t be done then i`ll try it