Target:
1 Import Excel
2 Show data in DataGridView
3 Save into MySQL
Materials
-
data.xls - there are 13 columns
data.xls
- student.sql - based on the data.xls file to create a 'student' table
CREATE TABLE `student` (
`id` int(11) NOT NULL,
`studentid` varchar(255) NOT NULL,
`cname` varchar(255) NOT NULL,
`ename` varchar(255) NOT NULL,
`program` varchar(255) NOT NULL,
`gender` varchar(1) NOT NULL,
`eoffer` varchar(255) NOT NULL,
`coffer` varchar(255) NOT NULL,
`status` varchar(255) NOT NULL,
`place` varchar(255) NOT NULL,
`numoffer` int(11) NOT NULL,
`eofferlist` text NOT NULL,
`cofferlist` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `student`
--
ALTER TABLE `student`
ADD PRIMARY KEY (`id`);
--
-- 在导出的表使用AUTO_INCREMENT
--
--
-- 使用表AUTO_INCREMENT `student`
--
ALTER TABLE `student`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
- Import student.sql file
Import student.sql file
Select database “workshop2”， if no, create it by yourself.
Import -> Choose File -> Go
The result is like as below.
student table sturcture
- Create C# UI
C# UI
Select button – to select Excel file.
Show Info button – to show the percentage result.
Result label – Result text.
DataGridView – to show the result.
- Add new cs file.
Add new cs file
Right click the project -> Add -> New Item...
Connector.cs
Create Connector.cs to deal with the DB.
// Connector.cs Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//** add the two statements **//
using System.Data;
using MySql.Data.MySqlClient;
namespace CSWithEexcel
{
class Connector
{
/// <summary>
/// Create MySQL connector
/// </summary>
/// <returns>Return MySqlConnection</returns>
public MySqlConnection getmysqlcon()
{
//sql connection stringcha, nge to your own information
string M_str_sqlcon = "Host = 127.0.0.1;Database = workshop2;username = root;password = ;charset=utf8";
MySqlConnection myCon = new MySqlConnection(M_str_sqlcon);
//initialize the connection
return myCon;
}
/// <summary>
/// Execute MySqlCommand
/// </summary>
/// <param name="M_str_sqlstr">SQL string</param>
public void getmysqlcom(string M_str_sqlstr)
{
MySqlConnection mysqlcon = this.getmysqlcon();
// get sql connection
mysqlcon.Open();
// open connection
MySqlCommand commn = new MySqlCommand("set names utf-8", mysqlcon);
MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
mysqlcom.ExecuteNonQuery();
mysqlcom.Dispose();
mysqlcon.Close();
mysqlcon.Dispose();
}
/// <summary>
/// clean student table
/// </summary>
public void cleanStudentTable()
{
string sql = "TRUNCATE TABLE student"; // clean student table sql string
MySqlConnection mysqlcon = this.getmysqlcon();
mysqlcon.Open();
MySqlCommand commn = new MySqlCommand("set names utf-8", mysqlcon);
MySqlCommand mysqlcom = new MySqlCommand(sql, mysqlcon);
mysqlcom.ExecuteNonQuery();
mysqlcom.Dispose();
mysqlcon.Close();
mysqlcon.Dispose();
}
/// <summary>
/// get the result from MySQL And do calculation
/// </summary>
/// <param name="M_str_sqlstr"></param>
/// <returns>calculation string</returns>
public string getResult(string M_str_sqlstr)
{
MySqlDataReader result = null;
MySqlConnection mysqlcon = this.getmysqlcon();
mysqlcon.Open();
//MySqlCommand commn = new MySqlCommand("set names utf-8", mysqlcon);
MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
result = mysqlcom.ExecuteReader();
String str = "";
while (result.Read())
{
//double percent = Convert.ToDouble(int.Parse(result["num"].ToString())) / Convert.ToDouble(int.Parse(result["total"].ToString()));
double percent = Convert.ToDouble(result["num"]) / Convert.ToDouble(result["total"]);
string r = percent.ToString("0.00%");//percentage format e.g. 6%
str = str + result["status"] + " " + result["num"] + " " + r + "\r\n";
Console.Write(str);
}
mysqlcom.Dispose();
mysqlcon.Close();
mysqlcon.Dispose();
return str;
}
/// <summary>
/// Create MySqlDataReader, using for output the content
/// </summary>
/// <param name="M_str_sqlstr">SQL string</param>
/// <returns>Return MySqlDataReader</returns>
public MySqlDataReader getmysqlread(string M_str_sqlstr)
{
MySqlConnection mysqlcon = this.getmysqlcon();
MySqlCommand mysqlcom = new MySqlCommand(M_str_sqlstr, mysqlcon);
mysqlcon.Open();
MySqlDataReader mysqlread = mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);
return mysqlread;
}
/// <summary>
/// Create DataTable object,using for output content in the dataGridView
/// </summary>
/// <param name="M_str_sqlstr">SQL string</param>
/// <returns>return MySqlDataReader</returns>
public DataTable getsqlread(string M_str_sqlstr)
{
MySqlConnection mysqlcon = this.getmysqlcon();
mysqlcon.Open();
MySqlCommand commn = new MySqlCommand("set names utf-8", mysqlcon);
MySqlDataAdapter mda = new MySqlDataAdapter(M_str_sqlstr, mysqlcon);
DataTable dt = new DataTable();
mda.Fill(dt);
return dt;
}
}
}
- Download Connector/Net and Add MySQL reference.
Click this link to Download Connector/Net
Download Connector/Net
Add MySQL reference
- Form1.cs code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
//** add this statements **//
using System.Data.OleDb;
namespace CSWithEexcel
{
public partial class Form1 : Form
{
// iniatialize the Connector object
Connector connector = new Connector();
public Form1()
{
InitializeComponent();
}
// select button click event
private void button1_Click(object sender, EventArgs e)
{
//Clean the student table
connector.cleanStudentTable();
//Fetch the excel file data
InsertXls();
}
// show info button click event
private void button2_Click(object sender, EventArgs e)
{
// hide the dataGridView
dataGridView1.Hide();
//SELECT * from (SELECT COUNT(status) num ,status from student GROUP by status) as temp join (SELECT COUNT(status) from student ) as temp3
String sql = "SELECT * from (SELECT COUNT(status) num ,status from student GROUP by status) as temp join (SELECT COUNT(status) total from student ) as temp3";
// call the getResult(sql) function to return percentage percentage string
string result = connector.getResult(sql);
// change Result label text to calculation result
studentinfor.Text = result;
}
//################################
/// <summary>
/// Read List_Class1_Submit.xls data, and save it into database.
/// </summary>
private void InsertXls()
{
// Show the dialog view to choose file
OpenFileDialog openfile = new OpenFileDialog();
// filter file format
openfile.Filter = "工作薄(*.xls)|*.xls|所有文件(*.*)|*.*|工作薄(*.xlsx)|*.xlsx";
if (openfile.FilterIndex == 1 && openfile.ShowDialog() == DialogResult.OK)
{
// call the ExcelToDS() function to get the dataset result
DataSet dataSet = ExcelToDS(openfile.FileName);
// show the DataGridView
dataGridView1.Show();
dataGridView1.DataSource = null;
DataTable dt = dataSet.Tables[0];
dataGridView1.DataSource = dt;
//String sqlSearch = "select * from student";
int count = 0;
string[] str = new string[13];// 13 means there are 13 columns in excel
string sql;
foreach (DataTable table in dataSet.Tables) //
{
foreach (DataRow row in table.Rows)
{
count = 0;
foreach (DataColumn column in table.Columns)
{
if (row[column] != null)
str[count] = row[column].ToString();// fetch each row data in the Excel
count++;
}
// create sql string
sql = string.Format("insert into student values(null,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}')",
str[0], str[1], str[2], str[3], str[4], str[5], str[6], str[7], str[8], str[9], str[10], str[11]);
// call the getmysqlcom() to save data into DB
connector.getmysqlcom(sql);
}
}
}
}
// =====================================================
/// <summary>
/// Fetch the data in the Excel and save to DataSet
/// </summary>
/// <param name="path"></param>
/// <returns> return DataSet </returns>
public DataSet ExcelToDS(string path)
{
// check the file extendsion name
string fileSuffix = System.IO.Path.GetExtension(path);
if (string.IsNullOrEmpty(fileSuffix))
return null;
using (DataSet ds = new DataSet())
{
// Excel 2003 or 2007 version ?
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\"";
//Read the data from the Excel Sheet1
string sql_select = " SELECT * FROM [Sheet1$]";
//Create a database connection
using (OleDbConnection conn = new OleDbConnection(connString))
// use to connect database, and used by DataSet
using (OleDbDataAdapter cmd = new OleDbDataAdapter(sql_select, conn))
{
conn.Open();
cmd.Fill(ds);// fill the data to DataSet
}
if (ds == null || ds.Tables.Count <= 0) return null;
return ds;
}
}
private void Form1_Load(object sender, EventArgs e)
{
}
}
}
- Running result.
Select button to choose the data.xlsx file.
choose the data.xlsx
There is the data result showing in the DataGridView.
DataGridView Result
Click show info button.
Percentage Calculation Result
网友评论