Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook write

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. write

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook write.

Prototype

private void write(POIFSFileSystem fs) throws IOException

Source Link

Document

Writes the workbook out to a brand new, empty POIFS

Usage

From source file:controladores4.controladorReportes.java

public void GenerarLibroRemuneracionesAtrasado(final String year, final String month) {
    DateFormat date2 = new SimpleDateFormat("dd-MMMM-yyyy");
    NumberFormat FORMAT = NumberFormat.getCurrencyInstance();
    DecimalFormatSymbols dfs = new DecimalFormatSymbols();

    Thread runnable;/*  www  .j ava2s.  c  o  m*/
    runnable = new Thread() {
        public void run() {
            try {
                DateFormat yearDate = new SimpleDateFormat("yyyy");
                DateFormat monthDate = new SimpleDateFormat("M");
                //                    String month2 = monthDate.format(new Date());
                //                    String year2 = yearDate.format(new Date());
                String per = perDate.format(new Date());
                String fecha = formatDate.format(new Date());
                controladores.controladorPrincipal miControlador = new controladorPrincipal();
                modelos3.modeloRemuneraciones remu = new modeloRemuneraciones();
                double uf = remu.obtenerUF();
                double utm = remu.obtenerUTM();
                String[][] imp2cat = remu.obtenerTablaImpuesto();

                //Listas para detalle afp y salud
                HashMap<String, ArrayList<AfpInfo>> mapAfp = new HashMap<>();
                HashMap<String, ArrayList<SaludInfo>> mapSalud = new HashMap<>();
                List<AfpInfo> listaAfp = new ArrayList<>();
                List<SaludInfo> listaSalud = new ArrayList<>();

                modelos.modeloEmpleados emp = new modeloEmpleados();
                String[][] data = emp.obtenerRemuneraciones2(Integer.parseInt(month), Integer.parseInt(year));

                String path = "Libro de remuneraciones - " + month + "-" + year + ".xls";
                File file = new File(path);
                HSSFWorkbook workbook = new HSSFWorkbook();
                HSSFCellStyle style = workbook.createCellStyle();
                HSSFFont font = workbook.createFont();
                font.setBold(true);
                font.setFontName("Calibri");
                font.setFontHeightInPoints((short) 11);
                style.setFont(font);
                HSSFSheet sheet_rem = workbook.createSheet("Libro_Remuneraciones");
                HSSFSheet sheet_afp = workbook.createSheet("Detalle_AFP");
                HSSFSheet sheet_salud = workbook.createSheet("Detalle_Salud");
                HSSFRow rowhead;
                //J para recorrer los datos de los trabajadores
                int j = 0;
                int numTrab = data.length;
                for (int i = 0; i < 2; i++) {
                    //Primera linea
                    rowhead = sheet_rem.createRow((short) 0 + i * 34);
                    rowhead.createCell(0).setCellValue("parte:1");
                    rowhead.createCell(3)
                            .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada");
                    rowhead.getCell(3).setCellStyle(style);
                    rowhead.createCell(11).setCellValue("pag:" + (i + 1));

                    rowhead.createCell(12).setCellValue("parte:2");
                    rowhead.createCell(15)
                            .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada");
                    rowhead.getCell(15).setCellStyle(style);
                    rowhead.createCell(23).setCellValue("pag:" + (i + 1));

                    rowhead.createCell(24).setCellValue("parte:3");
                    rowhead.createCell(27)
                            .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada");
                    rowhead.getCell(27).setCellStyle(style);
                    rowhead.createCell(35).setCellValue("pag:" + (i + 1));

                    //Segunda linea
                    rowhead = sheet_rem.createRow((short) 1 + (i * 34));
                    rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1");
                    rowhead.createCell(2).setCellValue("Mes: " + month);
                    rowhead.createCell(4).setCellValue("Ao: " + year);
                    rowhead.createCell(6).setCellValue("UF: $" + uf);
                    rowhead.createCell(9).setCellValue("UTM: $" + utm);

                    rowhead.createCell(12).setCellValue("Rut_empresa:77.037.960-1");
                    rowhead.createCell(14).setCellValue("Mes: " + month);
                    rowhead.createCell(16).setCellValue("Ao: " + year);
                    rowhead.createCell(18).setCellValue("UF: $" + uf);
                    rowhead.createCell(21).setCellValue("UTM: $" + utm);

                    rowhead.createCell(24).setCellValue("Rut_empresa:77.037.960-1");
                    rowhead.createCell(26).setCellValue("Mes: " + month);
                    rowhead.createCell(28).setCellValue("Ao: " + year);
                    rowhead.createCell(30).setCellValue("UF: $" + uf);
                    rowhead.createCell(33).setCellValue("UTM: $" + utm);

                    //Tercera linea
                    rowhead = sheet_rem.createRow((short) 4 + (i * 34));
                    rowhead.createCell(0).setCellValue("rut_trabajador");
                    rowhead.createCell(1).setCellValue("nombre_trabajador");
                    rowhead.createCell(2).setCellValue("dias_trab");
                    rowhead.createCell(3).setCellValue("sueldo_base_p");
                    rowhead.createCell(4).setCellValue("gratificacion");
                    rowhead.createCell(5).setCellValue("bono_aos");
                    rowhead.createCell(6).setCellValue("bono_horas");
                    rowhead.createCell(7).setCellValue("bono_asig_vol");
                    rowhead.createCell(8).setCellValue("bono_ad");
                    rowhead.createCell(9).setCellValue("otros_bonos");
                    rowhead.createCell(10).setCellValue("horas_extra");
                    rowhead.createCell(11).setCellValue("tot_h_imp");

                    rowhead.createCell(12).setCellValue("rut_trabajador");
                    rowhead.createCell(13).setCellValue("nombre_trabajador");
                    rowhead.createCell(14).setCellValue("colacion");
                    rowhead.createCell(15).setCellValue("movilizacion");
                    rowhead.createCell(16).setCellValue("asig_familiar");
                    rowhead.createCell(17).setCellValue("tot_h_no_imp");
                    rowhead.createCell(18).setCellValue("tot_haberes");
                    rowhead.createCell(19).setCellValue("desc_afp");
                    rowhead.createCell(20).setCellValue("desc_salud");
                    rowhead.createCell(21).setCellValue("afc_trab");
                    rowhead.createCell(22).setCellValue("afc_empl");
                    rowhead.createCell(23).setCellValue("tot_desc_leg");

                    rowhead.createCell(24).setCellValue("rut_trabajador");
                    rowhead.createCell(25).setCellValue("nombre_trabajador");
                    rowhead.createCell(26).setCellValue("imp_renta");
                    rowhead.createCell(27).setCellValue("caja_comp");
                    rowhead.createCell(28).setCellValue("anticipo");
                    rowhead.createCell(29).setCellValue("adelanto");
                    rowhead.createCell(30).setCellValue("prestamo");
                    rowhead.createCell(31).setCellValue("tot_desc_men");
                    rowhead.createCell(32).setCellValue("tot_desc");
                    rowhead.createCell(33).setCellValue("total_a_pago");
                    rowhead.createCell(34).setCellValue("sis");

                    //Cuarta linea                        
                    for (int k = 0; k < 28; k++, j++) {
                        if (j == numTrab) {
                            break;
                        }
                        int base = Integer.parseInt(data[j][2]) * Integer.parseInt(data[j][28]) / 30;
                        //GRATIFICACION
                        int grat = (int) (base * 0.25);
                        //BONO ANTIGUEDAD
                        int bonoAnt = miControlador.obtenerBonoAnt(data[j][5]);
                        //BONO 300
                        int totalBon300 = Integer.parseInt(data[j][27]);
                        //BONO ADICIONAL
                        int bonoAd = Integer.parseInt(data[j][11]);
                        //BONO RESPONSABILIDAD
                        int bonoResp = 0;
                        //BONO ADICIONAL
                        double bonoCol1 = Double.parseDouble(data[j][8]);
                        double bonoCol30 = Double.parseDouble(data[j][9]);
                        double bonoCol = bonoCol1 + bonoCol30 / 2;
                        int totalBonCol = (int) Math.round(((double) base * 0.0077777) * bonoCol);
                        //HORAS EXTRA
                        double horasExNor = Double.parseDouble(data[j][12]);
                        double horasExFes = Double.parseDouble(data[j][13]);
                        double cantHorEx = 0;
                        //total de horas extras normales = 1; festivas = 2
                        double totalHorex = 0;
                        double resHorEx = 0;
                        if (horasExNor > 45) {
                            cantHorEx = 45;
                            totalHorex = 45;
                            resHorEx = horasExNor - 45;
                        } else {
                            cantHorEx = horasExNor;
                            totalHorex = cantHorEx;
                        }
                        if (cantHorEx + horasExFes > 45) {
                            resHorEx = resHorEx + (horasExFes - 45 + cantHorEx) * 2;
                            totalHorex = 45 - cantHorEx;
                            cantHorEx = 45;
                        } else {
                            cantHorEx += horasExFes;
                            totalHorex += horasExFes * 2;
                        }

                        //BONO ASIGNACION VOLUNTARIA
                        double totalBonoAV = base * 0.0077777 * resHorEx;
                        double valorHorEx = (int) ((double) base * 0.0077777 * totalHorex);
                        //TOTAL IMPONIBLE
                        double totImp = base + grat + bonoAnt + bonoAd + bonoResp + totalBonoAV + totalBonCol
                                + totalBon300 + valorHorEx;
                        //DESCUENTO AFP
                        int descAFP = Integer.parseInt(data[j][21]);
                        int totalAFP = (int) (totImp * ((double) descAFP / 10000));
                        int sis = (int) (totImp * 0.0141);
                        //DESCUENTO SALUD
                        double descSalud = 0, totalSalud = 0;
                        String salud;
                        if (data[j][4].toLowerCase().compareTo("fonasa") == 0) {
                            salud = "FONASA";
                            descSalud = Integer.parseInt(data[j][22]);
                            totalSalud = (int) (totImp * ((double) descSalud / 10000));
                        } else {
                            if (data[i][23].compareTo("") == 0) {
                                salud = data[j][4];
                            } else {
                                salud = data[j][23];
                            }
                            descSalud = ((double) Integer.parseInt(data[j][24]) / 1000) * uf;
                            totalSalud = descSalud;
                        }
                        //DESCUENTO CESANTIA
                        int ces = (int) (totImp * 0.006);
                        int cesEmp = (int) (totImp * 0.024);
                        //DESCUENTOS LEGALES
                        double descLegales = ces + totalSalud + totalAFP;
                        //TOTAL TRIBUTABLE
                        double totTrib = totImp - totalAFP - totalSalud - ces;
                        int descRenta = 0;
                        double totAux = 0;
                        for (String[] imp2cat1 : imp2cat) {
                            if (totTrib > Float.parseFloat(imp2cat1[0]) / 10
                                    && totTrib <= Float.parseFloat(imp2cat1[1]) / 10) {
                                descRenta = (int) (totTrib * Float.parseFloat(imp2cat1[2]) / 1000
                                        - Float.parseFloat(imp2cat1[3]) / 100);
                                totAux = totTrib - descRenta;
                                break;
                            }
                        }
                        //CAJA COMPENSACION
                        int caja = Integer.parseInt(data[j][15]);
                        //ASIGNACION FAMILIAR
                        int af = Integer.parseInt(data[j][16]);
                        //LIQ ALCANZADO
                        double liqAl = totAux - caja;
                        //COLACION 
                        int col = Integer.parseInt(data[j][6]);
                        //TRANSPORTE
                        int trans = Integer.parseInt(data[j][7]);
                        //TOTAL NO IMPONIBLE
                        int noImp = trans + col + af;
                        //ANTICIPO ADELANTO PRESTAMOS
                        int antic = Integer.parseInt(data[j][17]);
                        int adel = Integer.parseInt(data[j][18]);
                        int pres = Integer.parseInt(data[j][19]);
                        int cuo = Integer.parseInt(data[j][20]);
                        int cuoPres = 0;
                        int cuores = Math.max(0, Integer.parseInt(data[j][26]) - 1);
                        if (cuo != 0) {
                            cuoPres = pres / cuo;
                        }
                        //DESCUENTOS MENSUALES
                        int descMensuales = caja + antic + adel + cuoPres + descRenta;
                        //TOTAL HABERES
                        double totalHaberes = noImp + totImp;
                        //TOTAL DESCUENTOS
                        int totDesc = antic + adel + cuoPres + caja;
                        //LIQUIDO
                        double liq = liqAl + col + trans + af - antic - adel - cuoPres;

                        //Agrego los datos de afp a listaAfp
                        String afp = data[j][3];
                        if (mapAfp.get(afp) == null) {
                            mapAfp.put(afp, new ArrayList<AfpInfo>());
                        }
                        if (mapSalud.get(salud.toUpperCase()) == null) {
                            mapSalud.put(salud.toUpperCase(), new ArrayList<SaludInfo>());
                        }
                        AfpInfo info = new AfpInfo(data[j][0], data[j][1], totImp, descAFP, sis, totalAFP);
                        mapAfp.get(afp).add(info);
                        SaludInfo sInfo = new SaludInfo(data[j][0], data[j][1], totImp, descSalud);
                        mapSalud.get(salud.toUpperCase()).add(sInfo);
                        rowhead = sheet_rem.createRow((short) 5 + k + (i * 34));
                        rowhead.createCell(0).setCellValue(data[j][0]);
                        rowhead.createCell(1).setCellValue(data[j][1]);
                        rowhead.createCell(2).setCellValue(Integer.parseInt(data[j][28]));
                        rowhead.createCell(3).setCellValue(base);
                        rowhead.createCell(4).setCellValue(grat);
                        rowhead.createCell(5).setCellValue(bonoAnt);
                        rowhead.createCell(6).setCellValue(totalBon300);
                        rowhead.createCell(7).setCellValue(totalBonoAV);
                        rowhead.createCell(8).setCellValue(totalBonCol);
                        rowhead.createCell(9).setCellValue(bonoAd);
                        rowhead.createCell(10).setCellValue(valorHorEx);
                        rowhead.createCell(11).setCellValue(totImp);

                        rowhead.createCell(12).setCellValue(data[j][0]);
                        rowhead.createCell(13).setCellValue(data[j][1]);
                        rowhead.createCell(14).setCellValue(col);
                        rowhead.createCell(15).setCellValue(trans);
                        rowhead.createCell(16).setCellValue(af);
                        rowhead.createCell(17).setCellValue(noImp);
                        rowhead.createCell(18).setCellValue(totalHaberes);
                        rowhead.createCell(19).setCellValue(totalAFP);
                        rowhead.createCell(20).setCellValue(totalSalud);
                        rowhead.createCell(21).setCellValue(ces);
                        rowhead.createCell(22).setCellValue(cesEmp);
                        rowhead.createCell(23).setCellValue(descLegales);

                        rowhead.createCell(24).setCellValue(data[j][0]);
                        rowhead.createCell(25).setCellValue(data[j][1]);
                        rowhead.createCell(26).setCellValue(descRenta);
                        rowhead.createCell(27).setCellValue(caja);
                        rowhead.createCell(28).setCellValue(antic);
                        rowhead.createCell(29).setCellValue(adel);
                        rowhead.createCell(30).setCellValue(cuoPres);
                        rowhead.createCell(31).setCellValue(descMensuales);
                        rowhead.createCell(32).setCellValue(totDesc);
                        rowhead.createCell(33).setCellValue(liq);
                        rowhead.createCell(34).setCellValue(sis);
                    }
                }

                //Totales
                rowhead = sheet_rem.createRow(66);
                rowhead.createCell(2).setCellFormula("SUM(C6:C34,C40:C66)");
                rowhead.createCell(3).setCellFormula("SUM(D6:D34,D40:D66)");
                rowhead.createCell(4).setCellFormula("SUM(E6:E34,E40:E66)");
                rowhead.createCell(5).setCellFormula("SUM(F6:F34,F40:F66)");
                rowhead.createCell(6).setCellFormula("SUM(G6:G34,G40:G66)");
                rowhead.createCell(7).setCellFormula("SUM(H6:H34,H40:H66)");
                rowhead.createCell(8).setCellFormula("SUM(I6:I34,I40:I66)");
                rowhead.createCell(9).setCellFormula("SUM(J6:J34,J40:J66)");
                rowhead.createCell(10).setCellFormula("SUM(K6:K34,K40:K66)");
                rowhead.createCell(11).setCellFormula("SUM(L6:L34,L40:L66)");

                rowhead.createCell(14).setCellFormula("SUM(O6:O34,O40:O66)");
                rowhead.createCell(15).setCellFormula("SUM(P6:P34,P40:P66)");
                rowhead.createCell(16).setCellFormula("SUM(Q6:Q34,Q40:Q66)");
                rowhead.createCell(17).setCellFormula("SUM(R6:R34,R40:R66)");
                rowhead.createCell(18).setCellFormula("SUM(S6:S34,S40:S66)");
                rowhead.createCell(19).setCellFormula("SUM(T6:T34,T40:T66)");
                rowhead.createCell(20).setCellFormula("SUM(U6:U34,U40:U66)");
                rowhead.createCell(21).setCellFormula("SUM(V6:V34,V40:V66)");
                rowhead.createCell(22).setCellFormula("SUM(W6:W34,W40:W66)");
                rowhead.createCell(23).setCellFormula("SUM(X6:X34,X40:X66)");

                rowhead.createCell(26).setCellFormula("SUM(AA6:AA34,AA40:AA66)");
                rowhead.createCell(27).setCellFormula("SUM(AB6:AB34,AB40:AB66)");
                rowhead.createCell(28).setCellFormula("SUM(AC6:AC34,AC40:AC66)");
                rowhead.createCell(29).setCellFormula("SUM(AD6:AD34,AD40:AD66)");
                rowhead.createCell(30).setCellFormula("SUM(AE6:AE34,AE40:AE66)");
                rowhead.createCell(31).setCellFormula("SUM(AF6:AF34,AF40:AF66)");
                rowhead.createCell(32).setCellFormula("SUM(AG6:AG34,AG40:AG66)");
                rowhead.createCell(33).setCellFormula("SUM(AH6:AH34,AH40:AH66)");
                rowhead.createCell(34).setCellFormula("SUM(AI6:AI34,AI40:AI66)");

                //Hoja detalle afp
                j = 0;

                //Primera linea
                rowhead = sheet_afp.createRow((short) j++);
                rowhead.createCell(3)
                        .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada");
                rowhead.getCell(3).setCellStyle(style);
                rowhead.createCell(11).setCellValue("pag:1");

                //Segunda linea
                rowhead = sheet_afp.createRow((short) j++);
                rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1");
                rowhead.createCell(2).setCellValue("Mes: " + month);
                rowhead.createCell(4).setCellValue("Ao: " + year);
                rowhead.createCell(6).setCellValue("UF: $" + uf);
                rowhead.createCell(9).setCellValue("UTM: $" + utm);

                j += 2;
                for (String key : mapAfp.keySet()) {
                    List<AfpInfo> aux = mapAfp.get(key);
                    rowhead = sheet_afp.createRow((short) j++);
                    rowhead.createCell(5).setCellValue("AFP " + key);
                    rowhead.getCell(5).setCellStyle(style);
                    rowhead = sheet_afp.createRow((short) j++);
                    rowhead.createCell(0).setCellValue("rut");
                    rowhead.createCell(1).setCellValue("nom_trab");
                    rowhead.createCell(2).setCellValue("tot_h_imp");
                    rowhead.createCell(3).setCellValue("desc_afp");
                    rowhead.createCell(4).setCellValue("sis");
                    rowhead.createCell(5).setCellValue("tot_afp");
                    rowhead.createCell(8).setCellValue("afiliados: " + aux.size());
                    for (AfpInfo usuario : aux) {
                        rowhead = sheet_afp.createRow((short) j++);
                        rowhead.createCell(0).setCellValue(usuario.getRut());
                        rowhead.createCell(1).setCellValue(usuario.getNombre());
                        rowhead.createCell(2).setCellValue(usuario.getTotalImp());
                        rowhead.createCell(3).setCellValue(usuario.getDescAfp());
                        rowhead.createCell(4).setCellValue(usuario.getSis());
                        rowhead.createCell(5).setCellValue(usuario.getTotAfp());
                    }
                    rowhead = sheet_afp.createRow((short) j++);
                    int totalTrab = aux.size();
                    rowhead.createCell(2).setCellFormula("SUM(C" + (j - totalTrab) + ":C" + (j - 1) + ")");
                    rowhead.createCell(3).setCellFormula("SUM(D" + (j - totalTrab) + ":D" + (j - 1) + ")");
                    rowhead.createCell(4).setCellFormula("SUM(E" + (j - totalTrab) + ":E" + (j - 1) + ")");
                    rowhead.createCell(5).setCellFormula("SUM(F" + (j - totalTrab) + ":F" + (j - 1) + ")");
                    j++;
                }

                //Hoja detalle salud
                j = 0;

                //Primera linea
                rowhead = sheet_salud.createRow((short) j++);
                rowhead.createCell(3)
                        .setCellValue("Libro de Remuneraciones - Gruas Santa Teresita FM Limitada");
                rowhead.getCell(3).setCellStyle(style);
                rowhead.createCell(11).setCellValue("pag:1");

                //Segunda linea
                rowhead = sheet_salud.createRow((short) j++);
                rowhead.createCell(0).setCellValue("Rut_empresa:77.037.960-1");
                rowhead.createCell(2).setCellValue("Mes: " + month);
                rowhead.createCell(4).setCellValue("Ao: " + year);
                rowhead.createCell(6).setCellValue("UF: $" + uf);
                rowhead.createCell(9).setCellValue("UTM: $" + utm);

                j += 2;
                for (String key : mapSalud.keySet()) {
                    List<SaludInfo> aux = mapSalud.get(key);
                    rowhead = sheet_salud.createRow((short) j++);
                    rowhead.createCell(5).setCellValue("Isapre " + key);
                    rowhead.getCell(5).setCellStyle(style);
                    rowhead = sheet_salud.createRow((short) j++);
                    rowhead.createCell(0).setCellValue("rut");
                    rowhead.createCell(1).setCellValue("nom_trab");
                    rowhead.createCell(2).setCellValue("tot_h_imp");
                    rowhead.createCell(3).setCellValue("desc_salud");
                    rowhead.createCell(8).setCellValue("afiliados: " + aux.size());
                    for (SaludInfo usuario : aux) {
                        rowhead = sheet_salud.createRow((short) j++);
                        rowhead.createCell(0).setCellValue(usuario.getRut());
                        rowhead.createCell(1).setCellValue(usuario.getNombre());
                        rowhead.createCell(2).setCellValue(usuario.getTotImp());
                        rowhead.createCell(3).setCellValue(usuario.getDescSalud());
                    }
                    rowhead = sheet_salud.createRow((short) j++);
                    int totalTrab = aux.size();
                    rowhead.createCell(2).setCellFormula("SUM(C" + (j - totalTrab) + ":C" + (j - 1) + ")");
                    rowhead.createCell(3).setCellFormula("SUM(D" + (j - totalTrab) + ":D" + (j - 1) + ")");
                    j++;
                }

                FileOutputStream fileOut;
                fileOut = new FileOutputStream(file);
                workbook.write(fileOut);
                fileOut.close();
                JOptionPane.showMessageDialog(null, "Libro de remuneraciones generado con xito",
                        "Operacin exitosa", JOptionPane.INFORMATION_MESSAGE);
            } catch (IOException ie) {
                JOptionPane.showMessageDialog(null,
                        "El archivo est siendo ocupado\nCierre el archivo y vuelva a intentarlo", "Error",
                        JOptionPane.INFORMATION_MESSAGE);
                ie.printStackTrace();
            } catch (Exception e) {
                JOptionPane.showMessageDialog(null, "Error al crear libro de remuneraciones", "Error",
                        JOptionPane.INFORMATION_MESSAGE);
                e.printStackTrace();
            }
        }
    };
    runnable.start();
}

