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.Text;
using CRMSL.DBL;
using CRMSL.Common;

using System.Data.SqlClient;
using System.Globalization;

public partial class _Import : BasePage
{
    CrmDB db = new CrmDB();
    protected string Aname
    {
        get { return Request.QueryString["Aname"] ?? string.Empty; }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        Users.CheckLogin();
        Users.CheckSingleAction("010805", "100");
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        try
        {
            //上传文件
            FileHelper file = new FileHelper();
            file.SaveDirectory = "io/FaWen";
            file.SupportType = ".xls.xlsx";
            file.SaveName = string.Format("{0}_{1}_{2}"
                , Aname, "发文"
                , DateTime.Now.ToString("yyyyMMdd.HHmmss")
                );
            // 上传失败     
            var result = file.Upload(fileInput.PostedFile);
            if (result == false)
            {
                lblMsg.Text = string.Format("上传文件失败：{0}", file.ErrorMessage);
                return;
            }
            // 分析数据
            string fullPath = file.UploadedFullPath;
            ExcelHelper excel = new ExcelHelper();
            string sheetName = excel.GetExcelFirstSheetName(fullPath);
            DataTable dt = excel.ExcelToDataSet(fullPath, sheetName).Tables[0];
            if (dt.Rows.Count == 0)
            {
                Script.Alert("Excel内容不能为空！");
                return;
            }
			//判断EXcel表是否存有以下列名
            if (!dt.Columns.Contains("string1")
                || !dt.Columns.Contains("string2")
                || !dt.Columns.Contains("Sring3")
                || !dt.Columns.Contains("string4")
                || !dt.Columns.Contains("dateTime1")
                || !dt.Columns.Contains("dateTime2"))
            {
                Script.Alert("Excel中的格式不正确，请下载正确的模板再导入！");
                return;
            }

            //校验excel中存在完全一致的数据
            DataView view = new DataView(dt);
            DataTable dt2 = view.ToTable(true);
            int dtrow = dt.Rows.Count;
            int dtrow2 = dt2.Rows.Count;
            if (dtrow > dtrow2)
            {
                Script.Alert("导入模板数据有误，请检查!");
                return;
            }

            BatchUpdate(dt);
        }
        catch (Exception ex)

        {
            Script.Alert(ex.Message);
        }
}

