ASP .NET - Exportando Excel para GridView e Salvando no SQL Server

ASP .NET - Exportando Excel para GridView e Salvando no SQL Server

Neste artigo vou mostrar como exportar dados de uma planilha Excel para uma página ASP .NET em um controle GridView e também como salvar os dados do GridView em um banco de dados SQL Server e gerar uma planilha Excel.

O objetivo não é criar uma rotina genérica mas mostrar uma das maneiras de se realizar a exportação de dados do Excel para o GridView e como Salvar em um banco de dados SQL Server.

Eu estou usando o Visual Web Developer 2010 Express Edition mas podemos usar o Visual Studio 2010.

Inicie o VWD 2010 e no menu File - New Project selecione Visual Basic -> Web e a seguir escolha o template : ASP .NET Empty Web Application informando o nome ExportarExcel e clicando no botão OK;

No menu Project -> Add New Item selecione o template WebForm e informe o nome Default.aspx para nossa página ASP .NET.

A seguir vamos definir o seguinte leiaute na página:

O código do arquivo Default.aspx pode ser visto na guia Source e esta mostrado a seguir:

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="ExportarExcel._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Importar dados do Excel em um GridView e Exportar para o Excel</title>
    <style type="text/css">
        .hdr
        {
            background: #ccc;
            font-family: Arial;
            font-size: 12px;
            color: #fff;
        }
        .ftr
        {
            background: #00c;
            color: #fff;
            font-family: Arial;
            font-size: 12px;
            color: #fff;
        }
        .hdr1
        {
            background: #FF0000;
            font-family: Arial;
            font-size: 12px;
            font-weight: bold;
            color: #fff;
        }
        .ftr1
        {
            background: #000;
            color: #fff;
            font-family: Arial;
            font-size: 12px;
            color: #fff;
        }
        .Row
        {
            background: #ccc;
            text-align: center;
            font-size: 12px;
            color: #000;
        }
        .Alt
        {
            background: ##808000;
            text-align: center;
            font-size: 12px;
            color: #000;
        }
    </style>

</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table cellpadding="10" cellspacing="10" style="font-family: Arial; font-size: 12px;
            border: solid 1px #ccc;" border="1" align="center">
            <tr>
                <td align="center">
                    <asp:FileUpload ID="FileUpload1" runat="server" />
                </td>
                <td>
                    <asp:Button ID="btnUpload" runat="server" Text="Enviar"
                        OnClick="btnUpload_Click" />
                </td>
            </tr>
            <tr>
                <td align="center" colspan="2">
                    <asp:GridView ID="gdvNotas" runat="server" AllowPaging="false"
                        OnPageIndexChanging="PageIndexChanging">
                        <HeaderStyle CssClass="hdr" />
                        <FooterStyle CssClass="ftr" />
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td align="center" colspan="2">
                    <asp:GridView ID="gdvResumoNotas" runat="server" AllowPaging="false">
                        <HeaderStyle CssClass="hdr1" />
                        <RowStyle CssClass="Row" />
                        <AlternatingRowStyle CssClass="Alt" />
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td align="center" colspan="2">
                    <asp:Button ID="SalvarExportarExcel" runat="server" Text="Salvar para o BD e Exportar para o Excel"
                        OnClientClick="exportarExcel()" />
                </td>
            </tr>
            <tr>
                <td colspan="2" align="center">
                    <asp:Label ID="lblError" runat="server" Text="Label"></asp:Label>
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

Observe que temos um estilo definido no arquivo e também uma função JScript que irá criar o arquivo Excel e exportar os dados.

Neste script estamos usando o objeto ActiveXObject para criar um objeto de Automação Excel atribuindo o valor a variável objeto oExcel.

A planilha Excel NotasAlunos.xlsx da qual vamos exportar dados possui o seguinte formato e dados:

E com base na estrutura dessa planilha que vamos obter um sumários das notas dos alunos e criar a tabela temporária e a tabela no banco de dados Excel para salvar os dados e exportar novamente os dados para o Excel.

A estrutura da tabela Notas que irá receber os dados sumarizados possui a seguinte estrutura:

