Example usage for org.apache.poi.ss.usermodel Workbook write

List of usage examples for org.apache.poi.ss.usermodel Workbook write

Introduction

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

Prototype

void write(OutputStream stream) throws IOException;

Source Link

Document

Write out this workbook to an Outputstream.

Usage

From source file:GestoSAT.Albaran.java

License:Open Source License

public String albaran2XLSX() {
    String archivo = "Albaran" + (new Date()).getTime() + ".xlsx";
    try {//from w  w w. j av a2  s.com
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Albarn");
        Row row;
        Cell cell;

        Oficina o = this.getGestoSAT().getEmpleado().getOficina();

        row = sheet.createRow((short) 0);
        cell = row.createCell(0);
        cell.setCellValue("Empresa");
        cell = row.createCell(1);
        cell.setCellValue(o.getNombre());
        row = sheet.createRow((short) 2);
        cell = row.createCell(0);
        cell.setCellValue("Direccin");
        row = sheet.createRow((short) 3);
        cell = row.createCell(0);
        cell.setCellValue(o.getProvincia());
        cell = row.createCell(1);
        cell.setCellValue(o.getPoblacion());
        cell = row.createCell(2);
        cell.setCellValue(o.getCalle());
        cell = row.createCell(3);
        cell.setCellValue(o.getNumero());

        row = sheet.createRow((short) 5);
        cell = row.createCell(0);
        cell.setCellValue("Datos cliente");
        row = sheet.createRow((short) 7);
        cell = row.createCell(0);
        cell.setCellValue("Nombre");
        cell = row.createCell(1);
        cell.setCellValue(this.getCliente().getNombre());

        row = sheet.createRow((short) 9);
        cell = row.createCell(0);
        cell.setCellValue("Datos Albarn");

        row = sheet.createRow((short) 10);
        cell = row.createCell(0);
        cell.setCellValue("Concepto");
        cell = row.createCell(1);
        cell.setCellValue(this.concepto);
        row = sheet.createRow((short) 11);
        cell = row.createCell(0);
        cell.setCellValue("Observaciones");
        cell = row.createCell(1);
        cell.setCellValue(this.getObservaciones());

        row = sheet.createRow((short) 13);
        cell = row.createCell(0);
        cell.setCellValue("Provincia");
        cell = row.createCell(1);
        cell.setCellValue("Poblacin");
        cell = row.createCell(2);
        cell.setCellValue("Calle");
        cell = row.createCell(3);
        cell.setCellValue("Nmero");
        cell = row.createCell(4);
        cell.setCellValue("Escalera");
        cell = row.createCell(5);
        cell.setCellValue("Piso");
        cell = row.createCell(6);
        cell.setCellValue("Puerta");

        row = sheet.createRow((short) 14);
        cell = row.createCell(0);
        cell.setCellValue(this.provincia);
        cell = row.createCell(1);
        cell.setCellValue(this.poblacion);
        cell = row.createCell(2);
        cell.setCellValue(this.calle);
        cell = row.createCell(3);
        cell.setCellValue(this.numero);
        cell = row.createCell(4);
        cell.setCellValue(this.escalera);
        cell = row.createCell(5);
        cell.setCellValue(this.piso);
        cell = row.createCell(6);
        cell.setCellValue(this.puerta);

        float total = 0;
        DecimalFormat df = new DecimalFormat("0.00");

        if (!this.trabajoRealizado.isEmpty()) {
            row = sheet.createRow((short) 16);
            cell = row.createCell(0);
            cell.setCellValue("Trabajos presupuestados");

            row = sheet.createRow((short) 18);
            cell = row.createCell(0);
            cell.setCellValue("Nombre");
            cell = row.createCell(1);
            cell.setCellValue("Precio h");
            cell = row.createCell(2);
            cell.setCellValue("Horas");
            cell = row.createCell(3);
            cell.setCellValue("Total");

            Iterator itTrabajos = this.trabajoRealizado.entrySet().iterator();
            for (int index = 19; itTrabajos.hasNext(); index++) {
                Map.Entry aux = (Map.Entry) itTrabajos.next();
                Trabajo trabajo = (Trabajo) aux.getValue();
                row = sheet.createRow((short) index);
                cell = row.createCell(0);
                cell.setCellValue(
                        trabajo.getEmpleado().getNombre() + " " + trabajo.getEmpleado().getApellidos());
                cell = row.createCell(1);
                cell.setCellValue(trabajo.getEmpleado().getPrecioHora());
                cell = row.createCell(2);
                cell.setCellValue(trabajo.getHoras());
                cell = row.createCell(3);
                cell.setCellValue(df.format(trabajo.getEmpleado().getPrecioHora() * trabajo.getHoras()));
                total += trabajo.getEmpleado().getPrecioHora() * trabajo.getHoras();
            }
        }
        int valueIndex = row.getRowNum();

        if (!this.materialUtilizado.isEmpty()) {
            valueIndex++;
            valueIndex++;
            row = sheet.createRow((short) valueIndex);
            cell = row.createCell(0);
            cell.setCellValue("Materiales presupuestados");

            valueIndex++;
            valueIndex++;
            row = sheet.createRow((short) valueIndex);
            cell = row.createCell(0);
            cell.setCellValue("#");
            cell = row.createCell(1);
            cell.setCellValue("Nombre");
            cell = row.createCell(2);
            cell.setCellValue("Precio Ud");
            cell = row.createCell(3);
            cell.setCellValue("Cantidad");
            cell = row.createCell(4);
            cell.setCellValue("Total");

            Iterator itMateriales = this.materialUtilizado.entrySet().iterator();
            valueIndex++;
            for (int index = valueIndex; itMateriales.hasNext(); index++) {
                Map.Entry aux = (Map.Entry) itMateriales.next();
                MaterialTrabajos material = (MaterialTrabajos) aux.getValue();
                row = sheet.createRow((short) index);
                cell = row.createCell(0);
                cell.setCellValue(aux.getKey().toString());
                cell = row.createCell(1);
                cell.setCellValue(material.getStock().getNombre());
                cell = row.createCell(2);
                cell.setCellValue(material.getStock().getPrecioUnidad());
                cell = row.createCell(3);
                cell.setCellValue(material.getCantidad());
                cell = row.createCell(4);
                cell.setCellValue(df.format(material.getStock().getPrecioUnidad() * material.getCantidad()));
                total += material.getStock().getPrecioUnidad() * material.getCantidad();
                valueIndex = index;
            }
        }

        float iva = this.getGestoSAT().getIva() / (float) 100;
        valueIndex++;
        valueIndex++;
        row = sheet.createRow((short) valueIndex);
        cell = row.createCell(0);
        cell.setCellValue("I.V.A");
        cell = row.createCell(1);
        cell.setCellValue(df.format(iva * total));
        valueIndex++;
        row = sheet.createRow((short) valueIndex);
        cell = row.createCell(0);
        cell.setCellValue("Total sin I.V.A");
        cell = row.createCell(1);
        cell.setCellValue(df.format(total));
        valueIndex++;
        row = sheet.createRow((short) valueIndex + 1);
        cell = row.createCell(0);
        cell.setCellValue("Total");
        cell = row.createCell(1);
        cell.setCellValue(df.format(this.total));

        FileOutputStream fileOut = new FileOutputStream("/TomEE/webapps/ROOT/descargables/" + archivo);
        wb.write(fileOut);
        fileOut.close();
        // Devolver Archivo
        return "descargables/" + archivo;
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    } catch (IOException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    }
}