    //导入
    #region BatchUpdate
    private static Dictionary<string, string> DicColumns = new Dictionary<string, string>()
    {
        { "string1","SQLstring1"},
        { "string2","SQLstring2"},
        { "string3","SQLroomsort3"},
        { "string4","SQLstring4" },
        { "dateTime1","SQldateTime11"},
        { "dateTime2","SQldateTime12"},
    };
    /// <summary>
    /// 发文
    /// </summary>
    /// <param name="btnText">操作动作</param>
    /// <param name="excel_dt">Eccel数据源</param>
    /// <param name="isMuch">是否多条</param>
    private void BatchUpdate(DataTable excel_dt)
    {
        //A表与B表的数据集合
        List<DispatchInfo> FaWen = new List<DispatchInfo>();
        #region 获取A表与B的数据集合
        //获取A的string1
        string strSql = string.Format(@"SELECT
	string1,
	string2,
	string3 
FROM
	A
WHERE
	Aname =AName ");
        DataTable formTable = db.GetDataTable(strSql, SqlHelper.MakeInParam("@AName", Aname));

        //根据Aname获取B表的信息
        string isql = string.Format(@"SELECT
	Aname,
	string1,
	string2,
	string3,
	string4,
	DataTime1,
	DataTime2 
FROM
	B 
WHERE
	Aname =@Aname");
        DataTable dispatchTable = db.GetDataTable(isql, SqlHelper.MakeInParam("@AName", Aname));
        //存入A信息
        foreach (DataRow item in formTable.Rows)
        {
            DispatchInfo DispatchInfo = new DispatchInfo();
            DispatchInfo.String1 = item["SQLstring1"].ToString();
            DispatchInfo.String2 = item["SQLstring2"].ToString();
            DispatchInfo.String3 = item["SQLstring3"].ToString();
            lstSorts.Add(DispatchInfo);
        }
        //存入B信息
        List<DispatchInfo> lstSorts1 = new List<DispatchInfo>();
        foreach (DataRow item in dispatchTable.Rows)
        {
            DispatchInfo DispatchInfo = new DispatchInfo();
            DispatchInfo.String1 = item["SQLstring1"].ToString();
            DispatchInfo.String2 = item["SQLstring2"].ToString();
            DispatchInfo.String3 = item["SQLstring3"].ToString();
            DispatchInfo.String4 = item["SQLstring4"].ToString();
            DispatchInfo.DataTime1 = Convert.ToDateTime(item["SQLDataTime1"]);
            DispatchInfo.DataTime2= Convert.ToDateTime(item["SQLDataTime2"]);
            lstSorts1.Add(DispatchInfo);
        }
        #endregion
        #region 数据检验
        //SQL事务组
        SqlList sqlList = new SqlList();
        //统计导入成功的数据
        int iSuccess = 0;
        //统计导入失败的数据
        int iFail = 0;
        foreach (DataRow rw in excel_dt.Rows)
        {
			//校验时期格式列是否正确
            DateTime dateTime = DateTime.Now;
            if (DateTime.TryParse(rw["DataTime1"].ToString(), out dateTime) == false || DateTime.TryParse(rw["DataTime2"].ToString(), out dateTime) == false)
            {
                iFail++;
                continue;
            }
            //取数
            string roomSort = rw["string1"].ToString();
            string roomSort1 = rw["string2"].ToString();
            string roomSort2 = rw["string3"].ToString();
            string dispatchName = rw["string4"].ToString();
            DateTime startime = Convert.ToDateTime(rw["DataTime1"]);
            DateTime endtime = Convert.ToDateTime(rw["DataTime2"]);
            //判断字段2是否在A表中都是存在的，不存在跳过本次循环
            if (!lstSorts.Any(m => m.String1 == string1 && m.String2==m.string2 && m.String3 == string3))
            {
                iFail++;
                continue;
            }
            if (DateTime.TryParse(rw["DataTime1"].ToString(), out dateTime) == false|| DateTime.TryParse(rw["DataTime2"].ToString(), out dateTime) == false)
            {
                iFail++;
                continue;
            }
            //TODO:判断是否为null DataTime2是否大于DataTime1
            if (rw.IsNull("string3")
                || string.IsNullOrWhiteSpace(rw["阶段发文名称"].ToString())
                || !DateTime.TryParse(rw["DataTime1"].ToString(), out dateTime)
                || !DateTime.TryParse(rw["DataTime2"].ToString(), out dateTime)
                || DataTime2>DataTime1)
            {
                iFail++;
                continue;
            }
            //查询对比完全一致数据
            if (lstSorts1.Any(m => m.RoomSort == roomSort
            && m.RoomSort2 == roomSort2
            && m.RoomSort1 == roomSort1
            && m.DispatchName == dispatchName
            && m.StarTime == startime
            && m.EndTime == endtime))
            {
                iFail++;
                continue;
            } 
			//执行导入SQL语句
            #endregion
            string sql = string.Format(@"
        	            INSERT INTO B ( Aname, SQLString4, SQLString1, SQLString2, SQLString3, SQLDataTime1, SQLDataTime2 )
                    VALUES
        	            ( @AName,@SQLstring4,@SQLString1,@SQLString2,@SQLString3,@SQLDataTime1,@SQLDataTime2 )");
            SqlItem sqlItem = new SqlItem(sql
                 , CrmDB.MakeInParam("@Aname", Aname)
                 , CrmDB.MakeInParam("@SQLString4", rw["string4"])
                 , CrmDB.MakeInParam("@SQLString1", rw["string1"])
                 , CrmDB.MakeInParam("@SQLString2", rw["string2"])
                 , CrmDB.MakeInParam("@SQLString3", rw["string3"])
                 , CrmDB.MakeInParam("@SQLDataTime1", rw["DataTime1"])
                 , CrmDB.MakeInParam("@SQLDataTime2", rw["DataTime2"]));
            sqlList.Add(sqlItem);
            iSuccess++;
        }
        if (sqlList.Count > 0)
        {
            int iResult = db.ExecuteNonQueryTran(sqlList);
            if (iResult > 0)
            {
                Script.Alert("导入成功" + iResult + "数据；失败" + (iFail) + "数据!");
                Script.DlgCloseAndParentReload();
                return;
            }
        }
        else
        {
            Script.Alert("操作失败！");
        }
    }
}
public class DispatchInfo
{
    public string String1 { get; set; }
    public string String2 { get; set; }
    public string String3 { get; set; }
    public string String4 { get; set; }
    public DateTime DataTime1{ get; set; }
    public DateTime DataTime1 { get; set; }
}
#endregion