Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2338
  • Last Modified:

export dataset to excel file

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
lynnton
Asked:
lynnton
1 Solution
 
NetDeveloperCommented:
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
 
lynntonAuthor Commented:
NetDeveloper,

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

Thanks.
0
 
NetDeveloperCommented:
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!

 
Ramesh SrinivasTechnical ConsultantCommented:
Programmatically you can format your excel spreadsheet as you see fit tho. I.e. colors, fonts, print areas etc etc.
0
 
lynntonAuthor Commented:
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
 
NetDeveloperCommented:
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
 
NetDeveloperCommented:
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
 
lynntonAuthor Commented:
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
 
NetDeveloperCommented:
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
 
lynntonAuthor Commented:
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
 
NetDeveloperCommented:
>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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now