<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Import Namespace="Excel"%>
<%@ Import Namespace="System.IO"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim objConn As New SqlConnection
Dim dtAdapter As SqlDataAdapter
Dim dt As New System.Data.DataTable
Dim strConnString As String
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"
objConn = New SqlConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM customer"
'*** Create DataTable ***'
dtAdapter = New SqlDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
'*** Export To Excel ***'
Dim FileName As String
FileName = "MyXls/MyExcel.xls"
'*** Create Excel.Application ***'
Dim xlApp As New Excel.Application
Dim xlSheet1 As Excel.Worksheet
Dim xlBook As Excel.Workbook
Dim i As Integer
Dim intRows As Integer
xlBook = xlApp.Workbooks.Add()
xlBook.Application.Visible = False
'*** Create Sheet 1 ***'
xlSheet1 = xlBook.Worksheets(1)
xlSheet1.Name = "My Sheet1"
'*** Width & Height (A1:A1) ***'
With xlApp.ActiveSheet.Range("A1:A1")
.ColumnWidth = 10.0
End With
With xlApp.ActiveSheet.Range("B1:B1")
.ColumnWidth = 13.0
End With
With xlApp.ActiveSheet.Range("C1:C1")
.ColumnWidth = 23.0
End With
With xlApp.ActiveSheet.Range("D1:D1")
.ColumnWidth = 12.0
End With
With xlApp.ActiveSheet.Range("E1:E1")
.ColumnWidth = 13.0
End With
With xlApp.ActiveSheet.Range("F1:F1")
.ColumnWidth = 12.0
End With
With xlApp.ActiveSheet.Range("A1:F1")
.BORDERS.Weight = 1
.MergeCells = True
.Font.Bold = True
.Font.Size = 20
.HorizontalAlignment = -4108
End With
With xlApp.ActiveSheet.Cells(1,1)
.Value = "Customer Report"
End With
'*** Header ***'
With xlApp.ActiveSheet.Cells(2,1)
.Value = "CustomerID"
.Font.Bold = True
.VerticalAlignment = -4108
.HorizontalAlignment = -4108
.BORDERS.Weight = 1
End With
With xlApp.ActiveSheet.Cells(2,2)
.Value = "Name"
.Font.Bold = True
.VerticalAlignment = -4108
.HorizontalAlignment = -4108
.BORDERS.Weight = 1
End With
With xlApp.ActiveSheet.Cells(2,3)
.Value = "Email"
.Font.Bold = True
.VerticalAlignment = -4108
.HorizontalAlignment = -4108
.BORDERS.Weight = 1
End With
With xlApp.ActiveSheet.Cells(2,4)
.Value = "CountryCode"
.Font.Bold = True
.VerticalAlignment = -4108
.HorizontalAlignment = -4108
.BORDERS.Weight = 1
End With
With xlApp.ActiveSheet.Cells(2,5)
.Value = "Budget"
.Font.Bold = True
.VerticalAlignment = -4108
.HorizontalAlignment = -4108
.BORDERS.Weight = 1
End With
With xlApp.ActiveSheet.Cells(2,6)
.Value = "Used"
.Font.Bold = True
.VerticalAlignment = -4108
.HorizontalAlignment = -4108
.BORDERS.Weight = 1
End With
'***********'
intRows = 3
For i = 0 To dt.Rows.Count - 1
'*** Detail ***'
With xlApp.ActiveSheet.Cells(intRows,1)
.Value = dt.Rows(i)("CustomerID")
.BORDERS.Weight = 1
.HorizontalAlignment = -4108
End With
With xlApp.ActiveSheet.Cells(intRows,2)
.Value = dt.Rows(i)("Name")
.BORDERS.Weight = 1
End With
With xlApp.ActiveSheet.Cells(intRows,3)
.Value = dt.Rows(i)("Email")
.BORDERS.Weight = 1
End With
With xlApp.ActiveSheet.Cells(intRows,4)
.Value = dt.Rows(i)("CountryCode")
.HorizontalAlignment = -4108
.BORDERS.Weight = 1
End With
With xlApp.ActiveSheet.Cells(intRows,5)
.Value = FormatNumber(dt.Rows(i)("Budget"),2)
.BORDERS.Weight = 1
End With
With xlApp.ActiveSheet.Cells(intRows,6)
.Value = FormatNumber(dt.Rows(i)("Used"),2)
.BORDERS.Weight = 1
End With
intRows = intRows + 1
Next
'*** If Files Already Exist Delete files ***'
Dim MyFile As New FileInfo(Server.MapPath(FileName))
If MyFile.Exists Then
MyFile.Delete()
End IF
MyFile = Nothing
'*** Save Excel ***'
'xlSheet1.PrintOut 1 '*** Print to printer ***'
xlSheet1.SaveAs(Server.MapPath(FileName))
xlApp.Quit()
'*** Quit and Clear Object ***'
xlSheet1 = Nothing
xlBook = Nothing
xlApp = Nothing
'*** End Export To Excel ***'
Me.lblText.Text = "Excel Created <a href="& FileName & ">Click here</a> to Download."
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
End Sub
</script>
<html>
<head>
<title>ThaiCreate.Com ASP.NET - Excel Application</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>
<%@ Import Namespace="PdfSharp"%>
<%@ Import Namespace="PdfSharp.Drawing"%>
<%@ Import Namespace="PdfSharp.Pdf"%>
<%@ Import Namespace="PdfSharp.Pdf.IO"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
' Create a new PDF document
Dim DocPDF As PdfDocument = New PdfDocument
' Create an empty page
Dim objPage As PdfPage = DocPDF.AddPage
' Get an XGraphics object for drawing
Dim gfx As XGraphics = XGraphics.FromPdfPage(objPage)
' Create a font
Dim font1 As XFont = New XFont("Verdana", 15, XFontStyle.Bold)
Dim font2 As XFont = New XFont("Tahoma", 8, XFontStyle.Bold)
Dim font3 As XFont = New XFont("Tahoma", 8, 0)
' Draw the text
gfx.DrawString("My Customer", font1, XBrushes.Black, _
New XRect(0, 50, objPage.Width.Point, objPage.Height.Point), XStringFormats.TopCenter)
' Draw the text
gfx.DrawString("CustomerID", font2, XBrushes.Black, 65, 80, XStringFormats.TopLeft)
gfx.DrawString("Name", font2, XBrushes.Black, 140, 80, XStringFormats.TopLeft)
gfx.DrawString("Email", font2, XBrushes.Black, 215, 80, XStringFormats.TopLeft)
gfx.DrawString("CountryCode", font2, XBrushes.Black, 365, 80, XStringFormats.TopLeft)
gfx.DrawString("Budget", font2, XBrushes.Black, 425, 80, XStringFormats.TopLeft)
gfx.DrawString("Used", font2, XBrushes.Black, 485, 80, XStringFormats.TopLeft)
' Line
Dim pen As XPen = New XPen(XColor.FromArgb(0, 0, 0))
gfx.DrawLine(pen, New XPoint(65, 78), New XPoint(520, 78))
gfx.DrawLine(pen, New XPoint(65, 90), New XPoint(520, 90))
' Customer From Database (Start)
Dim objConn As New SqlConnection
Dim objCmd As New SqlCommand
Dim dtAdapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dt As DataTable
Dim strConnString,strSQL As String
strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase;Max Pool Size=400;Connect Timeout=600;"
strSQL = "SELECT * FROM customer"
objConn.ConnectionString = strConnString
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With
dtAdapter.SelectCommand = objCmd
dtAdapter.Fill(ds)
dt = ds.Tables(0)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Dim intLine As Integer = 90
Dim i As Integer
For i = 0 To dt.Rows.Count - 1
gfx.DrawString(dt.Rows(i)("CustomerID"), font3, XBrushes.Black, 65, intLine, XStringFormats.TopLeft)
gfx.DrawString(dt.Rows(i)("Name"), font3, XBrushes.Black, 140, intLine, XStringFormats.TopLeft)
gfx.DrawString(dt.Rows(i)("Email"), font3, XBrushes.Black, 215, intLine, XStringFormats.TopLeft)
gfx.DrawString(dt.Rows(i)("CountryCode"), font3, XBrushes.Black, 365, intLine, XStringFormats.TopLeft)
gfx.DrawString(dt.Rows(i)("Budget"), font3, XBrushes.Black, 425, intLine, XStringFormats.TopLeft)
gfx.DrawString(dt.Rows(i)("Used"), font3, XBrushes.Black, 485, intLine, XStringFormats.TopLeft)
intLine = intLine + 10
Next
' Customer From Database (End)
' Save the document...
Dim FileName As String = "MyPDF/PdfDoc.pdf"
DocPDF.Save(Server.MapPath(FileName))
DocPDF.Close()
DocPDF = Nothing
Me.lblText.Text = "PDF Created <a href=" & FileName & ">click here</a> to view"
End Sub
</script>
<html>
<head>
<title>ThaiCreate.Com ASP.NET - Send Mail</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>
Load balance : Server 04
