C#实现使用form上传Excel文件，导入Excel数据到SQL SERVER数据库

上传类(参考)：

/// <summary>
/// 单文件上传类 (暂时不支持多文件上传)
/// </summary>
public class UploadFile
{
    private static readonly ILog logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
    private Dictionary<string, string> fileInfo = new Dictionary<string, string>();//上传文件信息
    private double fileSize = double.Parse(ConfigurationManager.AppSettings["maxRequestLength"]);//最大文件大小 单位为KB
    private string filePath = HttpContext.Current.Request.PhysicalApplicationPath + "Upload\\";//文件保存路径
    private string fileType = ".xls";//允许上传的文件类型(EXCEL 2003) 小写
    public string message;//消息
    public string fileDir;//FilePath+Filename

    /// <summary>
    /// 保存文件
    /// </summary>
    /// <param name="fileInputName">表单文件域（input type='file'）name属性值</param>
    public void Save(string fileInputName)
    {
        try
        {
            message = String.Empty;
            // 获取上传的文件
            HttpFileCollection file = HttpContext.Current.Request.Files;
            HttpPostedFile postFile = file[fileInputName];

            // 验证格式
            if (!this.CheckingType(postFile.FileName))
            {
                return;
            }

            // 获取存储目录
            string dir = this.filePath + this.fileInfo["Name"];

            // 保存文件
            postFile.SaveAs(dir);

            fileDir = dir;

            logger.Info(dir + "上传成功！");
        }
        catch (Exception ex)
        {
            if (ex.Message == "超过了最大请求长度。")
            {
                message += "上传文件超出大小限制(文件大小不得大于" + Math.Round(fileSize / 1024, 2) + "MB)！";
            }
            else
            {
                message += "上传文件出错！";
                logger.Error("上传文件出错！");
            }
            throw ex;
        }
    }

    /// <summary>
    /// 验证文件类型
    /// </summary>
    /// <param name="fileName">文件名</param>
    /// <returns>返回布尔值，true表示文件类型允许，false表示不允许</returns>
    private bool CheckingType(string fileName)
    {
        // 获取上传文件类型(小写)
        string type = Path.GetExtension(fileName).ToLowerInvariant();
        string filenameAndUserid = Guid.NewGuid().ToString() + "_" + 
                                   (HttpContext.Current.Session["Login_ID"] == null ? "null" : HttpContext.Current.Session["Login_ID"]);

        // 注册文件信息
        this.fileInfo.Add("Name", filenameAndUserid + type);

        // 验证类型
        if (fileType.IndexOf(type) == -1)
        {
            string tem = "服务器端检测到非法文件类型" + type + "！";
            message += tem;
            logger.Info(tem);
            return false;
        }
        else
        {
            return true;
        }
    }
}

导入数据的方法：

/// <summary>
/// 从Excel提取数据到Dataset
/// </summary>
/// <param name="filePath">Excel文件路径名</param>
private void ImportXlsToDataSet(string filePath)
{
   mesStr = String.Empty;
   if (!File.Exists(filePath))
   {
       mesStr = "服务器上找不到要读取的EXCEL文件！";
       logger.Error("服务器上找不到要读取的EXCEL文件" + filePath);
       return;
   }

   string oleDBConnString = String.Empty;
   oleDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;";
   oleDBConnString += "Data Source=";
   oleDBConnString += filePath;
   oleDBConnString += ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'";//第一行(标题行)也读取，使所有列的类型识别为字符串

   OleDbConnection oleDBConn = null;
   OleDbDataAdapter oleDBDataAdapter = null;
   DataTable dt = new DataTable();
   DataSet ds = new DataSet();
   try
   {
       oleDBConn = new OleDbConnection(oleDBConnString);
       oleDBConn.Open();
       dt = oleDBConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
       if (dt != null && dt.Rows.Count > 0)
       {
           dt.TableName = dt.Rows[0]["TABLE_NAME"].ToString();
       }

       oleDBDataAdapter = new OleDbDataAdapter(" SELECT *  FROM [" + dt.TableName + "]", oleDBConn);
       oleDBDataAdapter.Fill(ds, "m_tableName");

       //插入信息到数据库
       AddDatasetToDB(ds);
   }
   catch (Exception ex)
   {
       mesStr += "读取EXCEL中的企业信息出错！";
       logger.Error("读取EXCEL中的企业信息出错！");
       throw ex;
   }
   finally
   {
       if (oleDBDataAdapter != null)
       {
           oleDBDataAdapter.Dispose();
       }

       if (oleDBConn != null)
       {
           oleDBConn.Close();
           oleDBConn.Dispose();
       }
   }
}

