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

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

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. 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;
}