我做了一个小型的WinForm 程序,主要汲及到数据的导入(导入Excel)
把Excel表的数据导到数据库,先把Excel表的数据和数据库的做比较,如果没有就插入数据库
否则的话就把数据写入到另外一张Excel表里面,代码如下:
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Execl files (*.xls)|*.xls";
openFileDialog.ShowDialog(); //打开系统对话框
if (openFileDialog.FileName.IndexOf(".") < 0) return;//点击取消按钮 string path = openFileDialog.FileName;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable table = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = table.Rows[0][2].ToString();
string strExcel = "select * from " + " [" + tableName + "]";
OleDbCommand cmd = new OleDbCommand(strExcel, conn);
OleDbDataReader readerExcel = cmd.ExecuteReader();//读取Excel表的数据
int count = 0;
string b = "";
while (readerExcel.Read())
{
int no = Convert.ToInt32(readerExcel[0]);
string sqlSelect = "select customer,cellphone from Customer_Register where id=" + no + "";
if (DBHelp.getAll(sqlSelect).Tables[0].Rows.Count == 0)//表示数据没有重复
{
string sql = "insert into Customer_Register values(" + readerExcel[0] + ",'" + readerExcel[1] + "','" + readerExcel[2] + "','" + readerExcel[3] + "','" + readerExcel[4] + "','" + readerExcel[5] + "','" + readerExcel[6] + "','" + readerExcel[7] + "','" + readerExcel[8] + "','" + readerExcel[9] + "','" + readerExcel[10] + "','" + readerExcel[11] + "','" + readerExcel[12] + "','" + readerExcel[13] + "','" + readerExcel[14] + "','" + readerExcel[15] + "','" + readerExcel[16] + "','" + readerExcel[17] + "','" + readerExcel[18] + "','" + readerExcel[19] + "')";
DBHelp.CURD(sql);
}
else
{
count++;//如果有重复的记录就不进行插入
b += no+",";
}
}
conn.Close();
readerExcel.Close();
if (count > 0)
{
MessageBox.Show(count + "条数据重复","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
string [] a = b.Split(',');
for (int i = 0; i < a.Length; i++)
{
if (a[i] != "")
{
//把重复的记录写到Excel表
string sql = "select * from Customer_Register where id = '"+Convert.ToInt32(a[i])+"'"; }
}
}
怎样在红色的代码后面把数据写入到Excel里,不能用Excel组件,在后台默默在写到Excel表
把Excel表的数据导到数据库,先把Excel表的数据和数据库的做比较,如果没有就插入数据库
否则的话就把数据写入到另外一张Excel表里面,代码如下:
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Execl files (*.xls)|*.xls";
openFileDialog.ShowDialog(); //打开系统对话框
if (openFileDialog.FileName.IndexOf(".") < 0) return;//点击取消按钮 string path = openFileDialog.FileName;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable table = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = table.Rows[0][2].ToString();
string strExcel = "select * from " + " [" + tableName + "]";
OleDbCommand cmd = new OleDbCommand(strExcel, conn);
OleDbDataReader readerExcel = cmd.ExecuteReader();//读取Excel表的数据
int count = 0;
string b = "";
while (readerExcel.Read())
{
int no = Convert.ToInt32(readerExcel[0]);
string sqlSelect = "select customer,cellphone from Customer_Register where id=" + no + "";
if (DBHelp.getAll(sqlSelect).Tables[0].Rows.Count == 0)//表示数据没有重复
{
string sql = "insert into Customer_Register values(" + readerExcel[0] + ",'" + readerExcel[1] + "','" + readerExcel[2] + "','" + readerExcel[3] + "','" + readerExcel[4] + "','" + readerExcel[5] + "','" + readerExcel[6] + "','" + readerExcel[7] + "','" + readerExcel[8] + "','" + readerExcel[9] + "','" + readerExcel[10] + "','" + readerExcel[11] + "','" + readerExcel[12] + "','" + readerExcel[13] + "','" + readerExcel[14] + "','" + readerExcel[15] + "','" + readerExcel[16] + "','" + readerExcel[17] + "','" + readerExcel[18] + "','" + readerExcel[19] + "')";
DBHelp.CURD(sql);
}
else
{
count++;//如果有重复的记录就不进行插入
b += no+",";
}
}
conn.Close();
readerExcel.Close();
if (count > 0)
{
MessageBox.Show(count + "条数据重复","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
string [] a = b.Split(',');
for (int i = 0; i < a.Length; i++)
{
if (a[i] != "")
{
//把重复的记录写到Excel表
string sql = "select * from Customer_Register where id = '"+Convert.ToInt32(a[i])+"'"; }
}
}
怎样在红色的代码后面把数据写入到Excel里,不能用Excel组件,在后台默默在写到Excel表
gstrSql = "科目コード,金額,摘要1,摘要2" & vbCr For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
gstrSql = gstrSql & SetNullToEmpty(ds.Tables(0).Rows(i)("科目コード")) & ","
gstrSql = gstrSql & SetNullToEmpty(ds.Tables(0).Rows(i)("金額")) & ","
gstrSql = gstrSql & SetNullToEmpty(ds.Tables(0).Rows(i)("摘要1")) & ","
gstrSql = gstrSql & SetNullToEmpty(ds.Tables(0).Rows(i)("摘要2")) & vbCr Next File.WriteAllText(strCSVpath, gstrSql, System.Text.Encoding.GetEncoding("shift-jis"))看看这个吧,VB的,希望对你有帮助
gstrSql = "科目コード,金額,摘要1,摘要2" & vbCr For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
gstrSql = gstrSql & SetNullToEmpty(ds.Tables(0).Rows(i)("科目コード")) & ","
gstrSql = gstrSql & SetNullToEmpty(ds.Tables(0).Rows(i)("金額")) & ","
gstrSql = gstrSql & SetNullToEmpty(ds.Tables(0).Rows(i)("摘要1")) & ","
gstrSql = gstrSql & SetNullToEmpty(ds.Tables(0).Rows(i)("摘要2")) & vbCr Next File.WriteAllText(strCSVpath, gstrSql, System.Text.Encoding.GetEncoding("shift-jis"))
我这个程序是用C#做的
保存路径可以在后台直接指定,比如:c\\Excel_File\\abc.xls
用代码直接把文件写到这个文件里,这个文件事先不存在
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);2、CREATE TABLE tabName,创建表,语法与SQL基本相同3、INSERT INTO 插入数据.语法与SQL相同
private void button3_Click(object sender, EventArgs e)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='d:\\333.xls';Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = strConn;
conn.Open(); new OleDbCommand("create table abc (name varchar(80) null,sex varchar(2) null,age int null)", conn).ExecuteNonQuery();
new OleDbCommand("insert into abc (name,sex,age) values ('wang','b',30)", conn).ExecuteNonQuery();
conn.Close();
}
该属性在外部数据源或用低版本的 Microsoft Jet 所创建的数据库中不受支持
我 用的是Access2003 Office2003,版本应该不会低啊