using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Addmysql
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
String key = "";
String value = "";
DataTable _dt = null;
private void button1_Click(object sender, EventArgs e)
{
_dt = new DataTable();
string err = "";
ExcelToTable tab = new ExcelToTable();
try
{
//初始化一个OpenFileDialog类
OpenFileDialog fileDialog = new OpenFileDialog();
//判断用户是否正确的选择了文件
if (fileDialog.ShowDialog() == DialogResult.OK)
{
//获取用户选择文件的后缀名
string extension = Path.GetExtension(fileDialog.FileName);
//声明允许的后缀名
string[] str = new string[] { ".xls", ".xlsx" };
if (!str.Contains(extension))
{
MessageBox.Show("不是Excel!");
}
else
{
System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
watch.Start();//开始计时
this.textBox1.Text = fileDialog.FileName;
DataSet dt = new DataSet();
dt = tab.ExcelToDS(fileDialog.FileName, out err);
_dt = dt.Tables[0];
int a = _dt.Rows.Count;
int columnNum = 0;
columnNum = _dt.Columns.Count;
if (columnNum > 0)
{
this.label1.Text = "总:" + a + "条 " + columnNum + "列";
//for (int i = 0; i < columnNum; i++)
//{
// if (String.IsNullOrEmpty(key))
// {
// key = dt.Tables[0].Columns[i].ColumnName;
// value = getrow(dt.Tables[0], key);
// }
// else
// {
// string _key = dt.Tables[0].Columns[i].ColumnName;
// string _value = getrow(dt.Tables[0], _key);
// if (String.IsNullOrEmpty(_value))
// {
// _value = "0";
// }
// key = key + "," + _key;
// value = value + "," + _value;
// }
// //dataGridView1.Columns.Add(num, num);
//}
}
//this.dataGridView1.DataSource = null;
this.dataGridView1.DataSource = _dt;
watch.Stop();//停止计时
TimeSpan ts2 = watch.Elapsed;
this.label3.Text = " 用时:" + ts2.TotalMilliseconds + "毫秒";
}
}
}
catch (Exception ex)
{
err = err + ex.Message;
}
if (!String.IsNullOrEmpty(err))
{
MessageBox.Show(err);
}
}
private String getrow(DataTable dt, string Columns)
{
String start = "";
foreach (DataRow dr in dt.Rows)
{
start = dr[Columns].ToString(); //语句1
}
return start;
}
private void button2_Click(object sender, EventArgs e)
{
sqlHelp cls2 = new sqlHelp();
int a = 0;
try
{
string str = this.textBox2.Text;
String _str = this.textBox3.Text;
if (string.IsNullOrEmpty(str))
{
MessageBox.Show("请输入数据库链接");
}
if (string.IsNullOrEmpty(_str))
{
MessageBox.Show("请输入需要导入的表名称");
}
//string query = string.Format("insert into" + _str + "(" + key + ")values(" + value + ")");
//cls.InsertByDataTable(_dt, str, _str,out a);
a = cls2.BulkInsert(str, _dt, _str);
if (a > 0)
{
this.label6.Text = "本次导入:" + a;
MessageBox.Show("导入成功!" + "本次导入:" + a);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Form1_Load(object sender, EventArgs e)
{
string M_str_sqlcon = "server=192.168.1.242;port=3307;User Id=root;password=xws8417;Database=axcf";//连接MySQL的字符串
this.textBox2.Text = M_str_sqlcon;
}
private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
{
var grid = sender as DataGridView;
var rowIdx = (e.RowIndex + 1).ToString();
var centerFormat = new StringFormat()
{
// right alignment might actually make more sense for numbers
Alignment = StringAlignment.Center,
LineAlignment = StringAlignment.Center
};
var headerBounds = new Rectangle(e.RowBounds.Left, e.RowBounds.Top, grid.RowHeadersWidth, e.RowBounds.Height);
e.Graphics.DrawString(rowIdx, this.Font, SystemBrushes.ControlText, headerBounds, centerFormat);
}
}
}到此结束:下载地址:https://download.csdn.net/download/goodchangyong/10310468
C# Excel 导入mysql数据库
最新推荐文章于 2021-03-03 19:42:00 发布