Believe that there are a lot of people useful programs to Excel to guide the needs of data, and done. General export some text data is very convenient, there are many options, such as the concatenation of text strings. CVS format (separated by commas and carriage returns, by default in Excel), such as the XLS file as data with SQL to operate and so on. What to do when you need to export picture data? This requires the use of the Excel.Application object.
In fact, with Excel.Application can do the Officeexcel software can do all the operation, the function is quite powerful. But each of us has limited learning and it is impossible to be familiar with each of them. As a then, I chose a lot of keywords in Baidu and Google search, hoping to get some valuable things. But looking for, did not find a full version, most of the inquiries, and the code contains a lot of obvious errors and functional lack of sex. It is worth criticizing that a large number of sites directly copy other people's site articles, but also inferior articles. Eh, don't look for it! Buddha said I do not go to hell who went to hell, so I gave you this work to do.
I first found a VBA manual collection, but fortunately it is a CHM format. Open the Vbaexcelxl10.chm, well, yes, yes, it's a good handbook, but, it's not a tutorial, want to cry no tears ... No way, had to look down hard scalp. Handbook after all is a manual, not a few details on the convenience of the operation, and will not be part of the content of the logical correlation is very complete. After my careful analysis, bold prediction, careful thinking, a large number of experiments, on the sword, under the hot pot, no, is the fire, it is not easy to achieve the perfect. Now, and put it overnight to web630.net, just want everyone to remember this site, at the same time hope that the technology station more original articles, for the development of China's program industry to do some contribution.
Copy Code code as follows:
<%
Rem Initialization excelapplication Working environment
Dim Excelapp,ebook,esheet
Set excelapp = CreateObject ("Excel.Application") ' establishes an Excel object
Excelapp.displayalerts=false ' does not show warning
Excelapp.application.visible=false ' does not display interface
Rem initialization of Excel data
' ExcelApp.Workbooks.Open (Server.MapPath ("Zzz.xls")) ' opens the Excel work book, which replaces the following line
Set ebook=excelapp.workbooks.add ' new Excel work Ben
Set ebook=excelapp.workbooks (1) ' references the first working book
Set esheet = ebook.worksheets (1) ' References first worksheet
Rem Data Import
Dim i,img
i = 1
For I=1 to 5
Esheet.cells (i,1). value= "Field One" &i
Esheet.cells (i,2). value= "Field two" &i
Esheet.cells (i,3). value= "Field three" &i
Esheet.cells (i,4). Select ' Selects cell 4th cells in row I
Set Img=esheet.pictures.insert (Server.MapPath ("people.jpg")) ' Inserts a picture in the above position and gets a reference to the picture
Img. Top=img. Top+2 ' Adjusts the picture position, the same below, otherwise it will press the table sideline
Img. Left=img. Left+2 ' units are pounds
Esheet.rows (i). Rowheight=img. Height+4 ' Adjusts the height of the current line so that it automatically is the same height as the picture
Next
Rem preservation of the work done above
' Ebook.save ' If you are opening an existing Excel file, you can use this line instead of the following line
Ebook.saveas Server.MapPath ("Zzz.xls")
Set esheet=nothing
Set ebook=nothing
' Excelapp.quit ' must exit, otherwise Excel's process stays in the operating system.
Set excelapp = Nothing
%>
One of the simpler ways for an ASP to read or write data to Exce is to use Excel as a database to manipulate SQL statements. Many places on the Internet have related articles, this article only as "ASP to Excel Guide data (picture)" of the auxiliary materials for reference.
Copy Code code as follows:
<%
Dim Conn,rs,sql
Sub dbopen ()
Dim Db:db=server.mappath ("Zzz.xls")
Set conn=server.createobject ("Adodb.connection")
On Error Resume Next
Conn. Open "Provider=Microsoft.Jet.OLEDB.4.0; Extended properties= "" Excel 8.0; Hdr=yes ";D ata source=" & DB
Rem HDR defaults to Yes, which means the first row is the field name, otherwise it is considered content
Rem for Excel2007, but should be: "provider=microsoft.ace.oledb.12.0; Extended properties=excel 12.0;data source=xxx.xlsx; "
If Err.number<>0 Then
Err.Clear
Response.Write ("Response.End ()
End If
On Error GoTo 0
End Sub
Sub dbclose ()
IF Isnotblank (conn) Then
Conn. Close ()
Set conn=nothing
End If
End Sub
Function Isnotblank (ByRef TempVar)
IsBlank = True
Select case VarType (TempVar)
Case 0,1 ' Empty & Null
IsBlank = False
Case 9 ' Object
If TypeName (TempVar) = "Nothing" Or TypeName (TempVar) = "Empty" Then
IsBlank = False
End If
End Select
End Function
Call Dbopen ()
Sql= "SELECT * from [sheet1$]" "Note the table name, you need to add a symbol after the sheet name $
Set Rs=conn. Execute (SQL)
While not Rs. Eof
Response.Write (RS (0) & ",")
Response.Write (RS (1) & ",")
Response.Write (RS (2) & "<br/>" &vbcrlf)
Rs. Movenext
Wend
Rs. Close:set rs=nothing
Call Dbclose ()
%>
