如何创建Excel工作表 [英] How to Create excel sheet

问题描述

嗨所有

i想要从数据集创建一个excel文件。

请帮我解决这个问题。



即时通讯使用vs2003和vb.net

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

推荐答案

您好，请尝试将datagridview保存到文件：

（使用.csv文件名作为输出;这可以在excel中读取...）

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

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 作为 字符串 =
尝试
Dim objWriter As 新 System.IO.StreamWriter（fName， False ）
对于 j = 0 （DGV.Rows.Count - 1 ）
对于 I = 0 至 （DGV.Columns.Count - 1 ）
如果 不 TypeOf DGV.Item（I ，j）.Value DBNull 然后
cellvalue = DGV.Item（I，j） .Value
Else
cellvalue =
结束 如果
rowLine = rowLine + cellvalue + ，
下一步
objWriter.WriteLine（rowLine）
rowLine =
下一步
objWriter.Close（）
MsgBox（ 写入文件的文本）
Catch e As 异常
MessageBox.Show（ 写入文件时出错：& fName& 。 + e.ToString（））
最后
FileClose（ 1 ）
结束 尝试
结束 Sub

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

这里我又来了

这里你有代码将网格输出到excel或word

Exp =Word然后'导出到word

Exp =Excel然后'导出到excel

template =预先配置的模板文件excel或word

rgrds，

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,

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

这篇关于如何创建Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！