excel.cs

Excel导入MSSQL,MSSQL导出Excel使用方便,速度快,内带说明

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections;
using System.Text.RegularExpressions;
using System.Web;

namespace DF
{
    public class Excel
    {
        private string RepDate = "";
        private ArrayList imputList = null;
        private ArrayList imputType = null;
        /// <summary>
        /// 返回文件列表
        /// </summary>
        public ArrayList FileList = new ArrayList();
        private int numCol = 0;

        #region Excel连接
        /// <summary>
        /// Excel数据库连接语句,连接到导入的Excel文件.
        /// </summary>
        /// <param name="path">导入Excel 的路径</param>
        /// <returns>返加连接语句connStr</returns>
        string connStr(string path)
        { //aaa
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";

            //Provider=Microsoft.Jet.OLEDB.4.0;DataSource=" + path + ";" +
            //"Extended Properties=\"Excel 8.0;\"";//连接

            return connStr;
        }
        #endregion

        #region 返回Excel所有工作表名称
        /// <summary>
        /// 返回Excel第一个工作表名称
        /// </summary>
        /// <param name="Path">Excel路径</param>
        /// <returns>返回第一个工作表名称</returns>
        List<DataTable> ALLWorkSheetName(string Path)
        {
            List<DataTable> list = new List<DataTable>();
            string connstr = connStr(Path);
            OleDbConnection oconn = new OleDbConnection(connstr);
            try
            {
                if (oconn.State != ConnectionState.Open)
                {
                    oconn.Open();
                }
                DataTable dt = oconn.GetSchema("Tables");
                DataTableReader dtr = new DataTableReader(dt);
                while (dtr.Read())
                {
                    string sql = "select * from [" + dtr["Table_Name"].ToString() + "]";
                    DataSet ds = new DataSet();
                    OleDbDataAdapter da = new OleDbDataAdapter(sql, oconn);
                    da.Fill(ds);
                    if (ds != null)
                    {
                        list.Add(ds.Tables[0]);
                    }
                }
            }
            catch { }
            finally
            {
                if (oconn.State == ConnectionState.Open)
                {
                    oconn.Close();
                }
            }
            return list;
        }
        #endregion

