Example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. getRow

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow.

Prototype

@Override
public XSSFRow getRow(int rownum)

Source Link

Document

Returns the logical row ( 0-based).

Usage

From source file:localization.excel.java

public static void convert(String filePath) {
    Vector<String> zFile;
    if (filePath.endsWith(".zip")) {
        zFile = readzipfile(filePath);/* www . j  ava 2  s . c  o m*/
        for (String s : zFile) {
            if (s.endsWith(".xlsx")) {
                //System.out.println(s);
                convert(s);
            }
        }
    } else if (!filePath.endsWith(".xlsx")) {
        return;
    } else {
        try {
            FileInputStream file = new FileInputStream(new File(filePath));
            System.out.println(filePath);
            //Get the workbook instance for XLS file 
            XSSFWorkbook workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(0);
            XSSFRow row;
            XSSFCell cell;
            rowNumber = sheet.getPhysicalNumberOfRows();
            try {
                for (int i = 0; i < rowNumber; i++) {
                    row = sheet.getRow(i);
                    if (row != null) {
                        int columnNum = row.getPhysicalNumberOfCells();
                        //System.out.println(columnNum);
                        for (int j = 0; j < columnNum; j++) {
                            cell = row.getCell(j);

                            if (j == 0) {
                                String name = cell.getRichStringCellValue().getString();
                                if (name.equalsIgnoreCase("Esri")) {
                                    langNumber++;
                                }
                                //System.out.println(name);
                            }
                        }
                        if (i == 3) {
                            cell = row.getCell(30);
                            XSSFCellStyle cs = cell.getCellStyle();
                            cell = row.createCell(32);
                            cell.setCellValue("Additional Charge per language");
                            cell.setCellStyle(cs);
                        }
                    }
                }
            } catch (Exception e) {

            }
            System.out.println(langNumber);
            double total = Double.parseDouble(sheet.getRow(langNumber + 3).getCell(29).getRawValue());

            double subTotal = total / langNumber;
            DecimalFormat df = new DecimalFormat("#.000");
            for (int i = 0; i < langNumber; i++) {
                cell = sheet.getRow(i + 4).createCell(32);
                cell.setCellValue("$" + df.format(subTotal));
            }

            file.close();
            FileOutputStream outFile = new FileOutputStream(filePath);
            workbook.write(outFile);
            outFile.close();
            rowNumber = 0;
            langNumber = 0;
            System.out.println("Done");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

From source file:logic.Export.java

public boolean convertXls()
        throws IOException, FileNotFoundException, IllegalArgumentException, ParseException {
    FileInputStream tamplateFile = new FileInputStream(templatePath);
    XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile);

    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##"));
    double hours = 0.0;
    NumberFormat format = NumberFormat.getInstance(Locale.FRANCE);
    Number number;//from   w w  w .  j  av a 2  s. co  m
    XSSFSheet sheet;
    XSSFSheet sheet2;
    Cell cell = null;
    ConvertData cd = new ConvertData();
    for (int i = 0; i < cd.getSheetnames().size(); i++) {
        sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i));
        sheet = workbook.getSheetAt(i + 1);
        //formate sheets
        sheet.getPrintSetup().setLandscape(true);
        sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

        cell = sheet.getRow(0).getCell(1);
        cell.setCellValue(cd.sheetnames.get(i));
        ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i));
        //setPrintArea 
        workbook.setPrintArea(i + 1, //sheet index
                0, //start column Spalte
                6, //end column
                0, //start row zeile
                convert.size() + 8 //end row
        );
        for (int Row = 0; Row < convert.size(); Row++) {
            for (int Cell = 0; Cell < convert.get(Row).length; Cell++) {
                cell = sheet.getRow(9 + Row).getCell(Cell);
                if (Cell == 3) {
                    if ("true".equals(convert.get(Row)[Cell])) {
                        XSSFCellStyle style1 = workbook.createCellStyle();
                        style1 = (XSSFCellStyle) cell.getCellStyle();
                        style1 = (XSSFCellStyle) style1.clone();
                        style1.setFillBackgroundColor(HSSFColor.RED.index);
                        style1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
                        XSSFColor myColor = new XSSFColor(Color.RED);
                        style1.setFillForegroundColor(myColor);
                        sheet.getRow(9 + Row).getCell(6).setCellStyle(style1);
                    }
                } else {
                    cell.setCellValue(convert.get(Row)[Cell]);
                }
            }
        }
    }

    workbook.removeSheetAt(0);
    tamplateFile.close();
    File exportFile = newPath.getSelectedFile();
    if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) {

    } else {
        exportFile = new File(exportFile.getParentFile(),
                FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx");
    }

    FileOutputStream outFile = new FileOutputStream(exportFile);
    workbook.write(outFile);
    outFile.close();
    tamplateFile.close();
    return true;

}

