.Exporting data from Dataset to Excel sheet in VB.net Windows Application & vice ver

[sathya_k_83]

How to Export data from Dataset to Excel sheet in VB.net Windows Application
and also How to convert Excel Sheets into Data set Using VB.net coding(Windows Application)

how to Convert Dataset into XL Sheet to the VB.net Windows Application only by using the default resource(dll) available in VB.net.

ie without need to add Office Primary Interop Assemblies of the Microsoft Excel 2003 or without the need for installing the Microsoft Office.

We are developing a Product using VB.net (Windows Application). After Developing the Product , we need to install it in different Location which may or may not have Microsoft office.
So is it possible to write coding in VB.net to convert a data set into Excel sheet & vice versa by using the Normal features of VB.net instead of the need to Install Microsoft Office in the Client System adding reference to PIA*in the VB.net Application


The Below are some method/ideas by which data set con be converted into Excel Sheet but it is not suitable for our company.
so we are searching
for another Idea / Method which does not require the installation of Microsoft Office

Method 1:
One way is to Office Primary Interop Assemblies in the Microsoft Excel 2003.
1) By adding .net Programmability Support to the Microsoft Excel 2003.

2) When you want to use the Excel 2003 PIAs in a .NET-based application, add a reference to them in the Add Reference dialog box of Visual Studio .NET.
3) Once you add this reference, you can manipulate the Excel COM objects as though they were native .NET-based assemblies.
4) VB.net Coding can be Used to Convert the Dataset into XL Sheets.
Refer :http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

few other methods are
1.http://www.dotnetspider.com/kb/Article950.aspx
2.http://support.microsoft.com/default...;EN-US;Q318373
3.http://weblogs.asp.net/donxml/archiv.../21/24908.aspx
4.http://www.dotnetjohn.com/articles.aspx?articleid=78
5.http://support.microsoft.com/default...;EN-US;Q317109

[pclement]

You can also use data access methods. I believe at least one of the links demonstrates how to do this. Here is another:

How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

[joewmaki]

ie without need to add Office Primary Interop Assemblies of the Microsoft Excel 2003 or without the need for installing the Microsoft Office.

We are developing a Product using VB.net (Windows Application). After Developing the Product , we need to install it in different Location which may or may not have Microsoft office.
So is it possible to write coding in VB.net to convert a data set into Excel sheet & vice versa by using the Normal features of VB.net instead of the need to Install Microsoft Office in the Client System adding reference to PIA*in the VB.net Application

Just to make sure I understand, you want to be able to read and write to excel spreadsheets on a computer that does not have excel installed on it and without referencing the PIA, correct?

[lanisoftware]

would be to do it to either tab or comma delimited file. Excel and other spreadsheets are able to read comma delimited files. Alternatively, would be to use an xsl template and export the data out as a xml file.

Look under System.Xml for various functions you could use.

Here's a sample using C# code, but the class and function names are the same for VB.Net

DataSet ds; // dataset you want to export out to the file
//Create FileStream
XmlDataDocument xmlDoc = new System.Xml.XmlDataDocument(ds);
XslCompiledTransform xslTran = new System.Xml.Xsl.XslCompiledTransform();

//Create XmlTextWriter for the FileSteam
FileStream fs = new System.IO.FileStream(xmlFile, System.IO.FileMode.Create);
XmlTextWriter xtw = new System.Xml.XmlTextWriter(fs,System.Text.Encoding.Unicode);

//Add processing instructions to the beginning of the XML file,
// one of which indicates a style sheet.
xtw.WriteProcessingInstruction("xml","version='1.0'");
strXSLFilename = "test.xsl";
xtw.WriteProcessingInstruction("xml-stylesheet","type='text/xsl' href='" + xslRef + "'");

//Write the XML from the dataset to the file
ds.WriteXml(xtw);
xtw.Close();

[joewmaki]

This thread is a 2.5 years old.

[lanisoftware]

Oh well! Maybe somebody else can use the info.

[bvpavan]

iam getting errors..where i have to
creat xmlFile--- FileStream fs = new FileStream(xmlFile, FileMode.Create);
and xslref in ---
xtw.WriteProcessingInstruction("xml-stylesheet", "type='text/xsl' href='" + xslRef + "'");

iam new to .net..i have a requirement that selected rows in datagrid have to save in excel file..

if know please tell me how to export to excel

thanks in advance

[Hack]

What errors are you getting?

[bvpavan]

unknown xmlFile,xslref..my requirement is i have to create some random numberd excel file and sheet and i have to save my datagrid in to that sheet

[Hack]

I think that means that it can't find your file. What do you have as a value for xmlFile?

[bvpavan]

ok then what about xslRef

[bvpavan]

i have value as D:\111.xlsx
and i dont have any value in xslref

[cikaPero]

Hi,

you can also try GemBox.Spreadsheet Excel .NET library, it supports Excel to DataTable import/export within just one method call.

Here is a little more complicated example of Excel import to DataTable where Excel and DataTable datatype differs:

var ef = new ExcelFile();
ef.LoadCsv("FileName.csv", CsvType.CommaDelimited);

// Initialize DataTable (skip this if you have DataTable definition)
DataTable dt = new DataTable();
dt.Columns.Add("time1", typeof(DateTime));
dt.Columns.Add("time2", typeof(DateTime));

var ws = ef.Worksheets[0];
// Manage ExtractDataError.WrongType error
ws.ExtractDataEvent += (sender, e) =>
{
	if (e.ErrorID == ExtractDataError.WrongType)
	{
		if (e.Mapping.DataSetColumn == "time1" || e.Mapping.DataSetColumn == "time2")
		{
			e.DataTableValue = DateTime.Parse(e.ExcelValue);
			e.Action = ExtractDataEventAction.Continue;
		}
	}
};

// Extract data to DataTable
ws.ExtractToDataTable(dt, 1000, ExtractDataOptions.StopAtFirstEmptyRow, ws.Rows[0], ws.Columns[0]);