export datatable to excel

  • Thread starter Thread starter cj2
  • Start date Start date
C

cj2

#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 or SQL Server Business
Intelligence Development Studio or whatever it's called?
 
I should have made this clearer. I mean a datatable in VB.net.

dim mydt as new datatable
myodbcdataadapter.fill(mydt)

do some processing then export to excel.
 
Hello Chris,

Thanks for using Microsoft Newsgroup Support Service, my name is Ji Zhou
[MSFT] and I will be working on this issue with you.

As to your first question, we have the following three solutions to export
the data from a datatable into an Excel file:

1.Using Office automation as Gillard's code shows. In this approach, each
row in the datatable will cost one Excel COM server call. So the speed is a
little slow.

2.We can put the datatable in a System.Web.UI.WebControls.DataGrid control
and use the DataGrid's RenderControl function to export the data into an
Excel file. There is a sample article about this approach in code project:
http://www.codeproject.com/KB/office/ExcelDataTable.aspx

3.We take the Excel file as a database and use OleDbConnection to connect to
it. After that, we can update our datatable to the Excel file database. The
following Microsoft KB article dedicate to this method:
http://support.microsoft.com/kb/316934

As to the second question, we have the managed newsgroup
microsoft.public.sqlserver.reportingsvcs that targets at issues about SQL
Reporting services.

If you have any future questions, please feel free to let me know.


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
In the codeproject article it refers to System.Web.UI.WebControls.DataGrid()
this doesn't seem to exist in VB2008 or VB2005.

How do I reference this?


Ji Zhou said:
Hello Chris,

Thanks for using Microsoft Newsgroup Support Service, my name is Ji Zhou
[MSFT] and I will be working on this issue with you.

As to your first question, we have the following three solutions to
export the data from a datatable into an Excel file:

1.Using Office automation as Gillard's code shows. In this approach,
each row in the datatable will cost one Excel COM server call. So the
speed is a little slow.

2.We can put the datatable in a System.Web.UI.WebControls.DataGrid
control and use the DataGrid's RenderControl function to export the data
into an Excel file. There is a sample article about this approach in
code project: http://www.codeproject.com/KB/office/ExcelDataTable.aspx

3.We take the Excel file as a database and use OleDbConnection to
connect to it. After that, we can update our datatable to the Excel file
database. The following Microsoft KB article dedicate to this method:
http://support.microsoft.com/kb/316934

As to the second question, we have the managed newsgroup
microsoft.public.sqlserver.reportingsvcs that targets at issues about
SQL Reporting services.

If you have any future questions, please feel free to let me know.


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments
and suggestions about how we can improve the support we provide to you.
Please feel free to let my manager know what you think of the level of
service provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each
follow up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach
the most efficient resolution. The offering is not appropriate for
situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.



cj2 said:
#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 or SQL Server Business
Intelligence Development Studio or whatever it's called?
Click to expand...
Click to expand...
 
try
System.Windows.Forms.DataGridView



cj2 said:
In the codeproject article it refers to
System.Web.UI.WebControls.DataGrid()
this doesn't seem to exist in VB2008 or VB2005.

How do I reference this?


Ji Zhou said:
Hello Chris,

Thanks for using Microsoft Newsgroup Support Service, my name is Ji Zhou
[MSFT] and I will be working on this issue with you.

As to your first question, we have the following three solutions to
export the data from a datatable into an Excel file:

1.Using Office automation as Gillard's code shows. In this approach, each
row in the datatable will cost one Excel COM server call. So the speed is
a little slow.

2.We can put the datatable in a System.Web.UI.WebControls.DataGrid
control and use the DataGrid's RenderControl function to export the data
into an Excel file. There is a sample article about this approach in code
project: http://www.codeproject.com/KB/office/ExcelDataTable.aspx

3.We take the Excel file as a database and use OleDbConnection to connect
to it. After that, we can update our datatable to the Excel file
database. The following Microsoft KB article dedicate to this method:
http://support.microsoft.com/kb/316934

