SQL database to Excel

[piscis]

Need to find an example of how to export a small SQL database to Excel? Can this be done?

Thanks all

[JanZuk]

The easiest way is to do it from enterprise manager (export data) or query analyzer (choose "SAVE AS" from the results window).

Are you using sql server or MDBE?

Does this have to be done programmatically?

[piscis]

JanZuk:

I'm using an SQL database. And yes it must be done programmatically.

[JanZuk]

Quick way of getting dataTable rows into an excel spreadsheet

Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook
Dim excelWorksheet As Excel.Worksheet
 
excelBook = excelApp.Workbooks.Add
excelWorksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
 
Dim i As Integer = 1
Dim dr As DataRow
 
For Each dr In dt.Rows
      excelWorksheet.Range("A" & i.ToString).Value = dr("Row1")
      excelWorksheet.Range("B" & i.ToString).Value = dr("Row2")
      i += 1
Next

EDIT: I have a reference to the Microsoft Excel 9.0 Object Library

[piscis]

I will test it right away and will post back. Thanks JanZuk.

[JanZuk]

Cool.

Once upon a time I was looking for a way to print my data from a datagrid and the example I found was to first convert it to an excel spreadsheet and then print that spreadsheet. This is that code (slightly condensed).

[piscis]

You have given me a great idea. Instead of exporting directly from the database, blindly to the user, I will show the data first in a DataGridView and then export on a button.click event.

Does the code changes somehow?

Thanks

[JanZuk]

You have given me a great idea. Instead of exporting directly from the database, blindly to the user, I will show the data first in a DataGridView and then export on a button.click event.

Does the code changes somehow?

Thanks

All you would be changing is the datasource in the for each loop (using the datagrid's datasource instead). In your click event, instead of

For Each dr In dt.Rows

call

For Each dr In (CType(dg1.DataSource, DataTable)).Rows

EDIT: to print out the excel spreadsheet just add excelWorksheet.PrintOut()

[piscis]

Do I need to import any system namespace? I’m getting these errors:

[piscis]

And this:

[piscis]

I tried adding a reference to the Microsoft Excel 9.0 Object Library but in VB.NET 2005 we only have these two and they both return errors.

Microsoft Excel 5.0 Object Library
Microsoft Excel 11.0 Object Library

any idea?

[JanZuk]

1) try the 11.0 object library. Shouldn't be *that* different (knock on wood).
2) try importing from System.Data or use Data.DataTable/Data.DataRow

[piscis]

Did it, 2 errors only:

[JanZuk]

Instead of excelApp being of type system.aplicationid, can you make it of type Microsoft.Office.Interop.Excel.Application?

[piscis]

OK, it seems like we rae in a good track: see this

[piscis]

AND this:

[piscis]

Don't bother, I did not read you post as I should have.

Thanks for your help.

[piscis]

One last thing ran the button click event and got an Invalid Cast exception:

[evad4682]

I have also tried this code out and recieved the Invalid Cast execption error. Does anybody know how to fix this. Thank you very much.

[piscis]

I just order VB2005 Pro from Amazon, they will ship on Dec 2. I believe this version comes with Crystal Report. If so then I do not need to export to Excel any longer, I could just do the report and export to PDF.

That is if Crystal Report does allow to export to PDF.(I will post this question, just to make sure.)