c#将Excel数据导入到数据库的实现代码
时间:2024-01-25 21:53:43
假如Excel中的数据如下:
数据库建表如下:
其中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
{
/// <summary>
/// Excel数据导入Datable
/// </summary>
/// <param name="fileUrl"></param>
/// <param name="table"></param>
/// <returns></returns>
public System.Data.DataTable GetExcelDatatable(string fileUrl, string table)
{
//office2007之前 仅支持.xls
//const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
//支持.xls和.xlsx,即包括office2010等版本的 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();
}
}
/// <summary>
/// 从System.Data.DataTable导入数据到数据库
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
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();
sqlCmd.CommandText = 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;
}
}
}
运行结果:
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Sql存储过程游标循环的用法及sql如何使用cursor写一个简单的循环
Python算法练习之二分查找算法的实现
![](https://img.aspxhome.com/file/2023/8/104388_0s.jpg)
在ASP.NET 2.0中操作数据之六十七:在TableAdapters中使用JOINs
![](https://img.aspxhome.com/file/2023/3/126203_0s.png)
sqlserver 多库查询 sp_addlinkedserver使用方法(添加链接服务器)
Go使用Gin+mysql实现增删改查的详细实例
![](https://img.aspxhome.com/file/2023/6/133996_0s.png)
数据库工具sysbench安装教程和性能测试例子
Web开发人员常用速查手册 英文集合推荐
![](https://img.aspxhome.com/file/2023/3/132323_0s.jpg)
利用python实现逐步回归
![](https://img.aspxhome.com/file/2023/4/80584_0s.jpg)
centos7利用yum安装lnmp的教程(linux+nginx+php7.1+mysql5.7)
Python全局锁中如何合理运用多线程(多进程)
![](https://img.aspxhome.com/file/2023/1/110441_0s.png)
Windows存储 SQL行溢出 差异备份及疑问
ElasticSearch核心概念
![](https://img.aspxhome.com/file/2023/4/114694_0s.png)
mac下如何将python2.7改为python3
![](https://img.aspxhome.com/file/2023/2/112822_0s.jpg)
python实现分页效果
![](https://img.aspxhome.com/file/2023/2/80552_0s.jpg)
Python实现的简单万年历例子分享
swiper Scrollbar滚动条组件详解
![](https://img.aspxhome.com/file/2023/2/135922_0s.jpg)
2行Python实现给图片加水印效果
![](https://img.aspxhome.com/file/2023/1/104981_0s.jpg)
JavaScript 判断浏览器类型及版本
django数据库迁移migration实现
![](https://img.aspxhome.com/file/2023/5/127155_0s.png)
Mysql经典高逼格/命令行操作(速成)(推荐)
![](https://img.aspxhome.com/file/2023/9/85799_0s.png)