excel数据批量导入到Mysql数据库

wxl2012

关注

原创最新推荐文章于 2023-06-12 13:15:05 发布 4.3k 阅读 AI 写同款· GEO 优化 ›

从Mysql数据库读取数据批量导入到Excel文件本篇和上篇类型，只是顺序发生了变化——从Mysql度数据并保存到Excel中。导出XLS流程：读取数据库的表->循环记录构建制表符分隔的字段信息->设置header信息->导出文件（下载）到本地。下面直接分享代码，导出文件outdb.php如下： require_once './PHPExcel.php'; //路径根据自己实际项目的路径进行设置 //下阅读详情

aspx页面

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="customin.aspx.cs" Inherits="kf_customin" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>无标题页</title> <mce:style type="text/css"></mce:style><style type="text/css" mce_bogus="1"> body{font-size:12px;background-color:white;} </style> </head> <body> <form id="form1" runat="server"> <div> <div style="z-index: 101; left: 250px; width: 629px; position: absolute; top: 170px; height: 42px"> <table> <tr> <td style="width: 120px;text-align:center;" colspan="4"> <p style="color:Red;width:500px;"> *excle的中文不允许出现繁体字！（重复的公司将在左侧输出）</p> </td> </tr> <tr> <td style="width: 120px"> <asp:Label ID="Label1" runat="server" Text="批量导入客户资料" Width="153px" Font-Bold="True" Font-Italic="False" Font-Size="Medium"></asp:Label></td> <td style="width: 100px"> <asp:FileUpload ID="FileUpload1" runat="server" BackColor="White" /></td> <td colspan="2" style="width: 254px"> <asp:Button ID="btnkf" runat="server" Text="执行导入" OnClick="btnkf_Click" /> </td> </tr> <tr> <td style="width: 120px"> <asp:Label ID="Label2" runat="server" Text="分状态导入客户资料" Width="163px" Font-Bold="True" Font-Size="Medium"></asp:Label></td> <td style="width: 100px"> <asp:FileUpload ID="FileUpload2" runat="server" BackColor="White" /></td> <td colspan="2" style="width: 254px"> <asp:Button ID="btnzt" runat="server" Text="执行导入" OnClick="btnzt_Click" /> </td> </tr> <tr> <td style="width: 120px"> <asp:Label ID="Label3" runat="server" Text="批量修改信息" Width="134px" Font-Bold="True" Font-Size="Medium"></asp:Label></td> <td style="width: 100px"> <asp:FileUpload ID="FileUpload3" runat="server" BackColor="White" /></td> <td colspan="2" style="width: 254px"> <asp:Button ID="btnxg" runat="server" Text="执行导入" OnClick="btnxg_Click" /> </td> </tr> </table> </div> </div> </form> </body> </html>

