作业地址:http://user.qzone.qq.com/303727350/blog/1429718303
信管1123 林少鑫 201211671312
完成目标:目标3:C#读取Excel文件,并保存成HTML格式,同时把Excel数据保存到MySQL数据库中
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
namespace ExcelRead
{
public partial class Form1 : Form
{
static string text = null;
public Form1()
{
InitializeComponent();
}
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
}
//打开文件
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog openfile = new OpenFileDialog();
openfile.Filter = "工作薄(*.xls)|*.xls|所有文件(*.*)|*.*";
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
DataSet da = ExcelToDS(openFileDialog.FileName);
}
}
//从EXCEL表读取内容并显示
public DataSet ExcelToDS(string path)
{
//mysql数据库打开
string mycoon = "server=localhost;Database=test;Uid=root;Pwd=;Charset=utf8;";
MySqlConnection con = new MySqlConnection(mycoon);
con.Open();
//excel数据库打开
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + @path + ";" + "Extended Properties=Excel
8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
//
string strExcel = "";
strExcel = "select * from [sheet1$]";
//
OleDbDataAdapter myCommand = null;
myCommand = new OleDbDataAdapter(strExcel, strConn);
//
DataSet ds = null;
DataTable table1 = new DataTable();
ds = new DataSet();
//填充
myCommand.Fill(table1);
dataGridView1.DataSource = table1;
conn.Close();
string show_str = null;
string name = null, website = null;
int count = -1,ifName=0,ifWebsite=0;
foreach(DataRow row in table1.Rows)
{
ifName = ifWebsite = 0;
foreach(DataColumn column in table1.Columns)
{
if (column.ColumnName == "姓名")
{
if (row[column].ToString().Length == 2) name = row[column].ToString().Insert(1, "");
else name = row[column].ToString();
count++;
ifName=1;
}
if(column.ColumnName == "作业网址")
{
show_str += "<a href=\"" + row[column].ToString() + "\">" + name + "</a>"+ " ";
website = row[column].ToString();
ifWebsite=1;
}
if(count==5)
{
count = 0;
show_str += "<br />";
}
if(ifName==1&&ifWebsite==1)
{
saveToMysql(con,name,website);
}
}
}
text = show_str;
save();
//mysql数据库关闭
con.Close();
return ds;
}
private static void save()
{
try
{
// MessageBox.Show(text);
File.WriteAllText("save.html", text);
MessageBox.Show("转化html成功!");
}
catch (Exception)
{
MessageBox.Show("转化html失败!");
}
}
private static void saveToMysql(MySqlConnection con, string name,string website)
{
try
{
MySqlCommand cmd;
cmd = con.CreateCommand();
//建表(初始化执行)
//cmd.CommandText= "CREATE TABLE class (id int(10) unsigned NOT NULL AUTO_INCREMENT key,name varchar(20) NOT NULL,website varchar(120) NOT NULL)CHARSET=utf8; ";
//插入数据
cmd.CommandText = "INSERT INTO class(name,website)VALUES(@name,@website)";
cmd.Parameters.AddWithValue("@name",name);
cmd.Parameters.AddWithValue("@website",website);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
MessageBox.Show("保存到mysql数据库失败!");
}
}
private void Form1_Load(object sender, EventArgs e)
{
label1.Text = "";
}
}
}
这次作业中遇到的问题是读取excel经验不够,导致string读取数据一直失败,一开始以为是编码问题,试过很多次之后仍然不能完成,后来通过OleDb读取才成功