        #region 保存上传文件
        /// <summary>
        /// 保存上传文件
        /// </summary>
        /// <param name="upPath">临时文件路径</param>
        public void SaveFiles(string upPath)
        {
            System.IO.DirectoryInfo dfs = new System.IO.DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath(upPath));
            if (!dfs.Exists)
            {
                dfs.Create();
            }
            for (int i = 0; i < System.Web.HttpContext.Current.Request.Files.Count; i++)
            {
                string filename = System.Web.HttpContext.Current.Request.Files[i].FileName.ToString();
                filename = filename.Substring(filename.LastIndexOf(@"\") + 1);
                string FileType = filename.Substring(filename.LastIndexOf(@".") + 1);
                if (System.Web.HttpContext.Current.Request.Files[i].ContentType == "application/vnd.ms-excel" && filename != "")
                {
                    string RndFiles = RndNum(15) + "." + FileType;
                    string mPath = upPath + RndFiles;
                    mPath = System.Web.HttpContext.Current.Server.MapPath(mPath);
                    System.Web.HttpContext.Current.Request.Files[i].SaveAs(mPath);
                    FileList.Add(mPath);
                }
            }
        }
        #endregion

        #region 生成随机唯一编号
        /// <summary>
        /// 生成随机唯一编号
        /// </summary>
        /// <param name="strLength">30位长度（年月日时分秒毫秒）</param>
        /// <returns>返回30位长度字符串</returns>
        string RndNum(int mlength)
        {
            //取出年月日时分秒……           
            string strSep = ",";
            char[] chrSep = strSep.ToCharArray();
            string strChar = "0,1,2,3,4,5,6,7,8,9";

            string[] aryChar = strChar.Split(chrSep, strChar.Length);

            string strRandom = string.Empty;
            Random Rnd = new Random();

            //生成随机字符串 
            for (int i = 0; i < mlength; i++)
            {
                strRandom += aryChar[Rnd.Next(aryChar.Length)];
            }
            return strRandom;
        }
        #endregion

        #region 设置字段及字段类型
        public void SetImputList(string List, string Type, string CutType)
        {
            if (List != null)
            {
                this.imputList = SplitStr(List, CutType);
                numCol = this.imputList.Count;
            }
            if (Type != null)
            {
                this.imputType =SplitStr(Type, CutType);
            }
        }
        #endregion

        #region 入口
        /// <summary>
        /// 程序入口
        /// </summary>
        /// <param name="Bao">要插入表</param>
        /// <param name="Path">电子表格路径</param>
        /// <param name="Tnum">从第几行读起</param>
        /// <param name="conn">已打开数据库</param>
        public void ExcelToMSSQL(string Bao, string Path, int CellNum,int RowNum,System.Data.SqlClient.SqlConnection conn)
        {
            //取表结构
            RepDate = System.DateTime.Now.ToString();
            List<DataTable> tList = new List<DataTable>();
            DataTable dt = null;
            tList = this.ExcelDataSet(Path, numCol);
            string AllSql = "";
            for (int i = 0; i < tList.Count; i++)
            {
                dt = (DataTable)tList[i];
                for (int nu = CellNum; nu < dt.Rows.Count; nu++)
                {
                    List<String> mList = new List<string>();
                    for (int mu = RowNum; mu < dt.Columns.Count; mu++)
                    {
                        mList.Add(dt.Rows[nu][mu].ToString());
                    }
                    AllSql += this.MakeSql(Bao, imputList, mList, imputType);

                }
            }
            if (AllSql != "")
            {
                SqlCommand cmd = new SqlCommand(AllSql,conn);
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }

        /// <summary>
        /// SQL数据导出到Excel表
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="PathFileName">Excel文件</param>
        /// <param name="ExcelHeader">Excel文件头部模板</param>
        /// <param name="ExcelFooter">Excel文件尾部模板</param>
        public void MSSQLToEXCEL(DataTable dt,String FileName, string ExcelHeader, string ExcelFooter)
        {
            string data = "";           
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ContentType = "application/vnd.xls";
            HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("gb2312");
            string path = System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString()+".xls";
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + path + "");
            ExcelHeader += "\r\n";
            HttpContext.Current.Response.Write(ExcelHeader);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {                   
                    data += dt.Rows[i][j].ToString() + "\t";
                }
                data += "\r\n";

                HttpContext.Current.Response.Write(data);
                data = "";
            }
            HttpContext.Current.Response.Write(ExcelFooter);
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
        }
        #endregion

        #region 生成ＳＱＬ词句
        string MakeSql(string Bao, ArrayList list, List<String> aList, ArrayList pList)
        {
            string Sql = "insert into " + Bao + "(";
            string Value = "values(";
            for (int i = 0; i < list.Count; i++)
            {
                if (list[i].ToString() == "AddTime")
                {
                    Sql += list[i].ToString() + ")";
                    Value += "'" + RepDate + "')";
                }
                else
                {
                    Sql += list[i].ToString() + ",";
                    if (pList[i].ToString() == "varchar")
                    {
                        Value += "'" + aList[i].ToString() + "',";
                    }
                    else if (pList[i].ToString() == "float")
                    {
                        Value += "" + StrToFloat(aList[i], 0) + ",";
                    }
                    else if (pList[i].ToString() == "bit")
                    {
                        Value += "" + StrToBool(aList[i], true) + ",";
                    }
                    else
                    { Value += "'" + ChkSQL(aList[i].ToString()) + "',"; }
                }
            }
            Sql = Sql + Value;
            return Sql;
        }
        #endregion

        #region 函数名(StrToInt)string型转换为int型
        /// <summary>
        /// string型转换为int型
        /// </summary>
        /// <param name="strValue">要转换的字符串</param>
        /// <param name="defValue">缺省值</param>
        /// <returns>转换后的int类型结果</returns>
        int StrToInt(object strValue, int defValue)
        {
            if ((strValue == null) || (strValue.ToString() == string.Empty) || (strValue.ToString().Length > 10))
            {
                return defValue;
            }

            string val = strValue.ToString();
            string firstletter = val[0].ToString();

            if (val.Length == 10 && IsNumber(firstletter) && int.Parse(firstletter) > 1)
            {
                return defValue;
            }
            else if (val.Length == 10 && !IsNumber(firstletter))
            {
                return defValue;
            }


            int intValue = defValue;
            if (strValue != null)