/// <summary>
/// 将Dataset的内容导入数据库
/// </summary>
/// <param name="pds">企业信息DataSet</param>
private void AddDatasetToDB(DataSet ds)
{
   if (ds != null)
   {
       int colCount = ds.Tables[0].Columns.Count;//列数
       int rowCount = ds.Tables[0].Rows.Count;//行数 总条数 
       sumCount = rowCount - 1;//排除标题行

       string temStr = String.Empty;

       if (colCount < 6)
       {
           mesStr += "上传的EXCEL格式错误！";
           return;
       }

       if (rowCount > 1)
       {
           SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SqlConnectionStr"]);

           //新增语句
           string sqlStr = @"INSERT INTO Company([Area],[Code],[Name],[Class],[Special],[ApproveDate]) 
                       VALUES(@Area,@Code,@Name,@Class,@Special,@ApproveDate) ";
           SqlCommand insert_cmd = new SqlCommand(sqlStr, conn);
           insert_cmd.Parameters.Add(new SqlParameter("@Area", SqlDbType.NVarChar, 50));//区域
           insert_cmd.Parameters.Add(new SqlParameter("@Code", SqlDbType.NVarChar, 50));//编码
           insert_cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 256));//名称
           insert_cmd.Parameters.Add(new SqlParameter("@Class", SqlDbType.NVarChar, 50));//类别
           insert_cmd.Parameters.Add(new SqlParameter("@Special", SqlDbType.Int));//标识
           insert_cmd.Parameters.Add(new SqlParameter("@ApproveDate", SqlDbType.DateTime));//评定时间
           insert_cmd.Connection.Open();

           //查询语句
           SqlCommand select_cmd = new SqlCommand("SELECT count(Id) FROM Company WHERE Code=@CodeVal", conn);
           select_cmd.Parameters.Add(new SqlParameter("@CodeVal", SqlDbType.NVarChar, 50));//编码
           DataSet countDataSet;
           SqlDataAdapter sqlDataAdapter;

           //更新语句
           SqlCommand update_cmd = new SqlCommand(@"UPDATE Company SET Area=@Area, Name=@Name,
                                                   Class=@Class,Special=@Special,ApproveDate=@ApproveDate,SaveDate=@SaveDate
                                                   WHERE Code=@Code", conn);
           update_cmd.Parameters.Add(new SqlParameter("@Area", SqlDbType.NVarChar, 50));//区域
           update_cmd.Parameters.Add(new SqlParameter("@Code", SqlDbType.NVarChar, 50));//编码
           update_cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 256));//名称
           update_cmd.Parameters.Add(new SqlParameter("@Class", SqlDbType.NVarChar, 50));//类别
           update_cmd.Parameters.Add(new SqlParameter("@Special", SqlDbType.Int));//标识
           update_cmd.Parameters.Add(new SqlParameter("@ApproveDate", SqlDbType.DateTime));//评定时间
           update_cmd.Parameters.Add(new SqlParameter("@SaveDate", SqlDbType.DateTime));//保存/更新时间


           string strArea, strCode = String.Empty, strName, strClass, strSpecial, strApproveDate;
           DateTime? approveDate;
           DateTime date = new DateTime();

           //遍历数据 导入
           for (int i = 1; i < rowCount; i++)
           {
               try
               {
                   //暂存读取的值用于判断
                   strCode = ds.Tables[0].Rows[i][1].ToString().Trim();
                   strArea = ds.Tables[0].Rows[i][0].ToString().Trim();
                   strName = ds.Tables[0].Rows[i][2].ToString().Trim();
                   strClass = ds.Tables[0].Rows[i][3].ToString().Trim();
                   strSpecial = ds.Tables[0].Rows[i][4].ToString().Trim();
                   strApproveDate = ds.Tables[0].Rows[i][5].ToString().Trim().Replace(".", "-");

                   if (strApproveDate.Length == 6 && strApproveDate.IndexOf("-") == -1)
                   {
                       strApproveDate = "20" + strApproveDate.Substring(0, 2) +
                                        "-" + strApproveDate.Substring(2, 2) + "-" + strApproveDate.Substring(4, 2);
                   }

                   if (!string.IsNullOrEmpty(strCode))//编码不为空
                   {
                       select_cmd.Parameters["@CodeVal"].Value = strCode;
                       sqlDataAdapter = new SqlDataAdapter(select_cmd);
                       sqlDataAdapter.SelectCommand = select_cmd;
                       countDataSet = new DataSet();
                       sqlDataAdapter.Fill(countDataSet, "count");

                       approveDate = null;
                       if (!string.IsNullOrEmpty(strApproveDate))
                       {
                           date = DateTime.Parse(strApproveDate);
                           approveDate = date;
                       }

                       if (countDataSet.Tables["count"].Rows[0][0].ToString() == "0")//此信息不存在则添加
                       {
                           //SQL参数赋值
                           insert_cmd.Parameters["@Area"].Value = string.IsNullOrEmpty(strArea) ? SqlString.Null : strArea;
                           insert_cmd.Parameters["@Code"].Value = strCode;
                           insert_cmd.Parameters["@Name"].Value = string.IsNullOrEmpty(strName) ? SqlString.Null : strName;
                           insert_cmd.Parameters["@Class"].Value = string.IsNullOrEmpty(strClass) ? SqlString.Null : strClass;
                           insert_cmd.Parameters["@Special"].Value = string.IsNullOrEmpty(strSpecial) ? 0 :
                                                                    ((strSpecial == "是" || strSpecial == "特定") ? 1 : 0);
                           insert_cmd.Parameters["@ApproveDate"].Value = (approveDate == null ? SqlDateTime.Null : date);

                           if (insert_cmd.ExecuteNonQuery() != 0)//插入单条企业信息
                           {
                               successCount++;//成功条数
                           }
                           else
                           {
                               faultInfo_Code_List.Add(strCode);//插入失败的企业信息编码会在前台提示
                               logger.Error("插入第" + i + "行，编码为" + strCode + "的企业信息失败！");
                           }
                       }
                       else//更新数据
                       {

                           //SQL参数赋值
                           update_cmd.Parameters["@Area"].Value = string.IsNullOrEmpty(strArea) ? SqlString.Null : strArea;
                           update_cmd.Parameters["@Code"].Value = strCode;
                           update_cmd.Parameters["@Name"].Value = string.IsNullOrEmpty(strName) ? SqlString.Null : strName;
                           update_cmd.Parameters["@Class"].Value = string.IsNullOrEmpty(strClass) ? SqlString.Null : strClass;
                           update_cmd.Parameters["@Special"].Value = string.IsNullOrEmpty(strSpecial) ? 0 :
                                                                    ((strSpecial == "是" || strSpecial == "特定") ? 1 : 0);
                           update_cmd.Parameters["@ApproveDate"].Value = (approveDate == null ? SqlDateTime.Null : date);
                           update_cmd.Parameters["@SaveDate"].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");

                           if (update_cmd.ExecuteNonQuery() != 0)//更新单条企业信息
                           {
                               successCount++;//成功条数
                           }
                           else
                           {
                               faultInfo_Code_List.Add(strCode);//更新失败的企业信息的企业编码会在前台提示
                               logger.Error("更新第" + i + "行，编码为" + strCode + "的企业信息失败！");
                           }
                           //existInfo_Code_List.Add(strCode);//记录已经存在的企业信息
                       }
                   }
                   else
                   {
                       //faultInfo_Code_List.Add(strName);
                       if (mesStr.IndexOf("编码为空的企业信息未导入。") == -1)
                       {
                           mesStr += "编码为空的企业信息未导入。";
                       }
                   }
               }
               catch (Exception ex)
               {
                   if (mesStr.IndexOf("插入企业信息异常！详细：" + ex.Message) == -1)
                   {
                       mesStr += "插入企业信息异常！详细：" + ex.Message;
                   }

                   faultInfo_Code_List.Add(strCode);//插入失败的企业信息的企业编码会在前台提示

                   //此处异常不抛出使循环继续执行 但是会记录日志 也会在前台有提示
                   logger.Error("插入编码为" + strCode + "的企业信息异常！");
                   logger.Error("异常描述：\t" + ex.Message);
                   logger.Error("异常方法：\t" + ex.TargetSite);
                   logger.Error("异常堆栈：\t" + ex.StackTrace);
               }
           }

           if (conn != null)
           {
               conn.Close();
               conn.Dispose();
           }
           if (insert_cmd != null)
           {
               insert_cmd.Dispose();
           }
           if (select_cmd != null)
           {
               select_cmd.Dispose();
           }
           if (update_cmd != null)
           {
               update_cmd.Dispose();
           }
       }
       else
       {
           mesStr += "上传的EXCEL没有任何企业信息！";
       }
   }
}

