发布网友
发布时间：2022-04-19 17:42
共2个回答
懂视网
时间：2022-04-08 02:36
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Web;
5 using System.Web.UI;
6 using System.Web.UI.WebControls;
7 using System.Data;
8 using System.Data.OleDb;
9 using System.Configuration;
10 using System.Data.SqlClient;
11
12 namespace InExcelOutExcel
13 {
14 public partial class ExcelToDB : System.Web.UI.Page
15 {
16 protected void Page_Load(object sender, EventArgs e)
17 {
18 FileSvr fileSvr = new FileSvr();
19 System.Data.DataTable dt = fileSvr.GetExcelDatatable("C:\Users\NewSpring\Desktop\Demo\InExcelOutExcel\InExcelOutExcel\excel\ExcelToDB.xlsx", "mapTable");
20 fileSvr.InsetData(dt);
21 }
22 }
23 class FileSvr
24 {
25 /// <summary>
26 /// Excel数据导入Datable
27 /// </summary>
28 /// <param name="fileUrl"></param>
29 /// <param name="table"></param>
30 /// <returns></returns>
31 public System.Data.DataTable GetExcelDatatable(string fileUrl, string table)
32 {
33 //office2007之前 仅支持.xls
34 //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=‘Excel 8.0;IMEX=1‘;";
35 //支持.xls和.xlsx，即包括office2010等版本的 HDR=Yes代表第一行是标题，不是数据；
36 const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties=‘Excel 12.0; HDR=Yes; IMEX=1‘";
37
38 System.Data.DataTable dt = null;
39 //建立连接
40 OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
41 try
42 {
43 //打开连接
44 if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
45 {
46 conn.Open();
47 }
48
49
50 System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
51
52 //获取Excel的第一个Sheet名称
53 string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
54
55 //查询sheet中的数据
56 string strSql = "select * from [" + sheetName + "]";
57 OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
58 DataSet ds = new DataSet();
59 da.Fill(ds, table);
60 dt = ds.Tables[0];
61
62 return dt;
63 }
64 catch (Exception exc)
65 {
66 throw exc;
67 }
68 finally
69 {
70 conn.Close();
71 conn.Dispose();
72 }
73
74 }
75
76 /// <summary>
77 /// 从System.Data.DataTable导入数据到数据库
78 /// </summary>
79 /// <param name="dt"></param>
80 /// <returns></returns>
81 public int InsetData(System.Data.DataTable dt)
82 {
83 int i = 0;
84 string lng = "";
85 string lat = "";
86 string offsetLNG = "";
87 string offsetLAT = "";
88
89 foreach (DataRow dr in dt.Rows)
90 {
91 lng = dr["LNG"].ToString().Trim();
92 lat = dr["LAT"].ToString().Trim();
93 offsetLNG = dr["OFFSET_LNG"].ToString().Trim();
94 offsetLAT = dr["OFFSET_LAT"].ToString().Trim();
95
96 //sw = string.IsNullOrEmpty(sw) ? "null" : sw;
97 //kr = string.IsNullOrEmpty(kr) ? "null" : kr;
98
99 string strSql = string.Format("Insert into DBToExcel (LNG,LAT,OFFSET_LNG,OFFSET_LAT) Values (‘{0}‘,‘{1}‘,{2},{3})", lng, lat, offsetLNG, offsetLAT);
100
101 string strConnection = ConfigurationManager.ConnectionStrings["ConnectionStr"].ToString();
102 SqlConnection sqlConnection = new SqlConnection(strConnection);
103 try
104 {
105 // SqlConnection sqlConnection = new SqlConnection(strConnection);
106 sqlConnection.Open();
107 SqlCommand sqlCmd = new SqlCommand();
108 sqlCmd.CommandText = strSql;
109 sqlCmd.Connection = sqlConnection;
110 SqlDataReader sqlDataReader = sqlCmd.ExecuteReader();
111 i++;
112 sqlDataReader.Close();
113 }
114 catch (Exception ex)
115 {
116 throw ex;
117 }
118 finally
119 {
120 sqlConnection.Close();
121
122 }
123 //if (opdb.ExcSQL(strSql))
124 // i++;
125 }
126 return i;
127 }
128 }
129 }
运行结果：
将Excle数据导入到数据库
标签：
热心网友
时间：2022-04-07 23:44