From source file:GestoSAT.Entrada.java

License:Open Source License

public String entrada2XLSX() {
    String archivo = "Entrada" + (new Date()).getTime() + ".xlsx";
    try {//  w  w w.j  ava2 s  .  c  o m
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Entrada");
        // Create a row and put some cells in it. Rows are 0 based.
        Row row;
        Cell cell;
        row = sheet.createRow((short) 0);
        cell = row.createCell(0);
        cell.setCellValue("Datos cliente");
        row = sheet.createRow((short) 1);
        cell = row.createCell(0);
        cell.setCellValue("Nombre");
        cell = row.createCell(1);
        cell.setCellValue(this.getCliente().getNombre());
        row = sheet.createRow((short) 2);
        cell = row.createCell(0);
        cell.setCellValue("Apellidos");
        cell = row.createCell(1);
        cell.setCellValue(this.getCliente().getApellidos());

        row = sheet.createRow((short) 4);
        cell = row.createCell(0);
        cell.setCellValue("Valores entrada");

        row = sheet.createRow((short) 5);
        cell = row.createCell(0);
        cell.setCellValue("Motivo");
        cell = row.createCell(1);
        cell.setCellValue(this.incidencia.getMotivo());

        row = sheet.createRow((short) 6);
        cell = row.createCell(0);
        cell.setCellValue("Observaciones");
        cell = row.createCell(1);
        cell.setCellValue(this.getObservaciones());

        if (this.incidencia.getClass().getName().equals("GestoSAT.Cita")) {
            Cita cita = (Cita) this.incidencia;
            row = sheet.createRow((short) 8);
            cell = row.createCell(0);
            cell.setCellValue("Observaciones direccin");
            cell = row.createCell(1);
            cell.setCellValue(cita.getObservacionesDirrecion());
            row = sheet.createRow((short) 9);
            cell = row.createCell(0);
            cell.setCellValue("Observaciones cita");
            cell = row.createCell(1);
            cell.setCellValue(cita.getObservaciones());
            row = sheet.createRow((short) 11);
            cell = row.createCell(0);
            cell.setCellValue("Provincia");
            cell = row.createCell(1);
            cell.setCellValue("Poblacin");
            cell = row.createCell(2);
            cell.setCellValue("Calle");
            cell = row.createCell(3);
            cell.setCellValue("Nmero");
            cell = row.createCell(4);
            cell.setCellValue("Escalera");
            cell = row.createCell(5);
            cell.setCellValue("Piso");
            cell = row.createCell(6);
            cell.setCellValue("Puerta");
            row = sheet.createRow((short) 12);
            cell = row.createCell(0);
            cell.setCellValue(cita.getProvincia());
            cell = row.createCell(1);
            cell.setCellValue(cita.getPoblacion());
            cell = row.createCell(2);
            cell.setCellValue(cita.getCalle());
            cell = row.createCell(3);
            cell.setCellValue(cita.getNumero());
            cell = row.createCell(4);
            cell.setCellValue(cita.getEscalera());
            cell = row.createCell(5);
            cell.setCellValue(cita.getPiso());
            cell = row.createCell(6);
            cell.setCellValue(cita.getPuerta());
        } else {
            Averia averia = (Averia) this.incidencia;
            row = sheet.createRow((short) 8);
            cell = row.createCell(0);
            cell.setCellValue("Observaciones");
            cell = row.createCell(1);
            cell.setCellValue(averia.getObservaciones());
            row = sheet.createRow((short) 9);
            cell = row.createCell(0);
            cell.setCellValue("Observaciones aparato");
            cell = row.createCell(1);
            cell.setCellValue(averia.getAparato().getObservaciones());
            row = sheet.createRow((short) 11);
            cell = row.createCell(0);
            cell.setCellValue("Tipo");
            cell = row.createCell(1);
            cell.setCellValue("Modelo");
            cell = row.createCell(2);
            cell.setCellValue("Marca");
            cell = row.createCell(3);
            cell.setCellValue("N Seria");
            cell = row.createCell(4);
            cell.setCellValue("Color");
            row = sheet.createRow((short) 12);
            cell = row.createCell(0);
            cell.setCellValue(averia.getAparato().getTipo());
            cell = row.createCell(1);
            cell.setCellValue(averia.getAparato().getModelo());
            cell = row.createCell(2);
            cell.setCellValue(averia.getAparato().getMarca());
            cell = row.createCell(3);
            cell.setCellValue(averia.getAparato().getNumSerie());
            cell = row.createCell(4);
            cell.setCellValue(averia.getAparato().getColor());
        }

        FileOutputStream fileOut = new FileOutputStream("/TomEE/webapps/ROOT/descargables/" + archivo);
        wb.write(fileOut);
        fileOut.close();
        // Devolver Archivo
        return "descargables/" + archivo;
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    } catch (IOException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    }
}

From source file:GestoSAT.Factura.java

License:Open Source License

