如何将在前台通过单击某按钮使Excel中的数据导入到sql server数据库中？
sql server数据库中有表a，字段有“代号”、“书名”、“价格”。某磁盘下有名为b的Excel文件，其内容是：
代号 书名 价格
101 数据库 35
102 C语言 29
103 vb.net 27
请问如何在web窗体中单击某按钮，如“导入”，使b中的数据导入到a中？？？ --------------------编程问答-------------------- excel文件在客户端还是服务器端?
如果在客户端,先要选择文件,然后上传,最后导入
导入可连接excel作为一个数据库,查询数据然后写入数据库.
--------------------编程问答-------------------- excel文件是在服务器端的，请问能用代码实现吗？ --------------------编程问答-------------------- 用oledb连接
"Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=d:\aa.xls;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""
--------------------编程问答-------------------- 请问可不可以像windows界面那样，先选择某个excel文件再按“导入”按钮就将excel表中的记录导入到sql数据库中的a表中？也就是说excel文件不是固定放在某个磁盘的某个文件夹下的。 --------------------编程问答-------------------- string strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Server.MapPath("../xls_bang/bang.xls")+";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
OleDbConnection oleCon=new OleDbConnection(strCon);
OleDbDataAdapter oleDA=new OleDbDataAdapter("select * from [Sheet1$]",oleCon);
DataSet ds=new DataSet();
oleDA.Fill(ds);
dataAdapter.SelectCommand.CommandText="updatesql";
SqlCommandBuilder cmdBuilder=new SqlCommandBuilder(dataAdapter);
dataAdapter.UpdateCommand=cmdBuilder.GetUpdateCommand();
dataAdapter.Update(ds); --------------------编程问答-------------------- oh,make a mistake --------------------编程问答-------------------- 学习！ --------------------编程问答-------------------- 该回复被版主删除 --------------------编程问答-------------------- 我用如下sql语句时：
insert into a
select *
from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="e:\b.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]
执行后怎么在sql数据库中看a表时“书名”和“价格”下的记录全都掉换了！？
即“书名”字段下的记录导入的是“价格”字段的记录，而“价格”字段下的记录导入的却是
“书名”字段的记录！
what's the matter?! --------------------编程问答-------------------- 怎么会掉换数据了呢？？？ --------------------编程问答-------------------- 顶，学习一下 --------------------编程问答-------------------- Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Adapter_Default" %>
<!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>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="上传导入" />
<br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="FileUpload1"
Display="Dynamic" ErrorMessage="请选择要上传导入的.xls格式文件。"></asp:RequiredFieldValidator><br />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label><br />
<br />
<%--<asp:Image ID="Image1" runat="server" />--%><br />
<br />
</div>
</form>
</body>
</html>
Default.aspx.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;
public partial class Adapter_Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string name = FileUpload1.FileName;
string size = FileUpload1.PostedFile.ContentLength.ToString();
string type = FileUpload1.PostedFile.ContentType;
string type2 = name.Substring(name.LastIndexOf(".") + 1);
string ipath = Server.MapPath("..\\upimg") + "\\" + name;
string fpath = Server.MapPath("..\\upfile") + "\\" + name;
string wpath = "..\\upimg\\" + name;
if (type2 == "xls")
{
FileUpload1.SaveAs(fpath);
//Image1.ImageUrl = wpath;
Label1.Text = "你传的文件名称是：" + name + "<br>文件大小为:" + size + "字节<br>文件类型是：" + type + "<br>后缀名是：" + type2 + "<br>实际路径是:" + ipath + "<br>虚拟路径是：" + wpath;
Response.Redirect("index.asp?filename=" + name);
}
else
{
//Image1.Visible = false;
//FileUpload1.SaveAs(fpath);
Label1.Text = "<font color=red>请你选择.Microsoft Office Excel文件，格式为.xls。</font>";
}
//读取XLS数据
}
}
index.asp:
<%@language=vbscript codepage=936 %>
<%
option explicit
response.buffer=true
Server.ScriptTimeOut =100
%>
<%
dim conn1,rs1,sql1
Set conn1 = Server.CreateObject("ADODB.Connection")
conn1.open "driver={SQL Server};server=zg; uid=zg;pwd=123456;database=SOU"
DIM connXLS,rsXLS,iXLS,sqlXLS
iXLS=0
dim Filenamestr
Filenamestr="../Upfile/"&request("filename")
Set connXLS =Server.CreateObject("ADODB.Connection")
connXLS.open "Driver={Microsoft Excel Driver (*.xls)};DBQ="&Server.MapPath(""&filenamestr&"")
Set rsXLS=Server.CreateObject("ADODB.Recordset")
sqlXLS="Select * From [sheet1$] order by jgmc"
Set rsXLS=connXLS.Execute(sqlXLS)
dim jgdm,jgmc
IF RsXLS.Eof And RsXLS.Bof Then
Response.write "没有找到您需要的数据!!"
Else
Do While Not RsXLS.EOF
'Q1代码是否为空
if trim(RsXLS("jgdm"))<>"" then '非规则判断,定义问题，进入RuleA表对比
'response.write "<font color=green>合格数据列表:"&RsXLS("jgmc")&"</font><p>"
jgdm=trim(RsXLS("jgdm"))
jgmc=trim(RsXLS("jgmc"))
call InputJGDM() '把合格记录后加入库
else
dim rsRuleA,sqlRuleA
set rsRuleA=server.createobject("adodb.recordset")
sqlRuleA="select * from RuleA where jgmc='"&RsXLS("jgmc")&"'"
rsRuleA.open sqlRuleA,conn1,1,3
'do while rsRuleA.eof
if not rsRuleA.eof then '判断规则表是否有对应的问题解决记录
jgdm=rsRuleA("jgdm")
jgmc=rsRuleA("jgmc")
call InputJGDM() '把问题记录正规化后加入库
rsRuleA.close
set rsRuleA=nothing
'response.write "<font color=red>非合格数据列表:"&RsXLS("jgmc")&"</font><p>"
else
response.write "<p><font color=blue>待正规化数据:"
response.write RsXLS("jgmc")&"<p></font><a href=add.aspx>添加规则</a>"
end if
end if
'Q2是否代码为空与规则A表对比自检
iXLS=iXLS+1
RsXLS.MoveNext
Loop
End IF
response.write "<p>成功导入"&iXLS&"条数据"
conn1.close
set conn1=nothing
RsXLS.close
SET RsXLS=NOTHING
connXLS.close
set connXLS=nothing
'合格数据写入表开始子过程式
sub InputJGDM()
DIM rsJGDM,sqlJGDM
set rsJGDM=server.createobject("adodb.recordset")
sqlJGDM="select * from jgdm where jgdm='"&jgdm&"'"
rsJGDM.open sqlJGDM,conn1,1,3
if rsJGDM.eof then
rsJGDM.addnew
end if
rsJGDM("jgdm")=jgdm
rsJGDM("jgmc")=jgmc
rsJGDM.update
rsJGDM.close
set rsJGDM=nothing
end sub
'写入表结束
%>
说明：
以上三个文件为：default.aspx上传界面,default.aspx.cs处理
传参数到index.asp（注意asp文件，c#编码不是很熟悉所以用这法了，希望有人把他变更）
数据库字段要整理一下。
在index.asp文件中由于我的际实业务需要加正规处理问题，你可以删除。
--------------------编程问答-------------------- oo --------------------编程问答-------------------- 学习咯`
--------------------编程问答-------------------- cpp2017(慕白兄)正解 --------------------编程问答-------------------- #region 模板导出，导入处理
//*****下载己选择的单机模版
protected void btnDownDJJBXXTempt_ServerClick(object sender, EventArgs e)
{
//*****单机要素编号
string DJYSBHDBParamtes = "";
//*****引用Excel类库
UFIDAFramework.CommonLibrary.Reports.ExcelAccess.ExcelAccess mExcelAccess = new UFIDAFramework.CommonLibrary.Reports.ExcelAccess.ExcelAccess();
//****打开Excel处理
mExcelAccess.Open();
//****3、设置字体大小
System.Drawing.Font font = new System.Drawing.Font("黑体", 21, FontStyle.Bold);
System.Drawing.Font pFont = new System.Drawing.Font("宋体", 12, FontStyle.Regular);
//****6、表单报表表头,同时写入报表表头数据
mExcelAccess.MergeCells(1, 1, 1, 10);
mExcelAccess.SetFont(1, 1, 1, 10, font);
mExcelAccess.SetCellText(1, 1, 1, 10, "单机扩展信息");
//******合并行处理
//*****造册号
mExcelAccess.MergeCells(2, "A", 3, "A");
mExcelAccess.SetCellText(2, "A", 3, "A", "造册号");
//*****别号
mExcelAccess.MergeCells(2, "B", 3, "B");
mExcelAccess.SetCellText(2, "B", 3, "B", "别号");
//*****引进方式
mExcelAccess.MergeCells(2, "C", 3, "C");
mExcelAccess.SetCellText(2, "C", 3, "C", "引进方式");
//*****获取单机基本信息
DataSet DSet = new DataSet();
string DBParamtes = DelValue.Value;
//*****设置第四行的第一列为单机编号
mExcelAccess.SetCellText(1, "HO", DBParamtes);
mExcelAccess.SetColumnWidth("HO", 100f);
//*****设置第四行高度为0
mExcelAccess.SetRowHeight(4, 0);
string[] F_DJJBXXBH = DBParamtes.Split(',');
//****获取单机相应信息
DSet = (new ServiceProxy.Service()).DJ_JBXX_Base_Filter(" AND F_DJJBXXBH='" + F_DJJBXXBH[0] + "' AND F_ISDELETE=1");
//*****获取单机模版编号
DataTable DJMBDTable = DSet.Tables[0];
if (DJMBDTable.Rows.Count > 0)
{
DataRow DJMBDRow = DJMBDTable.Rows[0];
//*****获取单机模版编号
string F_DJMBBH = DJMBDRow["F_DYMBBH"].ToString();
//*****获取单机分类信息
DataTable DJFLDTable = new DataTable();
DJFLDTable = (new ServiceProxy.Service()).DJ_DYFL_Base_Filter(" AND F_DYMBBH='" + F_DJMBBH + "' AND F_ISDELETE=1 ORDER BY F_FLXH ASC ").Tables[0];
//****重新第二行的第4列开始输入单机分类信息
int colIndex = 3;
int endColindex = 3;
int DJYSSColIndex = 3;
//****输入单机分类标题
foreach (DataRow dRow in DJFLDTable.Rows)
{
//****构造表头处理
colIndex++;
endColindex++;
--------------------编程问答--------------------
//****获取单机分类编号
string F_DJDYFLBH = dRow["F_DJDYFLBH"].ToString();
//****得到单机分类要素总的个数
DataTable DJYSDTable = (new ServiceProxy.Service()).DJ_DYYS_Base_Filter(" AND F_DJDYFLBH='" + F_DJDYFLBH + "' AND F_IsDelete=1 order by F_DJDYYSBH asc").Tables[0];
//*****得到单机分类要素列数
int DJYSSCount = DJYSDTable.Rows.Count;
//*****合并行处理，同时设置合并行的相应分类信息
mExcelAccess.MergeCells(2, colIndex, 2, (colIndex + DJYSSCount) - 1);
mExcelAccess.SetCellText(2, colIndex, 2, (colIndex + DJYSSCount) - 1, dRow["F_DJDYFLMC"].ToString());
//****单机要素信息
foreach (DataRow DJYSSDRow in DJYSDTable.Rows)
{
DJYSSColIndex++;
//****单机要素名称
mExcelAccess.SetCellText(3, DJYSSColIndex, DJYSSDRow["F_YSMC"].ToString());
//****单机要素编号
mExcelAccess.SetCellText(4, DJYSSColIndex, DJYSSDRow["F_DJDYYSBH"].ToString());
if (DJYSBHDBParamtes == "")
{
DJYSBHDBParamtes = DJYSSDRow["F_DJDYYSBH"].ToString();
}
else
{
DJYSBHDBParamtes += "," + DJYSSDRow["F_DJDYYSBH"].ToString();
}
}
//*****计算合并行数处理
endColindex = (endColindex + DJYSSCount) - 1;
colIndex = (colIndex + DJYSSCount) - 1;
}
//*****设置第四行的第一列为单机编号
mExcelAccess.SetCellText(1, "IQ", "'" + DJYSBHDBParamtes + "'");
//mExcelAccess.SetColumnWidth("IQ", 255f);
}
//*****加载数据处理
int rowIndex = 5;
foreach (string DJJBXXBH in F_DJJBXXBH)
{
if (DJJBXXBH != "")
{
DataSet MBDSet = new DataSet();
//****获取单机相应信息
MBDSet = (new ServiceProxy.Service()).DJ_JBXX_Base_Filter(" AND F_DJJBXXBH='" + DJJBXXBH + "' AND F_ISDELETE=1");
//*****循环加载选择己有的单机数据集
foreach (DataRow DJMBDRow in MBDSet.Tables[0].Rows)
{
//*****造册号
mExcelAccess.SetCellText(rowIndex, "A", rowIndex, "A", DJMBDRow["F_ZUCH"].ToString());
//*****别号
mExcelAccess.SetCellText(rowIndex, "B", rowIndex, "B", DJMBDRow["F_BH"].ToString());
//*****引进方式
mExcelAccess.SetCellText(rowIndex, "C", rowIndex, "C", ConvertYJFS(Convert.ToInt32(DJMBDRow["F_YJFS"].ToString())));
//*****加载单机要素数据哈
//*****获取单机模版编号
string F_DJMBBH = DJMBDRow["F_DYMBBH"].ToString();
//*****获取单机分类信息
DataTable DJFLDTable = new DataTable();
DJFLDTable = (new ServiceProxy.Service()).DJ_DYFL_Base_Filter(" AND F_DYMBBH='" + F_DJMBBH + "' AND F_ISDELETE=1 ORDER BY F_FLXH ASC ").Tables[0];
//*****从第三行开始读取数据
int DJYSSColIndex = 3;
//****输入单机分类标题
foreach (DataRow dRow in DJFLDTable.Rows)
{
//****获取单机分类编号
string F_DJDYFLBH = dRow["F_DJDYFLBH"].ToString();
//****得到单机分类要素总的个数
DataTable DJYSDTable = (new ServiceProxy.Service()).DJ_DYYS_Base_Filter(" AND F_DJDYFLBH='" + F_DJDYFLBH + "' AND F_IsDelete=1 order by F_DJDYYSBH asc").Tables[0];
//****单机要素信息
foreach (DataRow DJYSSDRow in DJYSDTable.Rows)
{
DJYSSColIndex++;
//*****构造查询条件s
string strSQL = " AND F_DJJBXXBH='" + DJJBXXBH + "' AND F_DJDYYSBH='" + DJYSSDRow["F_DJDYYSBH"].ToString() + "'";
DataSet DJYSSJDSet = (new ServiceProxy.Service()).DJ_YSSJ_Base_Filter(strSQL);
if (DJYSSJDSet.Tables[0].Rows.Count > 0)
{
DataRow DJYSSJDRow = DJYSSJDSet.Tables[0].Rows[0];
//****单机要素名称
mExcelAccess.SetCellText(rowIndex, DJYSSColIndex, DJYSSJDRow["F_YSSJ"].ToString());
}
}
}
rowIndex++;
}
}
}
//****8、画边框及网格线
//mExcelAccess.SetBordersEdge(1, 1, dTable.Rows.Count - 1, dTable.Rows.Count, false);
//****9、下载文件
//****构造文件名
string FileName = UFIDAFramework.CommonLibrary.DevelopLibrary.Develops.Strings.BulidAutoDateCode(4);
//****保存文件到导出文件临时目录中
string mDirecotrPath = "..\\UploadFile\\DJJBXXTempte\\Excel";
//****获取文件夹路径
string DirecotrPath = Server.MapPath(mDirecotrPath);
if (Directory.Exists(DirecotrPath) == false)
{
//****创建文件夹
Directory.CreateDirectory(DirecotrPath);
}
string mSaveFiletPath = DirecotrPath + "\\" + FileName + ".xls";
//****10、保存文件
mExcelAccess.SaveAs(mSaveFiletPath, false);
//****显示Excel
//mExcelAccess.ShowExcel();
font.Dispose();
//***11、关闭报表
mExcelAccess.Close();
hidFilePath.Value = System.IO.Path.GetFileName(mSaveFiletPath);
//****清空处理哈
DelValue.Value = "";
Page.ClientScript.RegisterStartupScript(typeof(string), "scurity", "<script>HiddProccess();</script>");
}
--------------------编程问答-------------------- //****导入数据处理
protected void btnInputDJJBXX_ServerClick(object sender, EventArgs e)
{
//*****获取要导入的文件
//****１、获取上传的文件中径
string mFilePath = "UploadFile/DJJBXXTempte/InputExcel/" + hidInputFilePath.Value;
string mUploadPath = Server.MapPath("~/" + mFilePath);
//****2、验证文件是否存在
if (File.Exists(mUploadPath) == false)
{
return;
}
//****调用开发库类
UFIDAFramework.CommonLibrary.Reports.ExcelAccess.ExcelAccess mExcelAccess = new UFIDAFramework.CommonLibrary.Reports.ExcelAccess.ExcelAccess();
//****3、打开Excel文件
mExcelAccess.Open(mUploadPath);
//****4、读取Excel单元格数据
Excel.Range strDJJBXXBH = mExcelAccess.GetRange(1, "HO");
Excel.Range strDJYSBH = mExcelAccess.GetRange(1, "IQ");
//****5、得到单机基本信息编号
string mF_DJJBXXBH = (string)strDJJBXXBH.Text;
string DJYSBHDBParamtes = (string)strDJYSBH.Text.ToString().Replace("'", "").Replace("'", "");
//****6、导入文件格式不正确，就退出
if (mF_DJJBXXBH == "" || DJYSBHDBParamtes == "")
{
Page.ClientScript.RegisterStartupScript(typeof(string), "msg", "<script>alert('导入文件模版格式不正确,请重新选择！');</script>");
return;
}
//****7、重第4行开始读取数据
int intRow = 5;
int rowIndex = 0;
do
{
//****获得前三个单元格的值
string strF_ZCH = "";
string strF_BH = "";
int strF_YJFS = 1;
string F_DJJBXXBH = "";
string DBParamtes = "";
string[] DJDBParamtes = mF_DJJBXXBH.Split(',');
//****获取处理哈
F_DJJBXXBH = DJDBParamtes[rowIndex];
//****获取相应数据
strF_ZCH = mExcelAccess.GetCellText(intRow, 1);
strF_BH = mExcelAccess.GetCellText(intRow, 2);
strF_YJFS =YJFSConvert(mExcelAccess.GetCellText(intRow, 3));
//*****构造单机基本信息编号
string BulidDJJBXXDBParamtes = F_DJJBXXBH + "~" + strF_ZCH + "~" + strF_BH + "~" + strF_YJFS;
//****11、拆分要素编号
string[] mDJYSBH = DJYSBHDBParamtes.Split(',');
int intColIndex = 0;
//****8、循环获取行数据处理
for (int i = 4; i <= mDJYSBH.Length; i++)
{
intColIndex++;
if (DBParamtes == "")
{
DBParamtes = mExcelAccess.GetCellText(intRow, i) + "~" + mDJYSBH[intColIndex - 1].ToString();
}
else
{
DBParamtes += "\t" + mExcelAccess.GetCellText(intRow, i) + "~" + mDJYSBH[intColIndex - 1].ToString();
}
}
//****调用相应接口，更新数据处理
bool IsSucerity = (new ServiceProxy.Service()).DJ_JBXX_ExcelInput_Interface(BulidDJJBXXDBParamtes, DBParamtes);
if (IsSucerity == false) break;
intRow++;
//****获取单机基本信息编号
rowIndex++;
}
while (mExcelAccess.GetCellText(intRow, 1).ToString() != "");
//***9、关闭报表
mExcelAccess.Close();
//***10、删除导入文件处理
if (File.Exists(mUploadPath)==true)
{
//****删除文件
File.Delete(mUploadPath);
}
//***11、提示处理
Page.ClientScript.RegisterStartupScript(typeof(string), "msg", "<script>HiddenProccessBar();</script>");
}
#endregion
补充：.NET技术 , ASP.NET