From source file:controller.ExternalIOController.java

public static void gravarResultadoTeste(QueenSolver solver) throws IOException {
    File f = new File("ResultadoExperimento.xls");
    HSSFWorkbook workbook = null;
    HSSFSheet sheet = null;/*from www  .java2 s  .  com*/
    FileOutputStream fileOut = null;
    HSSFRow row = null;

    if (f.isFile() && f.canRead()) {
        workbook = new HSSFWorkbook(new FileInputStream(f));
        sheet = workbook.getSheetAt(0);
        row = sheet.createRow(sheet.getLastRowNum() + 1);
        row.createCell(0).setCellValue(sheet.getLastRowNum());
        row.createCell(1).setCellValue(solver.getN());
        row.createCell(2).setCellValue(solver.getIteracoes());
        row.createCell(3).setCellValue(solver.getUltimoTempo());
        fileOut = new FileOutputStream(f);
    } else {
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet("TestSheet");
        HSSFRow rowhead = sheet.createRow(0);
        rowhead.createCell(0).setCellValue("Numero");
        rowhead.createCell(1).setCellValue("N");
        rowhead.createCell(2).setCellValue("Iteraes");
        rowhead.createCell(3).setCellValue("Tempo de Servio (MS)");
        row = sheet.createRow(1);
        row.createCell(0).setCellValue(sheet.getLastRowNum());
        row.createCell(1).setCellValue(solver.getN());
        row.createCell(2).setCellValue(solver.getIteracoes());
        row.createCell(3).setCellValue(solver.getUltimoTempo());
        fileOut = new FileOutputStream(new File("ResultadoExperimento.xls"));
    }
    workbook.write(fileOut);
    fileOut.close();
}

