ایجاد گزارشات از فایلهای اکسل در NET2

مقدمه

اکسل یکی از قوی ترین برنامه ها در ایجاد گزارشات و ارائه آمار های گرافیکی میباشد که سالهاست طرفداران خاص خود را راضی نگه داشته است.

با توجه به استفاده عموم از این برنامه ارائه گزارشات آن تحت وب مورد توجه قرار میگیرد. در این مقاله بصورت خلاصه وار به مراحل تولید گزارش از فایلهای اکسل در دات نت 2 میپردازیم. لینک اصلی مقاله به زبان انگلیسی در انتهای مطلب ذکر شده است.

آبرقراری ارتباط با اکسل

در واقع آبجکتهای COM زیادی برای برقراری ارتباط با اکسل وجود دارد. ولی در این مقاله ما میخواهیم از OleDB برای این منظور استفاده کنیم تا به اکسل بعنوان یک جدول ساده ارتباط برقرار کنیم. همچنین میتوانیم با استفاده از SQL queries اطلاعات مورد نیازمان را از فایل اکسل استخراج نمائیم.

حال باید Microsoft Excel 11.0 Object Library  را به Reference پروژه تان  اضافه نمائید:

نحوه افزودن Microsoft Excel 11.0 Object Library به Reference

نحوه افزودن Microsoft Excel 11.0 Object Library به Reference

فایل اکسل را در شاخه DataSource در ASAP.NET  اضافه  کنید.حال در فایل web.config خط زیر را وارد نمائید:

<add name="ExcelConnection" connectionString="Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|\SchoolMgt.xls;Extended Properties=Excel 8.0"/>

خواندن اطلاعات از شیت اکسل

برای این کار کافیست فرامین زیر را بنویسیم

Imports System.Data.OleDb
Imports System.Data

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
            Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
            Dim dbConn As New OleDbConnection(strExcelConn)
            Dim strSQL As String
 
            strSQL = "SELECT * FROM [" & cmbSheets.SelectedItem.ToString() & "$]"
 
            dbConn.Open()
 
            Dim cmd As New OleDbCommand(strSQL, dbConn)
            Dim dsExcel As New DataSet
            Dim daExcel As New OleDbDataAdapter(cmd)
 
            daExcel.Fill(dsExcel)
 
            dgResults.DataSource = dsExcel
            dgResults.DataBind()
        Catch ex As Exception
            Throw ex
        End Try
End Sub

 

 

بازی با دستوات SQL

حال برای اینکه گزارشاتی شبیه به خود اکسل تولید نمائیم و شاید فرمولی را پیاده سازی نمائیم میتوانیم از فرامین زیر استفاده کنیم:

Protected Sub btnGenerateReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGenerateReport.Click
Try
 
Dim strExcelConn As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ExcelConnection").ToString()
Dim dbConn As New OleDbConnection(strExcelConn)
Dim strSQL As String
 
strSQL = "SELECT S.StudentId, S.StudentName, M.Marks, G.Marks, (M.Marks+G.Marks) AS Total "& _
                     "FROM [Students$] S, [Mathematics$] M, [Geography$] G "& _
                     "WHERE(S.StudentId = M.StudentId And S.StudentId = G.StudentId) " & _
                     "ORDER BY (M.Marks+G.Marks) DESC"
dbConn.Open()
 
Dim cmd As New OleDbCommand(strSQL, dbConn)
Dim dsExcel As New DataSet
Dim daExcel As New OleDbDataAdapter(cmd)
 
daExcel.Fill(dsExcel)
 
dgReports.DataSource = dsExcel
dgReports.DataBind()
 
Catch ex As Exception
Throw ex
End Try
End Sub

 

ایجاد فایل اکسل از گزارشات:

روش اول استفاده از کلاس Response:

Protected Sub btnToExcelByResponse_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByResponse.Click
        '   Variables declaration
        Dim dsExport As New DataSet()
        Dim tw As New System.IO.StringWriter()
        Dim hw As New System.Web.UI.HtmlTextWriter(tw)
        Dim dgGrid As New DataGrid()
 
        dgGrid.DataSource = getData()
 
        '   Report Header
        hw.WriteLine("<b><u><font size='5'> DotNetClub Report </font></u></b>")
 
        '   Get the HTML for the control.
        dgGrid.HeaderStyle.Font.Bold = True
        dgGrid.DataBind()
        dgGrid.RenderControl(hw)
 
        '   Write the HTML back to the browser.
        Response.ContentType = "application/vnd.ms-excel"
        Me.EnableViewState = False
        Response.Write(tw.ToString())
        Response.End()
    End Sub

 

 استفاده از Microsoft Excel 11.0  Objects provide  برای اتصال مستقیم به اکسل

Imports System.Reflection
Imports Excel = Microsoft.Office.Interop.Excel
 
    Protected Sub btnToExcelByObjects_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnToExcelByObjects.Click
        Try
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
 
            xlWorkBook = New Excel.Application().Workbooks.Add(Missing.Value)
            xlWorkBook.Application.Visible = True
            xlWorkSheet = xlWorkBook.ActiveSheet
 
            '   Gets the dataset containing the data
            Dim dsData As DataSet = getData()
            Dim i As Integer = 2
 
            '   Outputting the fieldnames in pink bold color
            xlWorkSheet.Cells(1, 1) = "Student ID"
            xlWorkSheet.Cells(1, 2) = "Student Name"
            xlWorkSheet.Cells(1, 3) = "Mathematics"
            xlWorkSheet.Cells(1, 4) = "Geography"
            xlWorkSheet.Cells(1, 5) = "Total"
 
            xlWorkSheet.Range("$A1:$E1").Font.ColorIndex = Excel.Constants.xlColor1
            xlWorkSheet.Range("$A1:$E1").Font.Bold = True
 
            '   Outputting the data
            For Each dr As DataRow In dsData.Tables(0).Rows
                xlWorkSheet.Cells(i, 1) = dr(0)
                xlWorkSheet.Cells(i, 2) = dr(1)
                xlWorkSheet.Cells(i, 3) = dr(2)
                xlWorkSheet.Cells(i, 4) = dr(3)
 
                '   Building the formula for calculating the sum
                xlWorkSheet.Cells(i, 5).Formula = "=SUM($C{0}:$D{0})".Replace("{0}", i.ToString())
 
                '   Going to the next row
                i = i + 1
            Next
 
            '   Auto fit the columns
            xlWorkSheet.Columns.AutoFit()
 
            '   Generating the graph
            Dim chart As Excel.Chart
            chart = xlWorkBook.Charts.Add()
 
            With chart
                .ChartType = Excel.XlChartType.xlColumnClustered
                .SetSourceData(xlWorkSheet.Range("A1:E11"), 2)
 
                .HasTitle = True
                .ChartTitle.Characters.Text = "Students' marks"
 
                .Axes(1, Excel.XlAxisGroup.xlPrimary).HasTitle = True
                .Axes(1, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Students"
                .Axes(2, Excel.XlAxisGroup.xlPrimary).HasTitle = True
                .Axes(2, Excel.XlAxisGroup.xlPrimary).AxisTitle.Characters.Text = "Marks"
            End With
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

 

خلاصه:

مختصری درباره نحوه اتصال به فایل اکسل و تهیه گزارشات .

دو روش برای ایجاد فایل اکسل از گزارشات در ASP.NET2

منبع :http://www.beansoftware.com/ASP.NET-Tutorials/Generating-Excel-Reports.aspx

دانلود کد:Excel Reports Example ASP.NET Project