USE [Agenda]
GO
CREATE TABLE [dbo].[Notas]
([codigo] [int] NULL,[nome] [nvarchar](50) NULL, [matricula] [nvarchar](50) NULL,[pontos] [nvarchar](50) NULL,[percentual] [nvarchar](50) NULL) ON [PRIMARY]

Script para gerar a tabela no SQL Server.

Deveremos definir no arquivo Web.Config as strings de conexão com a planilha Excel e com o banco de dados SQL Server que estamos usando para salvar os dados.

A seguir vamos o arquivo Web.Config com cada string de conexão definida. Para o Excel temos duas versões: uma para o Excel 2003 e outra para o Excel 2007:

<?xml version="1.0"?>

Executando a aplicação teremos a página principal:

Onde devemos selecionar a planilha NotasAlunos e enviá-la ao servidor:

Após isso os dados da planilha serão lidos e exportados para o controle GridView. Onde no primeiro apenas exibimos a planilha e no segundo efetuamos uma soma dos pontos de cada aluno e definindo um percentual:

Ao clicar no botão para salvar os dados e exportar para o Excel teremos primeiro os dados salvos na tabela Notas conforme podemos conferir abaixo:

O código completo do arquivo code-behind Default.aspx.vb pode ser visto a seguir:

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.IO
Imports System.Configuration
Public Class _Default
Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        lblError.Visible = False
    End Sub

    ' Vincula com o Grid
    Private Sub ImportarParaGrid(ByVal FilePath As String, ByVal Extension As String)
        Dim conStr As String = ""
        Select Case Extension
            Case ".xls"
                'Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
                Exit Select
            Case ".xlsx"
                'Excel 07
                conStr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
                Exit Select
        End Select

        conStr = [String].Format(conStr, FilePath, 1)
        Dim connExcel As New OleDbConnection(conStr)
        Dim cmdExcel As New OleDbCommand()
        Dim oda As New OleDbDataAdapter()
        Dim dt As New DataTable()
        cmdExcel.Connection = connExcel

        connExcel.Open()
        Dim dtExcelSchema As DataTable
        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
        connExcel.Close()

        'Le os dados da primeira planilha do Excel
        connExcel.Open()
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]"
        oda.SelectCommand = cmdExcel
        oda.Fill(dt)
        connExcel.Close()
        gdvNotas.DataSource = dt
        gdvNotas.DataBind()

        ' Cria arquivo temporário
        Dim dtTabela_Dados As New DataTable()
        dtTabela_Dados = dt
        'retorna a tabela com dados
        'Cria tabela temporaria
        Dim dtTemp As New DataTable()

        ' Cria linha de cabeçalho
        dtTemp.Columns.Add("Codigo")
        dtTemp.Columns.Add("Nome")
        dtTemp.Columns.Add("Matricula")
        dtTemp.Columns.Add("Pontos")
        dtTemp.Columns.Add("Percentual")

