Export null values from gridview to excel

Question

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 5-Apr-20 2:27am

Hemil Gandhi

Updated 6-Apr-20 3:32am

CHill60

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Hemil Gandhi · Answer 1 · 2020-04-06T03:33:00

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