using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
//ExcelName Excel文件名(路径) SheetName Sheet名
//TableName 数据库中表名 ConnectionString 数据库连接串
//IsExistColumn 是否存在列(false)
public bool ExcelToTable(string ExcelName, string SheetName, string TableName, string ConnectionString, bool IsExistColumn)
{
//创建一个数据链接
bool boolResult = false; SqlConnection sourceConn = new SqlConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + ExcelName + ";Extended Properties=Excel 8.0");
SqlConnection destConn = new SqlConnection(ConnectionString);
destConn.Open();
SqlCommand destcomm = new SqlCommand();
destcomm.Connection = destConn;
string Sql = " SELECT * FROM [" + SheetName + "$]";
DataSet ds = new DataSet(); //打开Excel
try
{
sourceConn.Open();
SqlDataAdapter oleDA = new SqlDataAdapter(Sql, sourceConn);
oleDA.Fill(ds, TableName);
sourceConn.Close();
}
catch (Exception err)
{
//throw err;
boolResult = false;
} int l = 0; //当前批数量
int m = ds.Tables[0].Rows.Count; //剩余记录数量
Sql = "";
try
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Sql = Sql + "INSERT INTO " + TableName + " VALUES(";
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
Sql = Sql + "'" + ds.Tables[0].Rows[i][j].ToString().Trim() + "',";
}
Sql = Sql.Substring(0, Sql.Length - 1); //去掉最后一个逗号并且添上后括号
Sql = Sql + ")"; l++; //判断是否达到批数量
m--;
if (l == 10 || m == 0)
{
destcomm.CommandText = Sql;
destcomm.ExecuteNonQuery(); //执行
Sql = "";
}
} boolResult = true;
}
catch (Exception ee)
{
string error = ee.Message;
boolResult = false;
}
return boolResult;
} private void button1_Click(object sender, System.EventArgs e)
{
if (openFileDialog1.ShowDialog(this) == DialogResult.OK)
{
System.IO.FileInfo ObjTmp = new System.IO.FileInfo(openFileDialog1.FileName);
ExcelToTable(ObjTmp.FullName+openFileDialog1.FileName,"Sheet1","Table_12345",)
}我这段程序的是实现EXCEL导入SQL,数据库也连好了,表格也和EXCWL对好了,但是执行不了,请问哪里需要修改呢,帮下我,谢谢
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
//ExcelName Excel文件名(路径) SheetName Sheet名
//TableName 数据库中表名 ConnectionString 数据库连接串
//IsExistColumn 是否存在列(false)
public bool ExcelToTable(string ExcelName, string SheetName, string TableName, string ConnectionString, bool IsExistColumn)
{
//创建一个数据链接
bool boolResult = false; SqlConnection sourceConn = new SqlConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + ExcelName + ";Extended Properties=Excel 8.0");
SqlConnection destConn = new SqlConnection(ConnectionString);
destConn.Open();
SqlCommand destcomm = new SqlCommand();
destcomm.Connection = destConn;
string Sql = " SELECT * FROM [" + SheetName + "$]";
DataSet ds = new DataSet(); //打开Excel
try
{
sourceConn.Open();
SqlDataAdapter oleDA = new SqlDataAdapter(Sql, sourceConn);
oleDA.Fill(ds, TableName);
sourceConn.Close();
}
catch (Exception err)
{
//throw err;
boolResult = false;
} int l = 0; //当前批数量
int m = ds.Tables[0].Rows.Count; //剩余记录数量
Sql = "";
try
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Sql = Sql + "INSERT INTO " + TableName + " VALUES(";
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
Sql = Sql + "'" + ds.Tables[0].Rows[i][j].ToString().Trim() + "',";
}
Sql = Sql.Substring(0, Sql.Length - 1); //去掉最后一个逗号并且添上后括号
Sql = Sql + ")"; l++; //判断是否达到批数量
m--;
if (l == 10 || m == 0)
{
destcomm.CommandText = Sql;
destcomm.ExecuteNonQuery(); //执行
Sql = "";
}
} boolResult = true;
}
catch (Exception ee)
{
string error = ee.Message;
boolResult = false;
}
return boolResult;
} private void button1_Click(object sender, System.EventArgs e)
{
if (openFileDialog1.ShowDialog(this) == DialogResult.OK)
{
System.IO.FileInfo ObjTmp = new System.IO.FileInfo(openFileDialog1.FileName);
ExcelToTable(ObjTmp.FullName+openFileDialog1.FileName,"Sheet1","Table_12345",)
}我这段程序的是实现EXCEL导入SQL,数据库也连好了,表格也和EXCWL对好了,但是执行不了,请问哪里需要修改呢,帮下我,谢谢
解决方案 »
- C#里有没有activeX控件测试容器?
- 继续下午的问题
- 请教:winform中添加了一个菜单栏,如何将原来的坐标原点(0,0)自动向下移动到菜单栏下方,像vc那样自动将客户区原点下移menu.height。
- 求个思路
- 如何判断在datatable某行是否存在
- 请问为什么动态生成的table的边框现实的时候很宽,打印时更宽,有什么解决的办法,请指点代码不合理处
- System.Array不包含"Zip"的定义
- 对IE托管windows窗体及windows用户控件的兄弟,进来讨论(有成功的也有失败的经验)!!
- 请问在C#中如何获取屏幕的大小?
- C#的Winform使用AppConfig配置文件怎么不行?
- 关于RichTextBox的疑惑
- c#读取xml 中的datapacket?
excel操作是需要另外导入office的using的,我感觉这个比较容易出错.
你再看看是不是导入的版本有错,有很多版本的.
我一个朋友说
ExcelToTable(ObjTmp.FullName+openFileDialog1.FileName,"Sheet1","Table_12345",)
这段代码少东西,但是他没说完就下了,我急着用,只好来这求助下
【
SqlConnection sourceConn = new SqlConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" + ExcelName + ";Extended Properties=Excel 8.0");
】
就出错了,错误提示:【 不支持关键字: “provider”。】我用的是Viual Studio 2008
接上面,再调试后终于搞定。我把你的这个链接修改为OleDbConnection后,Error1消失;
但是冒出新的错误,【找不到可安装的 ISAM】---》Error2细看之后,发现是链接字符串错误了,正确的应该是:
【
OleDbConnection sourceConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ExcelName+";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';");
】
尤其注意,Data Source之间是一个空格,不是2个,Extended Properties直接也只能是一个空格,修改后,Error2消失。OK,自此程序调试完毕,我已经测试过了,数据可以正确导入到数据库。