Data export to Excel

[dr223]

Hallo,

I have the following code which works fine. However, I want to change one format when I export it to excel ..

Colum 3 is Sort Code and have the format 00-00-00, I want to change it to 000000 i.e., remove the dashes.

When exported to excel I use a formalue (Substitute(A1, "-", "") and this removes the dashes. But what I want to do is remove the dashes before exporting to excel... Please see the code below for datagrid - excel export, highlighted is where I think the one line code should be...

Any ideas... Thanks

Private Sub ExporttoExcel()
        'verifying the datagridview having data or not
        If ((DgvQuarCommit.Columns.Count = 0) Or (DgvQuarCommit.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 DgvQuarCommit.ColumnCount - 1
            dset.Tables(0).Columns.Add(DgvQuarCommit.Columns(i).HeaderText)
        Next

        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To DgvQuarCommit.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DgvQuarCommit.Columns.Count - 1
                dr1(j) = DgvQuarCommit.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next

        Dim excel As New Microsoft.Office.Interop.Excel.Application
        Dim wBook As Microsoft.Office.Interop.Excel.Workbook
        Dim wSheet As Microsoft.Office.Interop.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).NumberFormat = "@"
            excel.Cells(1, colIndex) = dc.ColumnName
            'excel.Cells(1, colIndex).NumberFormat = "@"
        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).NumberFormat = "@"                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                'excel.Cells(rowIndex + 1, colIndex).NumberFormat = "@"

            Next
        Next

        wSheet.Columns.AutoFit()
        Dim strFileName As String = "E:\GPRD InfoMgtSys\GP Payments\GPRD Payment Invoice"
        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
        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
    End Sub

[stanav]

Howabout removing the dash from the value when you populate the datatable?

'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To DgvQuarCommit.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DgvQuarCommit.Columns.Count - 1
                'If it's column 3, remove the dash from the value
                If j = 3 Then
                      dr1(j) = DgvQuarCommit.Rows(i).Cells(j).Value.ToString.Replace("-", "")
                Else
                      dr1(j) = DgvQuarCommit.Rows(i).Cells(j).Value
                End If
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next

[dr223]

Thanks for the reply..

When I run the application I receive an unhandled error on this line of code

dr1(j) = DgvQuarCommit.Rows(i).Cells(j).Value.ToString.Replace("-", "")

"NullReferenceException was unhandled"
Object reference not set to an instance of an object.

Please note: Sometimes, the sort code might be null if it is to be paid by cheque. I have also tried to run the application with only records which have no null sort codes the error is still observed.

Any ideas....

Thanks