public String factura2XLSX() {
    String archivo = "Factura" + (new Date()).getTime() + ".xlsx";
    try {/*from  www . ja v a  2  s  .  co  m*/
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Factura");
        Row row;
        Cell cell;

        Oficina o = this.getGestoSAT().getEmpleado().getOficina();

        row = sheet.createRow((short) 0);
        cell = row.createCell(0);
        cell.setCellValue("Empresa");
        cell = row.createCell(1);
        cell.setCellValue(o.getNombre());
        row = sheet.createRow((short) 2);
        cell = row.createCell(0);
        cell.setCellValue("Direccin");
        row = sheet.createRow((short) 3);
        cell = row.createCell(0);
        cell.setCellValue(o.getProvincia());
        cell = row.createCell(1);
        cell.setCellValue(o.getPoblacion());
        cell = row.createCell(2);
        cell.setCellValue(o.getCalle());
        cell = row.createCell(3);
        cell.setCellValue(o.getNumero());

        row = sheet.createRow((short) 5);
        cell = row.createCell(0);
        cell.setCellValue("Datos cliente");
        row = sheet.createRow((short) 7);
        cell = row.createCell(0);
        cell.setCellValue("Nombre");
        cell = row.createCell(1);
        cell.setCellValue(this.getCliente().getNombre());

        row = sheet.createRow((short) 9);
        cell = row.createCell(0);
        cell.setCellValue("Datos Factura");

        row = sheet.createRow((short) 11);
        cell = row.createCell(0);
        cell.setCellValue("Concepto");
        cell = row.createCell(1);
        cell.setCellValue(this.concepto);
        row = sheet.createRow((short) 12);
        cell = row.createCell(0);
        cell.setCellValue("Forma pago");
        cell = row.createCell(1);
        cell.setCellValue(this.formaPago);
        row = sheet.createRow((short) 13);
        cell = row.createCell(0);
        cell.setCellValue("Observaciones");
        cell = row.createCell(1);
        cell.setCellValue(this.getObservaciones());

        row = sheet.createRow((short) 15);
        cell = row.createCell(0);
        cell.setCellValue("Albaranes");

        float total = 0;
        DecimalFormat df = new DecimalFormat("0.00");
        int valueIndex = row.getRowNum() + 2;

        Iterator itAlbaranes = this.albaran.entrySet().iterator();
        while (itAlbaranes.hasNext()) {
            Map.Entry auxAlb = (Map.Entry) itAlbaranes.next();
            Albaran alb = (Albaran) auxAlb.getValue();

            row = sheet.createRow((short) valueIndex);
            cell = row.createCell(0);
            cell.setCellValue("Concepto Alabrn");
            cell = row.createCell(1);
            cell.setCellValue(alb.getConcepto());

            if (!alb.getTrabajoRealizado().isEmpty()) {
                valueIndex = row.getRowNum() + 2;
                row = sheet.createRow((short) valueIndex);
                cell = row.createCell(0);
                cell.setCellValue("Trabajos presupuestados");
                valueIndex = row.getRowNum() + 2;
                row = sheet.createRow((short) valueIndex);
                cell = row.createCell(0);
                cell.setCellValue("Nombre");
                cell = row.createCell(1);
                cell.setCellValue("Precio h");
                cell = row.createCell(2);
                cell.setCellValue("Horas");
                cell = row.createCell(3);
                cell.setCellValue("Total");
                valueIndex = row.getRowNum() + 2;

                Iterator itTrabajos = alb.getTrabajoRealizado().entrySet().iterator();
                for (int i = valueIndex; itTrabajos.hasNext(); i++) {
                    Map.Entry aux = (Map.Entry) itTrabajos.next();
                    Trabajo trabajo = (Trabajo) aux.getValue();
                    row = sheet.createRow((short) i);
                    cell = row.createCell(0);
                    cell.setCellValue(
                            trabajo.getEmpleado().getNombre() + " " + trabajo.getEmpleado().getApellidos());
                    cell = row.createCell(1);
                    cell.setCellValue(trabajo.getEmpleado().getPrecioHora());
                    cell = row.createCell(2);
                    cell.setCellValue(trabajo.getHoras());
                    cell = row.createCell(3);
                    cell.setCellValue(df.format(trabajo.getEmpleado().getPrecioHora() * trabajo.getHoras()));
                    total += trabajo.getEmpleado().getPrecioHora() * trabajo.getHoras();
                }
            }

            valueIndex = row.getRowNum() + 2;

            if (!alb.getMaterialUtilizado().isEmpty()) {
                row = sheet.createRow((short) valueIndex);
                cell = row.createCell(0);
                cell.setCellValue("Materiales presupuestados");

                valueIndex++;
                valueIndex++;
                row = sheet.createRow((short) valueIndex);
                cell = row.createCell(0);
                cell.setCellValue("#");
                cell = row.createCell(1);
                cell.setCellValue("Nombre");
                cell = row.createCell(2);
                cell.setCellValue("Precio Ud");
                cell = row.createCell(3);
                cell.setCellValue("Cantidad");
                cell = row.createCell(4);
                cell.setCellValue("Total");

                Iterator itMateriales = alb.getMaterialUtilizado().entrySet().iterator();
                valueIndex++;
                for (int i = valueIndex; itMateriales.hasNext(); i++) {
                    Map.Entry aux = (Map.Entry) itMateriales.next();
                    MaterialTrabajos material = (MaterialTrabajos) aux.getValue();
                    row = sheet.createRow((short) i);
                    cell = row.createCell(0);
                    cell.setCellValue(aux.getKey().toString());
                    cell = row.createCell(1);
                    cell.setCellValue(material.getStock().getNombre());
                    cell = row.createCell(2);
                    cell.setCellValue(material.getStock().getPrecioUnidad());
                    cell = row.createCell(3);
                    cell.setCellValue(material.getCantidad());
                    cell = row.createCell(4);
                    cell.setCellValue(
                            df.format(material.getStock().getPrecioUnidad() * material.getCantidad()));
                    total += material.getStock().getPrecioUnidad() * material.getCantidad();
                }
            }
            valueIndex = row.getRowNum() + 2;

        }
        float iva = this.getGestoSAT().getIva() / (float) 100;
        valueIndex++;
        row = sheet.createRow((short) valueIndex);
        cell = row.createCell(0);
        cell.setCellValue("I.V.A");
        cell = row.createCell(1);
        cell.setCellValue(df.format(iva * total));
        valueIndex++;
        row = sheet.createRow((short) valueIndex);
        cell = row.createCell(0);
        cell.setCellValue("Total sin I.V.A");
        cell = row.createCell(1);
        cell.setCellValue(df.format(total));
        valueIndex++;
        row = sheet.createRow((short) valueIndex + 1);
        cell = row.createCell(0);
        cell.setCellValue("Total");
        cell = row.createCell(1);
        cell.setCellValue(df.format(this.total));

        FileOutputStream fileOut = new FileOutputStream("/TomEE/webapps/ROOT/descargables/" + archivo);
        wb.write(fileOut);
        fileOut.close();
        // Devolver Archivo
        return "descargables/" + archivo;
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    } catch (IOException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    }
}

From source file:GestoSAT.Presupuesto.java

License:Open Source License

