Excel导入数据

来源：互联网发布：洛阳青峰网络黄伟编辑：程序博客网时间：2024/05/16 18:41

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 System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

    }
    protected void btn_import_Click(object sender, EventArgs e)
    {
        //检查是否选中了文件
        if (!div_fu.HasFile)
        {
            lbl_messagebox.Text = "请选择文件再导入";
            lbl_messagebox.ForeColor = System.Drawing.Color.Red;
            lbl_messagebox.Visible = true;
            return;
        }
        //获取并保存上传的文件(临时保存)
        string strXLSFile = Server.MapPath("InfoExcel") + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xsl";
        div_fu.PostedFile.SaveAs(strXLSFile);

        //检查XLS数据的合法性，返回检查后的DataSet数据集
        DataSet ds = new DataSet();
        string strExceptionMessage = null;
        if (false == Check_XLS_Data_Validity(strXLSFile, out ds, out strExceptionMessage))
        {
            lbl_messagebox.Text = "导入未通过合法性检查<br/>" + strExceptionMessage;
            lbl_messagebox.ForeColor = System.Drawing.Color.Red;
            lbl_messagebox.Visible = true;
            System.IO.File.Delete(strXLSFile);
            return;
        }

        string strError;
        DataSet dsFinal = new DataSet();
        System.IO.File.Delete(strXLSFile);
        if (!ImportDsAssistTaskListToDataBase(ds, out strError))
        {
            Response.Write("<script>alert('导入出错，" + strError + "');</script>");
            return;

        }
        else
        {
            Response.Write("<script>alert('项目批量导入成功！');</script>");
        }
        //删除上传的文件
        System.IO.File.Delete(strXLSFile);
        return;
    }

    protected bool Check_XLS_Data_Validity(string strXLSFile, out DataSet ds, out string strExceptionMessage)
    {
        //初始化输入
        ds = null;
        strExceptionMessage = null;
        //读取XLS文件
        string strConn = "provider=microsoft.jet.oledb.4.0;data source=" + strXLSFile + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
        OleDbConnection dbConn = new OleDbConnection(strConn);

        try
        {
            dbConn.Open();
        }
        catch (Exception ex)
        {
            strExceptionMessage = "错误：导入文件非期望的XLS，导入操作终止！" + ex.Message;
            return false;
        }
        DataTable dt = dbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        dbConn.Close();

        //默认打开第一张Sheet
        string sql = "select * from [" + dt.Rows[0][2].ToString().Trim() + "]";
        OleDbDataAdapter da = new OleDbDataAdapter(sql, dbConn);
        ds = new DataSet();
        try
        {
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            strExceptionMessage = "错误：导入的XLS的文件中，Sheet表异常，操作终止！" + ex.Message;
            return false;
        }
        //检查相应的字段
        string sql1 = "";
        SqlDAL sqlAcess = new SqlDAL();
        DataSet ds1 = new DataSet();
        string Gname = "";
        if (ds.Tables[0].Rows.Count > 1)
        {

}

        else
        {
            strExceptionMessage = "错误：导入的文件格式问题！导入操作终止";
            return false;
        }
        sqlAcess.Dispose();
        sqlAcess = null;
        //给列明添加名称
        foreach (DataColumn dc in ds.Tables[0].Columns)

            dc.ColumnName = ds.Tables[0].Rows[0][dc.Ordinal].ToString();

        //移除行标题
        ds.Tables[0].Rows.RemoveAt(0);
        strExceptionMessage = "检查数据成功！";
        return true;

}

private bool ImportDsAssistTaskListToDataBase(DataSet ds, out string strError)
{

        string strTable = "SoftContact";
        strError = "";
        try
        {
            //数据批量导入SqlServer,创建实例
            SqlBulkCopy sqlbulk = new SqlBulkCopy(System.Configuration.ConfigurationManager.ConnectionStrings["db_CRPConnectionString"].ConnectionString);
            //目标数据库名
            sqlbulk.DestinationTableName = strTable;
            //数据集字段索引与数据库字段索引映射
            foreach (DataColumn dc in ds.Tables[0].Columns)

                sqlbulk.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);

            //导入
            sqlbulk.WriteToServer(ds.Tables[0]);
            sqlbulk.Close();
            return true;
        }
        catch (Exception ex)
        {
            strError = "错误原因：导入临时表异常，导入操作终止！" + ex.Message;
            return false;

}
}

}