asp.net导出数据到Excel的方法(完全无组件,不用调用Excel.exe)

来源：互联网发布：wordpress cms主题编辑：程序博客网时间：2024/06/05 20:09

using System;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace PowerAgent.DBUtility
{
/**//// <summary>
/// DataToExcel 的摘要说明。

    /// DataToExcel 的摘要说明。
    /// </summary>
    public class DataToExcel
    {
        const string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";

        public DataToExcel()
        {
        }

        public string DataTableToExcel(DataTable dt, string excelPath)
        {
            if (dt == null)
            {
                return "DataTable不能为空";
            }

            int rows = dt.Rows.Count;
            int cols = dt.Columns.Count;
            StringBuilder sb;
            string connString;

            if (rows == 0)
            {
                return "没有数据";
            }

sb = new StringBuilder();
connString = string.Format(ConnectionString, excelPath);

            //生成创建表的脚本
            sb.Append("CREATE TABLE ");
            sb.Append(dt.TableName + " ( ");

            for (int i = 0; i < cols; i++)
            {
                if (i < cols - 1)
                    sb.Append(string.Format("{0} varchar,", dt.Columns.ColumnName));
                else
                    sb.Append(string.Format("{0} varchar)", dt.Columns.ColumnName));
            }

            using (OleDbConnection objConn = new OleDbConnection(connString))
            {
                OleDbCommand objCmd = new OleDbCommand();
                objCmd.Connection = objConn;

objCmd.CommandText = sb.ToString();

                try
                {
                    objConn.Open();
                    objCmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    return "在Excel中创建表失败，错误信息：" + e.Message;
                }

                生成插入数据脚本#region 生成插入数据脚本
                sb.Remove(0, sb.Length);
                sb.Append("INSERT INTO ");
                sb.Append(dt.TableName + " ( ");

                for (int i = 0; i < cols; i++)
                {
                    if (i < cols - 1)
                        sb.Append(dt.Columns.ColumnName + ",");
                    else
                        sb.Append(dt.Columns.ColumnName + ") values (");
                }

                for (int i = 0; i < cols; i++)
                {
                    if (i < cols - 1)
                        sb.Append("@" + dt.Columns.ColumnName + ",");
                    else
                        sb.Append("@" + dt.Columns.ColumnName + ")");
                }
                #endregion

                //建立插入动作的Command
                objCmd.CommandText = sb.ToString();
                OleDbParameterCollection param = objCmd.Parameters;

                for (int i = 0; i < cols; i++)
                {
                    param.Add(new OleDbParameter("@" + dt.Columns.ColumnName, OleDbType.VarChar));
                }

                //遍历DataTable将数据插入新建的Excel文件中
                foreach (DataRow row in dt.Rows)
                {
                    for (int i = 0; i < param.Count; i++)
                    {
                        param.Value = row;
                    }

objCmd.ExecuteNonQuery();
}

                return "数据已成功导入Excel";
            }//end using
        }
    }//end class
}

-----C# 将数据导出到_execl汇总 http://blog.vsharing.com/jekkon/A1129596.html