C#实现使用form上传Excel文件,导入Excel数据到SQLSERVER数据库_百 ...

网站建设2010 更新时间：2025-06-08 04:49:20

2023年6月21日发(作者：)

C#实现使⽤form上传Excel⽂件，导⼊Excel数据到SQLSERVER数据库上传类()：///

/// 单⽂件上传类 (暂时不⽀持多⽂件上传)///

public class UploadFile{ private static readonly ILog logger = ger(rentMethod().DeclaringType); private Dictionary fileInfo = new Dictionary();//上传⽂件信息 private double fileSize = (tings["maxRequestLength"]);//最⼤⽂件⼤⼩单位为KB private string filePath = alApplicationPath + "Upload";//⽂件保存路径 private string fileType = ".xls";//允许上传的⽂件类型(EXCEL 2003) ⼩写 public string message;//消息 public string fileDir;//FilePath+Filename ///

/// 保存⽂件 ///

/// 表单⽂件域（input type='file'）name属性值 public void Save(string fileInputName) { try { message = ; // 获取上传的⽂件 HttpFileCollection file = ; HttpPostedFile postFile = file[fileInputName]; // 验证格式 if (!ngType(me)) { return; } // 获取存储⽬录 string dir = th + fo["Name"]; // 保存⽂件 (dir); fileDir = dir; (dir + "上传成功！"); } catch (Exception ex) { if (e == "超过了最⼤请求长度。") { message += "上传⽂件超出⼤⼩限制(⽂件⼤⼩不得⼤于" + (fileSize / 1024, 2) + "MB)！"; } else { message += "上传⽂件出错！"; ("上传⽂件出错！"); } throw ex; } } ///

/// 验证⽂件类型 ///

/// ⽂件名 /// 返回布尔值，true表⽰⽂件类型允许，false表⽰不允许 private bool CheckingType(string fileName) { // 获取上传⽂件类型(⼩写) string type = ension(fileName).ToLowerInvariant(); string filenameAndUserid = d().ToString() + "_" +

(n["Login_ID"] == null ? "null" : n["Login_ID"]); // 注册⽂件信息 ("Name", filenameAndUserid + type); // 验证类型 if (f(type) == -1) { string tem = "服务器端检测到⾮法⽂件类型" + type + "！"; message += tem; (tem); return false; } else { return true; } }}导⼊数据的⽅法：///

/// 从Excel提取数据到Dataset///

/// Excel⽂件路径名private void ImportXlsToDataSet(string filePath){ mesStr = ; if (!(filePath)) { mesStr = "服务器上找不到要读取的EXCEL⽂件！"; ("服务器上找不到要读取的EXCEL⽂件" + filePath); return; } string oleDBConnString = ; oleDBConnString = "Provider=.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); (); dt = DbSchemaTable(, null); if (dt != null && > 0) { ame = [0]["TABLE_NAME"].ToString(); } oleDBDataAdapter = new OleDbDataAdapter(" SELECT * FROM [" + ame + "]", oleDBConn); (ds, "m_tableName"); //插⼊信息到数据库 AddDatasetToDB(ds); } catch (Exception ex) { mesStr += "读取EXCEL中的企业信息出错！"; ("读取EXCEL中的企业信息出错！"); throw ex; } finally { if (oleDBDataAdapter != null) { e(); } if (oleDBConn != null) { (); e(); } }}///

/// 将Dataset的内容导⼊数据库///

/// 企业信息DataSetprivate void AddDatasetToDB(DataSet ds){ if (ds != null) { int colCount = [0].;//列数 int rowCount = [0].;//⾏数总条数

sumCount = rowCount - 1;//排除标题⾏ string temStr = ; if (colCount < 6) { mesStr += "上传的EXCEL格式错误！"; return; } if (rowCount > 1) { SqlConnection conn = new SqlConnection(tings["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_(new SqlParameter("@Area", ar, 50));//区域 insert_(new SqlParameter("@Code", ar, 50));//编码 insert_(new SqlParameter("@Name", ar, 256));//名称 insert_(new SqlParameter("@Class", ar, 50));//类别 insert_(new SqlParameter("@Special", ));//标识 insert_(new SqlParameter("@ApproveDate", me));//评定时间 insert_(); //查询语句 SqlCommand select_cmd = new SqlCommand("SELECT count(Id) FROM Company WHERE Code=@CodeVal", conn); select_(new SqlParameter("@CodeVal", ar, 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_(new SqlParameter("@Area", ar, 50));//区域 update_(new SqlParameter("@Code", ar, 50));//编码 update_(new SqlParameter("@Name", ar, 256));//名称 update_(new SqlParameter("@Class", ar, 50));//类别 update_(new SqlParameter("@Special", ));//标识 update_(new SqlParameter("@ApproveDate", me));//评定时间 update_(new SqlParameter("@SaveDate", me));//保存/更新时间 string strArea, strCode = , strName, strClass, strSpecial, strApproveDate; DateTime? approveDate; DateTime date = new DateTime(); //遍历数据导⼊ for (int i = 1; i < rowCount; i++) { try { //暂存读取的值⽤于判断 strCode = [0].Rows[i][1].ToString().Trim(); strArea = [0].Rows[i][0].ToString().Trim(); strName = [0].Rows[i][2].ToString().Trim(); strClass = [0].Rows[i][3].ToString().Trim(); strSpecial = [0].Rows[i][4].ToString().Trim(); strApproveDate = [0].Rows[i][5].ToString().Trim().Replace(".", "-"); if ( == 6 && f("-") == -1) { strApproveDate = "20" + ing(0, 2) + "-" + ing(2, 2) + "-" + ing(4, 2); } if (!OrEmpty(strCode))//编码不为空 { select_ters["@CodeVal"].Value = strCode; sqlDataAdapter = new SqlDataAdapter(select_cmd); Command = select_cmd; countDataSet = new DataSet(); (countDataSet, "count"); approveDate = null; if (!OrEmpty(strApproveDate)) { date = (strApproveDate); approveDate = date; } if (["count"].Rows[0][0].ToString() == "0")//此信息不存在则添加 { //SQL参数赋值 insert_ters["@Area"].Value = OrEmpty(strArea) ? : strArea; insert_ters["@Code"].Value = strCode; insert_ters["@Name"].Value = OrEmpty(strName) ? : strName; insert_ters["@Class"].Value = OrEmpty(strClass) ? : strClass; insert_ters["@Special"].Value = OrEmpty(strSpecial) ? 0 : ((strSpecial == "是" || strSpecial == "特定") ? 1 : 0); insert_ters["@ApproveDate"].Value = (approveDate == null ? : date); if (insert_eNonQuery() != 0)//插⼊单条企业信息 { successCount++;//成功条数 } else { faultInfo_Code_(strCode);//插⼊失败的企业信息编码会在前台提⽰ ("插⼊第" + i + "⾏，编码为" + strCode + "的企业信息失败！"); } } else//更新数据 { //SQL参数赋值 update_ters["@Area"].Value = OrEmpty(strArea) ? : strArea; update_ters["@Code"].Value = strCode; update_ters["@Name"].Value = OrEmpty(strName) ? : strName; update_ters["@Class"].Value = OrEmpty(strClass) ? : strClass; update_ters["@Special"].Value = OrEmpty(strSpecial) ? 0 : ((strSpecial == "是" || strSpecial == "特定") ? 1 : 0); update_ters["@ApproveDate"].Value = (approveDate == null ? : date); update_ters["@SaveDate"].Value = ng("yyyy-MM-dd HH:mm:"); if (update_eNonQuery() != 0)//更新单条企业信息 { successCount++;//成功条数 } else { faultInfo_Code_(strCode);//更新失败的企业信息的企业编码会在前台提⽰ ("更新第" + i + "⾏，编码为" + strCode + "的企业信息失败！"); } //existInfo_Code_(strCode);//记录已经存在的企业信息 } } else { //faultInfo_Code_(strName); if (f("编码为空的企业信息未导⼊。") == -1) { mesStr += "编码为空的企业信息未导⼊。"; } } } catch (Exception ex) { if (f("插⼊企业信息异常！详细：" + e) == -1) { mesStr += "插⼊企业信息异常！详细：" + e; } faultInfo_Code_(strCode);//插⼊失败的企业信息的企业编码会在前台提⽰ //此处异常不抛出使循环继续执⾏但是会记录⽇志也会在前台有提⽰ ("插⼊编码为" + strCode + "的企业信息异常！"); ("异常描述：t" + e); ("异常⽅法：t" + Site); ("异常堆栈：t" + race); } } if (conn != null) { (); e(); } if (insert_cmd != null) { insert_e(); } if (select_cmd != null) { select_e(); } if (update_cmd != null) { update_e(); } } else { mesStr += "上传的EXCEL没有任何企业信息！"; } }}调⽤⽰例： UploadFile uploadFile = new UploadFile(); try { ("excelfile");//保存EXCEL ImportXlsToDataSet(r);//导⼊数据 if ((r)) { (r);//删除EXCEL } } catch (Exception ex) { mesStr += e; ("导⼊企业信息异常：t" + e + mesStr); ("异常描述：t" + e); ("异常⽅法：t" + Site); ("异常堆栈：t" + race); if ((r)) { (r);//删除EXCEL } } mesStr = e + mesStr; mesStr = successCount > 0 ? "导⼊完成！成功导⼊" + successCount + "条，失败" + (sumCount - successCount) + "条。" + (faultInfo_Code_ > 0 ? "导⼊失败的企业信息编码为：" + (",", faultInfo_Code_y()) + "。后台错误信息：" + mesStr : mesStr) : mesStr; (mesStr); return mesStr;说明：1.其中logger是⽤log4net记⽇志；2.命名空间(需添加引⽤)：using ;using ;using ;using ;using ent;using uration;using es;using uration;3.调⽤时⽤到的成员变量：private static readonly ILog logger = ger(rentMethod().DeclaringType);private int successCount;//成功条数private int sumCount;//总数private string mesStr;//消息private List faultInfo_Code_List = new List();//记录插⼊失败的企业信息编码//private List existInfo_Code_List = new List();//已经存在的企业信息编码4.前台简单⽰例简单表单：

简单javascript⽅法：function submitFile(){　　//可在前台检测扩展名，此处就不写出了　　$("#fileForm").submit()//jQuery⽅法提交表单}

补充：插⼊/更新数据部分除了⽤中的执⾏SQL的⽅法，还可以使⽤DataTable来处理，简化成如下代码：SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(sql, conn));SqlCommandBuilder cb = new SqlCommandBuilder(da);DataSet dsdes = new DataSet();(dsdes, "destbl");["destbl"].Merge(dtSrc);foreach(DataRow dr in ["destbl"].Rows){ ed();}(["destbl"]);Changes();

2023年6月21日发(作者：)

C#实现使⽤form上传Excel⽂件，导⼊Excel数据到SQLSERVER数据库上传类()：///

/// 单⽂件上传类 (暂时不⽀持多⽂件上传)///

public class UploadFile{ private static readonly ILog logger = ger(rentMethod().DeclaringType); private Dictionary fileInfo = new Dictionary();//上传⽂件信息 private double fileSize = (tings["maxRequestLength"]);//最⼤⽂件⼤⼩单位为KB private string filePath = alApplicationPath + "Upload";//⽂件保存路径 private string fileType = ".xls";//允许上传的⽂件类型(EXCEL 2003) ⼩写 public string message;//消息 public string fileDir;//FilePath+Filename ///

/// 保存⽂件 ///

/// 表单⽂件域（input type='file'）name属性值 public void Save(string fileInputName) { try { message = ; // 获取上传的⽂件 HttpFileCollection file = ; HttpPostedFile postFile = file[fileInputName]; // 验证格式 if (!ngType(me)) { return; } // 获取存储⽬录 string dir = th + fo["Name"]; // 保存⽂件 (dir); fileDir = dir; (dir + "上传成功！"); } catch (Exception ex) { if (e == "超过了最⼤请求长度。") { message += "上传⽂件超出⼤⼩限制(⽂件⼤⼩不得⼤于" + (fileSize / 1024, 2) + "MB)！"; } else { message += "上传⽂件出错！"; ("上传⽂件出错！"); } throw ex; } } ///

/// 验证⽂件类型 ///

/// ⽂件名 /// 返回布尔值，true表⽰⽂件类型允许，false表⽰不允许 private bool CheckingType(string fileName) { // 获取上传⽂件类型(⼩写) string type = ension(fileName).ToLowerInvariant(); string filenameAndUserid = d().ToString() + "_" +

(n["Login_ID"] == null ? "null" : n["Login_ID"]); // 注册⽂件信息 ("Name", filenameAndUserid + type); // 验证类型 if (f(type) == -1) { string tem = "服务器端检测到⾮法⽂件类型" + type + "！"; message += tem; (tem); return false; } else { return true; } }}导⼊数据的⽅法：///

/// 从Excel提取数据到Dataset///

/// Excel⽂件路径名private void ImportXlsToDataSet(string filePath){ mesStr = ; if (!(filePath)) { mesStr = "服务器上找不到要读取的EXCEL⽂件！"; ("服务器上找不到要读取的EXCEL⽂件" + filePath); return; } string oleDBConnString = ; oleDBConnString = "Provider=.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); (); dt = DbSchemaTable(, null); if (dt != null && > 0) { ame = [0]["TABLE_NAME"].ToString(); } oleDBDataAdapter = new OleDbDataAdapter(" SELECT * FROM [" + ame + "]", oleDBConn); (ds, "m_tableName"); //插⼊信息到数据库 AddDatasetToDB(ds); } catch (Exception ex) { mesStr += "读取EXCEL中的企业信息出错！"; ("读取EXCEL中的企业信息出错！"); throw ex; } finally { if (oleDBDataAdapter != null) { e(); } if (oleDBConn != null) { (); e(); } }}///

/// 将Dataset的内容导⼊数据库///

/// 企业信息DataSetprivate void AddDatasetToDB(DataSet ds){ if (ds != null) { int colCount = [0].;//列数 int rowCount = [0].;//⾏数总条数

sumCount = rowCount - 1;//排除标题⾏ string temStr = ; if (colCount < 6) { mesStr += "上传的EXCEL格式错误！"; return; } if (rowCount > 1) { SqlConnection conn = new SqlConnection(tings["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_(new SqlParameter("@Area", ar, 50));//区域 insert_(new SqlParameter("@Code", ar, 50));//编码 insert_(new SqlParameter("@Name", ar, 256));//名称 insert_(new SqlParameter("@Class", ar, 50));//类别 insert_(new SqlParameter("@Special", ));//标识 insert_(new SqlParameter("@ApproveDate", me));//评定时间 insert_(); //查询语句 SqlCommand select_cmd = new SqlCommand("SELECT count(Id) FROM Company WHERE Code=@CodeVal", conn); select_(new SqlParameter("@CodeVal", ar, 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_(new SqlParameter("@Area", ar, 50));//区域 update_(new SqlParameter("@Code", ar, 50));//编码 update_(new SqlParameter("@Name", ar, 256));//名称 update_(new SqlParameter("@Class", ar, 50));//类别 update_(new SqlParameter("@Special", ));//标识 update_(new SqlParameter("@ApproveDate", me));//评定时间 update_(new SqlParameter("@SaveDate", me));//保存/更新时间 string strArea, strCode = , strName, strClass, strSpecial, strApproveDate; DateTime? approveDate; DateTime date = new DateTime(); //遍历数据导⼊ for (int i = 1; i < rowCount; i++) { try { //暂存读取的值⽤于判断 strCode = [0].Rows[i][1].ToString().Trim(); strArea = [0].Rows[i][0].ToString().Trim(); strName = [0].Rows[i][2].ToString().Trim(); strClass = [0].Rows[i][3].ToString().Trim(); strSpecial = [0].Rows[i][4].ToString().Trim(); strApproveDate = [0].Rows[i][5].ToString().Trim().Replace(".", "-"); if ( == 6 && f("-") == -1) { strApproveDate = "20" + ing(0, 2) + "-" + ing(2, 2) + "-" + ing(4, 2); } if (!OrEmpty(strCode))//编码不为空 { select_ters["@CodeVal"].Value = strCode; sqlDataAdapter = new SqlDataAdapter(select_cmd); Command = select_cmd; countDataSet = new DataSet(); (countDataSet, "count"); approveDate = null; if (!OrEmpty(strApproveDate)) { date = (strApproveDate); approveDate = date; } if (["count"].Rows[0][0].ToString() == "0")//此信息不存在则添加 { //SQL参数赋值 insert_ters["@Area"].Value = OrEmpty(strArea) ? : strArea; insert_ters["@Code"].Value = strCode; insert_ters["@Name"].Value = OrEmpty(strName) ? : strName; insert_ters["@Class"].Value = OrEmpty(strClass) ? : strClass; insert_ters["@Special"].Value = OrEmpty(strSpecial) ? 0 : ((strSpecial == "是" || strSpecial == "特定") ? 1 : 0); insert_ters["@ApproveDate"].Value = (approveDate == null ? : date); if (insert_eNonQuery() != 0)//插⼊单条企业信息 { successCount++;//成功条数 } else { faultInfo_Code_(strCode);//插⼊失败的企业信息编码会在前台提⽰ ("插⼊第" + i + "⾏，编码为" + strCode + "的企业信息失败！"); } } else//更新数据 { //SQL参数赋值 update_ters["@Area"].Value = OrEmpty(strArea) ? : strArea; update_ters["@Code"].Value = strCode; update_ters["@Name"].Value = OrEmpty(strName) ? : strName; update_ters["@Class"].Value = OrEmpty(strClass) ? : strClass; update_ters["@Special"].Value = OrEmpty(strSpecial) ? 0 : ((strSpecial == "是" || strSpecial == "特定") ? 1 : 0); update_ters["@ApproveDate"].Value = (approveDate == null ? : date); update_ters["@SaveDate"].Value = ng("yyyy-MM-dd HH:mm:"); if (update_eNonQuery() != 0)//更新单条企业信息 { successCount++;//成功条数 } else { faultInfo_Code_(strCode);//更新失败的企业信息的企业编码会在前台提⽰ ("更新第" + i + "⾏，编码为" + strCode + "的企业信息失败！"); } //existInfo_Code_(strCode);//记录已经存在的企业信息 } } else { //faultInfo_Code_(strName); if (f("编码为空的企业信息未导⼊。") == -1) { mesStr += "编码为空的企业信息未导⼊。"; } } } catch (Exception ex) { if (f("插⼊企业信息异常！详细：" + e) == -1) { mesStr += "插⼊企业信息异常！详细：" + e; } faultInfo_Code_(strCode);//插⼊失败的企业信息的企业编码会在前台提⽰ //此处异常不抛出使循环继续执⾏但是会记录⽇志也会在前台有提⽰ ("插⼊编码为" + strCode + "的企业信息异常！"); ("异常描述：t" + e); ("异常⽅法：t" + Site); ("异常堆栈：t" + race); } } if (conn != null) { (); e(); } if (insert_cmd != null) { insert_e(); } if (select_cmd != null) { select_e(); } if (update_cmd != null) { update_e(); } } else { mesStr += "上传的EXCEL没有任何企业信息！"; } }}调⽤⽰例： UploadFile uploadFile = new UploadFile(); try { ("excelfile");//保存EXCEL ImportXlsToDataSet(r);//导⼊数据 if ((r)) { (r);//删除EXCEL } } catch (Exception ex) { mesStr += e; ("导⼊企业信息异常：t" + e + mesStr); ("异常描述：t" + e); ("异常⽅法：t" + Site); ("异常堆栈：t" + race); if ((r)) { (r);//删除EXCEL } } mesStr = e + mesStr; mesStr = successCount > 0 ? "导⼊完成！成功导⼊" + successCount + "条，失败" + (sumCount - successCount) + "条。" + (faultInfo_Code_ > 0 ? "导⼊失败的企业信息编码为：" + (",", faultInfo_Code_y()) + "。后台错误信息：" + mesStr : mesStr) : mesStr; (mesStr); return mesStr;说明：1.其中logger是⽤log4net记⽇志；2.命名空间(需添加引⽤)：using ;using ;using ;using ;using ent;using uration;using es;using uration;3.调⽤时⽤到的成员变量：private static readonly ILog logger = ger(rentMethod().DeclaringType);private int successCount;//成功条数private int sumCount;//总数private string mesStr;//消息private List faultInfo_Code_List = new List();//记录插⼊失败的企业信息编码//private List existInfo_Code_List = new List();//已经存在的企业信息编码4.前台简单⽰例简单表单：

简单javascript⽅法：function submitFile(){　　//可在前台检测扩展名，此处就不写出了　　$("#fileForm").submit()//jQuery⽅法提交表单}

补充：插⼊/更新数据部分除了⽤中的执⾏SQL的⽅法，还可以使⽤DataTable来处理，简化成如下代码：SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(sql, conn));SqlCommandBuilder cb = new SqlCommandBuilder(da);DataSet dsdes = new DataSet();(dsdes, "destbl");["destbl"].Merge(dtSrc);foreach(DataRow dr in ["destbl"].Rows){ ed();}(["destbl"]);Changes();

本文发布于:2023-06-21，感谢您对本站的认可！

本文链接:http://torson.com.cn/wangzhan/1687351260a3451.html