Is possible to upload a file.xlsx, process it without save it in server? I am using jaxrs

Question

I'm using jaxrs for uploading files *.xlsx. Is possible to upload this files and process them in memory? I only want read that file, Process with Apache POI and save information at DataBase. Without save them in server

Thanks

Answer 1

sorry for late reply, this answer may help somebody.

Yes, It is possible to read the content of an uploaded .xlsx file in memory [without saving the uploaded content to a physical file]. but this approach require more memory, as the entire file content will be stored in buffer.

below servlet, is a working example for this, with slight modification you can convert this code into JSP, if needed.

pass the uploaded file's inputstream while creating XSSFWorkbook object, as shown below, you will be able to read the file content as in the example code.

add apache-poi jars and apache commons-fileupload.jar in the WEB-INF/lib folder for the correct working of this code.

import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.*;
import org.apache.commons.fileupload.disk.*;
import org.apache.commons.fileupload.servlet.*;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.List;
import java.util.Iterator;

@WebServlet("/FileUploadServlet")
public class FileUploadServlet extends HttpServlet {


    protected void doPost(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        OPCPackage pkg = null;
        XSSFWorkbook xlsxbook = null;
        InputStream xlsxContentStream = null;

        try {


                boolean isMultipart = ServletFileUpload.isMultipartContent(request);

                if (isMultipart) {

                    List<FileItem> items = new ServletFileUpload(
                            new DiskFileItemFactory()).parseRequest(request);
                    for (FileItem item : items) {
                        if (!item.isFormField()) {
                            String fieldName = item.getFieldName();
                            String fileName = FilenameUtils.getName(item.getName());

                            xlsxContentStream = item.getInputStream();

                            pkg = OPCPackage.open(xlsxContentStream);
                            xlsxbook = new XSSFWorkbook(pkg);
                            XSSFSheet sheet = xlsxbook.getSheetAt(0);

                            Iterator<Row> itr = sheet.iterator();

                            while (itr.hasNext()) {
                                Row row = itr.next();

                                // Iterating over each column of Excel file
                                Iterator<Cell> cellIterator = row.cellIterator();
                                String text = "";
                                double num = 0;
                                while (cellIterator.hasNext()) {

                                    Cell cell = cellIterator.next();

                                    switch (cell.getCellType()) {
                                    case Cell.CELL_TYPE_STRING:
                                        text = cell.getStringCellValue();
                                        break;
                                    case Cell.CELL_TYPE_NUMERIC:
                                        num = cell.getNumericCellValue();
                                        break;
                                    default:

                                    }
                                }

                                out.print(text + " " + num);
                                //call database insert method here and pass the xlsx column values
                            }

                        }
                    }
                }
                out.flush();

            } catch (FileUploadException e) {
                throw new ServletException("Cannot parse multipart request.", e);
            } catch (Exception e) {
                throw new ServletException("", e);
            } finally {
                if(null!=xlsxContentStream){ xlsxContentStream.close();}
                if(null!=pkg){ pkg.close();}

            }

        }


protected void doGet(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        doPost(request, response);
    }

}

your jsp/html file should have enctype="multipart/form-data" in the form

<form action="FileUploadServlet" name="form2" method="post" enctype="multipart/form-data">
Select a .xlsx File to upload :<input type="file" name="file" id="file" size="50" /> 
<input type="submit" value="Upload xlsx File"/>
</form>