Author: Unknown
Admiral often outputs a variety of query results into Excel, so make the following small program, used to output the result of a SQL SELECT query to an Excel format file, this program you only need to set it Since obtaining a record set SQL SQL SELECT query statement and a file name, the program can output the Excel format file, this program consists of three files, the first file name is: TOEXCEL.ASP is the main file, The content is as follows: <% 'The front is to link to the database. Please write the relevant statement yourself. Skose SQL = session ("TOEXCELSQL")' This is the query statement to output Excel, such as "SSECT * form cai where gender = 'Female' "FileName =" Excel.xls "'The file name of the Excel file to output, you can change the two sentences, don't change anything else.' You only need to modify the above two variables. Other I It is done. Call toExcel (filename, sql) set conn = Nothing function readtext (filename) 'This is a function set adf = server.createObject ("adoDb.stream") with adf .type = 2 .LineSeparator = 10 .Open .LoadFromFile (server.MapPath (FileName)) .Charset = "GB2312" .Position = 2 ReadText = .ReadText .Cancel () .Close () end with set ads = nothingend function sub SaveText (FileName , DATA) 'This is a function set fs = createObject ("scripting.filesystemObject") set ts = fs.createtextfile (Server.Mappath (filename), true) Ts.writeline (data) Ts.close Set TS = Nothing set fs = nothingnd sub sub toExcel (filename, sql) 'This is a SQL statement and filename generates an Excel file set = serv Er.CreateObject ("AdoDb.Recordset") rs.open SQL, CONN, 1, 3 TOEXCELLR = "
" & formatdatetime (RS (FieldName (I)), 2) & " " else toexcellr = TOEXCELLR & "| " end if else toexcellr = TOEXCELLR & " | " & RS (FIELDNAME (I)) & " "end if next toexcellr = toExcellr &" "rs.movenext loop toExcellr = toExcellr &" "Tou = readtext (" tou.txt ") Di = readtext (" di.txt ") TOEXCELLR = Tou & ToExcellr & Di Call Savetext (FileName, TOEXCELLR) End Sub%> is generating an EXLCE file </ title> </ head> < Body> The second file name is: di.txt content is as follows: <table x: str Border = 0 Cellpadding = 0 cellspacing = 0 width = 288 style = 'border-collapse: collapse; table-layout: fixed; width: 216pt'> <</p>
<p>! [if supportMisalignedColumns]> <tr height = 0 style = 'display: none'> <td width = 72 style = 'width: 54pt'> </ td> <td width = 72 style = 'width: 54pt'> < / TD> <td width = 72 style = 'width: 54pt'> </ td> <td width = 72 style = 'width: 54pt'> </ td> </ TR> <! [endif]> </ table > *********** The file name of the third file is: Tou.txt content is as follows: <html xmlns: o = "URN: schema-microsoft-com: office: office" xmlns: x = "URN: Schemas-Microsoft-Com: Office: Excel" xmlns = "http://www.w3.org/tr/rec-html40"> <head> <meta http-equiv = content-type content = "text / html; charSet = GB2312 "> <meta name = progid content = Excel.sheet> <meta name = generator content =" Microsoft Excel 9 "> <link rel = file-list href =" ./ 222.files / filelist. XML "> <link rel = edit-time-data href =" ./ 222.files / editdata.mso> <link rel = ole-object-data href = "./ 222.files / oleData.mso> < ! - [IF GTE MSO 9]> <xml> <o: DocumentProperties> <o: Author> xky </ o: author> <o: Lastauthor> xky </ o: Lastauthor> <o: create> 2002-05 -27T17: 51: 00Z </ o: create> <o: Lastsaved> 2002-06-22T10: 03: 03Z </ o: Lastsaved> <o: Company> ZYDN </ o: Company> <o: Version> 9 .2812 </ o: Version> </ o: DocumentProperties> <o: offnotocumentSettings> <o: DownloadComponents /> <o: location: /// e: /msowc.cab "/> </ o: OfficeDocumentSettings> </ xml></p>
<p><! [endif] -> <style> <! {mso-displayed-decimal-separator: "/."; mso-displayed-thousand-separator: "/,";} @ Page {margin: 1.0 IN .75IN 1.0IN .75in; mso-header-margin: .5in; mso-footer-margin: .5in;} Tr {mso-height-source: auto; mso-ruby-visibility: none;} col {mso- Width-source: auto; mso-ruby-visibility: None;} br {mso-data-placement: Same-cell;}. style0 {mso-number-format: general; text-align: general; value, vertical-align: bottom WHITE-SPACE: NOWRAP; MSO-ROTATE: 0; MSO-BACKGROTE: Auto; MSO-Pattern: Auto; Color: WindowText; font-size: 9.0; font-usight: 400; font-tempe;; TEXT-DECORATION: NONE; FONT-FAMILY: Song; MSO-generic-font-family: AUTO; MSO-FONT-CHARSET: 134; Border: None; MSO-Protection: Locked Visible; MSO-Style-Name: General; MSO -style-id: 0;} td {mso-style-parent: style0; padding-top: 1px; padding-right: 1px; padding-left: 1px; mso-ignore: padding; color: windowtext; font-size: 9.0pt; font-style: Normal; Text-Decoration: none; font-family: Song; MSO-generic-font-family: Auto; MSO-FONT-CHARSET: 134; M SO-NUMBER-ALIGN: BOTTOM; MSO-PATTERN: Auto; MSO-PATTERN: AUTO; MSO-PATTERN: AUTO; MSO-PATTERN: AUTO; MSO-PATTERN: AUTO; MSO-ROTATE: 0;}. Xl24 {mso-style-parent: style0; border: .5pt solid windowtext;}. Xl25 {mso-style-parent: style0; mso-number-format: "long date"; Text -Align: Left; border: .5pt solid windowtext;} ruby {ruby-align: left;} {color: windowtext; font-size: 9.0pt; font-weight: 400; font-style: normal; text-Decoration : None; Font-Family: Song; MSO-Generic-Font-Family: AUTO; MSO-FONT-CHARSET: 134; MSO-CHAR-TYPE: NONE; DISPLAY: NONE;} -> </ stop> <! - [IF GTE MSO 9]> <XML> <x: Excelworkbook> <x: Excelworksheets> <</p>
<p>x: ExcelWorksheet> <x: name> Sheet1 </ x: name> <x: WorksheetOptions> <x: defaultRowHeight> 225 </ x: defaultRowHeight> <x: print> <x: ValidprinterInfo /> <x: PapersizeIndex> 9 </ x: papersizeindex> <x: horizontalresolution> -3 </ x: horizontalresolution> <x: verticalResolution> 0 </ x: verticalResolution> </ x: print> <x: SELECTED /> <x: panes> <x : PANE> <x: Number> 3 </ x: Number> <x: activeRow> 24 </ x: activeroW> <x: ActiveCol> 5 </ x: ActiveCol> </ x: Pane> </ x: panes > <X: protectcontents> false </ x: protectcontents> <x: protectobjects> false </ x: protectobjects> <x: protectscenarios> false </ x: protectscenarios> </ x: WorksheetOptions> </ x: Excelworksheet> < x: ExcelWorksheet> <x: Name> Sheet2 </ x: Name> <x: WorksheetOptions> <x: DefaultRowHeight> 225 </ x: DefaultRowHeight> <x: ProtectContents> False </ x: ProtectContents> <x: ProtectObjects> False </ x: protectobjects> <x: protectscenarios> false </ x: protectscenarios> </ x: WorksheetOptions> </ x: Exce LWORKSHEET> <x: excelworksheet> <x: name> Sheet3 </ x: name> <x: WorksheetOptions> <x: defaultRowHeight> 225 </ x: defaultRowHeight> <x: protectcontents> false </ x: protectcontents> <x : ProtectObjects> false </ x: protectobjects> <x: protectscenarios> false </ x: protectscenarios> </ x: WorksheetOptions> </ x: Excelworksheet> </ x: Excelworksheets> <x: Windowheight></p></div><div class="text-center mt-3 text-grey">
转载请注明原文地址:https://www.9cbs.com/read-115128.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="115128" 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.055</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 = 'RuC_2F3sag01p8m_2FLm_2BhsYWgrvV76foZiMJu0Nwg5htCtZ8V_2Fk97O9ZYuMX10JfU0eoyvFo0rDNsLw611_2FDTXyzQ_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> |
