Asp.net导入Excel数据文件|江阴雨辰互联

2023年7月14日发(作者：)

导⼊Excel数据⽂件实现页⾯选择查找⽂件，并导⼊以如下结构为模板的Excel数据⽂件

前台设计界⾯如下：在这⾥使⽤的FileUpload控件实现⽂件查找选择，前台代码如下：

选择⽂件后点击上传按钮触发后台点击事件。 ///

/// 上传导⼊按钮点击 ///

/// /// protected void btnImport_Click(object sender, EventArgs e) { try { //⽂件名 string strFileName = me; //验证是否选择了⽂件 if ("" == ()) { erStartupScript(this, e(), "JsError", "alert('请选择⽂件！');", true); return; } //验证⽂件类型是不是Excel if (ing(dexOf('.')) != ".xlsx" && ing(dexOf('.')) != ".xls") { erStartupScript(this, e(), "JsError", "alert('⽂件类型错误！'", true); return; } //获取上载⽂件内容 string Attachment = "";//附件 //string strFilePath = tings["tempFilePath"].ToString(); string strFilePath = "./tem/"; strFilePath = h(strFilePath); Attachment = ing_FileUpLoadAttachmentABPath(strFileName, 50, strFilePath); string strPath = strFilePath + Attachment; (strPath); DataSet ds = GetExcelData(strPath); //判断⽂件内容是否为空 if (ds == null) { erStartupScript(this, e(), "JsError", "alert('这个⽂件没有数据！');", true); return; } int i_count = 0;//计算⾏数 ccess objDbSQLAccess = new ccess(SqlCon); //导⼊到数据库 try { int result = 0; foreach (DataTable dt in ) { try { string strRow = [0]["⼯⼚"].ToString();//判断⾏是否存在 } catch (Exception) { continue; } //⾏数存在,累加⾏数 i_count++; string strFactory = ""; string strWorkshop = ""; string strQualityDoor = ""; string strFirstPartStructure = ""; string strSecondPartStructure = ""; for (int i = 0; i < ; i++) { strFactory = [i]["⼯⼚"].ToString(); strWorkshop = [i]["车间"].ToString(); strQualityDoor = [i]["质量门"].ToString(); strFirstPartStructure = [i]["⼀级零部件结构"].ToString(); strSecondPartStructure = [i]["⼆级零部件结构"].ToString(); //判断⾏数据是否完整并给出提⽰ if (strFactory == "" || strWorkshop == "" || strQualityDoor == "" || strFirstPartStructure == "" || strSecondPartStructure == "") { int m = i + 1; erStartupScript(this, e(), "JsError", "alert('第" + m + "⾏数据不完整！')", true); return; } else//数据完整则插⼊数据库 { string strSqlDelete = (@" DELETE FROM [Test].[dbo].[QMS_QualiryDoorAndParts] WHERE [Factory]=N'{0}' AND [Workshop]=N'{1}' AND [QualityDoor]=N'{2}'

AND [FirstPartStructure]=N'{3}' AND [SecondPartStructure]=N'{4}' ", strFactory, strWorkshop, strQualityDoor, strFirstPartStructure, strSecondPartStructure); ing_SQLExecuteScalar(strSqlDelete); string strSqlInsert = (@" INSERT INTO [Test].[dbo].[QMS_QualiryDoorAndParts] ([Factory] ,[Workshop] ,[QualityDoor] ,[FirstPartStructure] ,[SecondPartStructure]) VALUES (N'{0}' ,N'{1}' ,N'{2}' ,N'{3}' ,N'{4}') ",strFactory ,strWorkshop ,strQualityDoor,strFirstPartStructure, strSecondPartStructure); result = ing_SQLExecuteScalar(strSqlInsert).funInt_StringToInt(0); } } } if (result == 0 && i_count > 0) { erStartupScript(this, e(), "JsError", "alert('导⼊成功。');", true); } else if (i_count == 0) { erStartupScript(this, e(), "JsError", "alert('EXEC中的列名不符合规则。');", true); } } catch (Exception ex) { erStartupScript(this, e(), "JsError", "alert('Fail:" + e + "');", true); } } catch (Exception exp) { erStartupScript(this, e(), "JsError", "alert('Fail:" + e + "');", true); } }

