It is often necessary to output a variety of queries to Excel, so do the following small programs, used to output the result of a SQL SELECT query to an Excel format file, this program You only have to get it A recordset SQL SELECT query statement and a file name, the program can output the Excel format file, this program consists of three files, the file name of the first file 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, this is slightly
SQL = session ("TOEXCELSQL") 'This is the query statement to output Excel, such as "Samplect * form cai where gender =' female '" filename = "excel.xls"' to output the file name of the Excel file, you only Change the above two sentences, don't change anything else.
'You just modify the above two variables. Others I have done it.
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 of writing files set fs = createObject ("scripting.FilesystemObject") set ts = fs.createtextfile (server.mappath (filename), true) Ts.writeline (data) Ts.Close set ts = nothing set fs = nothingend Sub
Sub toExcel (filename, sql) 'This is a SQL statement and filename generating an Excel file set RS = Server.createObject ("AdoDb.Recordset") RS.Open SQL, CONN, 1, 3 TOEXCELLR = "
& Formator (RS (FieldName (I)), 2) &" "Else TOEXCELLR = TOEXCELLR &" | "end if else toexcellr = toExcellr &" | "& rs (fieldname (i)) &" "end if Next TOEXCELLR = TOEXCELLR & "" rs.movenext loop toExcellr = TOEX Cellr & "" 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> is generating an exlu file .... </ body> </ html> ************* The second file name is: di.txt content is as follows:</p>
<p><Table x: str border = 0 cellpadding = 0 cellspacing = 0 width = 288 style = 'border-collapse: collapse; table-layout: fixed; width: 216pt'> <tr height = 0 style <[if supportMisalignedColumns]!> = '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></p>
<p>*********** The file name of the third file is: Tou.txt content is as follows:</p>
<p><html xmlns: o = "URN: schema-microsoft-com: office: office" xmlns: x = "URN: schema-microsoft-com: office: Excel" xmlns = "http://www.w3.org/tr / REC-HTML40 "></p>
<p><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: locationsOFComponents href = "file: // /E :/msowc.cab "/> </ o: officedocumentSettings> </ xml> <! [Endif] -> <style> <! {Mso-displayed-decimal-separator:" /. MSO-Displayed-Thousand-separator: "/,";} @ page {margin: 1.0in .75in 1.0in .75in; mso-header-margin: .5in; mso-footer-margin: .5in;} Tr {mso -Height-Source: Auto; MSO-Ruby-Visibility: None;} C Ol {mso-width-source: auto; mso-ruby-visibility: none;} br {mso-data-placement: Same-cell;}. style0 {mso-number-format: general; text-align: general; vertical -Align: bottom; WHIGN-SPACE: 0; MSO-ROTE: 0; MSO-BACKGROUND-SOURCE: Auto; MSO-Pattern: Auto; Color: WindowText; font-size: 9.0pt; font-usight: 400; font- STYLE: NORMAL; Text-Decoration: None; Font-Family: å; mso-generic-font-family: Auto; MSO-FONT-CHARSET: 134; Border: None</p>
<p>MSO-PROTECTION: LOCKED Visible; MSO-Style-Name: MSO-style-id: 0;} TD {mso-style-parent: style0; padding-top: 1px; padding-right: 1px; padding-limited: 1PX; MSO-IGNORE: PADDING; Color: WindowText; font-size: 9.0pt; font-weight: 400; font-style: none; font-decoration: none; font-family: Song; MSO-generic-font-family : AUTO; MSO-NUMBER-FORMAT: General; Text-Align: General; Vertical-Align: Bottom; Border: None; MSO-Background-Source: Auto; MSO-Pattern: Auto; MSO -protection: Locked visible; white-space: 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;} RT {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> <x: EX CELWORKSHEET> <x: name> Sheet1 </ x: name> <x: WorksheetOptions> <x: DefaultRowHeight> 225 </ x: defaultHeight> <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: activelow> 24 </ x: activeRow> <x: ActiveCol> 5 </ x: ActiveCol> </ x: Pane> </ x: panes> < x: ProtectContents> False <</p>
<p>/ 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: Excelworksheet> <x: Excelworksheet> <x: name> sheet3 </ x: name> <x: WorksheetOptions> <x: defaultHeight> 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> 6600 </ x: Windowheight> <x: WindowWidth> 12000 <x: WindowWidth> <x: Windowtopx> 0 </ x: Windowtopx> <x: Windowtopy> 1395 </ x: windowtopy> <x: protectstructure> false </ x: protectststst RucTure> <x: protectwindows> false </ x: protectWindows> </ x: Excelworkbook> </ xml> <! [endif] -> </ head> <body link = blue vlink = purple></p></div><div class="text-center mt-3 text-grey">
转载请注明原文地址:https://www.9cbs.com/read-6839.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="6839" 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.043</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 = 'Yi5KAbMOwhP5wd0xlQxY7isBi1QVx8J40Efao6hhqhA4IAxDWczfUdmcmUN1Q8qjt7ekVilhbR8_2FftibX4A_2FZQ_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> |