From source file:logic.Export.java

public boolean convertXls2()
        throws IOException, FileNotFoundException, IllegalArgumentException, ParseException {
    FileInputStream tamplateFile = new FileInputStream(templatePath);
    XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile);

    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##"));
    double hours = 0.0;
    NumberFormat format = NumberFormat.getInstance(Locale.FRANCE);
    Number number;//from w  w w  .  j  a v  a  2s .c om
    XSSFSheet sheet;
    XSSFSheet sheet2;
    Cell cell = null;
    ConvertData cd = new ConvertData();
    for (int i = 0; i < cd.getSheetnames().size(); i++) {
        sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i));
        sheet = workbook.getSheetAt(i + 1);
        cell = sheet.getRow(0).getCell(1);
        cell.setCellValue(cd.sheetnames.get(i));
        ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i));
        for (int Row = 0; Row < convert.size(); Row++) {
            for (int Cell = 0; Cell < convert.get(Row).length; Cell++) {
                cell = sheet.getRow(9 + Row).getCell(Cell + 1);
                String name;
                switch (Cell) {
                case 3:
                    name = convert.get(Row)[Cell];
                    int parseInt = Integer.parseInt(name);
                    cell.setCellValue(parseInt);
                    cell.setCellType(CellType.NUMERIC);
                    break;
                case 4:
                    number = format.parse(convert.get(Row)[Cell]);
                    cell.setCellValue(number.doubleValue());
                    //  cell.setCellStyle(cellStyle);
                    cell.setCellType(CellType.NUMERIC);

                    break;
                default:
                    cell.setCellValue(convert.get(Row)[Cell]);
                    break;
                }
            }
        }

        for (String[] sa : convert) {
            number = format.parse(sa[4]);
            hours = hours + number.doubleValue();
        }
        cell = sheet.getRow(6).getCell(5);
        cell.setCellValue(hours);
        cell = sheet.getRow(2).getCell(8);
        XSSFCell cellHourlyRate = sheet.getRow(1).getCell(8);
        double numericCellValue = cellHourlyRate.getNumericCellValue();
        cell.setCellValue(hours * numericCellValue);
    }
    workbook.removeSheetAt(0);
    tamplateFile.close();
    File exportFile = newPath.getSelectedFile();
    if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) {

    } else {
        exportFile = new File(exportFile.getParentFile(),
                FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx");
    }

    FileOutputStream outFile = new FileOutputStream(exportFile);
    workbook.write(outFile);
    outFile.close();
    tamplateFile.close();
    return true;

}

From source file:Logica.LLeerExcel.java

public static void LLeerExcel(File fileName) {

    try {//from w  w w .ja  v a 2s . co m
        InputStream inp = new FileInputStream(fileName);

        XSSFWorkbook wb = new XSSFWorkbook(inp);
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = sheet.getRow(13);
        Cell cell = row.getCell(13);

        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue("x");

        FileOutputStream fileOut = new FileOutputStream(
                "C:\\Users\\CLAUDIA\\Documents\\porteria\\formato.xlsx");
        wb.write(fileOut);

        fileOut.close();
    } catch (Exception e) {
        System.out.println(e);
    }
}

From source file:Logica.LLeerExcel.java

public static void LLeerExcel1(File fileName) {

    try {/*from w  ww.ja  v  a 2s .  co  m*/
        InputStream inp = new FileInputStream(fileName);

        XSSFWorkbook wb = new XSSFWorkbook(inp);
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = sheet.getRow(2);
        Cell cell = row.getCell(13);

        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue("x");

        FileOutputStream fileOut = new FileOutputStream(
                "C:\\Users\\CLAUDIA\\Documents\\porteria\\formato.xlsx");
        wb.write(fileOut);

        fileOut.close();
    } catch (Exception e) {
        System.out.println(e);
    }
}

From source file:Logica.LLeerExcel1.java

public LLeerExcel1(File fileName) {

    try {//from ww  w .j  a  va2s  .  co  m
        InputStream inp = new FileInputStream(fileName);

        XSSFWorkbook wb = new XSSFWorkbook(inp);
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row = sheet.getRow(12);
        Cell cell = row.getCell(14);

        cell.setCellType(Cell.CELL_TYPE_STRING);
        cell.setCellValue("a");

        FileOutputStream fileOut = new FileOutputStream(
                "C:\\Users\\CLAUDIA\\Documents\\porteria\\formato.xlsx");
        wb.write(fileOut);
        fileOut.close();
    } catch (Exception e) {
        System.out.println(e);
    }
}

From source file:Logica.LListadoAsistencia.java

public static void diaActual() throws IOException {
    File fileName = new File("C:\\Users\\CLAUDIA\\Documents\\porteria\\formato.xlsx");
    Date fecha = new Date();

    //System.out.print(fecha);

    Calendar cal1 = Calendar.getInstance();
    int dia = cal1.get(Calendar.DAY_OF_MONTH);

    ArrayList<LAprendiz> aprendices = new ArrayList<>();
    ResultSet rs11 = consultarUsuaFicha("901620");

    try {//ww  w.ja v a2 s . c  om
        while (rs11.next()) {
            LAprendiz aprendiz = new LAprendiz(rs11.getString("documento"), rs11.getString("nombres"),
                    rs11.getString("apellido"));
            aprendices.add(aprendiz);
        }
    } catch (SQLException ex) {
        Logger.getLogger(LListadoAsistencia.class.getName()).log(Level.SEVERE, null, ex);
    }

    for (int i = 1; i <= dia; i++) {

        for (LAprendiz aprendiz : aprendices) {

            Statement st;
            ResultSet rs;
            try {
                st = Conexion.getConect().createStatement();
                rs = st.executeQuery(
                        "select c1.suma_e,c2.suma_s, SEC_TO_TIME(TIMESTAMPDIFF(SECOND,c1.suma_e,c2.suma_s)) AS TIEMPO_ADENTRO from (select  sec_to_time(sum(time_to_sec(hora_ingreso))) as suma_e from ingreso_salida_usu where estado='adentro' and fecha_ingreso='2016/05/30' and documento='"
                                + aprendiz.getDocumento()
                                + "')  AS c1, (select sec_to_time(sum(time_to_sec(hora_ingreso))) as suma_s From ingreso_salida_usu where estado='afuera' and fecha_ingreso='2016/05/30' and documento='"
                                + aprendiz.getDocumento() + "' )as c2 ");

                while (rs.next()) {

                    String tiempoAdentro = (rs.getString("TIEMPO_ADENTRO"));
                    String[] ta = tiempoAdentro.split(":");
                    int tiempoAdentInt = Integer.parseInt(ta[0]);

                    if (tiempoAdentInt >= 3) {

                        InputStream inp;
                        try {
                            inp = new FileInputStream(fileName);
                            XSSFWorkbook wb = new XSSFWorkbook(inp);
                            XSSFSheet sheet = wb.getSheetAt(0);
                            XSSFRow row = sheet.getRow(12);
                            Cell cell = row.getCell(14);

                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue("a");

                        } catch (FileNotFoundException ex) {
                            Logger.getLogger(LListadoAsistencia.class.getName()).log(Level.SEVERE, null, ex);
                        }

                        //Logica.LLeerExcel.LLeerExcel(fileName);

                        // escriba en el archivo la a

                        // hola lizeth1234

                        System.out.print("asistio" + " ");

                    } else {

                        InputStream inp;
                        try {
                            inp = new FileInputStream(fileName);
                            XSSFWorkbook wb = new XSSFWorkbook(inp);
                            XSSFSheet sheet = wb.getSheetAt(0);
                            XSSFRow row = sheet.getRow(12);
                            Cell cell = row.getCell(14);

                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue("x");

                        } catch (FileNotFoundException ex) {
                            Logger.getLogger(LListadoAsistencia.class.getName()).log(Level.SEVERE, null, ex);
                        }
                        //Logica.LLeerExcel.LLeerExcel1(fileName);

                        //escriba en el archivo la x
                        System.out.print("no asistio" + " ");
                    }

                    System.out.print(tiempoAdentInt);

                    System.out.println(" " + aprendiz.getDocumento() + " " + aprendiz.getNombres() + " "
                            + aprendiz.getApellidos());

                }
            } catch (SQLException ex) {
                Logger.getLogger(LListadoAsistencia.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}

From source file:Logica.L_Exel.java

public String SobreExportar(File archivo, JTable jTable1, String drct) {
    String a = "revisr";
    try {// w  w  w.  j  av  a  2 s.co m
        FileInputStream entrada = new FileInputStream(new File(drct));
        XSSFWorkbook xlsx = new XSSFWorkbook(entrada);
        XSSFSheet hoja = xlsx.getSheetAt(0);
        Row fila = null;
        Cell celda = null;
        try {
            fila = hoja.getRow(4);
            celda = fila.createCell(3);
            celda.setCellValue(1);
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }
        XSSFFormulaEvaluator.evaluateAllFormulaCells(xlsx);
        entrada.close();

        FileOutputStream sld = new FileOutputStream(new File(drct));
        xlsx.write(sld);
        sld.close();
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, e);
    }
    return a;
}

From source file:Logica.L_POIExcel.java

public void Exportar(JTable Tbl_OT_Eventos_MostrandoExportar) throws Exception {
    JFileChooser selector = new JFileChooser();
    selector.setFileFilter(new FileNameExtensionFilter("Excel 2010 or Superior", "xlsx"));
    //selector.setFileFilter(new FileNameExtensionFilter("Excel with Macrs", "xlsm"));
    int numfila = Tbl_OT_Eventos_MostrandoExportar.getRowCount();
    int numcolumn = Tbl_OT_Eventos_MostrandoExportar.getColumnCount();

    String directorio = "";
    String extension = "";

    boolean seleccion = false;

    int resultado = selector.showOpenDialog(null);

    switch (resultado) {
    case JFileChooser.APPROVE_OPTION:
        directorio = selector.getSelectedFile().getPath();

        int i = directorio.lastIndexOf('.');
        if (i >= 0) {
            extension = directorio.substring(i + 1);
        }/*from   w w  w  . j  a  v a2  s . c  o  m*/
        seleccion = true;

        JOptionPane.showMessageDialog(null, "Seleccionaste" + directorio);
        break;
    case JFileChooser.CANCEL_OPTION:
        seleccion = false;
        JOptionPane.showMessageDialog(null, "No seleccionaste un archivo");
        break;
    case JFileChooser.ERROR_OPTION:
        seleccion = false;
        JOptionPane.showMessageDialog(null, "Ocurreo un Error");
        break;
    default:
        break;
    }

    if (extension.equals("xlsx")) {
        FileInputStream entrada = new FileInputStream(new File(directorio));
        XSSFWorkbook xlsx = new XSSFWorkbook(entrada);
        XSSFSheet hoja = xlsx.getSheetAt(0);
        Row fila = null;
        Cell celda = null;
        try {
            //EN CASO CELDAS TENGAN ALGUN VALOR
            //PODEMOS MANEJAR DICHO VALOR O SOBREESCRIBIR SOBRE EL COMO EN EL SIGUIENTE CASO
            //fila = hoja.getRow(0);
            //celda = fila.getCell(0);
            //celda.setCellValue(1);

            //fila = hoja.getRow(7);
            //celda = fila.getCell(6);
            //celda.setCellValue(9);
            for (int i = -1; i < numfila; i++) {
                //esta linea posiciona donde se empezara a escribir en este caso en la fila 4
                //recuerda que tanto fila como columna inician su conteo con 0
                fila = hoja.getRow(i + 14);
                for (int j = 0; j < numcolumn; j++) {
                    celda = fila.getCell(j);
                    if (i == -1) {
                        celda.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getColumnName(j)));
                    } else {
                        celda.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getValueAt(i, j)));
                    }
                }
            }
        } catch (NullPointerException NPE) {
            //EN CSAO LAS CELDAS ESTE VACIAS
            //ESCRIBIMOS UN VALOR SOBRE ELLAS
            for (int i = -1; i < numfila; i++) {
                Row filaV = hoja.createRow(i + 14);
                for (int j = 0; j < numcolumn; j++) {
                    Cell celdaV = filaV.createCell(j);
                    if (i == -1) {
                        celdaV.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getColumnName(j)));
                    } else {
                        celdaV.setCellValue(String.valueOf(Tbl_OT_Eventos_MostrandoExportar.getValueAt(i, j)));
                    }
                }
            }
        }
        //EVALUA LAS FORMULAS DEL WORKBK
        XSSFFormulaEvaluator.evaluateAllFormulaCells(xlsx);

        entrada.close();

        //ABRIENDO ARCHIVO PARA ESCRITURA
        FileOutputStream salida = new FileOutputStream(new File(directorio));
        //ESCRIBIENDO DATOS, EN ESTE CASO DE LA TABLA
        xlsx.write(salida);

        salida.close();
    }
}