From source file:controller.ExternalIOController.java

    o() throws IOException{
    File f = new File("ResultadoExperimento.xls");
    HSSFWorkbook workbook = null;
    HSSFSheet sheet = null;  //from ww w  . j a  va 2  s . c o  m
    FileOutputStream fileOut = null;
    HSSFRow row = null;
        
    if(f.isFile() && f.canRead()){
        workbook = new HSSFWorkbook(new FileInputStream(f));
        sheet = workbook.getSheetAt(0);
        row = sheet.getRow(0);
        row.createCell(4).setCellValue("Nmeros Gerados por Distribuio");
        for (int i = 0; i < DistributionController.getNumerosGerados().size(); i++) {
            row = sheet.getRow(i + 1);
            if(row == null){
                row = sheet.createRow(sheet.getLastRowNum() + 1);
            }
            row.createCell(4).setCellValue(DistributionController.getNumerosGerados().get(i));
        }
        fileOut = new FileOutputStream(f);
    }else{
        throw new IOException();
    }
    workbook.write(fileOut);
    fileOut.close();
}

From source file:Controller.ProviderController.java

@RequestMapping(value = "/ExportPackage", method = RequestMethod.POST)
public @ResponseBody String ExportPackage(@RequestBody final String data, HttpSession session) {
    try {//from   w ww .j a v a 2  s .com
        Gson gson = new Gson();
        JsonObject jsonObject = gson.fromJson(data, JsonObject.class);
        int packageID = jsonObject.get("packageID").getAsInt();

        String path = System.getProperty("catalina.base");
        File folderPackage = new File(path + "/webapps/Export/");
        String excelFileName = path + "/webapps/Export/" + packageID + ".xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Package " + packageID);

        if (!folderPackage.exists()) {
            folderPackage.mkdirs();
        }
        String result = providerService.getBookingForStatictis(packageID);
        Map<String, Object[]> empinfo = new TreeMap<String, Object[]>();
        JsonObject jsonObject2 = gson.fromJson(result, JsonObject.class);
        Set<Map.Entry<String, JsonElement>> entrySet = jsonObject2.entrySet();
        int i = 1;
        HSSFRow rowhead = sheet.createRow((short) 0);

        rowhead.createCell(0).setCellValue("Trip Start");
        rowhead.createCell(1).setCellValue("Number of Bookings");
        rowhead.createCell(2).setCellValue("Number of Trippers");
        rowhead.createCell(3).setCellValue("Max quota / Day");
        rowhead.createCell(4).setCellValue("Pending");
        rowhead.createCell(5).setCellValue("Completed");

        for (Map.Entry<String, JsonElement> entry : entrySet) {
            String bookingDate = entry.getKey();
            BookingStatistics bookingStatistics = gson.fromJson(entry.getValue(), BookingStatistics.class);
            rowhead = sheet.createRow((short) i);
            rowhead.createCell(0).setCellValue(bookingDate);
            rowhead.createCell(1).setCellValue(bookingStatistics.getNumberOfBooking());
            rowhead.createCell(2).setCellValue(bookingStatistics.getNumberOfTripper());
            rowhead.createCell(3).setCellValue(bookingStatistics.getMaxQuota());
            rowhead.createCell(4).setCellValue(bookingStatistics.getPending());
            rowhead.createCell(5).setCellValue(bookingStatistics.getCompleted());
            i++;
        }
        FileOutputStream fileOut = new FileOutputStream(excelFileName);

        workbook.write(fileOut);

        fileOut.close();
        return "/Export/" + packageID + ".xls";
    } catch (Exception e) {
        String content = "Function: ProviderController - ExportPackage\n" + "***Input***\n" + "data: " + data
                + "\n" + "**********\n" + "****Error****\n" + e.getMessage() + "\n" + "**********";
        errorService.logBugWithAccount(content, session, e);
        return "{\"result\": \"error\"}";
    }
}

