C#导入Excel数据

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using test.App_Code;
using System.Text.RegularExpressions;
using System.IO;

namespace test
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                DataTable dt = Common.GetList(1);
                ddCity.DataSource = dt;
                ddCity.DataValueField = "CityId";
                ddCity.DataTextField = "CityName";

                ddCity.DataBind();
                ddCity.Items.Insert(0, new ListItem("请选择城市", "0"));
            }
        }

        public DataSet ExcelToDS(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [" + RegexHelper.ReplaceStr(txtSheetName.Text.Trim()) + "$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            try
            {
                myCommand.Fill(ds, "table1");



                int CityId = int.Parse(ddCity.SelectedValue.Trim());
                int CountyId = int.Parse(ddCounty.SelectedValue.Trim());
                int drType = int.Parse(ddDrType.SelectedValue.Trim());
                //for (int j = 0; j < ds.Tables[0].Rows.Count && !string.IsNullOrEmpty(ds.Tables[0].Rows[j][0].ToString().Trim()); j++)
                //{
                //    for (int i = 0; i < 4; i++)
                //    {
                //        Response.Write(ds.Tables[0].Columns[i].ColumnName + "   :  " + ds.Tables[0].Rows[j][i].ToString().Trim()); Response.Write("<br>");
                //    }

                //    Response.Write(ds.Tables[0].Columns[4].ColumnName + "   :  " + GetStringByRegex(new Regex(@"\d{11}|\(?\d{3,4}\)?-\d*"), ds.Tables[0].Rows[j][4].ToString().Trim())); Response.Write("<br>");  //电话号码
                //    Response.Write(ds.Tables[0].Columns[5].ColumnName + "   :  " + GetStringByRegex(new Regex(@"\d{6,}"), ds.Tables[0].Rows[j][5].ToString().Trim())); Response.Write("<br>"); //Qq
                //    Response.Write(ds.Tables[0].Columns[6].ColumnName + "   :  " + GetStringByRegex(new Regex(@"(([0-9a-zA-Z]+)|([0-9a-zA-Z]+[_.0-9a-zA-Z-]*))@([a-zA-Z0-9-]+[.])+(net|com|gov|mil|org|edu|int|name|asia|[a-zA-Z]{2})"), ds.Tables[0].Rows[j][6].ToString().Trim())); Response.Write("<br>");
                //    Response.Write("summary" + "   :  "+ ItemIsNull(ds.Tables[0].Rows[j][7].ToString().Trim())); Response.Write("<br>");
                //}
                writeToTxt("=============" + DateTime.Now.ToString() + "===" + txtFileName.Text.Trim() + "===" + txtSheetName.Text.Trim() + "============");
                for (int j = 0; j < ds.Tables[0].Rows.Count && !string.IsNullOrEmpty(ds.Tables[0].Rows[j][0].ToString().Trim()); j++)
                {
                    string CompanyName = RegexHelper.ReplaceStr(ds.Tables[0].Rows[j][0].ToString().Trim());
                    string addr = RegexHelper.ReplaceStr(ds.Tables[0].Rows[j][1].ToString().Trim());
                    string business = 
                           //RegexHelper.ReplaceStr(ds.Tables[0].Rows[j][2].ToString().Trim());
                           RegexHelper.ReplaceStr(GetStringByRegex(new Regex(@"\S*"), ds.Tables[0].Rows[j][2].ToString().Trim(), "\r\n", 100));
                    string Contact = RegexHelper.ReplaceStr(GetStringByRegex(new Regex(@"\S*"), ds.Tables[0].Rows[j][3].ToString().Trim(),",",4));
                    string phone = RegexHelper.ReplaceStr(GetStringByRegex(new Regex(@"\d{11}|\(?\d{3,4}\)?-\d*|\d*"), ds.Tables[0].Rows[j][4].ToString().Trim(), ",", 4)); //电话号码
                    string qq = RegexHelper.ReplaceStr(GetStringByRegex(new Regex(@"\d{6,}"), ds.Tables[0].Rows[j][5].ToString().Trim(), ",", 4));//Qq
                    string email = RegexHelper.ReplaceStr(GetStringByRegex(new Regex(@"(([0-9a-zA-Z]+)|([0-9a-zA-Z]+[_.0-9a-zA-Z-]*))@([a-zA-Z0-9-]+[.])+(net|com|gov|mil|org|edu|int|name|asia|[a-zA-Z]{2})"), ds.Tables[0].Rows[j][6].ToString().Trim(), ",", 1));
                    string summary = 
                        //RegexHelper.ReplaceStr(ItemIsNull(ds.Tables[0].Rows[j][7].ToString().Trim()));
                        RegexHelper.ReplaceStr(GetStringByRegex(new Regex(@"\S*"), ds.Tables[0].Rows[j][7].ToString().Trim(), "\r\n", 100));
                    string userName = RegexHelper.ReplaceStr(GetRadomUserName(10));
                    string pwd = PassWordHelper.MD5String(GetRadomPwd(6));
                    int cpyNameExists = Common.CompanyNameExists(CompanyName);
                    int emailExists = 0;
                    if (!string.IsNullOrEmpty(email) && Common.EmailExists(email) == 1) { emailExists = 1; }

                    if (cpyNameExists == 1) { Response.Write(CompanyName + " 已被注册<br>"); writeToTxt(CompanyName + " 已被注册"); }
                    if (cpyNameExists == 2) { Response.Write(CompanyName + " 已被录入<br>"); writeToTxt(CompanyName + " 已被录入"); }
                    if (emailExists == 1) { Response.Write(CompanyName + ":" + email + " 已存在<br>"); writeToTxt(CompanyName + ":" + email + " 已存在"); }

                    if (cpyNameExists == 0 && emailExists == 0)
                    {
                        int userInfoId = Common.userInfoAdd(userName, pwd, "127.0.0.1");
                        if (userInfoId > 0)
                        {
                            int i = Common.Add(userInfoId, CityId, CountyId, CompanyName, addr, Contact, phone, business, qq, summary, email, drType);
                        }
                    }
                }
                return ds;
            }
            catch (Exception e)
            {
                Response.Write(e.Message + "<br><br>");
                return null;
            }

        }

        protected void ddCity_SelectedIndexChanged(object sender, EventArgs e)
        {
            DataTable dt = Common.GetCountyList(int.Parse(ddCity.SelectedValue.Trim()));
            ddCounty.DataSource = dt;
            ddCounty.DataValueField = "CountyId";
            ddCounty.DataTextField = "CountyName";
            ddCounty.DataBind();
            ddCounty.Items.Insert(0, new ListItem("请选择县区", "0"));
        }

        protected void btnOk_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(txtFileName.Text.Trim()))
            {
                if (ExcelToDS(Server.MapPath("/Companys/" + txtFileName.Text.Trim())) != null)
                {
                    Response.Write("<script>alert('导入完毕！');</script>");
                }
            }
            else { }
        }

        /// <summary>
        /// 根据正则获取字符串
        /// </summary>
        /// <param name="reg"></param>
        /// <param name="drValue"></param>
        /// <returns></returns>
        public string GetStringByRegex(Regex reg, string drValue, string strSplit, int _count)
        {
            int count = 0;
            MatchCollection mc = reg.Matches(drValue);
            string returnVal = string.Empty;
            foreach (Match m in mc)
            {
                if (m.Value != "" && count < _count)
                { returnVal = returnVal + m.Value + strSplit; count++; }
            }
            if (returnVal.Length != 0)
            {
                return returnVal.Substring(0, returnVal.Length - 1);
            }
            return string.Empty;
        }

        public string ItemIsNull(string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                return "暂无";
            }
            return value;
        }

        /// <summary>
        /// 获取随机字符串
        /// </summary>
        /// <param name="Length"></param>
        /// <returns></returns>
        public string GetRadomUserName(int Length)
        {
            char[] constant = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z' };
            System.Text.StringBuilder newRandom = new System.Text.StringBuilder(62);
            Random rd = new Random();
            for (int i = 0; i < Length; i++)
            {
                newRandom.Append(constant[rd.Next(62)]);
            }
            string username = newRandom.ToString();
            if (Common.UserNameExists(RegexHelper.ReplaceStr(username)) == 0)
            {
                return newRandom.ToString().ToLower();
            }
            else { return GetRadomUserName(Length); }
        }

        public string GetRadomPwd(int length)
        {
            char[] constant = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' };
            System.Text.StringBuilder newRandom = new System.Text.StringBuilder(10);
            Random rd = new Random();
            for (int i = 0; i < length; i++)
            {
                newRandom.Append(constant[rd.Next(10)]);
            }
            return newRandom.ToString();
        }

        public void writeToTxt(string value)
        {
            FileStream fs3 = new FileStream("E:\\log.txt", FileMode.Append);
            StreamWriter sw3 = new StreamWriter(fs3, System.Text.Encoding.GetEncoding("gb2312"));
            sw3.WriteLine(value + "\r\n");
            sw3.Close();
            fs3.Close();
        }
    }
}