ASP imports data (pictures) to Excel. The final version of ASP operates Excel. Friends in need can refer to it. I believe there are many people who need to use programs to import data to Excel, and have done so. Generally, it is very convenient to export some text data. There are many optional methods, such as splicing text strings and saving them in .cvs format (separating the data with commas and carriage returns). , opened with Excel by default), for example, treating the xls file as data and using SQL to operate it, etc. What should I do when I need to export image data? This requires the use of the Excel.Application object.
In fact, using Excel.Application, you can do all the operations that OfficeExcel software can do, and the function is quite powerful. However, each of us has limited learning energy, and it is impossible for everyone to be familiar with it. So, I chose a lot of key I searched for words on Baidu and Google, hoping to get something of value. But after searching, I couldn't find a complete version. Most of them were inquiries, and the code contained many obvious errors and functional deficiencies. sex. It is worth criticizing that a large number of sites directly copy articles from other sites. And it’s still a low-quality article. Hey, don’t look for it! As the saying goes, if I don’t go to hell, who will go to hell, so I did this job for everyone.
I first found a collection of VBA manuals, and luckily it was in chm format. I opened the Vbaexcelxl10.chm in it, and, well, it’s not bad, it’s indeed a good manual, but it’s not a tutorial, and I feel like crying... I can’t help it. , I had no choice but to read on. After all, the manual is a manual, and it will not mention some convenient operations in details, nor will it explain the logical connection of each part of the content completely. After my careful analysis and bold prediction, Careful thinking, a lot of experiments, up the mountain of knives, down the hot pot, no, the sea of fire, finally achieved it perfectly. Now, I uploaded it to Web630.Net overnight, just hope everyone will remember this site, and I hope that each technology site will post more original articles and contribute to the development of China's programming industry.
Copy the code code as follows:
<%
Rem initializes the working environment of ExcelApplication
Dim ExcelApp,eBook,eSheet
Set ExcelApp = CreateObject(Excel.Application) 'Create an Excel object
ExcelApp.DisplayAlerts=false 'Do not display warnings
ExcelApp.Application.Visible=false 'Do not display the interface
Rem initializes Excel data
'ExcelApp.Workbooks.Open(Server.MapPath(zzz.xls)) 'Open the Excel workbook and replace the following line
Set eBook=ExcelApp.Workbooks.Add 'Create a new Excel workbook
Set eBook=ExcelApp.Workbooks(1) 'Reference the first workbook
set eSheet = eBook.Worksheets(1) 'Reference the 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 2&i
eSheet.Cells(i,3).Value=Field three&i
eSheet.Cells(i,4).Select 'Select the 4th cell in row i
Set img=eSheet.Pictures.Insert(Server.MapPath(people.jpg)) 'Insert a picture at the above location and get a reference to the picture
img.Top=img.Top+2 'Adjust the position of the picture, the same below, otherwise it will press the edge of the table
img.Left=img.Left+2 'The unit is pounds
eSheet.Rows(i).RowHeight=img.Height+4 'Adjust the height of the current row so that it is automatically the same as the height of the picture
Next
Rem saves the work done above
'eBook.Save 'If you are opening an existing Excel file, you can use this line to replace the following line
eBook.SaveAs Server.MapPath(zzz.xls)
Set eSheet=Nothing
Set eBook=Nothing
'ExcelApp.Quit' must be exited, otherwise the Excel process will remain in the operating system.
set ExcelApp = Nothing
%>
When ASP reads or writes data to Excel, a simpler method is to treat Excel as a database and use SQL statements to operate it. There are related articles in many places on the Internet. This article is only as "ASP imports data to Excel (picture)" The auxiliary information of "The Final Edition" is for your reference.
Copy the code code as follows:
<%
Dim conn,rs,sql
SubDBOpen()
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;Data Source= & db
Rem HDR defaults to YES, which means the first line is used as the field name, otherwise it is regarded as the content
Rem for Excel2007, it 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(<h1>The Database link is ERROR</h1>)
Response.End()
End If
On Error GoTo 0
End Sub
SubDBClose()
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
CallDBOpen()
sql=SELECT * FROM [Sheet1$] 'Pay attention to the writing of the table name. You need to add the symbol $ after the worksheet 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
CallDBClose()
%>