首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 网站开发 > Web开发 >

可以倒入导出execl的demo

2012-12-17 
求一个可以倒入导出execl的demo求一个可以连接数据库倒入导出的execl功能[最优解释]把excel当作数据库来操

求一个可以倒入导出execl的demo
求一个可以连接数据库倒入导出的execl功能
[最优解释]
把excel当作数据库来操作,就变的简单了。
使用OleDb,让Excel操作与数据库一样简单

namespace GuideInto
{
    public class DAL_File
    {
        public static string FilePath
        {
            get;
            set;
        }

        /// <summary>
        /// 连接Excel
        /// </summary>
        /// <returns></returns>
        public static OleDbConnection Conn()
        {
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(connStr);

            return conn;
        }

        /// <summary>
        /// 读取所有工作薄
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAllExeclWorkBook()
        {
            using (OleDbConnection conn = Conn())
            {
                conn.Open();
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                return dt;
            }
        }

        /// <summary>
        /// 根据Sql读取Excel中的数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet Query(string sql)
        {
            using (OleDbConnection conn = Conn())
            {
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);


                DataSet ds = new DataSet();
                da.Fill(ds, "ds");
                return ds;
            }
        }

        /// <summary>
        /// 读取指定工作薄中的数据
        /// </summary>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public static DataTable GetList(string sheetname)
        {
            string sql = "select * from [" + sheetname + "]";

            return Query(sql).Tables[0];
        }

        /// <summary>
        /// 判断表指定工作薄是否存在
        /// </summary>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public static bool Exists(string sheetname)
        {
            try
            {
                string sql = "select top 1 * from [" + sheetname + "]";

                Query(sql);
                return true;
            }
            catch (OleDbException ex)
            {
                if (ex.Message.Contains("不存在"))
                    return false;
            }
            return false;
        }

        /// <summary>
        /// 删除execl中指定工作薄
        /// </summary>
        /// <param name="sheetname"></param>
        public static void DropSheet(string sheetname)
        {
            using (OleDbConnection conn = Conn())


            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    conn.Open();
                    cmd.Connection = conn;
                    cmd.CommandText = "drop table " + sheetname;
                    cmd.CommandType = CommandType.Text;

                    cmd.ExecuteNonQuery();
                }
            }
        }

[其他解释]

        /// <summary>
        /// 插入数据到工作薄
        /// </summary>
        /// <param name="dt"></param>
        public static void AddDateToSheet(DataTable dt, ProgressBar bar, Label lb)
        {
            using (OleDbConnection conn = Conn())
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {

                        OleDbCommand cmd = new OleDbCommand();
                        StringBuilder strSql;
                        if (dt.Rows.Count > 1)
                        {
                            bar.Minimum = 1;
                            bar.Maximum = dt.Rows.Count;


                            lb.Visible = bar.Visible = true;
                        }
                        for (int j = 0; j < dt.Rows.Count; j++)
                        {
                            if (j <= bar.Maximum)
                            {
                                bar.Value = j + 1;
                                lb.Text = (bar.Value * 100 / bar.Maximum).ToString() + "%";
                            }
                            Application.DoEvents();

                            cmd.Transaction = trans;
                            cmd.Connection = conn;
                            cmd.CommandType = CommandType.Text;

                            strSql = new StringBuilder();
                            strSql.Append("insert into " + dt.TableName + "(");

                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                if (i != dt.Columns.Count - 1)
                                    strSql.Append("[" + dt.Columns[i].ColumnName + "],");


                                else
                                    strSql.Append("[" + dt.Columns[i].ColumnName + "]");
                            }

                            strSql.Append(") values (");
                            string value;
                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                value = dt.Rows[j][i].ToString() == "" ? "null" : dt.Rows[j][i].ToString();
                                if (!string.IsNullOrEmpty(value))
                                {
                                    value = value.Replace("'", "''");
                                }

                                if (GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i != dt.Columns.Count - 1 && value.Equals("null"))
                                    strSql.Append(value + ",");
                                else if (GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i == dt.Columns.Count - 1 && value.Equals("null"))
                                    strSql.Append(value);
                                else if (GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i != dt.Columns.Count - 1)


                                    strSql.Append("'" + value + "',");
                                else if (GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i == dt.Columns.Count - 1)
                                    strSql.Append("'" + value + "'");
                                else if (!GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i != dt.Columns.Count - 1)
                                    strSql.Append(value + ",");
                                else if (!GetSheetTypeByInt(dt.Columns[i].DataType.Name) && i == dt.Columns.Count - 1)
                                    strSql.Append(value);
                            }
                            strSql.Append(")");

                            cmd.CommandText = strSql.ToString();
                            cmd.ExecuteNonQuery();
                        }
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                }


            }
        }

        //创建Execl工作薄
        public static void CreateExecl(DataTable dt)
        {
            using (OleDbConnection conn = Conn())
            {
                conn.Open();

                using (OleDbCommand cmd = new OleDbCommand())
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("create table " + dt.TableName + "(");

                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        if (i == dt.Columns.Count - 1)
                            strSql.Append("[" + dt.Columns[i].ColumnName + "] " + ReturnExeclType(dt.Columns[i].DataType.Name) + " null");
                        else

                            strSql.Append("[" + dt.Columns[i].ColumnName + "] " + ReturnExeclType(dt.Columns[i].DataType.Name) + " null,");
                    }
                    strSql.Append(")");

                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = strSql.ToString();
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// Net数据类型与Excel中的数据类型转换


        /// </summary>
        /// <param name="SqlType"></param>
        /// <returns></returns>
        private static string ReturnExeclType(string SqlType)
        {
            switch (SqlType.ToLower())
            {
                case "string":
                    //case "smalldatetime":
                    //case "datetime":
                    return "nvarchar(255)";
                case "byte":
                    return "byte";
                case "int32":
                    return "int";
                case "decimal":
                    return "numeric";
                case "tinyint":
                    return "byte";
                case "smalldatetime":
                    return "datetime";
                case "boolean":
                    return "bit";
                default:
                    return SqlType;
            }
        }

        /// <summary>
        /// Excel中的数据类型与Net数据类型转换
        /// </summary>
        /// <param name="SqlType"></param>
        /// <returns></returns>
        private static bool GetSheetTypeByInt(string sheettype)
        {
            switch (sheettype.ToLower())
            {
                case "string":


                    return true;
                case "smalldatetime":
                    return true;
                case "datetime":
                    return true;
                default:
                    return false;
            }
        }
    }


[其他解释]
有没有用poi做的导出啊

热点排行