Java tutorial
/* * 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; } }