后台完整实现代码如下：其中;是类似SQLHelper的⼀个引⽤using System;using c;using ;using ;using ;using trols;using ntrols;using ts;using ;using ;using uration;using ;namespace QualityDoorAndPartsCorrespondence{ public partial class QualityDoorAndPartsImport : { #页⾯属性... ///

/// 数据库连接字符串//⽂件中数据库的联接串name ///

private static string SqlCon { get { return "MES-Conn"; } } #页⾯属性... #⽅法... ///

/// 唯⼀需要注意的是，如果⽬标机器的操作系统，是64位的话。 /// 项⽬需要编译为 x86，⽽不是简单的使⽤默认的 Any CPU. ///

/// /// private string GetOleDbConnectionString(string strExcelFileName) { // Office 2007 以及以下版本使⽤. string strJETConnString = ("Provider=.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", strExcelFileName); // xlsx 扩展名使⽤. string strASEConnXlsxString = ("Provider=.12.0;Data Source={0};Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1;"", strExcelFileName); // xls 扩展名使⽤. string strACEConnXlsString = ("Provider=.12.0;Data Source={0};Extended Properties="Excel 8.0;HDR=YES"", strExcelFileName); //其他 string strOtherConnXlsString = ("Provider=.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", strExcelFileName); //尝试使⽤ ACE. 假如不发⽣错误的话，使⽤ ACE 驱动. try { onnection cn = new onnection(strACEConnXlsString); (); (); // 使⽤ ACE return strACEConnXlsString; } catch (Exception) { // 启动 ACE 失败. } // 尝试使⽤ Jet. 假如不发⽣错误的话，使⽤ Jet 驱动. try { onnection cn = new onnection(strJETConnString); (); (); // 使⽤ Jet return strJETConnString; } catch (Exception) { // 启动 Jet 失败. } // 尝试使⽤ Jet. 假如不发⽣错误的话，使⽤ Jet 驱动. try { onnection cn = new onnection(strASEConnXlsxString); (); (); // 使⽤ Jet return strASEConnXlsxString; } catch (Exception) { // 启动 Jet 失败. } // 假如 ACE 与 JET 都失败了，默认使⽤ JET. return strOtherConnXlsString; } ///

/// 获取Excel数据 ///

/// /// private DataSet GetExcelData(string strFilePath) { try { //获取连接字符串 // @"Provider=.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;HDR=YES;"; string strConn = GetOleDbConnectionString(strFilePath); DataSet ds = new DataSet(); using (OleDbConnection conn = new OleDbConnection(strConn)) { //打开连接 (); ble dt = DbSchemaTable(, new object[] { null, null, null, "TABLE" }); // 取得Excel⼯作簿中所有⼯作表

ble schemaTable = DbSchemaTable(, null); OleDbDataAdapter sqlada = new OleDbDataAdapter(); foreach (DataRow dr in ) { try { string strSql = "Select * From [" + dr[2].ToString().Trim() + "]"; if (ns("$")) { OleDbCommand objCmd = new OleDbCommand(strSql, conn); Command = objCmd; (ds, dr[2].ToString().Trim()); } } catch { } } //关闭连接 (); } return ds; } catch (Exception ex) { erClientScriptBlock(, e(), "err", "alert('" + e + "');", false); return null; } } #⽅法... #事件... ///

/// 页⾯加载 ///

/// /// protected void Page_Load(object sender, EventArgs e) { } ///

/// 上传导⼊按钮点击 ///

/// 关闭按钮点击 ///

/// /// protected void btnClose_Click(object sender, EventArgs e) { //关闭页⾯ (""); } #事件... }}

发布者：admin，转转请注明出处：http://www.yc00.com/news/1689309609a228532.html