Export dữ liệu thành nhiều Sheet Excel trong Asp.net

By Unknown - Saturday, May 30, 2015 - No Comments

(Export dữ liệu ra nhiều Sheet trong Asp.net) - Khi thực hiện công việc tổng hợp trên phần mềm bạn cần Export dữ liệu của nhiều bảng ra 1 file và mỗi bảng nằm trên 1 Sheet. Việc dữ liệu tổng hợp nằm trên 1 file sẽ giúp bạn có được cái nhìn cũng như đánh giá một cách trực quan và tổng hợp nhất. Bài viết dưới đây sẽ giới thiệu với các bạn cách Export dữ liệu từ 2 bảng ra 1 file Excel và mỗi bảng sẽ là 1 Sheet.

Nghe những bài hát đỉnh nhất về Thấy cô giáo - Nghe trên Youtube

- B1: Tạo CSDL SQL Customers

- B2: Tạo Bảng Contacts có cấu trúc phía dưới

STT	Tên trường	Kiểu trường	Ghi chú
1	ContactID	Int	Trường tự tăng
2	AccountID	Int
3	ContactCode	nvarchar(25)
4	ContactName	nvarchar(250)
5	Sex	bit
6	Birthdate	datetime
7	ContactAddress	nvarchar(250)
8	Mobille	nvarchar(50)
9	Email	nvarchar(150)
10	CreatedDate	datetime
11	ModifiedDate	datetime

+ Tạo Bảng Accounts có cấu trúc phía dưới

STT	Tên trường	Kiểu trường	Ghi chú
1	AccountID	Int	Trường tự tăng
2	AccountCode	nvarchar(25)
3	AccName	nvarchar(250)
4	AccAddress	nvarchar(250)
5	AccPhone	nvarchar(50)
6	AccFAX	nvarchar(50)
7	AccEmail	nvarchar(50)
8	AccWebsite	nvarchar(150)
9	AccDesc	nvarchar(1500)
10	CreatedDate	datetime
11	ModifiedDate	datetime

- B3: Nhập dữ liệu cho bảng Customers và Accounts

- B4: Tạo các stored procedure trong SQL Server

USE [Customers]

CREATE PROCEDURE [dbo].[Pro_Contacts_List]

@Keyword nvarchar(250),

@SortField nvarchar(50),

@SortType nvarchar(10)

declare @strSQL nvarchar(1000)

declare @strWhere nvarchar(500)

declare @strOrder nvarchar (50)

set @strSQL= 'Select * from Contacts'

set @strWhere =' Where 1=1 '

if @Keyword<>''

set @strWhere= @strWhere +' And (ContactCode like N''%' +@Keyword+'%''

Or ContactName like N''%' +@Keyword+'%'' Or Address like N''%' +@Keyword+'%''

Or Mobille like N''%' +@Keyword+'%'' Or Email like N''%' +@Keyword+'%'')'

if @SortField='CreatedDate'

Begin

set @strOrder =' Order by CreatedDate'

End

Else

Begin

set @strOrder =' Order by ContactName'

End

set @strSQL=@strSQL+@strWhere+@strOrder

print @strSQL

exec sp_executesql @strSQL

CREATE PROCEDURE [dbo].[Pro_Accounts_List]

@Keyword nvarchar(250),

@SortField nvarchar(50),

@SortType nvarchar(10)

declare @strSQL nvarchar(1000)

declare @strWhere nvarchar(500)

declare @strOrder nvarchar (50)

set @strSQL= 'Select * from Accounts'

set @strWhere =' Where 1=1 '

if @Keyword<>''

set @strWhere= @strWhere +' And (AccountCode like N''%' +@Keyword+'%''

Or AccName like N''%' +@Keyword+'%'' Or AccAddress like N''%' +@Keyword+'%''

Or AccPhone like N''%' +@Keyword+'%'' Or AccFAX like N''%' +@Keyword+'%''

Or AccEmail like N''%' +@Keyword+'%'' Or AccWebsite like N''%' +@Keyword+'%'')'

if @SortField='CreatedDate'

Begin

set @strOrder =' Order by CreatedDate'

End

Else

Begin

set @strOrder =' Order by AccName'

End

set @strSQL=@strSQL+@strWhere+@strOrder

print @strSQL

exec sp_executesql @strSQL

Bạn có thể tải về bảng cơ sở dữ liệu SQL bằng cách nhấn vào liên kết tải về dưới đây

