求一份用.net语言实现的 excel导入MYSQL数据库代码,就是实现一个浏览button然后选入要导入的文件,然后一个导入button实现EXCEL中的内容导入到MYSQL数据库中
解决方案 »
- DirectX.AudioVideoPlayback 出现试图访问已卸载的AppDomain.
- 难道客户端的button控件加上runat="server"属性后,就不能执行JavaScript代码了吗?
- 导出到Word和导出到Excel的问题
- 直接引用本地类和使用WEBSERVER生成代理类后再调用有什么区别啊,为什么好好的程序就运行异常了呢?
- Excel 导入问题
- 为什么 Type type = Type.GetType( "ChinaPWS.Data.SqlDataProvider, ChinaPWS.SqlDataProvider" ) 的值为<未定义的值>.?
- silverlight中,datagrid无法选中多行?
- 请求高手帮助!
- 如何配置iis使其能够解释asp.net
- 一个奇怪的问题。请各路大侠关注!谢谢!
- 帮我分析一下原因! datatable插入问题!
- 从一个页面把主键值传到另一个页面,怎么根据主键值把数据库表中的值一个个赋值到每个text中
FileInfo fileInfo = new FileInfo(Path.Combine(TEMP_DIRECTORY, Guid.NewGuid().ToString() + ".xlsx"));
try
{
using (Stream xlsStream = new BufferedStream(fileInfo.Open(FileMode.CreateNew), 1024))
{
xlsStream.Write(EmployeeBytes, 0, EmployeeBytes.Length);
}
string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileInfo.FullName + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"";
string strExcel = "select * from [Chengdu Resources$]"; using (OleDbDataAdapter adaptor = new OleDbDataAdapter(strExcel, strConn))
{
DataSet ds = new DataSet();
adaptor.Fill(ds);
Excel_UserInfo = ds.Tables[0];
}
}读出的DataTable循环插入数据库 记得用事务
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
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();
}
}
}
SqlBulkCopy