public String presupuesto2XLSX() {
    String archivo = "Presupuesto" + (new Date()).getTime() + ".xlsx";
    try {//from w w  w  .j a  v  a 2 s  .c  om
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Presuepuesto");
        Row row;
        Cell cell;

        Oficina o = this.getGestoSAT().getEmpleado().getOficina();

        row = sheet.createRow((short) 0);
        cell = row.createCell(0);
        cell.setCellValue("Empresa");
        cell = row.createCell(1);
        cell.setCellValue(o.getNombre());
        row = sheet.createRow((short) 2);
        cell = row.createCell(0);
        cell.setCellValue("Direccin");
        row = sheet.createRow((short) 3);
        cell = row.createCell(0);
        cell.setCellValue(o.getProvincia());
        cell = row.createCell(1);
        cell.setCellValue(o.getPoblacion());
        cell = row.createCell(2);
        cell.setCellValue(o.getCalle());
        cell = row.createCell(3);
        cell.setCellValue(o.getNumero());

        row = sheet.createRow((short) 5);
        cell = row.createCell(0);
        cell.setCellValue("Datos cliente");
        row = sheet.createRow((short) 7);
        cell = row.createCell(0);
        cell.setCellValue("Nombre");
        cell = row.createCell(1);
        cell.setCellValue(this.getCliente().getNombre());

        row = sheet.createRow((short) 9);
        cell = row.createCell(0);
        cell.setCellValue("Datos presupuesto");

        row = sheet.createRow((short) 10);
        cell = row.createCell(0);
        cell.setCellValue("Concepto");
        cell = row.createCell(1);
        cell.setCellValue(this.concepto);
        row = sheet.createRow((short) 11);
        cell = row.createCell(0);
        cell.setCellValue("Fecha de validez");
        cell = row.createCell(1);
        cell.setCellValue(this.validez.toString());
        row = sheet.createRow((short) 12);
        cell = row.createCell(0);
        cell.setCellValue("Aceptado");
        cell = row.createCell(1);
        if (this.aceptado)
            cell.setCellValue("SI");
        else
            cell.setCellValue("NO");

        row = sheet.createRow((short) 13);
        cell = row.createCell(0);
        cell.setCellValue("Forma de pago");
        cell = row.createCell(1);
        cell.setCellValue(this.formaPago);
        row = sheet.createRow((short) 14);
        cell = row.createCell(0);
        cell.setCellValue("Adelanto");
        cell = row.createCell(1);
        cell.setCellValue(this.adelanto);
        row = sheet.createRow((short) 15);
        cell = row.createCell(0);
        cell.setCellValue("Plazo de trabajo");
        cell = row.createCell(1);
        cell.setCellValue(this.plazo);
        row = sheet.createRow((short) 16);
        cell = row.createCell(0);
        cell.setCellValue("Condiciones");
        cell = row.createCell(1);
        cell.setCellValue(this.condiciones);
        row = sheet.createRow((short) 17);
        cell = row.createCell(0);
        cell.setCellValue("Seguro");
        cell = row.createCell(1);
        cell.setCellValue(this.seguro);
        row = sheet.createRow((short) 18);
        cell = row.createCell(0);
        cell.setCellValue("Garantia");
        cell = row.createCell(1);
        cell.setCellValue(this.garantia);

        float total = 0;
        DecimalFormat df = new DecimalFormat("0.00");

        if (!this.trabajoPresupuestado.isEmpty()) {
            row = sheet.createRow((short) 20);
            cell = row.createCell(0);
            cell.setCellValue("Trabajos presupuestados");

            row = sheet.createRow((short) 22);
            cell = row.createCell(0);
            cell.setCellValue("Nombre");
            cell = row.createCell(1);
            cell.setCellValue("Precio h");
            cell = row.createCell(2);
            cell.setCellValue("Horas");
            cell = row.createCell(3);
            cell.setCellValue("Total");

            Iterator itTrabajos = this.trabajoPresupuestado.entrySet().iterator();
            for (int index = 23; itTrabajos.hasNext(); index++) {
                Map.Entry aux = (Map.Entry) itTrabajos.next();
                Trabajo trabajo = (Trabajo) aux.getValue();
                row = sheet.createRow((short) index);
                cell = row.createCell(0);
                cell.setCellValue(
                        trabajo.getEmpleado().getNombre() + " " + trabajo.getEmpleado().getApellidos());
                cell = row.createCell(1);
                cell.setCellValue(trabajo.getEmpleado().getPrecioHora());
                cell = row.createCell(2);
                cell.setCellValue(trabajo.getHoras());
                cell = row.createCell(3);
                cell.setCellValue(df.format(trabajo.getEmpleado().getPrecioHora() * trabajo.getHoras()));
                total += trabajo.getEmpleado().getPrecioHora() * trabajo.getHoras();
            }
        }
        int valueIndex = row.getRowNum();

        if (!this.materialPresupuestado.isEmpty()) {
            valueIndex++;
            valueIndex++;
            row = sheet.createRow((short) valueIndex);
            cell = row.createCell(0);
            cell.setCellValue("Materiales presupuestados");

            valueIndex++;
            row = sheet.createRow((short) valueIndex);
            cell = row.createCell(0);
            cell.setCellValue("Nombre");
            cell = row.createCell(1);
            cell.setCellValue("Precio Ud");
            cell = row.createCell(2);
            cell.setCellValue("Cantidad");
            cell = row.createCell(3);
            cell.setCellValue("Total");

            Iterator itMateriales = this.materialPresupuestado.entrySet().iterator();
            valueIndex++;
            for (int index = valueIndex; itMateriales.hasNext(); index++) {
                Map.Entry aux = (Map.Entry) itMateriales.next();
                MaterialTrabajos material = (MaterialTrabajos) aux.getValue();
                row = sheet.createRow((short) index);
                cell = row.createCell(0);
                cell.setCellValue(material.getStock().getNombre());
                cell = row.createCell(1);
                cell.setCellValue(material.getStock().getPrecioUnidad());
                cell = row.createCell(2);
                cell.setCellValue(material.getCantidad());
                cell = row.createCell(3);
                cell.setCellValue(df.format(material.getStock().getPrecioUnidad() * material.getCantidad()));
                total += material.getStock().getPrecioUnidad() * material.getCantidad();
                valueIndex = index;
            }
        }

        float iva = this.getGestoSAT().getIva() / (float) 100;
        valueIndex++;
        valueIndex++;
        row = sheet.createRow((short) valueIndex);
        cell = row.createCell(0);
        cell.setCellValue("I.V.A");
        cell = row.createCell(1);
        cell.setCellValue(df.format(iva * total));
        valueIndex++;
        row = sheet.createRow((short) valueIndex);
        cell = row.createCell(0);
        cell.setCellValue("Total sin I.V.A");
        cell = row.createCell(1);
        cell.setCellValue(df.format(total));
        valueIndex++;
        row = sheet.createRow((short) valueIndex + 1);
        cell = row.createCell(0);
        cell.setCellValue("Total");
        cell = row.createCell(1);
        cell.setCellValue(df.format(this.total));

        FileOutputStream fileOut = new FileOutputStream("/TomEE/webapps/ROOT/descargables/" + archivo);
        wb.write(fileOut);
        fileOut.close();
        // Devolver Archivo
        return "descargables/" + archivo;
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    } catch (IOException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    }
}

From source file:GestoSAT.Recibo.java

License:Open Source License