- B5: Tạo Project trong Microsoft Visual Studio 2010
Trong Visual Studio tạo 1 Class có tên: Utility và nhập đoạn Code phía dưới cho Class này.

Imports System.Data.SqlClient

Imports System.Data

Imports System.IO

Namespace ExportMultiSheetToExcel

Public Class SqlDataProvider

#Region "Membres Prives"

Shared _IsError As Boolean = False

Private _connectionString As String

#End Region

#Region "Constructeurs"

Public Sub New()

Try

_connectionString = ConfigurationManager.ConnectionStrings("SiteSqlServer").ConnectionString

_IsError = False

Catch ex As Exception

_IsError = True

End Try

End Sub

#End Region

#Region "Proprietes"

Public ReadOnly Property ConnectionString() As String

Get

Return _connectionString

End Get

End Property

#End Region

#Region "Functions"

Public Function FillTable(ByVal ProcName As String, ByVal ParamArray Para() As ObjectPara) As DataTable

Try

Dim tb As New DataTable

Dim adap As New SqlDataAdapter(ProcName, _connectionString)

adap.SelectCommand.CommandType = CommandType.StoredProcedure

If Not Para Is Nothing Then

For Each p As ObjectPara In Para

adap.SelectCommand.Parameters.Add(New SqlParameter(p.Name, p.Value))

End If

adap.Fill(tb)

Return tb

Catch ex As Exception

Return Nothing

End Try

End Function

#End Region

End Class

Public Class ObjectPara

Dim _name As String

Dim _Value As Object

Sub New(ByVal Pname As String, ByVal PValue As Object)

_name = Pname

_Value = PValue

End Sub

Public Property Name() As String

Get

Return _name

End Get

Set(ByVal value As String)

_name = value

End Set

End Property

Public Property Value() As Object

Get

Return _Value

End Get

Set(ByVal value As Object)

_Value = value

End Set

End Property

End Class

Public Class ExportToExcel

#Region "Private Members"

Const rowLimit As Integer = 65000

#End Region

#Region "Private Methods"

Private Function GetWorkbookTemplate() As String

Dim sb As StringBuilder = New StringBuilder(818)

sb.AppendFormat("<?xml version=""1.0""?>{0}", Environment.NewLine)

sb.AppendFormat("<?mso-application progid=""Excel.Sheet""?>{0}", Environment.NewLine)

