com.automaster.autoview.server.servlet.ExcelServlet.java Source code

Java tutorial

Introduction

Here is the source code for com.automaster.autoview.server.servlet.ExcelServlet.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.automaster.autoview.server.servlet;

import com.automaster.autoview.server.DAO.ZzzPosPlacaVeiculoDAO;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.TimeZone;
import java.util.TreeMap;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author Adriano
 */
public class ExcelServlet extends HttpServlet {

    public ExcelServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        /*response.setContentType("application/vnd.ms-excel");
         response.setHeader("Content-Disposition", "attachment; filename=filename.xls");
         HSSFWorkbook workbook = new HSSFWorkbook();
         // ...
         // Now populate workbook the usual way.
         // ...
         OutputStream arqSaida = response.getOutputStream();
         workbook.write(arqSaida); // Write workbook to response.
         arqSaida.flush();
         arqSaida.close();*/
        //getServletContext().getRealPath("/")
        String tempoDecorrido = " 0";
        String url = getServletContext().getRealPath("/");
        String placa = request.getParameter("placa");
        //TimeZone timeZoneMundial = TimeZone.getTimeZone(ZoneId.ofOffset("UTC", ZoneOffset.UTC));
        Timestamp dataInicio = new Timestamp(Long.parseLong(request.getParameter("dataInicio")));
        Timestamp dataFim = new Timestamp(Long.parseLong(request.getParameter("dataFim")));
        //String timeZoneInterface = request.getParameter("timeZone");
        /*String timeZone = "Z";
        if(timeZoneInterface.equalsIgnoreCase("0")){
        timeZone = "Z";
        } else {
        timeZone = String.valueOf((-1) * (Integer.parseInt(timeZoneInterface) / 60));
        }  */
        TimeZone timeZonePadrao = TimeZone.getTimeZone(ZoneId.of("-3"));
        System.out.println("Time zone Cliente : " + timeZonePadrao);
        //System.out.println("timeZoneInterface: "+timeZoneInterface);
        //System.out.println("timeZone: "+timeZone);
        //String ign = request.getParameter("ign");
        ZzzPosPlacaVeiculoDAO zzzPosPlacaVeiculoDAO = new ZzzPosPlacaVeiculoDAO();
        ArrayList<TreeMap<String, String>> posicoes = zzzPosPlacaVeiculoDAO.buscarPosicoesPorIntervaloData(placa,
                dataInicio, dataFim);

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=Historico-" + placa + ".xlsx");

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Histrico - " + placa);
        int pictureIdx;
        try ( //add picture data to this workbook.
                InputStream is = new FileInputStream(url + "/imagens/logo.jpg")) {
            byte[] bytes = IOUtils.toByteArray(is);
            pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
        }

        CreationHelper helper = wb.getCreationHelper();

        // Create the drawing patriarch.  This is the top level container for all shapes. 
        Drawing drawing = sheet.createDrawingPatriarch();

        //add a picture shape
        ClientAnchor anchor = helper.createClientAnchor();
        //set top-left corner of the picture,
        //subsequent call of Picture#resize() will operate relative to it
        anchor.setCol1(1);
        anchor.setRow1(0);
        Picture pict = drawing.createPicture(anchor, pictureIdx);
        //auto-size picture relative to its top-left corner
        pict.resize(3, 3);
        //pict.resize();
        //sheet.setColumnWidth(0, 200);

        Font fonte = wb.createFont();
        fonte.setFontHeightInPoints((short) 24);
        fonte.setFontName("Arial");
        fonte.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fonte.setItalic(true);
        CellStyle estiloTitulo = wb.createCellStyle();
        estiloTitulo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        estiloTitulo.setFont(fonte);

        Font fonteCabecalho = wb.createFont();
        fonteCabecalho.setFontHeightInPoints((short) 14);
        fonteCabecalho.setFontName("Arial");
        fonteCabecalho.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //fonteCabecalho.setItalic(true);
        CellStyle estiloCabecalho = wb.createCellStyle();
        estiloCabecalho.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        estiloCabecalho.setFont(fonteCabecalho);

        Font fonteTituloTabela = wb.createFont();
        //fonteTituloTabela.setFontHeightInPoints((short) 14);
        fonteTituloTabela.setFontName("Arial");
        fonteTituloTabela.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        CellStyle estilo = wb.createCellStyle();
        estilo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        estilo.setFont(fonteTituloTabela);

