ClsExcelUpload.cs
6.07 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace MyClassLib
{
public class clsExcelUpload
{
#region ExcelToSQL server 2008R2
/// <summary>
/// 获取文件的路径名称(包括文件名）
/// </summary>
string strPathName = "";
/// <summary>
/// Excel数据插入表格的名称
/// </summary>
string strTableName = "";
/// <summary>
/// 连接字符串
/// </summary>
string ConnectString = "";
/// <summary>
/// 获取Excel文件中的数据到DataTable中
/// </summary>
/// <param name="fileUrl">文件路径名称包括文件名</param>
/// <param name="table">填充表格名称</param>
/// <param name="YesOrNo">首行是标题则为Yes，否则为No</param>
/// <returns></returns>
public DataTable GetExcelDataTable(string fileUrl, string table,string YesOrNo)
{
//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'";
const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR={1}; IMEX=1'";
DataTable dt = null;
//建立连接
OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl,YesOrNo));
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>
/// 将需要导入srcDatatableName中的数据导入到desDatatableName中 函数
/// </summary>
/// <param name="srcDatatableName">从Excel中获取到的数据表格DataTable</param>
/// <param name="ConnecStr">连接目标数据库的连接字符串</param>
/// <param name="desDatatableName">需要导入数据库中表格的名称(默认为当前连接的数据库中的表格)</param>
/// <param name="column">需要插入的目标数据表格的字段数组,这个字段的顺序需要与导入的Excel数据字段匹配</param>
/// <returns>插入成功数据或更新的数据影响行数</returns>
public int InsertDataToDB(System.Data.DataTable srcDatatableName,string ConnecStr, string desDatatableName,string[] column)
{
int i = 0;
try
{
if (srcDatatableName == null)
{
throw new ArgumentNullException("srcDatatableName");
}
if (desDatatableName.Length == 0)
{
throw new ArgumentNullException("desDatatableName");
}
//step1: 获取需要插入的目标表格信息
string strSelectSql = string.Format("select * from {0} ", desDatatableName);
DataTable dt2= CSqlHelper.ExecuteDataset(ConnecStr,CommandType.Text, strSelectSql).Tables[0];
//step2: 判断插入的字段数据，目标表格中是否都有以及插入表格字段有插入数组数量是否相同
foreach (DataColumn dc in dt2.Columns)
{
if (column.Contains(dc.ColumnName) != true)
{
throw new ArgumentException(dc.ColumnName);
}
}
if (srcDatatableName.Columns.Count != column.Length) { throw new ArgumentException("字段与Excel读取的字段数目不符"); }
foreach (DataRow dr in srcDatatableName.Rows)
{
//step3: 合成插入字符串
int Total = srcDatatableName.Columns.Count;
string InsertSql1 = string.Format("Insert into {0} (", desDatatableName);
string InsertSql2 = " ) Values ( ";
for (int k = 0; k < Total; k++)
{
InsertSql1 += column[k] + ",";
InsertSql2 += "'" + srcDatatableName.Columns[k].ToString() + "',";
}
InsertSql1 = InsertSql1.TrimEnd(',');
InsertSql2 = InsertSql2.TrimEnd(',') + " )";
string strSql = InsertSql1 + InsertSql2;
//step4 插入
if (CSqlHelper.ExecuteNonQuery(ConnecStr, CommandType.Text, strSql) == 1)
{
i++;
}
else
{
LogExecute.WriteLineDataLog(string.Format("插入数据失败:{0}",strSql));
}
}
}
catch (Exception ex)
{
LogExecute.WriteExceptionLog("insertDataToDb", ex);
}
return i;
}
#endregion
}
}