sb.AppendFormat("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine)

sb.AppendFormat(" xmlns:o=""urn:schemas-microsoft-com:office:office""{0}", Environment.NewLine)

sb.AppendFormat(" xmlns:x=""urn:schemas-microsoft-com:office:excel""{0}", Environment.NewLine)

sb.AppendFormat(" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine)

sb.AppendFormat(" xmlns:html=""http://www.w3.org/TR/REC-html40"">{0}", Environment.NewLine)

sb.AppendFormat(" <Styles>{0}", Environment.NewLine)

sb.AppendFormat(" <Style ss:ID=""Default"" ss:Name=""Normal"">{0}", Environment.NewLine)

sb.AppendFormat(" <Alignment ss:Vertical=""Bottom""/>{0}", Environment.NewLine)

sb.AppendFormat(" <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>{0}", Environment.NewLine)

sb.AppendFormat(" ss:Bold=""1""/>{0}", Environment.NewLine)

sb.AppendFormat(" <Interior/>{0}", Environment.NewLine)

sb.AppendFormat(" <NumberFormat/>{0}", Environment.NewLine)

sb.AppendFormat(" <Protection/>{0}", Environment.NewLine)

sb.AppendFormat(" </Style>{0}", Environment.NewLine)

sb.AppendFormat(" <Style ss:ID=""s60"">{0}", Environment.NewLine)

sb.AppendFormat(" <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""{0}", Environment.NewLine)

sb.AppendFormat(" ss:Bold=""1""/>{0}", Environment.NewLine)

sb.AppendFormat(" <Alignment ss:Horizontal=""Center"" ss:Vertical=""Center""/>", Environment.NewLine)

sb.AppendFormat(" </Style>{0}", Environment.NewLine)

sb.AppendFormat(" <Style ss:ID=""s61"">{0}", Environment.NewLine)

sb.AppendFormat(" <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""{0}", Environment.NewLine)

sb.AppendFormat(" ss:Bold=""1""/>{0}", Environment.NewLine)

sb.AppendFormat(" <Borders>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" </Borders>", Environment.NewLine)

sb.AppendFormat(" <Alignment ss:Horizontal=""Center"" ss:Vertical=""Center""/>", Environment.NewLine)

sb.AppendFormat(" </Style>{0}", Environment.NewLine)

sb.AppendFormat(" <Style ss:ID=""s62"">{0}", Environment.NewLine)

sb.AppendFormat(" <NumberFormat ss:Format=""#,##0""/>", Environment.NewLine)

sb.AppendFormat(" <Alignment ss:Horizontal=""Center"" ss:Vertical=""Center""/>", Environment.NewLine)

sb.AppendFormat(" <Borders>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" </Borders>", Environment.NewLine)

sb.AppendFormat(" </Style>{0}", Environment.NewLine)

sb.AppendFormat(" <Style ss:ID=""s63"">{0}", Environment.NewLine)

sb.AppendFormat(" <NumberFormat ss:Format=""Short Date""/>{0}", Environment.NewLine)

sb.AppendFormat(" <Alignment ss:Horizontal=""Center"" ss:Vertical=""Center""/>", Environment.NewLine)

sb.AppendFormat(" <Borders>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" </Borders>", Environment.NewLine)

sb.AppendFormat(" </Style>{0}", Environment.NewLine)

sb.AppendFormat(" <Style ss:ID=""s64"">{0}", Environment.NewLine)

sb.AppendFormat(" <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""{0}", Environment.NewLine)

sb.AppendFormat(" ss:Bold=""0""/>{0}", Environment.NewLine)

sb.AppendFormat(" <Borders>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" <Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""/>", Environment.NewLine)

sb.AppendFormat(" </Borders>", Environment.NewLine)

sb.AppendFormat(" </Style>{0}", Environment.NewLine)

sb.AppendFormat(" <Style ss:ID=""s65"">{0}", Environment.NewLine)

sb.AppendFormat(" <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""{0}", Environment.NewLine)

sb.AppendFormat(" ss:Bold=""0""/>{0}", Environment.NewLine)

sb.AppendFormat(" <Alignment ss:Horizontal=""Right"" ss:Vertical=""Center""/>", Environment.NewLine)

sb.AppendFormat(" </Style>{0}", Environment.NewLine)

sb.AppendFormat(" </Styles>{0}", Environment.NewLine)

sb.Append("{0}\r\n</Workbook>")

Return sb.ToString()

End Function

Private Function ReplaceXmlChar(ByVal input As String) As String

input = input.Replace("&", "&amp")

input = input.Replace("<", "<")

input = input.Replace(">", ">")

input = input.Replace("""", """)

input = input.Replace("'", "'")

Return input

End Function

Private Function GetCell(ByVal type As Type, ByVal cellData As Object) As String

Dim data As String = If((TypeOf cellData Is DBNull), "", cellData)

If type.Name.Contains("Int") OrElse type.Name.Contains("Double") OrElse type.Name.Contains("Decimal") Then

Return String.Format("<Cell ss:StyleID=""s62""><Data ss:Type=""Number"">{0}</Data></Cell>", data)

End If

If type.Name.Contains("Date") AndAlso data.ToString() <> String.Empty Then

Return String.Format("<Cell ss:StyleID=""s63""><Data ss:Type=""DateTime"">{0}</Data></Cell>", Convert.ToDateTime(data).ToString("yyyy-MM-dd"))

End If

Return String.Format("<Cell ss:StyleID=""s64""><Data ss:Type=""String"">{0}</Data></Cell>", ReplaceXmlChar(data.ToString()))

End Function

Private Function GetWorksheets(ByVal Source As DataSet) As String

Dim ReportTitle As String = ""

Dim sw As StringWriter = New StringWriter()

If source Is Nothing OrElse source.Tables.Count = 0 Then

sw.Write("<Worksheet ss:Name=""Sheet1"">" & vbCr & vbLf & "<Table>" & vbCr & vbLf & "<Row><Cell><Data ss:Type=""String""></Data></Cell></Row>" & vbCr & vbLf & "</Table>" & vbCr & vbLf & "</Worksheet>")

Return sw.ToString()

End If

For Each dt As DataTable In source.Tables

If dt.Rows.Count = 0 Then

sw.Write("<Worksheet ss:Name=""" + ReplaceXmlChar(dt.TableName) + """>" & vbCr & vbLf & "<Table>" & vbCr & vbLf & "<Row><Cell ss:StyleID=""s62""><Data ss:Type=""String""></Data></Cell></Row>" & vbCr & vbLf & "</Table>" & vbCr & vbLf & "</Worksheet>")

Else

Select Case dt.TableName

Case "Account"

ReportTitle = "LIST ACCOUNT"

Case "Contact"

ReportTitle = "LIST CONTACT"

End Select

'write each row data

Dim sheetCount As Integer = 0

Dim iColumn As Integer = 1

iColumn = dt.Columns.Count - 1

For i = 0 To dt.Rows.Count - 1

If (i Mod rowLimit) = 0 Then

'add close tags for previous sheet of the same data table

If (i / rowLimit) > sheetCount Then

sw.Write(vbCr & vbLf & "</Table>" & vbCr & vbLf & "</Worksheet>")

sheetCount = (i / rowLimit)

End If

'Sheet Name

sw.Write(vbCr & vbLf & "<Worksheet ss:Name=""" + ReplaceXmlChar(dt.TableName) + (If(((i / rowLimit) = 0), "", (i / rowLimit))) + """>" & vbCr & vbLf & "<Table>")

'Column Width

Select Case dt.TableName

Case "Account"

sw.Write(vbCr & vbLf & "<Column ss:Width=""55"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""60"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""125"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""140"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""80"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""75"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""120"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""100"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""80"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""65"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""70"" />")

Case Else

sw.Write(vbCr & vbLf & "<Column ss:Width=""55"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""80"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""180"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""60"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""75"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""100"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""65"" />")

sw.Write(vbCr & vbLf & "<Column ss:Width=""70"" />")

End Select

sw.Write(vbCr & vbLf & "<Row>")

sw.Write(String.Format("<Cell><Data ss:Type=""String"">{0}</Data></Cell>", ""))

sw.Write("</Row>")

'===================Title=============

sw.Write(vbCr & vbLf & "<Row>")

sw.Write(String.Format("<Cell ss:MergeAcross=""" & iColumn & """ ss:StyleID=""s60""><Data ss:Type=""String"">{0}</Data></Cell>", ReportTitle))

sw.Write("</Row>")

'=================End Title===========

sw.Write(vbCr & vbLf & "<Row>")

sw.Write(String.Format("<Cell><Data ss:Type=""String"">{0}</Data></Cell>", ""))

sw.Write("</Row>")

'===========Total Record=========

Dim sTotalRow As String = ""

sTotalRow = "Total: " & dt.Rows.Count & " record"

sw.Write(vbCr & vbLf & "<Row>")

sw.Write(String.Format("<Cell ss:MergeAcross=""" & iColumn & """ ss:StyleID=""s65""><Data ss:Type=""String"">{0}</Data></Cell>", sTotalRow))

sw.Write("</Row>")

'========End Total Record========

sw.Write(vbCr & vbLf & "<Row>")

sw.Write(String.Format("<Cell><Data ss:Type=""String"">{0}</Data></Cell>", ""))

sw.Write("</Row>")

'write column name row

sw.Write(vbCr & vbLf & "<Row>")

Dim sColumnName As String = ""

For Each dc As DataColumn In dt.Columns

sw.Write(String.Format("<Cell ss:StyleID=""s61""><Data ss:Type=""String"">{0}</Data></Cell>", ReplaceXmlChar(dc.ColumnName)))

sw.Write("</Row>")

End If

'End Header Row

'Data

sw.Write(vbCr & vbLf & "<Row>")

For Each dc As DataColumn In dt.Columns

sw.Write(GetCell(dc.DataType, dt.Rows(i)(dc.ColumnName)))

sw.Write("</Row>")

sw.Write(vbCr & vbLf & "</Table>" & vbCr & vbLf & "</Worksheet>")

End If

Return sw.ToString()

End Function

Public Function GetExcelXml(ByVal dtInput As DataTable, ByVal filename As String) As String

Dim excelTemplate As String = GetWorkbookTemplate()

Dim ds As DataSet = New DataSet()

ds.Tables.Add(dtInput.Copy())

Dim worksheets As String = GetWorksheets(ds)

Dim excelXml As String = String.Format(excelTemplate, worksheets)

Return excelXml

End Function

Public Function GetExcelXml(ByVal dsInput As DataSet, ByVal filename As String) As String

Dim excelTemplate As String = GetWorkbookTemplate()

Dim worksheets As String = GetWorksheets(dsInput)

Dim excelXml As String = String.Format(excelTemplate, worksheets)

Return excelXml

End Function

Public Function ToExcel(ByVal dsInput As DataSet, ByVal filename As String) As String

Dim excelXml As String = GetExcelXml(dsInput, filename)

Return excelXml

End Function

Public Sub ToExcel(ByVal dtInput As DataTable, ByVal filename As String)

Dim ds As DataSet = New DataSet()

ds.Tables.Add(dtInput.Copy())

ToExcel(ds, filename)

End Sub

#End Region

End Class

End Namespace

Chú ý: Thuộc tính SiteSqlServer chính là chuỗi Connect với SQL Server trong file Web.Config

- B6: Mở file Default.aspxdưới dạng HTML và nhập mã HTML

<%@ Page Title="Export Multi Sheet ToExcel in ASP.Net" Language="vb" MasterPageFile="~/Site.Master" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="ExportMultiSheetToExcel._Default" %>

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">

<asp:ScriptManager ID="ScriptManager1" runat="server">

</asp:ScriptManager>

<h1>

Export Multi Sheet ToExcel in ASP.Net

</h1>

<br />

<asp:UpdatePanel ID="updatePanel" runat="server" UpdateMode="Conditional">

<tr>

<tr>

<asp:label id="lblHeader" resourcekey="lblGeneralActivitiesReport_Header" runat="server"></asp:label>

</td>

</tr>

</table>

</td>

</tr>

<tr>

<asp:LinkButton id="cmdExport" runat="server" CssClass="btn btn-small" Causesvalidation="false">

</asp:LinkButton>

</td>

</td>

</tr>

</table>

</ContentTemplate>

<asp:PostBackTrigger ControlID="cmdExport" />

</Triggers>

</asp:UpdatePanel>

</asp:Content>

- B7: Viết Code cho file Default.aspx

Namespace ExportMultiSheetToExcel

Public Class _Default

Inherits System.Web.UI.Page

#Region "Private Method"

#End Region

#Region "Bind Data"

Private Function BindContact() As DataTable

Dim objSQL As New SqlDataProvider

Dim AccountID As Integer = -1

Dim objBind As DataTable = objSQL.FillTable("Pro_Contacts_List", New ObjectPara("@Keyword", ""), _

New ObjectPara("@AccountID", AccountID), _

New ObjectPara("@SortField", "CreatedDate"), _

New ObjectPara("@SortType", "DESC"))

Return objBind

End Function

Private Function BindAccount() As DataTable

Dim objSQL As New SqlDataProvider

Dim objBind As DataTable = objSQL.FillTable("Pro_Accounts_List", New ObjectPara("@Keyword", ""), _

New ObjectPara("@SortField", "CreatedDate"), _

New ObjectPara("@SortType", "DESC"))

Return objBind

End Function

Private Function GetData() As DataSet

Dim ds As New DataSet

Dim dtObject As New DataTable

'Account

dtObject = New DataTable

dtObject = BindAccount()

ds.Tables.Add(dtObject)

'Contact

dtObject = New DataTable

dtObject = BindContact()

ds.Tables.Add(dtObject)

ds.Tables(0).TableName = "Account"

ds.Tables(1).TableName = "Contact"

Return ds

End Function

#End Region

#Region "Export Excel"

Private Sub ExportExcel(ByVal FileName As String)

Dim objExport As New ExportToExcel

Dim ds As New DataSet

Dim ExcelXml As String = ""

ds = GetData()

ExcelXml = objExport.ToExcel(ds, FileName)

Response.Clear()

Response.AppendHeader("Content-Type", "application/vnd.ms-excel")

Response.AppendHeader("Content-disposition", "attachment; filename=" + FileName & ".xls")

Response.Write(excelXml)

Response.Flush()

Response.End()

End Sub

#End Region

#Region "Event Handles"

Private Sub cmdExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdExport.Click

Try

Dim FileName As String = ""

FileName = "ExportMultiSheet"

ExportExcel(FileName)

Catch ex As Exception

End Try

End Sub

#End Region

End Class

End Namespace

Bây giờ chạy Project và kích nút Export bạn sẽ có kết quả như ảnh phía dưới.

Chúc các bạn thành công!

Quang Bình

News Ticker