Wrox Programmer Forums
|
Pro VB.NET 2002/2003 For advanced Visual Basic coders working .NET version 2002/2003. Beginning-level questions will be redirected to other forums, including Beginning VB.NET.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB.NET 2002/2003 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 10th, 2005, 11:26 AM
Registered User
 
Join Date: May 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default vb.net and excel - HELP!

I have written some vb code (in an aspx page) to query a database and output the results in an excel sheet (.xls). It works fine and generates a .xls sheet with my data in it. But now I want to be able to sort the sheet using the top headers.
I used DataTable.Column.Add(...) to add the headers and rows.add to add the rows.
But I want my generated xls sheet to have a dropdown on top of each column that filters the sheet to show only those that have the value that is selected.
I know I can do it manually but I want to know how to generate the xls sheet with this already in it.

for example lets say I have xls sheet like below:

aa | bb
--------
11 | 23
22 | 13
22 | 23
33 | 34
33 | 14
44 | 34

I want to have a dropdown in the top column cells aa and bb that let me chose any value of 11, 22, 33, and 44 and when chosen for example 22 the list would shrink to:

aa | bb
--------
22 | 13
22 | 23

How can I code this in my aspx page using vb to generate this xls sheet?

thanx

 
Old May 10th, 2005, 03:36 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

The XLS export is reading the HTML generated by the control output. Because that nifty Excel filtering functionality is part of Excel, I don't think there is a way to get the HTML to output enough stuff so the Excel will do that. You'd have to actually generate an XSL file with the cell fomulas/stuff in it to make the column header filters.

-Peter
 
Old May 10th, 2005, 04:03 PM
Registered User
 
Join Date: May 2005
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How do I go about writing the xsl file? I've never done that!

 
Old May 10th, 2005, 09:38 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

I've never done it, but you should be able to reference the variables COM assemblies that excel uses and interact directly with excel functionality. Also, there are "Visual Studio Tools for Office". Something in that might give you a better way to generate excel worksheets.

-Peter





Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel reports in vb.net juster21 .NET Framework 2.0 1 March 3rd, 2014 03:53 AM
Working with Excel in VB.net spkalman Visual Basic 2008 Essentials 11 December 6th, 2013 04:01 AM
Writing to Excel from a dataset with VB.Net ruairi VB.NET 2002/2003 Basics 5 July 27th, 2009 12:07 PM
Import Excel to VB.Net stealthdevil Visual Basic 2005 Basics 4 June 6th, 2007 01:10 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.