Save uploaded excel in database

Question

I have a code where my client send an excel file to the server. The server (SpringBoot) needs to 'translate' the MultiplartFile to an excel File. From then, the data needs to be inserted into a database.

However, I never need to generate an excel but rather should insert the data from the spreadsheet into the database directly.

I first tried with :

@RequestMapping(value = "/insert", method = RequestMethod.POST, consumes = "multipart/form-data")
@ResponseBody
public MyMessage insertExcell(@RequestPart("typeFile") String typeFile,
        @RequestPart("uploadFile") MultipartFile multipart, @RequestPart("dataUser") DataUser dataUser) {

    BufferedReader br;
    List<String> result2 = new ArrayList<String>();

    try {
        String line;
        InputStream is = multipart.getInputStream();
        br = new BufferedReader(new InputStreamReader(is));
        while ((line = br.readLine()) != null) {
            result2.add(line);
        }
    } catch (Exception e) {

    }

    for (int i = 0; i < result2.size(); i++) {
        System.out.println("sentence" + result2.get(i));;
    }

The output returns strange symbols.

Then again i tried with :

InputStream inputStream;
        try {
            inputStream = multipart.getInputStream ();
            BufferedReader bufferedReader = new BufferedReader (new InputStreamReader (inputStream));
            String line;
            while ((line = bufferedReader.readLine()) != null)
            {
                System.out.println("linea era" + line);
            }

        } catch (IOException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }

The console output shows strange symbols.

How can i read the data from the excel file uploaded ?

Answer 1

From what i understand, you need to read an Excel file, get your data then save it into a database.

Excel files are stored in various formats :

• Excel 2003 Binary file format (BIFF8).
• Xml Based format (for .xlsx)

If you simply try to read such a file, this would be quite a task...

Fortunately, Apache POI has a library that should help.

You can download it here.

Here is a simple example on how to read your excel file :

try (InputStream inputStream = multipartFile.getInputStream())
    {
        Workbook wb = WorkbookFactory.create(inputStream);
        // opening the first sheet
        Sheet sheet = wb.getSheetAt(0); 
        // read the third row
        Row row = sheet.getRow(2);
        // read 4th cell
        Cell cell = row.getCell(3);
        // get the string value
        String myValue = cell.getStringCellValue();

        // store in the database...         
    } catch (IOException e) {
        //TODO
    }