?
Solved

export dataset to excel file

Posted on 2005-02-25
12
Medium Priority
?
2,334 Views
Last Modified: 2007-12-19
Hi,

Kindly help me export the dataset to a excel file and a user can choose which location he want the file to be place.

Please help me make it work.

Thanks.

            MyCommand = New SqlDataAdapter(strSQL, MyConnection)
            MyCommand.SelectCommand.CommandTimeout=0
            ds = new DataSet()
            MyCommand.Fill(ds, "DataSetName")

            MyDataGrid.DataSource=ds.Tables("DataSetName").DefaultView
            MyDataGrid.DataBind()
0
Comment
Question by:lynnton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 10

Accepted Solution

by:
NetDeveloper earned 2000 total points
ID: 13402471
Hi,

1. define a datagrid in ur page

2. in page load bind your datatable to the datagrid

3. define a button -> onclick insert this code:

        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.Charset = ""
        Me.EnableViewState = False
        Dim objStringWriter As System.IO.StringWriter = New System.IO.StringWriter
        Dim objHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(objStringWriter)
        yourdatagrid.RenderControl(objHtmlTextWriter)
        Response.Write(objStringWriter.ToString())
        Response.End()
0
 
LVL 1

Author Comment

by:lynnton
ID: 13402491
NetDeveloper,

Is this datagrid neccesary? since we need to export only..

Thanks.
0
 
LVL 10

Expert Comment

by:NetDeveloper
ID: 13402563
Hi lynnton , following my indicated approach an user can easily generate the excel file with the data contained in the dataset when he need it : he want the data in excel he clicks on a button and get the Excel

This is maybe more simple than looping ur datarows in ur datatabable and build the excel 'manually' by code...

it is an approach
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:saleek
ID: 13402596
Programmatically you can format your excel spreadsheet as you see fit tho. I.e. colors, fonts, print areas etc etc.
0
 
LVL 1

Author Comment

by:lynnton
ID: 13402639
NetDeveloper,

My apology, I'm lost, a user will click to export the data to excel then choose the path. What is the datagrid for ?

is it possible not to use one ? (yeah I don't like looping too)  :-)

Thanks.
0
 
LVL 10

Expert Comment

by:NetDeveloper
ID: 13402659
Yes, as saleek says , lets suppose u have a datagrid with a specific color for alternate row <AlternatingItemStyle></AlternatingItemStyle>
this style will be reflected in the generated .xls document too ...but this is true for about any format u apply to ur grid


0
 
LVL 10

Expert Comment

by:NetDeveloper
ID: 13402676
1. u bind the grid with ur data u indicated when page load (not pstaback)

2. in the event hand routine of a button insert that code

yourdatagrid is the ID of ur GRID
0
 
LVL 1

Author Comment

by:lynnton
ID: 13403177
NetDeveloper,

This is to cool to be true !!! Amazing abc done. You're the best!

I've made the grid not visible this removes the primary problem.

-How can we change the filename to system dateTime ?

-what will happen if it excedd the excel row limit 65K?

-how can we test a textbox1 for a date value ? return an error if it's not a date value..

Thanks so much.
0
 
LVL 10

Expert Comment

by:NetDeveloper
ID: 13403562
Hi,

- DateTime.Parse(urvalue) convert a value from string to DateTime...provided the value passed can be a date

- why you don't trim the string at DB level using TRIML and TRIMR sql functions?

- use a js or a regex ---search the web for such a control
0
 
LVL 1

Author Comment

by:lynnton
ID: 13403873
NetDeveloper,

Instead of naming the report "reports.xls" can we change it to generate dateTime value.xls ?

oh, excel has a max limit of 65K rows per sheet, what will happen if we exceed this limit?

Got it i'll research for regex for detecting if the inputed value is date format.

by the way, i've added alternate backcolor, when i export to excel the back color goes beyond the table..
i.e.
the raw data has 8 columns, when i open the excel the alternatining back color is there but it's like 20 columns long...

how can we fit to the columns only?

<p align="center">
            &nbsp;<asp:DataGrid id="Mydatagrid" runat="server" Font-Names="Arial">
                <HeaderStyle font-bold="True" backcolor="NavajoWhite"></HeaderStyle>
                <AlternatingItemStyle bordercolor="White" backcolor="LightSteelBlue"></AlternatingItemStyle>
            </asp:DataGrid>
        </p>
        <!-- Insert content here -->

Thanks.
0
 
LVL 10

Expert Comment

by:NetDeveloper
ID: 13404600
>the raw data has 8 columns, when i open the excel the alternatining back color is there but it's like 20 columns long...


Hi, tnx for the points...it happens to me to ...this evening when finishing with office work will try to see if I find something

Regards

D-
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month10 days, 21 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question