<Inserir Valores no Excel ⁄ >

11701

2007-05-08

mySnippets asp.net

Download

Bookmark This

As folhas de cálculo, tão amplamente conhecidas pelo software Excel da Microsoft, são das ferramentas mais poderosas de suites *Office (sejam elas Microsoft*, Open*, Star*, [your suite here ...]). Este snippet visa a inserção de dados num ficheiro desse tipo (.xls).

Além da possivel necessidade de termos os dados num ficheiro xls para efectuarmos o tratamento dos mesmos, poderemos também ter por exemplo um template de um ficheiro excel (com gráficos, textos, pivot-tables, etc, etc.) e apenas queremos "alimentar" esse ficheiro com dados novos, mas mantendo a estrutura do template, podemos por exemplo adicionar um sheet, a qual irá conter todos os valores, aos quais outra sheet com os gráficos, etc, etc, irá buscar os valores (talvez seja um pouco confuso de explicar ...), suponhamos que temos uma sheet (folha) com o nome [Graficos], os gráficos presentes nessa sheet poderão ir buscar os valores a outra sheet, por exemplo [MyValuesSheet], isto faz com que basta alterarmos os valores da [MyValuesSheet] para que os gráficos automaticamente fiquem actualizados (como todos sabem ;)) , e é aqui que entra a finalidade deste snippet, preencher essa sheet com os valores que pretendemos.

Para que este snippet funcione correctamente termos de preparar o ficheiro Excel previamente:
- 1º - o nome da sheet - é necessário, pois irá informar para onde irão ser armazenados os valores;
- 2º - o nome das colunas - é necessário, pois irá informar qual a ordem como os valores são inseridos;

Vejam isto como se de uma base de dados normal se tratasse, a sheet será o nome da tabela, o nome das colunas será o nome dos campos que constituem a tabela.
Neste exemplo é utilizada a seguinte estrutura:
- MySheet(id, nome, morada, telefone);

Logo no ficheiro xls, dentro da sheet [MySheet], terá de ser criado 4 campos (ou se quiserem chamar cabeçalhos) : na célula "A1" inserimos o nome do campo "id"; B1="nome"; C1="morada"; D1="telefone"; ... and so on, and so on ... (os nomes não levam as "")

Uma página simples com um label (somente usado para mostrar o resultado da operação, se teve ou não sucesso), é o q.b. para este snippet (utilizei code-behind files, mas tal não é necessário)

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<!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 runat="server">
    <title>Inserir Valores num Ficheiro Excel</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblResult" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>

Na nossa página code-behind, vamos criar uns valores quaisquer para inserir e poderá ter as seguintes instruções:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
 
public partial class Default2 : System.Web.UI.Page {
    protected void Page_Load(object sender, EventArgs e) {
        string xlsFilename = Server.MapPath("~/teste/teste.xls");
        string xlsSheetName = "MySheet";
        ArrayList xlsFields = new ArrayList();
        ArrayList xlsValues = new ArrayList();
 
        //dummy data
        xlsFields.Add("id");
        xlsFields.Add("nome");
        xlsFields.Add("morada");
        xlsFields.Add("telefone");
 
        xlsValues.Add("1");
        xlsValues.Add("pedrocorreia.net");
        xlsValues.Add("Morada de Exemplo");
        xlsValues.Add("12 345 67 89");
        //
 
        try {
            Excel xls = new Excel(xlsFilename, xlsSheetName, xlsValues, xlsFields);
            xls.ExcelInsertRow();
            lblResult.Text = "Ficheiro actualizado com sucesso";
        }
        catch (Exception ex){
            //nota: deverá ser feito outro tipo de tratamento ...
            lblResult.Text="Ocorreu o seguinte erro: " + (ex.Message.ToString());
        }
    }
}

A class responsável pelo comunicação com o xls e a inserção dos dados será esta (deverá residir na pasta especial App_Code):

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
 
using System.Data.OleDb;
using System.Text;
using System.Collections;
 
/// <summary>
/// Class Excel
/// </summary>
public class Excel {
    private string _filename;
    private string _sheetName;
    private int _numFields;
    private ArrayList _fields;
    private ArrayList _values;
 
    /// <summary>
    /// Método construtor (inserção de dados)
    /// </summary>
    /// <param name="filename">Nome do ficheiro</param>
    /// <param name="sheetName">Nome da Sheet (folha do excel)</param>
    /// <param name="values">Valores a inserir</param>
    /// <param name="fields">Nome das colunas que definem a estrutura</param>
    public Excel(string filename, string sheetName, ArrayList values, ArrayList fields) {
        if (values.Count != fields.Count) {
            throw new Exception("O número de campos tem de coincidir com o número de valores");
        }
 
        this._numFields = fields.Count;
 
        this._filename = filename;
        this._sheetName = sheetName;
 
        this._values = new ArrayList();
        this._values = values;
 
        this._fields = new ArrayList();
        this._fields = fields;
 
    }
 
    /// <summary>
    /// Inserir valores no ficheiro Excel
    /// </summary>
    /// <returns>Estado da Operação</returns>
    public bool ExcelInsertRow() {
        bool result = true;
        StringBuilder sbFields = new StringBuilder();
        StringBuilder sbValuesParams = new StringBuilder();
        string iSQL, auxValor, auxCampo;
 
        //criar instrução sql
        sbFields.Append("(");
        sbValuesParams.Append("(");
 
        for (int i = 0; i < this._numFields; i++) {
            auxCampo = this._fields[i].ToString();
            sbFields.Append(auxCampo); //campos
            sbValuesParams.Append(String.Format("@{0}", auxCampo)); //parametros
 
            if (i < this._numFields - 1) {
                sbFields.Append(", ");
                sbValuesParams.Append(", ");
            }
        }
        sbFields.Append(")");
        sbValuesParams.Append(")");
 
        //construir instrução sql de insert
        iSQL = String.Format("Insert Into [{0}$] {1} Values {2}", this._sheetName, sbFields.ToString(), sbValuesParams.ToString());
        //
 
        OleDbConnection myConnection = new OleDbConnection(this.ExcelConnection());
        OleDbCommand myCommand = new OleDbCommand(iSQL, myConnection);
 
        try {
            for (int i = 0; i < this._numFields; i++) {
                //preencher valores
                auxCampo = this._fields[i].ToString();
                auxValor = this._values[i].ToString();
                myCommand.Parameters.Add(new OleDbParameter(String.Format("@{0}", auxCampo), auxValor));
            }
 
            myConnection.Open();
            myCommand.Connection = myConnection;
            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }
 
        catch (Exception ex) {
            //tratar da excepção
            myCommand.Dispose();
            myConnection.Close();
            throw new Exception(ex.Message.ToString());
            result = false;
        }
 
        return result;
    }
 
    /// <summary>
    /// Obter ConnectionString para ligação ao Excel
    /// </summary>
    /// <returns>ConnectionString</returns>
    private string ExcelConnection() {
        return @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this._filename + @";Extended Properties=""Excel 8.0;HDR=Yes;""";
    }
}

Qualquer erro/ dúvida, é só dizer!

11701

2007-05-08

mySnippets asp.net

Download

Bookmark This