How to Create excel sheet

Question

1.00/5 (2 votes)

See more:

VS.NET2003

, +

hi to all
i want to create a excel file from dataset.
please help me to solve this problem.

im using vs2003 with vb.net

Posted 11-Dec-13 19:18pm

VenuKalyan

Add a Solution

Comments

Maciej Los 12-Dec-13 2:04am

What have you tried? Where are you stuck?

DaveAuld 12-Dec-13 3:33am

Use relevant tags also. You state VS and VB.net, so why have you got tags for javascript and jquery?

2 solutions

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)

DretenX · Answer 1 · 2013-12-29T23:43:00

Hi, try this to save datagridview to file:
(use a .csv filename as output; this can be read in excel...)

VB

Private Sub SaveGridDataInFile(ByVal DGV As Object, ByRef fName As String)
        Dim I As Integer = 0
        Dim j As Integer = 0
        Dim cellvalue$
        Dim rowLine As String = ""
        Try
            Dim objWriter As New System.IO.StreamWriter(fName, False) 
            For j = 0 To (DGV.Rows.Count - 1)
                For I = 0 To (DGV.Columns.Count - 1)
                    If Not TypeOf DGV.Item(I, j).Value Is DBNull Then
                        cellvalue = DGV.Item(I, j).Value
                    Else
                        cellvalue = ""
                    End If
                    rowLine = rowLine + cellvalue + ","
                Next
                objWriter.WriteLine(rowLine)
                rowLine = ""
            Next
            objWriter.Close()
            MsgBox("Text written to file")
        Catch e As Exception
            MessageBox.Show("Error occured while writing to the file: " & fName & "." + e.ToString())
        Finally
            FileClose(1)
        End Try
    End Sub

DretenX · Answer 2 · 2013-12-29T23:57:00

Hi, here i am again
here you have the code to export grid to excel or word
Exp = "Word" Then 'export to word
Exp = "Excel" Then 'export to excel
template= preconfigured template file in excel or word
rgrds,

VB

Public Function ExportDataGridView(ByRef dgv As DataGridView, ByVal Fname As String, ByVal Exp As String, ByVal Template As String) As String
        Try
            'copy DGV to clipboard
            Dim s As String = ""
            Dim oCurrentCol As DataGridViewColumn    'Get header
            oCurrentCol = dgv.Columns.GetFirstColumn(DataGridViewElementStates.Visible)
            Do
                s &= oCurrentCol.HeaderText & Chr(Keys.Tab)
                oCurrentCol = dgv.Columns.GetNextColumn(oCurrentCol, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
            Loop Until oCurrentCol Is Nothing
            s = s.Substring(0, s.Length - 1)
            s &= Environment.NewLine    'Get rows
            For Each row As DataGridViewRow In dgv.Rows
                oCurrentCol = dgv.Columns.GetFirstColumn(DataGridViewElementStates.Visible)
                Do
                    If row.Cells(oCurrentCol.Index).Value IsNot Nothing Then
                        s &= Replace(row.Cells(oCurrentCol.Index).Value.ToString, Chr(10), " - ")
                    Else
                        s &= " "
                    End If
                    s &= Chr(Keys.Tab)
                    oCurrentCol = dgv.Columns.GetNextColumn(oCurrentCol, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
                Loop Until oCurrentCol Is Nothing
                s = s.Substring(0, s.Length - 1)
                s &= Chr(13) 'Environment.NewLine
            Next    'Put to clipboard
            Dim o As New DataObject
            o.SetText(s)
            Clipboard.SetDataObject(o, True)
        Catch ex As Exception
            Return "0"
        End Try

        If Exp = "Excel" Then 'export to excel
            Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
            Dim oExcel As Excel.ApplicationClass
            oExcel = New Excel.ApplicationClass
            Dim oBook As Object
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
            Try 
                'Create a workbook in Excel.
                oBook = oExcel.Workbooks.Open(Template)
                'Paste the data.
                oBook.Worksheets(1).Range("A1").Select()
                oBook.Worksheets(1).Paste()
                'Save the workbook and quit Excel.
                oBook.SaveAs(Frm1.LblTemp.Text & "Temp\" & Fname & Format(Now, "yyMMddHHmm") & ".xls")
                oBook = Nothing
                CheckExcel(False)
                oExcel = Nothing
                GC.Collect()
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
                Return Frm1.LblTemp.Text & "Temp\" & Fname & Format(Now, "yyMMddHHmm") & ".xls"
            Catch ex As Exception
                'MessageBox.Show(ex.ToString)
                oBook = Nothing
                CheckExcel(False)
                oExcel = Nothing
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
                Return "0"
            End Try

        ElseIf Exp = "Word" Then 'export to word
            Dim oWord As Microsoft.Office.Interop.Word.ApplicationClass
            oWord = New Microsoft.Office.Interop.Word.ApplicationClass
            Dim oDoc As Microsoft.Office.Interop.Word.Document
            Try
                oDoc = oWord.Documents.Open(Template)
                Dim WordRange = oWord.ActiveDocument.Range(Start:=0, End:=0)
                WordRange.Paste()
                oDoc.SaveAs(Frm1.LblTemp.Text & "Temp\" & Fname & Format(Now, "yyMMddHHmm") & ".doc")
                oDoc = Nothing
                oWord.Quit()
                oWord = Nothing
                Return Frm1.LblTemp.Text & "Temp\" & Fname & Format(Now, "yyMMddHHmm") & ".doc"
            Catch ex As Exception
                'MessageBox.Show(ex.ToString)
                oDoc = Nothing
                oWord.Quit()
                oWord = Nothing
                Return "0"
            End Try
        Else
            Return "0"
        End If
    End Function

How to Create excel sheet

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0