How to read Excel file content from an HTML input?

Question

I have an input (type="file") where the client can attach an Excel document. The problem is that I don't know how to read it to get some information I need.

If I create a File with the Excel path, I can read the information I need from the document:

@Override
public String obtenerTelefono(File excelFile) throws IOException {
    File file = new File("C:\\Users\\usuario\\Desktop\\Cosas\\tlf.xls");
    FileInputStream iFile = new FileInputStream(file);
    HSSFWorkbook workbook = new HSSFWorkbook(iFile);
    HSSFSheet sheet = workbook.getSheetAt(0);
    workbook.close();
    return obtenerTelefono(sheet);
}

public String obtenerTelefono(HSSFSheet sheet) {
    HSSFRow row = sheet.getRow(0);
    int telefono = (int) row.getCell(1).getNumericCellValue();
    return String.valueOf(telefono);
}

The problem is I don't know how to do this same thing when I have to work with the document attached (File excelFile) instead of the path. I have an example method, but I can't make it work for my situation:

public void importarFichero() throws IOException, IcaException {
    boolean validado = validarFichero();
    if (validado) {
        prefijo = fichero.getFileName().split("\\.")[0];
        String sufijo = fichero.getFileName().split("\\.")[1];
        File file = File.createTempFile(prefijo + "_", "." + sufijo, new File(System.getProperty("java.io.tmpdir")));
        OutputStream outputStream = new FileOutputStream(file);
        org.apache.poi.util.IOUtils.copy(fichero.getInputstream(), outputStream);
        FileInputStream inputStream = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        List<UnidadPoblacion> unidadPoblacionImportadas = obtenerUnidadesPoblacionExcel(sheet, this.anyoEjercicio);
        if (service.guardaUnidadPoblacionImportadas(unidadPoblacionImportadas)) {
            if (this.getListado() != null) {
                try {
                    loadListadoFromDDBB();
                } catch (IcaException e) {
                    addMessageError("actualizar_multiple_error");
                }
            }
            int resultado = service.actualizaPoblacionEjercicioBonificacion(this.anyoEjercicio.intValue(),Integer.parseInt(unidadPoblacionImportadas.get(0).getProvincia().getCodigoProvincia()));
            if(resultado >= 0) {
                addMessageInfo("importar_fichero_ok", (Object) fichero.getFileName());
            } else {
                addMessageError("importar_fichero_ok_actualizar_poblacion_error", fichero.getFileName());
            }
            fichero = null;
            anyoEjercicio = null;

        } else {
            addMessageError("importar_fichero_error", fichero.getFileName());
        }
    }
}

I think I can't reuse this because fichero is an instance of a class named UploadedFile and I am working with File, so I don't have the getInputStream() method and I can't find a similar File method.

Do you know how I can create that temporal file with the information from the original document and then read that file? Maybe the input information I am receiving from the HTML shouldn't be stored as a File instance?

On the server side, I am using SpringMVC.

This is the HTML part:

<div class="form-group form-inline form-xtra required">
    <label>Seleccionar archivo:</label>
    <div required="true">
        <input type="file" id="destinatarioExcel" name="destinatarioExcel" disabled="disabled"/>
    </div>
</div>

This is the controller part:

@RequestMapping(value = "/guardarMensaje", method = RequestMethod.POST)
public String guardarMensaje(@Valid @ModelAttribute("mensaje") MensajeDto mensaje, @RequestParam("destinatarioExcel") File excelFile, BindingResult errors,
        final RedirectAttributes attr,
        final ModelMap model) throws IOException {

    if (errors.hasErrors()) {
        attr.addFlashAttribute("org.springframework.validation.BindingResult.mensaje", errors);
        attr.addFlashAttribute("mensaje", mensaje);
    }

    Calendar fechaActual = Calendar.getInstance();
    Date fechaSql = new Date((fechaActual.getTime()).getTime());
    String hora = Integer.valueOf(fechaActual.get(Calendar.HOUR_OF_DAY)).toString();

    mensaje.setIdLatiniaSMS("Prueba");
    mensaje.setIdUsuario(new Long(1));
    mensaje.setEstadoSMS("E");
    mensaje.setFechaSMS(fechaSql);
    mensaje.setHoraSMS(hora);
    mensaje.setResEnvio("Prue");
    mensaje.setResEnvioDec("Prue");
    mensaje.setEstadoEnvio("Est");

    if (mensaje.getTelfLibreta() != null) {
        mensaje.setNumTlfn(mensaje.getTelfLibreta());
    } else if (mensaje.getTelfManual() != null) {
        mensaje.setNumTlfn(mensaje.getTelfManual());
    } else if (excelFile != null) {
        mensaje.setNumTlfn(this.mensajeProvider.obtenerTelefono(excelFile));
    }

    Boolean res;
    res = this.mensajeProvider.guardarMensaje(mensaje);
    if (res) {
        attr.addFlashAttribute("feedback", new MensajeDto(false, "Mensaje guardado correctamente"));

    } else {
        attr.addFlashAttribute("feedback", new MensajeDto(true, "Error al guardar el mensaje"));

    }
    model.clear();
    return "redirect:/mensajes";

}

I think so. If in the controller I save the excelFile.toString() in my database, I get the value "tlf.xls" stored. — Erik Escriche, Oct 16 '18 at 11:47
You can create a FileInputStream like this : File file = //Upload the file FileInputStream fis = new FileInputStream(file); Now you can pass this FileInputStream to the HSSFWorkBook . — rootExplorr, Oct 16 '18 at 12:08
In the method where I read the file, the file has already been uploaded and received as an argument. If I do FileInputStream fis = new FileInputStream(file), "file" being the argument of the method, I get this error: org.apache.poi.poifs.filesystem.NotOLE2FileException: Invalid header signature; read 0x0000000000000000, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document — Erik Escriche, Oct 16 '18 at 12:20
https://stackoverflow.com/questions/1516144/how-to-read-and-write-excel-file https://poi.apache.org/ — , Oct 16 '18 at 15:59
Your suggestion works for me as long as I provide the path, like in my first code fragment. But if I try to use the file the user has selected, I get the same error I copied above in the comments: "org.apache.poi.poifs.filesystem.NotOLE2FileException: Invalid header signature; read 0x0000000000000000, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document". I have used isFile() and canRead() to make sure I am getting the file, and it looks like I do, so I have no idea why I am getting this error. — Erik Escriche, Oct 17 '18 at 06:48

score 1 · Answer 1 · answered Oct 16 '18 at 11:26

You can use something like this:

@PostMapping("/image/tmp")
open fun saveMediaContentTmp(file: MultipartFile): ResponseEntity<Map<String, String>> {

    val endPath = Path(PATH_TO_TMP_FILE)
    Files.copy(file.inputStream, endPath, StandardCopyOption.REPLACE_EXISTING)

    importarFichero(endPath.toFile())

    return ResponseEntity.ok(mapOf(
            "name" to file.originalFilename
    ))
}

this is Kotlin language, but I hope is woud be clear.

score 0 · Answer 2 · answered Oct 16 '18 at 11:19

0

Use apachePOI extenstion ( library ) and you can do whatever you want with excel

answered Oct 16 '18 at 11:19

Thilanka A. Weerasinghe

74
8

In fact, that is what I am using; but it is the first time I try to read an Excel and I feel quite lost. – Erik Escriche Oct 16 '18 at 12:02

How to read Excel file content from an HTML input?

2 Answers2