How to use ASP & Add Style & Format into excel sheet (Excel.Application) This is learn/tutorial asp developers how to using ASP script and Add Style and Format into excel.
ShotDev Focus:
- ASP & Add Style and Format into excel.
Example
asp_format_style.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 '*** Create Sheet 1 ***' xlBook.Worksheets(1).Name = "My Sheet1" xlBook.Worksheets(1).Select '*** Width & Height (A1:A1) ***' With xlApp.ActiveSheet.Range("A1:A1") .ColumnWidth = 40.0 .RowHeight = 25.0 End With '*** Write text to Row 1 Column 1 ***' With xlApp.ActiveSheet.Cells(1,1) .Value = "ShotDev.Com " .Font.Name = "Tahoma" .Font.Bold = True .VerticalAlignment = -4108 '*** Center Rows ***' .HorizontalAlignment = -4108 '*** Center Column ***' .Font.Size = 12 End With '*** Width & Height (A1:B1) ***' With xlApp.ActiveSheet.Range("A1:B1") End With '*** Write text to Row 1 Column 2 ***' With xlApp.ActiveSheet.Cells(1,2) .Value = "Mr.Weerachai Nukitram " .Font.Name = "Tahoma" .Font.Size = 20 End With '*** Width & Height (A2:A2) ***' With xlApp.ActiveSheet.Range("A2:A2") .BORDERS.Weight = 1 '*** Border ***' End With '*** Write text to Row 1 Column 2 ***' With xlApp.ActiveSheet.Cells(2,1) .Value = "I Love ShotDev.Com " .Font.Name = "Tahoma" .Font.Size = 10 .HorizontalAlignment = 4 End With '*** Width & Height (A3:D3) ***' With xlApp.ActiveSheet.Range("A3:D3") .BORDERS.Color = RGB(0, 0, 0) '*** Border Color ***' .BORDERS.Weight = 1 '*** Border ***' .MergeCells = True '*** Merge Cells ***' End With '*** Write text to Row 1 Column 2 ***' With xlApp.ActiveSheet.Cells(3,1) .Value = "I Love My Live" .Font.Name = "Tahoma" .Font.Size = 10 .HorizontalAlignment = -4108 .Interior.ColorIndex = 44 '*** Background Color ***' End With '*** Write text to Row 4 Column 5 ***' With xlApp.ActiveSheet.Cells(4,5) .Value = "My Life" .Font.Name = "Tahoma" .Font.Size = 10 .Font.Italic = True .Font.ColorIndex = 4 .EntireColumn.AutoFit '*** AutoFit Column ***' End With '*** Write text to Row 5 Column 5 ***' With xlApp.ActiveSheet.Cells(5,5) .Value = "My Life" .Font.Name = "Tahoma" .Font.Size = 10 .Font.Italic = True .Font.ColorIndex = 4 .HorizontalAlignment = -4152 '*** Text align Right ***' .EntireColumn.AutoFit '*** AutoFit Column ***' End With '*** Write text to Row 6 Column 1 ***' With xlApp.ActiveSheet.Cells(6,1) .Value = "Version 2010" .Font.Name = "Tahoma" .Font.Size = 10 .Font.ColorIndex = 4 .Characters(8, 12).Font.Bold = True End With '** SheetType 'xlChart = -4109; 'xlWorksheet = -4167; '** WBATemplate 'xlWBATWorksheet = -4167; 'xlWBATChart = -4109; '** Page Setup 'xlPortrait = 1; 'xlLandscape = 2; 'xlPaperA4 = 9; '** Format Cells 'xlBottom = -4107; 'xlLeft = -4131; 'xlRight = -4152; 'xlTop = -4160; '** Text Alignment 'xlHAlignCenter = -4108; 'xlVAlignCenter = -4108; '** Cell Borders 'xlThick = 4; 'xlThin = 2; '*** 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_format_style.asp
Screenshot