C# 获取excel架构并的导入sqlserver的方法
时间：2021-07-01 10:21:17
帮助过：1人阅读
System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
namespace WindowsFormsApplication1
{
public partial class Form3 : Form
{
public Form3()
{
InitializeComponent();
}
private void button1_Click(
object sender, EventArgs e)
{
string connString =
"server=localhost;database=Test;uid=sa;pwd=123456";
OpenFileDialog ofd =
new OpenFileDialog();
if (ofd.ShowDialog() ==
DialogResult.OK)
{
TransferData(ofd.FileName, connString);
}
}
public void TransferData(
string excelFile,
string connectionString)
{
try
{
string fileType =
Path.GetExtension(excelFile);
//获取全部数据
string strConn=
"";
if (fileType ==
".xls")
{
strConn =
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFile +
";" +
"Extended Properties=Excel 8.0;";
}
else
{
strConn =
"Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=" + excelFile +
";" +
";Extended Properties=Excel 12.0";
}
OleDbConnection conn =
new OleDbConnection(strConn);
conn.Open();
DataSet ds =
new DataSet();
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new object[] {
null,
null,
null,
"Table" });
for (
int i =
0; i < dt.Rows.Count; i++
)
{
string sheetName = dt.Rows[i][
"Table_Name"].ToString();
if (sheetName.Contains(
"$") && !sheetName.Replace(
"‘",
"").EndsWith(
"$"))
{
continue;
}
string strExcel =
string.Format(
"select * from [{0}]", sheetName);
OleDbDataAdapter myCommand =
new OleDbDataAdapter(strExcel, conn);
DataSet dsItem =
new DataSet();
myCommand.Fill(dsItem, sheetName);
dsItem.Tables[0].TableName = sheetName.TrimEnd(
‘$‘).Trim();
//读取出来的表明会自动添加‘$‘符号，需要去掉。
ds.Tables.Add(dsItem.Tables[
0].Copy());
}
for (
int i =
0; i < ds.Tables.Count; i++
)
{
string sheetName =
ds.Tables[i].TableName;
string strSql =
string.Format(
"if object_id(‘{0}‘) is null create table {0}(", sheetName);
foreach (System.Data.DataColumn c
in ds.Tables[
0].Columns)
{
strSql +=
string.Format(
"[{0}] varchar(255),", c.ColumnName);
}
strSql = strSql.Trim(
‘,‘) +
")";
using (SqlConnection sqlConn =
new SqlConnection(connectionString))
{
sqlConn.Open();
using (SqlCommand cmd =
sqlConn.CreateCommand())
{
cmd.CommandText =
strSql;
cmd.ExecuteNonQuery();
}
}
using (SqlBulkCopy bcp =
new SqlBulkCopy(connectionString))
{
bcp.BatchSize =
100;
bcp.DestinationTableName =
sheetName;
//建立excel和sqlserver表之间的映射关系
bcp.ColumnMappings.Add(
0,
0);
bcp.ColumnMappings.Add(1,
1);
bcp.ColumnMappings.Add(2,
2);
bcp.WriteToServer(ds.Tables[i]);
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
}
http://blog.csdn.net/jinjazz/article/details/2650506
http://www.cnblogs.com/advocate/archive/2010/04/12/1710383.html
http://developer.51cto.com/art/201302/380622.htm
C# 获取excel架构并的导入sqlserver的方法
标签：
