Click here to Skip to main content
15,879,326 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello coders, first i am inserting some values to grid view then exporting it to excel but post export i found "
 
" (Edit - this is a non-breaking space) in excel where i have inserted null value in grid.

please help me on this I want blank cell for "dbnull.value"

What I have tried:

Dim dt As New DataTable
        GridView1.DataSource = dt
        dt.Columns.Add("acno", GetType(String))
        dt.Columns.Add("particulars", GetType(String))
        dt.Columns.Add("amount", GetType(Double))
        dt.Columns.Add("chequenumber")
        dt.Columns.Add("chequedate")
        Dim ses As String
        ses = ""
        If RadioButton1.Checked = True Then
            ses = "1C"
        ElseIf RadioButton2.Checked = True Then
            ses = "2C"
        End If

        If Val(TextBox1.Text) <> 0 Then
            dt.Rows.Add(ATMISS, "NFS " & TextBox19.Text & "-" & ses, -1 * TextBox1.Text, DBNull.Value.ToString, DBNull.Value.ToString)
        End If
        If TextBox11.Text <> 0 Then
            dt.Rows.Add(NPCIEXP, "NFS " & TextBox19.Text & "-" & ses, Math.Round(-1 * TextBox11.Text, 2), DBNull.Value.ToString, DBNull.Value.ToString)
        End If
        If TextBox4.Text <> 0 Then
            dt.Rows.Add(ATMACQ, "NFS " & TextBox19.Text & "-" & ses, TextBox4.Text, DBNull.Value.ToString, DBNull.Value.ToString)
        End If
        If TextBox14.Text <> 0 Then
            dt.Rows.Add(NPCIINC, "NFS " & TextBox19.Text & "-" & ses, TextBox14.Text, DBNull.Value, DBNull.Value)
        End If
        If TextBox5.Text <> 0 Then
            dt.Rows.Add(ATMADJ, "ADJUSTMENT " & TextBox19.Text & "-" & ses, -1 * TextBox5.Text, DBNull.Value, DBNull.Value)
        End If
        If TextBox15.Text <> 0 Then
            dt.Rows.Add(NPCIEXP, "ADJUSTMENT " & TextBox19.Text & "-" & ses, -1 * TextBox15.Text, DBNull.Value, DBNull.Value)
        End If
        If TextBox6.Text <> 0 Then
            dt.Rows.Add(ATMADJ, "ADJUSTMENT " & TextBox19.Text & "-" & ses, TextBox6.Text, DBNull.Value, DBNull.Value)
        End If
        If TextBox16.Text <> 0 Then
            dt.Rows.Add(NPCIINC, "ADJUSTMENT " & TextBox19.Text & "-" & ses, TextBox16.Text, DBNull.Value, DBNull.Value)
        End If
        If TextBox9.Text <> 0 Then
            dt.Rows.Add(ATMSTL, "NFS " & TextBox19.Text & "-" & ses, TextBox9.Text, DBNull.Value, DBNull.Value)
        End If
        If TextBox10.Text <> 0 Then
            dt.Rows.Add(ATMSTL, "NFS " & TextBox19.Text & "-" & ses, -1 * TextBox10.Text, DBNull.Value.ToString, DBNull.Value.ToString)
        End If

        GridView1.DataBind()
        ExportToExcel()
Posted
Updated 6-Apr-20 3:32am
v2
Comments
CHill60 6-Apr-20 6:47am    
What is in the ExportToExcel method?
Hemil Gandhi 6-Apr-20 9:28am    
some how i have figured out the solution, problem was in exporting data not in inserting data

1 solution

replace the   while exporting data to excel

Protected Sub ExportExcel()
    Dim SES As String
    If RadioButton1.Checked = True Then
        SES = "1C"
    Else
        SES = "2C"
    End If
    Dim dt As New DataTable("salary")
    For Each cell As TableCell In GridView1.HeaderRow.Cells
        dt.Columns.Add(cell.Text)
    Next
    For Each row As GridViewRow In GridView1.Rows
        dt.Rows.Add()
        For i As Integer = 0 To row.Cells.Count - 1
            If row.Cells(i).Text = " " Then
                dt.Rows(dt.Rows.Count - 1)(i) = DBNull.Value
            Else
                dt.Rows(dt.Rows.Count - 1)(i) = row.Cells(i).Text
            End If
        Next
    Next
    Using wb As New XLWorkbook()
        wb.Worksheets.Add(dt)

        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;filename=NFS" & TextBox19.Text & " - " & SES & ".xlsx")
        Using MyMemoryStream As New MemoryStream()
            wb.SaveAs(MyMemoryStream)
            MyMemoryStream.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.[End]()
        End Using
    End Using
End Sub
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900