2. Tech
  3. About SQL Guide Excel

About SQL Guide Excel

xiaoxiao2021-03-06  26

Supplement: MSO-NUMBER-FORMAT: General; à mso-number-format: @; this can avoid digital transfer of scientific counting and zero disappearance. Use

SQL

of

SELECT

The result of the query is output

Excel

Format file, this program you only need to make a recordset

SQL

of

SELECT

Query the statement and a file name, the program can output

Excel

The format file, this program consists of three files, the file name of the first file is:

TOEXCEL.ASP

Is the primary 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" '' The file name of the Excel file to output, you only have to change the above two sentences, do not 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 for reading the file.

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 = Nothing

END FUNCTION

Sub Savetext (filename, data) 'This is a function for writing files.

Set fs = creteObject ("scripting.filesystemobject")

Set ts = fs.createtextfile (server.mappath (filename), true)

Ts.writeline (DATA)

Ts.close

SET TS = Nothing

SET FS = Nothing

End Sub

Sub toExcel (filename, SQL) 'This is a SQL statement and filename generates an Excel file

SET RS = Server.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 exlu file </ title></p> <p></ hEAD></p> <p><Body></p> <p>Generating an EXLCE file ....</p> <p></ Body></p> <p></ Html></p> <p>************** 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'></p> <p><! [if supportmisalignedcolumns]></p> <p><tr height = 0 style = 'Display: None'></p> <p><TD Width = 72 style = 'width: 54pt'> </ td></p> <p><TD Width = 72 style = 'width: 54pt'> </ td></p> <p><TD Width = 72 style = 'width: 54pt'> </ td></p> <p><TD Width = 72 style = 'width: 54pt'> </ td></p> <p></ TR></p> <p><! [Endif]></p> <p></ 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"</p> <p>XMLns: x = "URN: Schemas-Microsoft-Com: Office: Excel"</p> <p>XMLns = "http://www.w3.org/tr/rec-html40"></p> <p><HEAD></p> <p><meta http-equiv = content-type content = "text / html; charSet = GB2312"></p> <p><meta name = progid content = excel.sheet></p> <p><meta name = generator content = "Microsoft Excel 9"></p> <p><link rel = file-list href = "./ 222.files / filelist.xml></p> <p><link rel = edit-time-data href = "./ 222.files / editdata.mso"></p> <p><link rel = orabject-data href = "./ 222.files / oleData.mso></p> <p><! - [if gte mso 9]> <xml></p> <p><o: DocumentProperties></p> <p><o: Author> xky </ o: author></p> <p><o: Lastauthor> xky </ o: LastAuthor></p> <p><o: created> 2002-05-27T17: 51: 00Z </ o: create></p> <p><o: Lastsaved> 2002-06-22T10: 03: 03Z </ o: lastsaved></p> <p><o: Company> ZYDN </ o: Company></p> <p><o: Version> 9.2812 </ o: Version></p> <p></ o: DocumentProperties> <o: OfficeDocumentSettings></p> <p><o: Downloadcomponents /></p> <p><o: locationOfcomponents href = "file: /// e: / msowc.cab" /></p> <p></ o: OfficedocumentSettings></p> <p></ xml> <! [endif] -></p> <p><style></p> <p><! - TABLE</p> <p>{MSO-Displayed-Decimal-Separetor: "/."</p> <p>MSO-Displayed-Thousand-Separetor: "/,";</p> <p>@PAGE</p> <p>{margin: 1.0in .75in 1.0in .75in;</p> <p>MSO-HEADER-Margin: .5in;</p> <p>MSO-FOOTER-Margin: .5in;</p> <p>TR</p> <p>{MSO-HEIGHT-SOURCE: AUTO;</p> <p>MSO-Ruby-Visibility: none;</p> <p>COL</p> <p>{MSO-Width-Source: Auto;</p> <p>MSO-Ruby-Visibility: none;</p> <p>Br</p> <p>{MSO-DATA-Placement: Same-Cell;</p> <p>.style0</p> <p>{MSO-NUMBER-FORMAT: General;</p> <p>Text-align: general;</p> <p>Vertical-align: bottom;</p> <p>White-space: nowrap;</p> <p>MSO-ROTATE: 0;</p> <p>MSO-Background-Source: Auto;</p> <p>MSO-PATTERN: AUTO;</p> <p>Color: WindowText;</p> <p>FONT-SIZE: 9.0pt;</p> <p>Font-Weight: 400;</p> <p>Font-style: Normal;</p> <p>Text-decoration: none;</p> <p>FONT-FAMILY: Song;</p> <p>MSO-generic-font-family: auto;</p> <p>MSO-FONT-CHARSET: 134;</p> <p>Border: none;</p> <p>MSO-PROTECTION: LOCKED VISIBLE;</p> <p>MSO-Style-Name: General;</p> <p>MSO-style-id: 0;}</p> <p>TD</p> <p>{MSO-style-parent: style0;</p> <p>Padding-top: 1px;</p> <p>Padding-right: 1px;</p> <p>Padding-left: 1px;</p> <p>MSO-IGNORE: PADDING;</p> <p>Color: WindowText;</p> <p>FONT-SIZE: 9.0pt;</p> <p>Font-Weight: 400;</p> <p>Font-style: Normal;</p> <p>Text-decoration: none;</p> <p>FONT-FAMILY: Song;</p> <p>MSO-generic-font-family: auto;</p> <p>MSO-FONT-CHARSET: 134;</p> <p>MSO-NUMBER-FORMAT: General;</p> <p>Text-align: general;</p> <p>Vertical-align: bottom;</p> <p>Border: none;</p> <p>MSO-Background-Source: Auto;</p> <p>MSO-PATTERN: AUTO;</p> <p>MSO-PROTECTION: LOCKED Visible;</p> <p>White-space: nowrap;</p> <p>MSO-ROTATE: 0;</p> <p>.xl24</p> <p>{MSO-style-parent: style0;</p> <p>Border: .5pt solid windowtext;}</p> <p>.xl25</p> <p>{MSO-style-parent: style0;</p> <p>MSO-NUMBER-FORMAT: "Long Date";</p> <p>Text-align: Left;</p> <p>Border: .5pt solid windowtext;}</p> <p>Ruby</p> <p>{Ruby-align: Left;}</p> <p>RT</p> <p>{Color: WindowText;</p> <p>FONT-SIZE: 9.0pt;</p> <p>Font-Weight: 400;</p> <p>Font-style: Normal;</p> <p>Text-decoration: none;</p> <p>FONT-FAMILY: Song;</p> <p>MSO-generic-font-family: auto;</p> <p>MSO-FONT-CHARSET: 134;</p> <p>MSO-CHAR-TYPE: NONE;</p> <p>DISPLAY: NONE;</p> <p>-></p> <p></ style></p> <p><! - [if gte mso 9]> <xml></p> <p><x: Excelworkbook></p> <p><x: Excelworksheets></p> <p><x: Excelworksheet></p> <p><x: name> sheet1 </ x: name></p> <p><x: WorksheetOptions></p> <p><x: defaultRowHeight> 225 </ x: defaultRowheight></p> <p><x: print></p> <p><x: ValidprinterInfo /></p> <p><x: PapersizeIndex> 9 </ x: PapersizeIndex></p> <p><x: HorizontalResolution> -3 </ x: HorizontalResolution></p> <p><x: VerticalResolution> 0 </ x: VerticalResolution></p> <p></ x: print></p> <p><x: selected /></p> <p><x: panes></p> <p><x: Pane></p> <p><x: Number> 3 </ x: Number></p> <p><x: ActiveRow> 24 </ x: activeRow></p> <p><x: ActiveCol> 5 </ x: ActiveCol></p> <p></ x: pane></p> <p></ x: panes></p> <p><x: ProtectContents> false </ x: protectcontents></p> <p><x: ProtectObjects> false </ x: protectobjects></p> <p><x: ProtectsCenarios> False </ x: ProtectsCenarios></p> <p></ x: WorksheetOptions></p> <p></ x: Excelworksheet></p> <p><x: Excelworksheet></p> <p><x: name> Sheet2 </ x: name></p> <p><x: WorksheetOptions></p> <p><x: defaultRowHeight> 225 </ x: defaultRowheight></p> <p><x: ProtectContents> false </ x: protectcontents></p> <p><x: ProtectObjects> false </ x: protectobjects></p> <p><x: ProtectsCenarios> False </ x: ProtectsCenarios></p> <p></ x: WorksheetOptions></p> <p></ x: Excelworksheet></p> <p><x: Excelworksheet></p> <p><x: name> Sheet3 </ x: name></p> <p><x: WorksheetOptions></p> <p><x: defaultRowHeight> 225 </ x: defaultRowheight></p> <p><x: ProtectContents> false </ x: protectcontents></p> <p><x: protectObjects> false </ x: protectobjects> <x: protectscenarios> false </ x: protectscenarios></p> <p></ x: WorksheetOptions></p> <p></ x: Excelworksheet></p> <p></ x: Excelworksheets></p> <p><x: Windowheight> 6600 </ x: windowheight></p> <p><x: Windowwidth> 12000 </ x: WindowWidth></p> <p><x: windowtopx> 0 </ x: Windowtopx></p> <p><x: Windowtopy> 1395 </ x: windowtopy></p> <p><x: protectstructure> false </ x: protectstructure></p> <p><x: ProtectWindows> false </ x: protectWindows></p> <p></ x: Excelworkbook></p> <p></ xml> <! [endif] -></p> <p></ hEAD></p> <p><body link = blue vlink = purple></p></div><div class="text-center mt-3 text-grey"> 转载请注明原文地址:https://www.9cbs.com/read-80491.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="80491" 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.041</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 = 'xwx2vAaOwJrKDUXzeQz6DxTjqjMBrfkY_2BpGfxGM_2By87qx7v1_2BmUF10IO0saF_2BihTJSF1BhWTZv68V1B_2B'; 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>