cs文件

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 i_salesDAL; using i_salesModels; using i_salesBLL; using MySql.Data.MySqlClient; using System.Data.OleDb; public partial class kf_customin : System.Web.UI.Page { public int total = 0; string strConn = "server=localhost;user id=root;password=root;database=google;"; protected void Page_Load(object sender, EventArgs e) { } //批量导入客户资料 protected void btnkf_Click(object sender, EventArgs e) { if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件 { Response.Write("<mce:script type="text/javascript"></mce:script> "); return;//当无文件时,返回 } string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 if (IsXls != ".xls") { Response.Write("<mce:script type="text/javascript"></mce:script>"); return;//当选择的不是Excel文件时,返回 } MySqlConnection cn = new MySqlConnection(strConn); cn.Open(); string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;//获取Execle文件名 DateTime日期函数 string savePath = Server.MapPath(("~//upfiles//") + filename);//Server.MapPath 获得虚拟服务器相对路径~//upfiles// FileUpload1.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上 DataSet ds = ExecleDs(savePath, filename); //调用自定义方法 DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组 int rowsnum = ds.Tables[0].Rows.Count; if (rowsnum == 0) { Response.Write("<mce:script type="text/javascript"></mce:script>"); //当Excel表为空时,对用户进行提示 } else { for (int i = 0; i < dr.Length; i++) { string name = dr[i][0].ToString();// excel列名"公司名称" string trad = dr[i][1].ToString();//以下类似"性质" string business = dr[i][2].ToString();//"行业编号" string region = dr[i][3].ToString();//"地区" string comtel = dr[i][4].ToString();//"电话" string comfax = dr[i][5].ToString();//"传真" string comads = dr[i][6].ToString();//"地址" string comsite = dr[i][7].ToString();//"网址" string comemail = dr[i][8].ToString();//"邮箱" string commain = dr[i][9].ToString();//"主营" string combz = dr[i][10].ToString();//"备注" string p1 = dr[i][11].ToString();//"联系人1" string p1tel = dr[i][12].ToString();//"电话1" string p1work = dr[i][13].ToString();//"职位1" string p2 = dr[i][14].ToString();//空格"联系人2" string p2tel = dr[i][15].ToString();//"电话2" string p2work = dr[i][16].ToString();//"职位2" string p3 = dr[i][17].ToString();//空格"联系人3" string p3tel = dr[i][18].ToString();//"电话3" string p3work = dr[i][19].ToString();//"职位3" string come = dr[i][20].ToString();//"来源" string service = dr[i][21].ToString();//"业务形态" string time = DateTime.Now.ToString("yyyy-MM-dd"); string sqlcheck = "select count(*) from v_company where COM_NAME='" + name + "'"; //检查公司是否存在 MySqlCommand sqlcmd = new MySqlCommand(sqlcheck, cn); int count = Convert.ToInt32(sqlcmd.ExecuteScalar()); if (count < 1)//检查不存在公司 { string insertstr = "insert into v_company(COM_NAME,TRADETYPE_ID,BUSINESS_ID,REGION,COM_TEL,COM_FAX,COM_ADDRESS,COM_WEBSITE,COM_EMAIL,MAINFUNCTION,REMARKS,CONTACT_PERSON_1,CONTACT_PERSON_1_PHONE,CONTACT_PERSON_1_TITLE,CONTACT_PERSON_2,CONTACT_PERSON_2_PHONE,CONTACT_PERSON_2_TITLE,CONTACT_PERSON_3,CONTACT_PERSON_3_PHONE,CONTACT_PERSON_3_TITLE,SOURCE,SERVICE_ID,OPERATER_ID,STATE,TIME,REJECT_ID) values('" + name + "','" + trad + "','" + business + "','" + region + "','" + comtel + "','" + comfax + "','" + comads + "','" + comsite + "','" + comemail + "','" + commain + "','" + combz + "','" + p1 + "','" + p1tel + "','" + p1work + "','" + p2 + "','" + p2tel + "','" + p2work + "','" + p3 + "','" + p3tel + "','" + p3work + "','" + come + "','" + service + "','2','0','" + time + "','1')"; MySqlCommand cmd = new MySqlCommand(insertstr, cn); try { cmd.ExecuteNonQuery(); // Page.RegisterStartupScript("", "<mce:script type="text/javascript"></mce:script>"); } catch (MembershipCreateUserException ex) //捕捉异常 { Response.Write("<mce:script type="text/javascript"></mce:script>"); } } else//检查存在公司 { total = total + 1; Response.Write("重复公司："+name+"<br/>"); // Response.Write(name); // array.Add(name); // this.lblmsg.Text = name; // Page.RegisterStartupScript("", "<mce:script type="text/javascript"></mce:script>"); // Page.RegisterStartupScript("", "<mce:script type="text/javascript"></mce:script>"); continue; } } int last = dr.Length - total;//计算导入条数 Page.RegisterStartupScript("", "<mce:script type="text/javascript"></mce:script>"); } cn.Close(); } //分状态导入客户资料 protected void btnzt_Click(object sender, EventArgs e) { if (FileUpload2.HasFile == false)//HasFile用来检查FileUpload是否有指定文件 { Response.Write("<mce:script type="text/javascript"></mce:script> "); return;//当无文件时,返回 } string IsXls = System.IO.Path.GetExtension(FileUpload2.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 if (IsXls != ".xls") { Response.Write("<mce:script type="text/javascript"></mce:script>"); return;//当选择的不是Excel文件时,返回 } MySqlConnection cn = new MySqlConnection(strConn); cn.Open(); string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload2.FileName;//获取Execle文件名 DateTime日期函数 string savePath = Server.MapPath(("~//upfiles//") + filename);//Server.MapPath 获得虚拟服务器相对路径~//upfiles// FileUpload2.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上 DataSet ds = ExecleDs(savePath, filename); //调用自定义方法 DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组 int rowsnum = ds.Tables[0].Rows.Count; if (rowsnum == 0) { Response.Write("<mce:script type="text/javascript"></mce:script>"); //当Excel表为空时,对用户进行提示 } else { for (int i = 0; i < dr.Length; i++) { string name = dr[i][0].ToString();// excel列名"公司名称" string statue = dr[i][1].ToString();//"公司状态" string reject = dr[i][2].ToString();//"拒绝理由" string sqlcheck = "select count(*) from v_company where COM_NAME='" + name + "'"; //检查公司是否存在, MySqlCommand sqlcmd = new MySqlCommand(sqlcheck, cn); int count = Convert.ToInt32(sqlcmd.ExecuteScalar()); if (count < 1) { Response.Write("<mce:script type="text/javascript"></mce:script></script> "); continue; } else { int rejectId = RejectManager.GetRejectIDByRejectType(reject); string insertstr = "update v_company set STATE='" + statue + "',REJECT_ID='" + rejectId + "' where COM_NAME='" + name + "'"; MySqlCommand cmd = new MySqlCommand(insertstr, cn); try { cmd.ExecuteNonQuery(); } catch (MembershipCreateUserException ex) //捕捉异常 { Response.Write("<mce:script type="text/javascript"></mce:script>"); } } } Response.Write("<mce:script type="text/javascript"></mce:script>"); } cn.Close(); } //批量修改信息 protected void btnxg_Click(object sender, EventArgs e) { if (FileUpload3.HasFile == false)//HasFile用来检查FileUpload是否有指定文件 { Response.Write("<mce:script type="text/javascript"></mce:script> "); return;//当无文件时,返回 } string IsXls = System.IO.Path.GetExtension(FileUpload3.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名 if (IsXls != ".xls") { Response.Write("<mce:script type="text/javascript"></mce:script>"); return;//当选择的不是Excel文件时,返回 } MySqlConnection cn = new MySqlConnection(strConn); cn.Open(); string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload3.FileName;//获取Execle文件名 DateTime日期函数 string savePath = Server.MapPath(("~//upfiles//") + filename);//Server.MapPath 获得虚拟服务器相对路径~//upfiles// FileUpload3.SaveAs(savePath); //SaveAs 将上传的文件内容保存在服务器上 DataSet ds = ExecleDs(savePath, filename); //调用自定义方法 DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组 int rowsnum = ds.Tables[0].Rows.Count; if (rowsnum == 0) { Response.Write("<mce:script type="text/javascript"></mce:script>"); //当Excel表为空时,对用户进行提示 } else { for (int i = 0; i < dr.Length; i++) { string name = dr[i][0].ToString();// excel列名"公司名称" string trad = dr[i][1].ToString();//以下类似"企业性质" string business = dr[i][2].ToString();//"行业" string region = dr[i][3].ToString();//"地区" string comtel = dr[i][4].ToString();//"电话" string comfax = dr[i][5].ToString();//"传真" string comads = dr[i][6].ToString();//"地址" string comsite = dr[i][7].ToString();//"网站" string comemail = dr[i][8].ToString();//"公司Email" string commain = dr[i][9].ToString();//"主营业务" string combz = dr[i][10].ToString();//"备注" string p1 = dr[i][11].ToString();//"联系人1" string p1tel = dr[i][12].ToString();//"联系人1电话" string p1work = dr[i][13].ToString();//"联系人1职务" string p2 = dr[i][14].ToString();//"联系人2" string p2tel = dr[i][15].ToString();//"联系人2电话" string p2work = dr[i][16].ToString();//空格"联系人2职务 " string p3 = dr[i][17].ToString();//"联系人3" string p3tel = dr[i][18].ToString();//"联系人3电话" string p3work = dr[i][19].ToString();//"联系人3职务" string come = dr[i][20].ToString();//"来源" string service = dr[i][21].ToString();//"业务形态" string sqlcheck = "select count(*) from v_company where COM_NAME='" + name + "'"; //检查公司是否存在, MySqlCommand sqlcmd = new MySqlCommand(sqlcheck, cn); int count = Convert.ToInt32(sqlcmd.ExecuteScalar()); if (count < 1) { Response.Write("<mce:script type="text/javascript"></mce:script></script> "); continue; } else { string insertstr = "update v_company set TRADETYPE_ID='" + trad + "',BUSINESS_ID='" + business + "',REGION='" + region + "',COM_TEL='" + comtel + "',COM_FAX='" + comfax + "',COM_ADDRESS='" + comads + "',COM_WEBSITE='" + comsite + "',COM_EMAIL='" + comemail + "',MAINFUNCTION='" + commain + "',REMARKS='" + combz + "',CONTACT_PERSON_1='" + p1 + "',CONTACT_PERSON_1_PHONE='" + p1tel + "',CONTACT_PERSON_1_TITLE='" + p1work + "',CONTACT_PERSON_2='" + p2 + "',CONTACT_PERSON_2_PHONE='" + p2tel + "',CONTACT_PERSON_2_TITLE='" + p2work + "',CONTACT_PERSON_3='" + p3 + "',CONTACT_PERSON_3_PHONE='" + p3tel + "',CONTACT_PERSON_3_TITLE='" + p3work + "',SOURCE='" + come + "',SERVICE_ID='" + service + "' where COM_NAME='" + name + "'"; MySqlCommand cmd = new MySqlCommand(insertstr, cn); try { cmd.ExecuteNonQuery(); } catch (MembershipCreateUserException ex) //捕捉异常 { Response.Write("<mce:script type="text/javascript"></mce:script>"); } } } Response.Write("<mce:script type="text/javascript"></mce:script>"); } cn.Close(); } //用于将Excel表里的数据填充到DataSet 导入批量 public DataSet ExecleDs(string filenameurl, string table) { string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn); odda.Fill(ds, table); return ds; } //用于将Excel表里的数据填充到DataSet 修改批量 public DataSet ExecleDsxg(string filenameurl, string table) { string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet2$]", conn); odda.Fill(ds, table); return ds; } }

收录于