/// <summary>

/// 将SQLServer中的数据导出到Excel（使用OleDB）

/// </summary>

/// <param name="ExelDt">要导出的数据集</param>

/// <param name="filePath">输出到的文件目录</param>

/// <returns>信息</returns>

public static string SQLServerToExcel(System.Data.DataTable ExelDt, string filePath)

{

    if (ExelDt == null)

    {

        return "数据不能为空";

    }

    //数据集的行总数、列总数

    int rows = ExelDt.Rows.Count;

    int cols = ExelDt.Columns.Count;

    if (rows == 0)

    {

        return "没有数据";

    }

 

    StringBuilder sb = new StringBuilder();

    string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";

    OleDbCommand cmd = new OleDbCommand();

    OleDbConnection myConn = new OleDbConnection(strCon);

    try

    {

        //创建文件

        myConn.Open();

        //创建表

        cmd.Connection = myConn;

        sb.Append("create table ");

        sb.Append(ExelDt.TableName + "(");

        for (int i = 0; i < cols; i++)

        {

            if (i < cols - 1)

            {

                sb.Append(string.Format("{0} varchar,", ExelDt.Columns[i].ColumnName));

            }

            else

            {

                sb.Append(string.Format("{0} varchar)", ExelDt.Columns[i].ColumnName));

            }

        }

        cmd.CommandText = sb.ToString();

        cmd.ExecuteNonQuery();

    }

    catch (Exception ex)

    {

        return "建立Exel文件失败:" + ex.ToString();

    }

 

    //---------------------------------------------------------------------------------

 

    //插入数据

    sb.Remove(0, sb.Length);

 

    sb.Append("INSERT INTO ");

    sb.Append(ExelDt.TableName + " ( ");

 

    for (int i = 0; i < cols; i++)

    {

        if (i < cols - 1)

            sb.Append(ExelDt.Columns[i].ColumnName + ",");

        else

            sb.Append(ExelDt.Columns[i].ColumnName + ") values (");

    }

 

    for (int i = 0; i < cols; i++)

    {

        if (i < cols - 1)

            sb.Append("@" + ExelDt.Columns[i].ColumnName + ",");

        else

            sb.Append("@" + ExelDt.Columns[i].ColumnName + ")");

    }

    cmd.CommandText = sb.ToString();

    OleDbParameterCollection param = cmd.Parameters;

 

    for (int i = 0; i < cols; i++)

    {

        param.Add(new OleDbParameter("@" + ExelDt.Columns[i].ColumnName, OleDbType.VarChar));

    }

 

     //遍历DataTable将数据插入新建的Excel文件中

    foreach (DataRow row in ExelDt.Rows)

    {

        for (int i = 0; i < param.Count; i++)

        {

            param[i].Value = row[i];

        }

 

        cmd.ExecuteNonQuery();

    }

 

    cmd.Connection.Close();

 

    return "数据已成功导入Excel";

}