Usando JExcel API numa aplicação WEB Struts

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (3)  (0)

Crie uma aplicação web que edita planilhas do Microsoft Excel.

Crie uma aplicação web que edita planilhas do Microsoft Excel

 

Hoje em dia as empresas usam planilhas Excel para organizar quase tudo que ainda não é organizado por um sistema. Muitas vezes, mesmo havendo um sistema específico para determinada tarefa (como o MS Project para gerenciamento de projetos ou JIRA para controle de Trouble Tickets), muitos ainda preferem usar planilhas Excel, por sua flexibilidade e facilidade de uso. Dessa forma, torna-se extremamente útil poder criar sistemas web que saibam manipular planilhas Excel, que é o que veremos nesse artigo.

 

Pré-requisitos

É suporto que o leitor saiba usar a ferramenta ANT para empacotar a aplicação em um arquivo WAR (Web Archive). Também é suporto que o mesmo saiba usar algum IDE Java para trabalhar com o código fonte que criaremos e a usar um Web Server como o Apache Tomcat para rodar a aplicação criada.

 

É necessário também baixar a versão 1.3.8 do framework Struts, que pode ser encontrado no site http://struts.apache.org e a JExcel API, que pode ser encontrada no site http://jexcelapi.sourceforge.net/. Não se esqueça de ter o ANT instalado em sua máquina no PATH do sistema.

 

Definindo a aplicação

A aplicação que criaremos será bem simples, mas suficiente para os fins de demonstração ao qual esse artigo se propõe. Nossa aplicação terá uma página inicial que permite ao usuário escolher um arquivo Excel em sua máquina para formatação. Ao clicar no botão “Formatar”, o arquivo será enviado ao servidor e será feita uma varredura em todas as células de cada planilha. Todas as células numéricas terão sua cor de fundo alterada para vermelho e todas as células com texto, para azul.

 

Criando a aplicação

Crie uma pasta raiz para a aplicação em seu disco, com o nome de JExcelApiStrutsExample. Essa pasta pode ser a pasta de seu projeto no seu IDE predileto. Dentro dessa pasta, crie as seguintes subpastas:

  • src:para guardar os arquivos “.java”
  • lib:para guardar os arquivos “.jar” de dependência
  • build:para guardar o build do ANT
  • etc:para guardar arquivos .properties
  • web/jsp: para guardar os JSPs
  • web/WEB-INF:para guardar deployment descriptors.
  • web/tld:para guardar arquivos de Tag library, como os que vem com o Struts.

Copie os JARS necessários do struts e da JExcel API para a pasta libs. Isso inclui o arquivo jxl.jar da JExcel API e todos os JARs incluídos na pasta lib da distribuição do struts. Copie os arquivos TLD do struts para a pasta web/tld.

 

Crie um arquivo chamado index.jsp dentro da pasta web, conforme mostra a listagem 1. Esse é o único arquivo que ficará fora da pasta JSP. Coloque o seguinte conteúdo no mesmo:

 

<%@ taglib uri="/tags/struts-logic" prefix="logic" %>

<logic:redirect forward="welcome"/>

Listagem 01. Arquivo web/index.jsp

 

Tudo o que esse JSP faz é redirecionar a página atual para o JSP definido como welcome na configuração do struts.

 

Como nossa aplicação é bem simples, usaremos só mais um JSP. Crie um arquivo chamado Welcome.jsp na pasta web/jsp e então coloque nele o conteúdo da listagem 2.

 

<%@ taglib uri="/tags/struts-bean" prefix="bean"%>

<%@ taglib uri="/tags/struts-html" prefix="html"%>

<%@ taglib uri="/tags/struts-logic" prefix="logic"%>

<html:html>

<head>

<title>Exemplo JExcel API</title>

</head>

<html:base />

<body bgcolor="white">

 

<h3>Exemplo JExcel API</h3>

<p>Por favor, escolha o arquivo excel a ser analisdo</p>

<BR>

<html:form action="/FormatExcelAction.do" method="post"

      enctype="multipart/form-data">

      <html:file property="arquivo" title="Arquivo Excel" />

      <html:submit title="Processar arquivo" />

</html:form>

 

</body>

</html:html>

Listagem 02. Arquivo web/jsp/Welcome.jsp

 

