How to import data from DataSet to XLS spreadsheet with Spreadsheet SDK

These samples demonstrate how to import data from dataset to Excel spreadsheet in C# and Visual Basic .NET using Bytescout Spreadsheet SDK.

C#

using System.Data;
using System.Diagnostics;
using System.IO;
 
namespace Bytescout.Spreadsheet.Demo.Csharp.ImportFromDataSet
{
    class Program
    {
        static void Main(string[] args)
        {
            const string fileName = "CSharpImportFromDataSet.xls";
 
            // Create a new spreadsheet
            Spreadsheet spreadsheet = new Spreadsheet();
 
            // Get the data from the dataset that we wish to import
            DataSet periodicTableAndScientists = GetDataSet();
 
            // Import data into spreadheet
            spreadsheet.ImportFromDataSet(periodicTableAndScientists);
 
            // Save the spreadsheet
            if (File.Exists(fileName)) File.Delete(fileName);
            spreadsheet.SaveAs(fileName);
 
            // Close spreadsheet
            spreadsheet.Close();
 
            // Open the spreadsheet
            Process.Start(fileName);
        }
 
        /// <summary>
        /// Creates a data set of the periodic table of elements and some famous scientists
        /// </summary>
        /// <returns>A data set of the periodic table of elements and some scientists</returns>
        private static DataSet GetDataSet()
        {
            DataTable periodicTable = GetDataTableOfElements();
            DataTable scientists = GetDataTableOfScientists();
 
            DataSet dataset = new DataSet();
            dataset.Tables.Add(periodicTable);
            dataset.Tables.Add(scientists);
 
            return dataset;
        }
 
 
        /// <summary>
        /// Creates a data table of the periodic table of elements
        /// </summary>
        /// <returns>A data table of the periodic table of elements</returns>
        private static DataTable GetDataTableOfElements()
        {
            DataTable periodicTable = new DataTable("PeriodicTable");
 
            periodicTable.Columns.Add("Name", typeof(string));
            periodicTable.Columns.Add("Symbol", typeof(string));
            periodicTable.Columns.Add("AtomicNumber", typeof(int));
 
            DataRow dr = periodicTable.Rows.Add();
            dr[0] = "Hydrogen";
            dr[1] = "H";
            dr[2] = "1";
 
            dr = periodicTable.Rows.Add();
            dr[0] = "Helium";
            dr[1] = "He";
            dr[2] = "2";
 
            dr = periodicTable.Rows.Add();
            dr[0] = "Lithium";
            dr[1] = "Li";
            dr[2] = "3";
 
            dr = periodicTable.Rows.Add();
            dr[0] = "Beryllium";
            dr[1] = "Be";
            dr[2] = "4";
 
            dr = periodicTable.Rows.Add();
            dr[0] = "Boron";
            dr[1] = "B";
            dr[2] = "5";
 
            dr = periodicTable.Rows.Add();
            dr[0] = "Carbon";
            dr[1] = "C";
            dr[2] = "6";
 
            return periodicTable;
        }
 
        /// <summary>
        /// Creates a data table of scientists
        /// </summary>
        /// <returns>A data table of scientists</returns>
        private static DataTable GetDataTableOfScientists()
        {
            DataTable scientistsTable = new DataTable("Scientists");
 
            scientistsTable.Columns.Add("Name", typeof(string));
 
            DataRow dr = scientistsTable.Rows.Add();
            dr[0] = "Antoine Lavoisier";
 
            dr = scientistsTable.Rows.Add();
            dr[0] = "Julius Lothar Meyer ";
 
            dr = scientistsTable.Rows.Add();
            dr[0] = "Dmitri Ivanovich Mendeleev";
 
            return scientistsTable;
        }
    }
}

VB.NET

Module Module1
 
    Sub Main()
        Dim inputFile As String = "PeriodicTableOfElementsSpreadsheet.xls"
 
        'Open and load spreadsheet
        Dim spreadsheet = New Spreadsheet()
        spreadsheet.LoadFromFile(inputFile)
 
        'Get the data from the spreadsheet
        Dim ds As DataSet = spreadsheet.ExportToDataSet()
 
        'Close spreadsheet
        spreadsheet.Close()
 
        'Display data in first datatable of dataset
        Dim dt As DataTable = ds.Tables(0)
        Console.WriteLine("Displaying contents of first datatable")
        For i As Integer = 0 To dt.Rows.Count - 1
            For j As Integer = 0 To dt.Columns.Count - 1
 
                Console.Write(dt.Rows(i)(j) + " ")
            Next
            Console.WriteLine()
        Next
 
        'Pause
        Console.ReadLine()
    End Sub
 
End Module
Filed in: Spreadsheet SDK

Tutorials: