How to import data to excel

I often see some people asking how to transfer the table data to excel. Many people consider using execl to solve this problem first. appliction: I have used appliction. First, I have to master some of its internal usage. Second, it involves security when used on the client. The following describes three methods:

Method 1: Implemented in plain text format
This method is quite simple.CodeYou can easily understand
<% Set FSO = server. Createobject ("scripting. FileSystemObject ")
Strexcelfile = server. mappath ("txttoexcel.xls ")
If FSO. fileexists (strexcelfile) Then FSO. deletefile strexcelfile
Set destination file = FSO. createtextfile (strexcelfile, true)
Using file. writeline ("DF" & vbtab & "345" & vblf & "Fe" & vbtab & "mon" & vblf)
Closing file. Close
Set FSO = nothing
Response. Write "OK"
%>

Principle:
I believe everyone knows that excel can open text files (what? You don't know, you should first go back to work as a clerk for two months and then program again, so you don't understand how to break the rules), and excel will process the text content in it as follows: if you encounter a [Tab] Tab key, you will jump out of a column. If you encounter a [Line Break], you will change the next line. Therefore, we can sort out this rule to generate a single table-type Excel file,

Advantages:
Easy to master, just plain text sorting (Ha! It wasn't just a text game! When it comes to games, let's get started)
Disadvantages:
Low efficiency. If you convert a table with more content into an Excel file, you need to execute a long loop (). The document format can only be a rule row and column format, and you cannot set more tables.

