1. Home
  2. Development
  3. How to Read Excel File and Store in DB?

How to Read Excel File and Store in DB?

thumbnail
Mahammad Ashik A, modified 6 Years ago.
How to Read Excel File and Store in DB?
Junior Member Posts: 81 Join Date: 7/17/17 Recent Posts
Hi,


My requirement is read Excel File and store values in DB.

For that i have added  *apache.poi* jars in my gradle.

This is my code in controller.



UploadPortletRequest uploadPortletRequest = PortalUtil.getUploadPortletRequest(actionRequest); 
try {
uploadPortletRequest.getFileName("uploadedFile");
File userFile = uploadPortletRequest.getFile("uploadedFile");
InputStream inputStream = new FileInputStream(userFile);
Workbook workbook = WorkbookFactory.create(inputStream);

        Sheet sheet = workbook.getSheetAt(0);

        // Create a DataFormatter to format and get each cell's value as String
        DataFormatter dataFormatter = new DataFormatter();

        // rowIterator and columnIterator and iterate over them
        System.out.println("\n\nIterating over Rows and Columns using Iterator\n");
        Iterator<Row> rowIterator = sheet.rowIterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            // Now let's iterate over the columns of the current row
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String cellValue = dataFormatter.formatCellValue(cell);
                System.out.print(cellValue + "\t");
            }
            System.out.println();
        }

}
catch (Exception e) {
// TODO: handle exception
}




Now its reading all the values but how i can store the values in DB?

I am having n number of rows and 5 columns in  a sheet.


How i can achieve this?


Thanks in advance..
thumbnail
Christoph Rabel, modified 6 Years ago.
RE: How to Read Excel File and Store in DB?
Liferay Legend Posts: 1555 Join Date: 9/24/09 Recent Posts
You need to create tables, the usual way is to use service builder. It creates a CRUD layer. You specify in the service.xml the entities which then become tables and columns in the database. Service Builder creates code to create, read, update, delete (+some extra convenience functions) these objects.

https://dev.liferay.com/de/develop/tutorials/-/knowledge_base/7-0/what-is-service-builder
thumbnail
Mahammad Ashik A, modified 6 Years ago.
RE: How to Read Excel File and Store in DB?
Junior Member Posts: 81 Join Date: 7/17/17 Recent Posts
Hi Christoph Rabel,

Thanks for your support.

Is it correct way or any changes required here??
Please Find Below Code...



​​​​​​​public void uploadDocument(ActionRequest actionRequest, ActionResponse actionResponse) throws PortalException{

        UploadPortletRequest uploadPortletRequest = PortalUtil.getUploadPortletRequest(actionRequest); 
        try {
            uploadPortletRequest.getFileName("uploadedFile");
            File userFile = uploadPortletRequest.getFile("uploadedFile");
            InputStream inputStream = new FileInputStream(userFile);

            XSSFWorkbook workbook = new XSSFWorkbook(inputStream); 
            XSSFSheet sheet = workbook.getSheetAt(1); 
            Row row;
            String ri,ph,notes,recordId;

            for(int i=1; i<=sheet.getLastRowNum(); i++){  //points to the starting of excel i.e excel first row
                row = (Row) sheet.getRow(i);  //sheet number
                recordId= row.getCell(0).toString();
                ri = row.getCell(2).toString(); 
                ph  = row.getCell(3).toString();
                notes  = row.getCell(4).toString();
                long concentrationId=CounterLocalServiceUtil.increment();        
                DailyConcentration daily=DailyConcentrationLocalServiceUtil.createDailyConcentration(concentrationId);
                long recId=Long.parseLong(recordId);
                double Ri=Double.parseDouble(ri);
                double Ph=Double.parseDouble(ph);

                daily.setRecordId(recId);
                daily.setRi(Ri);
                daily.setPh(Ph);
                daily.setNotes(notes);
                DailyConcentrationLocalServiceUtil.addDailyConcentration(daily);
       

            }

        }

        catch (Exception e) {
            // TODO: handle exception
        }
    }

    private Log _log = LogFactoryUtil.getLog(MassUploadPortlet.class.getName());
}


​​​​​​​Thank you.
thumbnail
Christoph Rabel, modified 6 Years ago.
RE: How to Read Excel File and Store in DB?
Liferay Legend Posts: 1555 Join Date: 9/24/09 Recent Posts
In general, it should work, it looks correct, but it is very difficult to say without knowing the full code. You can e.g. check in the database, or read the existing entries using the API to check if something was written.

Silently catching exceptions is really bad/evil. If there is an error in the code, you probably just silently swallow it.

        catch (Exception e) {
            // TODO: handle exception
        }

And instead of using DailyConcentrationLocalServiceUtil, it is better to write into the class:

@Reference
private DailyConcentrationLocalService dailyConcentrationLocalService;

Then the service is automatically injected into your class. While the util classes can be convenient, the application is far more robust to you use the dependency injection mechanism.
thumbnail
Gnaniyar Zubair, modified 6 Years ago.
RE: How to Read Excel File and Store in DB?
Liferay Master Posts: 724 Join Date: 12/19/07 Recent Posts
Read the Excel file is a common java functionality and there are many approaches and plugins are avilable ( Apache POI and JExcel ..etc) .

Storing into Liferay DB, you can create the service.xml with all the columns from Excel with necessary finder method and generate the api and service to access from your web module. you can get the reference from here to create and persist the data using service builder

https://dev.liferay.com/en/develop/tutorials/-/knowledge_base/7-0/defining-an-object-relational-map-with-service-builder

​​​​​​​HTH
thumbnail
Mahammad Ashik A, modified 6 Years ago.
RE: How to Read Excel File and Store in DB?
Junior Member Posts: 81 Join Date: 7/17/17 Recent Posts
Thanks for your reply.
​​​​​​​I will do it.