Export data to Excel

[SVA]

Hello,

Is there a simple way to export data from a datagrid to an Excel sheet without using XML?

 

[cjard]

Erm.. I want to answer this with:

Yes, using an XML transform


Because I know I can do it in about 3 lines of code, which (to me) is very simple. However, youre stating that you do not want to use XML, so I dont know; is that because the route you think you have to follow in xml is hard, or what?

Decide; if you want, I'll give you the codes to export a dataset to Excel, using XMLT, and 3 lines of vb.

 

[SVA]

TXS cjard, that would be helpfull.

The reason is that some users still have Office 2000 (and W2k) and others 2003 & XP. I just want to make a generic solution....

 

[mohammedhakkim]

Csv

dear SVA

one easy method is to write the data in the data grid to a csv file. so that any version of excel can open it.

if needed i can give u the code for that

 

[SVA]

That would be very helpfull mohammedhakkim!!

 

[mohammedhakkim]

exporting datagrid to csv

dim ds as dataset= ctype(datagrid.datasource,dataset)
dim str as new StringBuilder()
dim fo as new io.streamwriter("path\file.xls")
for icounter as integer=0 to ds.tables(0).rows.count-1
for jcounter as interger=0 to ds.tables[0].columns.count-1
 
str.Append(ds.Tables(0).Rows(icounter).item(jcounter))
if jocounter<>ds.tables[0].columns.count-1 then
str.append(",")
end if
next
fo.writeline(str.tostring())
 
next
 
fo.close

 

[cjard]

Nooo.... dont even think of writing a CSV file that way..

As soon as your data contains acomma, the file is broken..

Search these forums for the word "ExcelableDataSet" and use that code..

 

[mohammedhakkim]

yes cjard i accept it by using the above method file can be broken.
thanks for the advice

but SVA requires a simple solutuion where he dont want any dependency on excel or xml
if we need to write a CSV in such a way and data has a comma what can be done?

 

[cjard]

He only declined XML because he thought it would be hard.. THere are good reasons for avoiding CSV; for a start, its not really an Excel format, it just happens that Excel can read it.
Numbers dont store well in CSV; excel tries to parse them out
Special characters in the data will break the CSV structure

All in, its just not good; the effort required to cater for all the special cases is not worth it! VB.NET does have the ability to read a csv file, maybe it also has the ability to write it.. but (IMHO) it's moot in the face of the simple code I gave for ExcelableDataSet; that's the easiest way of turning a dataset into an excel file..

if you didnt find it when you searched, here is the link:
http://www.vbdotnetforums.com/showthread.php?t=17555&highlight=excelabledataset

 

[mohammedhakkim]

thanks cjard
i got it. i am investigating it. thanks for the detailed reply

 

[cjard]

If you werent an XML fan before.. you might just read about the XML transforms, say "Hey.. that's cool!" and start loving it