Export to Excel

[kristof]

Hi,

I'm looking into different ways to export data to an Excel sheet. Speed and editability are the main priorities.

Anyone have any links/tips/suggestions, so I can look into it and decide which is best for me?

Thanks

PS: Not asking for code at this point, just ways to do it.

 

[Bruce1965]

How about ...

Instead of exporting to excel, get excel itself to import from database ??

 

[kristof]

That would limit the adaptability of how the export is done, as it would be set to a single template. That would mean the Excel sheet would contain options that I wouldn't want to have in there in some cases. And I don't want to have several template Excel files.

 

[kristof]

Also, the data doesn't necessarily come from DB alone.

 

[dace]

I generally use the Excel COM object to create/edit Excel docs. It gives you full control over it, and most of Excel's functionality is exposed. As for speed and reliability - even large manipulations are generally moderately fast (runs in less than user-time), and if coded correctly such that it releases references to files, etc. I've never heard of it failing.

 

[SqueakinSweep]

I agree with Dace, although I have also used Component One's Excel class control, which Im sure is just a wrapper around the COM object.

It really just depends on the level of control you want. With the COM object, you control the number of sheets, and can point to individual rows and cells, to change values and styles.

Just be sure to include try/catch loops on all file io, especially when testing, as you're guaranteed to leave the files open in Excel view mode, when you're also writing them in your .Net app.



Sweep
...if it works dont mess with it

 

[mariomoreira]

Hi

I' also trying to export data from a sql database to a Excel file but i still cant get there... i've tied diferent ways but i think the most (nearly) correct is this one!! but the sentence inside the double cycle isn't correct!! Please somebody help me!!

here his the code


Sub createFile()

Dim dsExcelExport As New dSet
Dim daExcelExport As SqlClient.SqlDataAdapter

Dim Excel As New Excel.Application
Dim intColumn, intRow, intColumnValue As Integer
Dim strExcelFile As String
Dim strFileName As String
Dim dt As New DataTable 'define new data table for temporary strorage

daExcelExport = New SqlClient.SqlDataAdapter("SELECT * FROM tBjf(nolock)", cnn)
daExcelExport.Fill(dsExcelExport)
dt = DS.Tables("tBjf")
Dim strAppPath = System.Reflection.Assembly.GetExecutingAssembly.Location.Substring(0, System.Reflection.Assembly.GetExecutingAssembly.Location.LastIndexOf("\") + 1)
With Excel
.SheetsInNewWorkbook = 3
.Workbooks.Add()
.Worksheets(1).Select()

'For displaying the column name in the the excel file.
For intColumn = 0 To dsExcelExport.Tables(0).Columns.Count - 1
.Cells(1, intColumn + 1).Value = dsExcelExport.Tables(0).Columns(intColumn).ColumnName.ToString
Next

'For displaying the column value row-by-row in the the excel file.
For intRow = 1 To dsExcelExport.tBjf.Rows.Count - 1
For intColumnValue = 1 To dsExcelExport.tBjf.Columns.Count - 1
.Cells(intRow + 1, intColumnValue + 1).Value = dsExcelExport.Tables(0).Rows
Next
Next
'i think the error is inside the cycle above


'save Excel File

strFileName = InputBox("Please enter the file name.", "FOCUS")
strExcelFile = strAppPath & strFileName
.ActiveWorkbook().SaveAs(strExcelFile)
.ActiveWorkbook.Close()
End With
MessageBox.Show("File exported sucessfully.", "Exporting done", MessageBoxButtons.OK, MessageBoxIcon.Information)
NormalExit:
Excel.Quit()
Excel = Nothing
GC.Collect()
Exit Sub
End Sub



this is a exemple that i found into he internet! but i still can't get it how to write thi in the correct way to fill up the file with the data from databank

Thanks for your help, and your time!!!

 

[MickTheBelgian]

If you don't mind everything in one sheet, and you don't want to depend on Excel COM, then just export an ascii file and give it extension CSV. Use ; as separator between cells and format your doubles as "0000000E00". (That way you don't get , or . in your numbers, and it equally imports well in Europe (, as decimal separator) and in the US(. as decimal separator).

 

[mariomoreira]

That could be a good idea, but i'm a roocky and i didn't understand, what you meen with that!!!
I think this is very simple, but i cant understand why doesn't the
.Cells(intRow + 1, intColumnValue + 1).Value = dsExcelExport.Tables(0).Rows

doesn't work!!!!!! the problem must be in here, i've already tried other ways, but it doesn't work at all

but thanks any way