By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,444 Members | 3,182 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Export complete database to excel file

P: 3
First of all, I need to give some credit to Mahesh Chand for providing me with an excellent basis to export data to excel.

What does this code do:
As the title says, this code is capable of extracting all tables and it's data from any given database! I was searching the net for a program like this, but I didn't come accross any (free) versions. So I decided to write it myself.


To get this code to work, you need to add a reference to Excel.dll by using Add Reference on the project and selecting Microsoft Excel 9.0 (or 10.0) Object Library from the COM tab on the Add Reference dialog.

And then import the following namespace:

Expand|Select|Wrap|Line Numbers
  1. Imports System.Runtime.InteropServices.Marshal
Now add the following class to your project:

Expand|Select|Wrap|Line Numbers
  1. Private Sub create(ByVal sDatabaseName As String)
  2. Dim dsTables As DataSet = New DataSet
  3.  
  4. 'Get all Tables from database
  5. dsTables = getAllTables(sDatabaseName)
  6. 'Create Excel Application, Workbook, and WorkSheets
  7. Dim xlExcel As New Excel.Application
  8. Dim xlBooks As Excel.Workbooks
  9. Dim xlBook As Excel.Workbook
  10. Dim tblSheet As Excel.Worksheet
  11. Dim xlCells As Excel.Range
  12. Dim sFile As String
  13. 'File name for the excel file
  14. sFile = Server.MapPath("~\Sheets\" & sDatabaseName & "_data.xls")
  15. xlExcel.Visible = False : xlExcel.DisplayAlerts = False
  16. xlBooks = xlExcel.Workbooks
  17. xlBook = xlBooks.Add
  18. For i As Integer = 0 To dsTables.Tables.Count - 1
  19.     tblSheet = xlBook.Worksheets.Add
  20.     tblSheet.Name = dsTables.Tables(i).TableName
  21.     xlCells = tblSheet.Cells
  22.     'Fill all cells with data 
  23.     GenerateExcelFile(dsTables.Tables(i), xlCells) 
  24. Next
  25. 'Remove initial excel sheets. Within a try catch because the database 
  26. 'could be empty (a workbook without worksheets is not allowed)
  27. Try
  28.     Dim SheetCount As Integer = xlExcel.Sheets.Count
  29.     CType(xlExcel.Sheets(SheetCount - 0), Excel.Worksheet).Delete()
  30.     CType(xlExcel.Sheets(SheetCount - 1), Excel.Worksheet).Delete()
  31.     CType(xlExcel.Sheets(SheetCount - 2), Excel.Worksheet).Delete()
  32. Catch ex As Exception
  33. End Try
  34. 'Save the excel file
  35. xlBook.SaveAs(sFile)
  36. 'Make sure all objects are disposed
  37. xlBook.Close()
  38. xlExcel.Quit()
  39. ReleaseComObject(xlCells)
  40. ReleaseComObject(tblSheet)
  41. ReleaseComObject(xlBook)
  42. ReleaseComObject(xlBooks)
  43. ReleaseComObject(xlExcel)
  44. xlExcel = Nothing
  45. xlBooks = Nothing
  46. xlBook = Nothing
  47. tblSheet = Nothing
  48. xlCells = Nothing
  49. 'Let the Garbage Collector know it can get to work
  50. GC.Collect()
  51. 'Export Excel for download
  52. Try
  53. HttpContext.Current.Response.ContentType = "application/octet-stream"
  54. HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.IO.Path.GetFileName(sFile))
  55. HttpContext.Current.Response.Clear()
  56. HttpContext.Current.Response.WriteFile(sFile)
  57. HttpContext.Current.Response.End()
  58. Catch ex As Exception
  59. 'An exception will be thrown, but can just be ignored
  60. End Try
  61. End Sub
To generate the individual sheets, the following Sub is used:

Expand|Select|Wrap|Line Numbers
  1. Private Sub GenerateExcelFile(ByRef table As DataTable, ByVal xlCells As Excel.Range)
  2. Dim dr As DataRow, ary() As Object
  3. Dim iRow As Integer, iCol As Integer
  4. 'Output Column Headers
  5. For iCol = 0 To table.Columns.Count - 1
  6.     xlCells(1, iCol + 1) = table.Columns(iCol).ToString
  7.     xlCells(1).EntireRow.Font.Bold = True
  8. Next
  9. 'Output Data
  10. For iRow = 0 To table.Rows.Count - 1
  11.     dr = table.Rows.Item(iRow)
  12.     ary = dr.ItemArray
  13.     For iCol = 0 To UBound(ary)
  14.         xlCells(iRow + 2, iCol + 1) = ary(iCol).ToString
  15.         Response.Write(ary(iCol).ToString & vbTab)
  16.     Next
  17. Next
  18. xlCells.Columns.AutoFit()
  19. End Sub
And now the trick to getting all tables and data from a database:

Expand|Select|Wrap|Line Numbers
  1. Public database as String
  2. Public ReadOnly Property getAllTables(ByVal sDB As String) As DataSet
  3.     Get
  4.         database = sDB
  5.         Dim m_dshelp As DataSet = New DataSet
  6.         getRequestedAllTables(m_dshelp)
  7.         Return m_dshelp
  8.     End Get
  9. End Property
  10.  
  11. Private Function getRequestedAllTables(ByRef p_dataset As DataSet) As Boolean
  12. 'Retrieve all tablenames from the database:
  13. Dim sSQL As String
  14. Dim dsTables As DataSet = New DataSet
  15. sSQL = "SELECT [TableName] = so.name, [RowCount] = MAX(si.rows) " & _
  16. "FROM sysobjects so, sysindexes si " & _
  17. "WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name) AND si.rows > 0 " & _
  18. "GROUP BY so.name " & _
  19. "ORDER BY 2 DESC"
  20. getData(sSQL, "Tables", dsTables)
  21. 'Loop thrue all tables and do a SELECT *. Then add them to the dataset
  22. For i As Integer = 0 To dsTables.Tables(0).Rows.Count - 1
  23.     sSQL = "SELECT * FROM " & dsTables.Tables(0).Rows(i).Item(0)
  24.     getData(sSQL, dsTables.Tables(0).Rows(i).Item(0), p_dataset)
  25. Next
  26. End Function
  27.  
  28. Private Function getData(ByVal p_sql As String, ByVal p_table As String, ByRef pdataset As DataSet) As Boolean
  29. Dim objDataAdapter As SqlDataAdapter
  30. Dim objcommand As SqlCommand
  31. objcommand = New SqlCommand(p_sql, getConnection)
  32. objDataAdapter = New SqlDataAdapter(objcommand)
  33. objDataAdapter.Fill(pdataset, p_table)
  34. End Function
  35.  
  36. Private Function getConnection() As SqlConnection
  37. If (ConfigurationManager.AppSettings("SQLPW") <> "") Then
  38.     getConnection = New SqlConnection("Server=" & _
  39.     ConfigurationManager.AppSettings("SQLserver") & ";password=" & _
  40.     ConfigurationManager.AppSettings("SQLPW") & "; user=" & _
  41.     ConfigurationManager.AppSettings("SQLUser") & ";database=" & database)
  42. Else
  43.     getConnection = New SqlConnection("Data Source=" & _
  44.    ConfigurationManager.AppSettings("SQLserver") & ";Initial Catalog=" & _
  45.     database & ";Integrated Security=True")
  46. End If
  47. End Function
That's all there is to it!! Happy Coding!
Mar 10 '08 #1
Share this Article
Share on Google+
1 Comment


Sidewinder2
P: 7
hi,

could you please tell me where can i find the Excel.dll as a free download. i don't find Excel.dll as a free download!.

Thanks!
Sep 18 '08 #2