c#将Excel数据导入到数据库的实现代码

后端开发|C#.Net教程

c#，Excel，数据库

后端开发-C#.Net教程

c#将Excel数据导入到数据库的实现代码

网站源码借款,ubuntu上安装go,爬虫算动物吗,php 取反值,普洱seo推广lzw

假如Excel中的数据如下：

记录纪念日的网站源码,vscode安装失败怎么办,ubuntu run报错,tomcat 线程数监控,白色长毛爬虫,php抓取html内容,汕头市整站seo优化品牌,网站后台怎么换图片lzw

s扫描器易语言源码,VSCOde里面换行快捷键,ubuntu 引导目录,tomcat 连接时间,爬虫店名称,入侵php网站数据库,大丰区seo优化数据中心lzw

数据库建表如下：

其中Id为自增字段：

代码：

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.Configuration;using System.Data.SqlClient;namespace InExcelOutExcel{ public partial class ExcelToDB : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { FileSvr fileSvr = new FileSvr(); System.Data.DataTable dt = fileSvr.GetExcelDatatable ("C:\\Users\\NewSpring\\Desktop\\Demo\\InExcelOutExcel\\InExcelOutExcel\\excel\\ExcelToDB.xlsx", "mapTable"); fileSvr.InsetData(dt); } } class FileSvr { /// /// /// /// public System.Data.DataTable GetExcelDatatable(string fileUrl, string table) { //office之前 仅支持.xls //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;IMEX=1;"; //支持.xls和.xlsx，即包括office等版本的 HDR=Yes代表第一行是标题，不是数据； const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties=Excel 12.0; HDR=Yes; IMEX=1\"; System.Data.DataTable dt = null; //建立连接 OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl)); try {//打开连接if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed){ conn.Open();}System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//获取Excel的第一个Sheet名称string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();//查询sheet中的数据string strSql = "select * from [" + sheetName + "]";OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);DataSet ds = new DataSet();da.Fill(ds, table);dt = ds.Tables[0];return dt; } catch (Exception exc) {throw exc; } finally {conn.Close();conn.Dispose(); } } /// /// /// public int InsetData(System.Data.DataTable dt) { int i = 0; string lng = ""; string lat = ""; string offsetLNG = ""; string offsetLAT = ""; foreach (DataRow dr in dt.Rows) {lng = dr["LNG"].ToString().Trim();lat = dr["LAT"].ToString().Trim();offsetLNG = dr["OFFSET_LNG"].ToString().Trim();offsetLAT = dr["OFFSET_LAT"].ToString().Trim();//sw = string.IsNullOrEmpty(sw) ? "null" : sw;//kr = string.IsNullOrEmpty(kr) ? "null" : kr;string strSql = string.Format("Insert into DBToExcel (LNG,LAT,OFFSET_LNG,OFFSET_LAT) Values ({0},{1},{2},{3})", lng, lat, offsetLNG, offsetLAT);string strConnection = ConfigurationManager.ConnectionStrings["ConnectionStr"].ToString();SqlConnection sqlConnection = new SqlConnection(strConnection);try{ // SqlConnection sqlConnection = new SqlConnection(strConnection); sqlConnection.Open(); SqlCommand sqlCmd = new SqlCommand(); mandText = strSql; sqlCmd.Connection = sqlConnection; SqlDataReader sqlDataReader = sqlCmd.ExecuteReader(); i++; sqlDataReader.Close();}catch (Exception ex){ throw ex;}finally{ sqlConnection.Close();}//if (opdb.ExcSQL(strSql))// i++; } return i; } }}

运行结果：