From source file:Controller.ProviderController.java

@RequestMapping(value = "/ExportSales", method = RequestMethod.POST)
public @ResponseBody String ExportSales(@RequestBody final String data, HttpSession session) {
    try {/*from w ww.  j  a va2  s . co m*/
        Gson gson = new Gson();
        JsonObject jsonObject = gson.fromJson(data, JsonObject.class);
        int providerID = jsonObject.get("ProviderID").getAsInt();
        JsonArray sales = jsonObject.get("ExportSales").getAsJsonArray();
        for (int i = 0; i < sales.size(); i++) {

        }
        String path = System.getProperty("catalina.base");
        File folderPackage = new File(path + "/webapps/ExportSale/");
        String excelFileName = path + "/webapps/ExportSale/" + providerID + ".xls";
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Revenue Detail");

        if (!folderPackage.exists()) {
            folderPackage.mkdirs();
        }

        HSSFRow rowhead = sheet.createRow((short) 0);

        rowhead.createCell(0).setCellValue("Package Name");
        rowhead.createCell(1).setCellValue("Number of Bookings");
        rowhead.createCell(2).setCellValue("Completed Booking");
        rowhead.createCell(3).setCellValue("Expire Date");
        rowhead.createCell(4).setCellValue("Revenue");
        rowhead.createCell(5).setCellValue("Payment Due");

        for (int i = 1; i < sales.size(); i++) {
            rowhead = sheet.createRow((short) i);
            rowhead.createCell(0).setCellValue(sales.get(i).getAsJsonObject().get("packageName").getAsString());
            rowhead.createCell(1)
                    .setCellValue(sales.get(i).getAsJsonObject().get("numberOfBooking").getAsString());
            rowhead.createCell(2)
                    .setCellValue(sales.get(i).getAsJsonObject().get("completedBooking").getAsString());
            rowhead.createCell(3).setCellValue(sales.get(i).getAsJsonObject().get("endDay").getAsString());
            rowhead.createCell(4).setCellValue(sales.get(i).getAsJsonObject().get("Revenue").getAsString());
            rowhead.createCell(5).setCellValue(sales.get(i).getAsJsonObject().get("paymentDue").getAsString());
        }
        FileOutputStream fileOut = new FileOutputStream(excelFileName);

        workbook.write(fileOut);

        fileOut.close();
        return "/ExportSale/" + providerID + ".xls";
    } catch (Exception e) {
        String content = "Function: ProviderController - ExportSales\n" + "***Input***\n" + "data: " + data
                + "\n" + "**********\n" + "****Error****\n" + e.getMessage() + "\n" + "**********";
        errorService.logBugWithAccount(content, session, e);
        return "{\"result\": \"error\"}";
    }
}