Method 2: Use owc to implement:
This method is also simple (owc! Don't learn new things again? Don't worry, it's very simple, as long as you have a good head of men, OK, what? It's not enough. I'm sorry, I'm addicted to it, but I am not familiar with it (I didn't see anyone else on the Internet ), the Code is also given first:
<Script language = "JavaScript">
Function exportexcel (atbldata ){
If (typeof (export_object )! = "Object "){
Document. Body. insertadjacenthtml ("afterbegin", "<object style = 'display: none' classid = CLSID: 0002e510-0000-0000-c000-00000046 id = export_object> </Object> ");
}
With (export_object ){
Datatype = "htmldata ";
Htmldata = atbldata. outerhtml;
Try {
Activesheet. Export ("C: \ owctoexcel.xls", 0 );
Alert ('export finished ');
}
Catch (e ){
Alert ('failed to export the Excel table. Make sure that excel2000 (or later) is installed and the xls file of the same name' is not opened ');
}
}
}
</SCRIPT>

<Table id = "tbldata">
<Tr> <TD> gdsssa </TD> <TD> 445 </TD> </tr>
<Tr> <TD> gdsssa </TD> <TD> 445 </TD> </tr>
</Table>

<Input type = "button" value = "Export" onclick = "exportexcel (tbldata)">

Principle:
We should see that this is the front-end script HTML implementation. In fact, this is to use the owc function to transfer the Excel file. We only need to sort out a table file (I will not do this table file yet! The table we sorted out is basically what it looks like on the webpage and in Excel, but note: <style> does not work in Excel.
Advantages:
It is also very easy to master, and can be implemented at the front-end without Designing permission issues. It is a headache for the front-end to generate an Excel file stored on the client. This method has passed by and never missed out.
Disadvantages:
The client must install excel2000 or later (but this is generally not a problem), and the style sheet content cannot be output.

Method 3: use XML to generate
Well, let's not give the code first. Let's talk about the truth, cough and cough, and pay attention to it. This is a very important and difficult part of this lesson and must be tested every year, I don't know if everyone else is asleep. I don't want to talk about it! Sorry, I got the wrong lecture. As for this principle, you should first compile an Excel file, save it as a *. htm file, and then open it. Isn't the root of our general webpage content very similar (alas! What is it like? Open it in a text editor! Can't be omitted at all), so we just need to sort out these words, haha! It's also a text game (why are you staring at such a big eye? When it comes to gaming, the key is how to sort out this text file.
(Isn't the first method enough? Oh! Why didn't I expect it? Yes, it's too much trouble )? Let's analyze the text. The first part is the definition of the <style> style table, the second part is the definition of Excel, and the third part is the most familiar table data, for these three parts, the first two parts are basically fixed. I just need:
<HTML xmlns: S = "UUID: BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns: dt = "UUID: C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns: rs = "urn: Schemas-Microsoft-com: rowset" xmlns: z = "# rowsetschema" xmlns: O = "urn: Schemas-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;"/>
<Style>
. Xl24 {MSO-style-parent: style0; MSO-number-format: "\ @"; text-align: Right ;}
</Style>
<XML>
<X: excelworkbook>
<X: excelworksheets>
<X: excelworksheet>
<X: Name> sheet1 </X: Name>
<X: worksheetoptions>
<X: protectcontents> false </X: protectcontents>
<X: protectobjects> false </X: protectobjects>
<X: protectscenarios> false </X: protectscenarios>
</X: worksheetoptions>
</X: excelworksheet>
</X: excelworksheets>
</X: excelworkbook>
</XML>
</Head>

(Wow! It is no wonder that the MS software is so large because it has been compressed so much.) Of course, I may have deleted some useful software, you may find someone to fight for me (don't think it's so easy to learn, of course you have to do something)
Next, the third part. If this part is still read through loops and then compiled into the table's HTML Script, it is too hard to get ahead. As we all know, we can use recordset to read specified data content, and we can use save to directly generate an XML file for those content, so we only need to write an XSL file based on this XML file to convert it... haha, I think this method is the best, because we can set the table style at will without having to do a lot of loops, effectively solving some clever little actions in Excel (those little actions? You can enter the number 3423423423424 in an Excel file and check the date and time format ),
The idea is over, and how to implement it depends on each person's own hands.
The advantage of this method is that you can customize Excel files in a good format, as long as you can write XSL files, including statistics, so people who know XSL may come here to better understand, and the efficiency is very high. You don't need to loop over it.
Disadvantage: the technical requirements are a little higher. As for how high it is, different people are different.

I have attached my code. I hope you will not be able to copy the code, but make some improvements. Of course, there are better methods. Don't forget to inform me!

=========== Xmltoexcel. asp ========================

<% Set conn = server. Createobject ("ADODB. Connection ")
Conn. Open "provider = Microsoft. Jet. oledb.4.0; Data Source =" & server. mappath ("comp_apply.mdb ")

'Strselect = "select dept_no, dept_name, dept_check from dept_data"
Strselect = "select * From user_data" 'changing the SQL statement can change the Excel content.
Set rs = server. Createobject ("ADODB. recordset ")

Rs. Open strselect, Conn, 1, 1
Set FSO = server. Createobject ("scripting. FileSystemObject ")
Strxmlfile = server. mappath ("xmltoexceltmp. xml ")
Strpolicfile = server. mappath ("xmltoexcel. XSL ")
Strexcelfile = server. mappath ("xmltoexcel.xls ")
If FSO. fileexists (strxmlfile) Then FSO. deletefile strxmlfile
Rs. Save strxmlfile, 1
Rs. Close
Conn. Close
Set conn = nothing

Set xmldoc = server. Createobject ("Microsoft. xmldom ")
Set destination Doc = server. Createobject ("Microsoft. xmldom ")
Invalid Doc. Load (strw.file)
Xmldoc. Load (strxmlfile)

Xmldoc. loadxml (xmldoc. transformnode (invalid DOC ))
If FSO. fileexists (strexcelfile) Then FSO. deletefile strexcelfile
Set destination file = FSO. createtextfile (strexcelfile, true)
Using file. writeline (xmldoc. XML)
Closing file. Close
FSO. deletefile strxmlfile
Set FSO = nothing
Response. Redirect "xmltoexcel.xls"
%>

================ Xmltoexcel. XSL ======================

<XSL: stylesheet version = "1.0" xmlns: XSL = "http://www.w3.org/1999/XSL/Transform"
Xmlns: S = 'uuid: BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
Xmlns: dt = 'uuid: C2F41010-65B3-11d1-A29F-00AA00C14882'
Xmlns: rs = 'urn: Schemas-Microsoft-com: rowset'
Xmlns: z = '# rowsetscheme'>

<XSL: output method = "html" indent = "yes"/>

sheet1

false
false
false

<Body>
<Table border = "1" cellpadding = "0" cellspacing = "0">
<Tr> <XSL: For-each select = "/XML/S: schema/S: elementtype/S: attributetype">
<TH> <XSL: value-of select = "@ name"/> </Th>
</XSL: For-each>
</Tr>
<XSL: Apply-templates select = "Z: Row"/>
</Table>
</Body>
</Html>
</XSL: Template>

<XSL: template match = "Z: Row">
<XSL: variable name = "position"> <XSL: value-of select = "position ()"/> </XSL: Variable>

<Tr> <XSL: For-each select = "/XML/S: schema/S: elementtype/S: attributetype"> <! -- Read the defined data column name -->
<XSL: variable name = "strcolumn"> <XSL: value-of select = "@ name"/> </XSL: Variable>
<XSL: variable name = "strvalue"> <XSL: value-of select = "/XML/RS: Data/Z: Row [position () = $ position]/@ * [name () = $ strcolumn] "/> </XSL: Variable>
<TD class = "xl24">
<XSL: value-of select = "$ strvalue"/>
</TD>
</XSL: For-each>
</Tr>
</XSL: Template>

</XSL: stylesheet>

Supplement: For the third method, because after the recordset uses the Save method, sometimes the field names of null fields do not exist in the row, so I can read the field names from elementtype, to ensure that there is no error in the column orientation of excel at that time, but this problem occurs. net, because if such NULL data is encountered in dataset, empty nodes such as <columnname/> will also appear.