As to the second question, we have the managed newsgroup
microsoft.public.sqlserver.reportingsvcs that targets at issues about SQL
Reporting services.

If you have any future questions, please feel free to let me know.


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you.
Please feel free to let my manager know what you think of the level of
service provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues where an initial response from the community or a Microsoft
Support
Engineer within 1 business day is acceptable. Please note that each
follow up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.



cj2 said:
#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 or SQL Server Business
Intelligence Development Studio or whatever it's called?
Click to expand...
Click to expand...
Click to expand...
 
I can try but I understood from the article that only the web grid
allowed exporting to excel.
try
System.Windows.Forms.DataGridView



cj2 said:
In the codeproject article it refers to
System.Web.UI.WebControls.DataGrid()
this doesn't seem to exist in VB2008 or VB2005.

How do I reference this?


Ji Zhou said:
Hello Chris,

Thanks for using Microsoft Newsgroup Support Service, my name is Ji
Zhou [MSFT] and I will be working on this issue with you.

As to your first question, we have the following three solutions to
export the data from a datatable into an Excel file:

1.Using Office automation as Gillard's code shows. In this approach,
each row in the datatable will cost one Excel COM server call. So the
speed is a little slow.

2.We can put the datatable in a System.Web.UI.WebControls.DataGrid
control and use the DataGrid's RenderControl function to export the
data into an Excel file. There is a sample article about this
approach in code project:
http://www.codeproject.com/KB/office/ExcelDataTable.aspx

3.We take the Excel file as a database and use OleDbConnection to
connect to it. After that, we can update our datatable to the Excel
file database. The following Microsoft KB article dedicate to this
method: http://support.microsoft.com/kb/316934

As to the second question, we have the managed newsgroup
microsoft.public.sqlserver.reportingsvcs that targets at issues about
SQL Reporting services.