Esse JSP simplesmente usa algumas tag libs do Struts para renderizar um form HTML com um input do tipo arquivo e um botão submit. Perceba que o form foi configurado para ser enviado via método POST com a codificação de multipart. Isso fará com que arquivo selecionado seja enviado pelo próprio stream http (e não como parâmetro da URL) e também fará com que o arquivo binário seja transformado em texto para que possa ser enviado viu http, numa codificação MIME específica para esse fim. O importante disso tudo é que quando o arquivo for recebido no lado do servidor, será preciso codificá-lo de volta para binário.

 

Antes de complicarmos mais, vejamos como ficam o web.xml  e o struts-config.xml, nas listagens 3 e 4, respectivamente.

 

<?xml version="1.0" encoding="ISO-8859-1"?>

 

<!DOCTYPE web-app

  PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"

  "http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">

 

<web-app>

  <display-name>JExcel Api Struts Example</display-name>

 

  <!-- Standard Action Servlet Configuration (with debugging) -->

  <servlet>

    <servlet-name>action</servlet-name>

    <servlet-class>org.apache.struts.action.ActionServlet</servlet-class>

    <init-param>

      <param-name>config</param-name>

      <param-value>/WEB-INF/struts-config.xml</param-value>

    </init-param>

    <init-param>

      <param-name>debug</param-name>

      <param-value>2</param-value>

    </init-param>

    <init-param>

      <param-name>detail</param-name>

      <param-value>2</param-value>

    </init-param>

    <load-on-startup>2</load-on-startup>

  </servlet>

 

 

  <!-- Standard Action Servlet Mapping -->

  <servlet-mapping>

    <servlet-name>action</servlet-name>

    <url-pattern>*.do</url-pattern>

  </servlet-mapping>

 

 

  <!-- The Usual Welcome File List -->

  <welcome-file-list>

    <welcome-file>index.jsp</welcome-file>

  </welcome-file-list>

 

 

  <!-- Struts Tag Library Descriptors -->

  <taglib>

    <taglib-uri>/tags/struts-bean</taglib-uri>

    <taglib-location>/WEB-INF/tld/struts-bean.tld</taglib-location>

  </taglib>

 

  <taglib>

    <taglib-uri>/tags/struts-html</taglib-uri>

    <taglib-location>/WEB-INF/tld/struts-html.tld</taglib-location>

  </taglib>

 

  <taglib>

    <taglib-uri>/tags/struts-logic</taglib-uri>

    <taglib-location>/WEB-INF/tld/struts-logic.tld</taglib-location>

  </taglib>

 

  <taglib>

    <taglib-uri>/tags/struts-nested</taglib-uri>

    <taglib-location>/WEB-INF/tld/struts-nested.tld</taglib-location>

  </taglib>

 

  <taglib>

    <taglib-uri>/tags/struts-tiles</taglib-uri>

    <taglib-location>/WEB-INF/tld/struts-tiles.tld</taglib-location>

  </taglib>

 

</web-app>

Listagem 03. Arquivo web/WEB-INF/web.xml

 

<?xml version="1.0" encoding="ISO-8859-1" ?>

 

<!DOCTYPE struts-config PUBLIC

          "-//Apache Software Foundation//DTD Struts Configuration 1.3//EN"

          "http://struts.apache.org/dtds/struts-config_1_3.dtd">

<struts-config>

      <!-- ================================================ Form Bean Definitions -->

      <form-beans>

            <form-bean name="FormatExcelForm" type="FormatExcelForm" />

      </form-beans>

 

      <!-- ========================================= Global Exception Definitions -->

      <global-exceptions></global-exceptions>

 

      <!-- =========================================== Global Forward Definitions -->

      <global-forwards>

            <forward name="welcome" path="/Welcome.do" />

      </global-forwards>

 

      <!-- =========================================== Action Mapping Definitions -->

      <action-mappings>

            <!-- Default "Welcome" action -->

            <!-- Forwards to Welcome.jsp -->

            <action path="/Welcome" forward="/WEB-INF/jsp/Welcome.jsp" />

 

            <action path="/FormatExcelAction" type="FormatExcelAction"

                  name="FormatExcelForm" scope="request" validate="false"

                  input="/WEB-INF/jsp/Welcome.jsp">

                  <forward name="sucesso" path="/WEB-INF/jsp/Welcome.jsp" />

            </action>

      </action-mappings>