public String recibo2XLSX() {
    String archivo = "Factura" + (new Date()).getTime() + ".xlsx";
    try {/*from w ww . ja  v a2 s  . c o  m*/
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Factura");
        Row row;
        Cell cell;

        Oficina o = this.getGestoSAT().getEmpleado().getOficina();

        row = sheet.createRow((short) 0);
        cell = row.createCell(0);
        cell.setCellValue("Empresa");
        cell = row.createCell(1);
        cell.setCellValue(o.getNombre());
        row = sheet.createRow((short) 2);
        cell = row.createCell(0);
        cell.setCellValue("Direccin");
        row = sheet.createRow((short) 3);
        cell = row.createCell(0);
        cell.setCellValue(o.getProvincia());
        cell = row.createCell(1);
        cell.setCellValue(o.getPoblacion());
        cell = row.createCell(2);
        cell.setCellValue(o.getCalle());
        cell = row.createCell(3);
        cell.setCellValue(o.getNumero());

        row = sheet.createRow((short) 5);
        cell = row.createCell(0);
        cell.setCellValue("Datos cliente");
        row = sheet.createRow((short) 7);
        cell = row.createCell(0);
        cell.setCellValue("Nombre");
        cell = row.createCell(1);
        cell.setCellValue(this.getCliente().getNombre());

        row = sheet.createRow((short) 9);
        cell = row.createCell(0);
        cell.setCellValue("Datos Factura");

        row = sheet.createRow((short) 11);
        cell = row.createCell(0);
        cell.setCellValue("Concepto");
        cell = row.createCell(1);
        cell.setCellValue(this.factura.getConcepto());
        row = sheet.createRow((short) 12);
        cell = row.createCell(0);
        cell.setCellValue("Forma pago");
        cell = row.createCell(1);
        cell.setCellValue(this.factura.getFormaPago());
        row = sheet.createRow((short) 13);
        cell = row.createCell(0);
        cell.setCellValue("Observaciones");
        cell = row.createCell(1);
        cell.setCellValue(this.getObservaciones());

        row = sheet.createRow((short) 14);
        cell = row.createCell(0);
        cell.setCellValue("Direccin entrega");

        row = sheet.createRow((short) 16);
        cell = row.createCell(0);
        cell.setCellValue("Provincia");
        cell = row.createCell(1);
        cell.setCellValue("Poblacin");
        cell = row.createCell(2);
        cell.setCellValue("Calle");
        cell = row.createCell(3);
        cell.setCellValue("Nmero");
        cell = row.createCell(4);
        cell.setCellValue("Escalera");
        cell = row.createCell(5);
        cell.setCellValue("Piso");
        cell = row.createCell(6);
        cell.setCellValue("Puerta");

        row = sheet.createRow((short) 17);
        cell = row.createCell(0);
        cell.setCellValue(this.provincia);
        cell = row.createCell(1);
        cell.setCellValue(this.poblacion);
        cell = row.createCell(2);
        cell.setCellValue(this.calle);
        cell = row.createCell(3);
        cell.setCellValue(this.numero);
        cell = row.createCell(4);
        cell.setCellValue(this.escalera);
        cell = row.createCell(5);
        cell.setCellValue(this.piso);
        cell = row.createCell(6);
        cell.setCellValue(this.puerta);

        row = sheet.createRow((short) 19);
        cell = row.createCell(0);
        cell.setCellValue("Albaranes");

        float total = 0;
        DecimalFormat df = new DecimalFormat("0.00");
        int valueIndex = row.getRowNum() + 2;

        Iterator itAlbaranes = this.factura.getAlbaranes().entrySet().iterator();
        while (itAlbaranes.hasNext()) {
            Map.Entry auxAlb = (Map.Entry) itAlbaranes.next();
            Albaran alb = (Albaran) auxAlb.getValue();

            row = sheet.createRow((short) valueIndex);
            cell = row.createCell(0);
            cell.setCellValue("Concepto Alabrn");
            cell = row.createCell(1);
            cell.setCellValue(alb.getConcepto());

            if (!alb.getTrabajoRealizado().isEmpty()) {
                valueIndex = row.getRowNum() + 2;
                row = sheet.createRow((short) valueIndex);
                cell = row.createCell(0);
                cell.setCellValue("Trabajos presupuestados");
                valueIndex = row.getRowNum() + 2;
                row = sheet.createRow((short) valueIndex);
                cell = row.createCell(0);
                cell.setCellValue("Nombre");
                cell = row.createCell(1);
                cell.setCellValue("Precio h");
                cell = row.createCell(2);
                cell.setCellValue("Horas");
                cell = row.createCell(3);
                cell.setCellValue("Total");
                valueIndex = row.getRowNum() + 2;

                Iterator itTrabajos = alb.getTrabajoRealizado().entrySet().iterator();
                for (int i = valueIndex; itTrabajos.hasNext(); i++) {
                    Map.Entry aux = (Map.Entry) itTrabajos.next();
                    Trabajo trabajo = (Trabajo) aux.getValue();
                    row = sheet.createRow((short) i);
                    cell = row.createCell(0);
                    cell.setCellValue(
                            trabajo.getEmpleado().getNombre() + " " + trabajo.getEmpleado().getApellidos());
                    cell = row.createCell(1);
                    cell.setCellValue(trabajo.getEmpleado().getPrecioHora());
                    cell = row.createCell(2);
                    cell.setCellValue(trabajo.getHoras());
                    cell = row.createCell(3);
                    cell.setCellValue(df.format(trabajo.getEmpleado().getPrecioHora() * trabajo.getHoras()));
                    total += trabajo.getEmpleado().getPrecioHora() * trabajo.getHoras();
                }
            }

            valueIndex = row.getRowNum() + 2;

            if (!alb.getMaterialUtilizado().isEmpty()) {
                row = sheet.createRow((short) valueIndex);
                cell = row.createCell(0);
                cell.setCellValue("Materiales presupuestados");

                valueIndex++;
                valueIndex++;
                row = sheet.createRow((short) valueIndex);
                cell = row.createCell(0);
                cell.setCellValue("#");
                cell = row.createCell(1);
                cell.setCellValue("Nombre");
                cell = row.createCell(2);
                cell.setCellValue("Precio Ud");
                cell = row.createCell(3);
                cell.setCellValue("Cantidad");
                cell = row.createCell(4);
                cell.setCellValue("Total");

                Iterator itMateriales = alb.getMaterialUtilizado().entrySet().iterator();
                valueIndex++;
                for (int i = valueIndex; itMateriales.hasNext(); i++) {
                    Map.Entry aux = (Map.Entry) itMateriales.next();
                    MaterialTrabajos material = (MaterialTrabajos) aux.getValue();
                    row = sheet.createRow((short) i);
                    cell = row.createCell(0);
                    cell.setCellValue(aux.getKey().toString());
                    cell = row.createCell(1);
                    cell.setCellValue(material.getStock().getNombre());
                    cell = row.createCell(2);
                    cell.setCellValue(material.getStock().getPrecioUnidad());
                    cell = row.createCell(3);
                    cell.setCellValue(material.getCantidad());
                    cell = row.createCell(4);
                    cell.setCellValue(
                            df.format(material.getStock().getPrecioUnidad() * material.getCantidad()));
                    total += material.getStock().getPrecioUnidad() * material.getCantidad();
                }
            }
            valueIndex = row.getRowNum() + 2;

        }
        float iva = this.getGestoSAT().getIva() / (float) 100;
        valueIndex++;
        row = sheet.createRow((short) valueIndex);
        cell = row.createCell(0);
        cell.setCellValue("I.V.A");
        cell = row.createCell(1);
        cell.setCellValue(df.format(iva * total));
        valueIndex++;
        row = sheet.createRow((short) valueIndex);
        cell = row.createCell(0);
        cell.setCellValue("Total sin I.V.A");
        cell = row.createCell(1);
        cell.setCellValue(df.format(total));
        valueIndex++;
        row = sheet.createRow((short) valueIndex + 1);
        cell = row.createCell(0);
        cell.setCellValue("Total");
        cell = row.createCell(1);
        cell.setCellValue(df.format(total * (1 + iva)));

        FileOutputStream fileOut = new FileOutputStream("/TomEE/webapps/ROOT/descargables/" + archivo);
        wb.write(fileOut);
        fileOut.close();
        // Devolver Archivo
        return "descargables/" + archivo;
    } catch (FileNotFoundException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    } catch (IOException ex) {
        Logger.getLogger(Entrada.class.getName()).log(Level.SEVERE, null, ex);
        return "";
    }
}

