C#编写,把excel 中的数据导入Sql server ,请高手指教,最好有代码。
解决方案 »
- (急!急!急!)后台生成控件的二级联动的问题
- string str="null"
- 在C#中怎么修改记事本中的内容?比如我想修改记事本中的某一行?
- 开始学习asp.net
- iframe交互问题
- 用C# 获取路由相关信息
- 在WinForm中,已知DataGrid行号i和列号j,怎么取此行此列的数据?
- 关于写应用程序时如何得到我程序中某个文件的绝对路径?
- 我有个字符串filename="1234.jpg",我要判断点的位置,就是要在'.'的前面加'_s',变成1234_s.jpg,如何做
- 请问高手们哪有c#可用的mp3 ,vcd播放控件??(给分)
- visual studio 2005开发平台问题
- c#获取word中两个标签的内容
//选择EXCEL文档
private void button2_Click(object sender, EventArgs e)
{
openFileDialog1.Filter = "Excel2003(*.xls)|*.xls|Excel2007(*.xlsx)|*.xlsx";
openFileDialog1.AddExtension = true;
openFileDialog1.Title = "数据导入";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
ExcelFileName.Text = openFileDialog1.FileName;
}
}
//导入数据
private void button1_Click(object sender, EventArgs e)
{
string ConnectionString = "database=" + dataName.Text.Trim() + ";Server=" + ServiceIp.Text.Trim() + "," + ServicePort.Text.Trim() + ";User ID=" + UserName.Text.Trim() + ";Password=" + PassWord.Text.Trim() + ";Persist Security Info=True";TransferData(ExcelFileName.Text.ToString().Trim(), "Sheet1", ConnectionString);
}
private void TransferData(string excelFile, string sheetName, string connectionString)
{
if (this.ExcelFileName.Text.Trim() == string.Empty)
{
this.ExcelFileName.Focus();
return;
}
int n = 0;
int RepeatData = 0;
int Sum = 0;
string ExcelStrConn = string.Empty;
int i = this.ExcelFileName.Text.Trim().IndexOf(".") + 1;
string StrTemp = this.ExcelFileName.Text.Trim().Substring(i);
DataSet ds = new DataSet();
try
{
if(StrTemp == "xls")
ExcelStrConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
if(StrTemp == "xlsx")
ExcelStrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";
OleDbConnection ExcelConn = new OleDbConnection(ExcelStrConn);
ExcelConn.Open();
string StrExcel = string.Format("select * from [{0}$]", sheetName);
OleDbDataAdapter adapter = new OleDbDataAdapter(StrExcel, ExcelConn);
adapter.Fill(ds, sheetName); SqlConnection conn = new SqlConnection(connectionString);
conn.Open(); progressBar1.Maximum = ds.Tables[0].Rows.Count;
progressBar1.Minimum = 0;
progressBar1.Step = 1;
SqlCommand comm = null;
SqlCommand commType = null;
foreach (DataRow dr in ds.Tables[0].Rows)
{
progressBar1.Value++;
string strID = dr[0].ToString().Trim();
if (strID == "")
continue;
comm = new SqlCommand("select count(*) from Product where ID='" + strID + "'", conn);
if (Convert.ToInt16(comm.ExecuteScalar()) == 0)
{
comm = new SqlCommand("insert into Product(ID,Name,Price) values(@ID,@Name,@Price)", conn); SqlParameter mypar = new SqlParameter(); mypar = new SqlParameter("@ID", dr[0].ToString().Trim());
comm.Parameters.Add(mypar);
mypar = new SqlParameter("@Name", dr[1].ToString().Trim());
comm.Parameters.Add(mypar);
mypar = new SqlParameter("@Price", dr[2].ToString().Trim());
comm.Parameters.Add(mypar);
comm.ExecuteNonQuery();
}
else
{
RepeatData++;
}
}
comm.Dispose();
conn.Dispose();
conn.Close();
ExcelConn.Dispose();
ExcelConn.Close(); Sum = ds.Tables[0].Rows.Count - RepeatData;
MessageBox.Show("已成功导入 " + Sum + " 条数据,其中 " + RepeatData + " 条数据由于编号重复未导成功!", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "出错", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
linq to xml 解析xml
linq to sql 保存到sql
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;IMEX=1';data source=" & strPath
'查询语句
Dim strSql As String = "SELECT * FROM [Page1$]"
Dim ds As New DataSet()
Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSql, strConn)
'填充DataSet
Try
da.Fill(ds, "ds")
Catch ex As Exception
Throw ex
End Try
Dim dtEmp As DataTable = ds.Tables("ds")
Return dtEmp
End Function
strPath 为Excel路径
这个方法可以读出excel 的数据为Datatable 你只需要把datatable数据循环插入数据库里了
namespace WindowsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
} private void button1_Click(object sender, EventArgs e)
{
//测试,将excel中的sheet1导入到sqlserver中
string connString = "server=localhost;uid=sa;pwd=sqlgis;database=master";
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
if (fd.ShowDialog() == DialogResult.OK)
{
TransferData(fd.FileName, "sheet1", connString);
}
} public void TransferData(string excelFile, string sheetName, string connectionString)
{
DataSet ds = new DataSet();
try
{
//获取全部数据
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName); //如果目标表不存在则创建
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 (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strSql;
command.ExecuteNonQuery();
sqlconn.Close();
}
//用bcp导入数据
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.BatchSize = 100;//每次传输的行数
bcp.NotifyAfter = 100;//进度提示的行数
bcp.DestinationTableName = sheetName;//目标表
bcp.WriteToServer(ds.Tables[0]);
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
} } //进度显示
void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
{
this.Text = e.RowsCopied.ToString();
this.Update();
}
}
} 上面的TransferData基本可以直接使用,如果要考虑周全的话,可以用oledb来获取excel的表结构,并且加入ColumnMappings来设置对照字段,这样效果就完全可以做到和sqlserver的dts相同的效果了。
1、将Excel视为一个数据源,使用OLEDBConnection连接,打开后读取数据,然后插入到SQL的表中;
2、使用OfficeApplication组件,打开Excel工作簿,循环读取Sheet中的数据,然后插入到SQL中;上述2个方法1各有一个缺陷:
1、如果Excel中的数据不规则,有可能无法读出数据(毕竟Excel的Sheet不是数据库中的表,单元格中的数据很可能遵守第一范式,即一个列中有多种数据);
2、读取数据太慢;本人有如下方法,即保证数据完整,又能提高速度:
1、使用高效操作字符串的Delphi编制读取Excel数据的窗体,编译成DLL(中间使用剪切板技术,可以快速将Excel中的数据转换成字符串,再粘贴回来,进行解析);
其中导入数据的函数设计成函数指针,作为DLL函数的参数;
2、使用c#设计导入数据的函数,定义成委托,作为DLL的传入参数;