Data Table To Excel - CSharp Office

CSharp examples for Office:Excel

  1. HOME
  2. CSharp
  3. Office
  4. Excel

Description

Data Table To Excel

Demo Code


using System.Collections.Generic;
using System.Text.RegularExpressions;
using System.Web;
using System.Drawing;
using System.Collections;
using System.Text;
using System.Data;
using System.IO;/*from w  w w.  j ava 2 s . c  o  m*/
using System;
using System.Data.OleDb;

public class Main{
        public static void DataTableToExcel(System.Data.DataView dataview,string Path)
      {
         try
         {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();  
            System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
            cmd.Connection = conn;

            string strSql = string.Empty ,strSql1 = string.Empty ;
            int i , j ;

            for(  i = 0 ; i < dataview.Count ; i++ )
            {
               
               strSql = "INSERT INTO [sheet1$] (";
               strSql1 = ") values(";
               for(  j = 0 ; j < dataview.Table.Columns.Count ; j++)
               {
                  strSql += dataview.Table.Columns[j].ColumnName +"," ;  
                  strSql1 += "'" +dataview[i][j].ToString() + "',";
               }
         //      
               try
               {
                  if( strSql.EndsWith(",") ) 
                     strSql = strSql.Substring(0,strSql.Length - 1 ) ;
                  if( strSql1.EndsWith(",") ) 
                     strSql1 = strSql1.Substring(0,strSql1.Length - 1 ) ;
                  strSql1 = strSql1 + ")";
                  strSql = strSql + strSql1 ;
                  cmd.CommandText = strSql  ;
                  cmd.ExecuteNonQuery();
               }
               catch(Exception ex)
               {
                  System.Diagnostics.Debug.WriteLine ("???Excel????????"+ strSql + ex.Message );
                        throw new Exception(strSql + ex.Message);
               }
            }
            conn.Close ();
         }
         catch(System.Data.OleDb.OleDbException ex)
         {
            System.Diagnostics.Debug.WriteLine ("???Excel????????"+ex.Message );
         }
      }
      public static void DataTableToExcel(System.Data.DataView dataview,string Path,Hashtable NameMap)
      {
         try
         {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();  
            System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();
            cmd.Connection = conn;

            string strSql = string.Empty ,strSql1 = string.Empty ;
            int i , j ;

            for(  i = 0 ; i < dataview.Count ; i++ )
            {
               
               strSql = "INSERT INTO [sheet1$] (";
               strSql1 = ") values(";
               for(  j = 0 ; j < dataview.Table.Columns.Count ; j++)
               {
                  if( NameMap.ContainsKey( dataview.Table.Columns[j].ColumnName ) )
                  {
                     strSql += NameMap[dataview.Table.Columns[j].ColumnName] +"," ;  //2414210
                     strSql1 += "'" +dataview[i][j].ToString() + "',";
                  }
                  
               }
               
               try
               {
                  if( strSql.EndsWith(",") ) 
                     strSql = strSql.Substring(0,strSql.Length - 1 ) ;
                  if( strSql1.EndsWith(",") ) 
                     strSql1 = strSql1.Substring(0,strSql1.Length - 1 ) ;

                  strSql1 = strSql1 + ")";
                  strSql = strSql + strSql1 ;

                  cmd.CommandText = strSql ;
                  cmd.ExecuteNonQuery();
               }
               catch(Exception ex)
               {
                  System.Diagnostics.Debug.WriteLine (strSql + strSql1 + ex.Message );
                        throw new Exception(strSql + ex.Message);
               }
            }
            conn.Close ();
         }
         catch(System.Data.OleDb.OleDbException ex)
         {
            System.Diagnostics.Debug.WriteLine (ex.Message );
         }
      }
}

Related Tutorials