From source file:controller.VisitasController.java

public void exportExcel() {

    FileChooser fileChooser = new FileChooser();
    fileChooser.setTitle("Open Resource File");
    fileChooser.setInitialDirectory(new File(System.getProperty("user.home")));
    fileChooser.getExtensionFilters().add(new FileChooser.ExtensionFilter("XLS", "*.xls"));
    File f = fileChooser.showSaveDialog(null);

    try {/*w  w  w  . ja v a 2  s .c o m*/
        String filename = f.getAbsolutePath();
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("FirstSheet");
        int bool = 1;

        InputStream inputStream = getClass().getResourceAsStream("/images/excel-logo.jpg");

        byte[] imageBytes = IOUtils.toByteArray(inputStream);

        int pictureureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);

        inputStream.close();

        CreationHelper helper = workbook.getCreationHelper();

        Drawing drawing = sheet.createDrawingPatriarch();

        ClientAnchor anchor = helper.createClientAnchor();

        anchor.setCol1(1);
        anchor.setRow1(0);

        Picture pict = drawing.createPicture(anchor, pictureureIdx);

        HSSFRow rowhead = sheet.createRow(8);

        rowhead.createCell(0);
        rowhead.createCell(1).setCellValue("Cedula");
        rowhead.createCell(2).setCellValue("Cliente");
        rowhead.createCell(3).setCellValue("Contrato");
        rowhead.createCell(4).setCellValue("Plan");
        rowhead.createCell(5).setCellValue("Fecha");
        rowhead.createCell(6).setCellValue("Hora");
        rowhead.createCell(7).setCellValue("Invitados");
        makeRowBold(workbook, rowhead);

        for (int i = 0; i < table.getItems().size(); i++) {
            HSSFRow row = sheet.createRow(i + 9);
            Asistencia a = (Asistencia) table.getItems().get(i);
            row.createCell(1).setCellValue(a.getCedula());
            row.createCell(2).setCellValue(a.getNombre());
            row.createCell(3).setCellValue(a.getContrato());
            row.createCell(4).setCellValue(a.getPlan());
            row.createCell(5).setCellValue(a.getFecha());
            row.createCell(6).setCellValue(a.getHora());
            row.createCell(7).setCellValue(Integer.parseInt(a.getInvitados()));
            centerRow(workbook, row);
        }
        autoSizeColumns(workbook);
        pict.resize();
        FileOutputStream fileOut = new FileOutputStream(filename);
        workbook.write(fileOut);
        fileOut.close();

    } catch (Exception ex) {
        ex.printStackTrace();
    }

}

