把DataTable导出为纯Excle文件

    //把DataTable导出为纯Excle文件
    //参数：DataTable, 文件服务器端物理全路径，每个sheet最多行数，Excel2003及以前版本最多允许6万5千块。
    public static bool ExportToExcel(DataTable dt, string filename, int RowsPerSheet)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
        strConn = strConn + filename + ";";
        strConn = strConn + "Extended Properties=Excel 8.0;";

        string strSql = "";
        System.Data.OleDb.OleDbConnection conn = null;
        int intSheet = 0;
        int intCounts = 0;
        try
        {
            conn = new System.Data.OleDb.OleDbConnection(strConn);
            conn.Open();

            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
            cmd.Connection = conn;

            System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter(cmd);
            oda.InsertCommand = cmd;

            DataTable dtTmp = dt.Clone();

            string strColDef = "";
            foreach (DataColumn dc in dt.Columns)
            {
                if (dc.DataType == typeof(Decimal))
                    strColDef += (strColDef.Equals("") ? "" : ",") + "[" + dc.ColumnName + "]" + " Numeric";
                else if (dc.DataType == typeof(DateTime))
                    strColDef += (strColDef.Equals("") ? "" : ",") + "[" + dc.ColumnName + "]" + " DateTime";
                else
                    strColDef += (strColDef.Equals("") ? "" : ",") + "[" + dc.ColumnName + "]" + " VarChar";
            }

            foreach (DataRow dr in dt.Rows)
            {
                if (intCounts == 0)
                {
                    #region add Excel sheet

                    cmd.Parameters.Clear();

                    //新增Excel工作表
                    intSheet += 1;
                    strSql = "Create Table [Sheet" + intSheet.ToString() + "]";
                    strSql += " (" + strColDef + ") ";
                    cmd.CommandText = strSql;
                    cmd.ExecuteNonQuery();

                    #endregion

                    #region Insert SQL

                    oda.InsertCommand.Parameters.Clear();
                    //Insert SQL
                    strSql = "";
                    foreach (DataColumn dc in dt.Columns)
                    {
                        strSql += (strSql.Equals("") ? "Insert Into [Sheet" + intSheet.ToString() + "$] Values(" : ",") + "?";

                        if (dc.DataType == typeof(Decimal))
                            oda.InsertCommand.Parameters.Add("@" + dc.ColumnName, System.Data.OleDb.OleDbType.Numeric);
                        else if (dc.DataType == typeof(DateTime))
                            oda.InsertCommand.Parameters.Add("@" + dc.ColumnName, System.Data.OleDb.OleDbType.Date);
                        else
                            oda.InsertCommand.Parameters.Add("@" + dc.ColumnName, System.Data.OleDb.OleDbType.VarChar);

                        oda.InsertCommand.Parameters["@" + dc.ColumnName].SourceColumn = dc.ColumnName;
                    }
                    strSql += ")";

                    oda.InsertCommand.CommandText = strSql;

                    #endregion
                }

                dtTmp.Rows.Add(dr.ItemArray);
                intCounts += 1;

                if (intCounts == RowsPerSheet)
                {
                    intCounts = 0;
                    oda.Update(dtTmp); //Insert Data to excel
                    dtTmp.Rows.Clear();
                }

            }

            if (dtTmp.Rows.Count > 0)
                oda.Update(dtTmp);  //Insert Data to excel

            return true;

        }
        catch (Exception ex)
        {
            return false;
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }

    public static bool ExportToExcel(DataSet ds, string filename, bool sheetNmaeIsTableName)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
        strConn = strConn + filename + ";";
        strConn = strConn + "Extended Properties=Excel 8.0;";

        string strSql = "";
        System.Data.OleDb.OleDbConnection conn = null;
        int intSheet = 0;
        string strSheetName = "";

        try
        {
            conn = new System.Data.OleDb.OleDbConnection(strConn);
            conn.Open();

            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
            cmd.Connection = conn;

            System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter(cmd);
            oda.InsertCommand = cmd;

            foreach (DataTable dt in ds.Tables)
            {

                DataTable dtTmp = dt.Clone();

                string strColDef = "";
                foreach (DataColumn dc in dt.Columns)
                {
                    if (dc.DataType == typeof(Decimal))
                        strColDef += (strColDef.Equals("") ? "" : ",") + "[" + dc.ColumnName + "]" + " Numeric";
                    else if (dc.DataType == typeof(DateTime))
                        strColDef += (strColDef.Equals("") ? "" : ",") + "[" + dc.ColumnName + "]" + " DateTime";
                    else
                        strColDef += (strColDef.Equals("") ? "" : ",") + "[" + dc.ColumnName + "]" + " VarChar";
                }

                #region add Excel sheet

                cmd.Parameters.Clear();

                //新增Excel工作表
                if (sheetNmaeIsTableName)
                {
                    strSql = "Create Table [" + dt.TableName + "]";
                    strSheetName = dt.TableName;
                }
                else
                {
                    intSheet += 1;
                    strSql = "Create Table [Sheet" + intSheet.ToString() + "]";
                    strSheetName = "Sheet" + intSheet.ToString();
                }
                strSql += " (" + strColDef + ") ";
                cmd.CommandText = strSql;
                cmd.ExecuteNonQuery();

                #endregion

                #region Insert SQL

                oda.InsertCommand.Parameters.Clear();
                //Insert SQL
                strSql = "";
                foreach (DataColumn dc in dt.Columns)
                {
                    strSql += (strSql.Equals("") ? "Insert Into [" + strSheetName + "$] Values(" : ",") + "?";

                    if (dc.DataType == typeof(Decimal))
                        oda.InsertCommand.Parameters.Add("@" + dc.ColumnName, System.Data.OleDb.OleDbType.Numeric);
                    else if (dc.DataType == typeof(DateTime))
                        oda.InsertCommand.Parameters.Add("@" + dc.ColumnName, System.Data.OleDb.OleDbType.Date);
                    else
                        oda.InsertCommand.Parameters.Add("@" + dc.ColumnName, System.Data.OleDb.OleDbType.VarChar);

                    oda.InsertCommand.Parameters["@" + dc.ColumnName].SourceColumn = dc.ColumnName;
                }
                strSql += ")";

                oda.InsertCommand.CommandText = strSql;

                #endregion

                foreach (DataRow dr in dt.Rows)
                {
                    dtTmp.Rows.Add(dr.ItemArray);
                }

                if (dtTmp.Rows.Count > 0)
                    oda.Update(dtTmp);  //Insert Data to excel
            }

            return true;

        }
        catch (Exception ex)
        {
            return false;
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }
        }
    }