none
importar dados do excel para bd formatando células para texto RRS feed

  • Pergunta

  • Pessoal, esses dias me passaram uma tarefa aonde teria que importar dados de uma planilha excel para o banco de dados.
    até ai tudo bem.
    Acontece que descobri que eu não estava conseguindo importar todos os dados que eu necessitava pois tem algumas células que estão formatadas com texto e outras como número.
    teria alguma maneira de eu formatar as células para texto e assim garantir que eu consiga ler todas???

    O código que estou usando para ler a planilha é este:

    Private Sub percorre_excel()
            Dim DS As DataSet
            Dim dr As DataRow
       
            Dim strConn As String = "Provider=Microsoft.Jet.OleDb.4.0;" _
                                  & "data source=" & Lcaminho_arquivo & "\" & Lnome_arquivo & ";" _
                                  & "Extended Properties=Excel 8.0;"

            'Populando o grid
            Dim objConn As New OleDbConnection(strConn)
            ' a consulta é feita de acordo com os itens do excel e no campo from vem o nome da planilha
            Dim strSql As String = " Select COD_MUNICIPIO ," & _
                                   " IPM_2008 from [IPM$]"

            Dim objCmd As New OleDbCommand(strSql, objConn)
            Dim myAdapter As New OleDbDataAdapter(objCmd)
            Dim campo As String

            Try
                DS = New System.Data.DataSet
                myAdapter.Fill(DS)

                For Each dr In DS.Tables(0).Rows 'Show results in output window
                    'criar método que recebe como parâmetro os dados de select para inserir
                    campo = Convert.ToString(dr("COD_MUNICIPIO"))

                    If campo <> String.Empty And campo.ToUpper <> "TOTAL" Then
                        Insere_banco(campo, dr.Item(Lnome_arquivo).ToString())
                    End If

                Next

                DS.Dispose()
                objCmd.Dispose()
                myAdapter.Dispose()

                Call Enviar_email("Planilha IPM acessada com sucesso!")
                'apaga arquivo
                ' Call Apaga_arquivo(Lcaminho_arquivo & "\" & Lnome_arquivo & ".xls")
            Catch ex As Exception

                Me.RegisterClientScriptBlock("Alerta", "<script>alert('" & ex.ToString() & "')</script>")
                'Enviar_email("Erro  ao percorrer a planilha Ipm: " + ex.ToString())
            End Try

        End Sub

    o campo que está me dando problema é cod_municipio, seria interessante que eu pudesse formatar antes de ler os dados, pois recebemos esta planilha de um usuário, e não é garantido que ela venha formatada...
    alguém pode me dar uma idéia???
    segunda-feira, 29 de dezembro de 2008 17:46

