Rabeea qabaha Rabeea qabaha - 1 month ago 9
Vb.net Question

printing gridlines in excel through interop

I'm trying to export

DataGridView
items to Excel file and everything perfect, but I want the gridlines appear when the client want to print the sheet, I can do this from inside excel as shown here.

but how can I do this from the vb.net code ??
and I have a problem that : I cant make the text Alignment center I tried this code:

wSheet.Range("a2", "z1000").HorizontalAlignment = excel.XlVAlign.xlVAlignCenter


but it show this error:
Public member 'XlVAlign' on type 'ApplicationClass' not found.


Is there a different way to make all columns Alignment center.

this Is my code :

If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
Exit Sub
End If

'Creating dataset to export
Dim dset As New DataSet
'add table to dataset
dset.Tables.Add()
'add column to that table
For i As Integer = 0 To DataGridView1.ColumnCount - 1
If DataGridView1.Columns(i).Visible = True Then
dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
End If
Next
Dim celltext As String
Dim count As Integer = -1
'add rows to the table
Dim dr1 As DataRow
For i As Integer = 0 To DataGridView1.RowCount - 1
dr1 = dset.Tables(0).NewRow


For j As Integer = 0 To DataGridView1.Columns.Count - 1
If DataGridView1.Columns(j).Visible = True Then
count = count + 1

dr1(count) = DataGridView1.Rows(i).Cells(j).Value
End If
Next

count = -1
dset.Tables(0).Rows.Add(dr1)
Next

Dim excel As New Excel.Application
Dim wBook As Excel.Workbook
Dim wSheet As Excel.Worksheet

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


Dim dt As System.Data.DataTable = dset.Tables(0)
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()

' for the header
wSheet.Rows(1).Font.Name = "Droid Arabic Kufi"
wSheet.Rows(1).Font.size = 11
wSheet.Rows(1).Font.Bold = True
wSheet.Rows(1).HorizontalAlignment = HorizontalAlignment.Right
Dim mycol As System.Drawing.Color = System.Drawing.ColorTranslator.FromHtml("#20b2aa")
wSheet.Rows(1).Font.color = mycol
' for all the sheet without header
wSheet.Range("a2", "z1000").Font.Name = "Droid Arabic Kufi"
wSheet.Range("a2", "z1000").Font.Size = 10

wSheet.Range("a2", "z1000").HorizontalAlignment = excel.XlVAlign.xlVAlignCenter

wSheet.Range("A1:X1").EntireColumn.AutoFit()
wSheet.Range("A1:X1").EntireRow.AutoFit()

Dim saveFileDialog1 As New SaveFileDialog()
saveFileDialog1.Filter = "Excel Workbook|*.xls|Excel Workbook 2011|*.xlsx"
saveFileDialog1.Title = "Save Excel File"
saveFileDialog1.FileName = "Export " & Now.ToShortDateString & ".xlsx"
saveFileDialog1.ShowDialog()

saveFileDialog1.InitialDirectory = "C:/"
If saveFileDialog1.FileName <> "" Then

Dim fs As System.IO.FileStream = CType(saveFileDialog1.OpenFile(), System.IO.FileStream)
fs.Close()

End If


Dim strFileName As String = saveFileDialog1.FileName
Dim blnFileOpen As Boolean = False


Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
Exit Sub
End Try

If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If

wBook.SaveAs(strFileName)
excel.Workbooks.Open(strFileName)
excel.Visible = True
Exit Sub
errorhandler:
MsgBox(Err.Description)
End Sub


And Is there a way to make rows In different color like row 1 background color blue Row 2 background color white, row 2 background color blue Row 4 background color ...etc

Note :

what is the default :
enter image description here

what I want :
enter image description here

Answer

Thank to H.Fadlallah for helping me

This the answer:

make Gridlines to the excel sheet :

Dim formatRange As Excel.Range = wSheet.UsedRange
Dim cell As Excel.Range = wSheet.Range("a1", "j" & DataGridView1.RowCount + 1 & "")
Dim border As Excel.Borders = cell.Borders
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
border.Weight = 1.0

And this code to make Alignment center :

wSheet.Range("a2", "z1000").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter

And this is the All Code To export DataGridView To Excel:

Button code :

Private Sub To_Excel_picbox_but_Click(sender As Object, e As EventArgs) Handles To_Excel_picbox_but.Click
    Try
        Dim day As Integer = Date.Today.Day
        Dim month As Integer = Date.Today.Month
        Dim year As Integer = Date.Today.Year

        SaveFileDialog1.Filter = "Excel File|*.xlsx"
        SaveFileDialog1.Title = "Save an Excel File"
        SaveFileDialog1.FileName = " الحوالات المرسلة" & day & "-" & month & "-" & year & ".xlsx"
        SaveFileDialog1.InitialDirectory = "C:/"
        Application.EnableVisualStyles()
        If SaveFileDialog1.ShowDialog = DialogResult.OK Then
            If SaveFileDialog1.FileName <> "" Then
                BackgroundWorker2.RunWorkerAsync()
                Dim fs As System.IO.FileStream = CType(SaveFileDialog1.OpenFile(), System.IO.FileStream)
                fs.Close()
            End If
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