Dim soma As Integer
Dim drAddItem As DataRow

        Try
            For i As Integer = 0 To dtTabela_Dados.Rows.Count - 1
                drAddItem = dtTemp.NewRow()
                drAddItem(0) = dtTabela_Dados.Rows(i)("Codigo").ToString()
                drAddItem(1) = dtTabela_Dados.Rows(i)("Nome").ToString()
                'nome do aluno
                drAddItem(2) = dtTabela_Dados.Rows(i)("Matricula").ToString()
                'matricula
                'Soma
                soma = (Integer.Parse(dtTabela_Dados.Rows(i)("Portugues").ToString()) + Integer.Parse(dtTabela_Dados.Rows(i)("Ingles").ToString()) + Integer.Parse(dtTabela_Dados.Rows(i)("Matematica").ToString()) + Integer.Parse(dtTabela_Dados.Rows(i)("Fisica").ToString()))
                drAddItem(3) = soma.ToString()

                'Percentual(%)
                Dim prcnt As Integer = (soma * 100 / 800)
                drAddItem(4) = prcnt.ToString()
                dtTemp.Rows.Add(drAddItem)
            Next

            'Vincula dados no GridView
            gdvResumoNotas.DataSource = dtTemp
            gdvResumoNotas.DataBind()

        Catch ex As Exception
            lblError.Text = "Erro ao criar tabela a partir da planilha " & ex.Message
        End Try
    End Sub

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpload.Click
        If FileUpload1.HasFile Then
            Dim FileName As String = Path.GetFileName(FileUpload1.PostedFile.FileName)
            Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
            Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
            Dim FilePath As String = Server.MapPath(FolderPath + FileName)
            FileUpload1.SaveAs(FilePath)
            ImportarParaGrid(FilePath, Extension)
        End If
    End Sub

    Protected Sub PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gdvNotas.PageIndexChanging
        Dim FolderPath As String = ConfigurationManager.AppSettings("FolderPath")
        Dim FileName As String = gdvNotas.Caption
        Dim Extension As String = Path.GetExtension(FileName)
        Dim FilePath As String = Server.MapPath(FolderPath + FileName)

        ImportarParaGrid(FilePath, Extension)
        gdvNotas.PageIndex = e.NewPageIndex
        gdvNotas.DataBind()
    End Sub

    ' Escreve dados na planilha Excel
    Private Sub CriarPlanilhaExcel()
        If Int32.Parse(gdvResumoNotas.Rows.Count.ToString()) < 65536 Then
            gdvResumoNotas.AllowPaging = True
            'grvProdReport.DataBind()
            Dim tw As New StringWriter()
            Dim hw As New System.Web.UI.HtmlTextWriter(tw)
            Dim frm As New HtmlForm()

            Dim strTmpTime As String = (System.DateTime.Today).ToString()
            If strTmpTime.IndexOf("/") <> -1 Then
                strTmpTime = strTmpTime.Replace("/", "-").ToString().Trim()
            End If
            If strTmpTime.IndexOf(":") <> -1 Then
                strTmpTime = strTmpTime.Replace(":", "-").ToString().Trim()
            End If

            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            Response.AddHeader("content-disposition", "attachment;filename=sheet.xls")
            Response.Charset = "UTF-8"
            EnableViewState = False
            Controls.Add(frm)
            frm.Controls.Add(gdvResumoNotas)
            frm.RenderControl(hw)
            hw.WriteLine("<b> <u> <font-size:'5'> Notas dos Alunos </font> </u> </b>")
            Response.Write(tw.ToString())
            Response.[End]()
        Else
            'grvProdReport.AllowPaging = "True"
            'grvProdReport.DataBind()
            lblError.Visible = True
            lblError.Text = "Não é possível exportar para o Excel"
        End If
    End Sub

    ' inclui dados da planilha excel no banco de dados
    Private Sub incluir_dados()
        Dim i As Integer
        'obtem a string de conexão com a tabela Notas
        Dim s As String = ConfigurationManager.ConnectionStrings("Notas").ConnectionString
        Dim con As New SqlConnection(s)

        For i = 0 To gdvNotas.Rows.Count - 1
            Dim query As String = "insert into Notas values ('" + gdvResumoNotas.Rows(i).Cells(0).Text + "','" + gdvResumoNotas.Rows(i).Cells(1).Text.ToString() + "','" + gdvResumoNotas.Rows(i).Cells(2).Text + "','" + gdvResumoNotas.Rows(i).Cells(3).Text + "','" + gdvResumoNotas.Rows(i).Cells(4).Text + "')"
            Dim cmd As New SqlCommand(query, con)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            lblError.Visible = True
            lblError.Text = "Dados incluidos com sucesso na tabela Notas"
        Next
    End Sub

    Protected Sub SalvarExportarExcel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles SalvarExportarExcel.Click
        incluir_dados()
        ' inclui dados no banco de dados
        CriarPlanilhaExcel()
        ' Cria um arquivo excel
    End Sub
End Class

No código acima temos as rotinas incluir_Dados() que exporta os dados do gridview para a tabela Notas e a rotina CriarPlanilhaExcel() que cria a planilha no Excel.

A rotina ImportarParaGrid() importa os dados da planilha Excel NotasALunos para o GridView e cria um resumo com a somatória dos pontos de cada aluno exportando para o outro gridview.

O projeto completo esta no Super DVD .NET e no Super CD .NET.

Eu sei é apenas ASP .NET mas eu gosto...

"Falou-lhes pois Jesus outra vez, dizendo: Eu sou a luz do mundo; quem me segue não andará em trevas, mas terá a luz da vida." (João 8:12)

Referências:

José Carlos Macoratti