Example usage for org.apache.poi.hssf.usermodel HSSFSheet createRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. createRow

Introduction

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

Prototype

@Override
public HSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

From source file:android_connector.ConfigWindowController.java

/**
 * Speichert die Ergebnisse der Lufe als Excel-Tabelle.
 *
 * @author Quelle://from   w  w  w. j a v a 2 s .  c o  m
 * http://viralpatel.net/blogs/java-read-write-excel-file-apache-poi/
 * @param programmende True, wenn das Programm beendet werden soll, sonst
 * false.
 *
 */
public void printResults(boolean programmende) {
    /**
     * Zahl der Messtore.
     */
    int tore = Integer.parseInt(messtore.getText());
    /**
     * Gibt die gespeicherten Werte der DB an. Jedes Unterarray
     * reprsentiert einen Datensatz. Der zweite Index ist bei 0 die
     * Startnummer, 1 der Name, 2 die Kategorie, 3 Lauf 1, 4 Lauf 2.
     */
    String[][] werte = (new MySQLConnection(host.getText(), port.getText(), db.getText(), user.getText(),
            pw.getText(), null)).getAuswertung(starter, tore);
    //FileChooser einsetzen, um Speicherort zu ermitteln
    FileChooser fc = new FileChooser();
    //Titel anzeigen
    fc.setTitle("Speicherort fr die Auswertung:");
    //standardmig im Home-Verzeichnis starten
    fc.setInitialDirectory(new File(System.getProperty("user.home")));
    //Alle-Dateien-Filter entfernen
    fc.setSelectedExtensionFilter(null);
    //FileFilter fr Exceldateien hinzufgen
    //nur "alte" Excel-Dateien knnen geschrieben werden!
    fc.getExtensionFilters()
            .addAll(new FileChooser.ExtensionFilter("Microsoft Excel 1997-2003 Dokument (.xls)", "*.xls"));
    //Dateien einlesen
    File returnVal = fc.showSaveDialog(primaryStage);
    //prfen, ob Datei zurckgegeben --> eine gewhlt; muss aber nicht existieren
    if (returnVal != null) {
        /**
         * Reprsentation der Excel-Datei.
         */
        HSSFWorkbook workbook = new HSSFWorkbook();
        /**
         * Alle Kategorien, die angegeben sind.
         */
        List<String> kategorien = getKategorien();
        //alle Kategorien durchgehen, fr alle eine Mappe der DB fllen.
        for (String kategorie : kategorien) {
            /**
             * Reprsentiert die Mappe, in der die Daten landen.
             */
            HSSFSheet sheet;
            //Prfen, ob die Lnge des Kategorienamens zwischen 1 und 31 liegt --> sonst ungltig
            if (!kategorie.isEmpty() && kategorie.length() < 31) {
                //wenn ja: Kategoriename ist Mappenname
                sheet = workbook.createSheet(kategorie);
                //sonst: Standardname
            } else {
                sheet = workbook.createSheet("namenlose Kategorie");
            }

            Map<String, List<String>> data = new HashMap<String, List<String>>();
            List<String> label = new ArrayList<>();
            label.add("Startnummer");
            label.add("Name");
            label.add("Kategorie");
            label.add("reine Laufzeit- Lauf 1");
            label.add("Gesamtstrafen- Lauf 1");
            label.add("Laufzeit insgesamt- Lauf 1");
            for (int i = 0; i < tore; i++) {
                label.add("Strafe Tor " + (i + 1));
            }
            label.add("reine Laufzeit- Lauf 2");
            label.add("Gesamtstrafen- Lauf 2");
            label.add("Laufzeit insgesamt- Lauf 2");
            for (int i = 0; i < tore; i++) {
                label.add("Strafe Tor " + (i + 1));
            }
            data.put("0", label);

            for (int i = 0; i < werte.length; i++) {
                //data.put(""+(i+2), werte[i]);
                if (werte[i][2] != null) {
                    if (werte[i][2].equals(kategorie)) {
                        List<String> angaben = new ArrayList<>();
                        angaben.add(werte[i][0]);
                        angaben.add(werte[i][1]);
                        angaben.add(werte[i][2]);
                        String lauf1 = werte[i][3];
                        if (werte[i][3] != null && !werte[i][3].isEmpty()) {
                            angaben.addAll(extractList(lauf1));
                        }
                        if (werte[i][4] != null && !werte[i][4].isEmpty()) {
                            angaben.addAll(extractList(werte[i][4]));
                        }
                        data.put("" + (i + 1), angaben);
                    }
                }
            }
            Set<String> keyset = data.keySet();
            int rownum = 0;
            for (String key : keyset) {
                Row row = sheet.createRow(rownum++);
                List<String> objArr = data.get(key);
                int cellnum = 0;
                for (Object obj : objArr) {
                    Cell cell = row.createCell(cellnum++);
                    if (obj instanceof Date) {
                        cell.setCellValue((Date) obj);
                    } else if (obj instanceof Boolean) {
                        cell.setCellValue((Boolean) obj);
                    } else if (obj instanceof String) {
                        cell.setCellValue((String) obj);
                    } else if (obj instanceof Double) {
                        cell.setCellValue((Double) obj);
                    }
                }
            }
            for (int i = 0; i < 9 + 2 * tore; i++) {
                sheet.autoSizeColumn(i);
            }
        }
        try {
            FileOutputStream out = new FileOutputStream(returnVal);
            workbook.write(out);
            out.close();
            System.out.println("Excel written successfully..");
            out = new FileOutputStream(System.getProperty("user.home") + "/Kanu-s.a.M.-Notfall.xls");
            workbook.write(out);
            out.close();

        } catch (FileNotFoundException e) {
            MySQLConnection.staticExceptionDialog(e, "Schreibfehler",
                    "Auswertung konnte nicht geschrieben werden",
                    "Die Excel-Datei mit der Auswertung konnte nicht geschrieben werden. Bitte versuchen Sie es erneut!");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    if (programmende) {
        Alert alert = new Alert(AlertType.INFORMATION);
        alert.setTitle("Programmende");
        alert.setHeaderText("Der Programmablauf ist beendet.");
        alert.setContentText(
                "Das Programm hat seine Aufgabe erfllt und wird nun beendet. Vielen Dank fr die Benutzung des Softwaresystems!");
        alert.showAndWait();
        Platform.exit();
    }
}

From source file:at.htlpinkafeld.beans.AlleAbwesenheitenBean.java

/**
 * xls post processing/*w  ww. ja  v a  2  s.  c om*/
 *
 * @param document xls document
 */
public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);

    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);

        sheet.autoSizeColumn(i);
    }

    HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
    bottomRow.createCell(0)
            .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

}

From source file:at.htlpinkafeld.beans.JahresuebersichtBean.java

/**
 * post processes the XLS for creating/*from  w w w  .  j a va 2s  .com*/
 *
 * @param document xls-doc
 */
public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);

    sheet.shiftRows(0, sheet.getLastRowNum(), 2);

    HSSFRow topRow = sheet.createRow(0);

    topRow.createCell(0).setCellValue("Jahresbersicht - " + selectedYear.getYear());
    topRow.createCell(3).setCellValue("von " + selectedUser.getPersName());
    sheet.createRow(1).createCell(0).setCellValue(" ");

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

    HSSFRow header = sheet.getRow(2);
    HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
        HSSFCell cell = header.getCell(i);
        cell.setCellStyle(cellStyle);

        cell = footer.getCell(i);
        cell.setCellStyle(cellStyle);

        sheet.autoSizeColumn(i);
    }

    HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
    bottomRow.createCell(0)
            .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

}

From source file:at.htlpinkafeld.beans.UserDetailsBean.java

public void postProcessXLS(Object document) {
        HSSFWorkbook wb = (HSSFWorkbook) document;
        HSSFSheet sheet = wb.getSheetAt(0);

        sheet.shiftRows(0, sheet.getLastRowNum(), 2);

        HSSFRow topRow = sheet.createRow(0);

        topRow.createCell(0)//from   w  w w  .j a  va  2 s . co  m
                .setCellValue("Monatsbersicht - " + selectedDate.format(DateTimeFormatter.ofPattern("MM.yyyy")));
        topRow.createCell(7).setCellValue("von " + selectedUser);
        sheet.createRow(1).createCell(0).setCellValue(" ");

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));

        HSSFRow header = sheet.getRow(2);
        HSSFRow footer = sheet.getRow(sheet.getLastRowNum());

        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = header.getCell(i);
            cell.setCellStyle(cellStyle);

            cell = footer.getCell(i);
            cell.setCellStyle(cellStyle);

            sheet.autoSizeColumn(i);
        }

        HSSFRow bottomRow = sheet.createRow(sheet.getLastRowNum() + 2);
        bottomRow.createCell(0)
                .setCellValue("Stand: " + LocalDate.now().format(DateTimeFormatter.ofPattern("dd.MM.yyyy")));

    }

From source file:attandance.standalone.manager.AttandanceManager.java

private void outputAttandance(List<LateRecord> lates, List<AbsenceRecord> absences) {
    // webbookExcel  
    HSSFWorkbook wb = new HSSFWorkbook();
    // webbooksheet,Excelsheet  
    HSSFSheet sheet = wb.createSheet("");
    int width = ((int) (20 * 1.14388)) * 256;
    sheet.setColumnWidth(0, width);//from   w  ww  .  j  av a2 s .  c o  m
    sheet.setColumnWidth(1, width);
    sheet.setColumnWidth(2, width);
    sheet.setColumnWidth(3, width);
    // sheet0,??poiExcel?short  
    HSSFRow row = sheet.createRow((int) 0);
    // ?   
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?  

    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue("??");
    cell.setCellStyle(style);
    cell = row.createCell((short) 1);
    cell.setCellValue("");
    cell.setCellStyle(style);
    cell = row.createCell((short) 2);
    cell.setCellValue("?");
    cell.setCellStyle(style);
    cell = row.createCell((short) 3);
    cell.setCellValue("?");
    cell.setCellStyle(style);

    // ? ??   
    int i = 0;
    for (; i < lates.size(); i++) {
        row = sheet.createRow((int) i + 1);
        LateRecord lateStaff = lates.get(i);
        // ?  
        row.createCell((short) 0).setCellValue(lateStaff.getStaffName());
        row.createCell((short) 1).setCellValue(lateStaff.getCaculateDateString());
        cell = row.createCell((short) 2);
        cell.setCellValue(new SimpleDateFormat(DateHelper.DATE_FORMAT).format(lateStaff.getLateDate()));
        row.createCell((short) 3).setCellValue(lateStaff.getLateTimeDesc());
    }
    for (int j = 0; j < absences.size(); j++) {
        row = sheet.createRow((int) i + j + 1);
        AbsenceRecord absenceStaff = absences.get(j);
        // ?  
        row.createCell((short) 0).setCellValue(absenceStaff.getStaffName());
        row.createCell((short) 1).setCellValue(absenceStaff.getCaculateDateString());
        cell = row.createCell((short) 2);
        cell.setCellValue(
                new SimpleDateFormat(DateHelper.ONLY_DATE_FORMAT).format(absenceStaff.getAbsenceDate()));
        row.createCell((short) 3).setCellValue("?");
    }
    // ?  
    try {
        String fileName = "C:/xhuxing-private/" + new Date(System.currentTimeMillis()).getMonth()
                + ".xls";
        FileOutputStream fout = new FileOutputStream(fileName);
        wb.write(fout);
        fout.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:attheraces.ExportarParaExcel.java

private void criarCabecalho(HSSFSheet sheet) {
    HSSFRow row = sheet.createRow(0);
    row.createCell(0).setCellValue("Data");
    row.createCell(1).setCellValue("Hora");
    row.createCell(2).setCellValue("Pista");
    row.createCell(3).setCellValue("OddFav");
    row.createCell(4).setCellValue("NomeFav");
    row.createCell(5).setCellValue("OddFav2");
    row.createCell(6).setCellValue("NomeFav2");
    row.createCell(7).setCellValue("Distancia");
    row.createCell(8).setCellValue("Num. Cavalos");
    row.createCell(9).setCellValue("Winner");
}

From source file:attheraces.ExportarParaExcel.java

private void addLinha(HSSFSheet sheet, Informacoes inf, int i) {
    HSSFRow row = sheet.createRow(i);
    row.createCell(0).setCellValue(inf.getDataToString());
    row.createCell(1).setCellValue(inf.getHora());
    row.createCell(2).setCellValue(inf.getPista());
    row.createCell(3).setCellValue(inf.getOddFav());
    row.createCell(4).setCellValue(inf.getNomeOddFav());
    row.createCell(5).setCellValue(inf.getOddFav2());
    row.createCell(6).setCellValue(inf.getNomeOddFav2());
    row.createCell(7).setCellValue(inf.getDistancia());
    row.createCell(8).setCellValue(inf.getNumCavalos());
    row.createCell(9).setCellValue(inf.getCampeao());
}

From source file:bad.robot.excel.DateCellTest.java

License:Apache License

@Test
public void shouldSetDataFormatWhenAddingACell() throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    HSSFRow row = sheet.createRow(0);
    cell.addTo(row, column(A), workbook);
    assertThat(getCellDataFormatAtCoordinate(coordinate(A, 1), workbook), is("dd-MMM-yyyy"));
}

From source file:bad.robot.excel.DateCellTest.java

License:Apache License

@Test
public void shouldSetDataFormatWhenReplacingACell() throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    HSSFRow row = sheet.createRow(0);
    HSSFCell original = row.createCell(0);
    cell.update(original, workbook);/*from  ww w.  j  a v a2  s.  c om*/
    assertThat(getCellDataFormatAtCoordinate(coordinate(A, 1), workbook), is("dd-MMM-yyyy"));
}

From source file:BaseDatos.ExportarDatos.java

public void CrearExel() throws IOException {

    FileOutputStream archivoSalida = null;

    try {/*from  www.  j a v a2 s. co  m*/
        Statement conexion = ConectarMysql.obtenerConexion().createStatement(ResultSet.CONCUR_READ_ONLY,
                ResultSet.TYPE_FORWARD_ONLY);
        HSSFWorkbook libro = new HSSFWorkbook();
        HSSFSheet hoja = libro.createSheet("reporte");
        String SqlVentas = "select * from ventas_realizadas";
        ResultSet resultado = conexion.executeQuery(SqlVentas);

        HSSFRow fila = hoja.createRow((short) 0);
        HSSFCell celda = fila.createCell(0);
        celda.setCellValue("nombre del producto");
        celda = fila.createCell(1);
        celda.setCellValue("fabricante");
        celda = fila.createCell(2);
        celda.setCellValue("contenedor");
        celda = fila.createCell(3);
        celda.setCellValue("cantidad");
        celda = fila.createCell(4);
        celda.setCellValue("precio Compra");
        celda = fila.createCell(5);
        celda.setCellValue("precio Venta");
        celda = fila.createCell(6);
        celda.setCellValue("fecha");
        if (resultado.first()) {
            int contador = 1;

            do {
                filaExel.clear();
                fila = hoja.createRow((short) contador);

                obtenerDatos(resultado.getInt(2));
                cantidad = resultado.getString(3);
                precioCompra = resultado.getString(4);
                precioVenta = resultado.getString(5);
                fecha = resultado.getString(6);
                filaExel.add(nombreProducto);
                filaExel.add(fabricante);
                filaExel.add(contenedor);
                filaExel.add(cantidad);
                filaExel.add(precioCompra);
                filaExel.add(precioVenta);
                filaExel.add(fecha);
                for (int a = 0; a < filaExel.size(); a++) {
                    celda = fila.createCell(a);
                    if (a == 3) {
                        celda.setCellValue(Integer.parseInt(filaExel.get(a)));
                    } else if (a == 4 || a == 5) {
                        celda.setCellValue(Double.parseDouble(filaExel.get(a)));
                    } else {
                        celda.setCellValue(filaExel.get(a));
                    }
                }
                this.operacion.setText("operando : " + contador);
                contador++;
            } while (resultado.next());

        }
        String direccionCompleta = "C:\\Users\\zombozo\\Documents\\ventas.xls";
        File archivo = new File(direccionCompleta);
        archivoSalida = new FileOutputStream(archivo);
        libro.write(archivoSalida);
        archivoSalida.close();
        conexion.close();
        JOptionPane.showMessageDialog(rootPane,
                "Creado Correctamente, busque el archivo en la carpeta documentos");
    } catch (Exception e) {
        JOptionPane.showMessageDialog(rootPane, "Ocurrio un error 1: " + e);
    }
}