BackGroundWorker do work code:

Private Sub BackgroundWorker2_DoWork(sender As Object, e As DoWorkEventArgs) Handles BackgroundWorker2.DoWork
    ExporttoExcel(DataGridView1)
End Sub

Export Data To Excel Code:

Sub ExporttoExcel(ByVal DataGridView1 As DataGridView)

    'verfying the datagridview having data or not
    If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
        Exit Sub
    End If

    'Creating dataset to export
    Dim dset As New DataSet
    'add table to dataset
    dset.Tables.Add()
    'add column to that table
    For i As Integer = 0 To DataGridView1.ColumnCount - 1
        If DataGridView1.Columns(i).Visible = True Then
            dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
        End If
    Next
    Dim celltext As String
    Dim count As Integer = -1
    'add rows to the table
    Dim dr1 As DataRow
    For i As Integer = 0 To DataGridView1.RowCount - 1
        dr1 = dset.Tables(0).NewRow


        For j As Integer = 0 To DataGridView1.Columns.Count - 1
            If DataGridView1.Columns(j).Visible = True Then
                count = count + 1

                dr1(count) = DataGridView1.Rows(i).Cells(j).Value
            End If
        Next

        count = -1
        dset.Tables(0).Rows.Add(dr1)
    Next

    Dim excel As New Excel.Application
    Dim wBook As Excel.Workbook
    Dim wSheet As Excel.Worksheet

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

    Dim dt As System.Data.DataTable = dset.Tables(0)
    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

    'calculate the sum for "المبلغ" from the datagridview
    Dim Result As Double
    For i As Integer = 0 To DataGridView1.RowCount - 1
        Result += DataGridView1.Rows(i).Cells(0).Value
        'Change the number 2 to your column index number (The first column has a 0 index column)
        'In this example the column index of Price is 2
    Next

    'add the sum to sheet
    wSheet.Cells(DataGridView1.RowCount + 2, 1) = Result
    wSheet.Cells(DataGridView1.RowCount + 2, 2) = "المجموع"

    ' for the header 
    wSheet.Rows(1).Font.Name = "Droid Arabic Kufi"
    wSheet.Rows(1).Font.size = 11
    wSheet.Rows(1).Font.Bold = True
    wSheet.Rows(1).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
    Dim mycol As System.Drawing.Color = System.Drawing.ColorTranslator.FromHtml("#20b2aa")
    wSheet.Rows(1).Font.color = mycol

    ' for all the sheet without header
    wSheet.Range("a2", "z1000").Font.Name = "Droid Arabic Kufi"
    wSheet.Range("a2", "z1000").Font.Size = 10
    ' make the sheet Alignment center
    wSheet.Range("a2", "z1000").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter

    wSheet.Range("A1:X1").EntireColumn.AutoFit()
    wSheet.Range("A1:X1").EntireRow.AutoFit()

    wSheet.Columns("J").ColumnWidth = 28

    'make the first column "المبلغ" format is money
    wSheet.Columns("A").NumberFormat = "#,##0_);[Red](#,##0)"

    ' this add Grid line to all rows and columns 
    Dim formatRange As Excel.Range = wSheet.UsedRange
    Dim cell As Excel.Range = wSheet.Range("a1", "j" & DataGridView1.RowCount + 1 & "")
    Dim border As Excel.Borders = cell.Borders
    border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
    border.Weight = 1.0

    ' this add header and footer when printing the sheet
    wSheet.PageSetup.CenterHeader = "&""Droid Arabic Kufi,Bold""&14الحوالات الصادرة"
    wSheet.PageSetup.RightFooter = DateTime.Now
    wSheet.PageSetup.LeftFooter = "Page &P of &N"

    'make the print page horizontal
    wSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape

    'make all columns fit in one page
    wSheet.PageSetup.Zoom = False
    wSheet.PageSetup.FitToPagesWide = 1
    wSheet.PageSetup.FitToPagesTall = False

    Dim strFileName As String = saveFileDialog1.FileName
    Dim blnFileOpen As Boolean = False

    Try
        Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
        fileTemp.Close()
    Catch ex As Exception
        blnFileOpen = False
        Exit Sub
    End Try

    If System.IO.File.Exists(strFileName) Then
        System.IO.File.Delete(strFileName)
    End If

    wBook.SaveAs(strFileName)
    excel.Workbooks.Open(strFileName)
    excel.Visible = True
    Exit Sub
    errorhandler:
    MsgBox(Err.Description)

End Sub
Comments