excel导入mysql数据库中
来源:互联网 发布:asphalt 8 windows 10 编辑:程序博客网 时间:2024/05/17 12:56
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using MySql.Data.MySqlClient;
using hot.dqt.com.DBUtility;
public partial class Default5 : PageBase_number
{
public static string connstr = PubConstant.ConnectionString;
// public string connstr = "server=localhost; port=3307;user id = root; password =123456; database = gq; Allow Zero Datetime=True; charset=utf8";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (AdminUid <= 0)
{
Response.Redirect("/number/manage/login.aspx");
}
}
}
string savePath = @"D:\website\hot.dqt.com.cn\number\tmpxml\";
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
if (FileUpload1.HasFile)
{
String filenames;
filenames = FileUpload1.FileName;
savePath += filenames;
FileUpload1.SaveAs(savePath);
//Page.Response.Write(FileUpload1.PostedFile.ContentType + FileUpload1.PostedFile.ContentLength + "<br>");
}
else
{
Page.Response.Write("<script>alert('上传失败!')</script>");
return;
}
MySqlConnection conn = new MySqlConnection(connstr);
conn.Open();
string filename = FileUpload1.FileName;
DataSet ds = ExcelDs(savePath, filename);
DataRow[] dr = ds.Tables[0].Select();
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
return;
}
else
{
//清空数据库
string delstr = "delete from jifen";
MySqlCommand delcmd = new MySqlCommand(delstr, conn);
try
{
delcmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex)
{
Response.Write("<script>alert('清空数据失败:" + ex.Message + "')</script>");
return;
}
for (int i = 0; i < dr.Length; i++)
{
string number = dr[i][1].ToString();
string jifen = dr[i][2].ToString();
string insertstr = "insert into jifen(number,jifen) values('" + number + "','" + jifen + "')";
MySqlCommand cmd = new MySqlCommand(insertstr, conn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex)
{
Response.Write("<script>alert('导入:" + ex.Message + "')</script>");
}
}
Response.Write("<script>alert('Excle表导入成功!')</script>");
}
conn.Close();
}
public DataSet ExcelDs(string filenameurl, string table)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
DataSet ds = new DataSet(); odda.Fill(ds, table);
return ds;
}
}
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using MySql.Data.MySqlClient;
using hot.dqt.com.DBUtility;
public partial class Default5 : PageBase_number
{
public static string connstr = PubConstant.ConnectionString;
// public string connstr = "server=localhost; port=3307;user id = root; password =123456; database = gq; Allow Zero Datetime=True; charset=utf8";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (AdminUid <= 0)
{
Response.Redirect("/number/manage/login.aspx");
}
}
}
string savePath = @"D:\website\hot.dqt.com.cn\number\tmpxml\";
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
if (IsXls != ".xls")
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
if (FileUpload1.HasFile)
{
String filenames;
filenames = FileUpload1.FileName;
savePath += filenames;
FileUpload1.SaveAs(savePath);
//Page.Response.Write(FileUpload1.PostedFile.ContentType + FileUpload1.PostedFile.ContentLength + "<br>");
}
else
{
Page.Response.Write("<script>alert('上传失败!')</script>");
return;
}
MySqlConnection conn = new MySqlConnection(connstr);
conn.Open();
string filename = FileUpload1.FileName;
DataSet ds = ExcelDs(savePath, filename);
DataRow[] dr = ds.Tables[0].Select();
int rowsnum = ds.Tables[0].Rows.Count;
if (rowsnum == 0)
{
Response.Write("<script>alert('Excel表为空表,无数据!')</script>"); //当Excel表为空时,对用户进行提示
return;
}
else
{
//清空数据库
string delstr = "delete from jifen";
MySqlCommand delcmd = new MySqlCommand(delstr, conn);
try
{
delcmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex)
{
Response.Write("<script>alert('清空数据失败:" + ex.Message + "')</script>");
return;
}
for (int i = 0; i < dr.Length; i++)
{
string number = dr[i][1].ToString();
string jifen = dr[i][2].ToString();
string insertstr = "insert into jifen(number,jifen) values('" + number + "','" + jifen + "')";
MySqlCommand cmd = new MySqlCommand(insertstr, conn);
try
{
cmd.ExecuteNonQuery();
}
catch (MembershipCreateUserException ex)
{
Response.Write("<script>alert('导入:" + ex.Message + "')</script>");
}
}
Response.Write("<script>alert('Excle表导入成功!')</script>");
}
conn.Close();
}
public DataSet ExcelDs(string filenameurl, string table)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
DataSet ds = new DataSet(); odda.Fill(ds, table);
return ds;
}
}
0 0
- excel导入mysql数据库中
- Excel表格数据导入mysql数据库中
- Excel表格数据导入mysql数据库中
- excel中数据导入到mysql数据库
- Excel表格数据导入mysql数据库中
- Excel导入MySQL数据库
- mysql中导入excel
- excel数据导入mysql数据库
- excel数据导入mysql数据库
- Excel数据导入Mysql数据库
- Mysql数据库导入excel数据
- excel数据导入MySQL数据库
- 如何把excel数据导入到mysql数据库中
- 把excel数据导入mysql数据库中方法
- 将excel中数据导入到mysql数据库
- 向MySql数据库中导入Excel表格中的…
- 将excel表导入到MySQL数据库表中
- java中jxls实现excel导入到mysql数据库
- 字符串进行压缩
- sql
- JDBC读写mysql总结
- java中反射系列一
- Codeforces Round #266 (Div. 2)(解题报告)
- excel导入mysql数据库中
- “Nonparametric Blind Super-Resolution”
- 算法学习之排序算法:归并排序
- android source code online
- 2014.09.13 周六-html-xhtml-正则表达式-html总结-css
- 广岛上空的蘑菇云
- 我们在过去的时光里找到了什么
- 编程之所见
- Java数组