From source file:controllers.ClientController.java

public void getXls(HttpServletResponse response, HSSFWorkbook workbook) throws IOException {
    response.setContentType("application/octet-stream");
    response.setHeader("Content-Disposition", "attachment; filename=Clients.xls");
    workbook.write(response.getOutputStream());
}

From source file:controllers.EventController.java

@RequestMapping("/getXls")
public void getXls(Map<String, Object> model, @RequestParam("eventId") Long eventId,
        HttpServletResponse response) throws Exception {
    File f = new File("/usr/local/etc/xls/" + eventId + ".xls");
    if (f.exists()) {
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attachment; filename=" + eventId + ".xls");
        InputStream inp = new FileInputStream(f);
        HSSFWorkbook wb = new HSSFWorkbook(inp);
        wb.write(response.getOutputStream());
    }/*from www  . ja  v a2 s . com*/
}

From source file:controllers.transformer.ExcelTransformer.java

License:Open Source License

public byte[] getBytes() {
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");

    if (results == null) {
        results = survey.resultCollection;
    }//from w  w w . j  a v  a 2  s.  c o  m

    /** Header **/
    HSSFRow row = sheet.createRow(0);
    int fieldcounter = 0;
    row.createCell(fieldcounter++).setCellValue("ResultId");
    row.createCell(fieldcounter++).setCellValue("SurveyId");
    row.createCell(fieldcounter++).setCellValue("Title");
    row.createCell(fieldcounter++).setCellValue("Start time");
    row.createCell(fieldcounter++).setCellValue("End time");
    row.createCell(fieldcounter++).setCellValue("Date Sent");
    row.createCell(fieldcounter++).setCellValue("User");
    row.createCell(fieldcounter++).setCellValue("Phone Number");
    row.createCell(fieldcounter++).setCellValue("Lat");
    row.createCell(fieldcounter++).setCellValue("Lon");

    /** Header Fields**/
    for (Question question : survey.getQuestions()) {
        row.createCell(fieldcounter++).setCellValue(question.label);
    }

    int countrow = 0;
    row = sheet.createRow(++countrow);

    //SimpleDateFormat dateFormat = new SimpleDateFormat("EEE, d MMM yyyy HH:mm:ss Z");
    SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss Z");

    for (NdgResult result : results) {
        fieldcounter = 0;
        row.createCell(fieldcounter++).setCellValue(result.resultId);
        row.createCell(fieldcounter++).setCellValue(result.survey.surveyId);
        row.createCell(fieldcounter++).setCellValue(result.title);
        row.createCell(fieldcounter++).setCellValue(dateFormat.format(result.startTime));
        row.createCell(fieldcounter++).setCellValue(dateFormat.format(result.endTime));

        if (result.dateSent != null) {
            row.createCell(fieldcounter++).setCellValue(dateFormat.format(result.dateSent));
        } else {
            row.createCell(fieldcounter++).setCellValue("");
        }

        row.createCell(fieldcounter++).setCellValue(result.ndgUser.username);
        row.createCell(fieldcounter++).setCellValue(result.ndgUser.phoneNumber);
        row.createCell(fieldcounter++).setCellValue(result.latitude);
        row.createCell(fieldcounter++).setCellValue(result.longitude);

        for (Question question : survey.getQuestions()) {//to ensure right answer order
            Collection<Answer> answers = CollectionUtils.intersection(question.answerCollection,
                    result.answerCollection);//only one should left, hope that it does not modify results
            if (answers.isEmpty()) {
                row.createCell(fieldcounter++).setCellValue("");
            } else if (answers.size() == 1) {
                Answer answer = answers.iterator().next();
                if (answer.question.questionType.typeName.equalsIgnoreCase(QuestionTypesConsts.IMAGE)) {//TODO handle other binary data
                    row.createCell(fieldcounter++).setCellValue(storeImagesAndGetValueToExport(survey.surveyId,
                            result.resultId, answer.id, answer.binaryData));
                } else if (answer.question.questionType.typeName.equalsIgnoreCase(QuestionTypesConsts.INT)) {
                    Integer value = Integer.valueOf(answer.textData);
                    row.createCell(fieldcounter++).setCellValue(value);
                } else if (answer.question.questionType.typeName
                        .equalsIgnoreCase(QuestionTypesConsts.DECIMAL)) {
                    Float value = Float.valueOf(answer.textData);
                    row.createCell(fieldcounter++).setCellValue(value);
                } else {
                    String value = answer.textData;
                    value = value.trim().replaceAll("\n", "");
                    row.createCell(fieldcounter++).setCellValue(value);
                }
            } else {
                Logger.getAnonymousLogger().log(Level.WARNING,
                        "to many answers. ResID={0}questioId={1}answerCount={2}",
                        new Object[] { result.resultId, question.id, question.answerCollection.size() });
                break;
            }
        }
        row = sheet.createRow(++countrow);
    }
    try {
        wb.write(out);
    } catch (IOException e) {
        e.printStackTrace();
    }
    return out.toByteArray();
}

