C#编写winform实现读取Excel并将其数据更新到Mysql数据库
**
C#编写winform实现读取Excel并将其数据更新到Mysql数据库
**
看了网上挺多的winform读取Excel,后来根据各路大神的指引,我也实现了读取Excel并将其数据更新到数据库的功能,以下便是我的代码,初次发帖,请多关照。
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
using MySql.Data.MySqlClient;
namespace ReadExcel
{
public partial class Form1 : Form
{
//数据库配置
static String connetStr = "server=127.0.0.1;port=3306;user=root;password=123456; database=Location;SslMode = none;";
// server=127.0.0.1/localhost 代表本机,端口号port默认是3306可以不写
MySqlConnection conn = new MySqlConnection(connetStr);
public Form1()
{
InitializeComponent();
}
//点击按钮
private void readExcelBtn_Click(object sender, EventArgs e)
{
DataTable ds = getData().Tables[0];
for (int j = 0; j < ds.Rows.Count; j++)
{
string sn = ds.Rows[j][0].ToString(); //Rows[i]["col1"]表示i行"col1"字段
string name = ds.Rows[j][1].ToString();
string quantity = ds.Rows[j][2].ToString();
insertMysqlDB(sn, name, quantity);//更新数据到数据库
}
dataGridView1.DataSource = null; //每次打开清空内容
dataGridView1.DataSource = ds;
}
private DataSet getData()
{
//打开文件
OpenFileDialog file = new OpenFileDialog();
file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
file.Multiselect = false;
if (file.ShowDialog() == DialogResult.Cancel)
return null;
//判断文件后缀
var path = file.FileName;
string fileSuffix = System.IO.Path.GetExtension(path);
if (string.IsNullOrEmpty(fileSuffix))
return null;
using (DataSet ds = new DataSet())
{
//判断Excel文件是2003版本还是2007版本
string connString = "";
if (fileSuffix == ".xls")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
//读取文件
string sql_select = " SELECT * FROM [Sheet1$]";
using (OleDbConnection conn = new OleDbConnection(connString))
using (OleDbDataAdapter cmd = new OleDbDataAdapter(sql_select, conn))
{
conn.Open();
cmd.Fill(ds);
}
if (ds == null || ds.Tables.Count <= 0) return null;
return ds;
}
}
private void insertMysqlDB(string sn,string name,string quantity)
{
//插入数据到数据库
if (conn.State == ConnectionState.Closed)
conn.Open();//打开通道,建立连接,可能出现异常,使用try catch语句
MySqlTransaction transaction = conn.BeginTransaction();//事务必须在try外面赋值不然catch里的transaction会报错:未赋值
try
{
//在这里使用代码对数据库进行增删查改
string sql1 = "update product set name='" + name + "',quantity='" + quantity + "'where sn='"+sn+"'";
MySqlCommand cmd = new MySqlCommand(sql1, conn);
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Console.WriteLine(ex.Message);
transaction.Rollback();//事务ExecuteNonQuery()执行失败报错
conn.Close();
}
finally
{
if (conn.State != ConnectionState.Closed)
{
transaction.Commit();//事务要么回滚要么提交,即Rollback()与Commit()只能执行一个
//conn.Close();
}
}
}
}
}
以下是运行后的示图,该图中首先将Excel中的数据读出到dataGridView中,然后将数据更行到数据库的表中,更加便捷的更新数据库信息。
以上资源链接在此:https://download.****.net/download/dadongge000/10988961