C#(3)-------excel上传到数据库
来源：互联网 发布：hfss微带线端口 编辑：程序博客网 时间：2024/06/16 18:54
.aspx
前台代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="test3.aspx.cs" Inherits="WEB.AjaxTools.test3" %><!DOCTYPE html><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"><meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title>fileupload</title></head><body> <form id="form1" runat="server"> <asp:FileUpload ID="FileUpload1" runat="server" /> <asp:Button ID="Button1" runat="server" OnClick="Button1_click" Text="Button" /> </form></body></html>
后台代码：
using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Data.OleDb;using System.Configuration;using System.Data.SqlClient;namespace WEB.AjaxTools{ public partial class test3 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button1_click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LYConnectionString"].ConnectionString); //链接数据库 conn.Open(); try { string fileurl = typename(FileUpload1);//调用typename方法取得excel文件路径 DataSet ds = new DataSet();//取得数据集 ds = xsldata(fileurl); int errorcount = 0;//记录错误信息条数 int insertcount = 0;//记录插入成功条数 int updatecount = 0;//记录更新信息条数 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string datePath = DateTime.Now.ToString("yyyyMMddHHmmssfff"); string Id = datePath + ds.Tables[0].Rows[i][0].ToString(); string ytime = ds.Tables[0].Rows[i][1].ToString(); string yname = ds.Tables[0].Rows[i][2].ToString(); string ytele = ds.Tables[0].Rows[i][3].ToString(); string yall = ds.Tables[0].Rows[i][4].ToString(); string yfname = ds.Tables[0].Rows[i][5].ToString(); string yfy = ds.Tables[0].Rows[i][6].ToString(); if (ytime == "") { for (int j = i-1;j > -1; j--) { String t1 = ds.Tables[0].Rows[j][1].ToString(); if(t1 != "") { ytime = t1; break; } } } if (yname == "") { for (int j = i - 1; j > -1; j--) { String t1 = ds.Tables[0].Rows[j][2].ToString(); if (t1 != "") { yname = t1; break; } } } if (ytele == "") { for (int j = i - 1; j > -1; j--) { String t1 = ds.Tables[0].Rows[j][3].ToString(); if (t1 != "") { ytele = t1; break; } } } if (yall == "") { for (int j = i - 1; j > -1; j--) { String t1 = ds.Tables[0].Rows[j][4].ToString(); if (t1 != "") { yall = t1; break; } } } Response.Write(Id); Response.Write(ytime); Response.Write(yname); Response.Write(ytele); Response.Write(yall); Response.Write(yfname); Response.Write(yfy); if (Id != "") { SqlCommand selectcmd = new SqlCommand("select count(*) as ynamenu from LiuOrdmr where Id='" + Id + "'", conn); int count = Convert.ToInt32(selectcmd.ExecuteScalar()); if (count > 0) { SqlCommand updatecmd = new SqlCommand("update LiuOrdmr set ytime='" + ytime + "',yname='" + yname + "',ytele='" + ytele + "',yall='" + yall + "',yfname='" + yfname + "', yfy ='" + yfy + "' where Id='" + Id + "'", conn); updatecmd.ExecuteNonQuery(); updatecount++; } else { String sql1 = "insert into LiuOrdmr values('" + Id + "','" + ytime + "','" + yname + "','" + ytele + "','" + yall + "','" + yfname + "','" + yfy + "')"; SqlCommand insertcmd = new SqlCommand(sql1, conn); insertcmd.ExecuteNonQuery(); insertcount++; } } else { errorcount++; } } Response.Write("<script language='javascript'>alert('" + insertcount + "条数据导入成功！" + updatecount + "条数据更新成功！" + errorcount + "条数据部分信息为空没有导入！');</script>"); } finally { conn.Close(); } } private String typename(FileUpload fileloads) { string fullfilename = fileloads.PostedFile.FileName; string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1); string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1); string murl = ""; if (type == "xls") { fileloads.PostedFile.SaveAs(Server.MapPath("excel") + "\\" + filename); murl = (Server.MapPath("excel") + "\\" + filename).ToString(); } else { Response.Write("<script language='javascript'>alert('导入文件格式不对!');</script>"); } return murl; } // 数据库导入数据集dataset private DataSet xsldata(string filepath) { string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'"; //如果是导入excel2013版本，连接字符串则应该变成 // string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;IMEX=1'"; System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon); string strCom = "SELECT * FROM [Sheet1$]"; Conn.Open(); System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn); DataSet ds = new DataSet(); myCommand.Fill(ds, "[Sheet1$]"); Conn.Close(); return ds; } }}后台连接数据库的部分需要在app.config中配置一下：
<connectionStrings> <add name="LYConnectionString" connectionString="Data Source=服务器名称;Initial Catalog=数据库名称;User ID=用户名;Password=密码" providerName="System.Data.SqlClient"/></connectionStrings>
from：asp.net 中excel 导入数据库
在.ashx与.html编写的代码
前台代码：
<div> <form id="form1" method="post" enctype="multipart/form-data"> <input type="file" id="file1" name="f1" /> </form> <a id="btnBatchImport" href="javascript:ImportData()">上传申请</a> </div> <script> function ImportData() { $('#form1').form('submit', { url: "../AjaxTools/test4.ashx?method=jiayou", success: function (data) { data = $.parseJSON(data); if (data.IsSuccess) { $("#test4").datagrid("reload"); showInfo(data.msg); } else { showError(data.msg); } } }); } </script>后台代码：
using System.Web.Script.Serialization;using System.Data;using DBUtility;using System.Collections.Generic;using System.Data.SqlClient;using System;using System.Web;using System.IO;using System.Windows.Forms;using System.Web.UI.WebControls;using System.Collections.Specialized;namespace WEB.AjaxTools{ public class test4 : AjaxTools.AjaxService { public void jiayou() { HttpFileCollection file1 = _context.Request.Files; SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LYConnectionString"].ConnectionString); //链接数据库 conn.Open(); try { string fileurl = typename(file1);//调用typename方法取得excel文件路径 if (fileurl == "") { ContextResponse(false, "导入文件格式不对!请导入xls格式文件。"); } else { DataSet ds = new DataSet();//取得数据集 ds = xsldata(fileurl); int errorcount = 0;//记录错误信息条数 int insertcount = 0;//记录插入成功条数 int updatecount = 0;//记录更新信息条数 for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { string Id = DateTime.Now.ToString("yyyyMMddHHmmssfff"); string pid = ds.Tables[0].Rows[i][0].ToString(); string ytime = ds.Tables[0].Rows[i][1].ToString(); string yname = ds.Tables[0].Rows[i][2].ToString(); string ytele = ds.Tables[0].Rows[i][3].ToString(); string yall = ds.Tables[0].Rows[i][4].ToString(); string yfname = ds.Tables[0].Rows[i][5].ToString(); string yfy = ds.Tables[0].Rows[i][6].ToString(); if (pid == "") { for (int j = i - 1; j > -1; j--) { String t1 = ds.Tables[0].Rows[j][0].ToString(); if (t1 != "") { pid = t1; break; } } } if (ytime == "") { for (int j = i - 1; j > -1; j--) { String t1 = ds.Tables[0].Rows[j][1].ToString(); if (t1 != "") { ytime = t1; break; } } } if (yname == "") { for (int j = i - 1; j > -1; j--) { String t1 = ds.Tables[0].Rows[j][2].ToString(); if (t1 != "") { yname = t1; break; } } } if (ytele == "") { for (int j = i - 1; j > -1; j--) { String t1 = ds.Tables[0].Rows[j][3].ToString(); if (t1 != "") { ytele = t1; break; } } } if (yall == "") { for (int j = i - 1; j > -1; j--) { String t1 = ds.Tables[0].Rows[j][4].ToString(); if (t1 != "") { yall = t1; break; } } } if (Id != "") { SqlCommand selectcmd = new SqlCommand("select count(*) as ynamenu from LiuOrdmr where Id='" + Id + "'", conn); int count = Convert.ToInt32(selectcmd.ExecuteScalar()); if (count > 0) { SqlCommand updatecmd = new SqlCommand("update LiuOrdmr set pid='" + pid + "',ytime='" + ytime + "',yname='" + yname + "',ytele='" + ytele + "',yall='" + yall + "',yfname='" + yfname + "', yfy ='" + yfy + "' where Id='" + Id + "'", conn); updatecmd.ExecuteNonQuery(); updatecount++; } else { String sql1 = "insert into LiuOrdmr values('" + Id + "','" + pid + "','" + ytime + "','" + yname + "','" + ytele + "','" + yall + "','" + yfname + "','" + yfy + "')"; SqlCommand insertcmd = new SqlCommand(sql1, conn); insertcmd.ExecuteNonQuery(); insertcount++; } } else { errorcount++; } } ContextResponse(true, insertcount + "条数据导入成功！" + updatecount + "条数据更新成功！" + errorcount + "条数据部分信息为空没有导入！"); } } finally { conn.Close(); } } private String typename(HttpFileCollection fileloads) { string fullfilename = Path.GetFileName(fileloads[0].FileName); string filename = fullfilename.Substring(fullfilename.LastIndexOf("\\") + 1); string type = fullfilename.Substring(fullfilename.LastIndexOf(".") + 1); string murl = ""; if (type.Equals("xls")) { fileloads[0].SaveAs(System.Web.HttpContext.Current.Server.MapPath("excel") + "\\" + filename); murl = (System.Web.HttpContext.Current.Server.MapPath("excel") + "\\" + filename).ToString(); } return murl; } private DataSet xsldata(string filepath) { string strCon; strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1'"; //如果是导入excel2013版本，连接字符串则应该变成 // strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;IMEX=1'"; System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon); Conn.Open(); //string strCom = "SELECT * FROM [Sheet1$]"; //获取sheet名 DataTable table = Conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); String tableName = table.Rows[0]["Table_Name"].ToString(); string strCom = "select * from " + "[" + tableName + "]"; System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn); DataSet ds = new DataSet(); myCommand.Fill(ds, "[" + tableName + "]"); Conn.Close(); return ds; } }}这个里面有些方法被写在AjaxTools.AjaxService中，所以直接使用有的地方会报错。
阅读全文
0 0
- C#(3)-------excel上传到数据库
- Uploadify上传Excel到数据库
- Excel上传到Sql数据库
- 通过上传excel导入数据到数据库
- asp.net 上传excel到数据库
- 上传excel表并导入到数据库
- 工具——excel上传到数据库
- 上传Excel数据导入到数据库
- 上传EXCEL文件并读取到数据库
- 读excel图片到数据库和上传图片到数据库
- Excel文件上传预览（上传到数据库后面再说）
- 上传excel到服务器端，并写入到mssql数据库
- C#- Excel表导入到sql数据库
- 上传Excel并将指定数据导入到数据库
- asp.net：上传excel表格到SQL Server数据库
- ASP.NET批量上传excel数据到数据库MySql
- jsp使用SmartUpload上传EXCEL并保存到数据库.
- Asp.net上传Excel并保存到数据库
- maven 打包可执行jar的方法
- static
- 514 - Rails
- linux下安装python3
- U-boot目录结构学习笔记
- C#(3)-------excel上传到数据库
- /etc/hosts文件修改后如何生效
- BZOJ 3251: 树上三角形 斐波那契数列 脑洞
- java数组转List
- 报错 E/EGL_emulation: tid 5975: eglSurfaceAttrib(1174): error 0x3009 (EGL_BAD_MATCH)
- 04.11 Linux文件系统类型与特点
- JAVA实现单向链表的增删操作
- A+B（while和if搞混了...）
- Java NIO编程
