Export Excel to Datatable and Import Datatable to Excel

As for developers and programmers, nothing is more hotter than topics of Excel export and Excel import. In this post, I am very glad to introduce solutions of both excel to datatable and datatable to excel since I have collected many and want to share with all of you the most popular ones. Now, first let us see how to export Excel to datatable.

                     Export Excel to MS Acess and Sql Datatable

First Solution for Excel to Datatable through Datagridview

This solution is selected from the forum questions of codeproject:http://www.codeproject.com/Questions/376355/import-MS-Excel-to-datatable. Solution 6 is the most popular solution for excel to datatable task. After performing, this solution uses totally four lines of code to export excel to datatable. Among them two lines of key code: Workbook.LoadFromFile(string fileName) to load an excel file and DataTable ExportDataTable() to get or set the data source that the System.Windows.Forms.DataGridView is displaying data for. Before we view the whole code, please allow me to remind you that this solution needs a .NET Excel component. Using this library, you can create project of excel to datatable through both Windows Forms application and Console Application.

C#:

using Spire.Xls;
namespace Data_Export
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, System.EventArgs e)
        {
            //export excel data to datatable      
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"D:\michelle\my file\FandH.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            this.dataGridView1.DataSource = sheet.ExportDataTable();
            //connect with database table
            //connect with ms access
            string conn = "Provider=Microsoft.ACE.OLEDB.12.0;data Source=dataBase.accdb; Persist Security
Info=False;";
            //connect with sql datatable
            //string connectionStr = @"Data Source=DEV05\SQLEXPRESS;Initial Catalog=Sample; User Id=
mich;Password=123";
            SqlConnection conn= new SqlConnection(connectionStr);
            SqlCommand command = new SqlCommand();
            conn.Open();
            command.Connection = conn;
            for (int i=1;i<data.Rows.Count;i++)
            {
                DataRow row=data.Rows[i];
                string commd = "insert into [tableSample](Name,Capital,Continent,Area) values('" + row[0].ToString() + "','" + row[1].ToString() +
               " ','" + row[2].ToString() + "','" + row[3].ToString()+ "')";
                command.CommandText = commd;
                command.ExecuteNonQuery();
            }  
            conn.Close();
         }
      }
 }

You can preview the effect of excel to datatable as below picture:

 Second Solution for Excel to Datatable through Console Application
This solution of excel to datatable is from a blog(http://chanmingman.wordpress.com/2010/01/08/open-read-excel-content-in-c/). The code only works for Visual Studio 2008 and Excel 2007 up to Version 2012,. Compared with first solution, this solution is a little complex. It uses MS Office Interop. Please see below code:

C#:

static void ReadExcel(String strFileName)
 {
   Object Opt = Missing.Value;
   Microsoft.Office.Interop.Excel.Application app = new Application(); //= new Excel.Application();
   //Declare Workbook
   Microsoft.Office.Interop.Excel.Workbook book;
   //Excel.Worksheet sheet;
   Microsoft.Office.Interop.Excel.Worksheet xlsSheet;
   Microsoft.Office.Interop.Excel.Range oRng;
   //Open Spreadsheet
   book = app.Workbooks.Open("", Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt);
   //Take the first sheet
   xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets[1];
   //Read the first cell
   oRng = (Microsoft.Office.Interop.Excel.Range)xlsSheet.Cells[1, 1];
   String StrCell = oRng.Text.ToString();
   Console.WriteLine(StrCell);
   book.Close(false, false, Missing.Value);
 }

                          Import Databable to Excel

First solution for Datatable to Excel

Using the .NET Excel that I introduced, I can import datatable to Excel. In this solution, first we need to load data from system and insert into datagridview by Workbook.LoadFromFile(string fileName) and DataTable ExportDataTable(). Then, save the data to excel file format. Actually, for special needs, you also can set excel font and background color or background image etc.
C#:

using Spire.Xls;
namespace datatabletoexcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void Run_Click(object sender, EventArgs e)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            sheet.InsertDataTable((DataTable)this.dataGridView1.DataSource, true, 2, 1, -1, -1);
            workbook.SaveToFile(“sample.xls”);
            ExcelDocViewer( workbook.FileName );
        }
        private void Form1_Load(object sender, System.EventArgs e)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\michelle\my file\FandH.xls”);
            Worksheet sheet = workbook.Worksheets[0];
            this.dataGridView1.DataSource =  sheet.ExportDataTable();
        }
        private void ExcelDocViewer( string fileName )
        {
            try
            {
                System.Diagnostics.Process.Start(fileName);
            }
        catch{}
        }
    }
}

Below is the effective screenshot of the excel file:

Second solution for Datatable to Excel

This solution uses a .NET dataexport component Spire.DataExport, which is a sister data export component with Spire.Free DataExport. As for some simple data export task, you can use the free one. However, for some complex task such as export large amount of data, data with special charts etc, you had better use the former one. Both of them can export data from database, listview, command to excel, pdf, word, csv, html, xml, sylk, dif, dbf, SQL, SQL Script, Access, clipboard etc.

In this solution, after connected with database, we can directly connect the datatable with worksheet by stating the SQLCommand which is a property of Spire.DataExport.XLS.WorkSheet. Now let us see another property StartDataCol. By setting this property, we can choose the start column in worksheet. Finally, we can add the worksheet data to Excel sheet by calling WorkSheet.Add(WorkSheet Item) method. After setting all these properties, we can open the database by System.Data.OleDb.OleDbConnection.Open.

C#:


            System.Data.OleDb.OleDbConnection oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
            oleDbConnection1.ConnectionString = @”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=demo.mdb”;
            System.Data.OleDb.OleDbCommand oleDbCommand1 = new System.Data.OleDb.OleDbCommand();
            oleDbCommand1.CommandText = “select * from parts”;
            oleDbCommand1.Connection = oleDbConnection1;
            Spire.DataExport.XLS.CellExport cellExport1 = new Spire.DataExport.XLS.CellExport();
            Spire.DataExport.XLS.WorkSheet workSheet1 = new Spire.DataExport.XLS.WorkSheet();
            cellExport1.FileName = “toexcel1.xls”;
            workSheet1.SQLCommand = oleDbCommand1;
            workSheet1.StartDataCol = ((System.Byte)(0));
            cellExport1.Sheets.Add(workSheet1);
            oleDbConnection1.Open();
            try
            {
                cellExport1.SaveToFile();
                System.Diagnostics.Process.Start(“toexcel1.xls”);
            }
            finally
            {
                oleDbConnection1.Close();
            }
        }

Third Solution for Datatable to Excel

This solution actually includes 9 solutions for exporting data to excel task. Details can be seen:Solutions to Export Data From Database to Excel in C#. I think it is very useful for all of you.


Leave a Reply