How to use ASP & Write Excel Multiple sheet into excel (Excel.Application) This is learn/tutorial asp developers how to using ASP script and Write/Create multiple sheet into excel
ShotDev Focus:
- ASP & Write multiple sheet into excel
Example
asp_multiple_sheet.asp
<%Option Explicit%> <html> <head> <title>ShotDev.Com Tutorial</title> </head> <body> <% Dim xlApp,xlBook,xlSheet1,FileName Dim Fso,MyFile FileName = "MyXls/MyExcel.xls" '*** Create Excel.Application ***' Set xlApp = Server.CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add xlApp.Application.Visible = False '*** Add Sheet 4 ***' xlBook.Sheets.Add() '*** Sheet Default=3 Add New 1 Sheet=4 ***' '*** Create Sheet 1 ***' xlBook.Worksheets(1).Name = "My Sheet1" xlBook.Worksheets(1).Select '*** Write text to Row 1 Column 1 ***' With xlApp.ActiveSheet.Cells(1,1) .Value = "ShotDev.Com 1" End With '*** Write text to Row 1 Column 2 ***' With xlApp.ActiveSheet.Cells(1,2) .Value = "Mr.Weerachai Nukitram 1" End With '*** Create Sheet 2 ***' xlBook.Worksheets(2).Name = "My Sheet2" xlBook.Worksheets(2).Select '*** Write text to Row 1 Column 1 ***' With xlApp.ActiveSheet.Cells(1,1) .Value = "ShotDev.Com 2" End With '*** Write text to Row 1 Column 2 ***' With xlApp.ActiveSheet.Cells(1,2) .Value = "Mr.Weerachai Nukitram 2" End With '*** Create Sheet 3 ***' xlBook.Worksheets(3).Name = "My Sheet3" xlBook.Worksheets(3).Select '*** Write text to Row 1 Column 1 ***' With xlApp.ActiveSheet.Cells(1,1) .Value = "ShotDev.Com 3" End With '*** Write text to Row 1 Column 2 ***' With xlApp.ActiveSheet.Cells(1,2) .Value = "Mr.Weerachai Nukitram 3" End With '*** Create Sheet 4 ***' xlBook.Worksheets(4).Name = "My Sheet4" xlBook.Worksheets(4).Select '*** Write text to Row 1 Column 1 ***' With xlApp.ActiveSheet.Cells(1,1) .Value = "ShotDev.Com 4" End With '*** Write text to Row 1 Column 2 ***' With xlApp.ActiveSheet.Cells(1,2) .Value = "Mr.Weerachai Nukitram 4" End With xlBook.Worksheets(1).Select '*** Focus Sheet 1 ***' '*** If Files Already Exist Delete files ***' Set Fso = CreateObject("Scripting.FileSystemObject") If (Fso.FileExists(Server.MapPath(FileName))) Then Set MyFile = Fso.GetFile(Server.MapPath(FileName)) MyFile.Delete End If '*** Save Excel ***' 'xlBook.PrintOut 1 '*** Print to printer ***' xlBook.SaveAs Server.MapPath(FileName) xlApp.Application.Quit '*** Quit and Clear Object ***' Set xlSheet1 = Nothing Set xlBook = Nothing Set xlApp = Nothing %> Excel Created <a href="<%=FileName%>">Click here</a> to Download. </body> </html>
Create a asp file and save to path root-path/myasp/
Run
http://localhost/myasp/asp_multiple_sheet.asp
Screenshot
2portugal…
…