473,372 Members | 818 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,372 developers and data experts.

Export complete database to excel file

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
1 6646
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

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: Mike MacSween | last post by:
My client has an MS Access database application on her local machine. I have full access to that in terms of changing the design. I've got a simple PHP/MySql application on shared hosting, so no...
1
by: Matt | last post by:
I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table....
6
by: Robin Cushman | last post by:
Hi all, I need some help -- I'm working with an A2K database, using DAO, and am trying to read records into a Crystal Report and then export it to a folder on our network as an Excel...
5
by: Simon | last post by:
Dear reader, With the export command you can export a query to Excel. By activate this command a form pop's up with the following text:
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
7
Merlin1857
by: Merlin1857 | last post by:
Its great producing data for users to look at in your web pages and generally that is sufficient for their needs but sometimes you may want to supply your user with the data in a form they can...
3
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this...
19
by: cj2 | last post by:
#1 Is there a quick way to export a datatable to an excel file? Or delimited file? #2 Where is the appropriate Microsoft monitored group to ask about writing reports in SQL Reporting services...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.