</struts-config>

Listagem 04. Arquivo web/WEB-INF/struts-config.xml

 

Nada demais, para quem já está acostumado com Struts, certo? Simplesmente configuramos o servlet controller do Struts no web.xml e indicamos as TLDS. No struts-config.xml, configuramos o forward welcome, que havíamos usado no index.jsp, para que redirecionasse para o Welcome.jsp que criamos. Como esse último fica dentro de WEB-INF, não podemos acessá-lo diretamente, tornando nossa aplicação mais bem feita em termos de segurança.

 

Também definimos uma action e um form. A action será chamada pelo botão submit de nosso formulário JSP e o form é um bean que conterá o arquivo transmitido.  O forward “sucesso” é opcional, não vamos usá-lo em nosso exemplo, está aí somente para ficar claro que não vamos sair nunca dessa página.

 

Vejamos como ficou o bean Form que usamos na listagem 5.

 

import org.apache.struts.action.ActionForm;

import org.apache.struts.upload.FormFile;

 

public class FormatExcelForm extends ActionForm {

      private static final long serialVersionUID = 4294242013432948993L;

      private FormFile arquivo;

 

      public FormFile getArquivo() {

            return arquivo;

      }

 

      public void setArquivo(FormFile arquivo) {

            this.arquivo = arquivo;

      }

     

}

Listagem 05. Arquivo src/FormatExcelForm.java

 

O serial version UID existe somente pelo fato do arquivo implementar a interface Serializable. Perceba que usamos a classe FormFile do Struts. Essa classe faz parte do suporte que o Struts tem para upload de arquivos. Como temos uma propriedade desse tipo em nosso form com o mesmo nome do campo tipo “file” do formulário html (o nome da propriedade é “arquivo”), o Struts automaticamente irá decodificar o arquivo de volta para binário, colocar nessa propriedade através do método setter e fornecer o form como parâmetro para nossa action.

 

Vejamos como ficou a action na listagem 6:

 

import java.io.InputStream;

 

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

import jxl.Cell;

import jxl.CellType;

import jxl.Workbook;

import jxl.format.Colour;

import jxl.write.WritableCell;

import jxl.write.WritableCellFormat;

import jxl.write.WritableWorkbook;

 

import org.apache.struts.action.Action;

import org.apache.struts.action.ActionForm;

import org.apache.struts.action.ActionForward;

import org.apache.struts.action.ActionMapping;

import org.apache.struts.upload.FormFile;

 

public class FormatExcelAction extends Action {

      public ActionForward execute(ActionMapping mapping, ActionForm form,

                  HttpServletRequest request, HttpServletResponse response)

                  throws Exception {

            FormatExcelForm uploadForm = (FormatExcelForm) form;

            FormFile arquivo = uploadForm.getArquivo();

 

            try {

                  InputStream in = arquivo.getInputStream();

 

                  response.setContentType("application/vnd.ms-excel");

                  response.setHeader("Content-Disposition",

                             "attachment; filename=sampleName.xls");

 

                  // Cria workbook novo a partir do recebido

                  WritableWorkbook w = Workbook.createWorkbook(response

                             .getOutputStream(), Workbook.getWorkbook(in));

                  for (int i = 0; i < w.getNumberOfSheets(); i++) {

                        for (int j = 0; j < w.getSheet(i).getColumns(); j++)

                             for (int k = 0; k < w.getSheet(i).getRows(); k++) {

                                   Cell celula = w.getSheet(i).getCell(j, k);

                                   System.out.println("celula sheet(" + i + ") [" + j

                                               + ", " + k + "] = " + celula);

                                   if (celula.getType() == CellType.NUMBER) {

                                         WritableCellFormat novoFormato = new WritableCellFormat(

                                                     celula.getCellFormat());

                                         novoFormato.setBackground(Colour.RED);

                                         WritableCell celulaModificavel = w.getSheet(i)

                                                     .getWritableCell(j, k);

                                         celulaModificavel.setCellFormat(novoFormato);

                                   } else if (celula.getType() == CellType.LABEL) {

                                         WritableCellFormat novoFormato = new WritableCellFormat(

                                                     celula.getCellFormat());

                                         novoFormato.setBackground(Colour.AQUA);

                                          WritableCell celulaModificavel = w.getSheet(i)

                                                     .getWritableCell(j, k);

                                         celulaModificavel.setCellFormat(novoFormato);

                                   }

 

                             }

                  }

                  w.write();

                  w.close();

            } catch (Exception e) {

                  throw new Exception("Erro ao transformar o arquivo: ", e);

            } finally {

                 

            }

            return null;

      }

}

