Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook

Introduction

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

Prototype

public HSSFWorkbook() 

Source Link

Document

Creates new HSSFWorkbook from scratch (start here!)

Usage

From source file:archivocsv.models.Reporte.java

public static ArrayList<Reporte> generarReporteExcel(String anio, String mes) throws IOException {
    ArrayList<Reporte> archivoExcel = new ArrayList<>();
    try {//from   w  ww.  j a v a 2s .co  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: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);/* w w  w .  j a  v  a 2  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 criarUnicoArquivo() {
    FileOutputStream fos = null;// w  w  w.  j av a 2 s.c o m
    try {
        HSSFWorkbook workbook = new HSSFWorkbook();
        File f = new File(caminho);

        fos = new FileOutputStream(f);

        int i = 1;
        HSSFSheet sheet = workbook.createSheet("Attherace");
        criarCabecalho(sheet);
        for (Informacoes inf : listagem) {
            addLinha(sheet, inf, i);
            i++;
        }

        workbook.write(fos);
    } catch (Exception e) {
        TrataException.fatal(e);
    } finally {
        try {
            fos.flush();
            fos.close();
        } catch (Exception e) {
            TrataException.fatal(e);
        }
    }
}

---

From source file:attheraces.ExportarParaExcel.java

private void criarUmArquivoPorMes() {
    try {//w  ww  . ja v  a2  s  .  com
        String mesAnoAnterior = "";
        int i = 1;
        HSSFSheet sheet = null;
        HSSFWorkbook workbook = null;
        File f = null;
        FileOutputStream fos = null;
        for (Informacoes inf : listagem) {

            String mesAno = DataUtils.getDataFormatada(inf.getData(), "MM-yy");

            if (!mesAno.equals(mesAnoAnterior)) {

                if (workbook != null) {
                    workbook.write(fos);
                    fos.flush();
                    fos.close();
                }

                workbook = new HSSFWorkbook();
                f = new File(caminho + mesAno + ".xls");
                fos = new FileOutputStream(f);
                sheet = workbook.createSheet("Attheraces");
                i = 1;

                criarCabecalho(sheet);
            }

            addLinha(sheet, inf, i);

            mesAnoAnterior = mesAno;
            i++;
        }

        workbook.write(fos);
        fos.flush();
        fos.close();

    } catch (Exception e) {
        TrataException.fatal(e);
    }
}

---

From source file:attheraces.ExportarParaExcel.java

private void criarUmArquivoPorMesSeparadoEmAbas() {
    try {//  ww w .ja  va  2s. c o  m
        String diaAnterior = "";
        String mesAnoAnterior = "";
        int i = 0;
        HSSFSheet sheet = null;
        HSSFWorkbook workbook = null;
        File f = null;
        FileOutputStream fos = null;
        for (Informacoes inf : listagem) {

            String dia = DataUtils.getDataFormatada(inf.getData(), "dd");
            String mesAno = DataUtils.getDataFormatada(inf.getData(), "MM-yy");

            if (!mesAno.equals(mesAnoAnterior)) {

                if (workbook != null) {
                    workbook.write(fos);
                    fos.flush();
                    fos.close();
                }

                workbook = new HSSFWorkbook();
                f = new File(caminho + mesAno + ".xls");
                fos = new FileOutputStream(f);
            }

            if (!dia.equals(diaAnterior)) {
                i = 0;
                sheet = workbook.createSheet(dia.split("/")[0]);
                criarCabecalho(sheet);
                i++;
            }

            addLinha(sheet, inf, i);

            diaAnterior = dia;
            mesAnoAnterior = mesAno;
            i++;
        }

        workbook.write(fos);
        fos.flush();
        fos.close();

    } catch (Exception e) {
        TrataException.fatal(e);
    }
}

---

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  ww w  .ja  v a2s  .  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: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);/* w w w. j ava  2  s.c  o m*/
    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);//from ww  w  .  ja  va2s. c  om
    HSSFCell original = row.createCell(0);
    cell.update(original, workbook);
    assertThat(getCellDataFormatAtCoordinate(coordinate(A, 1), workbook), is("dd-MMM-yyyy"));
}

---

From source file:bad.robot.excel.matchers.StubCell.java

License:Apache License

static Cell createCell(int row, int column, Date date) {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    Cell cell = sheet.createRow(row).createCell(column, CELL_TYPE_NUMERIC);
    cell.setCellValue(date);//from   w w w .  ja  v a  2s.c  o  m
    CellStyle style = workbook.createCellStyle();
    style.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("m/d/yy h:mm"));
    cell.setCellStyle(style);
    return cell;
}

---

From source file:bad.robot.excel.matchers.StubCell.java

License:Apache License

private static Cell create(int row, int column, int type) {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    Cell cell = sheet.createRow(row).createCell(column, type);
    return cell;/*from   w w w.j  a va 2 s.c  o  m*/
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• »