调用示例：

           UploadFile uploadFile = new UploadFile();

           try
           {
               uploadFile.Save("excelfile");//保存EXCEL
               ImportXlsToDataSet(uploadFile.fileDir);//导入数据
               if (File.Exists(uploadFile.fileDir))
               {
                   File.Delete(uploadFile.fileDir);//删除EXCEL
               }
           }
           catch (Exception ex)
           {
               mesStr += ex.Message;
               logger.Error("导入企业信息异常：\t" + uploadFile.message + mesStr);
               logger.Error("异常描述：\t" + ex.Message);
               logger.Error("异常方法：\t" + ex.TargetSite);
               logger.Error("异常堆栈：\t" + ex.StackTrace);

               if (File.Exists(uploadFile.fileDir))
               {
                   File.Delete(uploadFile.fileDir);//删除EXCEL
               }
           }

           mesStr = uploadFile.message + mesStr;
           mesStr = successCount > 0 ? "导入完成！成功导入" + successCount + "条，失败" + (sumCount - successCount) + "条。" +
                                        (faultInfo_Code_List.Count > 0 ? "导入失败的企业信息编码为：" +
                                                                              String.Join(",", faultInfo_Code_List.ToArray()) +
                                                                             "。后台错误信息：" + mesStr : mesStr) : mesStr;
           logger.Debug(mesStr);
           return mesStr;