From source file:gov.nasa.ensemble.core.plan.editor.merge.export.ExcelExportWizard.java

License:Open Source License

@Override
protected void savePlan(EPlan plan, File destinationFilename) throws Exception {
    boolean generatingResourceSummaryTable = isGeneratingResourceSummaryTable();
    boolean generatingPlanTable = isGeneratingActivityTable();

    Workbook wb = new HSSFWorkbook();
    Sheet planSheet = generatingPlanTable ? wb.createSheet("Plan") : null;
    Sheet resourceSheet = generatingResourceSummaryTable ? wb.createSheet("Resource Statistics") : null;
    CellStyle headerStyle = wb.createCellStyle();
    Font font = wb.createFont();/*from www.j a  v a  2s. co  m*/
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(font);

    if (generatingPlanTable && planSheet != null) {
        List<String> attributeNames = getActivityColumnHeaders();
        writeRow(planSheet.createRow(0), trimColumnHeaders(attributeNames), headerStyle);

        int rowNum = 1;
        List<RowForOneActivity> allActivitiesColumns = getColumnsForActivitiesToExport(plan, attributeNames);
        for (RowForOneActivity activityColumns : allActivitiesColumns) {
            writeRow(planSheet.createRow(rowNum++), activityColumns.allColumnsAsFormatted, null);
        }
    }

    if (generatingResourceSummaryTable && resourceSheet != null) {
        writeRow(resourceSheet.createRow(0), new String[] { getDescription(plan) }, headerStyle);
        Statistic[] statistics = getResourceColumnHeaders();
        writeRow(resourceSheet.createRow(1), mapToStrings(statistics), headerStyle);

        int rowNum = 2;
        List<String[]> resourcesColumns = getColumnsForResourceStats(plan, statistics);
        for (String[] resourceColumns : resourcesColumns) {
            writeRow(resourceSheet.createRow(rowNum++), resourceColumns, null);
        }
    }

    try {
        FileOutputStream out = new FileOutputStream(destinationFilename);
        wb.write(out);
        out.close();
    } catch (IllegalArgumentException iae) {
        iae.printStackTrace();
    } catch (FileNotFoundException fnfe) {
        fnfe.printStackTrace();
    } catch (IOException ioe) {
        ioe.printStackTrace();
    }
}

From source file:gov.nih.nci.cadsr.cdecurate.test.TestSpreadsheetDownload.java

License:BSD License

