Example usage for org.apache.poi.ss.usermodel Row getRowNum

List of usage examples for org.apache.poi.ss.usermodel Row getRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getRowNum

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

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  . ja v  a  2  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.Factura.java

License:Open Source License

public String factura2XLSX() {
    String archivo = "Factura" + (new Date()).getTime() + ".xlsx";
    try {// w ww.j  a v a 2 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.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 {/*www  . jav  a 2 s.c  o  m*/
        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 {/*ww w  . ja v a 2s .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.nih.nci.evs.app.neopl.XLStoXLSX.java

License:Open Source License

/**
 * @param args//from  w  w  w  .  jav a2s  .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:hu.webhejj.commons.io.table.excel.ExcelRowValueConverter.java

License:Apache License

private CellValue getCellValue(Row row, Cell cell, int column) {

    CellValue cellValue = null;/*from   ww  w  .java  2s.  c  om*/
    try {
        cellValue = evaluator.evaluate(cell);
    } catch (RuntimeException e) {
        if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            switch (cell.getCachedFormulaResultType()) {
            case Cell.CELL_TYPE_NUMERIC:
                cellValue = new CellValue(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                cellValue = new CellValue(cell.getStringCellValue());
                break;
            default:
                System.err.format("  Cell[%d,%d] unknown cached formula type %s\n", row.getRowNum(), column,
                        cell.getCachedFormulaResultType());
            }
        }
    }
    return cellValue;
}

From source file:ik1004labb5.DAOHundExcel.java

@Override
public void delete(int id) {
    XSSFWorkbook workbook = getExcelWorkbook();
    XSSFSheet worksheet = workbook.getSheetAt(0);
    DataFormatter df = new DataFormatter();
    //Loopa igenom nollkolumnen fr att frska hitta en matchning p ID

    for (Row row : worksheet) {
        if (df.formatCellValue(row.getCell(0)).equalsIgnoreCase(Integer.toString(id))) {
            if (row.getRowNum() == worksheet.getLastRowNum()) {
                worksheet.removeRow(row);
                break;
            } else {
                worksheet.shiftRows(row.getRowNum() + 1, worksheet.getLastRowNum(), -1);
            }// w  w  w. j  a  v  a2 s.  c  o m
            break;
        }
    }
    saveToExcel(workbook);
}

From source file:in.expertsoftware.colorcheck.Reporting_Qtr_Verification.java

public ArrayList<ErrorModel> startReporting_QtrVerification(int SORQ_tokenLocation, int EORQ_tokenLocation,
        ArrayList<String> opeartion_standard_workingSectionList,
        ArrayList<String> financial_standard_workingSectionList, XSSFWorkbook workbook) {
    ArrayList<ErrorModel> errorModelList = new ArrayList<ErrorModel>();
    boolean operationCheck;
    boolean financialCheck;
    int first_Occurance_Of_Financial_Comparision = 0;
    XSSFSheet Sheet = workbook.getSheet("Reporting_Qtr");
    Iterator<Row> rowIterator = Sheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.iterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getColumnIndex() == 1) {
                String key = cell.getStringCellValue();
                if ((key.equalsIgnoreCase("Financial Comparison"))
                        && (first_Occurance_Of_Financial_Comparision == 0)) {
                    first_Occurance_Of_Financial_Comparision = row.getRowNum() + 1;
                }// w ww.j av a2  s . c  o  m
            }
        }
    }
    operationCheck = operation_Standard_Workingsection_Verification(SORQ_tokenLocation,
            first_Occurance_Of_Financial_Comparision, Sheet, opeartion_standard_workingSectionList,
            errorModelList, workbook);
    //financialCheck=financial_Standard_Workingsection_Verification(first_Occurance_Of_Financial_Comparision,EORQ_tokenLocation,Sheet,financial_standard_workingSectionList,errorModelList,workbook);                                

    //if return false that means no error.
    if (!operationCheck) {
        reporting_Qtr_operation_Standard_C_To_I_Column_Verification((SORQ_tokenLocation + 3),
                first_Occurance_Of_Financial_Comparision, errorModelList, Sheet, workbook);
    }
    /*if(!financialCheck)
    {
              
    }*/
    return errorModelList;
}

From source file:in.expertsoftware.colorcheck.Reporting_Year_Verification.java

public ArrayList<ErrorModel> startReporting_YearVerification(int SORY_tokenLocation, int EORY_tokenLocation,
        ArrayList<String> opeartion_standard_workingSectionList,
        ArrayList<String> financial_standard_workingSectionList, XSSFWorkbook workbook) {
    ArrayList<ErrorModel> errorModelList = new ArrayList<ErrorModel>();
    boolean operationCheck;
    boolean financialCheck;
    int first_Occurance_Of_Financial_Comparision = 0;
    XSSFSheet Sheet = workbook.getSheet("Reporting_Year");
    Iterator<Row> rowIterator = Sheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.iterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getColumnIndex() == 1) {
                String key = cell.getStringCellValue();
                if ((key.equalsIgnoreCase("Financial Comparison"))
                        && (first_Occurance_Of_Financial_Comparision == 0)) {
                    first_Occurance_Of_Financial_Comparision = row.getRowNum() + 1;
                }/*ww  w. j  a va 2s . c o m*/
            }
        }
    }
    operationCheck = operation_Standard_Workingsection_Verification(SORY_tokenLocation,
            first_Occurance_Of_Financial_Comparision, Sheet, opeartion_standard_workingSectionList,
            errorModelList, workbook);
    //financialCheck=financial_Standard_Workingsection_Verification(first_Occurance_Of_Financial_Comparision,EORY_tokenLocation,Sheet,financial_standard_workingSectionList,errorModelList,workbook);                                

    //if return false that means no error.
    if (!operationCheck) {
        reporting_Year_operation_Standard_C_To_I_Column_Verification((SORY_tokenLocation + 3),
                first_Occurance_Of_Financial_Comparision, errorModelList, Sheet, workbook);
    }
    /*if(!financialCheck)
    {
              
    }*/
    return errorModelList;
}

From source file:invoiceapplication.CopyRowOriginal.java

public static void copyRow(Sheet worksheet, int sourceRowNum, int destRowNum) {
    // Get the source / new row
    Row newRow = worksheet.getRow(destRowNum);
    Row sourceRow = worksheet.getRow(sourceRowNum);

    // If the row exists in destination, push down all rows by 1 else create a new row
    if (newRow != null) {
        worksheet.shiftRows(newRow.getRowNum(), worksheet.getLastRowNum(), 1, true, true);
    } else {/*from   www .  j av  a2  s  . co m*/
        newRow = worksheet.createRow(destRowNum);
    }
    copyAnyMergedRegions(worksheet, sourceRow, newRow);
    // Loops through source column to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        //Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell = newRow.createCell(i);

        // if the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Use old cell style
        newCell.setCellStyle(oldCell.getCellStyle());

        // If there is a cell comment, copy
        if (newCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellValue(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        }
    }
}