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


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 13 of 13

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

Hybrid View

  1. #1
    Join Date
    Jan 2007
    Posts
    3

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

    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

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    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
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    May 2004
    Location
    Duluth MN
    Posts
    353
    Quote Originally Posted by sathya_k_83
    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?

    ...joe

  4. #4
    Join Date
    Jul 2009
    Posts
    2

    The easiest way to export the data

    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();

  5. #5
    Join Date
    May 2004
    Location
    Duluth MN
    Posts
    353
    This thread is a 2.5 years old.

    ...joe

  6. #6
    Join Date
    Jul 2009
    Posts
    2

    I noticed that after posting

    Oh well! Maybe somebody else can use the info.

  7. #7
    Join Date
    Feb 2010
    Posts
    4
    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
    Last edited by bvpavan; 02-10-2010 at 09:30 AM.

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    What errors are you getting?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  9. #9
    Join Date
    Feb 2010
    Posts
    4
    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
    Last edited by bvpavan; 02-10-2010 at 09:33 AM.

  10. #10
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    I think that means that it can't find your file. What do you have as a value for xmlFile?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  11. #11
    Join Date
    Feb 2010
    Posts
    4
    ok then what about xslRef

  12. #12
    Join Date
    Feb 2010
    Posts
    4
    i have value as D:\111.xlsx
    and i dont have any value in xslref

  13. #13
    Join Date
    Jan 2010
    Posts
    1
    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:

    Code:
    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]);

Similar Threads

  1. Replies: 6
    Last Post: 03-27-2010, 02:49 AM
  2. Replies: 1
    Last Post: 11-15-2006, 06:13 AM
  3. Can VB.NET peep into Windows OS for some data?
    By kurt@junsh in forum VB Classic
    Replies: 1
    Last Post: 05-25-2005, 02:10 AM
  4. Replies: 0
    Last Post: 09-28-2002, 04:00 PM
  5. Replies: 1
    Last Post: 09-26-2002, 08:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center