Listagem 06. Arquivo src/FormatExcelAction.java

 

Aqui é que começa a entrar a JExcel API. Através do FormFile que o Struts já nos forneceu prontinho, só precisamos conseguir um InputStream para ler os dados transmitidos. O que queremos fazer é: criar um novo arquivo Excel a partir desse, para que seja modificado e enviado como resposta.

 

Para tanto, obtemos também um OutputStream a partir do objeto response, usando o método Response.getOutputStream(). Dessa forma, usamos o InputStream para recuperar os dados transmitidos pela página e o OutputStream para transmitir os dados de volta.

 

Com a JExcel API, esse trabalho é facilitado. O comando

WritableWorkbook w = Workbook.createWorkbook(

response.getOutputStream(), Workbook.getWorkbook(in));

 

cria um novo objeto WorkBook modificável, que corresponde a uma planilha, no OutputStream, usando o arquivo transmitido como base. Então nós modificamos o Workbook e executamos os comandos

w.write();

w.close();

para escrever de fato o arquivo para a resposta http.

 

Perceba que setamos alguma propriedade importantes no objeto response:

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-Disposition",

                        "attachment; filename=sampleName.xls");

 

O primeiro comando dirá ao browser que um arquivo Excel está sendo transmitido. A segunda linha indica que não estamos redirecionando para uma outra página, mas sim para um download, de forma que o browser mostrará uma tela ao usuário perguntando se ele deseja abrir ou salvar o arquivo sendo transmitido. Se escolher salvar, o arquivo terá o nome default de sampleName.xls.

 

Mas afinal, o que fizemos no arquivo Excel? Fizemos um loop (aliás, três loops) pelo arquivo, percorrendo cada uma de suas worksheets. Para cada worksheet, usamos os comandos w.getSheet(i).getColumns() e w.getSheet(i).getRows() para percorrer por todas as células de cada sheet. O tamanho de cada sheet é retornado dependendo do conteúdo existente em suas células.

 

Para cada célula, verificamos o tipo do conteúdo. Se for um número, pintamos a cor de vermelho e, se for texto estático (LABEL), pintamos de azul.

 

Foi muito importante criarmos um objeto do tipo WriteableCell para modificarmos o formato. A JExcel API faz distinção entre Cell e WriteableCell, de forma que o último herda do primeiro. Só conseguimos modificar o arquivo porque criamos um objeto WritableWorkbook a partir do arquivo de entrada no OutputStream do response. Não é possível criar um objeto desses apenas a partir de um InputStream, somente um Workbook simples, não modificável.

 

Por último, mas não menos importante, falta o arquivo de build, para construirmos nossa aplicação. Vejamos como ficou o nosso nas listagens 7 e 8:

 

tomcat_home=G:/dev_env/Tomcat5/

tomcatlib=${tomcat_home}/common/lib

tomcatapps=${tomcat_home}/webapps

 

lib=lib

debug_on=true

Listagem 07. Arquivo build/build.properties

 

<?xml version='1.0'?>

