<% 'On Error Resume Next' Import Excel spreadsheet data to SQL Sever Database by Dicky 2004-12-27 16:41:12 QQ: 25941Function Open_CONN (SqlDatabaseEname, SqllocalName, SqlConn) Creates a database connection object and Open Database Connection DIM ConnStr 'SQL Server Database Connection Parameters: Database Name, User Password, User Name, Connection Name (Local Local, Field IP) Connstr = "Provider = SQLOLDB; User ID =" & SQLUSERNAME & "; Password = "& sqlPassword &"; Initial Catalog = "& SqlDatabaseName &"; Data Source = "& SqlLocalName &"; "Set sqlConn = Server.CreateObject (" ADODB.Connection ") SqlConn.Open ConnStr If Err Then Err.Clear Set sqlConn = Nothing response.write "Data connection error!" Response.end end if End function
Function Close_conn (SQLCONN) 'Closing the Database Connection and Clears Database Connection Object If IsObject (Sqlconn) Then Sqlconn.close Set Sqlconn = Nothing End IFEND Function
Call Open_CONN ("Shat_edg", "", "SA", "(local)", "SQLCONN) Opens SQL Server Database Connection
Function Get_EMP_CnName (NTACCNT) 'get the user name Chinese Dim Sql NT user account, Rs Sql = "Select EMP_CNAME From RF_EMPLOYEE Where EMP_NTACCNT ='" & NTACCNT & " '" Set Rs = Server.CreateObject ( "Adodb.RecordSet") Rs.Open SQL, SQLCONN, 1, 1 IF RS.EOF THEN GET_EMP_CNNAME = "" ELSE GET_EMP_CNNAME = RS ("EMP_CNAME") end if xi = nothingnd function%>
Import Excel spreadsheet Data to SQL SEVER Database </ TITLE> <body bgcolor = "# ACD9AF"> <center> <b> Import Excel spreadsheet data to SQL Sever Database </ b> </ center> <form method = "post" name = "FORM1"> Please select the data source: <input type = "file" name = "size =" 40 "title =" Please select the Excel file you want to export "> <input type = submit name = submit value =" Export "> <br> Select Target Table: <Select Name =" Table "Title =" Please select Table to Import Data "> <option> </ option> <% DIM Rssqldatabaseetable Set RssqlDatabaseetable = SqlConn.Openschema (20) Do While Not RssqldatabaseTable.eof%> <option <% IF TRIM (Request ("Table") = rssqldatabaseetable (2) Ten Response.write "SELECTED"% >> <% = rssqldatabaseetable (2)%> </ option> <% Rssqldatabaseetable.movenext: loop set rssqldatabaseetable = nothing%> </ select> </ for M> <font color = blue> Do not refresh the page during the export process! </ font> <br> <% IF Right (Request ("File")), 3) = "XLS" Ten Response.write "<font color = red> The Excel file you export is:" & Trim) Request ("File") & "" </ font> <br> "end if '=============</p>
<p>========= ASP reads the Excel injection =================================== == 'i) See the XLS file (BOOK) generated by Excel97 or Excel2000 as a database, each of which is a database table' II) ADO assumes that the first line in Excel is the field name. So you The defined range must include the line header (ie, the field name) in the first row 'III) Excel cannot include the number. Excel drives are wrong when this problem is encountered.</p>
<p>For example, your line title is "F1" 'IIII) If a column in your Excel spreadsheet contains text and numbers, then the ODBC driver of Excel will not be able to properly, processing the data type of this line, you must Ensure the data type of the column 'e-mail: kaxue@hotmail.com QQ: 484110 HomePage: www.flyday.net' Subscription Time: Thursday, May 23, 2002 5:54 PM Win2000 Server IIS5 test by '== ============================================================================================================================================================================================================= ================================= if Trim ("File") = "" "" "" "" "" "" "" "" "" "" "" "" " Please select the Excel file you want to export! </ Font> "Response.endelseif Right (Trim (Request (" File ")), 3) <>" XLS "Ten Response.write" <font color = red> Sorry, please make sure What you want to export excel files! </ Font> "Response.endelse 'lists all worksheets in the selected Excel file DIM EXECLFILE, OBJEXCELAPP, OBJEXCELBOOK EXECLFILE = TRIM (" file ") set objexcelapp = creteObject ( "Excel.Application") objExcelApp.Quit objExcelApp.DisplayAlerts = False 'without warning objExcelApp.Application.Visible = False' does not display interface objExcelApp.WorkBooks.Open (ExeclFile) set objExcelBook = objExcelApp.ActiveWorkBook reDim arrSheets (objExcelBook.Sheets .Count) for i = 1 to objexcelbook.sheets.count arrsheets (i) = ObjexcelBook.Sheets (i) .name 'Response.write Arrsheets (i) Next Objexcelapp.quit set objexeclapp = Nothing' Lists all worksheets in the selected Excel file END IF</p>
<p>DIM CONN, DRIVER, DBPATH, RS, SQLINSERT, SQLDELETE 'Establishing Connection Object SET CONN = Server.createObject ("AdoDb.Connection" DRIVER = "Driver = {Microsoft Excel Driver (* .xls)};"' dbpath = " DBQ = "& Server.mappath (" hrb.xls ") dbpath =" dbq = "& trim (Request (" file ")" Call Open method Open Database Conn.open Driver & dbpath</p>
<p>'DSN connection mode' conn.open "DSN = TEST" 'Note Name must be the following format "[Table Name $]" Writing for i = 1 to Ubound (arrsheets)' Start loop All worksheet SQL = " Select * from ["& Arrsheets (i) &" $] "set = conn.execute (sql) if xi = e e e = i i r w w" 没有 i i!!!!!!!!!!!!!!!!!!!! 'Response.write "<font color = blue> Worksheet: </ font> <font color = Green>" & arrsheets (i) & "</ font> <br>" do while not rs.eof sqlinsert = "Insert Into "& Trim (Request (" Table ")) &" (EDG_Project_Name, EDG_Project_No, EDG_Project_VM, EDG_Project_VM_CnName, EDG_Project_M, EDG_Project_M_CnName, EDG_Project_Director, EDG_Project_Director_CnName) Values ( ' "& Trim (Rs (0)) &"', ' "& Trim (Rs ( 1) & "','" & trim (rs (2)) & "','" & trim (rs (2)) & "(" & GET_EMP_CNNAME (TRIM (RS (2))) & ")" & "' , '"& TRIM (RS (3)) &"', '"& TRIM (" & GET_EMP_CNNAME (TRIM (RS (3))) & ")" & "', '" & TRIM (RS (4)) & "','" & TRIM ("& GET_EMP_CNNAME (" & GET_EMP_CNNAME (TRIM (rs (4))) & ")" "&") "" The value inserted here is based on the actual database structure Adjust SqlConn.execute (SQLINSERT) 'Insert all stuff in the Excel table in the Excel form "<font color = red> Congratulations, successfully export the worksheet in the Excel file [<font> <font color = Blue> "& arrsheets (i ) & "</ font> <font color = red>] data to SQL Server Data Sheet [<font color = blue>" & TRIM (Request ("Table") & "</ font>]! ^ _ ^ </ font> <br> "end if</p>
<p>Rs.close set = nothingnext 'Cycle All Worksheets End Call Close_conn (conn) Close Excel Database Connection Call Close_conn (SqlConn) Close SQL Server Database Connection%> </ body> </ html></p></div><div class="text-center mt-3 text-grey">
转载请注明原文地址:https://www.9cbs.com/read-76050.html</div><div class="plugin d-flex justify-content-center mt-3"></div><hr><div class="row"><div class="col-lg-12 text-muted mt-2"><i class="icon-tags mr-2"></i><span class="badge border border-secondary mr-2"><h2 class="h6 mb-0 small"><a class="text-secondary" href="tag-2.html">9cbs</a></h2></span></div></div></div></div><div class="card card-postlist border-white shadow"><div class="card-body"><div class="card-title"><div class="d-flex justify-content-between"><div><b>New Post</b>(<span class="posts">0</span>)
</div><div></div></div></div><ul class="postlist list-unstyled">
</ul></div></div><div class="d-none threadlist"><input type="checkbox" name="modtid" value="76050" checked /></div></div></div></div></div><footer class="text-muted small bg-dark py-4 mt-3" id="footer"><div class="container"><div class="row"><div class="col">CopyRight © 2020 All Rights Reserved
</div><div class="col text-right">Processed:
<b>0.054</b>, SQL:
<b>9</b></div></div></div></footer><script src="./lang/en-us/lang.js?2.2.0"></script><script src="view/js/jquery.min.js?2.2.0"></script><script src="view/js/popper.min.js?2.2.0"></script><script src="view/js/bootstrap.min.js?2.2.0"></script><script src="view/js/xiuno.js?2.2.0"></script><script src="view/js/bootstrap-plugin.js?2.2.0"></script><script src="view/js/async.min.js?2.2.0"></script><script src="view/js/form.js?2.2.0"></script><script>
var debug = DEBUG = 0;
var url_rewrite_on = 1;
var url_path = './';
var forumarr = {"1":"Tech"};
var fid = 1;
var uid = 0;
var gid = 0;
xn.options.water_image_url = 'view/img/water-small.png';
</script><script src="view/js/wellcms.js?2.2.0"></script><a class="scroll-to-top rounded" href="javascript:void(0);"><i class="icon-angle-up"></i></a><a class="scroll-to-bottom rounded" href="javascript:void(0);" style="display: inline;"><i class="icon-angle-down"></i></a></body></html><script>
var forum_url = 'list-1.html';
var safe_token = 'gASl_2BAr6nQ9bNEChnP1v256PxAakSGep4sY6DeEoB5nKOX1XIBgE6E774e34aCVLKdpaLiAZ3jz7fzAjUoyWAQ_3D_3D';
var body = $('body');
body.on('submit', '#form', function() {
var jthis = $(this);
var jsubmit = jthis.find('#submit');
jthis.reset();
jsubmit.button('loading');
var postdata = jthis.serializeObject();
$.xpost(jthis.attr('action'), postdata, function(code, message) {
if(code == 0) {
location.reload();
} else {
$.alert(message);
jsubmit.button('reset');
}
});
return false;
});
function resize_image() {
var jmessagelist = $('div.message');
var first_width = jmessagelist.width();
jmessagelist.each(function() {
var jdiv = $(this);
var maxwidth = jdiv.attr('isfirst') ? first_width : jdiv.width();
var jmessage_width = Math.min(jdiv.width(), maxwidth);
jdiv.find('img, embed, iframe, video').each(function() {
var jimg = $(this);
var img_width = this.org_width;
var img_height = this.org_height;
if(!img_width) {
var img_width = jimg.attr('width');
var img_height = jimg.attr('height');
this.org_width = img_width;
this.org_height = img_height;
}
if(img_width > jmessage_width) {
if(this.tagName == 'IMG') {
jimg.width(jmessage_width);
jimg.css('height', 'auto');
jimg.css('cursor', 'pointer');
jimg.on('click', function() {
});
} else {
jimg.width(jmessage_width);
var height = (img_height / img_width) * jimg.width();
jimg.height(height);
}
}
});
});
}
function resize_table() {
$('div.message').each(function() {
var jdiv = $(this);
jdiv.find('table').addClass('table').wrap('<div class="table-responsive"></div>');
});
}
$(function() {
resize_image();
resize_table();
$(window).on('resize', resize_image);
});
var jmessage = $('#message');
jmessage.on('focus', function() {if(jmessage.t) { clearTimeout(jmessage.t); jmessage.t = null; } jmessage.css('height', '6rem'); });
jmessage.on('blur', function() {jmessage.t = setTimeout(function() { jmessage.css('height', '2.5rem');}, 1000); });
$('#nav li[data-active="fid-1"]').addClass('active');
</script>
