Center Align text in Excel cell when exporting in Windows Application using Microsoft Office Interop Library

Akram.19
 
on Jul 20, 2022 10:47 PM
417 Views

In excel file all data have left alignment, I want it to be centered dynamically.

Imports System.IO
Imports System.Data
Imports System.Configuration
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat
 
Public Class Form1
    Private SQL As New SQLControl
    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        TextBox1.Text = TextBox1.Text + 1
        If TextBox1.Text = 20 Then
            Dim constr As String = "Server=DESKTOP-9EKQ5QI;Database=CREST_CONTROLS;User=sa;Pwd=123456"
            Using con As SqlConnection = New SqlConnection(constr)
                Using cmd As SqlCommand = New SqlCommand("SELECT *FROM Report1")
                    Dim sda As SqlDataAdapter = New SqlDataAdapter
                    Try
                        cmd.Connection = con : con.Open()
                        sda.SelectCommand = cmd
                        Dim dt As DataTable = New DataTable
                        sda.Fill(dt)
 
                        If dt.Rows.Count > 0 Then
                            Dim path As String = "C:\Users\AKRAM\Desktop\Reporting"
                            If Not Directory.Exists(path) Then
                                Directory.CreateDirectory(path)
                            End If
                            Dim xlAppToExport As New Excel.Application
                            xlAppToExport.Workbooks.Add()
                            Dim xlWorkSheetToExport As Excel.Worksheet
                            xlWorkSheetToExport = CType(xlAppToExport.Sheets("Sheet1"), Excel.Worksheet)
 
                            Dim iRowCnt As Integer = 8
                            With xlWorkSheetToExport
                                .Cells(1, 1).value = "Raw_Tank_A"
                                .Cells(1, 1).FONT.NAME = "Calibri"
                                .Cells(1, 1).Font.Bold = True
                                .Cells(1, 1).Font.Size = 20
                                .Range("A1:D1").MergeCells = True
                                .Cells(iRowCnt - 1, 1).value = "id"
                                .Cells(iRowCnt - 1, 2).value = "username"
                                .Cells(iRowCnt - 1, 3).value = "tank_name"
                                .Cells(iRowCnt - 1, 4).value = "material_name"
                                .Cells(iRowCnt - 1, 5).value = "Transaction_Type"
                                .Cells(iRowCnt - 1, 6).value = "Transaction_Value"
                                .Cells(iRowCnt - 1, 7).value = "Balance"
                                .Cells(iRowCnt - 1, 8).value = "created_at"
 
                                For i = 0 To dt.Rows.Count - 1
                                    .Cells(iRowCnt, 1).value = dt.Rows(i).Item("id")
                                    .Cells(iRowCnt, 2).value = dt.Rows(i).Item("username")
                                    .Cells(iRowCnt, 3).value = dt.Rows(i).Item("tank_name")
                                    .Cells(iRowCnt, 4).value = dt.Rows(i).Item("material_name")
                                    .Cells(iRowCnt, 5).value = dt.Rows(i).Item("Transaction_Type")
                                    .Cells(iRowCnt, 6).value = dt.Rows(i).Item("Transaction_Value")
                                    .Cells(iRowCnt, 7).value = dt.Rows(i).Item("Balance")
                                    .Cells(iRowCnt, 8).value = dt.Rows(i).Item("created_at")
 
                                    iRowCnt = iRowCnt + 1
                                Next
 
                                xlAppToExport.ActiveCell.Worksheet.Cells(8, 1).AutoFormat( _
                                    ExcelAutoFormat.xlRangeAutoFormatList3)
                            End With
 
                            xlWorkSheetToExport.SaveAs("C:\Users\AKRAM\Desktop\Reporting" & "Raw.xlsx")
 
                            xlAppToExport.Workbooks.Close() : xlAppToExport.Quit()
                            xlAppToExport = Nothing : xlWorkSheetToExport = Nothing
 
                            MsgBox("Exported")
 
                        End If
                    Catch ex As Exception
                        MsgBox(ex.Message)                   
                    End Try
                End Using
            End Using
        End If
    End Sub
 
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Timer1.Enabled = True
        Timer2.Enabled = True
    End Sub
 
    Private Sub Timer2_Tick(sender As Object, e As EventArgs) Handles Timer2.Tick
        Label1.Text = DateTime.Now.ToString("dd-MMM-yyyy  hh:mm:ss tt")
    End Sub
End Class

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 21, 2022 05:59 AM

Hi Akram.19,

You need to set the HorizontalAlignment property.

.Cells(iRowCnt, 1).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter

Sameway you need to set HorizontalAlignment for other Cells.