Respostas

  • Olá Lissandra, consegui um tempinho hoje aqui para tentar solucionar esta questão.
    Você não vai conseguir isso usando OleDb ou qualquer forma que leia o Excel como base de dados, pois a formatação nas células, de alguma forma, causam alguma falha de leitura (que eu tentei não aprofudar).

    Usei bibliotecas COM+ de de manipulação de arquivos Excel.

    Referência do componente:
    http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel%28VS.80%29.aspx

    Aqui testei com:
    .NET 1.1 - Visual Studio 2003 - C#

    As bibliotecas que usei (COM+) foram:
    - Microsoft Excel 9.0 Object Library
    - Microsoft Office 9.0 Object Library

    Caso possua, você pode usar as mais novas, que, se não me engano, estão na versão 12, porém não sei a compatibilidade com .NET 2.0+.

    Mantive a classe Teste, apresentada em post anterior.

    Segue abaixo o código que usei para acessar a planilha e fazer loop nas linhas e apresentar resultado.

    Não esqueça de dar as devidas permissões de acesso ao usuário do IIS, a depender da versão que utiliza ai.

    Adicione no Web.config/App.config a segunte configuração:

    Web.config/App.config

    <identity impersonate="true"/>


    Referência de matriz usando o impersonante:
    http://www.microsoft.com/brasil/security/guidance/topics/devsec/secmod38.mspx

    A camada de apresetação, criei um DataGrid:

    ASPX

    <aspBig SmileataGrid Runat="server" ID="DataGrid1" AutoGenerateColumns="False">
        <Columns>
            <asp:BoundColumn DataField="Campo1" HeaderText="Item"></asp:BoundColumn>
            <asp:BoundColumn DataField="Campo2" HeaderText="Cod Municipio"></asp:BoundColumn>
            <asp:BoundColumn DataField="Campo3" HeaderText="IPM"></asp:BoundColumn>
        </Columns>
    </aspBig SmileataGrid>


    No CodeBehind:

    CodeBehind

    private void LerExcel()
    {
        ApplicationClass excel = new ApplicationClass();
        Workbook wb = excel.Workbooks.Open(@"c:\inetpub\wwwroot\WebProject1\IPM_2008.xls",Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
        Worksheet ws = (Worksheet)wb.Sheets[1];

        //Primeira linha com dados, pois a linha 1 contém titulo.
        int row = 2;

        ArrayList lista = new ArrayList();
        Teste item;

        //Loop nas linhas, onde o valor pra primeira célula != null.
        //Ao final desta rotina, você terá um ArrayList com n Objetos "Teste", contendo
        //os dados de sua planilha em memória.
        //De acordo com os testes que fiz aqui, incluindo outras planilhas, o acesso a elas
        //foi batante lento com o componente. Mas, pode ter sido algo na minha máquina
        while(((Range)ws.Cells[row,1]).Value2 != null)
        {
            item = new Teste();

            item.Campo1 = ((Range)ws.Cells[row,1]).Value2.ToString();
            item.Campo2 = ((Range)ws.Cells[row,2]).Value2.ToString();
            item.Campo3 = ((Range)ws.Cells[row,3]).Value2.ToString();

            lista.Add(item);
            row++;
        }

        ws = null;
        wb.Close(false,"",null);
        wb = null;
        excel = null;

        //Popula o DataGrid
        this.DataGrid1.DataSource=lista;
        this.DataGrid1.DataBind();
    }



    Bom, com isso ai, eu consegui acessar sua planilha e outras que fiz como teste, com diferentes formatações nas células.

    Espero ter ajudado !
    terça-feira, 13 de janeiro de 2009 14:32

Todas as Respostas

    • Sugerido como Resposta alexcollioni quinta-feira, 13 de fevereiro de 2014 18:35
    segunda-feira, 29 de dezembro de 2008 19:53
  • Oi Danilo! Valeu pela dica, só tem um porem....
    no exemplo:http://bytes.com/groups/net-vb/385790-how-do-i-set-excel-cell-format-text-vb-net

    não consegui adicionar esta biblioteca:
    Microsoft.Office.Interop.Excel.Workbook,
    não sei se tem no vs2003.

    Outra coisa, naquele exemplo ele formata a planilha toda?

    eu tentei fazer assim:

    Private Sub Formata_Excel()
            Dim style As Excel.Style

            'Application - É a aplicação Excel
            Dim excelApp As New Excel.Application
            'WorkBook  -  É o arquivo XLS
            Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
            excelBook = excelApp.Workbooks.Open(Lcaminho_arquivo & "\" & Lnome_arquivo)
            'WorkSheet - É a planilha Excel de trabalho
            Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)

            style = excelBook.Styles.Add("Style1")
            style.NumberFormat = "@"
            'Dim LINHA As Integer = 1
            'With excelWorksheet

            '    Do Until .Cells(LINHA, 1).Value = String.Empty
            '        .Cells.NumberFormat = "@"
            '        LINHA = LINHA + 1

            '    Loop

            'End With



            ' excelWorksheet.Cells.NumberFormat = "@"
            excelBook.Close()


        End Sub

    Mas ele não funciona legal...

    O outro eu não consegui usar....
    segunda-feira, 29 de dezembro de 2008 20:50
  • Fiz um teste aqui populando um DataGrid a partir de dados de uma planilha excel.
    A planilha é composta de duas colunas, sendo a segunda com formato numérico com 2 casas decimais.
    O resultado no DataGrid foram duas colunas com formatação de texto (número sem as casas decimais).

    Criei a classe abaixo para auxiliar no processo.

    public class Teste
    {
        private string _campo1;
        private string _campo2;

        public string Campo1
        {
            get{return _campo1;}
            set{ _campo1=value;}
        }

        public string Campo2
        {
            get{return _campo2;}
            set{ _campo2=value;}
        }
    }


    O método abaixo foi usado para popular o DataGrid

    private void CarregarDados()
    {
        string connString=@"Provider=Microsoft.Jet.OleDb.4.0; data source=c:\inetpub\wwwroot\WebProject1\basedados.xls;Extended Properties=Excel 8.0;";

        System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connString);
        System.Data.OleDb.OleDbCommand cmd = conn.CreateCommand();

        cmd.CommandText="select * from [Plan1$]";
        cmd.CommandType= System.Data.CommandType.Text;

        System.Data.IDataReader dr;
        conn.Open();
        dr=cmd.ExecuteReader();

        ArrayList lista = new ArrayList();
        Teste item;

        while(dr.Read())
        {
            item = new Teste();
            item.Campo1=dr[0].ToString();
            item.Campo2=dr[1].ToString();
            lista.Add(item);
        }

        this.DataGrid1.DataSource=lista;
        this.DataGrid1.DataBind();

        conn.Close();
    }


    Veja se isso te ajuda !!!
    terça-feira, 30 de dezembro de 2008 14:38
  • Danilo, seu código esta funcionando como o meu...ele pega quase todos os código_município, exceto os que não estão formatados, vc poderia me dar seu email? assim eu te passaria uma parte da planilha e vc vai entender o que eu to falando. o meu e-mail é lissandra.galhardi@gmail.com.
    Pois a questão da formatação não é em duas colunas diferentes e sim na mesma coluna. e como é um usuário que vai enviar esta planilha e esta vai ser lida por um serviço windows de forma automática eu tenho que garantir a leitura de todos os dados.
    Muito obrigada pela sua ajuda! Smile
    terça-feira, 6 de janeiro de 2009 11:55
  • Olá Lissandra, consegui um tempinho hoje aqui para tentar solucionar esta questão.
    Você não vai conseguir isso usando OleDb ou qualquer forma que leia o Excel como base de dados, pois a formatação nas células, de alguma forma, causam alguma falha de leitura (que eu tentei não aprofudar).

    Usei bibliotecas COM+ de de manipulação de arquivos Excel.

    Referência do componente:
    http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel%28VS.80%29.aspx

    Aqui testei com:
    .NET 1.1 - Visual Studio 2003 - C#

    As bibliotecas que usei (COM+) foram:
    - Microsoft Excel 9.0 Object Library
    - Microsoft Office 9.0 Object Library

    Caso possua, você pode usar as mais novas, que, se não me engano, estão na versão 12, porém não sei a compatibilidade com .NET 2.0+.

    Mantive a classe Teste, apresentada em post anterior.

    Segue abaixo o código que usei para acessar a planilha e fazer loop nas linhas e apresentar resultado.

    Não esqueça de dar as devidas permissões de acesso ao usuário do IIS, a depender da versão que utiliza ai.

    Adicione no Web.config/App.config a segunte configuração:

    Web.config/App.config

    <identity impersonate="true"/>


    Referência de matriz usando o impersonante:
    http://www.microsoft.com/brasil/security/guidance/topics/devsec/secmod38.mspx

    A camada de apresetação, criei um DataGrid:

    ASPX

    <aspBig SmileataGrid Runat="server" ID="DataGrid1" AutoGenerateColumns="False">
        <Columns>
            <asp:BoundColumn DataField="Campo1" HeaderText="Item"></asp:BoundColumn>
            <asp:BoundColumn DataField="Campo2" HeaderText="Cod Municipio"></asp:BoundColumn>
            <asp:BoundColumn DataField="Campo3" HeaderText="IPM"></asp:BoundColumn>
        </Columns>
    </aspBig SmileataGrid>


    No CodeBehind:

    CodeBehind

    private void LerExcel()
    {
        ApplicationClass excel = new ApplicationClass();
        Workbook wb = excel.Workbooks.Open(@"c:\inetpub\wwwroot\WebProject1\IPM_2008.xls",Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
        Worksheet ws = (Worksheet)wb.Sheets[1];

        //Primeira linha com dados, pois a linha 1 contém titulo.
        int row = 2;

        ArrayList lista = new ArrayList();
        Teste item;

        //Loop nas linhas, onde o valor pra primeira célula != null.
        //Ao final desta rotina, você terá um ArrayList com n Objetos "Teste", contendo
        //os dados de sua planilha em memória.
        //De acordo com os testes que fiz aqui, incluindo outras planilhas, o acesso a elas
        //foi batante lento com o componente. Mas, pode ter sido algo na minha máquina
        while(((Range)ws.Cells[row,1]).Value2 != null)
        {
            item = new Teste();

            item.Campo1 = ((Range)ws.Cells[row,1]).Value2.ToString();
            item.Campo2 = ((Range)ws.Cells[row,2]).Value2.ToString();
            item.Campo3 = ((Range)ws.Cells[row,3]).Value2.ToString();

            lista.Add(item);
            row++;
        }

        ws = null;
        wb.Close(false,"",null);
        wb = null;
        excel = null;

        //Popula o DataGrid
        this.DataGrid1.DataSource=lista;
        this.DataGrid1.DataBind();
    }



    Bom, com isso ai, eu consegui acessar sua planilha e outras que fiz como teste, com diferentes formatações nas células.

    Espero ter ajudado !
    terça-feira, 13 de janeiro de 2009 14:32
  • Maravilha Danilo! Era isso mesmo que a gente precisava! Funcionou perfeitamente! Eu vou adaptar para num serviço windows!
    Valeu mesmo!
    Big Smile

    terça-feira, 13 de janeiro de 2009 16:52