        CellStyle estiloCorpo = wb.createCellStyle();
        estiloCorpo.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //estiloCorpo.setFillBackgroundColor(HSSFColor.WHITE.index);
        //estiloCorpo.setFont(fonteTituloTabela);

        XSSFRow linha6 = (XSSFRow) sheet.createRow(6);
        XSSFCell cell046 = linha6.createCell(3);
        cell046.setCellValue("Relatrio de Posies");
        cell046.setCellStyle(estiloTitulo);
        //sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 3));

        XSSFRow linha7 = (XSSFRow) sheet.createRow(7);
        XSSFCell cell047 = linha7.createCell(3);
        cell047.setCellValue("Veculo : " + placa);
        cell047.setCellStyle(estiloCabecalho);
        //sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 3));

        XSSFRow linha8 = (XSSFRow) sheet.createRow(8);
        XSSFCell cell038 = linha8.createCell(3);
        //TimeZone.setDefault(timeZoneMundial);
        //Date dataHoraInicio0 = new Date(Long.parseLong(request.getParameter("dataInicio")));
        //TimeZone.setDefault(timeZoneCliente);
        //Date dataHoraInicio = new Date(dataHoraInicio0.getTime());
        SimpleDateFormat dataFormatadaCabecalho = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
        dataFormatadaCabecalho.setTimeZone(timeZonePadrao);
        cell038.setCellValue("Perodo: De: " + dataFormatadaCabecalho.format(dataInicio) + " at: "
                + dataFormatadaCabecalho.format(dataFim));
        cell038.setCellStyle(estiloCabecalho);
        //sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 3));