说明：

1.其中logger是用log4net记日志；

2.命名空间(需添加引用)：

using System.Web;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.SqlTypes;
using System.Web.Configuration;

3.调用时用到的成员变量：

private static readonly ILog logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
private int successCount;//成功条数
private int sumCount;//总数
private string mesStr;//消息
private List<string> faultInfo_Code_List = new List<string>();//记录插入失败的企业信息编码
//private List<string> existInfo_Code_List = new List<string>();//已经存在的企业信息编码

4.前台简单示例

简单表单：

<form action="uploadExcelDone.aspx" method="post" enctype="multipart/form-data" id="fileForm">
<input type="file" name="excelfile" id="excelfile" />
<input type="button" id="submitBtn" onclick="submitFile()" />
</form>

简单javascript方法：

function submitFile()

{

　　//可在前台检测扩展名，此处就不写出了

　　$("#fileForm").submit()//jQuery方法提交表单

}

 

补充：插入/更新数据部分除了用ADO.NET中的执行SQL的方法，还可以使用DataTable来处理，简化成如下代码：

SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(sql, conn));
SqlCommandBuilder cb = new SqlCommandBuilder(da);

DataSet dsdes = new DataSet();
da.Fill(dsdes, "destbl");

dsdes.Tables["destbl"].Merge(dtSrc);
foreach(DataRow dr in dsdes.Tables["destbl"].Rows)
{
    dr.SetAdded();
}

da.Update(dsdes.Tables["destbl"]);
dsdes.AcceptChanges();