Question DataTable Export to ExCel with Cell Style

dbdev999

New member
Joined
Apr 17, 2013
Messages
3
Programming Experience
Beginner
Hi all
I found trouble about datatable export to excel file by using Microsoft.Office.Interop.ExCel Class Library V 12.
on worksheet some cell is not format in datatable .
Invalid format as below.
Invalid-format.jpg

data in datatable
Real-format.jpg

my code

         Imports excel = Microsoft.Office.Interop.Excel

         Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

         System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")

        'verfying the datagridview having data or not
        If ((grdMonitor.Columns.Count = 0) Or (grdMonitor.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 grdMonitor.ColumnCount - 1
            dset.Tables(0).Columns.Add(grdMonitor.Columns(i).HeaderText)
        Next
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To grdMonitor.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To grdMonitor.Columns.Count - 1
                dr1(j) = grdMonitor.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next

        Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim style As Microsoft.Office.Interop.Excel.Style

        'not ensure how to use
        'style = wBook.Styles.Add("Style1")
        'style.NumberFormat = "Text"
        'style.Font.Name = "Arial"
        'style.Font.Bold = True
        'style.Font.Size = 5
        'style.Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid

        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)
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

            Next
        Next

     End sub



How to fix it.
Thank in advance.
 
Top Bottom