If you have any future questions, please feel free to let me know.


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments
and suggestions about how we can improve the support we provide to
you. Please feel free to let my manager know what you think of the
level of service provided. You can send feedback directly to my
manager at: (e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.


Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues where an initial response from the community or a Microsoft
Support
Engineer within 1 business day is acceptable. Please note that each
follow up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach
the most efficient resolution. The offering is not appropriate for
situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.



#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 or SQL Server Business
Intelligence Development Studio or whatever it's called?
Click to expand...
Click to expand...
Click to expand...
 
I have a class to do the job
it is in attachment new.vb

cj2 said:
I can try but I understood from the article that only the web grid allowed
exporting to excel.
try
System.Windows.Forms.DataGridView



cj2 said:
In the codeproject article it refers to
System.Web.UI.WebControls.DataGrid()
this doesn't seem to exist in VB2008 or VB2005.

How do I reference this?


:
Hello Chris,

Thanks for using Microsoft Newsgroup Support Service, my name is Ji
Zhou [MSFT] and I will be working on this issue with you.

As to your first question, we have the following three solutions to
export the data from a datatable into an Excel file:

1.Using Office automation as Gillard's code shows. In this approach,
each row in the datatable will cost one Excel COM server call. So the
speed is a little slow.

2.We can put the datatable in a System.Web.UI.WebControls.DataGrid
control and use the DataGrid's RenderControl function to export the
data into an Excel file. There is a sample article about this approach
in code project:
http://www.codeproject.com/KB/office/ExcelDataTable.aspx

3.We take the Excel file as a database and use OleDbConnection to
connect to it. After that, we can update our datatable to the Excel
file database. The following Microsoft KB article dedicate to this
method: http://support.microsoft.com/kb/316934

As to the second question, we have the managed newsgroup
microsoft.public.sqlserver.reportingsvcs that targets at issues about
SQL Reporting services.

If you have any future questions, please feel free to let me know.


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments
and suggestions about how we can improve the support we provide to you.
Please feel free to let my manager know what you think of the level of
service provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues where an initial response from the community or a Microsoft
Support
Engineer within 1 business day is acceptable. Please note that each
follow up response may take approximately 2 business days as the
support
professional working with you may need further investigation to reach
the most efficient resolution. The offering is not appropriate for
situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.



#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 or SQL Server Business
Intelligence Development Studio or whatever it's called?
Click to expand...
Click to expand...
Click to expand...
 
Hello Chris,

If we want to use the System.Web.UI.WebControls in the console or windows
form VB.NET application. We need to add a reference to System.Web assembly.
To reference this assembly in VB.NET 2008, we open the Solution Explorer
Window, right click our project and choose Add Reference option. Then, the
Add Reference dialog pops up. In the .NET tab, we find and select the
System.Web, and click the OK button. Afterwards, we can use the
System.Web.UI.WebControls.DataGrid.

Please let me know if you have future questions or concerns on this. I will
be more than glad to provide future help!


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
 
What is the difference between grid.datasource and grid.datamember?

I found since I am using a datatable to hold the info I want in an excel
spreadsheet that I can do grid.datasource=mydt and don't have to use
datamember. But I'd like to know why.
 
Hello Chris,

The DataGrid.DataMember is inherited from the BasedDataList class. We can
find the related document in this link:
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.basedatali
st.datamember.aspx.

The description for it is "Gets or sets the specific data member in a
multimember data source to bind to a data listing control." Thus, we can
know that this property is used only when the data source is a multimember
one.

1.If we use a DataTable to set the DataSource property. The data source is
single member, so we do not need the DataMember property in this case.

2.If we set a DataSet as the data source which is multimember, we can use
DataMember to specify which table in that dataset is supposed to be bound
to the control.

Hope this clarifies the issue. Please feel free to let me know if you have
future questions on this. I am glad to be of assistance.

Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Hello Chris,

Thanks for using Microsoft Newsgroup Support Service, my name is Ji Zhou
[MSFT] and I will be working on this issue with you.

As to your first question, we have the following three solutions to export
the data from a datatable into an Excel file:

1.Using Office automation as Gillard's code shows. In this approach, each
row in the datatable will cost one Excel COM server call. So the speed isa
little slow.

2.We can put the datatable in a System.Web.UI.WebControls.DataGrid control
and use the DataGrid's RenderControl function to export the data into an
Excel file. There is a sample article about this approach in code project:http://www.codeproject.com/KB/office/ExcelDataTable.aspx

3.We take the Excel file as a database and use OleDbConnection to connectto
it. After that, we can update our datatable to the Excel file database. The
following Microsoft KB article dedicate to this method:http://support.microsoft.com/kb/316934
Click to expand...

4. Use some 3rd party Excel RW component.

Our GemBox.Spreadsheet Free Excel component
(http://www.gemboxsoftware.com/GBSpreadsheetFree.htm)
can be used free of charge for that task,
as long as XLS/CSV/XLSX files are not large.
You have ExcelWorksheet.InsertDataTable method
(http://www.gemboxsoftware.com/help/
GemBox.Spreadsheet.ExcelWorksheet.InsertDataTable_overload_2.html)
which does exactly what you need.

--Zeljko
 
That makes sense however I would have expected to do something like this
with a dataset.

grid.datasource = myds.table("mytable")

I would have thought that they would have had only one datasource for
the gid.

P.S. I know this is off the subject but is it also possible to export
the grid to a CSV file? While adequate opening the HTML in excel
doesn't look like excel files normally look.
 
yes it is

Sub export2csv(ByVal dtgv As DataGridView)
Dim str As String = String.Empty
For x = 0 To dtgv.RowCount - 1
For y = 0 To dtgv.Columns.Count - 1
str = str & dtgv.Item(y, x).Value & ";"
Next
str = str.Remove(str.Length - 1, 1)
str = str & vbCrLf
Next
Dim sdf As New SaveFileDialog
sdf.Filter = "Coma Separated value(*.csv)|*.csv"
sdf.AddExtension = True
sdf.DefaultExt = "csv"
If sdf.ShowDialog = Windows.Forms.DialogResult.OK Then
My.Computer.FileSystem.WriteAllText(sdf.FileName, str, False)
End If
End Sub
 
Hello all,

Gillard, thanks for your posting! Yes, I totally agree your approach. It is
a good and easy way to export data from the datatable to a csv file. Only
one more thing to mention is that a string in .Net Framework is immutable.
That means it cannot be modified in the place. Any modifications to a
string will cause a new string is allocated. Afterwards, the value of
original one will copied into the new string. So, if there is a lot of data
in our datatable, we'd better use StringBuilder to get a better performance.

See the following KB article regarding to this topic,
http://support.microsoft.com/kb/306821

And Chris, please let us to know if this approach addresses your issue. If
you have any questions on this, I will do my best to provide the future
help.


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Thanks to both of you. Gillard, if you modify the code how about
posting the new code for me. I haven't worked with stringbuilder. I'm
sure I could figure it out but it'd be nice to see your code.

Now I must admit, I've been pulled to a new task this AM which I expect
will take awhile. So, I don't think I'll be working on this for awhile now.
ok thank you Ji Zhou
I will modify the code if i have a big datagrid
"Ji Zhou [MSFT]" said:
Hello all,

Gillard, thanks for your posting! Yes, I totally agree your approach.
It is
a good and easy way to export data from the datatable to a csv file. Only
one more thing to mention is that a string in .Net Framework is
immutable.
That means it cannot be modified in the place. Any modifications to a
string will cause a new string is allocated. Afterwards, the value of
original one will copied into the new string. So, if there is a lot of
data
in our datatable, we'd better use StringBuilder to get a better
performance.

See the following KB article regarding to this topic,
http://support.microsoft.com/kb/306821

And Chris, please let us to know if this approach addresses your
issue. If
you have any questions on this, I will do my best to provide the future
help.


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you.
Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no
rights.
Click to expand...
Click to expand...
 
I will write it when I will need it
not now
sorry for you pal

cj2 said:
Thanks to both of you. Gillard, if you modify the code how about posting
the new code for me. I haven't worked with stringbuilder. I'm sure I
could figure it out but it'd be nice to see your code.

Now I must admit, I've been pulled to a new task this AM which I expect
will take awhile. So, I don't think I'll be working on this for awhile
now.
ok thank you Ji Zhou
I will modify the code if i have a big datagrid
"Ji Zhou [MSFT]" said:
Hello all,

Gillard, thanks for your posting! Yes, I totally agree your approach. It
is
a good and easy way to export data from the datatable to a csv file.
Only
one more thing to mention is that a string in .Net Framework is
immutable.
That means it cannot be modified in the place. Any modifications to a
string will cause a new string is allocated. Afterwards, the value of
original one will copied into the new string. So, if there is a lot of
data
in our datatable, we'd better use StringBuilder to get a better
performance.

See the following KB article regarding to this topic,
http://support.microsoft.com/kb/306821

And Chris, please let us to know if this approach addresses your issue.
If
you have any questions on this, I will do my best to provide the future
help.


Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments
and
suggestions about how we can improve the support we provide to you.
Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no
rights.
Click to expand...
Click to expand...
Click to expand...
 
Hello Chris,

I used the following code to import my data table into the .csv file. I did
not use the String to cache the items. I just used stream writer to output
each item in the loop. See the following code,

Dim dt As DataTable = Me.NorthwindDataSet.Tables("Customers")
Using sw As New System.IO.StreamWriter("D:\test.csv")
For i = 0 To dt.Rows.Count - 1
For j = 0 To dt.Columns.Count - 1
sw.Write("""" + dt.Rows(i).Item(j) + """")
sw.Write(",")
Next
sw.Write(sw.NewLine)
Next
End Using

Please let me know if you have any future questions or concerns on this
issue, and I will do my best to research.

Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top