Our Engineers can Write as well

How to upload an Excel File into your Web Application

It’s been three decades since Microsoft launched MS Excel, yet it leads the spreadsheet software space like no other app. It’s flexibility to define the layout and structure of the information the way user wants, makes the data managing spreadsheet software more reliable and attractive. Offered as part of the Microsoft Office Suite, today MS Excel comes with power packed features and functionalities that are easy to use, comprehensive and reliable so that users can enter, manage, maintain, save and retrieve nearly any type of information or data.

In this blog, let’s see how an embedded Excel Layout in a web app is downloaded and then successfully uploaded after entering relevant data in the respective columns. For easy understanding, we have provided you with screenshots of a web application meant for employee’s transport management of a leading organization and how an Excel Sheet is uploaded into this app using Spring Boot and Hibernate.

These are the steps to upload your file:

Step 1:

Click the download template button in the app to download the Excel Template into your system as illustrated in (Fig. A)

uploading excel file in web application

(Fig. A).

The downloaded spread sheet shows relevant data grid where the data needs to be entered (Fig. B).

uploading excel file in web application

(Fig. B).

In the following example (Fig. C), you may see personal information of an employee entered appropriately in the respective columns for further processing.

uploading excel file in web application

(Fig. C).

Once the data is entered as shown in the above illustration save the file. Then go back to your web app browser and click the “click here /drag “button to fetch or drag the file for uploading. Refer (Fig. D)

uploading excel file in web application

(Fig. D).

Then click the upload button as shown above in the screenshot to finally upload the data in to the web app, but before that you need code as shown below:

Code for Uploading:

1) Get File name

Code Below:

MultipartFile inputFile;
String originalFilename = inputFile.getOriginalFilename();

2) Save Excel file in local directory

Code Below:

if((inputFile.getOriginalFilename().split(“\\.”)[1].equals(“xlsx”))) {
int randomPrefix = (int)(Math.random()*9000)+1000;

destinationFile = new File(“E:\\…\\…\\…\\uploadDataDirectory\\” + String.valueOf(randomPrefix).concat(originalFilename));
inputFile.transferTo(destinationFile);
headers.add(“File saved Successfully – “, originalFilename);
}

3) Get file

Code Below:

FileInputStream excelFile = new FileInputStream(new File(destinationFile.toString()));
Workbook workbook = new XSSFWorkbook(excelFile);

4) Read input from first sheet

Code Below:

Sheet datatypeSheet = workbook.getSheetAt(0);
Iterator iterator = datatypeSheet.iterator();
ArrayList columnName = new ArrayList();

By using workbook.getSheetAt(0) we will be able to read value from sheet(0), datatypeSheet.iterator() function is used to iterate the next cell value.

5) Input validation

Code Below:

switch (type) {
case STRING:
value = cellValue.getStringCellValue();
break;
case NUMERIC:
value = Integer.toString(((int)Math.round(cellValue.getNumericCellValue())));
break;
case BLANK:
blankCells.add(columnName.get(columnCount));
break;
}

Read the cell wise value, depending upon value we can set the datatype.

6) By using Spring Data JpaRepository, we perform CRUD operations

Once the coding is successful, you may be able to export your data into the web app effectively as shown below in (Fig. E)

uploading excel file in web application

(Fig. E).

We trust that this quick guidepost helps you effectively upload your Excel file into your web application.

Keep watching Future Focus blogs for such useful development tips.

mahesh - software engineer

Contributed by:

Mageshwaran
Software Engineer

Your email address will not be published. Required fields are marked *