How to create an Excel file using a database query in VB.Net? -


I would like to generate an excel file using the database query in VB.Net. How can I do this?

To be more accurate: I would like to "bind" a query (like binding a query in a grid view) in an excel file, such as the row in the table capturing the relevant cell in a new Excel file Do it, and save the file to your computer, and then send it to someone.

While I can handle the mailing part, it is the creation of such a file that I need help, anyone know what I want to achieve?

PS: I need to do this in VBNET and I am using SQL Server 2008.

OK This is not correct, but you should start it. First of all you would like to add a reference to the version that uses Excel. In my case it is 12.0 (2007), but this code should work with the previous two or three versions in which there is slight change or two.

Import

Import 

  Public Function CreateTable) DataTable dim cn New SqlConnection (My.Settings.con) using the slow cmd New SqlCommand as new as the new SqlDataAdapter (as in DT) as the new DataTable () cmd.CommandType = CommandType.StoredProcedure cmd .CommandText = "[dbo]. [MyStoredProcedure]" cmd.CommandTimeout = 0 cn.Open () cmd.Connection = cn da.SelectCommand = cmd da.Fill (DT) cn.Close () Return using DT Second end function   
 code to take that dataable Dump it in further and in Excel  
  Public Shared Sub PopulateSheet (ByVal DT DataTable, as in the form of ByVal file string) Dim oXL as Excel.Application = CTYPE (CreateObject ("Excel.Application"), Excel.Application) as the slow OWB Excel.Workbook dim oSheet Excel.Worksheet dim oRng Excel.Range oXL.Visible = true OWB = oXL.Workbooks.Add oSheet = CTYPE (oWB.ActiveSheet, Excel.Worksheet) Dim Dc DataColumn Dynamic as DataRow dim colIndex as in the form of an integer = 0 dormant rowIndex as integer = 0 dt.Columns colIndex = colIndex + 1 oXL.Cells ( 1, co LIndex) = dc.ColumnName dt.Rows rowIndex for every Dr. next = rowIndex + 1 colIndex = 0 dt.Columns for each DC colIndex = colIndex + 1 oXL.Cells (rowIndex + 1, colIndex) = Dr (dc .ColumnName) Next Next oSheet.Cells.Select () oSheet.Columns.AutoFit () oSheet.Rows.AutoFit in each DC () OXL.Visible = True oXL.UserControl = True oWB.SaveAs (file) ORNG = None OXL.Quit (ExcelCleanUp) (oXL, oWB, oSheet) End Sub   
 Now you can call this one button or whatever you choose from was S  Try the DMT DT as new datatile = CreateTable () PopulateSheet (dt, "c : \ Test \ ExcelFile.xlsx ") Hold as the Exchange Message Box box. (Formerly message) Finally Dt.Dispose () End Try   
 Now it's really original, but with a little work you can do cell formatting, page setup and anything else Which with menu / options in Excel  
 We should also complete this by adding code to clean it.  
  Personal shared sub ExcelCleanUp (_ Excel as Excel, Excel bylv, o byWal oWL as Excel, worksheet, virtual OSCE as Excel, worksheet) GC. Collect () GC VATfording Finishers () Marshall.FinelRelicom Object (OXL) Marshall. Finlayrello.com Object (OSAT) Marshall Final Release Comm Object (OWB) OSAT = None OWB = Nothing Oxail = None Sub

Comments

Post a Comment