從DataSet到Excel

從DATASET到EXCEL

如果你要引用office中的dll，那麼你將犯下一個很大的錯誤，因為這樣不僅速度慢，而且會碰到office的版本問題

現給出兩個函數供參考

1.

   Public Sub ToExcel(ByVal ds As DataSet, ByVal SaveDl As SaveFileDialog)
        Dim strFlName As String
        With SaveDl
            .Title = "請選擇要匯出的csv文件名"
            .Filter = "csv文件(*.csv)|*.csv"
            .FileName = ""
        End With
        If SaveDl.ShowDialog = Windows.Forms.DialogResult.OK Then
            If SaveDl.FileName <> "" Then
                strFlName = SaveDl.FileName()
            Else
                Exit Sub
            End If
        Else
            Exit Sub
        End If
        Dim fs As FileStream = New FileStream(strFlName, FileMode.Create, FileAccess.Write)
        Dim sw As StreamWriter = New StreamWriter(fs, System.Text.Encoding.GetEncoding("BIG5"))
        Dim ColName As String = "", RowName As String = ""

        Dim Col As DataColumn
        Dim Row As DataRow
        For Each Col In ds.Tables(0).Columns
            ColName = ColName + Col.ColumnName + ","
        Next
        sw.WriteLine(ColName)
        For Each Row In ds.Tables(0).Rows
            For Each Col In ds.Tables(0).Columns
                RowName = RowName + Replace(Row(Col.ColumnName).ToString, ",", " ") + ","
            Next
            sw.WriteLine(RowName)
            RowName = ""
        Next
        sw.Close()
        MessageBox.Show("數據已經成功導入EXCEL文件" & strFlName, "數據導出", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub