snippetjournal

– notes, snippet codes, and my journal

Read Xlsx Using POI

leave a comment »

File type from uploaded user

private MultipartFile file0;

//getter setter

Instead of using iteration to read each cell in xlsx file, i`m using standard loop. its more easier to find an empty cell in each row. if we using iteration it will skip the empty cell and jump to the next cell that has a value.

method to get header column in xlsx file

public Map<String, Object> readXlsxFile(InputStream xlsxStream) throws IOException {
        Map<String, Object> returnMap = new HashMap<>();

        try {
            Map<Integer, String> headerMap = new HashMap<>();
            List<Map<String, Object>> detailContents = new ArrayList<>();

            //create xssfworkbook instance to read xlsx input stream
            XSSFWorkbook xlsxFile = new XSSFWorkbook(xlsxStream);
            //get first sheet
            XSSFSheet sheet = xlsxFile.getSheetAt(0);

            this.getHeaderColumnAndDetails(headerMap, detailContents, sheet);

            LOGGER.info(" header map ");
            for (Map.Entry<Integer, String> map : headerMap.entrySet()) {
                LOGGER.info("key " + map.getKey());
                LOGGER.info("value " + map.getValue());
            }

            LOGGER.info("header map " + headerMap.size());
            LOGGER.info("detailContents map " + detailContents.size());

            returnMap.put("headerMap", headerMap);
            returnMap.put("detailContents", detailContents);

            xlsxStream.close();

        } catch (Exception ex) {
            ex.printStackTrace();
            LOGGER.error(ex.getMessage(), ex.getCause());
        }
        return returnMap;
    }




private void getHeaderColumnAndDetails(Map<Integer, String> headerMap, List<Map<String, Object>> detailContents, XSSFSheet sheet) {

        //get header
        Row headerRow = sheet.getRow(0);
        Iterator<Cell> cellHeaderIterator = headerRow.cellIterator();
        while (cellHeaderIterator.hasNext()) {
            //get cell object
            Cell cell = cellHeaderIterator.next();
            headerMap.put(Integer.valueOf(cell.getColumnIndex()), cell.getStringCellValue().trim());
        }

        //get Details
        int headerSize = headerMap.size();
        LOGGER.info("======================================= sheet.getLastRowNum() " + sheet.getLastRowNum());
        for (int rowCount = 1; rowCount <= sheet.getLastRowNum(); rowCount++) {
            Row detailRow = sheet.getRow(rowCount);
            Map<String, Object> detailMap = new HashMap<>();
            LOGGER.info("======================================= rowCount " + rowCount);

            for (int cellCount = 0; cellCount < headerSize; cellCount++) {
                LOGGER.info("======================================= cellCount " + cellCount);
                Cell cell = detailRow.getCell(cellCount);
                if (cell != null) {
                    if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            detailMap.put(headerMap.get(cellCount), cell.getDateCellValue());
                        } else {
                            //force the celltype using string type, its easier to handle cell value in string           
                            //instead in numeric, if using getNumericCellValue it will return data type in double
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            detailMap.put(headerMap.get(cellCount), cell.getStringCellValue().trim());
                        }
                    }
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        detailMap.put(headerMap.get(cellCount), cell.getStringCellValue().trim());
                    }
                } else {
                    detailMap.put(headerMap.get(cellCount), null);
                }

            }
            detailContents.add(detailMap);
        }
    }
	

using the method :

Map<String, Object> dataMap = service.readXlsxFile(modelForm.getFile0().getInputStream());

reference :
http://howtodoinjava.com/2013/06/19/readingwriting-excel-files-in-java-poi-tutorial

Written by snippetjournal

February 5, 2014 at 9:43 am

Posted in Programming

Tagged with , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: