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

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

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

Introduction

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

Prototype

Cell createCell(int column);

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:appform.Complaint.java

public static void updateComplaint(String complaint) {
    try {//from   w w  w. j  a  v  a 2s  .co  m
        FileInputStream file = new FileInputStream(new File("Complaint.xls"));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        HashMap data;
        data = new HashMap<>();
        data.put("7", new Object[] { complaint });
        Set keyset = data.keySet();
        int rownum = sheet.getLastRowNum() + 1;
        for (Object key : keyset) {
            Row row = sheet.createRow(rownum++);
            Object[] objArr = (Object[]) data.get(key);
            int cellnum = 0;
            for (Object obj : objArr) {
                Cell cell = row.createCell(cellnum++);
                if (obj instanceof String)
                    cell.setCellValue((String) obj);
                else if (obj instanceof Double)
                    cell.setCellValue((Double) obj);
            }
        }
        FileOutputStream out = new FileOutputStream(new File("Complaint.xls"));
        workbook.write(out);
        out.close();
        System.out.println("Complaint written successfully..");

        //</string></string,></string,>
    }

    catch (Exception e) {
    }
}

From source file:archivocsv.models.Reporte.java

public static ArrayList<Reporte> generarReporteExcel(String anio, String mes) throws IOException {
    ArrayList<Reporte> archivoExcel = new ArrayList<>();
    try {/*  w  w w. j a  va2 s. c  o m*/
        FileChooser elegirArchivo = new FileChooser();
        FileChooser.ExtensionFilter filtroExt = new FileChooser.ExtensionFilter("Archivos Excel (*.xls)",
                "*.xls");
        elegirArchivo.getExtensionFilters().add(filtroExt);
        File archivo = elegirArchivo.showSaveDialog(archivoCsv.getVentanaPrincipal());
        archivo.createNewFile();
        Workbook libro = new HSSFWorkbook();
        FileOutputStream enviar = new FileOutputStream(archivo);
        Sheet hoja = libro.createSheet("Reportes");
        Sheet hoja2 = libro.createSheet("Entradas Tarde");
        Row fila = hoja.createRow(0);
        Row fila2 = hoja2.createRow(0);
        for (int c = 0; c < 5; c++) {
            Cell celda = fila.createCell(c);
            if (c == 0) {
                celda.setCellValue("ID");
            }
            if (c == 1) {
                celda.setCellValue("Nombre");
            }
            if (c == 2) {
                celda.setCellValue("Entrada");
            }
            if (c == 3) {
                celda.setCellValue("Salida");
            }
            if (c == 4) {
                celda.setCellValue("Horas Trabajadas");
            }
        }
        for (int c2 = 0; c2 < 3; c2++) {
            Cell celda2 = fila2.createCell(c2);
            if (c2 == 0) {
                celda2.setCellValue("ID");
            }
            if (c2 == 1) {
                celda2.setCellValue("Nombre");
            }
            if (c2 == 2) {
                celda2.setCellValue("Entrada");
            }
        }
        Connection con = Helper.getConnection();
        String entrada = "SELECT * FROM t_marcacion WHERE marcacion LIKE '%/" + mes + "/" + anio + "%'"
                + " AND marcacion LIKE '%a%'";
        ResultSet rs = con.createStatement().executeQuery(entrada);
        ResultSet rs3 = con.createStatement().executeQuery(entrada);
        int contar = 0;
        int contar2 = 1;
        while (rs.next()) {
            contar += 1;
            Row filaDatos = hoja.createRow(contar);
            Cell celdaID = filaDatos.createCell(0);
            Cell celdaNombre = filaDatos.createCell(1);
            Cell celdaEntrada = filaDatos.createCell(2);
            Cell celdaSalida = filaDatos.createCell(3);
            Cell celdaHoras = filaDatos.createCell(4);
            Reporte reporte = new Reporte();
            reporte.setEntrada(rs.getString("marcacion"));
            celdaEntrada.setCellValue(reporte.getEntrada());
            reporte.setId(rs.getString("id"));
            celdaID.setCellValue(reporte.getId());
            reporte.setNombre(rs.getString("nombre"));
            celdaNombre.setCellValue(reporte.getNombre());
            //Query que verifica si existe el registro de salida
            String coincidir = "SELECT * FROM t_marcacion WHERE id=" + rs.getString("id")
                    + " AND marcacion LIKE '%" + rs.getString("marcacion").substring(0, 10) + "%'"
                    + " AND marcacion LIKE '%p%'";
            ResultSet rs2 = con.createStatement().executeQuery(coincidir);
            if (rs2.next()) {
                reporte.setSalida(rs2.getString("marcacion"));
                celdaSalida.setCellValue(reporte.getSalida());
                String horaInicial = rs.getString("marcacion").substring(11, 15) + " AM";
                String horaFinal = rs2.getString("marcacion").substring(11, 15) + " PM";
                if (horaFinal.substring(0, 2).equals("12")) {
                    horaFinal = rs2.getString("marcacion").substring(11, 15) + " AM";
                }
                DateFormat sdf = new SimpleDateFormat("KK:mm a");
                Date date = sdf.parse(horaInicial);
                Date date2 = sdf.parse(horaFinal);

                double hrsInicialMs = date.getTime();
                double hrsFinalMs = date2.getTime();
                double diferencia = hrsFinalMs - hrsInicialMs;
                double resta = (diferencia / (1000 * 60 * 60));
                int primerNumero = (int) resta;
                double segundoNumero = resta - primerNumero;
                int convertirNumero = (int) (segundoNumero * 60);
                int restarAlumerzo = primerNumero - 1;
                if (convertirNumero == 0) {
                    reporte.setHorasTrabajadas(restarAlumerzo + ":00");
                    celdaHoras.setCellValue(reporte.getHorasTrabajadas());
                } else {
                    if (convertirNumero > 0 & convertirNumero < 10) {
                        reporte.setHorasTrabajadas(restarAlumerzo + ":0" + convertirNumero);
                        celdaHoras.setCellValue(reporte.getHorasTrabajadas());
                    } else {
                        reporte.setHorasTrabajadas(restarAlumerzo + ":" + convertirNumero);
                        celdaHoras.setCellValue(reporte.getHorasTrabajadas());
                    }
                }
            } else {
                reporte.setSalida("Sin registro");
                celdaSalida.setCellValue(reporte.getSalida());
                reporte.setHorasTrabajadas("Sin registro");
                celdaHoras.setCellValue(reporte.getHorasTrabajadas());
            }
        }
        while (rs3.next()) {
            Row filaDatos2 = hoja2.createRow(contar2);
            Cell celdaId = filaDatos2.createCell(0);
            Cell celdaNombre = filaDatos2.createCell(1);
            Cell celdaMarcacion = filaDatos2.createCell(2);
            Reporte reporte = new Reporte();
            DateFormat sdf = new SimpleDateFormat("KK:mm a");
            String horaInicio = "08:00 AM";
            String horaFin = "12:00 PM";
            String horaEntrada = rs3.getString("marcacion");
            if (horaEntrada.substring(11, 12).equals("0")) {
                horaEntrada = rs3.getString("marcacion").substring(11, 16) + " AM";
            } else {
                if (horaEntrada.substring(11, 13).equals("10") || horaEntrada.substring(11, 13).equals("11")) {
                    horaEntrada = rs3.getString("marcacion").substring(11, 16) + " AM";
                } else {
                    horaEntrada = rs3.getString("marcacion").substring(11, 15) + " AM";
                }
            }
            Date hrInicio = sdf.parse(horaInicio);
            Date hrFin = sdf.parse(horaFin);
            Date hrEntrada = sdf.parse(horaEntrada);
            Calendar calInicio = new GregorianCalendar();
            Calendar calFin = new GregorianCalendar();
            Calendar calEntrada = new GregorianCalendar();
            calInicio.setTime(hrInicio);
            calFin.setTime(hrFin);
            calEntrada.setTime(hrEntrada);

            if (calEntrada.after(calInicio) & calEntrada.before(calFin)) {
                contar2 += 1;
                reporte.setId(rs3.getString("id"));
                celdaId.setCellValue(reporte.getId());
                reporte.setNombre(rs3.getString("nombre"));
                celdaNombre.setCellValue(reporte.getNombre());
                reporte.setEntrada(rs3.getString("marcacion"));
                celdaMarcacion.setCellValue(reporte.getEntrada());
                archivoExcel.add(reporte);
            }
        }
        hoja.setColumnWidth(0, 850);
        hoja.setColumnWidth(1, 3000);
        hoja.setColumnWidth(2, 6000);
        hoja.setColumnWidth(3, 6000);
        hoja.setColumnWidth(4, 4000);
        hoja2.setColumnWidth(0, 850);
        hoja2.setColumnWidth(1, 3000);
        hoja2.setColumnWidth(2, 6000);
        libro.write(enviar);
        enviar.close();
        Desktop.getDesktop().open(archivo);
    } catch (Exception e) {

    }
    return archivoExcel;
}

From source file:at.fh.swenga.firefighters.report.ExcelFireEngineReportView.java

@Override
protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {

    List<FireEngineModel> fireEngines = (List<FireEngineModel>) model.get("fireEngines");

    // create a worksheet
    Sheet sheet = workbook.createSheet("FireEngine Report");

    // create style for header cells
    CellStyle style = workbook.createCellStyle();
    Font font = workbook.createFont();
    font.setFontName("Arial");
    style.setFillForegroundColor(HSSFColor.BLUE.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    style.setFont(font);// ww w .j  av a 2s .  c  om

    // create a new row in the worksheet
    Row headerRow = sheet.createRow(0);

    // create a new cell in the row
    Cell cell0 = headerRow.createCell(0);
    cell0.setCellValue("ID");
    cell0.setCellStyle(style);

    // create a new cell in the row
    Cell cell1 = headerRow.createCell(1);
    cell1.setCellValue("Modell");
    cell1.setCellStyle(style);

    // create a new cell in the row
    Cell cell2 = headerRow.createCell(2);
    cell2.setCellValue("Kennzeichen");
    cell2.setCellStyle(style);

    // create a new cell in the row
    Cell cell3 = headerRow.createCell(3);
    cell3.setCellValue("Leistung");
    cell3.setCellStyle(style);

    // create a new cell in the row
    Cell cell4 = headerRow.createCell(4);
    cell4.setCellValue("Baujahr");
    cell4.setCellStyle(style);

    // create a new cell in the row
    Cell cell5 = headerRow.createCell(5);
    cell5.setCellValue("Aktiv");
    cell5.setCellStyle(style);

    // create a new cell in the row
    Cell cell6 = headerRow.createCell(6);
    cell6.setCellValue("Funktion");
    cell6.setCellStyle(style);

    // create a new cell in the row
    Cell cell7 = headerRow.createCell(7);
    cell7.setCellValue("Feuerwehr");
    cell7.setCellStyle(style);

    // create multiple rows with fireEngines data
    int rowNum = 1;
    for (FireEngineModel fireEngine : fireEngines) {
        // create the row data
        Row row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(fireEngine.getId());
        row.createCell(1).setCellValue(fireEngine.getModel());
        row.createCell(2).setCellValue(fireEngine.getLicensePlate());
        row.createCell(3).setCellValue(fireEngine.getPerformance());
        row.createCell(4).setCellValue(fireEngine.getBuildYear());
        row.createCell(5).setCellValue(fireEngine.getActive());
        row.createCell(6).setCellValue(fireEngine.getAbbreviation().getAbbreviation());
        row.createCell(7).setCellValue(fireEngine.getFireBrigade().getName());
    }

    // adjust column width to fit the content
    sheet.autoSizeColumn((short) 0);
    sheet.autoSizeColumn((short) 1);
    sheet.autoSizeColumn((short) 2);
    sheet.autoSizeColumn((short) 3);
    sheet.autoSizeColumn((short) 4);
    sheet.autoSizeColumn((short) 5);
    sheet.autoSizeColumn((short) 6);
    sheet.autoSizeColumn((short) 7);

}

From source file:at.mukprojects.exclycore.model.ExclyDateTest.java

License:Open Source License

/**
 * Tests the ExclyDate setCell function.
 *///from w  w  w. java  2  s .  c om
@Test
public void testExclyDateSetCell() throws Exception {
    Row row = sheet.createRow(0);

    dateOne.setCell(row.createCell(0));
    log.debug(row.getCell(0).getDateCellValue() + " / " + calendar.getTime());
    assertEquals(row.getCell(0).getDateCellValue(), calendar.getTime());

    dateError.setCell(row.createCell(1));
    log.debug(row.getCell(1).getStringCellValue() + " / " + "###ERROR###");
    assertEquals(row.getCell(1).getStringCellValue(), "###ERROR###");

    dateBlank.setCell(row.createCell(2));
    log.debug(row.getCell(2).getStringCellValue() + " / " + "");
    assertEquals(row.getCell(2).getStringCellValue(), "");
}

From source file:at.mukprojects.exclycore.model.ExclyDoubleTest.java

License:Open Source License

/**
 * Tests the ExclyDouble setCell function.
 *//*from www  .  j  a v a 2  s.c om*/
@Test
public void testExclyDoubleSetCell() throws Exception {
    Row row = sheet.createRow(0);

    doubleOne.setCell(row.createCell(0));
    log.debug(row.getCell(0).getNumericCellValue() + " / " + 5.5);
    assertTrue(row.getCell(0).getNumericCellValue() == 5.5);

    doubleError.setCell(row.createCell(1));
    log.debug(row.getCell(1).getStringCellValue() + " / " + "###ERROR###");
    assertEquals(row.getCell(1).getStringCellValue(), "###ERROR###");

    doubleBlank.setCell(row.createCell(2));
    log.debug(row.getCell(2).getStringCellValue() + " / " + "");
    assertEquals(row.getCell(2).getStringCellValue(), "");
}

From source file:at.mukprojects.exclycore.model.ExclyIntegerTest.java

License:Open Source License

/**
 * Tests the ExclyInteger setCell function.
 *///  w  ww . j  av  a 2 s. c  o m
@Test
public void testExclyIntegerSetCell() throws Exception {
    Row row = sheet.createRow(0);

    integerOne.setCell(row.createCell(0));
    log.debug(row.getCell(0).getNumericCellValue() + " / " + 5);
    assertTrue(row.getCell(0).getNumericCellValue() == 5);

    integerError.setCell(row.createCell(1));
    log.debug(row.getCell(1).getStringCellValue() + " / " + "###ERROR###");
    assertEquals(row.getCell(1).getStringCellValue(), "###ERROR###");

    integerBlank.setCell(row.createCell(2));
    log.debug(row.getCell(2).getStringCellValue() + " / " + "");
    assertEquals(row.getCell(2).getStringCellValue(), "");
}

From source file:at.mukprojects.exclycore.model.ExclyLongTest.java

License:Open Source License

/**
 * Tests the ExclyLong setCell function.
 *///from ww w  .j  av a 2  s. co m
@Test
public void testExclyLongSetCell() throws Exception {
    Row row = sheet.createRow(0);

    longOne.setCell(row.createCell(0));
    log.debug(row.getCell(0).getNumericCellValue() + " / " + 5);
    assertTrue(row.getCell(0).getNumericCellValue() == 5);

    longError.setCell(row.createCell(1));
    log.debug(row.getCell(1).getStringCellValue() + " / " + "###ERROR###");
    assertEquals(row.getCell(1).getStringCellValue(), "###ERROR###");

    longBlank.setCell(row.createCell(2));
    log.debug(row.getCell(2).getStringCellValue() + " / " + "");
    assertEquals(row.getCell(2).getStringCellValue(), "");
}

From source file:at.mukprojects.exclycore.model.ExclyStringTest.java

License:Open Source License

/**
 * Tests the ExclyString setCell function.
 *//*from   www.jav a 2  s.  c  o m*/
@Test
public void testExclyStringSetCell() throws Exception {
    Row row = sheet.createRow(0);

    stringOne.setCell(row.createCell(0));
    log.debug(row.getCell(0).getStringCellValue() + " / Hello ");
    assertEquals(row.getCell(0).getStringCellValue(), "Hello ");

    stringError.setCell(row.createCell(1));
    log.debug(row.getCell(1).getStringCellValue() + " / " + "###ERROR###");
    assertEquals(row.getCell(1).getStringCellValue(), "###ERROR###");
}

From source file:au.gov.ansto.bragg.quokka.experiment.util.ExperimentModelUtils.java

License:Open Source License

public static void saveExperimentToExcel(Experiment experiment, String filename) throws IOException {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("Quokka Multi-Sample Scan");
    int rowCounter = 0;
    if (experiment.isControlledEnvironment()) {
        for (ControlledAcquisition acquisition : experiment.getAcquisitionGroups()) {
            for (SampleEnvironment sampleEnvironment : experiment.getSampleEnvironments()) {
                Row row = sheet.createRow(rowCounter++);
                Cell cell = row.createCell(0);
                cell.setCellValue(sampleEnvironment.getControllerId());
                cell = row.createCell(1);
                cell.setCellValue(acquisition.getEnvSettings().get(sampleEnvironment).getPreset());
            }//from  w ww.  ja  v  a2 s  . c  o  m
            rowCounter = saveAcquisitionToExcel(acquisition, sheet, rowCounter);
        }
    } else {
        rowCounter = saveAcquisitionToExcel(experiment.getNormalAcquisition(), sheet, rowCounter);
    }
    FileOutputStream fileOut = new FileOutputStream(filename);
    workbook.write(fileOut);
    fileOut.close();
}

From source file:au.gov.ansto.bragg.quokka.experiment.util.ExperimentModelUtils.java

License:Open Source License

private static int saveAcquisitionToExcel(Acquisition acquisition, Sheet sheet, int rowCounter) {
    // 1st header
    Row row = sheet.createRow(rowCounter++);
    int colIndex = 5;
    for (InstrumentConfig config : acquisition.getExperiment().getInstrumentConfigs()) {
        Cell cell = row.createCell(colIndex);
        cell = row.createCell(colIndex);
        cell.setCellValue(config.getName());
        colIndex += 5;/*from w ww  . j a va 2s  .c om*/
    }

    // 2nd header
    row = sheet.createRow(rowCounter++);
    Cell cell = row.createCell(0);
    cell.setCellValue("Sequence");
    cell = row.createCell(1);
    cell.setCellValue("Position");
    cell = row.createCell(2);
    cell.setCellValue("Sample Name");
    cell = row.createCell(3);
    cell.setCellValue("Thickness");
    colIndex = 4;
    for (InstrumentConfig config : acquisition.getExperiment().getInstrumentConfigs()) {
        cell = row.createCell(colIndex++);
        cell = row.createCell(colIndex++);
        cell.setCellValue("Transmission");
        cell = row.createCell(colIndex++);
        cell = row.createCell(colIndex++);
        cell.setCellValue("Scattering");
        cell = row.createCell(colIndex++);
        cell.setCellValue("Preset (sec)");
    }

    // Content
    int sequence = 1;
    for (AcquisitionEntry entry : acquisition.getEntries()) {
        row = sheet.createRow(rowCounter++);
        cell = row.createCell(0);
        cell.setCellValue(sequence);
        cell = row.createCell(1);
        cell.setCellValue(entry.getSample().getPosition());
        cell = row.createCell(2);
        cell.setCellValue(entry.getSample().getName());
        cell = row.createCell(3);
        cell.setCellValue(entry.getSample().getThickness());
        colIndex = 4;
        for (InstrumentConfig config : acquisition.getExperiment().getInstrumentConfigs()) {
            AcquisitionSetting setting = entry.getConfigSettings().get(config);
            cell = row.createCell(colIndex++);
            if (setting.isRunTransmission()) {
                cell.setCellValue("X");
            }
            cell = row.createCell(colIndex++);
            cell.setCellValue(setting.getTransmissionDataFile());
            cell = row.createCell(colIndex++);
            if (setting.isRunScattering()) {
                cell.setCellValue("X");
            }
            cell = row.createCell(colIndex++);
            cell.setCellValue(setting.getScatteringDataFile());
            cell = row.createCell(colIndex++);
            cell.setCellValue(setting.getPreset());
        }
        sequence++;
    }
    return ++rowCounter;
}