private void createDownloadColumns(ArrayList<String[]> allRows) {
    final int MAX_ROWS = 65000;

    String sheetName = "Custom Download";
    int sheetNum = 1;
    String fillIn = "false";// set true to fill in all values.
    String[] columns = null;//ww  w .j a  va2 s .co m

    ArrayList<String> defaultHeaders = new ArrayList<String>();

    for (String cName : allExpandedColumnHeaders) {
        if (cName.endsWith("IDSEQ") || cName.startsWith("CD ")
                || cName.startsWith("Conceptual Domain")) { /* skip */
        } else {
            System.out.println("cName = " + cName);
            defaultHeaders.add(cName);
        }
    }
    columns = defaultHeaders.toArray(new String[defaultHeaders.size()]);

    int[] colIndices = new int[columns.length];
    for (int i = 0; i < columns.length; i++) {
        String colName = columns[i];
        if (columnHeaders.indexOf(colName) < 0) {
            String tempType = arrayColumnTypes.get(colName);
            int temp = columnTypes.indexOf(tempType);
            colIndices[i] = temp;
        } else {
            int temp = columnHeaders.indexOf(colName);
            colIndices[i] = temp;
        }
    }

    Workbook wb = new HSSFWorkbook();

    Sheet sheet = wb.createSheet(sheetName);
    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle boldCellStyle = wb.createCellStyle();
    boldCellStyle.setFont(font);
    boldCellStyle.setAlignment(CellStyle.ALIGN_GENERAL);

    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    String temp;
    for (int i = 0; i < columns.length; i++) {
        Cell cell = headerRow.createCell(i);
        temp = columns[i];
        cell.setCellValue(temp);
        cell.setCellStyle(boldCellStyle);
    }

    // freeze the first row
    sheet.createFreezePane(0, 1);

    Row row = null;
    Cell cell;
    int rownum = 1;
    int bump = 0;
    int i = 0;
    try {
        System.out.println("Total CDEs to download [" + allRows.size() + "]");
        for (i = 0; i < allRows.size(); i++, rownum++) {
            // Check if row already exists
            int maxBump = 0;
            if (sheet.getRow(rownum + bump) == null) {
                row = sheet.createRow(rownum + bump);
            }

            if (allRows.get(i) == null)
                continue;

            for (int j = 0; j < colIndices.length; j++) {

                cell = row.createCell(j);
                String currentType = columnTypes.get(colIndices[j]);
                if (currentType.endsWith("_T")) {
                    // Deal with CS/CSI
                    String[] originalArrColNames = typeMap.get(currentType).get(0);

                    // Find current column in original data

                    int originalColumnIndex = -1;
                    for (int a = 0; a < originalArrColNames.length; a++) {
                        if (columns[j].equals(originalArrColNames[a])) {
                            originalColumnIndex = a;
                            break;
                        }
                    }
                    // ArrayList<HashMap<String,ArrayList<String[]>>>
                    // arrayData1 =
                    // (ArrayList<HashMap<String,ArrayList<String[]>>>)arrayData;
                    HashMap<String, List<String[]>> typeArrayData = arrayData.get(i);
                    ArrayList<String[]> rowArrayData = (ArrayList<String[]>) typeArrayData.get(currentType);

                    if (rowArrayData != null) {
                        int tempBump = 0;
                        for (int nestedRowIndex = 0; nestedRowIndex < rowArrayData.size(); nestedRowIndex++) {

                            String[] nestedData = rowArrayData.get(nestedRowIndex);
                            String data = "";
                            if (currentType.contains("DERIVED")) {
                                // Derived data element is special double
                                // nested, needs to be modified to be more
                                // general.

                                // General DDE information is in the first 4
                                // columns, but contained in the first row
                                // of the Row Array Data
                                if (originalColumnIndex < 5) {
                                    if (nestedRowIndex == 0)
                                        data = (originalColumnIndex > 0) ? nestedData[originalColumnIndex]
                                                : nestedData[originalColumnIndex + 1];
                                } else {
                                    if (nestedRowIndex + 1 < rowArrayData.size()) {
                                        data = rowArrayData.get(nestedRowIndex + 1)[originalColumnIndex - 5];
                                    }
                                }

                            } else
                                data = nestedData[originalColumnIndex];
                            logger.debug(
                                    "at line 828 of TestSpreadsheetDownload.java*****" + data + currentType);
                            if (currentType.contains("VALID_VALUE")) {
                                data = AdministeredItemUtil.truncateTime(data);
                            }
                            cell.setCellValue(data);

                            tempBump++;

                            if (nestedRowIndex < rowArrayData.size() - 1) {
                                row = sheet.getRow(rownum + bump + tempBump);
                                if (row == null) {
                                    if (rownum + bump + tempBump >= MAX_ROWS) {
                                        sheet = wb.createSheet(sheetName + "_" + sheetNum);
                                        sheetNum++;
                                        rownum = 1;
                                        bump = 0;
                                        tempBump = 0;
                                    }
                                    row = sheet.createRow(rownum + bump + tempBump);
                                }

                                cell = row.createCell(j);

                            } else {
                                // Go back to top row
                                row = sheet.getRow(rownum + bump);
                                if (tempBump > maxBump)
                                    maxBump = tempBump;
                            }
                        }
                    }
                } else {
                    temp = allRows.get(i)[colIndices[j]];
                    logger.debug("at line 866 of TestSpreadsheetDownload.java*****" + temp + currentType);
                    if (currentType.equalsIgnoreCase("Date")) {
                        temp = AdministeredItemUtil.truncateTime(temp);
                    }
                    cell.setCellValue(temp);
                }

            }

            bump = bump + maxBump;

            if (fillIn != null && (fillIn.equals("true") || fillIn.equals("yes") && bump > 0)) {
                sheet = fillInBump(sheet, i, rownum, bump, allRows, columnTypes, colIndices);
                rownum = rownum + bump;
                bump = 0;
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

    try {
        // Please specify the path below if needed, otherwise it will create in the root/dir where this test class is run
        fileOutputStream = new FileOutputStream("Test_Excel.xls");
        wb.write(fileOutputStream);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        /**
         * Close the fileOutputStream.
         */
        try {
            if (fileOutputStream != null) {
                fileOutputStream.close();
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
}

From source file:gov.nih.nci.cadsr.cdecurate.tool.CustomDownloadServlet.java

License:BSD License

private void createDownloadColumns(ArrayList<String[]> allRows) {
    final int MAX_ROWS = 65000;

    String sheetName = "Custom Download";
    int sheetNum = 1;

    String colString = (String) this.m_classReq.getParameter("cdlColumns");
    String fillIn = (String) this.m_classReq.getParameter("fillIn");

    ArrayList<String> allHeaders = (ArrayList<String>) m_classReq.getSession().getAttribute("headers");
    ArrayList<String> allExpandedHeaders = (ArrayList<String>) m_classReq.getSession()
            .getAttribute("allExpandedHeaders");
    ArrayList<String> allTypes = (ArrayList<String>) m_classReq.getSession().getAttribute("types");
    HashMap<String, ArrayList<String[]>> typeMap = (HashMap<String, ArrayList<String[]>>) m_classReq
            .getSession().getAttribute("typeMap");
    ArrayList<HashMap<String, ArrayList<String[]>>> arrayData = (ArrayList<HashMap<String, ArrayList<String[]>>>) m_classReq
            .getSession().getAttribute("arrayData");
    HashMap<String, String> arrayColumnTypes = (HashMap<String, String>) m_classReq.getSession()
            .getAttribute("arrayColumnTypes");

    String[] columns = null;/*  ww  w .  j a  va 2s.  c  o  m*/
    if (colString != null && !colString.trim().equals("")) {
        columns = colString.split(",");
    } else {
        ArrayList<String> defaultHeaders = new ArrayList<String>();

        for (String cName : allExpandedHeaders) {
            if (cName.endsWith("IDSEQ") || cName.startsWith("CD ") || cName.startsWith("Conceptual Domain")) {
                /*skip*/ } else {
                System.out.println("cName = " + cName);
                defaultHeaders.add(cName);
            }
        }
        columns = defaultHeaders.toArray(new String[defaultHeaders.size()]);

    }

    int[] colIndices = new int[columns.length];
    for (int i = 0; i < columns.length; i++) {
        String colName = columns[i];
        if (allHeaders.indexOf(colName) < 0) {
            String tempType = arrayColumnTypes.get(colName);
            int temp = allTypes.indexOf(tempType);
            colIndices[i] = temp;
        } else {
            int temp = allHeaders.indexOf(colName);
            colIndices[i] = temp;
        }
    }

    Workbook wb = new HSSFWorkbook();

    Sheet sheet = wb.createSheet(sheetName);
    Font font = wb.createFont(); //GF30779
    font.setBoldweight(Font.BOLDWEIGHT_BOLD); //GF30779
    CellStyle boldCellStyle = wb.createCellStyle(); //GF30779
    boldCellStyle.setFont(font); //GF30779
    boldCellStyle.setAlignment(CellStyle.ALIGN_GENERAL); //GF30779

    Row headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(12.75f);
    String temp;
    for (int i = 0; i < columns.length; i++) {
        Cell cell = headerRow.createCell(i);
        temp = columns[i];
        cell.setCellValue(temp);
        cell.setCellStyle(boldCellStyle); //GF30779
    }

    //freeze the first row
    sheet.createFreezePane(0, 1);

    Row row = null;
    Cell cell;
    int rownum = 1;
    int bump = 0;
    boolean fillRow = false;
    int i = 0;
    long startTime = System.currentTimeMillis();
    try {
        System.out.println("Total CDEs to download [" + allRows.size() + "]");
        for (i = 0; i < allRows.size(); i++, rownum++) {
            //Check if row already exists
            int maxBump = 0;
            if (sheet.getRow(rownum + bump) == null) {
                row = sheet.createRow(rownum + bump);
            }

            if (allRows.get(i) == null)
                continue;

            for (int j = 0; j < colIndices.length; j++) {

                cell = row.createCell(j);
                String currentType = allTypes.get(colIndices[j]);
                if (currentType.endsWith("_T")) {
                    //Deal with CS/CSI
                    String[] originalArrColNames = typeMap.get(currentType).get(0);

                    //Find current column in original data

                    int originalColumnIndex = -1;
                    for (int a = 0; a < originalArrColNames.length; a++) {
                        if (columns[j].equals(originalArrColNames[a])) {
                            originalColumnIndex = a;
                            break;
                        }
                    }

                    HashMap<String, ArrayList<String[]>> typeArrayData = arrayData.get(i);
                    ArrayList<String[]> rowArrayData = typeArrayData.get(currentType);

                    if (rowArrayData != null) {
                        int tempBump = 0;
                        for (int nestedRowIndex = 0; nestedRowIndex < rowArrayData.size(); nestedRowIndex++) {

                            String[] nestedData = rowArrayData.get(nestedRowIndex);
                            String data = "";
                            if (currentType.contains("DERIVED")) {
                                //Derived data element is special double nested, needs to be modified to be more general.

                                //General DDE information is in the first 4 columns, but contained in the first row of the Row Array Data
                                if (originalColumnIndex < 5) {
                                    if (nestedRowIndex == 0)
                                        data = (originalColumnIndex > 0) ? nestedData[originalColumnIndex]
                                                : nestedData[originalColumnIndex + 1]; //This skips the 2nd entry, description, which is not to be shown.
                                } else {
                                    if (nestedRowIndex + 1 < rowArrayData.size()) {
                                        data = rowArrayData.get(nestedRowIndex + 1)[originalColumnIndex - 5];
                                    }
                                }

                            } else
                                data = nestedData[originalColumnIndex];
                            logger.debug("at line 960 of CustomDownloadServlet.java*****" + data + currentType);
                            if (currentType.contains("VALID_VALUE")) { //GF30779
                                data = AdministeredItemUtil.truncateTime(data);
                            }
                            cell.setCellValue(data);

                            tempBump++;

                            if (nestedRowIndex < rowArrayData.size() - 1) {
                                row = sheet.getRow(rownum + bump + tempBump);
                                if (row == null) {
                                    if (rownum + bump + tempBump >= MAX_ROWS) {
                                        sheet = wb.createSheet(sheetName + "_" + sheetNum);
                                        sheetNum++;
                                        rownum = 1;
                                        bump = 0;
                                        tempBump = 0;
                                    }
                                    row = sheet.createRow(rownum + bump + tempBump);
                                }

                                cell = row.createCell(j);

                            } else {
                                //Go back to top row 
                                row = sheet.getRow(rownum + bump);
                                if (tempBump > maxBump)
                                    maxBump = tempBump;
                            }
                        }
                    }
                } else {
                    temp = allRows.get(i)[colIndices[j]];
                    logger.debug("at line 993 of CustomDownloadServlet.java*****" + temp + currentType);
                    if (currentType.equalsIgnoreCase("Date")) { //GF30779
                        temp = AdministeredItemUtil.truncateTime(temp);
                    }
                    cell.setCellValue(temp);
                }

            }

            bump = bump + maxBump;

            if (fillIn != null && (fillIn.equals("true") || fillIn.equals("yes") && bump > 0)) {
                sheet = fillInBump(sheet, i, rownum, bump, allRows, allTypes, colIndices);
                rownum = rownum + bump;
                bump = 0;
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

    //      sheet.setZoom(3, 4); //GF30779

    // Write the output to response stream.
    try {
        m_classRes.setContentType("application/vnd.ms-excel");
        m_classRes.setHeader("Content-Disposition", "attachment; filename=\"customDownload.xls\"");

        OutputStream out = m_classRes.getOutputStream();
        wb.write(out);
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:gov.nih.nci.evs.app.neopl.XLStoXLSX.java

License:Open Source License

/**
 * @param args//  w  ww. j  a  va 2 s . c  o m
 * @throws InvalidFormatException
 * @throws IOException
 */

public static void run(String inputfile, String outputfile) throws IOException {
    InputStream in = new BufferedInputStream(new FileInputStream(inputfile));
    try {
        Workbook wbIn = new HSSFWorkbook(in);
        File outFn = new File(outputfile);
        if (outFn.exists()) {
            outFn.delete();
        }

        Workbook wbOut = new XSSFWorkbook();
        int sheetCnt = wbIn.getNumberOfSheets();
        for (int i = 0; i < sheetCnt; i++) {
            Sheet sIn = wbIn.getSheetAt(0);
            Sheet sOut = wbOut.createSheet(sIn.getSheetName());
            Iterator<Row> rowIt = sIn.rowIterator();
            while (rowIt.hasNext()) {
                Row rowIn = rowIt.next();
                Row rowOut = sOut.createRow(rowIn.getRowNum());

                Iterator<Cell> cellIt = rowIn.cellIterator();
                while (cellIt.hasNext()) {
                    Cell cellIn = cellIt.next();
                    Cell cellOut = rowOut.createCell(cellIn.getColumnIndex(), cellIn.getCellType());

                    switch (cellIn.getCellType()) {
                    case Cell.CELL_TYPE_BLANK:
                        break;

                    case Cell.CELL_TYPE_BOOLEAN:
                        cellOut.setCellValue(cellIn.getBooleanCellValue());
                        break;

                    case Cell.CELL_TYPE_ERROR:
                        cellOut.setCellValue(cellIn.getErrorCellValue());
                        break;

                    case Cell.CELL_TYPE_FORMULA:
                        cellOut.setCellFormula(cellIn.getCellFormula());
                        break;

                    case Cell.CELL_TYPE_NUMERIC:
                        cellOut.setCellValue(cellIn.getNumericCellValue());
                        break;

                    case Cell.CELL_TYPE_STRING:
                        cellOut.setCellValue(cellIn.getStringCellValue());
                        break;
                    }

                    {
                        CellStyle styleIn = cellIn.getCellStyle();
                        CellStyle styleOut = cellOut.getCellStyle();
                        styleOut.setDataFormat(styleIn.getDataFormat());
                    }
                    cellOut.setCellComment(cellIn.getCellComment());

                    // HSSFCellStyle cannot be cast to XSSFCellStyle
                    // cellOut.setCellStyle(cellIn.getCellStyle());
                }
            }
        }
        OutputStream out = new BufferedOutputStream(new FileOutputStream(outFn));
        try {
            wbOut.write(out);
        } finally {
            out.close();
        }
    } finally {
        in.close();
    }
}

From source file:gov.nih.nci.evs.app.neopl.XLSXMetadataUtils.java

License:Open Source License

public static boolean freezeRow(String filename, int sheetNumber, int rowNum) {
    FileOutputStream fileOut = null;
    boolean status = false;
    try {/* ww w .  ja v a 2  s. co m*/
        InputStream inp = new FileInputStream(filename);
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(sheetNumber);
        sheet.createFreezePane(0, rowNum); // this will freeze first rowNum rows
        fileOut = new FileOutputStream(filename);
        wb.write(fileOut);
        status = true;
        System.out.println("File modified " + filename);

    } catch (Exception ex) {
        //ex.printStackTrace();
        System.out.println("ERROR: freezeRow " + filename);

    } finally {
        try {
            fileOut.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    return status;
}