From source file:Logica.L_POIExcel.java

public void Exportar_To_Mcr() throws Exception {
    JFileChooser selector = new JFileChooser();
    selector.setFileFilter(new FileNameExtensionFilter("Excel 2010 or Superior", "xlsx"));
    //selector.setFileFilter(new FileNameExtensionFilter("Excel with Macrs", "xlsm"));        
    String sSql = "";
    String UPS_Monitoreados = "";

    sSql = "SELECT Count(EstadoMonitoreo) FROM upsinformacion_prb WHERE EstadoMonitoreo Like '%MON%'";

    try {/* ww  w .  j  a  v a  2s . c om*/
        Statement st = cn.createStatement();
        ResultSet rs = st.executeQuery(sSql);
        rs.next();
        UPS_Monitoreados = rs.getString("Count(EstadoMonitoreo)");
    } catch (Exception e) {
    }

    String directorio = "";
    String extension = "";

    boolean seleccion = false;

    int resultado = selector.showOpenDialog(null);

    switch (resultado) {
    case JFileChooser.APPROVE_OPTION:
        directorio = selector.getSelectedFile().getPath();

        int i = directorio.lastIndexOf('.');
        if (i >= 0) {
            extension = directorio.substring(i + 1);
        }
        seleccion = true;

        JOptionPane.showMessageDialog(null, "Seleccionaste" + directorio);
        break;
    case JFileChooser.CANCEL_OPTION:
        seleccion = false;
        JOptionPane.showMessageDialog(null, "No seleccionaste un archivo");
        break;
    case JFileChooser.ERROR_OPTION:
        seleccion = false;
        JOptionPane.showMessageDialog(null, "Ocurreo un Error");
        break;
    default:
        break;
    }
    if (extension.equals("xlsx")) {
        //Leer el archivo de Excel XLSX
        FileInputStream entrada = new FileInputStream(new File(directorio));
        //Acceso al libro de trabajo
        XSSFWorkbook xlsx = new XSSFWorkbook(entrada);
        //Acceso a la hoja de trabajo
        XSSFSheet hoja = xlsx.getSheetAt(0);
        //Declaracion de fila y celda
        Row fila = null;
        Cell celda = null;

        try {
            //Asignando a valores a celdas con valores                                       
            fila = hoja.getRow(2);

            celda = fila.getCell(8);
            celda.setCellValue(Integer.parseInt(UPS_Monitoreados));
        } catch (NullPointerException NPE) {
            //En caso de que las celdas esten vacias hay que crearlas
            fila = hoja.createRow(0);

            celda = fila.createCell(0);
            celda.setCellValue(2);
            celda = fila.createCell(1);
            celda.setCellValue(6);

            fila = hoja.createRow(1);

            celda = fila.createCell(0);
            celda.setCellValue(9);
            celda = fila.createCell(1);
            celda.setCellValue(3);
        }

        //Evaluando formulas de todo el libro de excel
        XSSFFormulaEvaluator.evaluateAllFormulaCells(xlsx);

        //Cerrando la entrada archivo
        entrada.close();

        //Abriendo archivo para escritura
        FileOutputStream salida = new FileOutputStream(new File(directorio));
        //write changes
        xlsx.write(salida);
        //close the stream
        salida.close();
    }
}