        sheet.setColumnWidth(0, 5000);
        sheet.setColumnWidth(1, 3000);
        sheet.setColumnWidth(2, 3500);
        //sheet.setColumnWidth(3, 4000);
        sheet.setColumnWidth(3, 30000);
        //sheet.setColumnWidth(4, 4000);
        //sheet.setColumnWidth(4, 30000);
        sheet.setColumnWidth(5, 3000);
        sheet.setColumnWidth(6, 3000);
        sheet.setColumnWidth(7, 3000);
        sheet.setColumnWidth(8, 3000);
        sheet.setColumnWidth(9, 3000);
        //sheet.setColumnWidth(10, 20000);        
        sheet.setColumnWidth(11, 3000);
        XSSFRow linha9 = (XSSFRow) sheet.createRow(10);
        XSSFCell cell0 = linha9.createCell(0);
        cell0.setCellValue("Data e hora");
        cell0.setCellStyle(estilo);
        XSSFCell cell1 = linha9.createCell(1);
        cell1.setCellValue("Velocidade");
        cell1.setCellStyle(estilo);
        XSSFCell cell2 = linha9.createCell(2);
        cell2.setCellValue("Ignio");
        cell2.setCellStyle(estilo);
        //XSSFCell cell3 = linha9.createCell(3);
        //cell3.setCellValue("Latitude");
        //cell3.setCellStyle(estilo);
        //XSSFCell cell4 = linha9.createCell(4);
        //cell4.setCellValue("Longitude");
        //cell4.setCellStyle(estilo);
        //XSSFCell cell5 = linha9.createCell(5);
        //cell5.setCellValue("Satlite");
        //cell5.setCellStyle(estilo);
        //XSSFCell cell6 = linha9.createCell(6);
        //cell6.setCellValue("GPS");
        //cell6.setCellStyle(estilo);
        //XSSFCell cell7 = linha9.createCell(7);
        //cell7.setCellValue("Entrada");
        //cell7.setCellStyle(estilo);
        //XSSFCell cell8 = linha9.createCell(8);
        //cell8.setCellValue("Sada");
        //cell8.setCellStyle(estilo);
        //XSSFCell cell9 = linha9.createCell(9);
        //cell9.setCellValue("Evento");
        //cell9.setCellStyle(estilo);
        XSSFCell cell10 = linha9.createCell(3);
        cell10.setCellValue("Endereo");
        cell10.setCellStyle(estilo);
        //sheet.addMergedRegion(new CellRangeAddress(11, 11, 4, 8));
        //XSSFCell cell11 = linha9.createCell(11);
        //cell11.setCellValue("Direo");
        //cell11.setCellStyle(estilo);
        int linha = 0;
        int j = 11;
        double latAnt = 0;
        double lonAnt = 0;
        double latAtual = 0;
        double lonAtual = 0;
        double distancia = 0;
        double distanciaTotal = 0;
        for (int i = 0; i < posicoes.size(); i++) {
            XSSFRow row = (XSSFRow) sheet.createRow(j);
            if (i == 0) {
                distancia = 0;
                //System.out.println("linha 00 - EXCEL");
            } else {
                //System.out.println("linha 01 - EXCEL");
                latAnt = Double.parseDouble(posicoes.get(i - 1).get("lat"));
                lonAnt = Double.parseDouble(posicoes.get(i - 1).get("lon"));
                latAtual = Double.parseDouble(posicoes.get(i).get("lat"));
                lonAtual = Double.parseDouble(posicoes.get(i).get("lon"));
                //System.out.println("linha 02 - PDF");
                if (latAnt == latAtual && lonAnt == lonAtual) {
                    distancia = 0;
                } else {
                    distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual);
                    //System.out.println("linha 03 - PDF");
                }

            }
            distanciaTotal = distanciaTotal + distancia;
            /*if(i==0) {
             latAnt = Double.parseDouble(posicoes.get(i).get("lat"));
             lonAnt = Double.parseDouble(posicoes.get(i).get("lon"));
            } else{
                latAnt = Double.parseDouble(posicoes.get(i-1).get("lat"));
                lonAnt = Double.parseDouble(posicoes.get(i-1).get("lon"));
            }
            double latAtual = Double.parseDouble(posicoes.get(i).get("lat"));
            double lonAtual = Double.parseDouble(posicoes.get(i).get("lon"));
            double distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual);
            distanciaTotal = distanciaTotal + distancia;*/
            for (int col = 0; col < posicoes.get(linha).size(); col++) {
                XSSFCell cell = row.createCell(col);
                cell.setCellStyle(estiloCorpo);
                switch (col) {
                case 0:
                    Date dataHora0 = new Date(Long.parseLong(posicoes.get(i).get("dataHora")));
                    Date dataHora = new Date(dataHora0.getTime());
                    SimpleDateFormat dataFormatada = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
                    dataFormatada.setTimeZone(timeZonePadrao);
                    cell.setCellValue(dataFormatada.format(dataHora));
                    break;
                case 1:
                    cell.setCellValue(posicoes.get(linha).get("vel"));
                    break;
                case 2:
                    cell.setCellValue(
                            posicoes.get(linha).get("ign").equalsIgnoreCase("True") ? "Ligada" : "Desligada");
                    break;
                /*case 3:
                    cell.setCellValue(posicoes.get(linha).get("lat"));
                    break;
                case 4:
                    cell.setCellValue(posicoes.get(linha).get("lon"));
                    break;
                case 5:
                    cell.setCellValue(posicoes.get(linha).get("sat"));
                    break;
                case 6:
                    cell.setCellValue(posicoes.get(linha).get("gps"));
                    break;
                case 7:
                    cell.setCellValue(posicoes.get(linha).get("entrada"));
                    break;
                case 8:
                    cell.setCellValue(posicoes.get(linha).get("saida"));
                    break;
                case 9:
                    cell.setCellValue(posicoes.get(linha).get("evento"));
                    break;*/
                case 3:
                    cell.setCellValue(posicoes.get(linha).get("endereco") == null ? "Sem endereo"
                            : posicoes.get(linha).get("endereco"));
                    break;
                /*case 11:
                    cell.setCellValue(posicoes.get(linha).get("direcao"));
                    break;*/
                }

            }
            j = j + 1;
            linha = linha + 1;

        }
        tempoDecorrido = calculaDatas(Long.parseLong(posicoes.get(0).get("dataHora")),
                Long.parseLong(posicoes.get(posicoes.size() - 1).get("dataHora")));
        int index = 0;
        String kms = "0";
        String m = "";
        double metros = 0;
        if (distanciaTotal > 0) {
            BigDecimal decimalFormatado = new BigDecimal(distanciaTotal).setScale(2, RoundingMode.HALF_EVEN);
            index = String.valueOf(decimalFormatado).indexOf(".");
            kms = String.valueOf(decimalFormatado).substring(0, index);
            m = "0" + (String.valueOf(decimalFormatado).substring(index));
            metros = Double.parseDouble(m) * 1000;
        }
        //String formatted = NumberFormat.getFormat("000.00").format(metros);
        //System.out.println("Percorridos aproximadamente : "+kms+" KM e "+metros+" metros");
        XSSFRow linhaX = (XSSFRow) sheet.createRow(linha + 12);
        XSSFCell cellX = linhaX.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(linha + 12, linha + 12, 0, 3));
        cellX.setCellStyle(estilo);
        cellX.setCellValue(
                "Percorridos: " + kms + " KM e " + String.valueOf(metros) + " metros. Tempo: " + tempoDecorrido);
        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        wb.write(outByteStream);

        byte[] outArray = outByteStream.toByteArray();
        OutputStream outStream = response.getOutputStream();
        outStream.write(outArray);
        outStream.flush();

    }

    private double caculaDistanciaEntreDoisPontos(double lat1, double lon1, double lat2, double lon2) {

        //Transforma cordenadas em radianos
        /*String lat1Reduzida = String.valueOf(lat1);
        int index = lat1Reduzida.indexOf(".");
        String latFinal1 = lat1Reduzida.substring(0, index+5);
            
        String lon1Reduzida = String.valueOf(lon1);
        String lonFinal1 = lon1Reduzida.substring(0, index+5);
            
        String lat2Reduzida = String.valueOf(lat2);
        String latFinal2 = lat2Reduzida.substring(0, index+5);
            
        String lon2Reduzida = String.valueOf(lon2);
        String lonFinal2 = lon2Reduzida.substring(0, index+5);*/

        double lat01 = Math.toRadians(lat1);
        double lon01 = Math.toRadians(lon1);
        double lat02 = Math.toRadians(lat2);
        double lon02 = Math.toRadians(lon2);
        //calcula a distncia em KM atravs da frmula
        double dist = (6371 * Math.acos(
                Math.cos(lat01) * Math.cos(lat02) * Math.cos(lon02 - lon01) + Math.sin(lat01) * Math.sin(lat02)));
        //formata o resultado
        if (dist > 0) {
            BigDecimal decimalFormatado = new BigDecimal(dist).setScale(2, RoundingMode.HALF_EVEN);
            return decimalFormatado.doubleValue();
        }
        return 0;
        //return dist;

    }

    private String calculaDatas(long dataFinal, long dataInicial) {

        String tempoDecorrido = "";
        try {
            //GWT.log("datas : "+ dataInicio+ " "+dataFim);
            long diferenca = dataFinal - dataInicial;
            //int dia = 24*60*60*1000;
            long dia = 0;
            long hora = 0;
            long minuto = 0;
            long segundo = 0;
            //GWT.log("diferenca : "+ diferenca);
            // um dia equivale a 86400000 ms
            // uma hora equivale a 3600000 ms;
            // um minuto equivale a 60000 ms;
            // um minuto equivale a 1000 ms;
            if (diferenca > 0) {
                while (diferenca > 0) {
                    if (diferenca >= 86400000) {
                        //System.out.println("LINHA 01 calculaDatas");
                        dia = diferenca / 86400000;
                        tempoDecorrido = tempoDecorrido + " " + dia + " dia(s), ";
                        diferenca = diferenca % 86400000;
                    } else if (diferenca < 86400000 && diferenca >= 3600000) {
                        //System.out.println("LINHA 02 calculaDatas");
                        hora = diferenca / 3600000;
                        tempoDecorrido = tempoDecorrido + "" + hora + " hora(s), ";
                        diferenca = diferenca % 3600000;

                    } else if (diferenca < 3600000 && diferenca >= 60000) {
                        //System.out.println("LINHA 03 calculaDatas");
                        minuto = diferenca / 60000;
                        tempoDecorrido = tempoDecorrido + "" + minuto + " minuto(s), ";
                        diferenca = diferenca % 60000;

                    } else if (diferenca < 60000) {
                        //System.out.println("LINHA 04 calculaDatas");
                        segundo = diferenca / 1000;
                        tempoDecorrido = tempoDecorrido + "" + segundo + " segundo(s).";
                        diferenca = diferenca % 1000;

                    }
                }
            } else {
                tempoDecorrido = " 0.";
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return tempoDecorrido;
    }

}