<project name="JExcelApiStrutsExample" default="package" basedir="..">

 

      <property file="build/build.properties" />

 

      <!-- set global properties for this build -->

      <property name="webinf" value="web/WEB-INF" />

      <property name="src" value="src" />

      <property name="etc" value="etc" />

      <property name="jsp" value="web/jsp" />

      <property name="index.jsp" value="web/index.jsp" />

      <property name="web.xml" value="${webinf}/web.xml" />

      <property name="tld" value="web/tld" />

      <property name="build" value="build/" />

      <property name="appname" value="JExcelApiStrutsExample" />

      <!-- generated folders -->

      <property name="dist" value="dist" />

      <property name="distbuild" value="${dist}/build" />

      <property name="distwebinf" value="${distbuild}/WEB-INF" />

      <property name="classes" value="${distwebinf}/classes" />

      <property name="distjsp" value="${distwebinf}/jsp" />

      <property name="disttld" value="${distwebinf}/tld" />

      <property name="distlibs" value="${dist}/lib" />

      <property name="warname" value="${dist}/${appname}.war" />

 

      <target name="init">

            <!-- Create the build directory structure used by compile -->

            <mkdir dir="${dist}" />

            <mkdir dir="${distbuild}" />

            <mkdir dir="${distwebinf}" />

            <mkdir dir="${classes}" />

            <mkdir dir="${disttld}" />

            <mkdir dir="${distjsp}" />

            <!-- Create the directory for the jar files -->

            <mkdir dir="${distlibs}" />

      </target>

 

      <target name="compile" depends="init">

            <!-- run javac to compile the source files -->

            <javac srcdir="${src}" destdir="${classes}" debug="${debug_on}">

                  <classpath>

                        <!-- include all jar files  -->

                        <fileset dir="${tomcatlib}">

                             <include name="**/*.jar" />

                        </fileset>

                        <fileset dir="${lib}">

                             <include name="**/*.jar" />

                        </fileset>

                  </classpath>

            </javac>

 

      </target>

 

      <target name="package" depends="compile">

            <!-- Copy files to dist dir -->

            <copy todir="${distlibs}/" flatten="true">

                  <fileset dir="${lib}">

                        <include name="**/*.jar" />

                  </fileset>

            </copy>

            <copy todir="${classes}/">

                  <fileset dir="${etc}">

                        <include name="**" />

                  </fileset>

            </copy>

            <copy todir="${disttld}/">

                  <fileset dir="${tld}">

                        <include name="**" />

                  </fileset>

            </copy>

            <copy todir="${distjsp}/">

                  <fileset dir="${jsp}">

                        <include name="**" />

                  </fileset>

            </copy>

            <copy tofile="${distbuild}/index.jsp" file="${index.jsp}" />

            <copy todir="${distwebinf}/">

                  <fileset dir="${webinf}">

                        <include name="**.xml" />

                  </fileset>

            </copy>

 

            <!-- make a war file -->

            <war destfile="${warname}" webxml="${web.xml}">

                  <fileset dir="${distbuild}">

                        <include name="**" />

                  </fileset>

                  <lib dir="${distlibs}">

                        <include name="**" />

                  </lib>

            </war>

      </target>

 

      <target name="clean">

            <!-- Delete the ${dist} directory tree -->

            <delete dir="${dist}" />

      </target>

 

      <target name="deploy" depends="package">

            <!-- Delete the ${dist} directory tree -->

            <delete dir="${tomcatapps}/${appname}" />

            <delete file="${tomcatapps}/${appname}.war" />

            <copy todir="${tomcatapps}" file="${warname}" />

      </target>

 

</project>

Listagem 08. Arquivo build/build.xml

 

O build.properties simplesmente configura algumas propriedades a serem usadas no build.xml, como a pasta onde se encontra instalado o tomcat, pastas onde se encontram as libs e opções de debug. O segundo tem os targets para construção. O target init simplesmente cria uma pasta dist dentro do projeto, o target compile compila os arquivos .Java e os joga para dentro de dist, o target package copie os arquivos do projeto para uma estrutura de pastas de um arquivo war dentro de DIST e gera o WAR a partir da mesma.  Finalmente, existe um target deploy, que copia o WAR gerado para dentro da pasta webapps do Tomcat, apagando primeiro a versão anterior.

 

Vejamos como funciona a aplicação rodando:

 

mejeawsfig01.jpg 

Figura 01. Entrando na aplicação

 

mejeawsfig02.jpg 

Figura 02. Escolhendo um arquivo exemplo

 

mejeawsfig03.jpg 

Figura 03. Arquivo exemplo original

 

mejeawsfig04.jpg 

Figura 04. Clicando no botão submit

 

mejeawsfig05.jpg 

Figura 05. Arquivo exemplo depois do processamento

 

A última linha não foi modificada, pois era uma fórmula. Poderíamos tê-la modificado também, bastante verificar seu tipo.

 

Agora é sua vez! Baixe a JExcel API e manipule arquivos Excel a vontade em suas aplicações web! Até o próximo artigo.

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?