Convert base64 to excel file in java

Question

I have a web application from where I am uploading excel file and converting it into base64 and sending it to my rest api as a string, from my rest API i need to read that excel file and then I need to upload that file in my database (MYSQL), I don't want to save the file anywhere on disk, i want to read file in memory. I searched a lot on internet on how can i convert base64 to excel in java, but unfortunately i am not getting any solution.

Any help would be appreciated.

Do you need to convert base64 to excel or excel to base64? It's not clear from your question — ebbishop, Jan 08 '20 at 16:31
Can you add any code you've already tried? Also some explanation of why you do not want to save the file on the disk would be helpful if we are to suggest alternatives. — Joel Trauger, Jan 08 '20 at 16:40
Why would you convert to base64? That sounds like an entirely unnecessary conversion step. — Mark Rotteveel, Jan 08 '20 at 19:07

score 2 · Accepted Answer · answered Jan 08 '20 at 16:20

2

Apache POI is a good library to process excel files, here is a good tutorial to start with it, basically you have to convert your base base 64 file into an InputStream and from there use one of the WorkBook implementation that Apache POI provides to process the data.

answered Jan 08 '20 at 16:20

David Florez

1,460
2
13
26

Thanks David I did the changes and its working – Rigsby Jan 08 '20 at 16:38

Georg Muehlenberg · Answer 2 · 2020-01-08T16:30:47.747

It makes no difference to Java if you have an image or an excel file. Below is a piece of exemplary code that I think should help you getting started. It is based on Write Base64-encoded image to file and the code provided there for decoding the base 64 encoded file and based on the instructions from https://www.codejava.net/java-se/jdbc/insert-file-data-into-mysql-database-using-jdbc. It should work for many databases as it is using only JDBC, but it is not tested against MySQL.

package net.codejava.jdbc;

import org.apache.commons.codec.binary.Base64;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

class JdbcInsertFileTwo {

    public static void main(String[] args) {

        String base64encoded = "asdf123";

        byte[] excelBytes = Base64.decodeBase64(base64encoded);

        String url = "jdbc:mysql://localhost:3306/contactdb";
        String user = "root";
        String password = "secret";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {

            PreparedStatement statement = conn.prepareStatement("insert into table(name, data) " + "values(?,?)");
            statement.setString(1, "My file name.xls");
            statement.setBinaryStream(2, new ByteArrayInputStream(excelBytes), excelBytes.length);

            int row = statement.executeUpdate();
            if (row > 0) {
                System.out.println("The excel file was uploaded.");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

Hi @georg, I have updated my questing, and i dont want to save the file in my hard disk, i want to read it in memory and my database is MYSQL — Rigsby, Jan 08 '20 at 16:10
I have updated my answer accordingly and provided a code sample for you. — Georg Muehlenberg, Jan 08 '20 at 16:31
Glad to help. Don't forget to accept the answer if it worked for you, so other users can find it more easily. — Georg Muehlenberg, Jan 28 '20 at 13:40

Convert base64 to excel file in java

2 Answers2