From source file:corner.orm.tapestry.service.excel.ExcelService.java

License:Apache License

/**
 * @see org.apache.tapestry.engine.IEngineService#service(org.apache.tapestry.IRequestCycle)
 *///from  w  w w . j  a v  a2s.  c  om
public void service(IRequestCycle cycle) throws IOException {

    String activePageName = cycle.getParameter(ServiceConstants.PAGE);

    Object[] parameters = _linkFactory.extractListenerParameters(cycle);

    String tableviewId = (String) parameters[0];

    String fileName = (String) parameters[1];

    String genType = (String) parameters[2];

    boolean enableTitle = (Boolean) parameters[3];

    IPage page = cycle.getPage(activePageName);

    cycle.activate(page);

    // ?TableView
    TableView comp = (TableView) page.getComponent(tableviewId);
    if (ComponentResponseUtils.EXCEL_DATA_GENERATE_TYPE_FULL.equals(genType)) {// ?
        comp.getTableModel().getPagingState().setCurrentPage(0);
        comp.getTableModel().getPagingState().setPageSize(Integer.MAX_VALUE);// ??
    }

    if (page != null) {
        HSSFWorkbook workbook = generateExcelData(page, enableTitle, comp.getTableModel());

        OutputStream output = _response.getOutputStream(new ContentType(CONTENT_TYPE));

        // response
        ComponentResponseUtils.constructResponse(fileName, EXCLE_FILE_EXTENSION_NAME, _requestCycle, _response);

        workbook.write(output);
    }

}